Oracle PL/SQL (1) - 編程基礎(chǔ)知識(shí)

PL/SQL概述

Oracle提供了在數(shù)據(jù)庫(kù)服務(wù)器端編程的語(yǔ)言，即PL/SQL。而SQL只是一種聲明式語(yǔ)言，是非過(guò)程性的，語(yǔ)句之間相互獨(dú)立。在實(shí)際工作中，許多事務(wù)處理應(yīng)用都是過(guò)程性的，前后語(yǔ)句之間存在一定關(guān)聯(lián)。
PL/SQL是過(guò)程化SQL，可以彌補(bǔ)SQL語(yǔ)句的不足?？梢跃帉?xiě)過(guò)程、函數(shù)、觸發(fā)器，而這些存放在oracle數(shù)據(jù)庫(kù)中可以在java程序中調(diào)用就節(jié)省了傳輸量和解析時(shí)間，同時(shí)提高了性能。

在Oracle 中使用PL/SQL來(lái)擴(kuò)展SQL的功能，PL/SQL可以通過(guò)IF和LOOP語(yǔ)句控制程序的執(zhí)行流程；可以定義變量以便在語(yǔ)句之間傳遞數(shù)據(jù)信息。PL/SQL是對(duì)標(biāo)準(zhǔn)SQL語(yǔ)言的擴(kuò)展， SQL語(yǔ)句完全可以嵌套在PL/SQL程序代碼中，將SQL的數(shù)據(jù)處理能力和PL/SQL的過(guò)程處理能力結(jié)合在一起。使得SQL能夠更加的靈活，功能更加強(qiáng)大，效率更高

本文主要講述PL/SQL的基本語(yǔ)法、條件判斷語(yǔ)句、循環(huán)語(yǔ)句。

1、PL/SQL變量的數(shù)據(jù)類(lèi)型

number：數(shù)字類(lèi)型，可以代表整數(shù)和浮點(diǎn)數(shù)。
int：整數(shù)型。
pls_integer：整數(shù)型，計(jì)算速度快（直接由cpu執(zhí)行），占用較少的存儲(chǔ)空間。但產(chǎn)生溢出時(shí)將觸發(fā)異常。
binary_integer：帶符號(hào)的整數(shù)型，不會(huì)出現(xiàn)溢出，但計(jì)算速度較慢（由oracle模擬執(zhí)行）。
char：定長(zhǎng)字符，最長(zhǎng)255個(gè)字符。
varchar2：變長(zhǎng)字符，最長(zhǎng)2000個(gè)字符。
long：變長(zhǎng)字符，最長(zhǎng)2gb。
date：日期型。
boolean：布爾型(true或false)。

2、變量的聲明和賦值

1）聲明變量的語(yǔ)法
變量名 數(shù)據(jù)類(lèi)型 := 初值 ;
變量名 數(shù)據(jù)類(lèi)型 default 初值 ;
A.長(zhǎng)度不能超過(guò)30個(gè)字符，不能有空格。
B.由字母、數(shù)字、下劃線、美元符號(hào)$和#號(hào)組成，必須以字母開(kāi)頭。
C.不能使用PL/SQL或SQL的關(guān)鍵字?？梢允褂胔elp reserved words 命令獲得關(guān)鍵字列表。
D.變量默認(rèn)值為null。
變量命名規(guī)范


image.png

2)使用%TYPE聲明變量類(lèi)型
%TYPE用來(lái)定義與數(shù)據(jù)表中指定字段數(shù)據(jù)類(lèi)型相同的變量。如果表中字段的數(shù)據(jù)類(lèi)型或長(zhǎng)度發(fā)生變化，%TYPE變量會(huì)自動(dòng)隨之變化。這樣用戶不必查看表中各個(gè)字段的數(shù)據(jù)類(lèi)型，就可以確保所定義的變量能夠正確存儲(chǔ)字段數(shù)據(jù)。
格式為：變量名 方案名.表名.字段名%type
如：var_username scott.emp.empname%type; --員工名稱

3)使用%ROWTYPE聲明變量類(lèi)型
%ROWTYPE類(lèi)型可以根據(jù)數(shù)據(jù)表的行結(jié)構(gòu)定義數(shù)據(jù)類(lèi)型，用于存儲(chǔ)從數(shù)據(jù)表中檢索到的一行完整的數(shù)據(jù)。如果數(shù)據(jù)庫(kù)表的結(jié)構(gòu)發(fā)生變化，記錄變量中的結(jié)構(gòu)也將隨之改變。
格式為：變量名 方案名.表名%rowtype
例如：輸入職工編號(hào)，輸出其詳細(xì)信息

declare
    var_emp  scott.emp%rowtype ; --數(shù)據(jù)行變量
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select * into var_emp from scott.emp where  empno=var_empno ;
    dbms_output.put (var_emp.ename) ;
    dbms_output.put_line(' '||var_emp.job) ; 
    dbms_output.put_line(' '||var_emp.sal) ;
end;

4)自定義變量類(lèi)型
自定義記錄數(shù)據(jù)類(lèi)型可以表示由多個(gè)字段值組成的一行數(shù)據(jù)。使用時(shí)首先要定義記錄類(lèi)型的結(jié)構(gòu)，然后聲明該類(lèi)型的變量存儲(chǔ)數(shù)據(jù)。

type 類(lèi)型名稱 is record (
     變量名稱  數(shù)據(jù)類(lèi)型 := 默認(rèn)值 , 
     ……
     變量名稱  數(shù)據(jù)類(lèi)型 := 默認(rèn)值 
) ;

例如:自定義員工姓名與工資類(lèi)型，輸入職工編號(hào)，輸出其姓名與工資

declare
    type employee_type is record (  --聲明自定義記錄類(lèi)型
         ename  scott.emp.ename%type, 
         sal  scott.emp.sal%type
);
    var_emp employee_type ; --聲明自定義記錄類(lèi)型變量
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select ename, sal into var_emp from scott.emp where  empno=var_empno ; 
     dbms_output.put (var_emp.ename) ;
     dbms_output.put_line(' '||var_emp.sal) ; 
end;

例如：允許同一記錄類(lèi)型的兩個(gè)變量整體賦值。

declare
    type employee_type is record ( 
         ename  scott.emp.ename%type, 
         sal  scott.emp.sal%type
);
    var_emp1 employee_type ;
    var_emp2 employee_type ; 
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select ename, sal into var_emp1 from scott.emp where empno=var_empno ;
    var_emp2:=var_emp1; 
    dbms_output.put (var_emp2.ename) ;
    dbms_output.put_line(' '||var_emp2.sal) ; 
end;

3、PL/SQL的語(yǔ)法結(jié)構(gòu)

　　　　declare 
　　　　　　//這部分用于聲明變量
　　　　begin
　　　　　　//這部分用于處理業(yè)務(wù)邏輯
　　　　exception
　　　　　　//這部分用于處理例外也就是異常情況
　　　　end;

例如：

　declare
　　　　　　　　uname varchar2(10);
　　　　　　　　hello varchar2(10) := 'helloworld'; --為變量賦值
　　　　　　　　vsal emp.sal%type; --聲明一個(gè)引用類(lèi)型的變量
　　　　　　　　pall emp%rowtype; --聲明一個(gè)記錄類(lèi)型的變量
　　　　　　　　digit number:=#---提示用戶輸入，然后根據(jù)用戶輸入執(zhí)行相應(yīng)的操作
　　　　　　　　...
　　　　　　begin
　　　　　　　　dbms_output.put_line(hello);
　　　　　　exception
　　　　　　　　when exception_name then
　　　　　　　　　　　//操作
　　　　　　end;

例如：根據(jù)用戶輸入的員工編號(hào)獲得其工資和獎(jiǎng)金

declare
    var_empno number;  --員工編號(hào)
    var_sal number;    --工資
    var_comm number;   --獎(jiǎng)金
begin
    --接收用戶輸入的員工編號(hào)
    var_empno:=&var_empno;
    --獲得工資
    select sal,nvl(comm,0) into var_sal,var_comm from scott.emp where empno=var_empno;
    dbms_output.put_line('員工編號(hào)是'||var_empno||'的工資是'||var_sal||'獎(jiǎng)金是'||var_comm);
end;

4、PL/SQL的條件判斷語(yǔ)句

if語(yǔ)句

　if 條件 then 
　　　　　　語(yǔ)句；
　end if；

if...else語(yǔ)句

　if 條件 then 
　　　　　　語(yǔ)句；
　else 
　　　　　　語(yǔ)句；
　end if；

if...elseif...else語(yǔ)句

　if 條件 then 
　　　　　　語(yǔ)句；
　elsif 條件then 
　　　　　　語(yǔ)句；
　else 
　　　　　　語(yǔ)句；
　end if；

例如：根據(jù)輸入的數(shù)字判斷大小

declare
   v_url VARCHAR(50):='www.baidu.com';
   v_num1 NUMBER:= 100;
   v_num2 NUMBER:=200;
   v_num3 NUMBER;
 begin
   dbms_output.put_line('請(qǐng)輸入第三個(gè)數(shù)字：');
   v_num3 :=&num3;
   if v_num1 < v_num3 then
      dbms_output.put_line('第一個(gè)字符比第三個(gè)字符小');
   end if;
   if v_url like '%baidu%' then
      dbms_output.put_line('網(wǎng)址之中包含baidu單詞');
   end if;
 end;

5、case語(yǔ)句

case語(yǔ)句語(yǔ)法為：

case 變量
    when <表達(dá)式1> then 語(yǔ)句塊1;
    when <表達(dá)式2> then 語(yǔ)句塊2;
      ……
    when <表達(dá)式n> then 語(yǔ)句塊n;
   [ else 語(yǔ)句塊 n+1 ]
end ;

例如：使用case語(yǔ)句判斷數(shù)值

 declare
    v_choose NUMBER:=1;
 BEGIN
    case v_choose 
     when 0 then
          dbms_output.put_line('選擇的是0');
     when 1 then
          dbms_output.put_line('選擇的是1');
     else
          dbms_output.put_line('沒(méi)有選項(xiàng)滿足');
    end case;
 END;

6、PL/SQL的循環(huán)語(yǔ)句

在PL/SQL中有三種循環(huán)結(jié)構(gòu)
1).loop循環(huán)
loop循環(huán)語(yǔ)法為：

loop
　語(yǔ)句塊；
    exit when 循環(huán)結(jié)束條件;
　循環(huán)語(yǔ)句修改;
end loop;

例如：輸出1-99的數(shù)。

　　　　　　declare
　　　　　　　　step number := 1;
　　　　　　begin
　　　　　　　　loop
　　　　　　　　　　exit when step > 100;--當(dāng)step>100退出循環(huán)
　　　　　　　　　　dbms_output.put_line(step || ' ');
　　　　　　　　　　step := step + 1;
　　　　　　　　end loop;
　　　　　　end;

2).while...loop循環(huán)
while...loop循環(huán)語(yǔ)法為：

while 循環(huán)結(jié)束條件 loop
            語(yǔ)句塊；
　　　 循環(huán)語(yǔ)句修改;
end loop;

例如：循環(huán)輸出1-100的數(shù)。

　　　　　　declare
　　　　　　　　step number := 1;
　　　　　　begin
　　　　　　　　while step <= 100 loop
　　　　　　　　　　dbms_output.put_line(step);
　　　　　　　　　　step := step + 1;
　　　　　　　　end loop;
　　　　　　end;

3).for語(yǔ)句循環(huán)
for語(yǔ)句循環(huán)語(yǔ)法為：

　for 循環(huán)變量 in n..N loop
　　　　　　語(yǔ)句塊；
　　　　end loop;

例如：輸出1-100之間的數(shù)

　　　　declare
　　　　begin
　　　　　　for i in 1.. 100 loop
　　　　　　dbms_output.put_line(i);
　　　　end loop;
　　　　end;

7、異常處理

PL/SQL程序運(yùn)行時(shí)出現(xiàn)的錯(cuò)誤稱為異常?？梢苑譃閮深?lèi)：
預(yù)定義異常： 當(dāng) PL/SQL 程序違反 Oracle 規(guī)則或超越系統(tǒng)限制時(shí)隱式引發(fā)
用戶自定義異常：用戶可以聲明自定義異常，自定義的異常通過(guò) raise 語(yǔ)句手動(dòng)引發(fā)
系統(tǒng)預(yù)定義異常

begin
     過(guò)程及sql語(yǔ)句;
exception
     when 異常名稱then
          過(guò)程及sql語(yǔ)句;
     when others then
          過(guò)程及sql語(yǔ)句;
end;
image.png

例如：當(dāng)在程序中使用0作為除數(shù)進(jìn)行運(yùn)算時(shí)，將觸發(fā)該異常。

DECLARE
v_test1 NUMBER := 100;
v_test2 NUMBER := 0;
v_test3 NUMBER := 0;
BEGIN
v_test3 := v_test1 / v_test2;
dbms_output.put_line(v_test3);
EXCEPTION
WHEN zero_divide THEN
dbms_output.put_line('0不能作為除數(shù).');
END;
/

例如：當(dāng)程序中的對(duì)象還沒(méi)有先進(jìn)行對(duì)象初始化的操作，就直接為對(duì)象的屬性賦值，將觸發(fā)該異常。

DECLARE
v_test test_type;
BEGIN
v_test.v_name := 'test';
EXCEPTION
WHEN access_into_null THEN
dbms_output.put_line('首先初始化對(duì)象v_test');
END;
/

例外函數(shù)1： SQLCODE，SQLERRM 在PL/SQL塊中出現(xiàn)Oracle錯(cuò)誤時(shí),通過(guò)使用例外函數(shù)可以取得錯(cuò)誤號(hào)以及相關(guān)的錯(cuò)誤消息。
SQLCODE 返回Oracle錯(cuò)誤號(hào)。
SQLERRM 返回錯(cuò)誤號(hào)對(duì)應(yīng)的錯(cuò)誤消息。

begin
  insert into scott.emp(empno , ename , job , sal , deptno)
  values('7999','ATG','CLERK',1500,'ABC');
exception
  when dup_val_on_index then
    dbms_output.put_line('捕獲dup_val_on_index異常');
    dbms_output.put_line('該主鍵值已經(jīng)存在');
  when others then  -- others子句必須放在各種異常處理的最后
    dbms_output.put_line( '錯(cuò)誤號(hào)：' || sqlcode );
    dbms_output.put_line( '錯(cuò)誤描述：' ||sqlerrm );
end;

備注:others可以捕獲所有異常，如果使了其它具體異常則others子句必須放在這些異常處理的最后。

例外函數(shù)2：RAISE_APPLICATION_ERROR在存儲(chǔ)過(guò)程,函數(shù)和包中使用RAISE_APPLICATION_ERROR可以自定義錯(cuò)誤號(hào)和消息。
raise_application_error：用于自定義錯(cuò)誤消息(用于程序段中)

語(yǔ)法：
raise_application_error(error_number,message[,{TRUE | FALSE}]);
error_number : 錯(cuò)誤號(hào),范圍是: -20000 ~ -20999之間的負(fù)整數(shù);
message : 錯(cuò)誤消息,長(zhǎng)度不能超過(guò)2048字節(jié);
第三個(gè)可靠選參數(shù),如果TRUE,該錯(cuò)誤會(huì)被放在先前錯(cuò)誤堆棧中;如果FALSE(默認(rèn)),則替換先前所有錯(cuò)誤.
例如：

CREATE OR REPLACE PROCEDURE raise_comm
(
eno NUMBER,
commission NUMBER
) IS
v_comm emp.comm%TYPE;
BEGIN
SELECT comm INTO v_comm FROM emp WHERE emp = eno;
IF v_comm IS NULL THEN
raise_application_error(-20001, '該員工無(wú)補(bǔ)助.');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('該雇員不存在.');
END;
/

用戶自定義異常
自定義異常使用預(yù)定義異常和非預(yù)定義異常，程序可以捕獲Oracle錯(cuò)誤，并且在出現(xiàn)Oracle錯(cuò)誤時(shí)自動(dòng)觸發(fā)對(duì)應(yīng)的異常。但是在實(shí)際應(yīng)用中，可能還會(huì) 遇到其它錯(cuò)誤，這時(shí)可以為特定的情況自定義異常，不過(guò)需要顯示觸發(fā)該異常：通過(guò)定義異常，然后關(guān)聯(lián)異常和錯(cuò)誤，顯示觸發(fā)異常，最后在EXCEPTION 中處理該異常。
自定義異常的處理步驟：
定義異常處理：異常名 exception ;
觸發(fā)異常處理：raise 異常名
例如：

--自定義異常  
declare  
        e_wage EXCEPTION;  
        v_wage teachers.wage%type;  
    begin  
            v_wage:=&wage;  
            insert into teachers values (10111,'王彤','教授','01-9月-1990',1000,v_wage,101);  
            if v_wage <0 then   
             raise e_wage;  
            end if;  
            exception   
                when e_wage then  
                dbms_output.put_line('教師工資不能為負(fù)');  
                rollback;  
                when others then  
                    dbms_output.put_line('未知異常');  
    end;  
/
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容