Oracle動態(tài)SQL之本地動態(tài)SQL的使用

1、Oracle動態(tài)SQL之本地動態(tài)SQL的使用論文導(dǎo)讀：本地動態(tài)SQL在處理SQL語句時將其分為兩類：一類是DDL語句、DCL語句、非查詢的DML語句、單行查詢的SELECT語句，這一類可使用EXECUTEIMMEIDIATE語句執(zhí)行。關(guān)鍵詞：Oracle，數(shù)據(jù)庫，PL/SQL開發(fā)動態(tài)SQL，本地動態(tài)SQL引言本地動態(tài)SQL在處理SQL語句時將其分為兩類：一類是DDL語句、DCL語句、非查詢的DML語句、單行查詢的SELECT語句，這一類可使用EXECUTE IMMEIDIATE語句執(zhí)行。第二類是多行查詢的SELECT語句，這類語句需要通過游標(biāo)來實現(xiàn)。1、使用EXECUTE IMMEDIATE處

2、理DDL、DCL、非多行查詢的DML語句等動態(tài)SQL使用EXECUTE IMMEDIATE來執(zhí)行動態(tài)SQL是非常簡單的，只需將要執(zhí)行的動態(tài)SQL放到EXECUTE IMMEDIATE命令后即可。如下例：declarev_sqlvarchar2(1000);beginv_sql:= create table tab_test(|name varchar2(80) not null,|addrvarchar2(200),|birthday date); -DDL語句executeimmediate v_sql;v_sql:= grant select on tab_test to public;

3、-DCL語句executeimmediate v_sql;v_sql:= insert into tab_test(name, addr) values(test1,test1 addr); -DML語句executeimmediate v_sql;exception when others thendbms_output.put_line(出現(xiàn)錯誤：|sqlerrm);end;執(zhí)行完這段代碼后，用SELECT查詢一下新建的tab_test表結(jié)構(gòu)及表中數(shù)據(jù)，結(jié)果如下：SQL select * fromtab_test;NAMEADDR BIRTHDAY- -test1test1 addr在這個

4、例子中分別將三個簡單的SQL語句賦值給一個字符串變量，然后通過EXECUTE IMMEDIATE命令來執(zhí)行這個字符串中包含的SQL語句。在實際的使用中，所使用到的DML語句往往不會如此簡單，可能會包含入?yún)?、返回值等，這種情況就需要通過EXECUTE IMMEDIATE增加相應(yīng)的子句來完成處理。完整的EXECUTEIMMEDIATE語句的格式如下：EXECUTE IMMEDIATE sql_statementINTO variable ,variable . |recordUSING IN | OUT | IN OUT bind_argument,IN | OUT | IN OUT bind_a

5、rgument. RETURNING |RETURN INTO bind_argument ,bind_argument.;在這個完整的EXECUTE IMMEDIATE語句中，INTO子句表示在執(zhí)行單行查詢時將查詢結(jié)果保存到指定的變量variable中；USING子句表示在執(zhí)行的SQL語句中含有參數(shù)時使用bind_argument來替換參數(shù)；RETURNING INTO子句表示在執(zhí)行包含RETURNING子句的非查詢DML語句時將返回值保存到bind_argument中。下面通過幾個例子來分別說明這幾個子句的用法，首先看一個單行查詢的例子。declarev_emp_tableemp%rowt

6、ype;v_sqlvarchar2(1000);beginv_sql:= select * from emp where empno=:v_empno;executeimmediate v_sql into v_emp_table using 7369;dbms_output.put_line(emp_name=|v_emp_table.ename);exception when others thendbms_output.put_line(出現(xiàn)錯誤：|sqlerrm);end;在這個例子中使用了INTO子句和USING子句。INTO子句將查詢的結(jié)果保存到變量v_emp_table中，同時在

7、USING子句中用值 7369替換SQL中的參數(shù)v_empno。需要注意的是，EXECUTE IMMEDIATEINTO只能用于返回單行結(jié)果的SELECT語句，如果查詢結(jié)果有多行，必須使用游標(biāo)的FETCH語句。再看一個使用RETURNING INTO子句的例子。declarev_enameemp.ename%type;v_sqlvarchar2(1000);beginv_sql:= update emp set ename=Davis where empno=:v_empno returning ename into:in_ename;executeimmediate v_sql using

8、7369 returning into v_ename;dbms_output.put_line(emp_name=|v_ename);exception when others thendbms_output.put_line(出現(xiàn)錯誤：|sqlerrm);end;在這個例子使用了RETURNINGINTO子句和USING子句。需要注意的是，在這個例子中，EXECUTEIMMEDIATE語句中取返回值使用的是RETURNING INTO子句，而單行查詢中使用的是INTO子句，兩者在EXECUTE IMMEDIATE語句中的位置也有所不同。2、使用FETCH游標(biāo)來處理多行查詢語句的動態(tài)SQL。

9、使用FETCH游標(biāo)處理多行查詢語句分為三個步驟：第一步打開游標(biāo)，第二步循環(huán)獲取每一行數(shù)據(jù)，第三步關(guān)閉游標(biāo)。具體使用過程看下面的例子：set serverout on;declaretypecursor_type_def is ref cursor;v_cursor_acursor_type_def;v_emp_tableemp%rowtype;v_sqlvarchar2(1000);v_jobemp.job%type := CLERK;beginv_sql:= select * from emp where job=:in_job ;openv_cursor_a for v_sql using

10、 v_job;loopfetchv_cursor_a into v_emp_table ;exitwhen v_cursor_a%notfound;dbms_output.put_line(emp_name=|v_emp_table.ename);endloop;closev_cursor_a;exception when others thenclosev_cursor_a;dbms_output.put_line(出現(xiàn)錯誤：|sqlerrm);end;在這個例子中首先定義了一個游標(biāo)類型cursor_type_def，然后定義了一個cursor_type_def類型的變量v_cursor_a

11、，在PL/SQL程序塊中使用OPEN語句將v_cursor_a游標(biāo)變量關(guān)聯(lián)到SQL語句，然后在一個循環(huán)中使用FETCH語句取出每一行數(shù)據(jù)，并在每一次提取數(shù)據(jù)后判斷是否取到了數(shù)據(jù)，如果沒有就退出循環(huán)，并使用CLOSE語句關(guān)閉游標(biāo)。需要注意：使用OPEN打開游標(biāo)時，關(guān)聯(lián)的SQL語句必須是SELECT語句，不能是其它DML語句。在使用FETCH游標(biāo)查詢多行數(shù)據(jù)時，也可以不使用參數(shù)。如果不使用參數(shù)，只需要在OPEN語句中將USING子句去掉即可。另外，在使用FETCH獲取每一行查詢結(jié)果時，除了可以將“select * ”的結(jié)果賦給一個數(shù)據(jù)行變量外(如果SELECT語句查詢指定列則只能賦給普通SQL變量)，還可以將其結(jié)果賦給一個或多個普通SQL變量，但保存查詢結(jié)果的變量個數(shù)不能超過查詢結(jié)果的列數(shù)，并且保存結(jié)果的變量的順序要與表結(jié)構(gòu)順序一致。結(jié)語該文通過對本地動態(tài)SQL的實現(xiàn)原理、使用方法和一些技巧的介紹，希望能夠?qū)φ趶氖禄驕蕚鋸氖逻@方面開發(fā)工作的同行有所幫助。限于知識水平和表達能力，對于文中的一