2004-9-7     星期二    陰

PL/SQL數據庫編程(上)

第一章

1. Oracle應用編輯方法概覽
答：1) Pro*C/C++/... : C語言和數據庫打交道的方法，比OCI更常用;
    2) ODBC
    3) OCI: C語言和數據庫打交道的方法，和ProC很相似，更底層，很少用;
    4) SQLJ: 很新的一種用Java訪問Oracle數據庫的方法，會的人不多;
    5) JDBC
    6) PL/SQL: 存儲在數據內運行, 其他方法為在數據庫外對數據庫訪問;

2. PL/SQL
答：1) PL/SQL(Procedual language/SQL)是在標準SQL的基礎上增加了過程化處理的語言;
    2) Oracle客戶端工具訪問Oracle服務器的操作語言;
    3) Oracle對SQL的擴充;

4. PL/SQL的優缺點
答：優點：
    1) 結構化模塊化編程，不是面向對象;
    2) 良好的可移植性(不管Oracle運行在何種操作系統);
    3) 良好的可維護性(編譯通過后存儲在數據庫里);
    4) 提升系統性能;

    缺點
    1) 不便于向異構數據庫移植應用程序(只能用于Oracle);

5. SQL與PL/SQL的區別
答：SQL：1) 第四代語言(智能語言);
         2) 做什么，不管怎么做;
         3) 缺少過程與控制語句;
         4) 無算法
    PL/SQL: 1) 擴展變量和類型;
            2) 擴展控制結構;
            3) 擴展過程與函數;
            4) 擴展對象類型與方法
             

第二章

PL/SQL程序結構

1. PL/SQL塊
答：1) 申明部分, DECLARE(不可少);
    2) 執行部分, BEGIN...END;
    3) 異常處理，EXCEPTION(可以沒有);

2. PL/SQL開發環境
答：可以運用任何純文本的編輯器編輯，例如：VI

3. PL/SQL字符集
答：PL/SQL對大小寫不敏感

4. 標識符命名規則
答：1) 字母開頭;
    2) 后跟任意的非空格字符、數字、貨幣符號、下劃線、或# ;
    3) 最大長度為30個字符(八個字符左右最合適);

5. 變量聲明
答：語法
    Var_name type [CONSTANT][NOT NULL][:=value];
    注：1) 申明時可以有默認值也可以沒有;
        2) 如有[CONSTANT][NOT NULL], 變量一定要有一個初始值;
        3) 賦值語句為“:=”;
        4) 變量可以認為是數據庫里一個字段;
        5) 規定沒有初始化的變量為NULL;

第三章

1. 數據類型
答：1) 標量型：數字型、字符型、布爾型、日期型;
    2) 組合型：RECORD(常用)、TABLE(常用)、VARRAY(較少用)
    3) 參考型：REF CURSOR(游標)、REF object_type
    4) LOB(Large Object)

2. %TYPE
答：變量具有與數據庫的表中某一字段相同的類型
    例：v_FirstName studengts.first_name%TYPE;

3. RECORD類型
答：TYPE record_name IS RECORD(            /*其中TYPE，IS，RECORD為關鍵字，record_name為變量名稱*/
	field1 type [NOT NULL][:=expr1],   /*每個等價的成員間用逗號分隔*/
	field2 type [NOT NULL][:=expr2],   /*如果一個字段限定NOT NULL，那么它必須擁有一個初始值*/
        ...                                /*所有沒有初始化的字段都會初始為NULL
	fieldn type [NOT NULL][:=exprn]);

4. %ROWTYPE
答：返回一個基于數據庫定義的類型
    DECLARE
      v_StuRec Student%ROWTYPE;            /*Student為表的名字*/

    注：與3中定一個record相比，一步就完成，而3中定義分二步：a. 所有的成員變量都要申明; b. 實例化變量;

5. TABLE類型
答：TYPE tabletype IS TABLE OF type INDEX BY BINARY_INTEGER;
    例：DECLARE
          TYPE t_StuTable IS TABLE OF Student%ROWTYPE INDEX BY BINARY_INTERGER;
          v_Student t_StuTable;
	BEGIN
          SELECT * INTO v_Student(100) FROM Student WHERE id = 1001;
        END;
    注：1) 行的數目的限制由BINARY_INTEGER的范圍決定;

6. 變量的作用域和可見性
答：1) 執行塊里可以嵌入執行塊;
    2) 里層執行塊的變量對外層不可見;
    3) 里層執行塊對外層執行塊變量的修改會影響外層塊變量的值;

第四章

1. 條件語句
答：IF boolean_expression1 THEN
    ...
    ELSIF boolean_expression2 THEN   /*注意是ELSIF，而不是ELSEIF*/
    ...                              /*ELSE語句不是必須的，但END IF;是必須的*/
    ELSE
    ...
    END IF;

2. 循環語句
答：1) Loop
       ...
         IF boolean_expr THEN        /* */
            EXIT;                    /* EXIT WHEN boolean_expr */
         END IF;                     /* */
       END LOOP;
    2) WHILE boolean_expr LOOP
       ...
       END LOOP;
    3) FOR loop_counter IN [REVERSE] low_blound..high_bound LOOP
       ...
       END LOOP;
       注：a. 加上REVERSE 表示遞減，從結束邊界到起始邊界，遞減步長為一;
           b. low_blound  起始邊界; high_bound  結束邊界;

3. GOTO語句
答：GOTO label_name;
    1) 只能由內部塊跳往外部塊;
    2) 設置標簽：<<label_name>>
    3) 示例：
       LOOP
       ...
         IF D%ROWCOUNT = 50 THEN
            GOTO l_close;
         END IF;
       ...
       END LOOP;
       <<l_close>>;
       ...

4. NULL語句
答：在語句塊中加空語句，用于補充語句的完整性。示例：
    IF boolean_expr THEN
    ...
    ELSE
      NULL;
    END IF;

5. SQL in PL/SQL
答：1) 只有DML SQL可以直接在PL/SQL中使用;

第五章

1. 游標(CURSOR)
答：1) 作用：用于提取多行數據集;
    2) 聲明：a. 普通申明：DELCARE CURSOR CURSOR_NAME IS select_statement  /* CURSOR的內容必須是一條查詢語句*/
             b. 帶參數申明：DELCARE CURSOR c_stu(p_id student.ID%TYPE) SELECT * FROM student WHERE ID = p_id;
    3) 打開游標：OPEN Cursor_name;   /*相當于執行select語句，且把執行結果存入CURSOR;
    4) 從游標中取數：a. FETCH cursor_name INTO var1, var2, ...; /*變量的數量、類型、順序要和Table中字段一致;*/
                     b. FETCH cursor_name INTO record_var;
       注：將值從CURSOR取出放入變量中，每FETCH一次取一條記錄;
    5) 關閉游標: CLOSE Cursor_name;
       注：a. 游標使用后應該關閉;
           b. 關閉后的游標不能FETCH和再次CLOSE;
           c. 關閉游標相當于將內存中CURSOR的內容清空;

2. 游標的屬性
答：1) %FOUND:    是否有值;
    2) %NOTFOUND: 是否沒有值;
    3) %ISOPEN:   是否是打開狀態;
    4) %ROWCOUNT: CURSOR當前的記錄號;

3. 游標的FETCH循環
答：1) LOOP
         FETCH cursor INTO ...
         EXIT WHEN cursor%NOTFOUND;   /*當cursor中沒記錄后退出*/
       END LOOP;
    2) WHILE cursor%FOUND LOOP
         FETCH cursor INTO ...
       END LOOP;
    3) FOR var IN cursor LOOP
         FETCH cursor INTO...
       END LOOP;
       
第六章

1. 異常
答：DECLARE
      ...
      e_TooManyStudents EXCEPTION;  /* 申明異常 */
      ...
    BEGIN
      ...
      RAISE e_TooManyStudents;      /* 觸發異常 */
      ...
    EXCEPTION
      WHEN e_TooManyStudents THEN   /* 觸發異常 */
      ...
      WHEN OTHERS THEN              /* 處理所有其他異常 */
      ...
    END;

2004-9-8     星期三    陰

PL/SQL數據庫編程(下)

1. 存儲過程(PROCEDURE)
答：創建過程：
    CREATE [OR REPLACE] PROCEDURE proc_name
         [(arg_name[{IN|OUT|IN OUT}]TYPE,
           arg_name[{IN|OUT|IN OUT}]TYPE)]
          {IS|AS}
    procedure_body
    1) IN: 表示該參數不能被賦值(只能位于等號右邊);
    2) OUT:表示該參數只能被賦值(只能位于等號左邊);
    3) IN OUT: 表示該類型既能被賦值也能傳值;

2. 存儲過程例子
答：CREATE OR REPLACE PROCEDURE ModeTest(
        p_InParm IN NUMBER,
        p_OutParm OUT NUMBER,
        p_InOutParm IN OUT NUMBER)
    IS
        v_LocalVar NUMBER;                      /* 聲明部分 */
    BEGIN
        v_LocalVar:=p_InParm;                   /* 執行部分 */
        p_OutParm:=7;
        p_InOutParm:=7;
        ...
    EXCEPTION
        ...                                     /* 異常處理部分 */
    END ModeTest;
    
3. 調用PROCEDURE的例子
答：1) 匿名塊可以調;
    2) 其他PROCDEURE可以調用;
    例：
    DECLARE
        v_var1 NUMBER;
    BEGIN
        ModeTest(12, v_var1, 10);
    END;
    注：此時v_var1等于7

4. 指定實參的模式
答：1) 位置標示法：調用時添入所有參數，實參與形參按順序一一對應;
    2) 名字標示法：調用時給出形參名字，并給出實參
       ModeTest(p_InParm=>12, p_OutParm=>v_var1, p_Inout=>10);
    注：a. 兩種方法可以混用;
        b. 混用時第一個參數必須通過位置來指定。

5. 函數(Function)與過程(Procedure)的區別
答：1) 過程調用本身是一個PL/SQL語句(可以在命令行中通過exec語句直接調用); 
    2) 函數調用是表達式的一部分;

6. 函數的聲明
答：CREATE [OR REPLACE] PROCEDURE proc_name
        [(arg_name[{IN|OUT|IN OUT}]TYPE,
          arg_name[{IN|OUT|IN OUT}]TYPE)]
    RETURN TYPE
    {IS|AS}
    procedure_body
    注：1) 沒有返回語句的函數將是一個錯誤;

7. 刪除過程與函數
答：DROP PROCEDURE proc_name;
    DROP FUNCTION func_name;

第八章

1. 包
答：1) 包是可以將相關對象存儲在一起的PL/SQL的結構;
    2) 包只能存儲在數據庫中，不能是本地的;
    3) 包是一個帶有名字的聲明;
    4) 相當于一個PL/SQL塊的聲明部分;
    5) 在塊的聲明部分出現的任何東西都能出現在包中;
    6) 包中可以包含過程、函數、游標與變量;
    7) 可以從其他PL/SQL塊中引用包，包提供了可用于PL/SQL的全局變量。
    8) 包有包頭和包主體，如包頭中沒有任何函數與過程，則包主體可以不需要。

2. 包頭
答：1) 包頭包含了有關包的內容的信息，包頭不含任何過程的代碼。
    2) 語法：
       CREATE [OR REPLACE] PACKAGE pack_name {IS|AS}
            procedure_specification|function_specification|variable_declaration|type_definition|exception_declaration|cursor_declaration
       END pack_name;
    3) 示例：
       CREATE OR REPLACE PACKAGE pak_test AS
           PROCEDURE RemoveStudent(p_StuID IN students.id%TYPE);
           TYPE t_StuIDTable IS TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER;
       END pak_test;

3. 包主體
答：1) 包主體是可選的，如包頭中沒有任何函數與過程，則包主體可以不需要。
    2) 包主體與包頭存放在不同的數據字典中。
    3) 如包頭編譯不成功，包主體無法正確編譯。
    4) 包主體包含了所有在包頭中聲明的所有過程與函數的代碼。
    5) 示例：
       CREATE OR REPLACE PACKAGE BODY pak_test AS
           PROCEDURE RemoveStudent(p_StuID IN students.id%TYPE) IS
           BEGIN
              ...
           END RemoveStudent;
           TYPE t_StuIDTable IS TABLE OF students.id%TYPE INDEX BY BINARY_INTEGER;
       END pak_test;

4. 包的作用域
答：1) 在包外調用包中過程(需加包名)：pak_test.AddStudent(100010, 'CS', 101);
    2) 在包主體中可以直接使用包頭中聲明的對象和過程(不需加包名);

5. 包中子程序的重載
答：1) 同一個包中的過程與函數都可以重載;
    2) 相同的過程或函數名字，但參數不同;

6. 包的初始化
答：1) 包存放在數據庫中;
    2) 在第一次被調用的時候，包從數據庫中調入內存并被初始化;
    3) 包中定義的所有變量都被分配內存;
    4) 每個會話都將擁有自己的包內變量的副本。

第九章

1. 觸發器
答：1) 觸發器與過程/函數的相同點
       a. 都是帶有名字的執行塊;
       b. 都有聲明、執行體和異常部分;
    2) 觸發器與過程/函數的不同點
       a. 觸發器必須存儲在數據庫中;
       b. 觸發器自動執行;

2. 創建觸發器
答：1) 語法：
       CREATE [OR REPLACE] TRIGGER trigger_name
       {BEFORE|AFTER} triggering_event ON table_reference
       [FOR EACH ROW [WHEN trigger_condition]]
       trigger_body;
    2) 范例：
       CREATE OR REPLACE TRIGGER UpdateMajorStats AFTER INSERT OR DELETE OR UPDATE ON students
       DECLARE
           CURSOR c_Statistics IS
               SELECT * FROM students GROUP BY major;
       BEGIN
           ...
       END Up;

3. 觸發器
答：1) 三個語句(INSERT/UPDATE/DELETE);
    2) 二種類型(之前/之后);
    3) 二種級別(row-level/statement-level);
    所以一共有 3 X 2 X 2 = 12

4. 觸發器的限制
答：1) 不應該使用事務控制語句;
    2) 不能聲明任何LONG或LONG RAW變量;
    3) 可以訪問的表有限。

5. 觸發器的主體可以訪問的表
答：1) 不可以讀取或修改任何變化表(被DML語句正在修改的表);
    2) 不可以讀取或修改限制表(帶有約束的表)的主鍵、唯一值、外鍵列。