用户5884834322944
2024-06-16 01:30:47
最佳回答
功能一:create database bank;功能二:create table user**(customerid number(10) not **,customername varchar2(10) not **,pid char(18) not **,telephone char(11) not **,address varchar2(255) not **);create table card**(cardid varchar2(19) not **,cardcustomerid number(10) not **,curtype char(3) not **,s**ingtype char(4) not **,opendate date not **,openmoney number(10,2) not **,blance number(10,2) not **,password char(6) not **,**reportloss char(2) not **);create table trans**(id number(15) not **,transcustomerid number(10) not **,transcardid varchar2(19) not **,transdate date not **,transmoney number(10,2) not **,transtype char(4) not **,remark varchar2(255) not **);功能三:alter table user** add constraint pk_user** primary key (customerid);alter table user** add constraint ck_pid check(length(pid)=18);alter table card** add constraint pk_card** primary key (cardid);alter table card** add constraint fk_customerid_card foreign key cardcustomerid references user**(customerid);alter table trans** add constraint pk_trans** primary key (id);alter table trans** add constraint fk_customerid_trans foreign key cardcustomerid references user**(customerid);alter table trans** add constraint fk_transcardid_trans foreign key cardcustomerid references card**(cardid);功能四:insert into user** values(1,'张三','123456789012345671','13012345671','地址1');insert into user** values(2,'李四','123456789012345672','13012345672','地址2');insert into user** values(3,'王五','123456789012345673','13012345673','地址3');insert into card** values('1234567890123456789',1,'rmb','活期',sysdate,5.04,5.04,'123456','否');insert into card** values('1234567890123456788',2,'jpy','活期',sysdate,3.22,3.22,'123457','否');insert into card** values('1234567890123456787',3,'usa','定期',sysdate,6.78,6.78,'123458','否');insert into trans** values(1,1,'1234567890123456789',sysdate,0.23,'存入','存钱');insert into trans** values(2,2,'1234567890123456788',sysdate,1.27,'支取','取钱');insert into trans** values(3,3,'1234567890123456787',sysdate,2.34,'存入','存钱');功能五:select transcardid,transmoney from trans** where to_char(transdate,'mm')=to_char(sysdate,'mm') and transmoney=max(transmoney);功能六:create procedure p_c**cursor cr **select b.cardid,a.customername,a.telephone from user** a,card** b where a.customerid=b.cardcustomerid and to_char(sysdate,'dd') in ('28','29','30','31') and b.blance<200;cur_** cr;beg**r cur_** in cr loopdbms_output.put_line('**:' || cur_**.cardid || '&&' || '姓名:' || cur_**.customername || '电话:' || cur_**.telephone);end loop;end p_c; 20210311