Oracle學習筆記十 使用PL/SQL，oracle學習筆記

Oracle學習筆記十 使用PL/SQL，oracle學習筆記

PL/SQL 簡介

PL/SQL 是過程語言(Procedural Language)與結構化查詢語言(SQL)結合而成的編程語言，是對 SQL 的擴展，它支持多種數據類型，如大對象和集合類型，可使用條件和循環等控制結構，可用於創建存儲過程、觸發器和程序包，給SQL語句的執行添加程序邏輯，與 Oracle 服務器和 Oracle 工具緊密集成，具備可移植性、靈活性和安全性。  

PL/SQL 的優點

• 支持 SQL，在 PL/SQL 中可以使用：

　　1.數據操縱命令 　　2.事務控制命令 　　3.游標控制 　　4.SQL 函數和 SQL 運算符

• 支持面向對象編程 (OOP)

• 可移植性，可運行在任何操作系統和平台上的Oralce 數據庫

• 更佳的性能，PL/SQL 經過編譯執行

  　　

• 與 SQL 緊密集成，簡化數據處理。

　　1.支持所有 SQL 數據類型 　　2.支持 NULL 值 　　3.支持 %TYPE 和 %ROWTYPE 屬性類型 　　  

• 安全性，可以通過存儲過程限制用戶對數據的訪問

 

PL/SQL 的體系結構

PL/SQL 引擎駐留在 Oracle 服務器中，該引擎接受 PL/SQL 塊並對其進行編譯執行。  

PL/SQL基礎

PL/SQL 塊簡介

PL/SQL 塊是構成 PL/SQL 程序的基本單元，它將邏輯上相關的聲明和語句組合在一起。 PL/SQL 分為三個部分，聲明部分、可執行部分和異常處理部分。

[DECLARE  
    declarations]   --聲明部分定義變量、游標和自定義異常
    BEGIN
    executable statements  --包含 SQL 和 PL/SQL 語句的可執行部分
    [EXCEPTION 
    handlers]  --指定出現錯誤時需要執行的操作
    END;

 

DECLARE
  qty_on_hand NUMBER(5);    --聲明部分定義變量、游標和自定義異常
BEGIN     --begin到end 包含 SQL 和 PL/SQL 語句的可執行部分
  SELECT quantity INTO qty_on_hand
  FROM Products
  WHERE product = '芭比娃娃'
  FOR UPDATE OF quantity;
  IF qty_on_hand > 0 THEN
    UPDATE Products SET quantity = quantity + 1
    WHERE product = '芭比娃娃';
    INSERT INTO purchase_record
    VALUES ('已購買芭比娃娃', SYSDATE);
  END IF;
  COMMIT;
EXCEPTION  /* 異常處理語句 */  --指定出現錯誤時需要執行的操作
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('出錯:'|| SQLERRM);  
END;

 

PL/SQL中的復合符號

　　:=        賦值操作符 　　||         連接操作符 　　--        單行注釋 　　/**/      多行注釋 　　<<>>  標簽分隔符 　　..         范圍操作符 　　**        求冪操作符  

變量和常量

PL/SQL 塊中可以使用變量和常量 　　1.在聲明部分聲明，使用前必須先聲明 　　2.聲明時必須指定數據類型，每行聲明一個標識符 　　3.在可執行部分的 SQL 語句和過程語句中使用   聲明變量和常量的語法：

identifier [CONSTANT] datatype [NOT NULL]   
        [:= | DEFAULT expr];

 

給變量賦值有兩種方法： 1.使用賦值語句 := 2.使用 SELECT INTO 語句

DECLARE
  icode VARCHAR2(6);
  p_catg VARCHAR2(20);
  p_rate NUMBER;
  c_rate CONSTANT NUMBER := 0.10;
BEGIN
  ...
  icode := 'i205';
  SELECT p_category, itemrate * c_rate
  INTO  p_catg, p_rate
  FROM itemfile WHERE itemcode = icode;
  ...
END;

 

 

 數據類型

PL/SQL 支持的內置數據類型

 

 指定數值的存儲格式

 

 字符數據類型

字符數據類型包括： 1.CHAR 2.VARCHAR2 3.LONG 4.RAW 5.LONG RAW

 

PL/SQL 的數據類型與 SQL數據類型的比較

數據類型

SQL類型

PL/SQL類型

CHAR

1~2000

1~32767

LONG

1~2GB

1~32760

LONG RAW

1~2GB

1~32760

RAW

1~2000

1~32767

VARCHAR2

1~4000

1~32767

 

日期時間和布爾數據類型

日期時間類型 1.存儲日期和時間數據 2.常用的兩種日期時間類型 ①DATE ②TIMESTAMP   布爾數據類型 1.此類別只有一種類型，即BOOLEAN類型 2.用於存儲邏輯值(TRUE、FALSE和NULL) 3.不能向數據庫中插入BOOLEAN數據 4.不能將列值保存到BOOLEAN變量中 5.只能對BOOLEAN變量執行邏輯操作

LOB  數據類型

用於存儲大文本、圖像、視頻剪輯和聲音剪輯等非結構化數據。 LOB 數據類型可存儲最大 4GB的數據。 LOB 類型包括： 　　1.BLOB   將大型二進制對象存儲在數據庫中 　　2.CLOB   將大型字符數據存儲在數據庫中 　　3.NCLOB   存儲大型UNICODE字符數據 　　4.BFILE     將大型二進制對象存儲在操作系統文件中 LOB 類型的數據庫列僅存儲定位符，該定位符指向大型對象的存儲位置 DBMS_LOB程序包用於操縱 LOB 數據

SET SERVEROUTPUT ON
DECLARE
  clob_var   CLOB;
  amount     INTEGER;
  offset     INTEGER;
  output_var VARCHAR2(100);
BEGIN
  SELECT chapter_text INTO clob_var   --從表中選擇 CLOB 定位符到 clob_var變量中
  FROM my_book_text
  WHERE chapter_id=5;
  amount := 24;  -- 要讀取的字符數
  offset := 1;   -- 起始位置
  DBMS_LOB.READ(clob_var,amount,offset,output_var);  --從CLOB數據中讀取24個字符存儲到 output_var 變量中
  DBMS_OUTPUT.PUT_LINE(output_var);  --顯示讀到的信息
END;

 

  屬性類型

用於引用數據庫列的數據類型，以及表示表中一行的記錄類型。   屬性類型有兩種： 1.%TYPE  -  引用變量和數據庫列的數據類型 2.%ROWTYPE  -  提供表示表中一行的記錄類型   使用屬性類型的優點： 1.不需要知道被引用的表列的具體類型 2.如果被引用對象的數據類型發生改變，PL/SQL 變量的數據類型也隨之改變

icode itemfile.itemcode%TYPE;
emp_rec scott.emp%ROWTYPE;

 

 邏輯比較

邏輯比較用於比較變量和常量的值，這些表達式稱為布爾表達式，布爾表達式由關系運算符與變量或常量組成。

 

關系運算符

說明

=

比較兩個變量是否相等，如果值相當，則返回 True

<>, !=

比較兩個變量，如果不相等，則返回 True

<

比較兩個變量，檢查值 1 是否小於值 2

>

比較兩個變量，檢查值 1 是否大於 值 2

<=

比較兩個變量，檢查變量 1 是否小於等於變量 2

>=

比較兩個變量，檢查變量 1 是否大於等於變量 2

  布爾表達式的結果為TRUE、FALSE或NULL，通常由邏輯運算符AND、OR和NOT連接。 布爾表達式有三種類型： 　　1.數字布爾型 　　2.字符布爾型 　　3.日期布爾型  

控制結構

PL/SQL 支持的流程控制結構：

　　條件控制 　　　　1.IF 語句 　　　　2.CASE 語句 　　循環控制 　　　　1.LOOP 循環 　　　　2.WHILE 循環 　　　　3.FOR 循環 　　順序控制 　　　　1.GOTO 語句 　　　　2.NULL 語句  

條件控制

DECLARE
    icode VARCHAR2(4);
    irate NUMBER;
BEGIN
    icode := 'i203';
  SELECT itemrate INTO irate FROM itemfile 
  WHERE  itemcode = icode;
  IF irate > 200 THEN      --條件判斷
    UPDATE itemfile SET itemrate = itemrate - 200
    WHERE itemcode = icode;
  ELSE
    UPDATE itemfile SET itemrate = itemrate - 50
    WHERE itemcode = icode;
  END IF;    --結束
  DBMS_OUTPUT.PUT_LINE('itemrate='|| irate);
END;

 

CASE 語句用於根據單個變量或表達式與多個值進行比較，執行 CASE 語句前，先計算選擇器的值。

BEGIN
    CASE ‘&grade’
      WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’優異’);
      WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (優秀’);
      WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’);
      WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’);
      WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’較差’);
      ELSE DBMS_OUTPUT.PUT_LINE (’沒有此成績’);
    END CASE;
END;

 循環控制

循環控制用於重復執行一系列語句。 循環控制語句包括： 　　1.LOOP、EXIT 和 EXIT WHEN   循環控制的三種類型： 　　1.LOOP   -   無條件循環

LOOP 
  sequence_of_statements
END LOOP;

　　2.WHILE  -  根據條件循環

WHILE condition LOOP 
  sequence_of_statements
END LOOP;

　　3.FOR  -  循環固定的次數

FOR counter IN [REVERSE] value1..value2
LOOP 
  sequence_of_statements
END LOOP;

loop...exit...when...end loop循環控制

采用loop...exit...when...end loop循環控制的語法結構如下所示：

loop
  循環體；
  exit when循環條件;
end loop

while...loop...end loop循環控制

這種循環控制的語法如下：

while 條件
loop
  執行語句段;
end loop;

 

循環例子：

--while循環
--循環輸出Hello world
declare
    var_i int := 10;
begin
    loop
        dbms_output.put_line('Hello world');
        if var_i = 0 then
            exit;
        end if;
        var_i := var_i - 1;
    end loop;
end;

--loop輸出1-10
declare
    var_i int := 1;
begin
    loop
        dbms_output.put_line('i = ' || var_i);
        exit when var_i = 10;
        var_i := var_i + 1;
    end loop;
End

--while循環1-5
Declare
    var_i int:=1;
Begin
    while var_i<=10 loop
        Dbms_output.put_line('I='||var_i);
        Var_i:=var_i+1;
    End loop;
End;



--for 循環 : for 變量 in 范圍 loop
--1-10輸出
begin
    for i in 1..10 loop
        dbms_output.put_line('i = ' || i);
    end loop;
end

--對員工的工資判斷，如果少於2000則加50，直到工資滿足為止！
declare
    var_sal int;
begin
    loop
        select sal into var_sal from emp where empno = 7499;
        exit when var_sal >= 2000;
        update emp set sal = sal + 50 where empno = 7499;
        dbms_output.put_line('加50');
    end loop;
end;

 

 

順序控制

順序控制用於按順序執行語句。 順序控制語句包括： 　　1.GOTO 語句 -  無條件地轉到標簽指定的語句 　　2.NULL 語句 -  什麼也不做的空語句

DECLARE
  qtyhand itemfile.qty_hand%type;
  relevel itemfile.re_level%type;
BEGIN
  SELECT qty_hand,re_level INTO qtyhand,relevel
  FROM itemfile WHERE itemcode = 'i201';
  IF qtyhand < relevel THEN
    GOTO updation;  --跳轉到<<updation>>語句塊
  ELSE
    GOTO quit;  --跳轉到<<quit>>
  END IF;
  <<updation>>
  UPDATE itemfile SET qty_hand = qty_hand + re_level
  WHERE itemcode = 'i201';
  <<quit>>
  NULL;
END;

 動態 SQL

動態 SQL 是指要執行的SQL語句需要在執行的時候才能確定的SQL語句，是在PL/SQL程序執行時生成的 SQL 語句，編譯程序對動態 SQL 不做處理，而是在程序運行時動態構造語句、對語句進行語法分析並執行。DDL 語句命令和會話控制語句不能在 PL/SQL 中直接使用，但是可以通過動態 SQL 來執行。   執行動態 SQL 的語法：

   EXECUTE IMMEDIATE dynamic_sql_string

      [INTO  define_variable_list]

      [USING bind_argument_list];

 例：

DECLARE
  sql_stmt VARCHAR2(200);
  emp_id NUMBER(4) := 7566;
  emp_rec emp%ROWTYPE;
BEGIN
    EXECUTE IMMEDIATE 
    'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';

    sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
    EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
END;

 

--顯示員工編號為7499的所有信息
declare
    Var_row emp%rowtype;
begin
    select * into var_row from emp where empno = 7499;
    dbms_output.put_line('編號=' || var_row.empno || '姓名=' || var_row.ename);
end;

 

--動態執行
declare
    var_row emp%rowtype;
    var_sql varchar(100);
    var_empno int;
begin
    var_sql := 'select * from emp where empno = :num' ;
    execute immediate var_sql into var_row using var_empno;
    dbms_output.put_line('編號=' || var_row.empno || '姓名=' || var_row.ename);
end;

--用動態SQL來實現分頁
begin
    execute immediate 'select * from (select rownum rn, d.* from (select * from :tableName order by sal desc) d) where rn > :min and rn <= :max' using 'emp' , 1, 5;
End;

 

 錯誤處理

  在運行程序時出現的錯誤叫做異常，發生異常後，語句將停止執行，控制權轉移到 PL/SQL 塊的異常處理部分。   異常有兩種類型： 1.預定義異常 -  當 PL/SQL 程序違反 Oracle 規則或超越系統限制時隱式引發 2.用戶定義異常  -  用戶可以在 PL/SQL 塊的聲明部分定義異常，自定義的異常通過 RAISE 語句顯式引發     處理預定義異常

access_into_null   --在未初化對象時出現
case_not_found    --在CASE語句中的選項與用戶輸入的數據不匹配時出現
collection_is_null    --在給尚未初始化的表或數組賦值時出現
cursor_already_open    --在用戶試圖打開已經打開的游標時出現
dup_val_on_index    --在用戶試圖將重復的值存在使用唯一索引的數據庫列中時出現
invalid_cursor        --在執行非法游標運算（如打開一個尚未打開的游標）時出現
invalid_number     --在將字符串轉換為數字時出現
login_denied        --在輸入的用戶名或密碼無效時出現
no_data_found     --在表中不存在的請求的行時出現，此外，當程序引用已經刪除的元素時
storage_error       --在內存損壞或PL/SQL耗盡內存時出現
too_many_rows    --在執行SELECT INTO語句後返回多行時出現
value_error    --在產生大小限制錯誤時出現
zero_divide    --以零作除數時出現
Others          --針對所有異常

 處理用戶定義異常

DECLARE 
  invalidCATEGORY EXCEPTION;
  category VARCHAR2(10);
BEGIN
  category := '&Category';
  IF category NOT IN ('附件','頂蓋','備件') THEN
    RAISE invalidCATEGORY;
  ELSE
    DBMS_OUTPUT.PUT_LINE('您輸入的類別是'|| category);
  END IF;
EXCEPTION
  WHEN invalidCATEGORY THEN
    DBMS_OUTPUT.PUT_LINE('無法識別該類別');
END;

 引發應用程序錯誤

DECLARE
  rate itemfile.itemrate%TYPE;
  rate_exception EXCEPTION;  --定義異常
BEGIN
  SELECT NVL(itemrate,0) INTO rate FROM itemfile 
  WHERE  itemcode = 'i207';
  IF rate = 0 THEN
    RAISE rate_exception;  --引發異常
  ELSE
    DBMS_OUTPUT.PUT_LINE('項費率為：' || rate);
  END IF;
EXCEPTION
  WHEN rate_exception THEN  --撲捉異常
    RAISE_APPLICATION_ERROR(-20001, '未指定項費率');
END;

引發系統級錯誤

begin
    if 10 > 5 then
        raise_application_error(-20001, '這個是自定義錯誤顯示');
    end if;
end;