oracle自增序列

创建自增序列
CREATE SEQUENCE SEQname
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999

CREATE or replace TRIGGER TRGname
BEFORE INSERT ON userinfo
FOR EACH ROW
Begin
SELECT SEQname.NEXTVAL
INTO :NEW.customerid
FROM DUAL;
End;

CREATE or replace TRIGGER tr_trade
BEFORE INSERT ON tradeinfo
FOR EACH ROW
Begin
SELECT SEQname.NEXTVAL
INTO :NEW.tid
FROM DUAL;
End;

CREATE or replace TRIGGER tr_type
BEFORE INSERT ON tradetype
FOR EACH ROW
Begin
SELECT SEQname.NEXTVAL
INTO :NEW.transid
FROM DUAL;
End;

创建银行卡号
CREATE or replace TRIGGER card_id
BEFORE INSERT ON cardinfo
FOR EACH ROW
Begin
SELECT '1010 3576 '||lpad(to_char(trunc(dbms_random.value(0,9999))),4,'0')||' '||lpad(to_char(trunc(dbms_random.value(0,9999))),4,'0')
INTO :NEW.cardid
FROM DUAL;
End;

测试语句
insert into userinfo(customername,pid,telephone) values('王法英','370126199003092128','15253503967')
select * from userinfo
insert into cardinfo(savingid,openmoney,balance,password,customerid) values('定期',1,1,123456,2)
select * from cardinfo

select * from tradetype
insert into tradetype(transname) values('转账');
insert into tradetype(transname) values('存款');
insert into tradetype(transname) values('取款');
insert into tradetype(transname) values('手机缴费')；
insert into cardinfo(savingid,openmoney,balance,customerid) values('定期',1,1,13);

insert into cardinfo(savingid,openmoney,balance,customerid) values('定期',1,1,14);
修改客户密码

update cardinfo set password='123456' where customerid=13

办理银行卡挂失

update cardinfo set isreportloss='是' where customerid=13

查询本周开户的卡号，显示该卡相关信息


select * from cardinfo where opendate>=sysdate-7 and opendate<=sysdate

查询本月交易金额最高的卡号

select cardid from tradeinfo where transmoney =
(select max(transmoney) from tradeinfo
where transdate>=to_date(to_char(sysdate,'yyyymm'),'yyyymm') and transdate<=sysdate)

查询挂失账号的客户信息
select * from userinfo where customerid in(
select customerid from cardinfo where isreportloss='是'
)

客户基本信息：v_userInfo

create or replace view v_userinfo as
select customerid 用户编号,customername 用户名,pid 身份证号,telephone 电话,address 地址
from userinfo;

select * from v_userinfo


银行卡信息：v_cardInfo
create or replace view v_cardinfo as
select cardid 银行卡号,curid 货币类型,savingid 存款类型,opendate 开户时间,openmoney 开户金额,
balance 余额,password 密码,isreportloss 是否挂失,customerid 客户编号
from cardinfo;

select * from v_cardinfo


银行卡交易信息：v_tradeInfo

create or replace view v_tradeinfo as
select tid 交易号,transdate 交易时间,tradetype.transname 交易类型,
transmoney 交易金额,transcardid 交易对象,remark 备注
from tradeinfo,tradetype where tradeinfo.transid=tradetype.transid;

select * from v_tradeinfo


查询、统计指定时间段内没有发生交易的账户信息
create or replace procedure user_no_trade(
overDate in date,
startDate in date
)
is
cursor cursor_test is select * from cardinfo where transdate>=startDate and transdate<=overDate
and cardid not in(
select distinct cardid from tradeinfo
);
row_test tradeinfo%rowtype;
begin
for row_test in cursor_test loop
dbms_output.put_line(row_test.cardid||'||'||row_test.curid||'||'||row_test.opendate||'||'||row_test.openmoney
||'||'||row_test.balance||'||'||row_test.isreportloss||'||'||row_test.custeomerid);
end loop;
end;