oracle 存儲過程、函數(shù)和觸發(fā)器用法實例詳解

create [or replace] procedure 過程名(參數(shù)列表)
as
PLSQL子程序體;

--給指定員工漲工資
create procedure addSal(empid in number)
as
  psal emp.sal%type;
begin
  select sal into psal from emp where empno=empid;
  update emp set sal = sal * 1.1 where empno=empid;
  dbms_output.put_line(empid || '漲工資前' || psal || '漲工資后' || (psal * 1.1));
end;

--方法一
begin
addSal(7369);
end;
--方法二
exec addSal(7369);

CREATE [OR REPLACE] FUNCTION 函數(shù)名 (參數(shù)列表)
RETURN 函數(shù)值類型
AS
PLSQL子程序體;

--查詢指定員工的年收入
create function queryEmpSal(empid in number)
return number
as
  psal emp.sal%type;
  pcomm emp.comm%type;
begin
  select sal,comm into psal,pcomm from emp where empno=empid;
  return (psal*12) + nvl(pcomm,0);
end;

declare
 psal number;
begin
 psal:=queryEmpSal(7369);
 dbms_output.put_line(psal);
end;

begin
 dbms_output.put_line(queryEmpSal(7369));
end;

--包定義
create [or replace] package 包名 as
[公有數(shù)據(jù)類型定義]
[公有游標(biāo)聲明]
[公有變量、常量聲明]
[公有子程序聲明]
end 包名;

--包主體
create [or replace] package body 包名 as
[私有數(shù)據(jù)類型定義]
[私有變量、常量聲明]
[私有子程序聲明和定義]
[公有子程序定義]
begin
PL/SQL子程序體;
end 包名;

--創(chuàng)建mypackage包
create or replace package mypackage as
 procedure total(num1 in number, num2 in number, num3 out number);
end mypackage;

--mypackage包體
create or replace package body mypackage as
--計算累加和的total過程
procedure total(num1 in number, num2 in number, num3 out number) as
 tmp number := num1;
begin
 if num2 < num1 then num3 := 0;
 else num3 := tmp;
  loop
   exit when tmp > num2;
   tmp := tmp + 1;
   num3 := num3 + tmp;
  end loop;
 end if;
end total;

end mypackage;

declare
 num1 number;
begin
 mypackage.total(1, 5, num1);
 dbms_output.put_line(num1);
end;