Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE account
- (
- id number(10) NOT NULL,
- account number(20) NOT NULL,
- CONSTRAINT account_id PRIMARY KEY (id)
- );
- CREATE TABLE account_statements
- (
- id number(10) NOT NULL,
- account_id number(10) NOT NULL,
- statement_date date,
- inbalance number(20,2),
- outbalance number (20,2),
- CONSTRAINT statement_id PRIMARY KEY (id),
- CONSTRAINT account_statements_foreign_id FOREIGN KEY (account_id) REFERENCES account(id)
- );
- CREATE TABLE account_operations
- (
- id number(10) NOT NULL,
- account_id number(10) NOT NULL,
- operdate date,
- summ number(20,2),
- opertype char(6),
- CONSTRAINT operations_id PRIMARY KEY (id),
- CONSTRAINT account_operations_foreign_id FOREIGN KEY (account_id) REFERENCES account(id)
- );
- CREATE OR REPLACE FUNCTION Get_debet_on_period (
- v_startdate IN date,
- v_enddate IN date,
- v_account IN account.account%TYPE)
- RETURN number
- IS
- v_debet_summ number(20,2);
- BEGIN
- SELECT SUM(summ) INTO v_debet_summ
- FROM account_operations ao,
- account a
- WHERE ao.operdate between v_startdate AND v_enddate
- AND ao.opertype='DEBET'
- AND a.account=v_account
- AND ao.account_id=a.id;
- RETURN v_debet_summ;
- END;
- CREATE OR REPLACE FUNCTION Get_credit_on_period (
- v_startdate IN date,
- v_enddate IN date,
- v_account IN account.account%TYPE)
- RETURN number
- IS
- v_credit_summ number(20,2);
- BEGIN
- SELECT SUM(summ) INTO v_credit_summ
- FROM account_operations ao,
- account a
- WHERE ao.operdate between v_startdate AND v_enddate
- AND ao.opertype='CREDIT'
- AND a.account=v_account
- AND ao.account_id=a.id;
- RETURN v_credit_summ;
- END;
- CREATE OR REPLACE FUNCTION Get_balance_on_date (
- v_date IN date,
- v_account IN account.account%TYPE)
- RETURN number
- IS
- v_balance_summ number(20,2);
- v_startdate date;
- v_startsumm number;
- BEGIN
- SELECT MAX(as.statement_date) INTO v_startdate
- FROM account_statements as, account a
- WHERE a.id=as.account_id
- AND a.account=v_account
- AND as.statement_date<v_date;
- IF v_startdate IS NOT NULL THEN
- SELECT as.outbalance INTO v_startsumm
- FROM account_statement as, account a
- WHERE a.id=as.account_id
- AND a.account=v_account
- AND as.statement_date=v_statement_date;
- v_balance_summ:=v_startsumm+Get_credit_on_period(v_startdate, v_date, v_account)-Get_debet_on_period(v_startdate, v_date, v_account);
- ELSE
- v_startsumm:=0;
- v_balance_summ:=Get_credit_on_period(v_startdate, v_date, v_account)-Get_debet_on_period(v_startdate, v_date, v_account);
- END IF;
- RETURN v_balance_summ;
- END;
Add Comment
Please, Sign In to add comment