ORACLE1.24 银行系统操作和游标

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调用的游标

create or replace procedure p_query
(
user_id in bank_user.card_id%type,
deal_type in deal_record.deal_type%type,
mycursor out sys_refcursor
)
is
begin
if deal_type=1 then
open mycursor for select * from deal_record where card_id = user_id and deal_type in (1,4);
elsif deal_type=2 then
open mycursor for select * from deal_record where card_id = user_id and deal_type in (2,3);
elsif deal_type=3 then
open mycursor for select * from deal_record where card_id = user_id;
end if;
end;

declare
cursortype sys_refcursor;
mycursor cursortype%type;
mydeal deal_record%rowtype;
begin
p_query('123',1,mycursor);
loop
fetch mycursor into mydeal;
exit when mycursor%notfound;
dbms_output.put_line(mydeal.card_id||' '||mydeal.money);
end loop;
end;

select * from deal_record
-- java调用的游标
-- 报错的例子
create or replace procedure p_java_query(mycursor out cursor)
is
begin
open mycursor for select id,deal_type,card_id,money,dest_card_id from deal_record;
end p_java_query;

----------------------------
--- 第一步：建立一个程序包（用于定义一个指针变量,引用指针）
-- 看图，就是绿色的东西
create or replace package mypackage as
type
my_cursor is ref cursor;
end mypackage;
commit
------------

create or replace procedure p_java_query(java_cursor out mypackage.my_cursor)
is
begin
open java_cursor for select * from deal_record;
end p_java_query;
-----------------
create or replace procedure p_java_query(java_cursor out mypackage.my_cursor)
is
begin
open java_cursor for select id,(case deal_type when 1 then '存钱' when 2 then '取钱' when 3 then '转出' when 4 then '转入' ELSE '未知' end) deal_type,card_id,money,dest_card_id from deal_record;
end p_java_query;
-----------------

select * from dba_objects where OBJECT_TYPE='PACKAGE' and object_name='MYPACKAGE'
-----------
-- 增加查询人
create or replace procedure p_java_query(user_id in deal_record.card_id%type,java_cursor out mypackage.my_cursor,user_name out bank_user.user_name%type,ye out bank_user.ye%type)
is
begin
open java_cursor for select id,(case deal_type when 1 then '存钱' when 2 then '取钱' when 3 then '转出' when 4 then '转入' ELSE '未知' end) deal_type,card_id,money,dest_card_id from deal_record where card_id=user_id;
select user_name,ye into user_name,ye from bank_user where card_id=user_id;
end p_java_query;