PL/SQL學習匯總

文章目錄

• 1：ex: 一個很綜合的例子：  說明：
• 2： ex2:關聯陣列的使用：這也是一個很好的例子：
• 3：關於key為string的關聯陣列的使用：

1：介紹

sql是非過程語言，在編程中總是會用到過程化控制，PL/SQL就是提供了過程化控制的語言來彌補SQL語言的這一點缺陷。

PL/SQL 陳述式在後面的執行中  pl/sql語句由 PL/SQL引擎執行，SQL語句由 sql引擎執行。PL/SQL 整合了過程化與SQL一起使用， 提高了效能，因為一個過程執行完成後，
統一返回結果。減少了資料的傳輸。如所示：

2：PL/SQL塊結構

PL/SQL塊由四個部分組成；

1：DECLARE(optional)可有可無  用於生命  變數(variables) ， 遊標(cursors)，使用者自訂異常( user-defined exceptions)等。

2：BEGIN(mandatory) 必有：begin裡面是 SQL 陳述式和PL/SQL語句

3：EXCEPTION (optional)可選擇的，當出現錯誤的時候執行，

4：END; (mandatory)必有

函數和過程的區別是：　function 需要有傳回值， 而 procedure 沒有傳回值

PL/SQL的變數的規則查詢官方文檔

注意：非空變數和常量必須對其進行初始化，例如：

v_myname varchar2(20) := 'Hooo'
v_myname varchar2(20) default   'HOOO'

關於 %TYPE型別宣告

%TYPE  用法：identifier  table.column_name %TYPE

ex：
emp_lname  employees.last_name%TYPE

也可以和聲明的變數的名稱相同：
balance  NUMBER(7,2);
min_balance  balance%TYPE := 1000;

注意： 關於oracle中的boolean：   boolean 變數可以有三種類型： TRUE,FALSE,NULL

關於綁定變數(又叫session變數)

綁定變數：
和環境相關：
ex：

VARIABLE b_emp_salary NUMBERBEGIN  select  salary into  :b_emp_salary  from  employees where  employee_id=178;END;

PL/SQL支援嵌入塊；
ex:
DECLARE
BEGIN
        DECLARE
         BEGIN
         ....................
         END;
END;

3： Cursor 遊標1:oracle FAQ2:oracle 11g遊標介紹

遊標是一個指標指向一個私人的記憶體區，私人記憶體區被 oracle 伺服器分配。

A cursors is a pointer used to fetch rows from
a result set. One can think of a cursor as a data structure that describes the results returned from a SQL SELECT statement. One of the variables in
this structure is a pointer to the next record to be fetched from the query results.

Note that if you do repetitive stuff inside a loop and you fail to close your cursors, you would soon run into theORA-01000: maximum number
of open cursors exceeded error.

遊標有兩種類型： 隱式遊標和顯式遊標 ，隱式遊標： 有oracle伺服器進行建立和管理，顯式遊標： 編程人員要進行聲明和建立；

顯式遊標和隱式遊標

遊標的屬性：
SQL%FOUND  至少一條記錄在sql 語句的操作中受到影響
SQL%NOTFOUND 和%FOUND相反
SQL%ROWCOUNT

關於遊標需要單獨深入研究

4：書寫控制結構1：IF 控制

IF condition   THEN  statementsELSIF condition   THEN  statementsELSE  statementsEND IF;

condition有三種類型 ：  TRUE ,FALSE,NULL驗證樣本：

SQL> declare  2       v_myage  number;  3  begin  4    IF v_myage < 11  THEN  5            DBMS_OUTPUT.PUT_LINE('I am a child !');  6    ELSE  7            DBMS_OUTPUT.PUT_LINE('I am not a a child');  8    END IF;  9  END; 10  /PL/SQL 過程已成功完成。SQL> set serveroutput onSQL> /I am not a a childPL/SQL 過程已成功完成。

初始化時為null：

SQL> declare  2       v_myage  number;  3  begin  4    IF v_myage IS NULL  THEN  5            DBMS_OUTPUT.PUT_LINE('I am a child !');  6    ELSE  7            DBMS_OUTPUT.PUT_LINE('I am not a a child');  8    END IF;  9  END; 10  /I am a child !PL/SQL 過程已成功完成。

2：CASE

CASE selector   WHEN  expression1  THEN  result1   WHEN  expression2  THEN  result2   .........   WHEN  expressionN  THEN  resultN   [ELSE resultN+1]END;

簡單的一個例子：

SQL> SET VERIFY OFFSQL> DECLARE  2     v_grade  CHAR(1) := UPPER('&grade');  3     v_appraisal VARCHAR2(20);  4  5  BEGIN  6      v_appraisal := CASE v_grade  7            WHEN  'A' THEN  'Excellent'  8            WHEN  'B'  THEN 'Very Good'  9            WHEN  'C'  THEN  'Good' 10            ELSE 'NO such grde' 11  END; 12  DBMS_OUTPUT.PUT_LINE('Grade: '|| v_grade || '  Apprasisal ' || v_appraisal); 13  END; 14  /輸入 grade 的值:  AGrade: A  Apprasisal ExcellentPL/SQL 過程已成功完成。

第二種 CASE  同樣的例子比較一下，第二種CASE沒有選擇符 WHEN後面直接加判斷即可
有時候需要判斷多個欄位時候，比較靈活。

SQL> DECLARE  2     v_grade  CHAR(1) := UPPER('&grade');  3     v_appraisal VARCHAR2(20);  4  5  BEGIN  6      v_appraisal := CASE  7            WHEN  v_grade = 'A' THEN  'Excellent'  8            WHEN  v_grade IN ('B','C')  THEN 'Very Good'  9 10            ELSE 'NO such grde' 11  END; 12  DBMS_OUTPUT.PUT_LINE('Grade: '|| v_grade || '  Apprasisal ' || v_appraisal); 13  END; 14  /輸入 grade 的值:  BGrade: B  Apprasisal Very GoodPL/SQL 過程已成功完成。

以上是CASE的運算式的介紹

下面介紹CASE 語句；
CASE
END CASE;
在CASE語句中 要注意用END CASE結尾；

CASE運算式和CASE語句的區別：  運算式可以賦值
CASE語句是一個語句，不能再賦值給其他結果了，CASE語句需要用 END CASE結尾；幾個注意事項：if  a and b
或者 if  a or b
應該把比較好運算的，耗記憶體比較少的，放在a位置，
因為 a先計算，對於 and來說 a為false 後面的b就不用計算了，
節省了指令和記憶體。
同or a為true時一樣。

使用null可以增強程式的可讀性：
if condition
   then
else
   then  null；

null的另一個用法：DECLARE  .....  BEGIN     IF  condition  then goto lastpoint END IF;     ....... <<lastpoint>>  --這個標籤後必須有語句，所以 可以用null來表示。        null;   END;           

3：迴圈控制PL/SQL中有三種迴圈：
BASIC LOOP
FOR  LOOP
WHILE LOOP1：基本迴圈：

LOOP  statement1；  EXIT  [WHEN condition];END LOOP

EXIT相當於 其他語言的break；2：While 迴圈：

WHILE condition LOOP     statement1;     statement2;......END LOOP;

3：FOR迴圈；

FOR counter  IN [REVERSE]--REVERSE代表倒過來     lover_bound...upper_bound LOOP  statement1;  statement2;  ............END LOOP;FOR迴圈的步長必須是1。

11g版本 增加了continue關鍵字，這個和java中的continue用法一致。
continue 和 exit的文法一致。
三種方式。
exit ;
exit when condition;
exit  標籤 when condition; 退出到標籤為位置。例子：

SQL> DECLARE  2     v_total NUMBER :=0;  3  BEGIN  4    <<BeforeTopLoop>>  5    FOR  i  IN  1..10  LOOP  6      v_total := v_total+1;  7      DBMS_OUTPUT.PUT_LINE('Total is:' || v_total);  8     FOR j  in 1..10 LOOP  9         CONTINUE BeforeTopLoop  WHEN i+j > 5; 10         v_total := v_total +1; 11     END LOOP; 12    END LOOP; 13  END; 14  /Total is:1Total is:6Total is:10Total is:13Total is:15Total is:16Total is:17Total is:18Total is:19Total is:20PL/SQL 過程已成功完成。

4：GOTO 語句：

文法：  GOTO  label_name;

SQL> BEGIN  2    GOTO second_output;  3    DBMS_OUTPUT.PUT_LINE('This line will never execute.');  4    <<second_output>>  5    DBMS_OUTPUT.PUT_LINE('We　are here!');  6   END;  7   /We　are here!PL/SQL 過程已成功完成。

以上語句第一個輸出永遠不被執行。5：複合資料型別兩種複合資料型別：  collections 和 records集合和記錄的區別：
記錄中儲存不同的資料類型，而集合中儲存相同的資料類型。
記錄相當於java語言中的實體類.
集合相當於數組，java的集合的底層都是採用數組實現的。1：建立PL/SQL Record 文法：

記錄聲明；TYPE type_name IS RECORD    (field_declaration[,field_declaration]......);     identifier type_name;域的聲明field_declaration;    field_name { field_type|variable%TYPE| table.column%TYPE|table%ROWTYPE}    [[NOT NULL]{:= | DEFAULT} expr]    }

2：%ROWTYPE類型

SQL> DECLARE  2        person employees%ROWTYPE;  3  BEGIN  4        select * INTO person FROM employees WHERE  employee_id = 100;  5       DBMS_OUTPUT.PUT_LINE('Name: '||person.first_name);  6  END;  7  /Name: StevenPL/SQL 過程已成功完成。

%TYPE %ROWTYPE 都是採用 錨鎖定技術，
錨引用是在編譯期間解析，如果資料變數類型或者表結構列類型以及列數
發生改變， 含有 %TYPE %ROWTYPE  的代碼要重新進行編譯。

關於錨聲明的一些瞭解：

相當於 A---->B  A引用B B被改變了，要進行重新編譯，保持同步。兩個很好的例子：
使用 %ROWTYPE

首先建立表：SQL> create table retired_emps(empno number(6),ename varchar(25),job varchar(10),mgr number(6) , hiredate date,leavedate date,sal number(8,2),comm number(2,2),deptno number(4));表已建立。

SQL> select * from  retired_emps;未選定行SQL> DECLARE  2     v_employee_number number :=124;  3     v_emp_rec  retired_emps%ROWTYPE;  4  5    BEGIN  6     SELECT employee_id,last_name,job_id,manager_id,  7     hire_date,sysdate,salary,commission_pct,department_id INTO  8     v_emp_rec FROM employees WHERE  employee_id = v_employee_number;  9     INSERT INTO retired_emps VALUES v_emp_rec; 10    END; 11    /PL/SQL 過程已成功完成。SQL>  select * from  retired_emps;     EMPNO ENAME                     JOB               MGR HIREDATE---------- ------------------------- ---------- ---------- --------------LEAVEDATE             SAL       COMM     DEPTNO-------------- ---------- ---------- ----------       124 Mourgos                   ST_MAN            100 16-11月-9908-6月 -13           5800                    50

更新的時候也可以使用 都是一範例如： UPDATE retired_emps  set row = v_emp_rec where empno= v_employee_number;例子： 一個例子說明了三種方式；
這個例子說明了 通過一個表的列聲明 ROWTYPE
               通過顯式遊標聲明，
               通過 TYPE 直接聲明三種方式 。

create table cust_sales_roundup(     customer_id NUMBER(5),     customer_name VARCHAR2(100),     total_sales NUMBER(15,2)   );

SQL> DECLARE  2        cust_sales_roundup_rec  cust_sales_roundup%ROWTYPE;  3        CURSOR cust_sales_cur is SELECT * FROM cust_sales_roundup;  4        cust_sales_rec cust_sales_cur % ROWTYPE;  5  6        TYPE  customer_sales_rectype is RECORD  7        (  8          customer_id NUMBER(5),  9             customer_name cust_sales_roundup.customer_name%TYPE, 10                     total_sales NUMBER(15,2) 11        ); 12 13        prefererred_cust_rec customer_sales_rectype; 14 15   BEGIN 16     --Assign one recored to another 17     cust_sales_roundup_rec := cust_sales_rec; 18     prefererred_cust_rec := cust_sales_rec; 19   END; 20   /PL/SQL 過程已成功完成。

3：關聯陣列關聯陣列是PL/SQL集合，有兩列：
 Key  Values                 key： 可以是 integer和 string資料類型
               values：是一個scalar標量 或者 record
  關聯陣列的順序：
  如何定義關聯陣列：

TYPE  type_name  IS TABLE OF         { column_type | variable%TYPE         |table.column%TYPE} [NOT NULL]         |INDEX BY PLS_INTEGER | BINARY_INTEGER          |VARCHAR2(<size>);       }     identifier type_name;

1：ex: 一個很綜合的例子：  說明：

  
  happyfamily.FIRST 第一個索引
  happyfamily.NEXT 下一個索引
  happyfamily.EXISTS(key) 判斷key是否存在。 

SQL> DECLARE  2     TYPE  list_of_names_t IS TABLE OF   employees.first_name%TYPE  3           INDEX BY PLS_INTEGER;  4     happyfamily list_of_names_t;  5     l_row PLS_INTEGER;  6  7  BEGIN  8     happyfamily(2020202) := 'topwqp';  9     happyfamily(-15070) := 'Steven'; 10     happyfamily(-90900) :='Chris'; 11     happyfamily(88) := 'Veva'; 12 13     l_row := happyfamily.FIRST; 14     WHILE(l_row IS NOT NULL) 15     LOOP 16       DBMS_OUTPUT.PUT_LINE(l_row || '-->' || happyfamily(l_row)); 17       l_row := happyfamily.NEXT(l_row); 18     END LOOP; 19 20      l_row := 88; 21 22      IF  happyfamily.EXISTS(l_row) THEN 23           DBMS_OUTPUT.PUT_LINE('It is here!---->' || happyfamily(l_row)); 24      ELSE 25           DBMS_OUTPUT.PUT_LINE('It is not here !---->'|| happyfamily(l_row)); 26      END IF; 27  END; 28  /-90900-->Chris-15070-->Steven88-->Veva2020202-->topwqpIt is here!---->VevaPL/SQL 過程已成功完成。

關聯陣列相關的方法：
  
  EXISTS  PRIOR  COUNT NEXT FIRST DELETE  LAST
  這些方法需要自己查PL/SQL推薦的書籍學習。2： ex2:關聯陣列的使用：這也是一個很好的例子：

SQL>  DECLARE  2         TYPE  emp_table_type IS TABLE OF  3         employees%ROWTYPE  INDEX BY PLS_INTEGER;  4        my_emp_table  emp_table_type;  5        max_count NUMBER(3) := 104;  6  BEGIN  7      FOR i IN  100..max_count  8      LOOP  9         SELECT * INTO my_emp_table(i) FROM employees 10              WHERE employee_id = i; 11      END LOOP; 12 13      FOR i IN my_emp_table.FIRST..my_emp_table.LAST 14      LOOP 15      DBMS_OUTPUT.PUT_LINE(my_emp_table(i).last_name); 16      END LOOP; 17  END; 18  /KingKochharDe HaanHunoldErnstPL/SQL 過程已成功完成。

3：關於key為string的關聯陣列的使用： 這個例子也很具有典型性；可以運行下：

SQL> DECLARE  2       SUBTYPE location_t IS VARCHAR2(64);  3       TYPE population_type IS TABLE OF NUMBER INDEX BY location_t;  4  5       l_country_population  population_type;  6       l_count PLS_INTEGER;  7       l_location location_t;  8  BEGIN  9        l_country_population('Greeland') := 100000; 10        l_country_population('USA') := 3000000000; 11        l_country_population('Iceland') := 750000; 12        l_country_population('Australia') := 230000000; 13        l_country_population('usa') := 40000000; 14 15        l_count := l_country_population.COUNT; 16        DBMS_OUTPUT.PUT_LINE('COUNT='||l_count); 17 18        l_location := l_country_population.FIRST; 19        DBMS_OUTPUT.PUT_LINE('First Row ='||l_location); 20        DBMS_OUTPUT.PUT_LINE('First Value='||l_country_population(l_location)); 21 22        l_location := l_country_population.LAST; 23        DBMS_OUTPUT.PUT_LINE('LAST Row ='||l_location); 24        DBMS_OUTPUT.PUT_LINE('LAST Value='||l_country_population(l_location)); 25  END; 26  /COUNT=5First Row =AustraliaFirst Value=230000000LAST Row =usaLAST Value=40000000PL/SQL 過程已成功完成。

4：巢狀表格5：顯式遊標文法：
CURSOR cursor_name is  select statement;顯式遊標的執行流程：ex:  示範執行一下：

SQL> DECLARE  2   CURSOR c_emp_cursor IS  3    SELECT employee_id, last_name FROM employees  4    WHERE  department_id =30;  5    v_emp_record c_emp_cursor%ROWTYPE;  6  BEGIN  7      OPEN  c_emp_cursor;  8      LOOP  9       FETCH c_emp_cursor INTO v_emp_record; 10       EXIT WHEN c_emp_cursor%NOTFOUND; 11       DBMS_OUTPUT.PUT_LINE(v_emp_record.employee_id ||'  ' ||v_emp_record.last_name); 12     END LOOP; 13     CLOSE c_emp_cursor; 14  END; 15  /114  Raphaely115  Khoo116  Baida117  Tobias118  Himuro119  ColmenaresPL/SQL 過程已成功完成。

另一種變種：很簡單的形式：

SQL> BEGIN  2    FOR  i IN (SELECT employee_id,last_name FROM employees WHERE department_id =30 )  3    LOOP  4    DBMS_OUTPUT.PUT_LINE(i.employee_id ||'---->'||i.last_name);  5    END LOOP;  6  END;  7  /114---->Raphaely115---->Khoo116---->Baida117---->Tobias118---->Himuro119---->ColmenaresPL/SQL 過程已成功完成。

遊標的屬性：

%ISOPEN

%NOTFOUND

%FOUND

%ROWCOUNT 不是固定值，隨著fetch的次數的增加而增加；

通過這個可以寫如下語句判斷fetch的裝載次數：
EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR  c_emp_cursor %NOTFOUND;6：帶參數的遊標：BULK COLLECT 把查詢結果大量匯入到集合例子1：用數組

SQL> DECLARE  2       TYPE emp_type IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;  3       l_emp emp_type;  4       l_row PLS_INTEGER;  5  BEGIN  6      SELECT * BULK COLLECT INTO l_EMP FROM employees;  7      DBMS_OUTPUT.PUT_LINE('The count is: '|| l_emp.COUNT);  8      l_row := l_emp.FIRST;  9      WHILE(l_row IS NOT NULL) 10      LOOP 11        DBMS_OUTPUT.PUT_LINE(l_row || ':'||l_emp(l_row).employee_id||'---->'||l_emp(l_row).first_name); 12        l_row:=l_emp.NEXT(l_row); 13       END LOOP; 14  END; 15  /The count is: 1071:198---->Donald2:199---->Douglas3:200---->Jennifer4:201---->Michael5:202---->Pat6:203---->Susan7:204---->Hermann8:205---->Shelley9:206---->William10:100---->Steven11:101---->Neena12:102---->Lex13:103---->Alexander14:104---->Bruce15:105---->David16:106---->Valli17:107---->Diana18:108---->Nancy19:109---->Daniel20:110---->John21:111---->Ismael22:112---->Jose Manuel23:113---->Luis24:114---->Den25:115---->Alexander26:116---->Shelli27:117---->Sigal28:118---->Guy29:119---->Karen30:120---->Matthew31:121---->Adam32:122---->Payam33:123---->Shanta34:124---->Kevin35:125---->Julia36:126---->Irene37:127---->James38:128---->Steven39:129---->Laura40:130---->Mozhe41:131---->James42:132---->TJ43:133---->Jason44:134---->Michael45:135---->Ki46:136---->Hazel47:137---->Renske48:138---->Stephen49:139---->John50:140---->Joshua51:141---->Trenna52:142---->Curtis53:143---->Randall54:144---->Peter55:145---->John56:146---->Karen57:147---->Alberto58:148---->Gerald59:149---->Eleni60:150---->Peter61:151---->David62:152---->Peter63:153---->Christopher64:154---->Nanette65:155---->Oliver66:156---->Janette67:157---->Patrick68:158---->Allan69:159---->Lindsey70:160---->Louise71:161---->Sarath72:162---->Clara73:163---->Danielle74:164---->Mattea75:165---->David76:166---->Sundar77:167---->Amit78:168---->Lisa79:169---->Harrison80:170---->Tayler81:171---->William82:172---->Elizabeth83:173---->Sundita84:174---->Ellen85:175---->Alyssa86:176---->Jonathon87:177---->Jack88:178---->Kimberely89:179---->Charles90:180---->Winston91:181---->Jean92:182---->Martha93:183---->Girard94:184---->Nandita95:185---->Alexis96:186---->Julia97:187---->Anthony98:188---->Kelly99:189---->Jennifer100:190---->Timothy101:191---->Randall102:192---->Sarah103:193---->Britney104:194---->Samuel105:195---->Vance106:196---->Alana107:197---->KevinPL/SQL 過程已成功完成。

例子2： 用遊標

DECLARE     CURSOR  ee　IS  SELECT * FROM employees;     TYPE emp_type IS TABLE OF ee%ROWTYPE INDEX BY PLS_INTEGER;     l_emp emp_type;     l_row PLS_INTEGER;BEGIN    OPEN ee;    FETCH ee BULK COLLECT INTO l_emp;    CLOSE ee;    DBMS_OUTPUT.PUT_LINE('The count is: '|| l_emp.COUNT);    l_row := l_emp.FIRST;    WHILE(l_row IS NOT NULL)    LOOP      DBMS_OUTPUT.PUT_LINE(l_row || ':'||l_emp(l_row).employee_id||'---->'||l_emp(l_row).first_name);           l_row:=l_emp.NEXT(l_row);     END LOOP;END;