oracle資料庫預存程序例子

預存程序：一段程式，用於改變資料庫物件的狀態，可以包含一個或多個行為，往往是將一個表的記錄經過處理後放到另一個表。

1、基本文法：

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] TYPE[,....])]
{IS | AS }
  [LOCAL declarations]
BEGIN
  executable statements;
[EXCEPTION
  exception_statements;
END procedure_name;

例子：

--/
CREATE OR REPLACE PROCEDURE proc_1 (num number, name varchar2)
IS
BEGIN
  insert into emp(empno, ename) values (num, name);
END;
/

調用：

--/
BEGIN
  proc_1(1002, '1002');
END;
/

2、in和out參數：
in參數表示預存程序的輸入參數。out參數表示預存程序的輸出參數，輸出參數會改變參數的值，傳遞到調用方的一個變數當中。
例子：

--/
CREATE OR REPLACE PROCEDURE proc_2 (num in number, v_emp out emp%rowtype)
IS
BEGIN
  select * into v_emp from emp where empno = num;
END;
/

調用：

--/
DECLARE
  var emp%rowtype;
BEGIN
  proc_2(1000, var);
  dbms_output.put_line(var.ename);
END;
/

3、同時帶in和out參數：
IN OUT模式是IN和OUT方式的組合，又稱為輸入/輸出參數。
當過程被調用的時候，實際參數的值會被傳遞給過程，形式參數可以被讀出和寫入。當程序呼叫結束，控制返回到調用環境時，形式參數的內容被賦予實際的值。
例子：

--/
CREATE OR REPLACE PROCEDURE proc_3(x IN NUMBER, y IN OUT NUMBER)
IS
  v_tmp NUMBER;
BEGIN
  v_tmp := x + y;
  y := v_tmp;
END;
/

調用：

--/
DECLARE
  num1 number :=1;
  num2 number :=2;
BEGIN
  proc_3(num1, num2);
  dbms_output.put_line(num2);
END;
/

4、
預存程序中具體的處理依賴於pl sql的基礎文法來實現。

預存程序樣本：為指定的職工在原工資的基礎上長10%的工資
 
/*
為指定的職工在原工資的基礎上長10%的工資，並列印工資前和工資後的工資
*/
SQL> create or replace procedure raiseSalary(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;
 1  /
 
Procedure created
--預存程序調用
--方法一
SQL> set serveroutput on
SQL> exec raisesalary(7369);
 
員工號:7369漲工資前
800漲工資後880
 
方法二
    set serveroutput on
begin
 raisesalary(7369);
end;
/
 
PL/SQL procedure successfully completed
 
 
       儲存函數
      函數（Function）為一命名的儲存程式，可帶參數，並返回一計算值。函數和過程的結構類似，但必須有一個RETURN子句，用於返回函數值。函數說明要指定函數名、結果值的類型，以及參數類型等。
 
     建立儲存函數的文法：
 
CREATE [OR REPLACE] FUNCTION函數名(參數列表)
 RETURN 函數實值型別
AS
PLSQL子程式體；
 
 
      樣本：查詢某職工的年度營收。
SQL> /**/
    /*
    查詢某職工的總收入
    */
    create or replace function queryEmpSalary(empid in number)
    return number
   as
    pSal number; --定義變數儲存員工的工資
    pComm number; --定義變數儲存員工的獎金
   begin
   select sal,comm into psal,pcomm from emp where empno = empid;
   return psal*12+nvl(pcomm,0);
   end;
   /
 
Function created
 
l        函數的調用
 
SQL> declare
    v_sal number;
    begin
    v_sal:=queryEmpSalary(7934);
    dbms_output.put_line('salary is:'|| v_sal);
    end;
    /
 
salary is:15600
 
PL/SQL procedure successfully completed
 
SQL> begin
    dbms_output.put_line('salary is:'|| queryEmpSalary(7934));
    end;
    /
 
salary is:15600
 
PL/SQL procedure successfully completed