Oracle游标

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,
   - 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
(
 user_id bank_user.card_id%type
)
IS
  mydeal deal_record%rowtype;
  cursor mycursor is select * from deal_record where card_id=user_id;
begin
  --打开游标
  open mycursor;
  loop
     fetch mycursor into mydeal;
     exit when mycursor%notfound;
     
     dbms_output.put_line(mydeal.card_id||'   '||mydeal.money);
     
  end loop;
end;
--------------
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;