Oracle之PL/SQL學習筆記

標籤：ble   處理常式   程式員   筆記   螢幕   point   pac   save   類型   

自己在學習Oracle是做的筆記及實驗代碼記錄，內容挺全的，也挺詳細，發篇博文分享給需要的朋友，共有1w多字的學習筆記吧。是以前做的，一直在壓箱底，今天拿出來整理了一下，給大家分享，有不足之處還望大家批評指正。

 

　　PL/SQL定義：PL/SQL是由Oracle開發，專門用於Oracle的程式設計語言。 PL---Procedural Language. SQL—Structure QueryLanguage。PL/SQL包括過程化語句和SQL語句

    PL/SQL的單位：塊。 一個塊中可以嵌套子塊。

    塊的三個組成部分：

        一：定義部分（declare）

PL/SQL中使用的變數，常量，遊標和異常的名字都必須先定義後使用。並且定義在以declare關鍵字開頭的定義部分

        二：可執行部分：（begin）

是PL/SQL的主題，包含該塊的可執行語句，該部分定義了塊的功能，是必須的部分。由關鍵字begin開始，end結束

        三：異常處理部分：（exception）

             該部分以exception開始，以end結束

    Demo:        DECLARE –可選            變數，常量，遊標，使用者自訂的特殊類型        BEGIN –必須            --SQL語句            --PL/SQL語句        EXCEPTION –可選        --異常處理部分        END; --必須即由聲明，執行，異常組成

 

DEMO:        DECLARE            V_value1 VARCHAE2(5);        BEGIN            SELECT cn_name            INTO  v_value1            FROM table_name;        EXCEPTION            WHEN exception_name  THEN            --處理常式……        END;

 

PL/SQL的優點：

　　1、    改善了效能：PL/SQL把整個語句塊發送給伺服器，這個過程在單次調用中完成，降低了網路擁擠

　　2、    可重用性：只要有Oracle的地方都能運行

　　3、    模組化：程式中的每一塊都實現一個邏輯操作，有效進行了分割。

 

PL/SQL塊的類：

　　1、    匿名塊：只能儲存一次，不能儲存在資料庫中

　　2、    過程，函數和包（procedure，function，package）：是命了名的PL/SQL塊，被儲存在資料庫中，可以被多次使用，可以用外部程式顯示執行。

　　3、    觸發器：是命名的PL/SQL塊，被儲存在資料庫中，當觸發某事件時自動執行。

 

PL/SQL中變數的命名規範：

　　1、    至多有30個字元

　　2、    不能是保留字

　　3、    必須以字母開頭

　　4、    不允許和資料庫中表的列名相同

　　5、    不可包括$,_和數字以外的字元

 

PL/SQL中的變數

　　1、    PL/SQL變數

　　　　a)  標量型：只能存放單一值

　　　　b)  複合型

　　　　c)   引用型

　　　　d)  LOBx型：存放大資料

　　2、    定義變數文法

　　　　a)  變數名   變數類型   :=  變數值

　　　　b)  V_number NUMBER(2) NOT NULL :=20;

　　　　c)   常量的定義

　　             i.       V_number CONSTANT

　　　　　　　　　 NUMBER(2,3) :=20.098;

 

 1 DEMO：查詢員工號為7369的員工，把其job存入v_job中並輸出 2 DECLARE 3 --定義儲存job的變數v_job為引用變數與--emp.job的類型相同，用%TYPE實現 4          v_job emp.job%TYPE; 5          --定義員工號變數並賦初值（:=） 6          n_empno emp.empno%TYPE:=7369; 7 BEGIN 8          --查詢語句 9          SELECT emp.job10          --把查出來的結果賦給v_job11          INTO   v_job12          FROM   emp13          WHERE emp.empno = n_empno;14          --列印輸出結果15          Dbms_Output.put_line(v_job);16 END;

 

%TYPE屬性:

通過%TYPE聲明一個變數，實際上就是參照變數或表中的欄位的類型作為變數的類型，並且保持同步。

變數將遵循下面的型別宣告：

　　1.  已聲明過的變數類型

　　2.  資料庫中表欄位的類型

 

demo1:

建立一個匿名塊，輸出hello world

 1 --建立一個匿名塊，輸出hello world 2 DECLARE 3   v_hello varchar2(20) :=‘Hello World‘; 4 BEGIN 5   Dbms_Output.put_line(v_hello); 6 END; 7  8 --建立一個匿名塊，查詢emp表，顯示僱員名是’SCOTT‘的薪水，通過DBMS_OUTPUT包來顯示。 9 DECLARE10    v_sal  emp.sal%TYPE;11    v_name emp.ename%TYPE := ‘SCOTT‘;12 BEGIN13   SELECT emp.sal14   INTO v_sal15   FROM emp16   WHERE emp.ename = v_name;17   dbms_output.put_line(v_sal);18 END;

 

demo2:

-從部門表中找到最大的部門號，將其輸出到螢幕

1 --從部門表中找到最大的部門號，將其輸出到螢幕2 DECLARE3   v_deptno dept.deptno%TYPE;4 BEGIN5   SELECT MAX(dept.deptno)6   INTO v_deptno7   FROM dept;8   dbms_output.put_line(v_deptno);9 END;

 

demo3:

--PL/SQL嵌套和變數的範圍

--PL/SQL嵌套和變數的範圍DECLARE    v_parent NUMBER :=10;BEGIN   DECLARE     v_child NUMBER :=20;   BEGIN     dbms_output.put_line(‘chile=‘||v_child);     dbms_output.put_line(‘parent=‘||v_parent);   END;   --dbms_output.put_line(‘chile=‘||v_child);  --注意變數的範圍   dbms_output.put_line(‘chile=‘||v_parent);END;結果：chile=20parent=10chile=10

 

demo4:

--選擇並列印emp表中薪水總和

1 --選擇並列印emp表中薪水總和2 DECLARE3    v_sal emp.sal%TYPE;4 BEGIN5   SELECT sum(emp.sal)6   INTO v_sal7   FROM emp;8   dbms_output.put_line(v_sal);9 END

 

demo5:事務的操作

 1 DECLARE 2   v_sal emp.sal%TYPE :=800; 3 BEGIN  4     UPDATE emp 5     SET emp.sal = emp.sal+ v_sal 6     WHERE emp.job=‘ANALYST‘; 7   SAVEPOINT a; 8     UPDATE emp 9     SET emp.sal = emp.sal+ v_sal10     WHERE emp.job=‘ANALYST‘;11   SAVEPOINT b;12   ROLLBACK TO SAVEPOINT a;13   COMMIT;14 END;

 

 

編寫控制結構

　　1、    條件分支語句

　　　　a)  IF語句：　　

1. 1.  i.      – IF – THEN – END IF
   2.  ii.      – IF – THEN – ELSE – END IF
   3.  iii.      – IF – THEN – ELSEIF – END IF

　　2、    條件陳述式文法

　　　　a)  IF condition THEN

　　　　　　i.      Statement;

　　　　b)  [ELSIF condition THEN

　　　　　　i.      Statement;]

　　　　c)  [ELSE

　　　　　　i.      Statement;]

　　　　d)  ENDIF;

　　3、    DEMO:

　　　　a)  IF v_name = ‘SCOTT’ AND SAL >= 3000   THEN

　　　　　　i.      v_dept :=20;

　　　　b)  END IF;

 

 1 DEMO: 2     --null的處理 3 DECLARE 4   v_x NUMBER :=NULL; 5   v_y NUMBER := NULL; 6 BEGIN 7   IF v_x = v_y THEN 8     dbms_output.put_line(‘NULL等於NULL‘); 9   ELSE10     dbms_output.put_line(‘NULL不等於NULL‘);11   END IF;12 END;13 結果：NULL不等於NULL  空是未知的東西

 

 

　　4.Case語句：文法(有傳回值的)

　　

 1 CASE demo: 2 DECLARE 3   v_sal emp.sal%TYPE; 4   v_dept emp.deptno%TYPE; 5   v_result VARCHAR(20); 6 BEGIN 7   SELECT emp.deptno 8   INTO v_dept 9   FROM emp10   WHERE emp.sal = 11   (12         SELECT MAX(emp.sal) 13         FROM emp14   );15   dbms_output.put_line(v_dept);16   v_result :=17   CASE v_dept18     WHEN 10 THEN ‘部門一‘19     WHEN 20 THEN ‘部門二‘20     ELSE ‘部門三‘21   END;22   dbms_output.put_line(v_result);   23 END;24 輸出結果：25 1026 部門一

5、    迴圈語句LOOP ：

DEMO：迴圈插入11條資料

 1 DECLARE 2    v_count NUMBER := 0; 3 BEGIN 4   LOOP 5     --插入資料 6     INSERT INTO 7     test(name,id,password) 8     VALUES (‘TEST‘||v_count,v_count,‘admin‘); 9     --變數加一10     v_count := v_count+1;11     --判斷允出準則12     EXIT WHEN v_count > 10;13   END LOOP;14 END;

 

 

b)  FOR LOOP迴圈

　　

 1 DEMO： 2 DECLARE 3   v_counter NUMBER :=0; 4 BEGIN 5   --v_counter是自增的 6   FOR v_counter IN 0 .. 10 LOOP 7     DELETE FROM test 8     WHERE test.id = v_counter; 9   END LOOP;10 END;

 

b)  WHILE LOOP

 1 DECLARE 2    v_count NUMBER := 0; 3 BEGIN 4   WHILE v_count<10 LOOP 5     --插入資料 6     INSERT INTO 7     test(name,id,password) 8     VALUES (‘TEST‘||v_count,v_count,‘admin‘); 9     --變數加一10     v_count := v_count+1;11   END LOOP;12 END;

 

 

三：複合類型

　　1、    複合資料型別

　　　　a)  一個複合變數可以存放多個值

　　　　b)  複合變數建立後可以多次使用

　　　　c)  如同枚舉類型和數組

　　2、    PL/SQL記錄

　　　　a)  每個記錄內都有很多的不同類型的欄位

　　　　b)  無初始值的欄位為NULL

　　　　c)  Record 型別宣告使用者自訂的類型

 

　　3、    定義一個記錄

　　　　a)  文法：

1 i.    TYPE type_name IS RECORD(2 ii.    欄位名1    欄位類型1，3 iii.    欄位名2     欄位類型24 iv.    )；

 

b)  DEMO

i.    TYPE emp_record_name IS RECORD(ii.    V_name        varchar(20),iii.    V_password  varchar(10)iv.    );v.    Emp_record  emp_record_name;

 

--記錄的定義與使用DECLARE  TYPE test_record_name  IS RECORD(     v_name test.name%TYPE,     v_id   test.id%TYPE,     v_password   test.password%TYPE  );   test_record test_record_name;BEGIN  SELECT test.name,test.id,test.password  INTO   test_record  FROM test  WHERE test.name=‘TEST0‘;dbms_output.put_line(test_record.v_name||test_record.v_id||test_record.v_password);END;

 

 

5  記錄的另一種定義：表名%ROWTYPE

　　a)  Exp_row table_name%ROWTYPE

　　DEMO:

 1 --記錄的定義與使用 2 DECLARE 3   emp_record emp%ROWTYPE; 4  5 BEGIN 6   SELECT * 7   INTO   emp_record 8   FROM emp 9   WHERE emp.empno=‘7788‘;10   dbms_output.put_line(emp_record.empno||‘  ‘||emp_record.sal);11 END;

編寫遊標

　　1、    遊標的定義：遊標是Oracle在資料庫中開闢的一個工作區，用來存放SELECT語句查詢的結果。

　　2、    遊標的分類

　　　　a)  隱式遊標：PL/SQL隱式建立並管理這一遊標。

　　　　b)  顯示遊標：由程式員定義並控制，從資料庫中讀出多行資料，並從多行資料中一行一行的處理。

　　3、    遊標的聲明：

　　　　a)  文法：CURSOR cursor_name IS select_statement;

　　　　b)  在遊標聲明中SELECT語句不能使用INTO語句，可以在字句子中使用ORDER字句。

　　c)  Demo:

1 CURSOR emp_cursor IS2             SELECT * 3 FROM emp;    

 

　　4、    開啟遊標

　　　　a)  文法：OPEN cursor_name;

　　　　b)  使用遊標之前應開啟遊標

　　　　c)  開啟遊標實際上是執行定義遊標時的SELECT語句，將查詢結果檢索到工作區中。

　　　　d)  如果沒有要返回的行沒有異常

　　5、    從遊標中提取數值

　　　　a)  文法

　　　　　　 i.      FETCH cursor_name INTO [v1,v2……]|record_name]

　　　　b)  在使用FETCH時先把遊標開啟，不然沒法使用。

　　　　c)  對遊標第一次使用FETCH時，遊標指向的是第一條記錄，使用後遊標指向下一條記錄。

　　　　d)  遊標只能向下移動不能回退，如果想回退到上一條記錄，只有把遊標關閉後在開啟。

　　　　e)  INTO字句中的變數個數、順序、資料類型必須和工作區中的保持一致；

　　6、    關閉遊標

　　　　a)  文法：CLOSE cursor_name

　　　　b)  處理完資料後必須關閉遊標，如果需要可以再次開啟遊標，遊標一旦關閉不可再從遊標中提取資料，當關閉遊標後所有和遊標相關的資源都會被關閉。

　　7.遊標的使用Demo

 1 --遊標的使用 2 DECLARE 3   --定義臨時變數來存放遊標中的內容 4   emp_empno emp.empno%TYPE; 5 emp_ename emp.ename%TYPE; 6   --定義名為emp_cursor的遊標 7   CURSOR emp_cursor IS 8   SELECT emp.empno,emp.ename 9   FROM emp;10 BEGIN11   --開啟遊標12   OPEN emp_cursor;13   --迴圈輸出遊標14   FOR i IN 1..5 LOOP15     --提取遊標中的內容16     FETCH emp_cursor 17     INTO emp_empno,emp_ename;18     dbms_output.put_line(emp_empno||‘  ‘||emp_ename);19 END LOOP;20   --關閉遊標21 CLOSE emp_cursor;22 END;

Oracle之PL/SQL學習筆記