使用Oracle的DBMS_SQL包执行动态SQL语句.docx

使用Oracle的DBMS_SQL包执行动态SQL语句 引用自:/ggjjzhzz/archive/2005/10/17/507880.aspx在某些场合下，存储过程或触发器里的SQL语句需要动态生成。Oracle的DBMS_SQL包可以用来执行动态SQL语句。本文通过一个简单的例子来展示如何利用DBMS_SQL包执行动态SQL语句： DECLARE v_cursor NUMBER; v_stat NUMBER; v_row NUMBER; v_id NUMBER; v_no VARCHAR(100); v_date DATE; v_sql VARCHAR(200); s_id NUMBER; s_date DATE;BEGIN s_id := 3000; s_date := SYSDATE; v_sql := SELECT id,qan_no,sample_date FROM tblno WHERE id :sid and sample_date :sdate; v_cursor := dbms_sql.open_cursor;-打开游标； dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);-解析动态SQL语句； dbms_sql.bind_variable(v_cursor, :sid, s_id); -绑定输入参数； dbms_sql.bind_variable(v_cursor, :sdate, s_date); dbms_sql.define_column(v_cursor, 1, v_id);-定义列 dbms_sql.define_column(v_cursor, 2, v_no, 100); dbms_sql.define_column(v_cursor, 3, v_date); v_stat := dbms_sql.execute(v_cursor);-执行动态SQL语句。 LOOP EXIT WHEN dbms_sql.fetch_rows(v_cursor)=0;-fetch_rows在结果集中移动游标，如果未抵达末尾，返回1。 dbms_sql.column_value(v_cursor, 1, v_id);-将当前行的查询结果写入上面定义的列中。 dbms_sql.column_value(v_cursor, 2, v_no); dbms_sql.column_value(v_cursor, 3, v_date); dbms_output.put_line(v_id | ; | v_no | ; | v_date); END LOOP; dbms_sql.close_cursor(v_cursor); -关闭游标。END;结果：3095;S051013XW00010;15-10月-053112;A051013XW00027;10-10月-053113;A051013XW00028;13-10月-053116;S051013XW00031;13-10月-05 附：DBMS_SQL的文档(来源：)Oracle DBMS_SQLVersion 10.2 GeneralNote: DMBS_SQL is the traditional form of dynamic SQL in Oracle.For most purposes native dynamic sql (NDS) will suffice but there are some things the DBMS_SQL package does that can not be done any other way. This page emphasizes those areas where there is no substitute.PurposeSourceORACLE_HOME/rdbms/admin/dbmssql.sqlConstantsNameData TypeValuev6INTEGER0nativeINTEGER1v7INTEGER2Defined Data TypesGeneral TypesTYPE desc_rec IS RECORD (col_type binary_integer := 0,col_max_len binary_integer := 0,col_name varchar2(32) := ,col_name_len binary_integer := 0,col_schema_name varchar2(32) := ,col_schema_name_len binary_integer := 0,col_precision binary_integer := 0,col_scale binary_integer := 0,col_charsetid binary_integer := 0,col_charsetform binary_integer := 0,col_null_ok boolean := TRUE);TYPE desc_rec2 IS RECORD (col_type binary_integer := 0,col_max_len binary_integer := 0,col_name varchar2(32767) := ,col_name_len binary_integer := 0,col_schema_name varchar2(32) := ,col_schema_name_len binary_integer := 0,col_precision binary_integer := 0,col_scale binary_integer := 0,col_charsetid binary_integer := 0,col_charsetform binary_integer := 0,col_null_ok boolean := TRUE);TYPE desc_tab IS TABLE OF desc_recINDEX BY binary_integer;TYPE desc_tab2 IS TABLE OF desc_rec2INDEX BY binary_integer;TYPE varchar2a IS TABLE OF VARCHAR2(32767)INDEX BY binary_integer;TYPE varchar2s IS TABLE OF VARCHAR2(256)INDEX BY binary_integer;Bulk SQL TypesTYPE Bfile_Table IS TABLE OF bfileINDEX BY binary_integer;TYPE Binary_Double_Table IS TABLE OF binary_doubleINDEX BY binary_integer;TYPE Binary_Float_Table IS TABLE OF binary_floatINDEX BY binary_integer;TYPE Blob_Table IS TABLE OF blobINDEX BY binary_integer;TYPE Clob_Table IS TABLE OF clobINDEX BY binary_integer;TYPE Date_Table IS TABLE OF dateINDEX BY binary_integer;TYPE interval_day_to_second_Table IS TABLE OFdsinterval_unconstrained INDEX BY binary_integer;TYPE interval_year_to_MONTH_Table IS TABLE OFyminterval_unconstrainedINDEX BY binary_integer;TYPE Number_Table IS TABLE OF NUMBERINDEX BY binary_integer;TYPE time_Table IS TABLE OF time_unconstrainedINDEX BY binary_integer;TYPE time_with_time_zone_Table IS TABLE OF TIME_TZ_UNCONSTRAINED INDEX BY binary_integer;TYPE timestamp_Table IS TABLE OF timestamp_unconstrainedINDEX BY binary_integer;TYPE timestamp_with_ltz_table IS TABLE OFTIMESTAMP_LTZ_UNCONSTRAINEDINDEX BY binary_integer;TYPE Urowid_Table IS TABLE OF urowidINDEX BY binary_integer;TYPE timestamp_with_time_zone_table IS TABLE OFTIMESTAMP_TZ_UNCONSTRAINEDINDEX BY binary_integer;TYPE Varchar2_Table IS TABLE OF VARCHAR2(2000)INDEX BY binary_integer;DependenciesSELECT nameFROM dba_dependenciesWHERE referenced_name = DBMS_SQLUNIONSELECT referenced_nameFROM dba_dependenciesWHERE name = DBMS_SQL;ExceptionsError CodeReasonORA-06562Inconsistent types: Raised by procedure column_value orvariable_value if the type of the given out argument where to put the requested value is different from the type of the valueBIND_ARRAYBinds a given value to a given collectiondbms_sql.BIND_ARRAY(c IN INTEGER,name IN VARCHAR2, IN ,index1 IN INTEGER,index2 IN INTEGER);DECLAREstmt VARCHAR2(200);dept_no_array dbms_sql.number_table;c NUMBER;dummy NUMBER;BEGIN dept_no_array(1) := 10; dept_no_array(2) := 20; dept_no_array(3) := 30; dept_no_array(4) := 40; dept_no_array(5) := 30; dept_no_array(6) := 40; stmt := delete from emp where deptno = :dept_array; c := dbms_sql.open_cursor; dbms_sql.parse(c, stmt, dbms_sql.NATIVE); dbms_sql.bind_array(c, :dept_array, dept_no_array, 1, 4); dummy := dbms_sql.execute(c); dbms_sql.close_cursor(c);EXCEPTIONS WHEN OTHERS THEN IF dbms_sql.is_open(c) THEN dbms_sql.close_cursor(c); END IF; RAISEEND;/BIND_VARIABLEBinds a given value to a given variabledbms_sql.bind_variable (c IN INTEGER,name IN VARCHAR2,value IN )CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AScursor_name INTEGER;rows_processed INTEGER;BEGIN cursor_name := dbms_sql.open_cursor; dbms_sql.parse(cursor_name, DELETE FROM emp WHERE sal :x,dbms_sql.NATIVE); dbms_sql.bind_variable(cursor_name, :x, salary);rows_processed := dbms_sql.execute(cursor_name); dbms_sql.close_cursor(cursor_name);EXCEPTION WHEN OTHERS THEN dbms_sql.close_cursor(cursor_name);END;/BIND_VARIABLE_CHARBinds a given value to a given variabledbms_sql.bind_variabl_char (c IN INTEGER,name IN VARCHAR2,value IN CHAR CHARACTER SET ANY_CS ,out_value_size IN INTEGER);See bind_variable demoBIND_VARIABLE_RAWBinds a given value to a given variabledbms_sql.bind_variable_raw (c IN INTEGER,name IN VARCHAR2,value IN RAW ,out_value_size IN INTEGER);See bind_variable demoBIND_VARIABLE_ROWIDBinds a given value to a given variabledbms_sql.bind_variable_rowid (c IN INTEGER,name IN VARCHAR2,value IN ROWID);See bind_variable demoCLOSE_CURSORCloses cursor and free memorydbms_sql.close_cursor(c IN OUT INTEGER);See is_open demoCOLUMN_VALUEReturns value of the cursor element for a given position in a cursordbms_sql.column_value (c IN INTEGER,position IN INTEGER,value OUT ,column_error OUT NUMBER,actual_length OUT INTEGER);See final demoCOLUMN_VALUE_CHARReturns value of the cursor element for a given position in a cursordbms_sql.column_value_char (c IN INTEGER,position IN INTEGER,value OUT CHAR CHARACTER SET ANY_CS,column_error OUT NUMBER,actual_length OUT INTEGER);See column_value in final demoCOLUMN_VALUE_LONGReturns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONGdbms_sql.column_value_long (c IN INTEGER,position IN INTEGER,length IN INTEGER,offset IN INTEGER,value OUT VARCHAR2,value_length OUT INTEGER);See column_value in final demoCOLUMN_VALUE_RAWReturns value of the cursor element for a given position in a cursordbms_sql.column_value_raw (c IN INTEGER,position IN INTEGER,value OUT RAW,column_error OUT NUMBER,actual_length OUT INTEGER);See column_value in final demoCOLUMN_VALUE_ROWIDUndocdbms_sql.column_value_rowid (c IN INTEGER,position IN INTEGER,value OUT ROWID,column_error OUT NUMBER,actual_length OUT INTEGER);See column_value in final demoDEFINE_ARRAYDefines a collection to be selected from the given cursor, used only with SELECT statementsdbms_sql.define_array (c IN INTEGER,position IN INTEGER, IN cnt IN INTEGER,lower_bnd IN INTEGER);DECLAREc number;d number;n_tab dbms_sql.number_table;indx number := -10;BEGIN c := dbms_sql.open_cursor; dbms_sql.parse(c,select n from t order by 1,dbms_sql.NATIVE); dbms_sql.define_array(c, 1, n_tab, 10, indx); d := dbms_sql.execute(c); LOOP d := dbms_sql.fetch_rows(c); dbms_sql.column_value(c, 1, n_tab); exit when d != 10; END LOOP; dbms_sql.close_cursor(c);EXCEPTIONS WHEN OTHERS THEN IF dbms_sql.is_open(c) THEN dbms_sql.close_cursor(c); END IF; RAISE;END;/DEFINE_COLUMNDefines a column to be selected from the given cursor, used only with SELECT statementsdbms_sql.define_column (c IN INTEGER,position IN INTEGER,column IN )See final demoDEFINE_COLUMN_CHARUndocdbms_sql.define_column_char (c IN INTEGER,position IN INTEGER,column IN CHAR CHARACTER SET ANY_CS,column_size IN INTEGER);See define_column in final demoDEFINE_COLUMN_LONGDefines a LONG column to be selected from the given cursor, used only with SELECT statementsdbms_sql.define_column_long (c IN INTEGER,position IN INTEGER);See define_column in final demoDEFINE_COLUMN_RAWUndocdbms_sql.define_column_raw (c IN INTEGER,position IN INTEGER,column IN RAW,column_size IN INTEGER);See define_column in final demoDEFINE_COLUMN_ROWIDUndocdbms_sql.define_column_rowid (c IN INTEGER,position IN INTEGER,column IN ROWID);See define_column in final demoDESCRIBE_COLUMNSDescribes the columns for a cursor opened and parsed through DBMS_SQLdbms_sql.describe_columns (c IN INTEGER,col_cnt OUT INTEGER,desc_t OUT DESC_TAB);DECLAREc NUMBER;d NUMBER;col_cnt PLS_INTEGER;f BOOLEAN;rec_tab dbms_sql.desc_tab;col_num NUMBER;PROCEDURE print_rec(rec in dbms_sql.desc_rec) ISBEGIN dbms_output.new_line; dbms_output.put_line(col_type = | rec.col_type);dbms_output.put_line(col_maxlen = | rec.col_max_len);dbms_output.put_line(col_name = | rec.col_name);dbms_output.put_line(col_name_len = | rec.col_name_len);dbms_output.put_line(col_schema_name = | rec.col_schema_name);dbms_output.put_line(col_schema_name_len = | rec.col_schema_name_len);dbms_output.put_line(col_precision = | rec.col_precision);dbms_output.put_line(col_scale = | rec.col_scale);dbms_output.put(col_null_ok = );if (rec.col_null_ok) thendbms_output.put_line(true);elsedbms_output.put_line(false); END IF;END;BEGIN c := dbms_sql.open_cursor; dbms_sql.parse(c, select * from scott.bonus, dbms_sql.NATIVE); d := dbms_sql.execute(c); dbms_sql.describe_columns(c, col_cnt, rec_tab);/* Following loop could simply be for j in 1.col_cnt loop.* Here we are simply illustrating some of the PL/SQL table* features.*/ col_num := rec_tab.first; IF (col_num IS NOT NULL) THEN LOOP print_rec(rec_tab(col_num); col_num := rec_tab.next(col_num); EXIT WHEN (col_num is null); END LOOP; END IF; dbms_sql.close_cursor(c);end;/DESCRIBE_COLUMNS2Describes the specified column, an alternative methoddbms_sql.describe_columns2 (c IN INTEGER,col_cnt OUT INTEGER,desc_tab2 OUT DESC_TAB);Why? ResearchEXECUTEExecute dynamic SQL cursordbms_sql.execute(c IN INTEGER) RETURN INTEGER;DECLARE sqlstr VARCHAR2(50); tCursor PLS_INTEGER; RetVal NUMBER;BEGIN sqlstr := DROP SYNONYM my_synonym; tCursor := dbms_sql.open_cursor; dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE); RetVal := dbms_sql.execute(tCursor); dbms_sql.close_cursor(tCursor);END;/EXECUTE_AND_FETCHExecutes a given cursor and fetch rowsdbms_sql.execute_and_fetch(c IN INTEGER,exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;Combine demo w/ last_row_count and last_row_id demosFETCH_ROWSFetches a row from a given cursordbms_sql.fetch_rows(c IN INTEGER) RETURN INTEGER;See final demoIS_OPENDetermine whether a cursor is opendbms_sql.is_open(c IN INTEGER) RETURN BOOLEAN;set serveroutput onDECLARE tCursor PLS_INTEGER;BEGIN tCursor := dbms_sql.open_cursor; IF dbms_sql.is_open(tCursor) THEN dbms_output.put_line(1-OPEN); ELSE dbms_output.put_line(1-CLOSED); END IF; dbms_sql.close_cursor(tCursor); IF dbms_sql.is_open(tCursor) THEN dbms_output.put_line(2-OPEN); ELSE dbms_output.put_line(2-CLOSED); END IF;END;/LAST_ERROR_POSITIONReturns byte offset in the SQL statement text where the error occurreddbms_sql.last_error_position RETURN INTEGER;LAST_ROW_COUNTReturns cumulative count of the number of rows fetcheddbms_sql.last_row_count RETURN INTEGER;LAST_ROW_IDReturns ROWID of last row processeddbms_sql.last_row_id RETURN ROWID;LAST_SQL_FUNCTION_CODEReturns SQL function code for statementdbms_sql.last_sql_function_code RETURN INTEGER;OPEN_CURSOROpen dynamic SQL cursor and return cursor ID number of new cursordbms_sql.open_cursor RETURN INTEGER;See is_open demoPARSEParse statementOverload 1dbms_sql.parse(, ,dbms_sql.NATIVE);CREATE SYNONYM test_syn FOR dual;SELECT *FROM test_syn;SELECT synonym_nameFROM user_synonyms;DECLARE sqlstr VARCHAR2(50); tCursor PLS_INTEGER;BEGIN sqlstr := DROP SYNONYM test_syn; tCursor := dbms_sql.open_cursor; dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE); dbms_sql.close_cursor(tCursor);END;/SELECT synonym_nameFROM user_synonyms;- with returning clausecreate or replace procedure single_Row_insert(c1 number, c2 number, r out number) isc number;n number;beginc := dbms_sql.open_cursor;dbms_sql.parse(c, insert into tab values (:bnd1, :bnd2) |returning c1*c2 into :bnd3, 2);dbms_sql.bind_variable(c, bnd1, c1);dbms_sql.bind_variable(c, bnd2, c2);dbms_sql.bind_variable(c, bnd3, r);n := dbms_sql.execute(c);dbms_sql.variable_value(c, bnd3, r); - get value of outbind variabledbms_Sql.close_Cursor(c);end;/Parse statementOverload 2dbms_sql.parse(c IN INTEGER,statement IN VARCHAR2A,lb IN INTEGER,ub IN INTEGER,lfflg IN BOOLEAN,language_flag IN INTEGER);Parse statementOverload 3dbms_sql.parse(c IN INTEGER,statement IN VARCHAR2S,lb IN INTEGER,ub IN INTEGER,lfflg IN BOOLEAN,language_flag IN INTEGER);VARIABLE_VALUEReturns value of named variable for given cursorOverload 1dbms_sql.variable_value(c IN INTEGER,name IN VARCHAR2,value OUT );Returns value of named variable for given cursorOverload 2dbms_sql.variable_value(c IN INTEGER,name IN VARCHAR2, IN );VARIABLE_VALUE_CHARUndocdbms_sql.variable_value_char(c IN INTEGER,name IN VARCHAR2,value OUT CHAR CHARACTER SET ANY_CS);VARIABLE_VALUE_RAWUndocdbms_sql.variable_value_raw(c IN INTEGER,name IN VARCHAR2,value OUT RAW);VARIABLE_VALUE_ROWIDUndocdbms_sql.variable_value_rowid(c IN INTEGER,name IN VARCHAR2,value OUT ROWID);DemosDrop Synonym DemoSELECT synonym_name FROM user_synonyms;CREATE SYNONYM d FOR dept;CREATE SYNONYM e FOR emp;SELECT synonym_name FROM user_synonyms;CREATE OR REPLACE PROCEDURE dropsyn ISCURSOR syn_cur ISSELECT synonym_nameFROM user_synonyms;RetVal NUMBER;sqlstr VARCHAR2(200);tCursor PLS_INTEGER;BEGIN FOR syn_rec IN syn_cur LOOP sqlstr := DROP SYNONYM | syn_rec.synonym_name; tCursor := dbms_sql.open_cursor; dbms_sql.parse(tCursor, sqlstr, dbms_sql.NATIVE); RetVal := dbms_sql.execute(tCursor); dbms_sql.close_cursor(tCursor); END LOOP;EXCEPTION WHEN OTHERS THEN RAISE;END dropsyn;/exec dropsyn;SELECT synonym_name FROM user_synonyms;Executing CLOBS Demo TablesCREATE TABLE workstations (srvr_id NUMBER(10),ws_id NUMBER(10),location_id NUMBER(10),cust_id VARCHAR2(15),status VARCHAR2(1),latitude FLOAT(20),longitude FLOAT(20),netaddress VARCHAR2(15)TABLESPACE data_sml;CREATE TABLE test (test NUMBER(10);Run DemoCREATE OR REPLACE PROCEDURE execute_plsql_block(plsql_code_block CLOB) ISds_cur PLS_INTEGER := dbms_sql.open_cursor;sql_table dbms_sql.VARCHAR2S;c_buf_len CONSTANT BINARY_INTEGER := 256;v_accum INTEGER := 0;v_beg INTEGER := 1;v_end INTEGER := 256;v_loblen PLS_INTEGER;v_RetVal PLS_INTEGER;- local function to the execut