目錄

• 一、PLSQL基本結構
• 1、基本數據類型變量
• 1. 基本數據類型
• 2. 基本數據類型變量的定義方法
• 2、表達式
• 1. 數值表達式
• 2. 字符表達式
• 3. 關系表達式
• 4. 邏輯表達式
• 3、PLSQL函數
• 4、系統輸出打印
• 5、PL/SQL中的變量聲明
• 二、變量賦值方式
• 三、PLSQL復合類型
• 1、記錄類型record
• 2、表類型變量table
• 1. 定義一維表類型變量
• 2. 定義多維表類型變量
• 四、 %type和%rowtype
• 五、PLSQL變量的可見空間
• 六、PLSQL流程控制
• if判斷
• loop循環
• while循環
• for循環
• 七、PLSQL異常處理
• 1、聲明異常
• 2、拋出異常
• 3、處理異常
• 八、PLSQL游標和goto語句
• 如何遍歷游標fetch
• 九、Oracle存儲過程
• 存儲過程輸入參數作為變量進行條件判斷
• 利用存儲過程中定義的變量對入參的空值處理：
• 多個參數的存儲過程
• out輸出參數
• in out參數，既賦值又取值
• 對存儲過程入參賦缺省值
• 十、PLSQL中的function
• FUNCTION和PROCEDURE的區別
• 帶out參數的函數
• 帶in out 參數的函數

一、PLSQL基本結構

1、基本數據類型變量

1. 基本數據類型

• Number 數字型
• Int 整數型
• Pls_integer 整數型，產生溢出時出現錯誤
• Binary_integer 整數型，表示帶符號的整數
• Char 定長字符型，最大255個字符
• Varchar2 變長字符型，最大2000個字符
• Long 變長字符型，最長2GB
• Date 日期型
• Boolean 布爾型(TRUE、FALSE、NULL三者取一)

在PL/SQL中使用的數據類型和Oracle數據庫中使用的數據類型，有的含義是完全一致的，有的是有不同的含義的。

2. 基本數據類型變量的定義方法

變量名 類型標識符[not null]:=值;

declare 

     age number(3):=26; --長度為3，初始值為26 

begin 

     commit; 

end;

其中，定義常量的語法格式：

常量名 constant 類型標識符 [not null]:=值;

declare 

     pi constant number(9):=3.1415926;--為pi的數字型常量，長度為9，初始值為3.1415926 

begin 

     commit; 

end;

2、表達式

變量、常量經常需要組成各種表達式來進行運算，下面介紹在PL/SQL中常見表達式的運算規則。

1. 數值表達式

 PL/SQL程序中的數值表達式是由數值型常數、變量、函數和算術運算符組成的， 
可以使用的算術運算符包括+(加法)、-(減法)、*(乘法)、/(除法)和**(乘方)等。 
命令窗口中執行下列PL/SQL程序，該程序定義了名為result的整數型變量，計算的是10+3*4-20+5**2的值，理論結果應該是27。 

set serveroutput on 
 Declare 
    result integer; 
 begin 
    result:=10+3*4-20+5**2; 
    dbms_output.put_line("運算結果是："||to_char(result)); 
 end;

dbms_output.put_line函數輸出只能是字符串，因此利用to_char函數將數值型結果轉換為字符型。

2. 字符表達式

字符表達式由字符型常數、變量、函數和字符運算符組成，唯一可以使用的字符運算符就是連接運算符“||”。

3. 關系表達式

關系表達式由字符表達式或數值表達式與關系運算符組成，可以使用的關系運算符包括以下9種。 
< 小于 
> 大于 
= 等于(不是賦值運算符:=) 
like 類似于 
in 在……之中 
<= 小于等于 
>= 大于等于 
!= 不等于 或<> 
between 在……之間 
關系型表達式運算符兩邊的表達式的數據類型必須一致。

4. 邏輯表達式

邏輯表達式由邏輯常數、變量、函數和邏輯運算符組成，常見的邏輯運算符包括以下3種。 
NOT：邏輯非 
OR：邏輯或 
AND：邏輯與 
運算的優先次序為NOT、AND和OR。

3、PLSQL函數

PL/SQL程序中提供了很多函數供擴展功能，除了標準SQL語言的函數可以使用外，最常見的數據類型轉換函數有以下3個。 
To_char：將其他類型數據轉換為字符型。 
To_date：將其他類型數據轉換為日期型。 
To_number：將其他類型數據轉換為數值型。 
繼續追加中..

4、系統輸出打印

利用pl/sql在數據庫服務器端打印一句話： 
set serveroutput on--設置數據庫輸出，默認為關閉，每次重新打開窗口需要重新設置。

BEGIN 
     DBMS_OUTPUT.PUT_LINE("Hello PL/SQL"); 
END;

pl/sql程序中對大小寫不敏感(打印聲明的變量)

set serveroutput on 
DECLARE 
   v_char varchar2(20):="a"; 
   v_char1 varchar2(20):="b"; 
BEGIN 
   DBMS_OUTPUT.PUT_LINE(v_char); 
   DBMS_OUTPUT.PUT_LINE(v_char1); 
 END;

pl語句塊是pl/sql里最小的編程塊，其中可以再嵌套begin end

begin 
  dbms_output.put_line("Hello World"); 
  dbms_output.put_line("2*3="||(2*3)); 
  dbms_output.put_line("what""s"); 
 end;

5、PL/SQL中的變量聲明

所有變量必須在declare中聲明，程序中不允許聲明。 
沒有初始化的變量默認值為null，屏幕上null是看不見的，命名習慣：PL/SQL中變量一般以v_開頭 
(等同于存儲過程中as和begin區域的變量定義習慣)。 
注意number也能存小數，最長38位，所以以后建議整數都用binary_integer存。 
long是字符類型，boolean類型不能打印。 
標準變量類型：數字，字符，時間，布爾。

declare 
     v_number1 number; 
     v_number2 number(3,2) ; 
     v_number3 binary_integer :=1; 
     v_name varchar2(20) :="kettas"; 
     v_date date :=sysdate; 
     v_long long :="ni hao"; 
     v_b boolean := true; 
begin 
     if (v_number1 is null) then 
     dbms_output.put_line( "hello"); 
     end if; 
     dbms_output.put_line(v_number1); 
     dbms_output.put_line(v_number2); 
     dbms_output.put_line(v_number3); 
     dbms_output.put_line(v_name); 
     dbms_output.put_line(v_date); 
     dbms_output.put_line(v_long); 
   --dbms_output.put_line(v_b); --執行該句ORACLE提示“調用"PUT_LINE" 時參數個數或類型錯誤” 
end;

備注： 
關于聲明number(4,3)中括號中的兩個數字的意義，前面的數字叫精度，后面的叫刻度。 
刻度： 
當刻度為正數的時候，表示四舍五入到小數點后面的位數 
當刻度為負數的時候，表示四舍五入到小數點前面的位數 
精度： 
從數字的最前面不為零開始到刻度精確到的位置 
v_Number number(4,3):=123.12312 
1、按刻度進行四舍五入得到123.123 
2、確定刻度精確到的位置123123處，精度為6位(.符號不算) 
2、根據精度進行判斷6位(>4)精度上限值 --報錯不能存儲 
number(3,-3):=44445 
1、根據刻度－3進行四舍五入得到44000 
2、小數點向前移動3位44.此位置為刻度精確到的位置 
3、根據精度進行判斷2位(<3)精度上限值 --不報錯可存儲結果為44000

DECLARE 
  v_Number number(4,3):=123.12312;--實際精度6位大于上限精度值4位，提示“ORA-06502:PL/SQL: 數字或值錯誤 :  數值精度太高” 
BEGIN 
  DBMS_OUTPUT.PUT_LINE(v_Number); 
END;

DECLARE 
  v_Number number(7,3):=4555; --實際精度7位等于上限精度值，可以存儲 
BEGIN 
  DBMS_OUTPUT.PUT_LINE(v_Number); 
 END;

二、變量賦值方式

oracle中變量賦值方式是值拷貝而非引用

declare 
    v_number1 number:=100; 
    v_number2 number; 
begin 
    v_number2:=v_number1; 
    v_number1:=200; 
    dbms_output.put_line(v_number1); --200 
    dbms_output.put_line(v_number2); --100

end;

三、PLSQL復合類型

1、記錄類型record

record類型最常用，聲明的時候可以加not null，但必須給初始值，如果record類型一致可以相互賦值，如果類型不同，里面的字段恰好相同，不能互相賦值。引用記錄型變量的方法是“記錄變量名.基本類型變量名”。

declare 
    type t_first is record( 
 idnumber(3), 
 namevarchar2(20) 
    ); 
    v_first t_first; 
begin 
      v_first.id:=1; 
      v_first.name:="cheng"; 
      dbms_output.put_line(v_first.id); 
      dbms_output.put_line(v_first.name); 
 end;

record類型變量間賦值

declare 
    type t_first is record( 
      id number, 
      name varchar2(20)   
    ); 
    v_first t_first; 
    v_second t_first; 
begin 
    v_first.id:=1; 
    v_first.name:="susu"; 
    
    v_second:=v_first;--相互賦值 
    
    v_first.id:=2; 
    v_first.name:="kettas"; 
    dbms_output.put_line(v_first.id); 
    dbms_output.put_line(v_first.name); 
    dbms_output.put_line(v_second.id); 
    dbms_output.put_line(v_second.name); 
 end;

2、表類型變量table

語法如下： 
type 表類型 is table of 類型 index by binary_integer; 
表變量名 表類型; 
類型可以是前面的類型定義，index by binary_integer子句代表以符號整數為索引，這樣訪問表類型變量中的數據方法就是“表變量名(索引符號整數)”。table類型，相當于Java中的Map容器，就是一個可變長的數組，key(符號整數索引)必須是整數，可以是負數，value(類型)可以是標量，也可以是record類型。可以不按順序賦值，但必須先賦值后使用。 

1. 定義一維表類型變量

declare 
     type t_tb is table of varchar2(20)index by binary_integer; 
     v_tb t_tb; 
 begin 
   v_tb(100):="hello"; 
   v_tb(98):="world"; 
   dbms_output.put_line(v_tb(100)); 
   dbms_output.put_line(v_tb(98)); 
 end;

類型為record的表類型變量

declare 
     type t_rd is record(id number,namevarchar2(20)); 
     type t_tb is table of t_rd index bybinary_integer; 
     v_tb2 t_tb; 
 begin 
     v_tb2(100).id:=1; 
     v_tb2(100).name:="hello"; 
     --dbms_output.put_line(v_tb2(100).id); 
    --dbms_output.put_line(v_tb2(100).name); 
    dbms_output.put_line(v_tb2(100).id||"    "||v_tb2(100).name); 
 end;

2. 定義多維表類型變量

該程序定義了名為tabletype1的多維表類型，相當于多維數組，table1是多維表類型變量，將數據表tempuser.testtable中recordnumber為60的記錄提取出來 
存放在table1中并顯示。

declare 
   type tabletype1 is table of testtable%rowtypeindex by binary_integer; 
   table1 tabletype1; 
 begin 
    select * into table1(60) fromtempuser.testtable where recordnumber=60; 
   dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate); 
 end;

備注：在定義好的表類型變量里，可以使用count、delete、first、last、next、exists和prior等屬性進行操作，使用方法為“表變量名.屬性”，返回的是數字。

set serveroutput on 
declare 
     type tabletype1 is table ofvarchar2(9) index by binary_integer; 
     table1 tabletype1; 
begin 
     table1(1):="成都市"; 
     table1(2):="北京市"; 
     table1(3):="青島市"; 
     dbms_output.put_line("總記錄數："||to_char(table1.count)); 
     dbms_output.put_line("第一條記錄："||table1.first); 
     dbms_output.put_line("最后條記錄："||table1.last); 
     dbms_output.put_line("第二條的前一條記錄："||table1.prior(2)); 
     dbms_output.put_line("第二條的后一條記錄："||table1.next(2)); 
 end;

四、 %type和%rowtype

使用%type定義變量，為了讓PL/SQL中變量的類型和數據表中的字段的數據類型一致，Oracle 9i提供了%type定義方法。這樣當數據表的字段類型修改后，PL/SQL程序中相應變量的類型也自動修改。

create table student( 
    id number, 
    name varchar2(20), 
    age number(3,0) 
 ); 
 insert into student(id,name,age) values(1,"susu",23); 
--查找一個字段的變量 
declare 
    v_name varchar2(20); 
    v_name2 student.name%type; 
 begin 
    select name into v_name2 from studentwhere rownum=1; 
    dbms_output.put_line(v_name2); 
 end; 
--查找多個字段的變量 
declare 
     v_id student.id%type; 
     v_name student.name%type; 
     v_age student.age%type; 
 begin 
   select id,name,age into v_id,v_name,v_age fromstudent where rownum=1; 
   dbms_output.put_line(v_id||" "||v_name||"  "||v_age); 
 end; 
--查找一個類型的變量，推薦用* 
declare 
    v_student student%rowtype; 
 begin 
    select * into v_student from student whererownum=1; 
    dbms_output.put_line(v_student.id||" "||v_student.name||"  "||v_student.age); 
 end; 
--也可以按字段查找，但是字段順序必須一樣，不推薦這樣做 
declare 
    v_student student%rowtype; 
 begin 
  select id,name,age into v_student from student whererownum=1; 
  dbms_output.put_line(v_student.id||" "||v_student.name||"  "||v_student.age); 
 end; 
 declare 
    v_student student%rowtype; 
 begin 
  select id,name,age intov_student.id,v_student.name,v_student.age from student where id=1; 
  --select * intov_student.id,v_student.name,v_student.age from student where id=1; 
  dbms_output.put_line(); 
 end;

備注：insert，update，delete，select都可以，createtable，droptable不行。DPL，DML，和流程控制語句可以在pl/sql里用，但DDL語句不行。

declare 
    v_name student.name%type:="wang"; 
 begin 
    insert into student(id,name,age)values(2,v_name,26); 
 end;

begin 
    insert into student(id,name,age)values(5,"hehe",25); 
 end; 
 declare 
    v_name student.name%type:="hexian"; 
 begin 
    update student set name=v_name where id=1; 
 end; 
 begin 
    update student set name="qinaide" whereid=2; 
 end;

五、PLSQL變量的可見空間

變量的作用域和可見性，變量的作用域為變量申明開始到當前語句塊結束。

當外部過程和內嵌過程定義了相同名字的變量的時候，在內嵌過程中如果直接寫這個變量名是沒有辦法訪問外部過程的變量的，可以通過給外部過程定義一個名字<<outername>>,通過outername變量名來訪問外部過程的變量(待測試..)。

declare 
 v_i1binary_integer:=1; 
begin 
      declare 
 v_i2binary_integer:=2; 
      begin 
dbms_output.put_line(v_i1); 
dbms_output.put_line(v_i2); 
      end; 
   dbms_output.put_line(v_i1); 
--dbms_output.put_line(v_i2);  解開后執行Oracle會提示“必須說明標識符"V_I2"” 
end;

六、PLSQL流程控制

if判斷

declare 
    v_b boolean:=true; 
begin if v_b then 
      dbms_output.put_line("ok"); 
   end if; 
 end; 
if else判斷 
declare 
     v_b boolean:=true; 
 begin 
  if v_b then 
     dbms_output.put_line("ok"); 
  else 
     dbms_output.put_line("false"); 
  end if; 
end; 
 if elsif else判斷 
declare 
     v_name varchar2(20):="cheng"; 
 begin 
  if v_name="0701" then 
     dbms_output.put_line("0701"); 
  elsif v_name="cheng" then 
     dbms_output.put_line("cheng"); 
  else 
     dbms_output.put_line("false"); 
  end if; 
 end;

loop循環

注意推出exit是推出循環，而不是推出整個代碼塊

declare 
    v_i binary_integer:=0; 
 begin 
  loop 
      if v_i>10 then 
 exit; 
      end if; 
      v_i:=v_i+1; 
      dbms_output.put_line("hehe"); 
  end loop; 
      dbms_output.put_line("over"); 
 end;

loop簡化寫法

declare 
    v_i binary_integer :=0; 
 begin 
  loop 
      exit when v_i>10; 
      v_i :=v_i+1; 
      dbms_output.put_line("hehe"); 
  end loop; 
      dbms_output.put_line("over"); 
 end;

while循環

declare 
    v_i binary_integer:=0; 
 begin 
    while v_i<10 loop 
 dbms_output.put_line("hello"||v_i ); 
 v_i:=v_i+1; 
    end loop; 
    dbms_output.put_line("over"); 
 end;

for循環

注意不需要聲明變量

begin 
  for v_i in 0..10 loop 
     dbms_output.put_line("hello"||v_i); 
  end loop; 
      dbms_output.put_line("over"); 
end;

七、PLSQL異常處理

1、聲明異常

異常名 EXCEPTION；

2、拋出異常

RAISE 異常名

3、處理異常

拋出異常后的邏輯代碼不會被繼續執行 
異常的定義使用

begin 
       dbms_output.put_line(1/0); 
 exception 
 when othersthen 
    dbms_output.put_line("error"); 
 end; 
 declare 
e_myException exception; 
 begin 
dbms_output.put_line("hello"); 
 raisee_myException; --raise拋出異常,用此關鍵字，拋出后轉到自定義的e_myException ，執行其里面的putline函數后，再跳到end處，結束PL/SQL塊，raise接下面的2句不會繼續執行。 
dbms_output.put_line("world"); 
dbms_output.put_line(1/0); 
 exception 
 whene_myException then 
    dbms_output.put_line(sqlcode); --當前會話執行狀態，錯誤編碼 
    dbms_output.put_line(sqlerrm); --當前錯誤信息 
    dbms_output.put_line("my error"); 
 when othersthen 
    dbms_output.put_line("error"); 
 end;

八、PLSQL游標和goto語句

備注：下面提到的游標為靜態cursor，包括顯示和隱式。 
游標，從declare、open、fetch、close是一個完整的生命旅程。當然了一個這樣的游標是可以被多次open進行使用的，顯式cursor是靜態cursor，她的作用域是全局的，但也必須明白，靜態cursor也只有pl/sql代碼才可以使用它。靜態游標變量是在定義時就必須指定SQL語句。 
cursor 游標(結果集)用于提取多行數據，定義后不會有數據，使用后才有。一旦游標被打開，就無法再次打開(可以先關閉，再打開)。

declare 
       cursor c_studentis  select * from book; 
 begin 
       open c_student; 
       close c_student; 
 end;

第二種游標的定義方式，用變量控制結果集的數量。

declare 
       v_id binary_integer; 
       cursor c_student isselect * from book where id>v_id; 
 begin 
       v_id:=10; 
       open c_student; 
       close c_student; 
 end;

第三種游標的定義方式，帶參數的游標，用的最多。

declare 
       cursor c_student(v_idbinary_integer) is select * from book where id>v_id; 
 begin 
       open c_student(10); 
       close c_student; 
 end;

游標的使用，一定別忘了關游標。

declare 
       v_student book%rowtype; 
       cursor c_student(v_idbinary_integer) is select * from book where id>v_id; 
 begin 
       open c_student(10); 
       fetch c_student intov_student; 
       close c_student; 
       dbms_output.put_line(v_student.name); 
 end;

如何遍歷游標fetch

游標的屬性 %found，%notfound,%isopen,%rowcount。

• %found:若前面的fetch語句返回一行數據，則%found返回true，如果對未打開的游標使用則報ORA-1001異常。
• %notfound，與%found行為相反。
• %isopen，判斷游標是否打開。
• %rowcount:當前游標的指針位移量，到目前位置游標所檢索的數據行的個數，若未打開就引用，返回ORA-1001。

注： 
no_data_found和%notfound的用法是有區別的，小結如下 
1)SELECT. . . INTO 語句觸發 no_data_found； 
2)當一個顯式光標(靜態和動態)的 where 子句未找到時觸發 %notfound； 
3)當UPDATE或DELETE語句的where 子句未找到時觸發 sql%notfound； 
4)在光標的提取(Fetch)循環中要用 %notfound 或%found 來確定循環的退出條件，不要用no_data_found。 
下面是幾個實例：

create table BOOK 
 ( 
 ID       VARCHAR2(10) not null, 
 BOOKNAME VARCHAR2(10) not null, 
 PRICE    VARCHAR2(10) not null, 
 CID      VARCHAR2(10) not null 
 ); 
--insert 
 create or replace procedure say_hello( 
 i_name in varchar2, 
o_result_msg out varchar2 
 ) 
  as 
  v_price varchar2(100); 
  e_myException exception; 
 begin 
     insert into book(id,bookname,price)values (1,2,3); 
     o_result_msg := "success"; 
  exception 
     when others then 
 rollback; 
 o_result_msg := substr(sqlerrm, 1, 200); 
  end; 
--update or delete 
 create or replace procedure say_hello( 
 i_name in varchar2, 
 o_result_msg out varchar2 
 ) 
  as 
  v_price varchar2(100); 
  e_myException exception; 
 begin 
     update book set price = "55" wherebookname = i_name; 
     delete from book where bookname =i_name; 
     if sql%notfound then 
raisee_myException; 
     end if; 
     /* 
 if sql%rowcount = 0 then--寫法2 
raisee_myException; 
     end if; 
 */ 
     o_result_msg := "success"; 
  exception 
     when e_myException then 
 rollback; 
 o_result_msg := "update or delete dail"; 
  end; 
--select 
 create or replace procedure say_hello( 
 i_name in varchar2, 
 o_result_msg out varchar2 
 ) 
  as 
  v_price varchar2(100); 
  e_myException exception; 
 begin 
     select price into v_price from bookwhere bookname = i_name; 
     o_result_msg := "success"; 
  exception 
     when no_data_found then 
 rollback; 
 o_result_msg := "select into dail"; 
  end;

loop方式遍歷游標

declare 
       v_bookname varchar2(100); 
       cursor c_book(i_idnumber) is select bookname from book where id = i_id; 
 begin 
     Open  c_book(i_id); 
     Loop 
 Fetch c_bookinto v_bookname; 
 exit whenc_student%notfound; 
  update book set price = "33" where bookname = v_bookname; 
     End Loop; 
     Close c_book; 
 end;

或

declare 
       v_bookname varchar2(100); 
       cursor c_book(i_idnumber) is select bookname from book where id = i_id; 
 begin 
     Open  c_book(i_id); 
       Fetch c_book intov_bookname; 
       While c_book%Found 
       Loop 
  update book set price = "33" where bookname = v_bookname; 
       Fetch  c_book intov_bookname; 
       End Loop; 
     Close c_book; 
 end;

while循環遍歷游標，注意，第一次游標剛打開就fetch，%found為null，進不去循環 
解決方法：while nvl(c_student%found,true) loop

declare 
      v_bookname  varchar2(100); 
      cursor c_book(i_id number) isselect bookname from book where id = i_id; 
 begin 
      Open  c_book(i_id); 
      while nvl(c_book%found,true)--或這種寫法：while c_book%found is null or c_book%foundloop    
  Fetchc_book into v_bookname; 
  updatebook set price = "33" where bookname = v_bookname; 
      End Loop; 
      Close c_book; 
 end;

for循環遍歷，最簡單，用的最多，不需要 聲明v_student,Open和Close游標和fetch操作(不用打開游標和關閉游標，實現遍歷游標最高效方式)

declare 
      cursor c_book(i_id number) isselect bookname from book where id = i_id; 
begin 
      for cur in c_book(i_id) --直接將入參i_id傳入cursor即可 
      loop 
  updatebook set price = "53" where bookname = cur.bookname; 
      end loop; 
 end;

goto例子，一般不推薦使用goto，會使程序結構變亂

declare 
      i number:=0; 
 begin 
      if i=0 then 
  gotohello; 
      end if; 
      <<hello>> 
      begin 
 dbms_output.put_line("hello"); 
  gotoover; 
      end; 
      <<world>> 
      begin 
 dbms_output.put_line("world"); 
  gotoover; 
      end; 
      <<over>> 
 dbms_output.put_line("over"); 
  end;

九、Oracle存儲過程

在談存儲過程書寫中的一些規則時，先看一下執行它的規則，在命令窗口執行存儲過程sp_get_product_prompt

set serveroutput on 
  var ret1 varchar2(200); 
  var ret2 varchar2(200); 
  exec sp_get_product_prompt(83,:ret1,:ret2); --或execute 
  print ret1; 
  print ret2; 
  或 
  set serveroutput on 
  declare 
     ret1 varchar2(200); 
     ret2 varchar2(200); 
  begin 
     sp_get_product_prompt(83,ret1,ret2); 
     dbms_output.put_line(ret1); 
     dbms_output.put_line(ret2); 
  end;

存儲過程入參，不論類型，缺省情況下值都為null,入參和出參不能有長度,其中關鍵字as可以替換成is,存儲過程中變量聲明在as和begin之間，同時，存儲過程中可以再調用其它的存儲過程，如果要保證存儲過程之間的事務處理不受影響，可以定義為自治事務。

create or replace procedure say_hello( 
    v_name in varchar2, 
    v_flag number, 
    o_ret out number 
  ) 
  as 
  begin 
    if v_name is null and v_flag is null then --v_name和v_flag都等于null 
       o_ret := 10; 
    else 
o_ret := 100; 
    end if; 
  end;

對于入參為null情況下給予缺省值

create or replace procedure say_hello( 
    i_name in varchar2, 
    i_flag number, 
    o_ret out number 
  ) 
  as 
    v_name  varchar2(100); 
  begin 
    if i_name is null then 
       v_name := "0"; 
    else 
       v_name := i_name; 
    end if; 
    insert into phone(..,wname..,)values(..,v_name,..);  
  
  end;

或直接在insert語句中調用nvl函數賦缺省值

insert intophone(..,wname..,) values(..,nvl(v_name," "),..); ----如果將" "寫成"",則insert進來的v_name值還是為""等價于null值

帶一個參數的存儲過程 
輸入參數in，輸入參數不能進行:=賦值，但可以將它賦給as后面定義的變量； 
輸入參數in，可以作為變量進行條件判斷； 
默認不寫就是in； 
存儲過程沒有重載，這個有參的say_hello會替代已經存在的無參say_hello。

create or replace procedure say_hello(v_name in varchar2) 
  as 
  begin 
     --v_name:="a"; --存儲過程入參v_name不能做為賦值目標 
    dbms_output.put_line("hello "||v_name); 
  end;

存儲過程輸入參數作為變量進行條件判斷

create or replace procedure say_hello( 
     i_opFlag in number 
  ) 
  as 
     v_name varchar2(100); 
  begin 
     if i_opFlag = 1 then 
v_name :="0"; 
     else 
v_name :="haha"; 
     end if; 
     dbms_output.put_line("hello"||v_name); 
  end;

利用存儲過程中定義的變量對入參的空值處理：

create or replace procedure say_hello( 
     i_name in varchar2 
  ) 
  as 
     v_name varchar2(100); 
  begin 
     if i_name is null then 
v_name :="0"; 
     else 
v_name :=i_name;--將入賦值給定義變量 
     end if; 
     dbms_output.put_line("hello"||v_name); 
  end;

多個參數的存儲過程

create or replace procedure say_hello( 
     v_first_name in varchar2, 
     v_last_name in varchar2) 
  as 
  begin 
     dbms_output.put_line("hello"||v_first_name||"."||v_last_name); 
  end;

out輸出參數

用于利用存儲過程給一個或多個變量賦值，類似于返回值

create or replaceprocedure say_hello( 
     v_name in varchar2, 
     v_content out varchar2 
  ) 
  begin 
     v_content:="hello"||v_name; 
  end;

調用：

declare 
     v_con varchar2(200); 
     v_in varchar2(20):="wang"; 
  begin 
     say_hello(v_in,v_con); 
     dbms_output.put_line(v_con); 
  end;

in out參數，既賦值又取值

create or replace procedure say_hello(v_name in outvarchar2) 
  as 
  begin 
     v_name:="hi "||v_name; 
  end;

調用：

declare 
     v_inout varchar2(20):="wangsu"; 
  begin 
     say_hello(v_inout); 
     dbms_output.put_line(v_inout); 
  end;

對存儲過程入參賦缺省值

create or replace procedure say_hello( 
     v_name varchar2 default "susu", 
     v_content varchar2 default "hello" 
  ) 
  as 
  begin 
     dbms_output.put_line(v_name||""||v_content); 
  end;

調用：(用指明形參名的方式調用更好)

begin 
     say_hello(); 
  end;

或

begin 
     say_hello("cheng"); 
  end;

或

begin 
  say_hello(v_name=>"cheng"); 
 end;

十、PLSQL中的function

FUNCTION和PROCEDURE的區別

1、函數有返回值，過程沒有 
2、函數調用在一個表達式中，過程則是作為pl/sql程序的一個語句 
過程和函數都以編譯后的形式存放在數據庫中，函數可以沒有參數也可以有多個參數并有一個返回值。

過程有零個或多個參數，沒有返回值。

函數和過程都可以通過參數列表接收或返回零個或多個值，函數和過程的主要區別不在于返回值，而在于他們的調用方式，過程是作為一個獨立執行語句調用的，函數以合法的表達式的方式調用。

create or replace function func(v_name in varchar2) 
return varchar2 
is 
begin 
    return(v_name||" hello"); 
 end;

調用：

declare 
    v_name varchar2(20); 
begin 
    v_name:=func("cheng"); 
    dbms_output.put_line(v_name); 
 end;

帶out參數的函數

create or replace function func( 
    v_name in varchar2, 
    v_content out varchar2 
) 
return varchar2 
is 
begin 
    v_content:=v_name||" hello"; 
    return v_content; 
 end;

調用：

declare 
    v_name varchar2(20); 
    v_name1 varchar2(20); 
begin 
    v_name1:=func("susu",v_name);--返回v_name值 
    dbms_output.put_line(v_name1);--打印func結果 
    dbms_output.put_line(v_name);--打印v_name結果 
end;

帶in out 參數的函數

create or replace function func( 
    v_name in out varchar2) 
return varchar2 
is 
begin 
    v_name:=v_name||" hello"; 
    return "cheng"; 
 end;

調用：

declare 
    v_inout varchar2(20):="world"; 
    v_ret varchar2(20); 
begin 
    v_ret:=func(v_inout);--返回調用v_inout值(作為出參) 
    dbms_output.put_line(v_ret);--打印func結果    
    dbms_output.put_line(v_inout);--返回v_name結果 
end;

到此這篇關于Oracle中PL/SQL用法的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持。