oracle 存储sql,oracle学习-存储过程.sql

oracle学习--存储过程.sql

create or replace procedure test

as

begin

dbms_output.put_line('hello world!');

end;

show serveroutput;

set serveroutput on;

begin

test;

end;

select * from user_source where name='TEST' order by line;

select * from user_source where name='TEST2' order by line;

create or replace procedure product_update_prc

as

begin

update productinfo set description='促销产品'

where productid in (

select productid from (select * from productinfo order by productprice asc) where rownum<4

);

commit;

end;

/

declare

cursor mycursor is select * from productinfo;

myrow productinfo%rowtype;

begin

product_update_prc;

/*

for r in mycursor loop

dbms_output.put_line(r.description);

end loop;*/

open mycursor;

loop

fetch mycursor into myrow;

exit when mycursor%notfound;

dbms_output.put_line(myrow.description);

end loop;

close mycursor;

end;

show errors procedure product_update_prc;

create or replace procedure test2

as

v_categoryid categoryinfo.categoryid%type;

v_categoryname categoryinfo.categoryname%type;

cursor cursor_categoryid is select category from productinfo group by category;

begin

open cursor_categoryid;

loop

fetch cursor_categoryid into v_categoryid;

exit when cursor_categoryid%notfound;

select categoryname into v_categoryname from categoryinfo where categoryid=v_categoryid;

dbms_output.put_line(v_categoryname);

for product in (

select * from productinfo where category=v_categoryid

)

loop

dbms_output.put_line(product.productid||' '||product.productname||' '||product.productprice);

end loop;

end loop;

close cursor_categoryid;

end;

show errors procedure test2;

begin

test2;

end;