oracle 学习笔记

　　数据库也学了好多种了有oracle,sqlsever,access，理论太多，而且不精,编一段小代码，出错不断，难得调试好了，f8之后居然啥效果都没有，自己有点眼高手低了，暂时将自己的代码放在这，以后慢慢改

create table BANK_USER (id number(10),personid varchar2(10),name varchar2(10),sex

varchar2(5),tel varchar2(12));
alter table BANK_USER modify PERSONID not null;
alter table BANK_USER rename column NAME to userNAME;
alter table BANK_USER add constraint pkkey1 primary key (personid);

insert into BANK_USER
VALUES(2,'9236565','xoy','F','1316663655')

insert into BANK_USER
VALUES(5,'165655','sky','f','11296663655')

SELECT * FROM BANK_USER

create table bank_card (id number(10),cardID number(10) not null ,password varchar2(10)

not null, money number(10));
alter table bank_card add constraint pkkey2 primary key (cardid);

insert into bank_card
values(5,093291,'066098',0)

select * from bank_card

create table bank_card_user (id number(10) default 0, userid varchar2(10),cardid number(10));
alter table bank_card_user add constraint fkkey1 foreign key (userid)  references
bank_user (personid);
alter table bank_card_user add constraint fkkey2 foreign key (cardid) references
bank_card (cardid);
alter table bank_card_user add constraint cukey1 primary key (id);

insert into bank_card_user
values (1,'3236565',563265);

create table bank_business (id number(10),cardid number(10),inputmoney number(10) default
0 not null ,outputmoney number(10) default 0 not null ,businesstime date);
alter table bank_business add constraint pkkey3 primary key(id);
alter table bank_business add constraint fkkey3 foreign key (cardid) references bank_card
(cardid)

declare
--定义一个记录类型
type user_card_record is record(
    id bank_user.id%type,
    personid bank_user.personid%type,
    cardid bank_card.cardid%type
);
--定义一个记录变量
    aa user_card_record;
--定义三个整型变量   
    x number;
    b number;
    c number;
 
 begin
--把表bank_card_user最大id赋值给变量 x;
--把表bank_user最大id赋值给变量 b;
 select max(id) into x from bank_card_user;
 select max(id) into b from bank_user;
--如果表bank_card_user的记录小于表bank_user的记录，则把 表bank_user，bank_card多余的 记录插入到表bank_card_user中
 
 while x <= b
   
  loop

  select id,personid into aa.id ,aa.personid
  from bank_user where id=x+1;
  select cardid into aa.cardid
  from bank_card where id=x+1;
  insert into bank_card_user
  values(aa.id,aa.cardid,aa.personid);
  x:=x+1;
  end loop;
  end;
 
  select * from bank_card_user;
  select * from bank_user;
  select * from bank_card;