create table bank_user(
card_id varchar2(18) primary key, --身份证号码
user_name varchar2(100), ---姓名
ye number, ---余额(冗余的)
status number ---状态 1正常 0锁定
);
drop table deal_record;
create table deal_record(
id number primary key,
deal_type number, -- 1.存钱 2.取钱 3.转出 4.转入
card_id varchar2(18), -- 交易者
money number,
dest_card_id varchar2(18) -- 转出对象
);
select * from bank_user
select * from deal_record where card_id='123'
-- 合理的冗余（重复）是一种科学的表现
-- 科学在以空间换时间
insert into bank_user values('123','小明',100,1);
insert into bank_user values('456','小红',300,1);
--3笔事务
1.小明存钱2000元
insert into deal_record values(1,1,'123',2000,null);
update bank_user set ye=ye+2000 where card_id='123';
commit;
2.小明取钱300元
insert into deal_record values(2,2,'123',300,null);
update bank_user set ye=ye-300 where card_id='123';
commit;
3.小明转钱给小红，1500元
INSERT INTO deal_record VALUES (3,3,'123',1500,'456');
INSERT INTO deal_record VALUES (4,4,'456',1500,NULL);
UPDATE bank_user SET ye=ye-1500 WHERE card_id = '123';
UPDATE bank_user SET ye=ye+1500 WHERE card_id = '456';
COMMIT
select * from deal_record where card_id = '456';
select myseq.nextval from dual
-- 存钱的存储过程
create or replace procedure p_add
(
user_id in bank_user.card_id%type,
add_money in deal_record.money%type
)
as
begin
insert into deal_record values(myseq.nextval,1,user_id,add_money,null);
update bank_user set ye=ye+add_money where card_id=user_id;
end;
-- 取钱的存储过程
create or replace procedure p_min
(
user_id in bank_user.card_id%type,
min_money in deal_record.money%type
)
as
begin
insert into deal_record values(myseq.nextval,2,user_id,min_money,null);
update bank_user set ye=ye-min_money where card_id=user_id;
end;
----
--转账的存储过程
create or replace procedure p_tran
(
user_id1 in bank_user.card_id%type,--转出人
user_id2 in bank_user.card_id%type,--转入人
money in deal_record.money%type
)
as
begin
INSERT INTO deal_record VALUES (myseq.nextval,3,user_id1,money,user_id2);
INSERT INTO deal_record VALUES (myseq.nextval,4,user_id2,money,NULL);
UPDATE bank_user SET ye=ye-money WHERE card_id = user_id1;
UPDATE bank_user SET ye=ye+money WHERE card_id = user_id2;
end;
-- pl/sql自己用的游标
-- 查询业务记录
--1.用户id 2.业务类型(1.查选所有收入 2.查询所有的指出 3.全部)
user_id in bank_user.card_id%type,--转出人
query_type in number
create or replace procedure p_query
IS
mydeal deal_record%rowtype;
cursor mycursor is select * from deal_record;
begin
--打开游标
open mycursor;
loop
fetch mycursor into mydeal;
exit when mycursor%notfound;
dbms_output.put_line(mydeal.card_id||' '||mydeal.money);
end loop;
end;
-- java调用的游标