Oracle專題11之PLSQL入門

1、PLSQL工具

• PL/SQL Developer的簡介：PL/SQL Developer是一種專門用于開發、測試、調試和優化Oracle PL/SQL的一種工具，更是一個非常好用的Oracle的管理工具。
• 官網下載地址：https://www.allroundautomations.com/bodyplsqldevreg.html （像普通軟件安裝即可）

  2、PLSQL的簡介

  a、什么是PL/SQL？

• PL/SQL是一種程序語言，叫做過程化SQL語言（Procedural Language/SQL）。
• PL/SQL是Oracle數據庫對SQL語句的擴展。在普通SQL語句的使用上增加了編程語言的特點。

• PL/SQL是面向過程的語言，PL/SQL是SQL語言的擴展。

  - 不同數據庫對SQL語言的擴展：

• Oracle：PL/SQL; SQL Server：Transac-SQL(T-SQL)。

  b、PLSQL的特點？

• PL/SQL是Oracle系統的核心語言，現在Oracle的許多部件都是由PL/SQL寫成的。PLSQL具有簡單、高效、靈活、實用的特點。

  3、PLSQL的語法結構

  a、PL/SQL塊

• 塊（block）：是PL/SQL的基本程序單元。
• PL/SQL塊由三部分構成：聲明部分、執行部分、異常處理部分。
• 聲明部分（DECLARE）：聲明變量、常量、復雜數據類型、游標等；執行部分（BEGIN）：PL/SQL語句和SQL語句；異常處理部分（EXCEPTION）：處理執行錯誤。
• END; -- 塊結束標記。（執行部分是必須的，聲明部分和異常處理部分是可選的。）

  b、第一個PL/SQL程序

• 示例：打印hello everyone!
  1、使用Command Window窗口：在PLSQL Developer工具中，使用菜單欄中的New -> Command Window打開命令行：
  
• 在打開的命令行中輸入如下內容：

• 注意：當使用DBMS_OUPUT包輸出信息時，需要設置SQL*Plus環境serveroutput的值為ON。（使用 /后，鍵入回車鍵執行PL/SQL程序）

  SQL> begin dbms_output.put_line('hello everyone!');
   end;
      /
  PL/SQL procedure successfully completed
  
  SQL> set serveroutput on
  SQL> begin dbms_output.put_line('hello everyone!');
  end;
   /
  hello everyone!
  PL/SQL procedure successfully completed

  ---

  2、使用SQL Window窗口：在PLSQL Developer工具中，使用菜單欄中的New ->SQL Window打開命令行：
  

• 在打開的命令行中輸入如下內容：

  begin dbms_output.put_line('hello everyone!');
  end;

• 鍵入F8執行后，output窗口中顯示打印行：（不需要像Command Window窗口中設置serveroutput的值）
  
• 需要注意，SQL Window窗口只能執行PL/SQL或者是SQL代碼。（不能執行SQLPlus命令）

  c、PL/SQL塊的分類

• 匿名塊：動態構造、只能執行一次。（如上的第一個PL/SQL程序）
• 子程序：存儲在數據庫中的存儲過程、函數以及包等。當在數據庫上建立好后可以在其他程序中調用它們。
• 觸發器：當數據庫發生操作時，會觸發一些事件，從而自動執行相應的程序。

  4、PLSQL的變量類型

  a、標識符

• 當編寫PL/SQL塊時，為了臨時存儲數據，需要定義變量和常量。那么變量和常量的定義是需要滿足標識符的限制要求的：
  1、標識符名不能超過30個字符；2、第一個字符必須為字母；3、不分大小寫；4、不能用‘-’
  （減號）。
• 注意：盡量不把變量名聲明和表中字段名一樣。

  b、變量的命名方法

• 為了提高代碼的可讀性，建議遵從以下編碼規則：

c、變量的類型

• 數值類型：NUMBER(p, s)（用于定義固定長度的整數和浮點數） 以及子類型INT、FLOAT等。
• 字符類型：CHAR(n)（定長字符串）、VARCHAR2(n)。（變長的字符串）
• 日期類型：DATE。
• 布爾類型：BOOLEAN。（布爾類型值有false，true和nul，為PLSQL數據類型，表中的列不能夠采用此類型）

d、變量的大小寫規則

• 當編寫sql語句和PL/SQL語句時，即可以采用大寫格式，也可以采用小寫格式。但是為了程序的可讀性，應該盡量按照以下規則：
• SQL關鍵字采用大寫格式，如SELECT，UPDATE等。
• PL/SQL關鍵字采用大寫格式，如DECLARE，BEGIN，END等。
• 數據類型采用大寫格式，如INT、DATE等。
• 標識符和參數采用小寫格式，如：v_sal等。
• 數據庫對象和列采用小寫格式，如：emp，sal等。

  e、PLSQL中的注釋

• 單行注釋： -- 注釋內容
• 多行注釋：/ 注釋內容 /

  f、PL/SQL程序綜合示例

• 使用SQL Window窗口，在窗口中鍵入如下代碼：

  DECLARE
      v_name VARCHAR2(10);
      v_sal NUMBER(7,2);
      v_hiredate DATE;
      c_tax_rate CONSTANT NUMBER(3,2) := 0.02;
      v_tax_sal NUMBER(7,2);
      v_valid BOOLEAN DEFAULT TRUE;
  BEGIN
      SELECT ename, sal, hiredate INTO v_name, v_sal, v_hiredate FROM emp WHERE empno = 7369;
      --計算所得稅
      v_tax_sal := v_sal * c_tax_rate;
      --打印輸出
      DBMS_OUTPUT.put_line(v_name ||'的工資是：' || v_sal ||'， 雇員日期是：' || v_hiredate || '，所得稅是：' || v_tax_sal);
  F v_valid THEN 
          DBMS_OUTPUT.put_line('已核實');
          END IF;
  END;

• 需要注意，在PL/SQL程序中變量和常量的賦值需要使用“:=”的方式，而數據庫中的數據的賦值需要使用INTO關鍵字。
• 布爾類型為PLSQL特有的數據類型，不能把數據庫中檢索出來的數據賦值給布爾類型，主要用于邏輯判斷。

  5、PLSQL的引用型變量和記錄型變量

  a、為什么要使用引用型變量和記錄型變量

• 在許多情況下，PL/SQL變量可以用來存儲在數據庫表中的數據。在這種情況下，變量應該擁有與表列相同的類型。
• 舉例：使用SQL Window窗口。

  DECLARE 
      v_name varchar2(10);
      v_sal number(7,2);
  BEGIN
      SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7788;
      --打印姓名和薪水
      DBMS_OUTPUT.PUT_LINE(v_name || '的工資是：' || v_sal);
  END;

• 這種情況建議使用引用型變量（使用%TYPE）或者記錄型變量（使用%ROWTYPE）而不是將變量類型硬性編碼。

  b、引用型變量

• 引用型變量：是指其數據類型與已經定義的某個數據變量的類型相同，或者與數據庫表的某個列的數據類型相同。
• 舉例：

  DECLARE 
      --定義引用型變量
      v_name emp.ename%TYPE;
      v_sal emp.sal%TYPE;
  BEGIN
      --將7788的姓名和工資賦值給定義的引用型變量
      SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7788;
      --打印姓名和薪水
      DBMS_OUTPUT.put_line(v_name || '的工資是：' || v_sal);
  END;

  c、記錄型變量

• PL/SQL提供%ROWTYPE操作符，返回一個記錄類型，其數據類型和數據庫表的數據結構相一致。
• 舉例：記錄型變量，emp_record emp%ROWTYPE;、記錄型變量分量的引用：emp_record.ename := 'ROSE';
• 示例：

  DECLARE          
      --定義記錄型變量
      emp_record emp%ROWTYPE;
  BEGIN 
      --將7788的員工信息賦值給了記錄型變量emp_record
      SELECT * INTO emp_record FROM emp WHERE empno = 7788;
      --打印姓名和薪水
      DBMS_OUTPUT.PUT_LINE(emp_record.ename || '的工資是：' || emp_record.sal);
  END;

  6、PLSQL的運算符

  a、算術運算符

• 示例：

  BEGIN 
      dbms_output.put_line(10 + 2);
      dbms_output.put_line(10 - 2);
      dbms_output.put_line(10 * 2);
      dbms_output.put_line(10 / 2);
      dbms_output.put_line(10 ** 2);
  END;

  b、關系運算符

• 示例：

  DECLARE
      v_num1 NUMBER(2) := &n1;
      v_num2 NUMBER(2) := &n2;
  BEGIN
      IF(v_num1 = v_num2) THEN
                          DBMS_OUTPUT.put_line('num1 等于 num2');
      ELSIF(v_num1 < v_num2) THEN
                          DBMS_OUTPUT.put_line('num1 小于 num2');
      ELSIF(v_num1 > v_num2) THEN
                          DBMS_OUTPUT.put_line('num1 大于 num2');
      END IF;      
      IF(v_num1 <> v_num2) THEN 
                          DBMS_OUTPUT.put_line('num1 不等于 num2');
      END IF;
  END;   

• 注意：&n1、&n2為鍵盤輸入值。

  c、比較運算符

• 示例：

  DECLARE
      v_num1 NUMBER(2) := &n1;
  BEGIN
      IF(v_num1 BETWEEN 5 AND 10) THEN
                          DBMS_OUTPUT.put_line('num1 介于5 到10之間');
      ELSE 
                          DBMS_OUTPUT.put_line('num1 不在5到10之間');
      END IF;
  
      IF(v_num1 IN(3, 8, 10)) THEN
                          DBMS_OUTPUT.put_line('num1 等于3, 8 , 10中的一個值');
      ELSE
                          DBMS_OUTPUT.put_line('num1 不等于3, 8 , 10中的一個值');
      END IF;
  
      IF(v_num1 IS NULL) THEN 
                          DBMS_OUTPUT.put_line('num1 為空');
      ELSE
                          DBMS_OUTPUT.put_line('num1 不為空');
      END IF;
  END;     

  d、邏輯運算符

• 示例：

  DECLARE
      v_b1 BOOLEAN := &n1;
      v_b2 BOOLEAN := &n2;
  BEGIN
      IF(v_b1 AND v_b2) THEN
                      DBMS_OUTPUT.put_line('AND --true');
      END IF;
  
      IF(v_b1 OR v_b2) THEN
                      DBMS_OUTPUT.put_line('OR --true');
      END IF;
  
      IF(NOT v_b1) THEN
                      DBMS_OUTPUT.put_line('b_v1取反為TRUE');      
      END IF;
  END;

  e、字符以及數字運算的特點

• 空值加上數字仍然是空值：NULL + <數字> = NULL。
• 空值加上（連接）字符，結果是字符：NULL || <字符串> = <字符串>

  7、PLSQL的條件控制語句

  - 附加說明：PL/SQL流程控制語句分類：

• 條件控制語句（條件分支語句）：IF語句和CASE語句。
• 循環語句：LOOP語句。

• 順序語句：GOTO語句、NULL語句。

  a、條件控制語句：if語句

• 分為三種條件分支語句：簡單條件判斷：IF-THEN；二重條件分支：IF-THEN-ELSE；多重條件分支：IF-THEN-ELSIF。
• 簡單條件判斷的語法格式：IF condition THEN statements; END IF;
• 二重條件分支的語法格式：IF condition THEN statements; ELSE statements; END IF;
• 多重條件分支的語法格式：IF condition THEN statements; ELSIF condition THEN statements; ELSE statements; END IF;

  b、條件控制語句：CASE語句

• 使用CASE語句執行多重條件分支操作，語句更加簡捷，執行效率更好。

• 使用CASE語句處理多重條件分支有兩種方法：第一種方法是使用單一選擇符進行等值比較；第二種方法是使用多種條件進行非等值比較。

  - CASE語句中使用單一選擇符進行等值比較的語法格式：

  CASE selector
          WHEN expression1 THEN sequence_of_statements1;
          WHEN expression2 THEN sequence_of_statements2;
          ......
          WHEN expressionN THEN sequence_of_statementsN;
          [ELSE sequence_of_statements;]
  END CASE;

• 示例：輸入成績等級，判斷屬于哪個層次，并打印輸出。

  DECLARE
      v_grade CHAR(1) := '&n';
  BEGIN
      CASE v_grade
          WHEN 'A' THEN
              DBMS_OUTPUT.put_line('優秀');
          WHEN 'B' THEN
              DBMS_OUTPUT.put_line('中等');
          WHEN 'C' THEN
              Dbms_Output.put_line('一般');
          ELSE
              DBMS_OUTPUT.put_line('輸入有誤');
   END CASE;
  END;

  - CASE語句中使用多種條件比較的語法格式：

  CASE 
          WHEN condition1 THEN sequence_of_statements1;
          WHEN condition2 THEN sequence_of_statements2;
          ......
          WHEN conditionN  THEN sequence_of_statementsN;
          [ELSE sequence_of_statements;]
  END CASE;

• 示例：輸入員工號，獲取員工工資，判斷工資，如果工資小于1500，補助加100，如果工資小于2500，補助加80，如果工資小于5000，補助加50。

  DECLARE
  v_sal emp01.sal%TYPE;
  v_empno emp01.empno%TYPE := &no;
  BEGIN
  SELECT sal INTO v_sal FROM emp01 WHERE empno = v_empno;
  CASE
      WHEN v_sal < 1500 THEN 
          UPDATE emp01 SET comm = nvl(comm, 0) + 100 where empno = v_empno;
      WHEN v_sal < 2500 THEN
          UPDATE emp01 SET comm = nvl(comm, 0) + 80 where empno = v_empno;
      WHEN v_sal < 5000 THEN
          UPDATE emp01 SET comm = nvl(comm, 0) + 50 where empno = v_empno;
      COMMIT;
  END CASE;
  END;

  c、循環語句：基本循環

• 語法格式：LOOP statement1; ...... END LOOP;
• 示例：打印數字1到10:。

  DECLARE 
      v_cnt INT :=1;
  BEGIN
      LOOP
          DBMS_OUTPUT.put_line(v_cnt);
          EXIT WHEN v_cnt = 10;
          v_cnt := v_cnt + 1;
      END LOOP;
  END LOOP;

  d、循環語句：WHILE循環

• 語法格式：WHILE condition LOOP statement1; statement2; ...... END LOOP;

  DECLARE 
      v_cnt INT :=1;
  BEGIN
      while v_cnt <= 10 LOOP
                  DBMS_OUTPUT.put_line(v_cnt);
                  v_cnt := v_cnt + 1;
      END LOOP;
  END ;

  e、循環語句：FOR循環

• 語法格式：

  FOR counter in [REVERSE] lower_bound ..upper_bound LOOP 
  statement1; statement2; 
  ...... 
  END LOOP;

• 示例：打印從1到10。

  BEGIN 
      FOR i IN 1..10 LOOP
          Dbms_Output.put_line(i);
      END LOOP;
  END;

• 需要注意，其中i為Oracle的隱含控制變量。

  f、循環語句：嵌套循環和控制語句

• 嵌套循環和標號：嵌套循環是指在一個循環語句中嵌入另一個循環語句；標號用于標記嵌套塊或者嵌套循環；使用<<lable_name>>定義標號。

  DECLARE 
      v_result INT;
  BEGIN
      <<outter>>
      FOR i IN 1..5 LOOP
          <<inter>>
          FOR j IN 1 .. 5 LOOP
              v_result := i;
              EXIT outter WHEN i = 4;
          END LOOP inner; 
          DBMS_OUTPUT.put_line('內：' || v_result);
      END LOOP outter;
      DBMS_OUTPUT.put_line('外：' || v_result);
  END;

• EXIT和EXIT WHEN語句：EXIT語句用于直接退出當前循環。（EXIT WHEN語句用于在滿足條件時退出當前循環）

  DECLARE
      v_cnt INT :=1;
  BEGIN
      LOOP
          DBMS_OUTPUT.put_line(v_cnt);
          EXIT WHEN v_cnt = 10;
          v_cnt := v_cnt + 1;
      END LOOP;
  END;

• CONTINUE和CONTINUE WHEN語句：
• CONTINUE語句時oracle 11g的新特性，用于直接結束當前循環并繼續下一組循環。
• CONTINUE WHEN語句用于在滿足特定條件時結束當前循環語句并繼續下一組循環語句。

  DECLARE
      v_cnt INT := 0;
  BEGIN
      LOOP 
          v_cnt := v_cnt + 1;
          CONTINUE WHEN v_cnt = 5;
          DBMS_OUTPUT.put_line(v_cnt);
          EXIT WHEN v_cnt = 10;
      END LOOP;
  END;

  g、順序語句

• GOTO語句：GOTO語句用于跳轉到特定標號處執行語句。
• 語法格式：GOTO label_name;
• 注意：當使用GOTO跳轉到特定標號時，標號后至少要包含一條執行語句。
• 示例：打印輸出1-10，使用GOTO語句處理。

  DECLARE
      v_cnt INT := 1;
  BEGIN
      LOOP
          DBMS_OUTPUT.put_line(v_cnt);
      IF v_cnt = 10 THEN
          GOTO end_loop;
      END IF;
      v_cnt := v_cnt + 1;
      END LOOP;
      <<end_loop>>
      DBMS_OUTPUT.put_line('循環結束');
  END;  

• NULL語句：NULL語句不會執行任何操作，并且會直接將控制傳遞到下一個語句，使用該語句的主要目的是提高PL/SQL塊的可讀性。

  BEGIN
      SELECT ename, sal INTO v_name, v_sal FROM emp01 WHERE empno = &no;
      IF v_sal < 3000 THEN
          UPDATE emp01 SET comm = nvl(comm, 0) + sal * 0.2 WHERE ename = v_name;
          COMMIT;
          DBMS_OUTPUT.put_line(v_name || '的獎金更新了');
      ELSE
          NULL;
      END IF;
  END; 

• 溫馨提示：之后的Oracle專題代碼示例大部分在SQL WINDOW窗口下執行。