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;