Oracle 學習筆記 18

PL/SQL子程式 包括函數和過程。這裡的函數指的是使用者自己定義的函數，和系統函數是不同的。子程式一般是完成特定功能的PL/SQL程式塊，並且具有一定的通用性，可以被不同的應用程式多次調用。Oracle提供可以把PL/SQL程式儲存在資料庫中，並可以再任何地方來運行它。這樣就叫做預存程序或者是函數。過程和函數的唯一區別就是函數總是向調用者返回資料，而過程則不返回資料。

函數

如果使用者要經常執行某些操作，並且需要返回特定的資料，那麼就可以將這些操作構造成一個函數。

可以使用SQL語句定義函數。

基本文法：

create or replace function fun_name(argument [in | out | in out ]datatype  ...)
return datatype 
is | as
       -- 需要定義的變數 ，記錄類型 ， 遊標
begin
       --函數的執行體
exception
       --處理出現的異常
end ;

其中，return datatype ， 是要返回資料的類型。IN參數標記表示傳遞給函數的值在函數執行中不改變；OUT標記表示一個值在函數進行計算並通過該參數傳遞給調用語句；IN OUT 標記表示傳遞給函數的值可以變化並傳遞給調用語句。

例：定義一個返回hello 的函數

create or replace function hello
return varchar2 
is
begin
       return 'Hello World';
end ;

執行 ，Function created ，函數建立成功。

函數的調用，與Oracle內建函數的調用相同，可以使用select hello from dual ;進行調用，也可以使用PL/SQL語句進行調用 ：

begin 

  dbms_output.put_line(hello);

end ;

例:建立帶參數的helloworld函數

create or replace function helloworld(str varchar2)
return varchar2
is
begin
       return 'Hello' || '_' ||str ;
end ;

函數的調用要在函數名稱後面加上參數,即：select helloworld('World') from dual ;使用Pl/sql的調用除了加上參數外與上面相同，不在贅述。

例：求一個部門中，員工的工資總數的函數

create or replace function get_sal(dept number)
return number 
is
       v_sum number(10) := 0 ;
       cursor sal_cursor is select sal from emp where deptno = dept ;
begin 
       for c in sal_cursor loop
           v_sum := v_sum + c.sal ;
       end loop ;
       return v_sum ;
end ;

預存程序

預存程序，可以被多個應用程式調用，也可以向預存程序傳遞參數，向預存程序傳回參數。

基本文法

create or replace procedure pro_name(argument [in | out | in out ]datatype  ...) 
is | as
       -- 需要定義的變數 ，記錄類型 ， 遊標
begin
       --函數的執行體
exception
       --處理出現的異常
end ;

例：使用預存程序，求部門的工資總和

create or replace procedure get_sal1(dept number ,sumsal out  number)
is
       cursor sal_cursor is select sal from emp where deptno = dept ;
begin 
       sumsal := 0 ;
       for c in sal_cursor loop
           sumsal := sumsal + c.sal ;
       end loop ;
       dbms_output.put_line(sumsal);
end ;

預存程序的調用：

declare 
  v_sum number(10) := 0 ;
begin 
  get_sal1(30 , v_sum);
end ;

調用格式：

CALL | EXCEUTE procedure_name(arg_list) ;

可以使用show error 命令來提示源碼的錯誤位置。使用user_error 資料字典來查看各預存程序的錯誤位置。

刪除過程和函數

刪除過程

文法如下：

DROP PROCEDURE[USER.]procedure_name ;

刪除函數

文法如下：

DROP FUNCTION [USER.]function_name ;