首页 > 代码库 > 银行储蓄管理系统---Oracle数据库表结构、视图、触发器、序列、函数、过程等的sql文件---Bank_Oracle.sql
银行储蓄管理系统---Oracle数据库表结构、视图、触发器、序列、函数、过程等的sql文件---Bank_Oracle.sql
--------------------------------------------- -- Export file for user SCOTT@ORCL -- -- Created by Johnny on 2017/6/4, 14:28:22 -- --------------------------------------------- set define off spool Bank_Oracle.log prompt prompt Creating table ACCOUNT prompt ====================== prompt create table SCOTT.ACCOUNT ( id NUMBER(20) not null, password VARCHAR2(6) not null, balance NUMBER(20,2) not null, opendate DATE not null, openaddress VARCHAR2(20) not null, isdignity NUMBER(1) not null, islost NUMBER(1) default 0 not null, other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.ACCOUNT add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table CLOSING_INSURANCE prompt ================================ prompt create table SCOTT.CLOSING_INSURANCE ( id INTEGER not null, productname VARCHAR2(50) not null, username VARCHAR2(10) not null, idcardnum NUMBER not null, closing_insurance_date DATE not null, amount NUMBER(20,2) not null, other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.CLOSING_INSURANCE add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table CREDIT prompt ===================== prompt create table SCOTT.CREDIT ( cardint NUMBER not null, username VARCHAR2(10) not null, withid NUMBER(20) not null, balance NUMBER(20,2) not null, creditlimit NUMBER(20,2) not null, creditstatus VARCHAR2(10) default ‘good‘ not null, other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.CREDIT add primary key (CARDINT) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table DEL_ACCOUNT prompt ========================== prompt create table SCOTT.DEL_ACCOUNT ( delid NUMBER(20) not null, id NUMBER(20) not null, balance_beforedel NUMBER(20,2) not null, deldate DATE not null, operaterid NUMBER(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.DEL_ACCOUNT add primary key (DELID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table DEPOSIT prompt ====================== prompt create table SCOTT.DEPOSIT ( depositid NUMBER(20) not null, id NUMBER(20) not null, amount NUMBER(20,2) not null, depositdate DATE not null, deposittype VARCHAR2(4) not null, interest NUMBER(20,6) not null, operaterid NUMBER(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.DEPOSIT add primary key (DEPOSITID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table DEPUTE prompt ===================== prompt create table SCOTT.DEPUTE ( id INTEGER not null, amount NUMBER(20,2), deputename VARCHAR2(50), depute_service VARCHAR2(50), other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table DRAW prompt =================== prompt create table SCOTT.DRAW ( drawid NUMBER(20) not null, id NUMBER(20) not null, amount NUMBER(20,2) not null, interest NUMBER(20,2) not null, balance NUMBER(20,2), drawdate DATE not null, drawtype VARCHAR2(10) not null, operaterid NUMBER(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.DRAW add primary key (DRAWID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table INTEREST_RATE prompt ============================ prompt create table SCOTT.INTEREST_RATE ( id NUMBER(20) not null, type VARCHAR2(4) not null, interest NUMBER(11,10) not null, timelimit NUMBER(3) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.INTEREST_RATE add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table LOAN prompt =================== prompt create table SCOTT.LOAN ( loanid INTEGER not null, withid NUMBER(20) not null, username VARCHAR2(20) not null, idcardnum NUMBER(20) not null, userphone VARCHAR2(20), loanamount NUMBER(20,2) not null, loanlimit INTEGER not null, loaninterstrate NUMBER(11,10) not null, loandate DATE not null, deadline DATE not null, reamountpermontht NUMBER(20,2) not null, other VARCHAR2(50) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.LOAN add primary key (LOANID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table LOST prompt =================== prompt create table SCOTT.LOST ( id NUMBER(20) not null, balance NUMBER(20,2) not null, lostdate DATE not null, operaterid VARCHAR2(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.LOST add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table MANAGER prompt ====================== prompt create table SCOTT.MANAGER ( id NUMBER(10), password VARCHAR2(6), grade NUMBER(1), username VARCHAR2(10), idcardnum VARCHAR2(18), phone NUMBER(15), address VARCHAR2(80), photo VARCHAR2(50), other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table PLAN_TABLE prompt ========================= prompt create table SCOTT.PLAN_TABLE ( statement_id VARCHAR2(30), plan_id NUMBER, timestamp DATE, remarks VARCHAR2(4000), operation VARCHAR2(30), options VARCHAR2(255), object_node VARCHAR2(128), object_owner VARCHAR2(30), object_name VARCHAR2(30), object_alias VARCHAR2(65), object_instance INTEGER, object_type VARCHAR2(30), optimizer VARCHAR2(255), search_columns NUMBER, id INTEGER, parent_id INTEGER, depth INTEGER, position INTEGER, cost INTEGER, cardinality INTEGER, bytes INTEGER, other_tag VARCHAR2(255), partition_start VARCHAR2(255), partition_stop VARCHAR2(255), partition_id INTEGER, other LONG, distribution VARCHAR2(30), cpu_cost INTEGER, io_cost INTEGER, temp_space INTEGER, access_predicates VARCHAR2(4000), filter_predicates VARCHAR2(4000), projection VARCHAR2(4000), time INTEGER, qblock_name VARCHAR2(30), other_xml CLOB ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table RECORD_PAY_INSURANCE prompt =================================== prompt create table SCOTT.RECORD_PAY_INSURANCE ( recordid INTEGER not null, typename VARCHAR2(30), username VARCHAR2(30), idcard NUMBER(18), dotime DATE, amount NUMBER(20,2), other VARCHAR2(20) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.RECORD_PAY_INSURANCE add primary key (RECORDID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table REPAYMENT prompt ======================== prompt create table SCOTT.REPAYMENT ( repayint INTEGER not null, loanid INTEGER not null, deadline DATE not null, repaymentamount NUMBER(20,2) not null, isbreakpromise INTEGER not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.REPAYMENT add primary key (REPAYINT) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table TRANSFER prompt ======================= prompt create table SCOTT.TRANSFER ( transferid NUMBER(20) not null, sourceid NUMBER(20) not null, destinateid NUMBER(20) not null, amount NUMBER(20,2) not null, transferdate DATE not null, brokerage NUMBER(20,2) not null, operaterid NUMBER(10) not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.TRANSFER add primary key (TRANSFERID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating table USERS prompt ==================== prompt create table SCOTT.USERS ( id NUMBER(20) not null, idcardnum NUMBER(18), username VARCHAR2(10), phone VARCHAR2(11), photo VARCHAR2(50), other VARCHAR2(50) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); alter table SCOTT.USERS add primary key (ID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); prompt prompt Creating sequence ACCOUNTIDSEQUENCE prompt =================================== prompt create sequence SCOTT.ACCOUNTIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 8600000041 increment by 1 cache 20; prompt prompt Creating sequence CREDITIDSEQUENCE prompt ================================== prompt create sequence SCOTT.CREDITIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 8180021 increment by 1 cache 20; prompt prompt Creating sequence DEL_ID_SEQUENCE prompt ================================= prompt create sequence SCOTT.DEL_ID_SEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 120 increment by 1 cache 20; prompt prompt Creating sequence DEPOSITIDSEQUENCE prompt =================================== prompt create sequence SCOTT.DEPOSITIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 8880081 increment by 1 cache 20; prompt prompt Creating sequence DEPUTEIDINCREASE prompt ================================== prompt create sequence SCOTT.DEPUTEIDINCREASE minvalue 1 maxvalue 999999999999999999999999999 start with 60001 increment by 1 cache 20; prompt prompt Creating sequence IDINCREASE prompt ============================ prompt create sequence SCOTT.IDINCREASE minvalue 1 maxvalue 999999999999999999999999999 start with 1000061 increment by 1 cache 20; prompt prompt Creating sequence LOANIDSEQUENCE prompt ================================ prompt create sequence SCOTT.LOANIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 5150041 increment by 1 cache 20; prompt prompt Creating sequence RECORDID_CO_INS_SEQUENCE prompt ========================================== prompt create sequence SCOTT.RECORDID_CO_INS_SEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 30061 increment by 1 cache 20; prompt prompt Creating sequence RECORD_PAYINSUR_ID_SEQUENCE prompt ============================================= prompt create sequence SCOTT.RECORD_PAYINSUR_ID_SEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 2310061 increment by 1 cache 20; prompt prompt Creating sequence REPAYIDSEQUENCE prompt ================================= prompt create sequence SCOTT.REPAYIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 5250021 increment by 1 cache 20; prompt prompt Creating sequence TRANSFERIDSEQUENCE prompt ==================================== prompt create sequence SCOTT.TRANSFERIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 1230041 increment by 1 cache 20; prompt prompt Creating sequence USERIDSEQUENCE prompt ================================ prompt create sequence SCOTT.USERIDSEQUENCE minvalue 1 maxvalue 999999999999999999999999999 start with 1010061 increment by 1 cache 20; prompt prompt Creating function CALCULATEINTEREST prompt =================================== prompt create or replace function scott.calculateInterest(pid int) return number as fsysdate Date; finterest deposit.interest%type; starttime Date; ftype varchar(4); fdeposit deposit.amount%type; durtime int; fsave deposit.amount%type; begin select sysdate into fsysdate from dual; select depositdate,deposittype into starttime,ftype from deposit where deposit.depositid=pid; select interest,timelimit into fdeposit,durtime from interest_rate where interest_rate.type=ftype; if (fsysdate-starttime)/30>=durtime then select amount into fsave from deposit where deposit.depositid=pid; finterest:=fsave*power((1+fdeposit),(durtime/12))-fsave; else return 0; end if; return finterest; end; / prompt prompt Creating function GETPHOTO prompt ========================== prompt create or replace function scott.getphoto(fid in number) return varchar is FunctionResult varchar(50); begin select photo into FunctionResult from manager where id=fid; return(FunctionResult); end getphoto; / prompt prompt Creating function JUDGEKEY prompt ========================== prompt create or replace function scott.judgeKey (fid in int ,fkey in varchar) return int as realkey account.password%type; begin select password into realkey from account where account.id=fid; if fkey=realkey then return 1; else return 0; end if; return 0; end; / prompt prompt Creating function JUDGEKEY_MANAGER prompt ================================== prompt create or replace function scott.judgekey_manager(fid in int,fkey in varchar) return integer is FunctionResult integer; realkey manager.password%type; begin select password into realkey from manager where fid=manager.id; if fkey=realkey then return 1; else return 0; end if; return 0; end judgekey_manager; / prompt prompt Creating function JUDGEMANAGERLEVEL prompt =================================== prompt create or replace function scott.judgemanagerlevel(fid in number) return integer is FunctionResult integer; begin select grade into FunctionResult from manager where id=fid; return(FunctionResult); end judgemanagerlevel; / prompt prompt Creating procedure ALTERACCOUNTPWD prompt ================================== prompt create or replace procedure scott.alterAccountpwd(paccountid in int,ppwd in varchar) is begin update account set password=ppwd where id=paccountid; end alterAccountpwd; / prompt prompt Creating procedure ALTERADDRESS prompt =============================== prompt create or replace procedure scott.alteraddress(pid in number, paddress in varchar) is begin update manager set manager.address=paddress where manager.id=pid; end; / prompt prompt Creating procedure ALTERCONTACT prompt =============================== prompt create or replace procedure scott. altercontact (pid in number, pphone in number) is begin update manager set manager.phone=pphone where manager.id=pid; end; / prompt prompt Creating procedure ALTERKEY prompt =========================== prompt create or replace procedure scott.alterkey(pid in number,ppassword in varchar) is begin update manager set password=ppassword where id=pid; end alterkey; / prompt prompt Creating procedure ALTERLIMIT prompt ============================= prompt create or replace procedure scott.alterLimit(pid in int,pnewLimit in number) is begin update credit set creditlimit=pnewlimit where cardint=pid; end alterLimit; / prompt prompt Creating procedure ALTERPHOTO prompt ============================= prompt create or replace procedure scott.alterphoto (pid in manager.id%type,pphoto in manager.photo%type) as begin update manager set manager.photo=pphoto where manager.id=pid; end; / prompt prompt Creating procedure BUSSINESSASSURANCE prompt ===================================== prompt create or replace procedure scott.bussinessAssurance(paccountid in int, passurancename in varchar,pname in varchar,pidcard in number,ptime in Date,pmoney in number,other in varchar) is fund number(20,2); begin select balance into fund from account; if fund>pmoney then update account set balance=balance -pmoney where account.id=paccountid; insert into record_pay_insurance(typename,username,idcard,dotime,amount,other) values(passurancename,pname,pidcard,ptime,pmoney,other); else rollback; end if; end bussinessAssurance; / prompt prompt Creating procedure CANCELACCOUNT prompt ================================ prompt create or replace procedure scott.cancelAccount (pid in number,poperate in int) as ptime Date; pmoney account.balance%type; begin select sysdate into ptime from dual; select balance into pmoney from account where id=pid; insert into del_account(id,balance_beforeDel,delDate,operaterId) values(pid,pmoney,ptime,poperate); end; / prompt prompt Creating procedure CANCELFREEZEACCOUNT prompt ====================================== prompt create or replace procedure scott.cancelfreezeAccount (pid in number) as begin update account set account.islost=0 where account.id=pid; end; / prompt prompt Creating procedure CANCELREPORTLOSS prompt =================================== prompt create or replace procedure scott.cancelreportLoss (pid in int) as begin update account set islost=0 where account.id=pid; delete from lost where lost.id=pid; end; / prompt prompt Creating procedure CHANGERATE prompt ============================= prompt create or replace procedure scott.changerate(pid in number,pnew_rate in number) is begin update interest_rate set interest = pnew_rate where id=pid; end changerate; / prompt prompt Creating procedure CREATEACCOUNT prompt ================================ prompt create or replace procedure scott.createAccount(pid in number,ppassword in number,pbalance in number,pcreateplace in varchar,pisdignity in int ,pislost in int ,pother in varchar) is pdate Date; begin select sysdate into pdate from dual; insert into account(id,password,balance,opendate,openaddress,isdignity,islost,other) values(pid,ppassword,pbalance,pdate,pcreateplace,pisdignity,pislost,pother); end createAccount; / prompt prompt Creating procedure CREATEDEPOSIT prompt ================================ prompt create or replace procedure scott.createDeposit (id in number,pname in varchar,phone in varchar,photo in varchar,other in varchar) as begin insert into users(idcardnum,username,phone,photo,other) values(id,pname,phone,photo,other); end; / prompt prompt Creating procedure DEPOSITDEMAND prompt ================================ prompt create or replace procedure scott.depositDemand (pid in int, pmoney in number,pdatestring in varchar,pinterest in number,poperate in int) as ptime Date; begin select to_date(pdatestring ,‘yyyy-mm-dd hh24:mi:ss‘) into ptime from dual; insert into deposit (id,amount,depositdate,deposittype,interest,operaterid) values (pid,pmoney,ptime,‘活期‘,pinterest,poperate); update account set balance=balance+pmoney where account.id=pid; end; / prompt prompt Creating procedure DEPOSITFIXED prompt =============================== prompt create or replace procedure scott.depositFixed (pid in int,pmoney in number,pdatestring in varchar,ptype in varchar,pinterest in number,poperate in int) as ptime Date; begin select to_date(pdatestring,‘yyyy-mm-dd hh24:mi:ss‘) into ptime from dual; insert into deposit (id,amount,depositdate,deposittype,interest,operaterid) values (pid,pmoney,ptime,ptype,pinterest,poperate); update account set account.balance=account.balance+pmoney where account.id=pid; end; / prompt prompt Creating procedure DRAWCREDIT prompt ============================= prompt create or replace procedure scott.drawCredit(pid in int, psum in number,pdatestring in varchar,ptype in varchar,operatorid in int) is belongedID int; pbalance number(20,2); pdate Date; begin select to_date(pdatestring,‘yyyy-mm-dd hh24:mi:ss‘) into pdate from dual; select withid,balance into belongedID,pbalance from credit where cardint=pid; pbalance := pbalance-psum; update credit set balance = pbalance where cardint=pid; insert into draw(id,amount,interest,balance,drawdate,drawtype,operaterid) values(belongedID,psum,0,psum,pdate,ptype,operatorid); end drawCredit; / prompt prompt Creating procedure DRAWMONEY prompt ============================ prompt create or replace procedure scott.drawMoney(pid in int ,saveodd in int,psum in number,pdatestring in varchar,ptype in varchar,operator in int) is pinterest number(20,2); total number(20,2); pbalance number(20,2); psysdate Date; pdate Date; begin select to_date(pdatestring,‘yyyy-mm-dd hh24:mi:ss‘) into pdate from dual; select sysdate into psysdate from dual; select balance into pbalance from account where account.id=pid; select calculateInterest(saveodd) into pinterest from dual; select (pinterest+psum) into total from dual; if pbalance>psum then pbalance := pbalance -psum; else rollback; end if; update account set balance = pbalance where id=pid; insert into draw (id,amount,interest,balance,drawdate,drawtype,operaterid) values(pid,psum,pinterest,total,pdate,ptype,operator); delete from deposit where deposit.depositid=saveodd; end drawMoney; / prompt prompt Creating procedure DRAWQUICK prompt ============================ prompt create or replace procedure scott.drawQuick(pid in int,psum in number,pdatestring in varchar,ptype in varchar,operatorid in int) is pbalance number(20,2); pdate Date; begin select to_date(pdatestring,‘yyyy-mm-dd hh24:mi:ss‘) into pdate from dual; select balance into pbalance from account where account.id=pid; if pbalance>=psum then pbalance:=pbalance-psum; else return; end if; update account set balance=pbalance where id=pid; insert into draw (id,amount,interest,balance,drawdate,drawtype,operaterid) values(pid,psum,0,psum,pdate,ptype,operatorid); end drawQuick; / prompt prompt Creating procedure ESTABLISHCREDIT prompt ================================== prompt create or replace procedure scott.establishCredit(pid in int ,plimitMoney in number,pstate in varchar) is pname varchar(10); begin select username into pname from users where id=pid; insert into credit(username,withid,balance,creditlimit,creditstatus) values(pname,pid,0,plimitMoney,pstate); end establishCredit; / prompt prompt Creating procedure EXPENSECOLLECTION prompt ==================================== prompt create or replace procedure scott.expenseCollection(paccountid in number,pcollectionname in varchar,pidcard in number,ptimestring in varchar,pmoney in number,pother in varchar) is fund number(20,2); ptime Date; pusername varchar(20); pidcard_1 number(18); begin select to_date(ptimestring,‘yyyy-mm-dd hh24:mi:ss‘) into ptime from dual; select balance into fund from account where id=paccountid; select username into pusername from users where id=paccountid; select idcardnum into pidcard_1 from users where id=paccountid; if fund>pmoney then update account set balance=balance -pmoney where id=paccountid; insert into record_pay_insurance(typename,username,idcard,dotime,amount,other) values(pcollectionname,pusername,pidcard_1,ptime,pmoney,pother); else rollback; end if; end expenseCollection; / prompt prompt Creating procedure FREEZEACCOUNT prompt ================================ prompt create or replace procedure scott.freezeAccount (pid in number) as begin update account set islost=3 where account.id=pid; end; / prompt prompt Creating procedure LOANQ prompt ======================== prompt create or replace procedure scott.loanq(pid in int, pname in varchar,pidcard in number,pphone in varchar,pmoney in number,ptime in int,prate in number,ploandatestring in varchar,penddatestring in varchar,premoneymonth in number,pother in varchar) is ploandate Date; penddate Date; begin select to_date(ploandatestring,‘yyyy-mm-dd hh24:mi:ss‘) into ploandate from dual; select to_date(penddatestring,‘yyyy-mm-dd hh24:mi:ss‘) into penddate from dual; insert into loan(withid,username,idcardnum,userphone,loanamount,loanlimit,loaninterstrate,loandate,deadline,reamountpermontht,other) values(pid,pname,pidcard,pphone,pmoney,ptime,prate,ploandate,penddate,premoneymonth,pother); end loanq; / prompt prompt Creating procedure REPORTLOSS prompt ============================= prompt create or replace procedure scott.reportLoss (pid in int,ptimestring in varchar,poperate in int) as ptime Date; pmoney account.balance%type; begin select to_date(ptimestring,‘yyyy-mm-dd hh24:mi:ss‘) into ptime from dual; select balance into pmoney from account where account.id=pid; insert into lost(id,balance,lostdate,operaterid) values(pid,pmoney,ptime,poperate); update account set islost=1 where account.id=pid; end; / prompt prompt Creating procedure RETURNLOAN prompt ============================= prompt create or replace procedure scott.returnloan(pid in int ,ptimestring in varchar, pmoney in number,pbreakrules in int) is ptime Date; begin select to_date(ptimestring,‘yyyy-mm-dd hh24:mi:ss‘) into ptime from dual; insert into repayment(loanid,deadline,repaymentamount,isbreakpromise) values(pid,ptime,pmoney,pbreakrules); end returnloan; / prompt prompt Creating procedure SOCIALASSURANCE prompt ================================== prompt create or replace procedure scott.socialAssurance(paccountid in int,pname in varchar,pidcard in number,ptimestring in varchar,pmoney in number,pother in varchar) is fund number(20,2); ptime Date; begin select to_date(ptimestring,‘yyyy-mm-dd hh24:mi:ss‘) into ptime from dual; select balance into fund from account where id=paccountid; if(fund>pmoney) then update account set balance = balance-pmoney where id=paccountid; insert into record_pay_insurance(typename,username,idcard,dotime,amount,other) values(‘社会保险‘,pname,pidcard,ptime,pmoney,pother); else rollback; end if; end socialAssurance; / prompt prompt Creating procedure TRANSFERMONEY prompt ================================ prompt create or replace procedure scott.transferMoney(pid1 in number,pid2 in number ,pmoney in number,ptimestring in varchar,pcharge in number,poperator in int) is fund number(20,2); ptime Date; begin select to_date(ptimestring,‘yyyy-mm-dd hh24:mi:ss‘) into ptime from dual; select balance into fund from account where id=pid1; if fund>(pmoney+pcharge) then insert into transfer(sourceid,destinateid,amount,transferdate,brokerage,operaterid) values(pid1,pid2,pmoney,ptime,pcharge,poperator); update account set balance=balance-pmoney-pcharge where account.id=pid1; update account set balance=balance+pmoney where account.id=pid2; else rollback; end if; end transferMoney; / prompt prompt Creating trigger CREDITIDGENERATE prompt ================================= prompt create or replace trigger scott.creditidgenerate before insert on credit for each row declare -- local variables here begin select creditidsequence.nextval into :NEW.CARDINT from dual; end creditidgenerate; / prompt prompt Creating trigger DEL_ACCOUNT prompt ============================ prompt create or replace trigger scott.del_account after insert on del_account for each row declare -- local variables here begin delete from account where id= :new.id; end del_account; / prompt prompt Creating trigger DEL_ID_GENERATE prompt ================================ prompt create or replace trigger scott.del_id_generate before insert on del_account for each row declare -- local variables here begin select del_id_sequence.nextval into :new.delid from dual; end del_id_generate; / prompt prompt Creating trigger DEPOSITIDGENERATE prompt ================================== prompt create or replace trigger scott.depositidgenerate before insert on deposit for each row declare -- local variables here begin select depositidsequence.nextval into :NEW.Depositid from dual; end depositidgenerate; / prompt prompt Creating trigger DRAWIDINCREASE prompt =============================== prompt create or replace trigger scott.drawIdIncrease before insert on draw for each row declare -- local variables here begin select idincrease.nextval into :new.drawid from dual ; end drawIdIncrease; / prompt prompt Creating trigger GENERATEACCOUNTID prompt ================================== prompt create or replace trigger scott.generateAccountId before insert on account for each row declare -- local variables here begin select accountidsequence.nextval into :NEW.Id from dual; end generateAccountId; / prompt prompt Creating trigger LOANIDGENERATE prompt =============================== prompt create or replace trigger scott.loanidgenerate before insert on loan for each row declare -- local variables here begin select loanidsequence.nextval into :NEW.Loanid from dual; end loanidgenerate; / prompt prompt Creating trigger RECORDID_CO_INS_GENERATE prompt ========================================= prompt create or replace trigger scott.recordid_co_ins_generate before insert on record_pay_insurance for each row declare -- local variables here begin select recordid_co_ins_sequence.nextval into :NEW.RECORDID from dual; end recordid_co_ins_generate; / prompt prompt Creating trigger RECORD_PAYINSUR_ID_GENERATE prompt ============================================ prompt create or replace trigger scott.record_payinsur_id_generate before insert on record_pay_insurance for each row declare -- local variables here begin select record_payinsur_id_sequence.nextval into :new.recordid from dual; end record_payinsur_id_generate; / prompt prompt Creating trigger REPAYIDGENERATE prompt ================================ prompt create or replace trigger scott.repayidgenerate before insert on repayment for each row declare -- local variables here begin select repayidsequence.nextval into :new.repayint from dual; end repayidgenerate; / prompt prompt Creating trigger TRANSFER_ID_GENERATE prompt ===================================== prompt create or replace trigger scott.transfer_id_generate before insert on transfer for each row declare -- local variables here begin select transferidsequence.nextval into :new.transferid from dual; end transfer_id_generate; / prompt prompt Creating trigger USERIDGENERATE prompt =============================== prompt create or replace trigger scott.useridgenerate before insert on users for each row declare -- local variables here begin select useridsequence.nextval into :new.id from dual; end useridgenerate; / spool off
银行储蓄管理系统---Oracle数据库表结构、视图、触发器、序列、函数、过程等的sql文件---Bank_Oracle.sql
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。