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;