二十四、oracle pl/sql 變數

標籤：

 

一、變數介紹
在編寫pl/sql程式時，可以定義變數和常量；在pl/sql程式中包括有：
1)、標量類型(scalar)
2)、複合類型(composite) --用於操作單條記錄
3)、參照類型(reference) --用於操作多條記錄
4)、lob(large object)

  

二、標量(scalar)——常用類型
1)、在編寫pl/sql塊時，如果要使用變數，需在定義部分定義變數。
pl/sql中定義變數和常量的文法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier: 名稱
constant：指定常量。需要指定它的初始值，且其值是不能改變的
datatype：資料類型
not null：指定變數值不能為null
:= 給變數或是常量指定初始值
default 用於指定初始值
expr ：指定初始值的pl/sql運算式，可以是文本值、其它變數、函數等。

       

2)、標量定義的案例
1.定義一個變長字串
v_ename varchar2(10);
2.定義一個小數,範圍-9999.99~9999.99
v_sal number(6,2);
3.定義一個小數並給一個初始值為5.4，:=是pl/sql的賦值號
v_sal2 number(6,2):=5.4;
4.定義一個日期類型的資料
v_hiredate date;
5.定義一個布爾變數，不可為空，初始值為false
v_valid boolean not null default false;

          
三、標量(scalar)——使用標量

在定義好變數後，就可以使用這些變數。這裡需要說明的是pl/sql塊為變數賦值不同於其它的程式設計語言，需要在等號前面加冒號(:=)。
下面以輸入員工號，顯示僱員姓名、工資、個人所得稅(稅率為0.03)為例。說明變數的使用，看看如何編寫。

set serveroutput on; --開啟輸出選項
DECLARE
  --稅率為0.03
  C_TAX_RATE NUMBER(3, 2) :=0.03;
  --僱員姓名
  V_ENAME   VARCHAR2(5);
  --工資
  V_SAL     NUMBER(7, 2);
  --個人所得稅
  V_TAX_SAL NUMBER(7, 2);
BEGIN
  --執行
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7369
  --計算所得稅
  V_TAX_SAL := V_SAL * C_TAX_RATE;
  --輸出
  DBMS_OUTPUT.PUT_LINE(‘僱員姓名：‘ || V_ENAME || ‘工資：‘ || V_SAL || ‘ 交稅：‘ || V_TAX_SAL);
END;
/

      

四、標量(scalar)——使用%type類型
對於上面的pl/sql塊有一個問題：就是如果員工的姓名超過了5個字元的話，就會有“ORA-06502: PL/SQL: 數字或值錯誤 :  字串緩衝區太小”錯誤，為了降低pl/sql程式的維護工作量，可以使用%type屬性定義變數，這樣它會按照資料庫列來確定你定義的變數的類型和長度。
我們看看這個怎麼使用：標識符名 表名.列名%type;
比如上例的v_ename，這樣定義： v_ename emp.ename%type;

set serveroutput on; --開啟輸出選項
DECLARE
  --稅率為0.03
  C_TAX_RATE NUMBER(3, 2) :=0.03;
  --僱員姓名
  V_ENAME   emp.ename%TYPE;--推薦使用%type類型
  --工資
  V_SAL     NUMBER(7, 2);
  --個人所得稅
  V_TAX_SAL NUMBER(7, 2);
BEGIN
  --執行
  SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO=&empno; --7777
  --計算所得稅
  V_TAX_SAL := V_SAL * C_TAX_RATE;
  --輸出
  DBMS_OUTPUT.PUT_LINE(‘僱員姓名：‘ || V_ENAME || ‘工資：‘ || V_SAL || ‘ 交稅：‘ || V_TAX_SAL);
END;
/

         

五、複合變數(composite)——介紹
用於存放多個值的變數。主要包括這幾種：
1)、pl/sql記錄
2)、pl/sql表
3)、巢狀表格
4)、varray

 

六、複合類型——pl/sql記錄
類似於進階語言中的結構體，需要注意的是，當引用pl/sql記錄成員時，必須要加記錄變數作為首碼(記錄變數.記錄成員)如下：

set serveroutput on; --開啟輸出選項
DECLARE
  --定義一個pl/sql記錄類型emp_record_type，
  --類型包含3個資料NAME, SALARY, TITLE。說白了，就是一個類型可以存放3個資料，主要是為了方便管理 
  TYPE EMP_RECORD_TYPE IS RECORD(
    NAME   EMP.ENAME%TYPE,
    SALARY EMP.SAL%TYPE,
    TITLE  EMP.JOB%TYPE);
  --定義了一個sp_record變數，這個變數的類型是emp_record_type
  SP_RECORD EMP_RECORD_TYPE;
BEGIN
  SELECT ENAME, SAL, JOB INTO SP_RECORD FROM EMP WHERE EMPNO = 7788;
  DBMS_OUTPUT.PUT_LINE(‘員工名:‘ || SP_RECORD.NAME || ‘工資：‘ || SP_RECORD.SALARY);
END;
/

        

七、複合類型——pl/sql表
相當於進階語言中的數組，但是需要注意的是在進階語言中數組的下標不能為負數，而pl/sql是可以為負數的，並且表元素的下標沒有限制。執行個體如下：

方法一(推薦)：
set serveroutput on; --開啟輸出選項
DECLARE
  --定義了一個pl/sql表類型sp_table_type，該類型是用於存放EMP.ENAME%TYPE
  --INDEX BY VARCHAR2(20)表示下標是字串
  TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY VARCHAR2(20);
  --定義了一個sp_table變數，這個變數的類型是sp_table_type
  SP_TABLE SP_TABLE_TYPE;
BEGIN
  SELECT ENAME, sal INTO SP_TABLE(‘ename‘), SP_TABLE(‘sal‘) FROM EMP WHERE EMPNO = 7788;
  DBMS_OUTPUT.PUT_LINE(‘員工名:‘ || SP_TABLE(‘ename‘)||‘工資：‘||SP_TABLE(‘sal‘));
END;
/

方法二：
set serveroutput on; --開啟輸出選項
DECLARE
  --定義了一個pl/sql 表類型sp_table_type，該類型是用於存放EMP.ENAME%TYPE
  --index by binary_integer表示下標是整數
  TYPE SP_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER; --注意binary_integer如果換為integer就會報錯，知道的朋友歡迎告訴我下
  --定義了一個sp_table變數，這個變數的類型是sp_table_type
  SP_TABLE SP_TABLE_TYPE;
BEGIN
  SELECT ENAME,sal INTO SP_TABLE(-1),SP_TABLE(-2) FROM EMP WHERE EMPNO = 7788;
  DBMS_OUTPUT.PUT_LINE(‘員工名:‘ || SP_TABLE(-1)||‘工資：‘||SP_TABLE(-2));
END;
/

說明：
sp_table_type是pl/sql表類型
emp.ename%type 指定了表的元素的類型和長度
sp_table 為pl/sql表變數
sp_table(0) 則表示下標為0的元素
注意：如果把select ename into sp_table(-1) from emp where empno = 7788；變成select ename into sp_table(-1) from emp;則運行時會出現錯誤，錯誤如下：ORA-01422:實際返回的行數超出請求的行數
解決方案是：使用參照變數(這裡不講)
                 

八、複合變數——巢狀表格(nested table)
      複合變數——變長數組(varray)
省略

       

九、參照變數——介紹
參照變數是指用於存放數值指標的變數。通過使用參照變數，可以使得應用程式共用相同對象，從而降低佔用的空間。在編寫pl/sql程式時，可以使用遊標變數(ref cursor)和物件類型變數(ref obj_type)兩種參照變數類型。推薦使用遊標變數。

         

十、參照變數——ref cursor遊標變數
使用遊標時，當定義遊標時不需要指定相應的select語句，但是當使用遊標時（open 時）需要指定select語句，這樣一個遊標與一個select語句結合了。執行個體如下：
1.請使用pl/sql編寫一個塊，可以輸入部門號，並顯示該部門所有員工姓名和他的工資。
2.在1的基礎上，如果某個員工的工資低於200元，就添加100元。

SET serveroutput ON;
DECLARE 
  --定義遊標
  TYPE sp_emp_cursor IS REF CURSOR;
  --定義一個遊標變數
  sp sp_emp_cursor;
  --定義變數
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  OPEN sp FOR SELECT e.ename, e.sal FROM emp e WHERE e.deptno=10;
  --方法一 loop迴圈
  /*
  LOOP 
  FETCH sp INTO v_ename, v_sal;
  EXIT WHEN sp%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(‘名字：‘ || V_ENAME || ‘ 工資：‘ || V_SAL);
  END LOOP;*/
  --方法二 while迴圈
  /*
  WHILE 1=1 LOOP
    FETCH sp INTO v_ename, v_sal;
    EXIT WHEN sp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(‘名字：‘ || V_ENAME || ‘ 工資：‘ || V_SAL);
  END LOOP;*/
  --方法三 for迴圈
  FOR cur IN (SELECT e.ename, e.sal FROM emp e WHERE e.deptno=10) LOOP
    DBMS_OUTPUT.PUT_LINE(‘名字：‘ || cur.ename || ‘ 工資：‘ || cur.sal);
  END LOOP;
END;
/

二十四、oracle pl/sql 變數