Created
March 5, 2010 02:30
-
-
Save eight/322395 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
|| 消費税率のカレンダーテーブル | |
*/ | |
DROP TABLE TAX_CALENDAR; | |
/ | |
-- TAX CALENDAR TABLE | |
CREATE TABLE TAX_CALENDAR ( | |
DtS date NOT NULL, | |
DtE date NOT NULL, | |
TAX number(3,2) | |
); | |
-- TAX TABLE | |
ALTER TABLE TAX_CALENDAR ADD CONSTRAINT DISP_TAX_CALENDAR_PK PRIMARY KEY (DtS,DtE); | |
/ | |
/* | |
|| TAX_CALENDAR INSERTION | |
*/ | |
DELETE FROM TAX_CALENDAR; | |
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1900-01-01'),to_date('1989-04-01'),0.000); | |
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1989-04-01'),to_date('1997-04-01'),0.03); | |
INSERT INTO TAX_CALENDAR(DtS,DtE,Tax) VALUES(to_date('1997-04-01'),to_date('2999-04-01'),0.05); | |
CREATE OR REPLACE PACKAGE TAX_PKG AS | |
-- 日付で消費税率を返す | |
FUNCTION TAX_RATE(i_date IN date DEFAULT SYSDATE) RETURN NUMBER; | |
-- 日付と金額で消費税額を返す | |
FUNCTION TAX(i_price IN NUMBER,i_date IN date DEFAULT SYSDATE) RETURN NUMBER; | |
-- 日付と金額で税込金額を返す | |
FUNCTION INCLUDE_TAX(i_price IN NUMBER,i_date IN date DEFAULT SYSDATE) RETURN NUMBER; | |
PRAGMA RESTRICT_REFERENCES(TAX_RATE,WNDS); | |
PRAGMA RESTRICT_REFERENCES(TAX,WNDS); | |
PRAGMA RESTRICT_REFERENCES(INCLUDE_TAX,WNDS); | |
END TAX_PKG; | |
/ | |
CREATE OR REPLACE PACKAGE BODY TAX_PKG AS | |
/* | |
|| 日付で消費税率を返す | |
|| TAX_CALENDARテーブルを参照する | |
*/ | |
FUNCTION TAX_RATE(i_date IN date DEFAULT SYSDATE) RETURN NUMBER | |
IS | |
ATax number; | |
BEGIN | |
SELECT TAX INTO ATAX FROM TAX_CALENDAR WHERE i_date >= DtS AND i_date < DtE; | |
RETURN ATAX; | |
END; | |
/* | |
|| 日付で消費税率を返す | |
*/ | |
FUNCTION TAX_RATE2(i_date IN date DEFAULT SYSDATE) RETURN NUMBER | |
IS | |
BEGIN | |
IF i_date < to_date('89-04-01') THEN | |
RETURN 0.00; | |
ELSIF i_date < to_date('97-04-01') THEN | |
RETURN 0.03; | |
ELSE | |
RETURN 0.05; --現状 | |
END IF; | |
END; | |
------------------------------------------------------- | |
-- 日付と金額で消費税額を返す | |
------------------------------------------------------- | |
FUNCTION TAX(i_price IN NUMBER, i_date IN date DEFAULT SYSDATE) RETURN NUMBER | |
IS | |
BEGIN | |
RETURN ROUND(i_price * TAX_RATE(i_date)); | |
END; | |
------------------------------------------------------- | |
-- 日付と金額で税込金額を返す | |
------------------------------------------------------- | |
FUNCTION INCLUDE_TAX(i_price IN NUMBER, i_date IN date DEFAULT SYSDATE) RETURN NUMBER | |
IS | |
BEGIN | |
RETURN ROUND(i_price + (i_price * TAX_RATE(i_date))); | |
END; | |
END TAX_PKG; | |
/ | |
------------------------------------------------------- | |
-- 消費税計算のテスト用 | |
------------------------------------------------------- | |
select tax_pkg.tax_rate('89-03-31'), | |
tax_pkg.tax_rate('89-04-01'), | |
tax_pkg.tax_rate('97-03-31'), | |
tax_pkg.tax_rate('97-04-01'), | |
tax_pkg.tax_rate(SYSDATE) | |
from dual; | |
select tax_pkg.tax(101,'89-03-31'), | |
tax_pkg.tax(101,'89-04-01'), | |
tax_pkg.tax(101,'97-03-31'), | |
tax_pkg.tax(101,'97-04-01'), | |
tax_pkg.tax(101,SYSDATE) | |
from dual; | |
select tax_pkg.include_tax(101,'89-03-31'), | |
tax_pkg.include_tax(101,'89-04-01'), | |
tax_pkg.include_tax(101,'97-03-31'), | |
tax_pkg.include_tax(101,'97-04-01'), | |
tax_pkg.include_tax(101,SYSDATE) | |
from dual; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment