第27章動態(tài)SQL

1、第第2727章章 動態(tài)動態(tài)SQLSQL在前面介紹的在前面介紹的PL/SQL語句塊中，語句塊中，PL/SQL使用早期綁使用早期綁定執(zhí)行定執(zhí)行SQL語句。即執(zhí)行的語句。即執(zhí)行的SQL在編譯期間就已經(jīng)確定下來在編譯期間就已經(jīng)確定下來，它不會隨著程序執(zhí)行而發(fā)生變化，這樣的，它不會隨著程序執(zhí)行而發(fā)生變化，這樣的SQL語句稱為靜語句稱為靜態(tài)態(tài)SQL語句。但是在實際應(yīng)用中，有些時候需要在程序運(yùn)行語句。但是在實際應(yīng)用中，有些時候需要在程序運(yùn)行時建立并處理相應(yīng)的時建立并處理相應(yīng)的SQL語句，即在執(zhí)行語句，即在執(zhí)行PL/SQL語句塊中語句塊中動態(tài)的處理動態(tài)的處理SQL語句。這樣的語句。這樣的SQL語句稱為動態(tài)語句

2、稱為動態(tài)SQL語句。語句。當(dāng)執(zhí)行當(dāng)執(zhí)行DDL語句（例如，語句（例如，CREAET、DROP等）、等）、DCL語句（例如，語句（例如，CREAET、ROVOKE等）或者是會話控等）或者是會話控制語句制語句(例如，例如，ALTER SESSION等等)時，需要使用動態(tài)時，需要使用動態(tài)SQL。為了在程序中可以更靈活的執(zhí)行。為了在程序中可以更靈活的執(zhí)行SELECT語句或者語句或者DML語句，例如，有些時候有可能直到程序運(yùn)行時才會知道語句，例如，有些時候有可能直到程序運(yùn)行時才會知道DML語句中相關(guān)的模式名、表名和列名，這時也需要使用語句中相關(guān)的模式名、表名和列名，這時也需要使用動態(tài)動態(tài)SQL語句。語句。

3、第第2727章章 動態(tài)動態(tài)SQLSQL在在PL/SQL中提供了兩種方式來實現(xiàn)動態(tài)中提供了兩種方式來實現(xiàn)動態(tài)SQL語句。一語句。一種方式是使用本地動態(tài)種方式是使用本地動態(tài)SQL，另一種方式是使用，另一種方式是使用DBMS_SQL包。本章就來介紹實現(xiàn)動態(tài)包。本章就來介紹實現(xiàn)動態(tài)SQL語句的方法。語句的方法。使用使用EXECUTE IMMEDIATE語句處理單行查詢；語句處理單行查詢；使用游標(biāo)變量處理多行查詢；使用游標(biāo)變量處理多行查詢；批量綁定；批量綁定；使用使用DBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQL。27.1 27.1 使用使用EXECUTE IMMEDIATEEXECUTE IMMEDIAT

4、E語句處理單語句處理單行查詢行查詢EXECUTE IMMEDIATE語句可以用來處理語句可以用來處理DDL語句語句（例如，（例如，CREAET、DROP等）、等）、DCL語句（例如，語句（例如，CREAET、ROVOKE等）、等）、DML語句（例如，語句（例如，INSERT、UPDATE等）以及等）以及SELECT INTO單行查詢語句。其語法規(guī)單行查詢語句。其語法規(guī)則如下：則如下：EXECUTE IMMEDIATE dynamic_stringINTO define_variable, define_variable. | recordUSING IN | OUT | IN OUT bind

5、_argument, IN | OUT | IN OUT bind_argument.RETURNING | RETURN INTO bind_argument, bind_argument.; 27.1 27.1 使用使用EXECUTE IMMEDIATEEXECUTE IMMEDIATE語句處理單行查詢語句處理單行查詢其中，其中，dynamic_string表示一條有效的表示一條有效的SQL語句或者是語句或者是一個一個PL/SQL語句塊的字符串變量。字符串中可以包括用于語句塊的字符串變量。字符串中可以包括用于參數(shù)綁定的占位符；參數(shù)綁定的占位符；define_variable表示存放被選出的

6、字段表示存放被選出的字段值的變量；值的變量；record表示存放被選出的行記錄，其記錄類型為表示存放被選出的行記錄，其記錄類型為是用戶定義類型或者是是用戶定義類型或者是%ROWTYPE類型；類型；bind_argument表示參數(shù)，其參數(shù)模式可以是輸入模式、輸出模式或者是輸表示參數(shù)，其參數(shù)模式可以是輸入模式、輸出模式或者是輸入輸出模式。一個參數(shù)模式為輸入模式的入輸出模式。一個參數(shù)模式為輸入模式的bind_argument參參數(shù)是一個表達(dá)式，而一個參數(shù)模式為輸出模式的數(shù)是一個表達(dá)式，而一個參數(shù)模式為輸出模式的bind_argument參數(shù)就是一個能保存動態(tài)參數(shù)就是一個能保存動態(tài)SQL返回值的變量

7、返回值的變量。27.2 27.2 使用游標(biāo)變量處理多行查詢使用游標(biāo)變量處理多行查詢在前面一節(jié)中講到的在前面一節(jié)中講到的EXECUTE IMMEDIATE語句只語句只能動態(tài)的處理單行查詢，而不能處理多行查詢。為了可以動能動態(tài)的處理單行查詢，而不能處理多行查詢。為了可以動態(tài)的處理多行查詢，就需要使用態(tài)的處理多行查詢，就需要使用OPEN-FOR、FETCH和和CLOSE游標(biāo)操作語句來完成。其中，游標(biāo)操作語句來完成。其中，OPEN語句用于打開游語句用于打開游標(biāo)變量，標(biāo)變量，F(xiàn)ETCH語句用于從游標(biāo)結(jié)果集中提取數(shù)據(jù)，語句用于從游標(biāo)結(jié)果集中提取數(shù)據(jù)，CLOSE語句用于關(guān)閉游標(biāo)變量。本節(jié)就來介紹使用游標(biāo)變語

8、句用于關(guān)閉游標(biāo)變量。本節(jié)就來介紹使用游標(biāo)變量處理多行查詢的方法量處理多行查詢的方法27.2.1 27.2.1 定義游標(biāo)變量定義游標(biāo)變量為了使用游標(biāo)變量動態(tài)的處理多行查詢，首先就需要為了使用游標(biāo)變量動態(tài)的處理多行查詢，首先就需要定義游標(biāo)變量。定義游標(biāo)時需要指定游標(biāo)的類型，并要為游定義游標(biāo)變量。定義游標(biāo)時需要指定游標(biāo)的類型，并要為游標(biāo)定義一個名字，其定義游標(biāo)的語法規(guī)則如下：標(biāo)定義一個名字，其定義游標(biāo)的語法規(guī)則如下：TYPE cursortype IS REF CURSOR;cursor_variable cursortype;其中，其中，cursortype用來執(zhí)行游標(biāo)的類型為用來執(zhí)行游標(biāo)的類型為

9、REF CURSOR；cursor_variable表示游標(biāo)的名字。例如，要定義表示游標(biāo)的名字。例如，要定義一個一個executeTestcurtyp游標(biāo)，就可以使用下面的語句。游標(biāo)，就可以使用下面的語句。TYPE executeTestcurtyp IS REF CURSOR; executeTest_cv executeTestcurtyp; 27.2.2 27.2.2 打開游標(biāo)變量打開游標(biāo)變量在定義完一個游標(biāo)之后，就可以使用在定義完一個游標(biāo)之后，就可以使用OPEN-FOR語句語句打開這個游標(biāo)了。打開這個游標(biāo)了。OPEN-FOR語句可以把游標(biāo)變量和一個語句可以把游標(biāo)變量和一個多行查詢語句關(guān)

10、聯(lián)在一起。只有在打開了游標(biāo)之后，才可以多行查詢語句關(guān)聯(lián)在一起。只有在打開了游標(biāo)之后，才可以執(zhí)行相關(guān)數(shù)據(jù)提取操作。打開游標(biāo)的語法規(guī)則如下：執(zhí)行相關(guān)數(shù)據(jù)提取操作。打開游標(biāo)的語法規(guī)則如下：OPEN cursor_variable FOR dynamic_stringUSING bind_argument, bind_argument.; 27.2.2 27.2.2 打開游標(biāo)變量打開游標(biāo)變量下面來看一個打開游標(biāo)變量的例子。在下面的這個例下面來看一個打開游標(biāo)變量的例子。在下面的這個例子中，首先定義一個游標(biāo)變量，然后將它和一個動態(tài)的子中，首先定義一個游標(biāo)變量，然后將它和一個動態(tài)的SELECT語句關(guān)聯(lián)在一起

11、。語句關(guān)聯(lián)在一起。DECLARE TYPE executeTestcurtyp IS REF CURSOR; -定義游標(biāo)定義游標(biāo)executeTest_cv executeTestcurtyp; tea_ teaName VARCHAR2(10); tea_ salary NUMBER := 3500;BEGIN OPEN executeTest_cv FOR -打開游標(biāo)打開游標(biāo) SELECT teaName , salary FROM t_teacherWHERE salary :s USING tea_ salary;END; 27.2.3 27.2.3 從游標(biāo)變量取得數(shù)據(jù)從游標(biāo)變量取得數(shù)

12、據(jù)在完成了聲明游標(biāo)和打開游標(biāo)的操作之后，就可以使在完成了聲明游標(biāo)和打開游標(biāo)的操作之后，就可以使用游標(biāo)取得結(jié)果集中的數(shù)據(jù)了。使用游標(biāo)取得結(jié)果集中的數(shù)用游標(biāo)取得結(jié)果集中的數(shù)據(jù)了。使用游標(biāo)取得結(jié)果集中的數(shù)據(jù)使用據(jù)使用FETCH語句來完成。其語法規(guī)則如下：語句來完成。其語法規(guī)則如下：FETCH cursor_variableINTO define_variable, define_variable. | record; 其中，其中，cursor_variable表示示前面已經(jīng)定義的游標(biāo)的名表示示前面已經(jīng)定義的游標(biāo)的名字；字；define_variable用來表示接收數(shù)據(jù)的變量；用來表示接收數(shù)據(jù)的變量；

13、record用來用來表示接收數(shù)據(jù)的記錄變量。表示接收數(shù)據(jù)的記錄變量。27.2.3 27.2.3 從游標(biāo)變量取得數(shù)據(jù)從游標(biāo)變量取得數(shù)據(jù)FETCH語句可以從查詢的結(jié)果集中返回一個單行數(shù)據(jù)語句可以從查詢的結(jié)果集中返回一個單行數(shù)據(jù)，并將得到的數(shù)據(jù)值賦值給，并將得到的數(shù)據(jù)值賦值給INTO子句后對應(yīng)的變量，賦值子句后對應(yīng)的變量，賦值操作完成之后，屬性操作完成之后，屬性%ROWCOUNT的值會自動加的值會自動加1，這樣，這樣游標(biāo)就會移動到下一行，繼續(xù)查詢下一行的數(shù)據(jù)值。依次類游標(biāo)就會移動到下一行，繼續(xù)查詢下一行的數(shù)據(jù)值。依次類推，直達(dá)將所有的數(shù)據(jù)全部都查詢出來為止。推，直達(dá)將所有的數(shù)據(jù)全部都查詢出來為止。2

14、7.2.3 27.2.3 從游標(biāo)變量取得數(shù)據(jù)從游標(biāo)變量取得數(shù)據(jù)例如，對于在前面定義的游標(biāo)變量例如，對于在前面定義的游標(biāo)變量executeTest_cv，如果希望將游標(biāo)變量如果希望將游標(biāo)變量executeTest_cv中取得的數(shù)據(jù)放到變中取得的數(shù)據(jù)放到變量量tea_teaName和和tea_salary中，可以使用下面的語句來完中，可以使用下面的語句來完成。成。LOOP FETCH executeTest_cv INTO tea_teaName, tea_salary; EXIT WHEN executeTest_cv%NOTFOUND; -執(zhí)行操作代碼執(zhí)行操作代碼END LOOP; 27.2.

15、4 27.2.4 關(guān)閉游標(biāo)變量關(guān)閉游標(biāo)變量當(dāng)查詢的結(jié)果集檢索完成之后，就可以關(guān)閉游標(biāo)了。當(dāng)查詢的結(jié)果集檢索完成之后，就可以關(guān)閉游標(biāo)了。關(guān)閉游標(biāo)是使用關(guān)閉游標(biāo)是使用CLOSE語句來完成的。關(guān)閉游標(biāo)后，與游語句來完成的。關(guān)閉游標(biāo)后，與游標(biāo)相關(guān)的資源將會被釋放。關(guān)閉游標(biāo)的語法規(guī)則如下：標(biāo)相關(guān)的資源將會被釋放。關(guān)閉游標(biāo)的語法規(guī)則如下：CLOSE cursor_variable; 其中，其中，cursor_variable表示關(guān)閉游標(biāo)的名字。這里的表示關(guān)閉游標(biāo)的名字。這里的cursor_variable是前面已經(jīng)打開的游標(biāo)名。游標(biāo)被關(guān)閉后還是前面已經(jīng)打開的游標(biāo)名。游標(biāo)被關(guān)閉后還可以重新將其打開?？梢灾匦?/p>

16、將其打開。27.2.5 27.2.5 動態(tài)處理多行查詢動態(tài)處理多行查詢DECLARE TYPE executeTestcurtyp IS REF CURSOR; - 定義游標(biāo)定義游標(biāo)executeTest_cv executeTestcurtyp; sql_stmt VARCHAR2(200); tea_ teaName VARCHAR2(10); tea_ salary NUMBER := 3500;tea_record t_teacher%ROWTYPE;BEGINsql_stmt := SELECT teaName , salary FROM t_teacherWHERE salary

17、:sOPEN executeTest_cv FOR sql_stmt- 打開游標(biāo)打開游標(biāo)USING tea_ salary;LOOP FETCH executeTest_cv- 提取數(shù)據(jù)提取數(shù)據(jù) INTO tea_record; EXIT WHEN executeTest_cv%NOTFOUND; DBMS_OUTPUT.PUT_LINE (教師姓名：教師姓名：| tea_record. teaName| 工資：工資：| tea_record. salary);END LOOP;CLOSE executeTest_cv;- 關(guān)閉游標(biāo)關(guān)閉游標(biāo)END; 27.3 27.3 批量綁定批量綁定批量綁定

18、是指把批量綁定是指把SQL語句中的一個變量與一個集合綁語句中的一個變量與一個集合綁定在一起。集合元素必須是定在一起。集合元素必須是SQL數(shù)據(jù)類型（例如，數(shù)據(jù)類型（例如，CHAR、DATE、NUMBER等）。在動態(tài)等）。在動態(tài)SQL中使用批量綁定可以加中使用批量綁定可以加快批量數(shù)據(jù)的處理速度，減少快批量數(shù)據(jù)的處理速度，減少PL/SQL和和SQL引擎之間的切引擎之間的切換提高換提高PL/SQL程序應(yīng)用性能。支持動態(tài)批量綁定的語句主程序應(yīng)用性能。支持動態(tài)批量綁定的語句主要有要有FOR ALL、EXECUTE IMMEDIATE和和FETCH三種。三種。本節(jié)就來介紹使用這三種語句實現(xiàn)批量綁定的方法。本

19、節(jié)就來介紹使用這三種語句實現(xiàn)批量綁定的方法。27.3.1 27.3.1 使用使用FORALLFORALL語句批量綁定語句批量綁定FORALL語句批量綁定允許在動態(tài)語句批量綁定允許在動態(tài)SQL語句中批量綁語句中批量綁定輸入?yún)?shù)?？梢栽趧討B(tài)定輸入?yún)?shù)?？梢栽趧討B(tài)DML上使用上使用FORALL和和 EXECUTE IMMEDIATE語句實現(xiàn)動態(tài)綁定，其語法規(guī)則如語句實現(xiàn)動態(tài)綁定，其語法規(guī)則如下：下：FORALL index IN lower bound.upper boundEXECUTE IMMEDIATE dynamic_stringUSING bind_argument | bind_argu

20、ment(index) , bind_argument | bind_argument(index) ; 27.3.1 27.3.1 使用使用FORALLFORALL語句批量綁定語句批量綁定DECLARETYPE teaID_table_type IS TABLE OF t_teacher.teaID%TYPE;teaID_tab teaID_table_type;sql_stmt VARCHAR2(200);BEGIN teaID_tab:= teaID_table_type (t156354, t186585, t105320);sql_stmt :=UPDATE t_teacher SE

21、T salary= salary * 1.2 WHERE teaID = :tID ; FORALL i IN 1 . teaID_tab.COUNT EXECUTE IMMEDIATE sql_stmt USING teaID_tab (i)；END; 27.3.2 27.3.2 使用使用EXECUTE IMMEDIATEEXECUTE IMMEDIATE語句批量綁定語句批量綁定使用使用EXECUTE IMMEDIATE語句可以把變量或者語句可以把變量或者OUT模式的參數(shù)批量綁定到一個動態(tài)的模式的參數(shù)批量綁定到一個動態(tài)的SQL語句，其語法規(guī)語句，其語法規(guī)則如下：則如下：EXECUTE IMM

22、EDIATE dynamic_string BULK COLLECT INTO define_variable, define_variable . USING bind_argument, bind_argument . RETURNING | RETURN BULK COLLECT INTO bind_argument, bind_argument .; 27.3.2 27.3.2 使用使用EXECUTE IMMEDIATEEXECUTE IMMEDIATE語句批量綁定語句批量綁定BEGINsql_stmt :=UPDATE t_teacher SET salary= salary * 1

23、.2 WHERE deptID = : dID ;RETURNING teaName, salary INTO : tName: tSalary; EXECUTE IMMEDIATE sql_stmt USING deptIDRETURNING BULK COLLECT INTO deptName _tab, teaSalary _tab;FOR i IN 1 . deptID _tab.COUNT LOOPDBMS_OUTPUT.PUT_LINE (教師姓名：教師姓名：| deptName_tab(i)| 新工資：新工資：| teaSalary _tab(i);END LOOP;END; 2

24、7.3.3 27.3.3 使用使用FETCH FETCH 語句批量綁定語句批量綁定使用使用FETCH 語句批量綁定允許從動態(tài)游標(biāo)中取得數(shù)據(jù)語句批量綁定允許從動態(tài)游標(biāo)中取得數(shù)據(jù)，如果希望在，如果希望在FETCH語句中取得多條記錄，可以使用語句中取得多條記錄，可以使用BULK COLLECT子句。子句。BULK COLLECT子句可以批量綁子句可以批量綁定數(shù)據(jù)。使用定數(shù)據(jù)。使用FETCH 語句批量綁定語法規(guī)則如下：語句批量綁定語法規(guī)則如下：FETCH cursor_ variable BULK COLLECT INTO define_variable, define_variable .; 其中，

25、其中，cursor_ variable表示游標(biāo)變量名；表示游標(biāo)變量名；define_variable表示接收查詢結(jié)果的集合變量。表示接收查詢結(jié)果的集合變量。27.3.3 27.3.3 使用使用FETCH FETCH 語句批量綁定語句批量綁定BEGINsql_stmt := SELECT teaName , professionFROM t_teacherWHERE deptID :=:dID;OPEN executeTest_cv FOR sql_stmt- 打開游標(biāo)打開游標(biāo)USING deptID; FETCH executeTest_cv- 提取數(shù)據(jù)提取數(shù)據(jù) BULK COLLECT IN

26、TO teaName_tab t,profession_tab; CLOSE executeTest_cv;- 關(guān)閉游標(biāo)關(guān)閉游標(biāo)FOR i IN 1 . teaName_tab.COUNT LOOP DBMS_OUTPUT.PUT_LINE (教師姓名：教師姓名：| teaName_tab(i)| 職稱：職稱：| profession_tab END LOOP;END;27.4 27.4 使用使用DBMS_SQLDBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQLSQL在在PL/SQL程序設(shè)計過程中，很多時候需要使用動態(tài)程序設(shè)計過程中，很多時候需要使用動態(tài)SQL解決問題。除了前面介紹的使用本地動態(tài)解決問

27、題。除了前面介紹的使用本地動態(tài)SQL之外，在之外，在Oracle系統(tǒng)中還提供了一個專門用于在系統(tǒng)中還提供了一個專門用于在PL/SQL中動態(tài)執(zhí)行中動態(tài)執(zhí)行SQL的系統(tǒng)包的系統(tǒng)包DBMS_SQL包，也可以用于實現(xiàn)動態(tài)包，也可以用于實現(xiàn)動態(tài)SQL。相對于本地動態(tài)。相對于本地動態(tài)SQL會在每次執(zhí)行時都需要進(jìn)行解析，會在每次執(zhí)行時都需要進(jìn)行解析，DBMS_SQL可以分析一次執(zhí)行多次進(jìn)行。本節(jié)就來介紹可以分析一次執(zhí)行多次進(jìn)行。本節(jié)就來介紹DBMS_SQL包中常用存儲過程和函數(shù)以及如何使用包中常用存儲過程和函數(shù)以及如何使用DBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQL。27.4.1 DBMS_SQL27.4.1

28、 DBMS_SQL包中常用存儲過程和函數(shù)包中常用存儲過程和函數(shù)在在DBMS_SQL系統(tǒng)包中，包括了執(zhí)行打開游標(biāo)、分析系統(tǒng)包中，包括了執(zhí)行打開游標(biāo)、分析語句、變量綁定、關(guān)閉游標(biāo)等操作的很多的存儲過程和函數(shù)語句、變量綁定、關(guān)閉游標(biāo)等操作的很多的存儲過程和函數(shù)，通過這些存儲過程和函數(shù)，可以有效的實現(xiàn)對，通過這些存儲過程和函數(shù)，可以有效的實現(xiàn)對SQL語句進(jìn)語句進(jìn)行控制和處理。下面介紹一些在行控制和處理。下面介紹一些在DBMS_SQL系統(tǒng)包中經(jīng)常系統(tǒng)包中經(jīng)常用到的存儲過程和函數(shù)。用到的存儲過程和函數(shù)。27.4.1 DBMS_SQL27.4.1 DBMS_SQL包中常用存儲過程和函數(shù)包中常用存儲過程和函數(shù)

29、FUNCTION OPEN_CURSOR：打開指定的動態(tài)游標(biāo)：打開指定的動態(tài)游標(biāo)。函數(shù)返回游標(biāo)。函數(shù)返回游標(biāo)ID的值，其返回值類型為的值，其返回值類型為INTEGER類型。類型。PROCEDURE PARSE(c IN INTEGER , statement INVARCHAR2, language_flag IN INTEGER )：解析動態(tài)游：解析動態(tài)游標(biāo)中的標(biāo)中的SQL語句。其中，參數(shù)語句。其中，參數(shù)c用來指定游標(biāo)用來指定游標(biāo)ID；參數(shù)；參數(shù)statement表示被解析的表示被解析的SQL語句；參數(shù)語句；參數(shù)language-flag表示解表示解析析SQL語句使用的語句使用的Oracle

30、版本（包括版本（包括V6、V7和和NATIVE3個個版本）。版本）。PROCEDURE BIND_VARIABLE (c IN INTEGER, name IN VARCHAR2, value)：綁定游標(biāo)結(jié)果到指定變量。：綁定游標(biāo)結(jié)果到指定變量。其中，參數(shù)其中，參數(shù)c用來指定游標(biāo)用來指定游標(biāo)ID；參數(shù)；參數(shù)name表示變量名稱；參表示變量名稱；參數(shù)數(shù)value表示綁定到變量的值。表示綁定到變量的值。27.4.1 DBMS_SQL27.4.1 DBMS_SQL包中常用存儲過程和函數(shù)包中常用存儲過程和函數(shù)PROCEDURE BIND_VARIABLE_CHAR(c IN INTEGER, name

31、 IN VARCHAR2, value, column_size IN INTEGER)：綁定游標(biāo)結(jié)果到指定變量。其中，參數(shù)：綁定游標(biāo)結(jié)果到指定變量。其中，參數(shù)c用來用來指定游標(biāo)指定游標(biāo)ID；參數(shù)；參數(shù)name表示變量名稱；參數(shù)表示變量名稱；參數(shù)value表示綁定表示綁定到變量的值；參數(shù)到變量的值；參數(shù)column_size表示指定列的長度，該參數(shù)表示指定列的長度，該參數(shù)只有在只有在CHAR 、VARCHAR2等類型中使用。它是可選的。等類型中使用。它是可選的。FUNCTION EXECUTE (c IN INTEGER)：使用游標(biāo)執(zhí)：使用游標(biāo)執(zhí)行語句。函數(shù)返回值為一個行語句。函數(shù)返回值為一個

32、INTEGER類型的值。對于類型的值。對于DML語句（語句（INSERT、UPDATE、DELETE），如果返回），如果返回1，則，則表示執(zhí)行成功；如果返回表示執(zhí)行成功；如果返回0，則表示執(zhí)行失敗。其中，參數(shù)，則表示執(zhí)行失敗。其中，參數(shù)c用來指定游標(biāo)用來指定游標(biāo)ID。27.4.1 DBMS_SQL27.4.1 DBMS_SQL包中常用存儲過程和函數(shù)包中常用存儲過程和函數(shù)FUNCTION FETCH_ROWS (c IN INTEGER)：獲?。韩@取游標(biāo)結(jié)果中的數(shù)據(jù)。函數(shù)返回值為一個游標(biāo)結(jié)果中的數(shù)據(jù)。函數(shù)返回值為一個INTEGER類型的值類型的值。如果返回值為。如果返回值為0，則表示已經(jīng)取值取到

33、了游標(biāo)最后。，則表示已經(jīng)取值取到了游標(biāo)最后。PROCEDURE COLUMN_VALUE(c IN INTEGER, position IN INTEGER, value)：取得一個游標(biāo)在指定位置上：取得一個游標(biāo)在指定位置上對應(yīng)的變量值。其中，參數(shù)對應(yīng)的變量值。其中，參數(shù)c用來指定游標(biāo)用來指定游標(biāo)ID；position表示表示指定字段在游標(biāo)中的位置指定字段在游標(biāo)中的位置(從從1開始開始)；value表示指定到變量表示指定到變量的值。（該過程需要在函數(shù)的值。（該過程需要在函數(shù)FETCH_ROWS之后調(diào)用）。之后調(diào)用）。27.4.1 DBMS_SQL27.4.1 DBMS_SQL包中常用存儲過程和

34、函數(shù)包中常用存儲過程和函數(shù)PROCEDURE DEFINE_CLUMN(c IN INTEGER, position IN INTEGER, column IN datatype, column_size IN INTEGER)：定義動態(tài)游標(biāo)獲取的被選擇列的對應(yīng)值。其：定義動態(tài)游標(biāo)獲取的被選擇列的對應(yīng)值。其中，參數(shù)中，參數(shù)c用來指定游標(biāo)用來指定游標(biāo)ID；position表示指定字段在游標(biāo)中表示指定字段在游標(biāo)中的位置的位置(從從1開始開始)；參數(shù)；參數(shù)column指定被定義的列；參數(shù)指定被定義的列；參數(shù)column_size表示指定列的長度，該參數(shù)只有在表示指定列的長度，該參數(shù)只有在CHAR 、

35、VARCHAR2等類型中使用。它是可選的。等類型中使用。它是可選的。PROCEDURE CLOSE_CURSOR(c IN OUT INTEGER)：關(guān)閉指定的動態(tài)游標(biāo)。其中，參數(shù)：關(guān)閉指定的動態(tài)游標(biāo)。其中，參數(shù)c用來指定用來指定游標(biāo)游標(biāo)ID。27.4.2 27.4.2 使用使用DBMS_SQLDBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQLSQL的查的查詢操作詢操作如果希望使用如果希望使用DBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQL的查詢操作的查詢操作，一般需要經(jīng)歷打開游標(biāo)、解析，一般需要經(jīng)歷打開游標(biāo)、解析SQL、綁定變量、獲取的被、綁定變量、獲取的被選擇列的對應(yīng)值、執(zhí)行選擇列的對應(yīng)值、執(zhí)行SQL、

36、獲取查詢結(jié)果值以及關(guān)閉游標(biāo)、獲取查詢結(jié)果值以及關(guān)閉游標(biāo)等幾個步驟。等幾個步驟。27.4.2 27.4.2 使用使用DBMS_SQLDBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQLSQL的查詢操作的查詢操作BEGINv_ cursorID:= DBMS_SQL. OPEN_CURSOR; - 打開游標(biāo)打開游標(biāo)v_ select:=SELECT teaID, teaName , dept FROM t_ teacher WHERE deptID=: deptID; DBMS_SQL. PARSE (v_ cursorID,v_ select, DBMS_SQL. NATIVE); - 解析解析SQLDB

37、MS_SQL. BIND_VARIABLE_CHAR (v_cursorID, : deptID,p_ deptID); - 綁定變量綁定變量DBMS_SQL. DEFINE_CLUMN (v_ cursorId,1,v_teaID,15); - 獲取的被選擇列的對應(yīng)值獲取的被選擇列的對應(yīng)值DBMS_SQL. DEFINE_CLUMN (v_ cursorI,2,v_teaName,10); DBMS_SQL. DEFINE_CLUMN (v_ cursorI,3,v_dept,20); v_row selectResult Updated:= DBMS_SQL. EXECUTE (v_cur

38、sorID); - 執(zhí)行執(zhí)行SQLLOOPIF DBMS_SQL. FETCH_ROWS (v_ cursorID)=0 THENEXIT; END IF; DBMS_SQL. COLUMN_VALUE (v_ cursorID,1,v_teaID); - 取得指定位置對應(yīng)的值取得指定位置對應(yīng)的值DBMS_SQL COLUMN_VALUE (v_ cursorID,2,v_teaName); DBMS_SQL. COLUMN_VALUE (v_ cursorID,3,v_dept); END LOOP; DBMS_SQL. CLOSE_CURSOR (v_ cursorID); - 關(guān)閉游標(biāo)關(guān)

39、閉游標(biāo)COMMIT; -提交事務(wù)提交事務(wù)EXCEPTION WHEN OTHERS THEN DBMS_SQL. CLOSE_CURSOR (v_ cursorID); - 關(guān)閉游標(biāo)關(guān)閉游標(biāo)RAISE; END; 27.4.3 27.4.3 使用使用DBMS_SQLDBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQLSQL的的DMLDML操作操作如果希望使用如果希望使用DBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQL的的INSERT和和UPDATE操作，一般需要經(jīng)歷打開游標(biāo)、解析操作，一般需要經(jīng)歷打開游標(biāo)、解析SQL、綁定、綁定變量、執(zhí)行變量、執(zhí)行SQL以及關(guān)閉游標(biāo)等幾個步驟。以及關(guān)閉游標(biāo)等幾個步驟。27.4

40、.3 27.4.3 使用使用DBMS_SQLDBMS_SQL包實現(xiàn)動態(tài)包實現(xiàn)動態(tài)SQLSQL的的DMLDML操作操作CREATE OR REPLACE FUNCTION updat_result (p_ result IN t_result. result %TYPEp_stuID IN t_ t_result.stuID%TYPE,p_curID IN t_result.curID %TYPE)RETURN INTEGERASv_cursorID INTEGER; - 游標(biāo)游標(biāo)ID v_rowUpdated INTEGER;- 游標(biāo)執(zhí)行語句返回結(jié)果游標(biāo)執(zhí)行語句返回結(jié)果v_updateResult VARCHAR2 (100);- 修改成績表中數(shù)據(jù)的修改成績表中數(shù)據(jù)的SQL語句語句 v_ result INT; - 學(xué)生成績學(xué)生成績 BEGINv_ cursorID:= DBMS_SQL. OPEN_CURSOR; - 打開游標(biāo)打開游標(biāo)v_ updateResult:=UPDATE t_