oracle学习-存储过程.sql

create or replace procedure testas 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 asbegin  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;