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;