PL/SQL的預存程序和函數

預存程序概述

預存程序是子程序的一種類型，能夠完成一些任務，作為schema物件儲存於資料庫。是一個有名字的PL/SQL程式碼區塊，支援接收或不接受參數，同時也支援參數輸出。一個預存程序通常包含定義部分，執行部分，Exception部分，可以被其他子程序調用，也可以重複使用。
過程定義
CREATE ［OR REPLACE］PROCEDURE procedure_name
[(argument_name [IN | OUT | IN OUT] argument_type)]
AS | IS
BEGIN
    procedure_body;
END [procedure_name];
存儲過程中參數的類型
IN ：表示是一個輸入參數，可以指定預設值。如省略參數類型，則缺省為in類型
OUT：表示是一個輸出參數
IN OUT：既可以作為一個輸入參數，也可以作為一個輸出參數來輸出結果
過程呼叫
EXECUTE ｜CALL procedure_name [(argument_list )]

例：定義一個過程，以JOB為參數，查詢該JOB的最高工資、最低工資、平均工資。

create or replace procedure query_sal(v_job in emp.job%type)
   as
   v_min_sal emp.sal% select min(sal) into v_min_sal from emp where job = v_job;
   select max(sal) into v_max_sal from emp where job = v_job;
   select avg(sal) into v_avg_sal from from adhejin. um salary is ' || v_min_sal);
   dbms_output.put_line('This job is maximum salary is ' || v_max_sal);
   dbms_output.put_line('This job is average cept found then
dbms_output.put_line('Not Record Found');
end;

SQL> set serveroutput on
SQL> exec query_sal('SALESMAN');
This job is minimum salary is 12505 . job is average salary is 1400
PL/SQL procedure successfully completed.

參數及其傳遞方式

在建立過程時，傳遞的參數為可選項，如果省略參數選項，則過程為無參過程（定義時不指定參數，呼叫時也不需要參數）。若指定參數選項，則過程為有參過程（定義時需指定參數名字、模式、資料類型，調時時需給予對應的參數值），定義時的參數，稱為形參，呼叫時的參數稱為實參。

無參照過程
create or replace procedure display_systime
as
begin
  dbms_output.put_line('Current Time is ' || sysdate);

end

有參過程
定義時需要指定參數的名字、模式、資料型別

例：定義一個新增記錄的過程（全部為輸入參數）

create or replace procedure add_emp
(
v_no in emp.empno%type,
v_name in emp.ename%type,
v_dept in emp.deptno%type default 20   --缺省的部門號碼
)
as
begin

  insert into emp (empno,ename,deptno) values(v_.novalue);

exception
  when dup_val_on_index then
    dbms_output.put_line('Record Exists');
end ;

SQL> exec add_emp(7369, on.輸入員工編號，修改記錄，再回傳修改後的結果（姓名和薪資）。

create or replace procedure ed_emp
 (
 v_no in emp.empno%type,    --定義了一個in類型，二個out類型的參數
 v_name out emp.ename%%),typeename%
 as
 begin
   update emp set sal = sal + 100 where empno = v_no;
   select ename,sal into v_name,v_sal from empere    select ename,sal into v_name,v_sal from x    dbms_output.put_line('Not Data Found' );
 end;

 /

Procedure created.

SQL>VARIABLE t_name varchar2(20);

SQL>VARIABLE t_sal number;

SQL> exec ed_emp(7369:o); completed.

SQL> print t_name
T_NAME
---------------------------------------- ------------------------------------------
SMITH
SQL> print t_sal
     T_SAL
----------
       900
例：IN OUT型別參數的使用

create or replace procedure comp
(num1 in out number,num2 in out number)
as
  v1 number;
  v2 number;
beginnum v1 := num1 + 2 v1;
  num2 := v2;
end;
SQL> var v1 number
SQL> var v2 number
SQL> exec :v1 := 3
PL/SQL procedure successf complepleted. PL/SQL procedure successfully completed.
SQL> exec comp(:v1,:v2);
SQL> print v1 v2
        V1 ---- ----
        15
SQL> exec comp(:v1,:v2);
PL/SQL procedure successfully completed.
SQL> print v1 v21  3
        V2
----------
       120

可以看到in out類型的參數既作為輸入參數又作為輸出參數。

儲存程序參數的傳遞方式：
按位置傳遞：
實參依序將值傳給形參
EXECUTE ED_EMP(7900,:t_name,:t_sal);
EXECUTE ED_EMP(8000,'TEST2' ;
按名字傳遞
EXECUTE ED_EMP(v_name=>'ABCDE',v_dept=>10,v_no=>8003); 
混合傳遞
EXECUTE ED_EMP(8005,v_deptept=20,dept

製程管理
檢視系統流程資訊
DBA_OBJECTS
DBA_PROCEDURES
DBA_SOURCE
使用desc procedure_name 檢視儲存程序的參數資訊
_        Type                    In/Out Default?
 ------- ----------------------- ----------------------- ---- -- --------
 V_NO                           NUMBER                      VARCHAR2(10)              NUMBER(7,2)             OUT 
從dba_objects取得預存程序的資訊
SQL>select owner,object_name,objects取得預存程序的資訊
SQL>select owner,object_name,object_type ,status from dba_objects where object_name = 'ED_EMP';
OWNER                   STATUS
------------------------------ --- ----------------- --------------- -------
SCOTT                          ED_EMP   select object_name, procedure_name,interface,authid from user_procedures;
OBJECT_NAME          PROCEDURE_NAME        -------------- --- ------------
DISPLAY_SAL                                                              text from user_source where name='ED_EMP';
LINE        TEXT
---------- -------------------------- -------------------------------------------------- ----
  1         PROCEDURE ed_emp
  2               (   
  4               v_name OUT emp.ename%TYPE,
  5              )
  7               AS
  8      9                 UPDATE emp SET          UPDATE emp SET empno=v_no;
 11               EXCEPTION
 12                      DBMS_OUTPUT.PUT_LINE('NOT FOUND RECORD! ');
 14               END ed_emp;
檢視錯誤訊息
SHOW ERRORS

函數概述

函數通常用於傳回特定的資料。其實質是一個有名字的PL/SQL區塊，作為一個schema物件儲存於資料庫，可以被重複執行。函數通常被當作一個表達式來呼叫或預存程序的一個參數，具有傳回值。
一、建立函數的語法
CREATE [ OR REPLACE ] FUNCTION function_name
    (argument1 [mode1] datatype1,
     argument2 [mode2] datetype2,
     argument2 [mode2] datetype2, oc  [local_variable_declarations;...]
BEGIN
    --actions;
    RETURN expression;
END [function_name];
建立函數的幾點注意事項
1.指定參數資料類型時(argument)，不能指定其長度必須指定其長度從句，函數體內至少要包含一條return語句
3.可以指定in參數，也可以指定out參數，以及in out 參數
4.可以為參數指定缺省值。指定預設值時使用default關鍵字。如arg1 varchar2 default 'SCOTT'
使用函數的優點:
1.增加了程式碼的彈性，可以完成一些較為複雜的任務，以及僅透過SQL無法完成的任務
2.可以直接將函式使用到where子句中來過濾資料
3.可以作為預存程序的參數使用，是預存程序的補充

建立函數
建立不帶參數的函數

create or replace function get_user

return  varchar2
as

(as 20);

begin
  select username into v_user from user_users;
  return v_user;
end;

使用全域變數接收函數的回傳值

SQL> var v1 必須;

PL/SQL procedure successfully completed.

SQL> print v1

V1

--------------------------------- -----
SCOTT
使用本地變數接收函數的回傳值

SQL> declare user_name varchar2(20);               user();
  4   dbms_output.put_line('Current User: ' || user_name);
  5  end;
  6  /

Current User: SCOTT

PL/SQL procedure successfully completed.

SQL 語句中直接呼叫函數lectUp針對

con分別看--------------------------------
SCOTT
使用dbms_output呼叫函數(此呼叫作為預存程序的一個參數來進行呼叫)  
SQL> set serveroutput on;

SQL> exec dbms_output.put_line('Current user: '||get_user);

Current user: SCOTT  

,place in varchar2)
return number
as
  new_sal emp.sal%type;
begin
  select sal * 1.2 into new_sal from emp 
  where upper(ename) = uppernameename); _found then
raise_application_error(-20000,'Current Employee does not exists');

end;

 

SQL>  select sal,raise_sal('SCOTT') from
SQL>  select sal,raise_sal('SCOTT') from
SQL>  select sal,raise_sal('SCOTT') from
SQL>  select sal.
---------- ------------------
      3000               3600

 

SQL> ='SCOTT';
 select sal,raise_sal('SCOTTT') from emp where ename='SCOTT'

            *

ERROR at line 1:       *

ERROR at line 1:

ORA-2000ee TT. RAISE_SAL", line 11

建立帶有out參數的函數
create or replace function get_info

(name varchar2,titile out varchar2)

return varchar2

asgin  deptapt. d.dname into titile,deptname

  from emp e,dept d
  where e.deptno = d.deptno
  and upper(e.ename) = upper(name);
  return deptname; or( -20000,'Current Employee does not exists');
end;
/

注意對於使用out參數的函數，不能使用SQL語句來呼叫。而必須定義變數接收out參數和函數的回傳值。
呼叫如下

SQL> var job varchar2(20);
SQL>; var dname varchar2(20);
SQL>; exec :dname := get_info('scott',:job);
PL/SQL 過程成功完成。
SQL>列印dname 作業
DNAME
----------------------------------------- --------------- -----
研究
工作
------------------------- --------------- ---------
分析師
SQL> select get_info('scott') from Dual
  2  ;
select get_info('scott') from Dual
       *
第1 行出現錯誤：
ORA-06553: PLS-306: 呼叫「GET_INFO」時參數數量或型別錯誤'

建立帶輸入輸出參數的函數
(Ncomp ,num2 in out number)
回傳數字
as
  v_result number(6);
  v_remainder number;
begin
  v_result := num1 * num2;
return v_result;
異常
 當零除時
    raise_application_error(-20000,'除以零');
end;
/

SQL> var n1 數字
end;

/

SQL> var n1 數字
end;
/

SQL> var n1 數字;
PL/SQL 過程成功完成。
SQL> exec :n1 := comp(16,:n2);
PL/SQL 程序已成功完成。
SQL> print n1 n2
        N1
----------
       160
      的調用（其特定呼叫方法）參考上面的示範)
a.使用全域變數接收函數的回傳值
b.使用本地變數接受函數的回傳值
c.在SQL語句中直接呼叫函數
d.使用dbms_output呼叫函數
註：函數在呼叫的時候需要按位置指定參數，沒有預存程序參數傳遞必須具有執行函數的權限
2.函數在SQL中呼叫的主要場合
由於函數必須要傳回數據，因此只能作為表達式的一部分呼叫另外。函數可以在 SQL 語句的以下部分呼叫
a。選擇指令的選擇清單或子查詢中
b。 條件式where,having子句中
c.連接方式，開始方式，排序方式以及分組方式子句中
d。在
f 中插入指令的值子句。 update指令的set子句中
3.函數在SQL中呼叫的限制
a. SQL語句中只能呼叫儲存在伺服器端的函數，而不能呼叫儲存在客戶端的函數
b. SQL語句中呼叫的函數只能帶輸入參數IN，而不能帶輸出參數OUT以及輸入輸出參數IN OUT
c. SQL語句中調用的函數只能使用SQL支援的標準資料類型，不能使用PL/SQL特有的類型，如boolean,table ,record等
d。 SQL語句中呼叫的函式不能包含插入、更新、刪除語句
建立一張表格tb_emp

SQL> create table tb_emp as select * from emp;

建立表。

建立一個函數，用於刪除tb_emp表中指定的empno號的員工信息，並返回其薪資

SQL>;創建或替換函數del_emp
  2 （無數字）
  3   .sal%type;
  6  begin
  7    從emp 選擇sal 到v_sal，其中empno = no;
  8    刪除tb_emp，其中empno = no;
  8    刪除tb_emp，其中empno = no;
  8    刪除tb 
函數創建。

使用SQL語句呼叫時，收到了錯誤訊息，在內部查詢內無法完成DML操作
SQL>; select del_emp(7788) from Dual;
select del_emp(7788) from Dual

     

ORA-14551：無法在查詢內執行DML 操作
ORA-06512：位於“SCOTT.DEL_EMP”，行8

使用exec執行時函數成功執行
SQL> var n1 數字
SQL> exec :n11 del_emp(7788);
PL/SQL 過程成功完成。

SQL> print n1

        N1
----------
      3000

函數的管理
函數使用了與儲存流程視圖中的相關資訊

函數的管理

CE

查看函數的來源碼

SQL>select line,text from user_source where name='DEL_EMP' order by line

      LINE TEXT
---------------------------------------------- ------------------------
         1 function del_emp
         2 (no number)
          5   v_sal emp.sal%type;
         6 begin
         7   select sal into v_sal from emp where empno = no        9   return v_sal;
        10 end;
10 rows selected.

查看函數的參數資訊

SQL> desc del_emp;

FUNCTION del_emp RETURNS NUMBER

 Argument Name                   -------------- -- --------------------- ------ --------

 NO                            

儲存過程函數
----------------------------------                               -------------- -----------------
不能被當作表達式呼叫                                                 聲明頭部關鍵字為function
聲明頭部不包含return關鍵字來描述回傳類型         頭部必須包含return關鍵字,且PL/SQL區塊中至少包含一個有效的return語句
可以透過out,in out回傳零個或多個值      語句與一個與一個語句一起傳回與一個接見頭部聲明中類型一致的值,也可使用in,in out回傳值

SQL語句中不可調用儲存過程                      多用於特定的數據如選擇等