oracle部分存储过程

1.create user IT337008 identified by 'Anqi';#15058803，name：passwd：

学生证

2:

create procedure test(birthday in date) as

bi number;

begin

select TRUNC(months_between(sysdate, birthday)/12) into bi from dual;

if (bi<13) then

DBMS_OUTPUT.PUT_LINE('xiao13');

elsif (bi<20) then

DBMS_OUTPUT.PUT_LINE('xiao20');

elsif (bi<65) then

DBMS_OUTPUT.PUT_LINE('xiao65');

else

DBMS_OUTPUT.PUT_LINE('da65');

end if;

end test;

drop procedure test;

call test(to_date('2000-12-12','yyyy-mm-dd'));

SQL> set serveroutput on;

SQL> exec test(to_date('2000-12-12','yyyy-mm-dd'));

da20

PL/SQL procedure successfully completed

CREATE GLOBAL TEMPORARY TABLE REPROTTEST(

ID NUMBER,

ANAME VARCHAR2(20)

) ON COMMIT DELETE ROWS;

create or replace procedure report_month_responsibility(

o_cur out sys_refcursor)

as

begin

insert into reprottest(id,aname) values(1,'1');

open o_cur for select * from reprottest;

end report_month_responsibility;

declare

v_id number;

v_aname varchar2(20);

o_cur sys_refcursor;

begin

report_month_responsibility(o_cur);

fetch o_cur into v_id,v_aname;

while o_cur%found loop

dbms_output.put_line('输出结果：'||v_id||','||v_aname);

fetch o_cur into v_id,v_aname;

end loop;

commit;

end;

/

VARIABLE x REFCURSOR

DECLARE

V_Sqlstatement Varchar2(2000);

BEGIN

V_Sqlstatement := 'SELECT * FROM t';

OPEN :x for v_Sqlstatement;

End;

/

print :x;

create or replace procedure testb

is

begin

for i in (select * from test11) loop

dbms_output.put_line(i.id);

end loop;

end;

/

------插入一个表的同时插入另外一个表

Create or replace trigger tri_A

after insert on ODATA_DELIVER_M

For each row

declare

pragma autonomous_transaction;

dno integer;

begin

select count(1) into dno from HXMY_DESALE_D t where

t.deliver_no=:new.deliver_no;

if dno=1 then

insert into ODATA_BDSALE_M(deliver_no,id) values( :new.deliver_no,:new.id) ; --

in (select deliver_no from HXMY_DESALE_D);

commit;

end if;

end;

/