Oracle存储过程

• 到底用存储过程还是存储函数的原则：如果只需要返回一个值用存储函数，否则用存储过程

• 存储过程

--简单的只打印的存储过程（无参）
create or replace procedure sayhelloword
as
begin
  dbms_output.put_line('HELLO WORD');
end;

--带参数的存储过程 uid输入参数
create or replace procedure changes(uid in number)
as
  pid users.id%type;
  pname users.username%type;
  pwd users.password%type;
  psex users.sex%type;
begin
  select id ,username,password,sex into pid,pname,pwd,psex from users where id=uid;
  update users set sex=1 where id=uid;
  dbms_output.put_line(pid||':'||pname||':'||pwd||'前性别：'||psex);
end;
... prompt'''

• 存储函数

create or replace  function queryprice(no in number)
return number
as
    psale emp.sale%type;
    pp emp.prize%type;
begin
  select sale,prize into psale,pp from emp where empno=no;
  return psale*12+vnl(pp);
end;
/
--测试
找到function，右击函数名，点击测试，输入参数的值
... prompt'''

• out参数的使用

create or replace procedure queryempifo(no in number,pname out varchar2, psale out number, ptype out number)
as
begin
  select sname,sale,stype into pname,psale,ptype from emp  where empno=no;
end;
/
... prompt'''

• 存储过程如何返回结果集

1.创建包
create or replace package mypackage as
--定义empcursor类型为cursor
 type empcursor is ref cursor;
 procedure  queryemplist (ty in number,emplist out empcursor);
end mypackage;
2.创建包体
create or replace package body mypackage is

  procedure  queryemplist (ty in number,emplist out empcursor) as
    begin
      open emplist for select * from emp where stype=ty;
    end queryemplist;
end mypackage;
... prompt'''