oracle中游标与动态sql,ORACLE1.26 综合：游标和动态SQL

--假设分了4个部门(存款部，ATM部，转出，转入)

--每个月定期最后1天自动生成4张表的数据

--(数据来源：deal_record)

--第一步：先把4张表建立起来

--存款表

create table r1(

id number primary key,

card_id varchar2(18),

money number

);

--取款表

create table r2(

id number primary key,

card_id varchar2(18),

money number

);

--转出表

create table r3(

id number primary key,

card_id varchar2(18),

money number

);

create table r4(

id number primary key,

card_id varchar2(18),

money number

);

------------------------------

--思路1，不使用存储过程

--问题：遍历了4次

insert into r1(

select id,card_id,money from deal_record where deal_type=1

)

commit

select * from r1

select * from r2

truncate table r1

--思路2，只遍历1次(游标)

--一边游，一边插入数据

--插入表，不固定(动态SQL)

--带参数的动态SQL (using)

---------------------------

create or replace procedure p_make_record

IS

mydeal deal_record%rowtype;

cursor mycursor is select * from deal_record;

insert_str varchar2(200);

begin

--打开游标

open mycursor;

loop

fetch mycursor into mydeal;

exit when mycursor%notfound;

insert_str:= ‘insert into r‘||mydeal.deal_type||‘ values(:1,:2,:3)‘;

--dbms_output.put_line(insert_str);

execute immediate insert_str using mydeal.id,mydeal.card_id,mydeal.money;

end loop;

commit;

end;