使用Oracle的DBMS_SQL包执行动态SQL语句
使用Oracle的DBMS_SQL包执行动态SQL语句
引用自:http://blog.csdn.net/ggjjzhzz/archive/2005/10/17/507880.aspx
引用自:http://www.cnblogs.com/simonhaninmelbourne/archive/2013/01/23/2872438.html
在某些场合下,存储过程或触发器里的SQL语句需要动态生成。Oracle的DBMS_SQL包可以用来执行动态SQL语句。本文通过一个简单的例子来展示如何利用DBMS_SQL包执行动态SQL语句:
DECLARE
|
结果:
3095;S051013XW00010;15-10月-05 3112;A051013XW00027;10-10月-05 3113;A051013XW00028;13-10月-05 3116;S051013XW00031;13-10月-05 |
附:DBMS_SQL的文档(来源:http://www.psoug.org)
Oracle DBMS_SQL |
||
Version 10.2 |
General | |||||||||||||
Note: 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. |
|||||||||||||
Purpose | |||||||||||||
Source | {ORACLE_HOME}/rdbms/admin/dbmssql.sql | ||||||||||||
Constants |
|
||||||||||||
Defined Data Types | General Types TYPE 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_rec INDEX BY binary_integer; TYPE desc_tab2 IS TABLE OF desc_rec2 INDEX 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 Types TYPE Bfile_Table IS TABLE OF bfile INDEX BY binary_integer; TYPE Binary_Double_Table IS TABLE OF binary_double INDEX BY binary_integer; TYPE Binary_Float_Table IS TABLE OF binary_float INDEX BY binary_integer; TYPE Blob_Table IS TABLE OF blob INDEX BY binary_integer; TYPE Clob_Table IS TABLE OF clob INDEX BY binary_integer; TYPE Date_Table IS TABLE OF date INDEX BY binary_integer; TYPE interval_day_to_second_Table IS TABLE OF dsinterval_unconstrained INDEX BY binary_integer; TYPE interval_year_to_MONTH_Table IS TABLE OF yminterval_unconstrained INDEX BY binary_integer; TYPE Number_Table IS TABLE OF NUMBER INDEX BY binary_integer; TYPE time_Table IS TABLE OF time_unconstrained INDEX 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_unconstrained INDEX BY binary_integer; TYPE timestamp_with_ltz_table IS TABLE OF TIMESTAMP_LTZ_UNCONSTRAINED INDEX BY binary_integer; TYPE Urowid_Table IS TABLE OF urowid INDEX BY binary_integer; TYPE timestamp_with_time_zone_table IS TABLE OF TIMESTAMP_TZ_UNCONSTRAINED INDEX BY binary_integer; TYPE Varchar2_Table IS TABLE OF VARCHAR2(2000) INDEX BY binary_integer; |
||||||||||||
Dependencies | SELECT name FROM dba_dependencies WHERE referenced_name = ‘DBMS_SQL‘ UNION SELECT referenced_name FROM dba_dependencies WHERE name = ‘DBMS_SQL‘; |
||||||||||||
Exceptions |
|
||||||||||||
BIND_ARRAY | |||||||||||||
Binds a given value to a given collection | dbms_sql.BIND_ARRAY( c IN INTEGER, name IN VARCHAR2, <table_variable> IN <datatype> [,index1 IN INTEGER, index2 IN INTEGER)]); |
||||||||||||
DECLARE stmt 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; RAISE END; / |
|||||||||||||
BIND_VARIABLE | |||||||||||||
Binds a given value to a given variable | dbms_sql.bind_variable ( c IN INTEGER, name IN VARCHAR2, value IN <datatype>) |
||||||||||||
CREATE OR REPLACE PROCEDURE demo(salary IN NUMBER) AS cursor_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_CHAR | |||||||||||||
Binds a given value to a given variable | dbms_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 demo | |||||||||||||
BIND_VARIABLE_RAW | |||||||||||||
Binds a given value to a given variable | dbms_sql.bind_variable_raw ( c IN INTEGER, name IN VARCHAR2, value IN RAW [,out_value_size IN INTEGER]); |
||||||||||||
See bind_variable demo | |||||||||||||
BIND_VARIABLE_ROWID | |||||||||||||
Binds a given value to a given variable | dbms_sql.bind_variable_rowid ( c IN INTEGER, name IN VARCHAR2, value IN ROWID); |
||||||||||||
See bind_variable demo | |||||||||||||
CLOSE_CURSOR | |||||||||||||
Closes cursor and free memory | dbms_sql.close_cursor(c IN OUT INTEGER); | ||||||||||||
See is_open demo | |||||||||||||
COLUMN_VALUE | |||||||||||||
Returns value of the cursor element for a given position in a cursor | dbms_sql.column_value ( c IN INTEGER, position IN INTEGER, value OUT <datatype> [,column_error OUT NUMBER] [,actual_length OUT INTEGER]); |
||||||||||||
See final demo | |||||||||||||
COLUMN_VALUE_CHAR | |||||||||||||
Returns value of the cursor element for a given position in a cursor | dbms_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 demo | |||||||||||||
COLUMN_VALUE_LONG | |||||||||||||
Returns a selected part of a LONG column, that has been defined using DEFINE_COLUMN_LONG | dbms_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 demo | |||||||||||||
COLUMN_VALUE_RAW | |||||||||||||
Returns value of the cursor element for a given position in a cursor | dbms_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 demo | |||||||||||||
COLUMN_VALUE_ROWID | |||||||||||||
Undoc | dbms_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 demo | |||||||||||||
DEFINE_ARRAY | |||||||||||||
Defines a collection to be selected from the given cursor, used only with SELECT statements | dbms_sql.define_array ( c IN INTEGER, position IN INTEGER, <table_variable> IN <datatype> cnt IN INTEGER, lower_bnd IN INTEGER); |
||||||||||||
DECLARE c 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_COLUMN | |||||||||||||
Defines a column to be selected from the given cursor, used only with SELECT statements | dbms_sql.define_column ( c IN INTEGER, position IN INTEGER, column IN <datatype>) |
||||||||||||
See final demo | |||||||||||||
DEFINE_COLUMN_CHAR | |||||||||||||
Undoc | dbms_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 demo | |||||||||||||
DEFINE_COLUMN_LONG | |||||||||||||
Defines a LONG column to be selected from the given cursor, used only with SELECT statements | dbms_sql.define_column_long ( c IN INTEGER, position IN INTEGER); |
||||||||||||
See define_column in final demo | |||||||||||||
DEFINE_COLUMN_RAW | |||||||||||||
Undoc | dbms_sql.define_column_raw ( c IN INTEGER, position IN INTEGER, column IN RAW, column_size IN INTEGER); |
||||||||||||
See define_column in final demo | |||||||||||||
DEFINE_COLUMN_ROWID | |||||||||||||
Undoc | dbms_sql.define_column_rowid ( c IN INTEGER, position IN INTEGER, column IN ROWID); |
||||||||||||
See define_column in final demo | |||||||||||||
DESCRIBE_COLUMNS | |||||||||||||
Describes the columns for a cursor opened and parsed through DBMS_SQL | dbms_sql.describe_columns ( c IN INTEGER, col_cnt OUT INTEGER, desc_t OUT DESC_TAB); |
||||||||||||
DECLARE c 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) IS BEGIN 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) then dbms_output.put_line(‘true‘); else dbms_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_COLUMNS2 | |||||||||||||
Describes the specified column, an alternative method | dbms_sql.describe_columns2 ( c IN INTEGER, col_cnt OUT INTEGER, desc_tab2 OUT DESC_TAB); |
||||||||||||
Why? Research | |||||||||||||
EXECUTE | |||||||||||||
Execute dynamic SQL cursor | dbms_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_FETCH | |||||||||||||
Executes a given cursor and fetch rows | dbms_sql.execute_and_fetch(c IN INTEGER, exact IN BOOLEAN DEFAULT FALSE) RETURN INTEGER; |
||||||||||||
Combine demo w/ last_row_count and last_row_id demos | |||||||||||||
FETCH_ROWS | |||||||||||||
Fetches a row from a given cursor | dbms_sql.fetch_rows(c IN INTEGER) RETURN INTEGER; | ||||||||||||
See final demo | |||||||||||||
IS_OPEN | |||||||||||||
Determine whether a cursor is open | dbms_sql.is_open(c IN INTEGER) RETURN BOOLEAN; | ||||||||||||
set serveroutput on DECLARE 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_POSITION | |||||||||||||
Returns byte offset in the SQL statement text where the error occurred | dbms_sql.last_error_position RETURN INTEGER; | ||||||||||||
LAST_ROW_COUNT | |||||||||||||
Returns cumulative count of the number of rows fetched | dbms_sql.last_row_count RETURN INTEGER; | ||||||||||||
LAST_ROW_ID | |||||||||||||
Returns ROWID of last row processed | dbms_sql.last_row_id RETURN ROWID; | ||||||||||||
LAST_SQL_FUNCTION_CODE | |||||||||||||
Returns SQL function code for statement | dbms_sql.last_sql_function_code RETURN INTEGER; | ||||||||||||
OPEN_CURSOR | |||||||||||||
Open dynamic SQL cursor and return cursor ID number of new cursor | dbms_sql.open_cursor RETURN INTEGER; | ||||||||||||
See is_open demo | |||||||||||||
PARSE | |||||||||||||
Parse statement Overload 1 |
dbms_sql.parse(<cursor_variable>, <sql_string>, dbms_sql.NATIVE); |
||||||||||||
CREATE SYNONYM test_syn FOR dual; SELECT * FROM test_syn; SELECT synonym_name FROM 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_name FROM user_synonyms; |
|||||||||||||
-- with returning clause create or replace procedure single_Row_insert (c1 number, c2 number, r out number) is c number; n number; begin c := 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 variable dbms_Sql.close_Cursor(c); end; / |
|||||||||||||
Parse statement Overload 2 |
dbms_sql.parse( c IN INTEGER, statement IN VARCHAR2A, lb IN INTEGER, ub IN INTEGER, lfflg IN BOOLEAN, language_flag IN INTEGER); |
||||||||||||
Parse statement Overload 3 |
dbms_sql.parse( c IN INTEGER, statement IN VARCHAR2S, lb IN INTEGER, ub IN INTEGER, lfflg IN BOOLEAN, language_flag IN INTEGER); |
||||||||||||
VARIABLE_VALUE | |||||||||||||
Returns value of named variable for given cursor Overload 1 |
dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, value OUT <datatype>); |
||||||||||||
Returns value of named variable for given cursor Overload 2 |
dbms_sql.variable_value( c IN INTEGER, name IN VARCHAR2, <table_varaible> IN <datatype>); |
||||||||||||
VARIABLE_VALUE_CHAR | |||||||||||||
Undoc | dbms_sql.variable_value_char( c IN INTEGER, name IN VARCHAR2, value OUT CHAR CHARACTER SET ANY_CS); |
||||||||||||
VARIABLE_VALUE_RAW | |||||||||||||
Undoc | dbms_sql.variable_value_raw( c IN INTEGER, name IN VARCHAR2, value OUT RAW); |
||||||||||||
VARIABLE_VALUE_ROWID | |||||||||||||
Undoc | dbms_sql.variable_value_rowid( c IN INTEGER, name IN VARCHAR2, value OUT ROWID); |
||||||||||||
Demos | |||||||||||||
Drop Synonym Demo |
SELECT 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 IS CURSOR syn_cur IS SELECT synonym_name FROM 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 Tables | CREATE 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 Demo |
CREATE OR REPLACE PROCEDURE execute_plsql_block( plsql_code_block CLOB) IS ds_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 execute_plsql_block procedure FUNCTION next_row ( clob_in IN CLOB, len_in IN INTEGER, off_in IN INTEGER) RETURN VARCHAR2 IS BEGIN RETURN DBMS_LOB.SUBSTR(clob_in, len_in, off_in); END next_row; --------------------------- BEGIN v_loblen := DBMS_LOB.GETLENGTH(plsql_code_block); INSERT INTO test VALUES (v_loblen); COMMIT; LOOP -- Set the length to the remaining size -- if there are < c_buf_len characters remaining. IF v_accum + c_buf_len > v_loblen THEN v_end := v_loblen - v_accum; END IF; sql_table(NVL(sql_table.LAST, 0) + 1) := next_row(plsql_code_block, v_end, v_beg); v_beg := v_beg + c_BUF_LEN; v_accum := v_accum + v_end; IF v_accum >= v_loblen THEN EXIT; END IF; END LOOP; -- Parse the pl/sql and execute it dbms_sql.parse(ds_cur, sql_table, sql_table.FIRST, sql_table.LAST, FALSE, dbms_sql.NATIVE); v_RetVal := dbms_sql.execute(ds_cur); dbms_sql.close_cursor(ds_cur); END execute_plsql_block; / |
||||||||||||
Executing CLOBS Demo Data |
DECLARE clob_in CLOB; BEGIN clob_in := CAST( ‘BEGIN INSERT INTO WORKSTATIONS VALUES (1,1,20075,‘‘‘‘,‘‘Y‘‘,32.97948,-117.2569,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (1,10,20077,‘‘‘‘,‘‘N‘‘,32.97125,-117.2675,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (1,11,20078,‘‘‘‘,‘‘N‘‘,33.03865,-96.83579,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (1,12,20079,‘‘‘‘,‘‘Y‘‘,32.97413,-117.2694,‘‘10.128.48.121‘‘); INSERT INTO WORKSTATIONS VALUES (1,2,20081,‘‘‘‘,‘‘N‘‘,32.97948,-117.2569,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (1,3,20082,‘‘‘‘,‘‘Y‘‘,32.97948,-117.2569,‘‘10.128.0.1‘‘); INSERT INTO WORKSTATIONS VALUES (1,4,20083,‘‘15689‘‘,‘‘N‘‘,32.98195,-117.2636,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (1,5,20085,‘‘‘‘,‘‘Y‘‘,32.98195,-117.2636,‘‘10.128.16.105‘‘); INSERT INTO WORKSTATIONS VALUES (1,6,20086,‘‘‘‘,‘‘N‘‘,32.97096,-117.2689,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (1,7,20077,‘‘‘‘,‘‘Y‘‘,32.97125,-117.2675,‘‘10.128.48.105‘‘); INSERT INTO WORKSTATIONS VALUES (1,8,20090,‘‘‘‘,‘‘N‘‘,32.97124,-117.2676,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (1,9,20092,‘‘‘‘,‘‘N‘‘,32.97023,-117.2688,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (10,1,20094,‘‘‘‘,‘‘Y‘‘,61.2224,-149.8047,‘‘10.128.112.1‘‘); INSERT INTO WORKSTATIONS VALUES (10,2,20095,‘‘‘‘,‘‘N‘‘,61.2224,-149.8047,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (10,3,20096,‘‘‘‘,‘‘Y‘‘,61.2224,-149.8047,‘‘10.128.112.113‘‘); INSERT INTO WORKSTATIONS VALUES (10,4,13545,‘‘‘‘,‘‘Y‘‘,61.14104,-149.9519,‘‘10.128.112.121‘‘); INSERT INTO WORKSTATIONS VALUES (10,5,20104,‘‘‘‘,‘‘N‘‘,61.2224,-149.8047,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (10,6,20106,‘‘‘‘,‘‘Y‘‘,61.21685,-149.8002,‘‘10.128.80.113‘‘); INSERT INTO WORKSTATIONS VALUES (11,1,20110,‘‘‘‘,‘‘N‘‘,61.137,-149.9395,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,10,20113,‘‘‘‘,‘‘N‘‘,61.14104,-149.9519,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,11,20116,‘‘‘‘,‘‘N‘‘,61.14104,-149.9519,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,12,20117,‘‘‘‘,‘‘Y‘‘,61.137,-149.9395,‘‘10.128.32.193‘‘); INSERT INTO WORKSTATIONS VALUES (11,13,20118,‘‘‘‘,‘‘Y‘‘,61.137,-149.9395,‘‘10.128.16.129‘‘); INSERT INTO WORKSTATIONS VALUES (11,14,20119,‘‘‘‘,‘‘Y‘‘,61.137,-149.9395,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,15,20121,‘‘‘‘,‘‘N‘‘,61.13695,-149.9396,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,16,20122,‘‘‘‘,‘‘N‘‘,61.13695,-149.9396,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,17,13545,‘‘‘‘,‘‘Y‘‘,61.14104,-149.9519,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,18,19922,‘‘‘‘,‘‘Y‘‘,61.13549,-149.959,‘‘10.128.48.153‘‘); INSERT INTO WORKSTATIONS VALUES (11,19,19923,‘‘‘‘,‘‘N‘‘,61.13422,-149.962,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,2,19924,‘‘‘‘,‘‘N‘‘,61.137,-149.9395,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,20,19925,‘‘‘‘,‘‘N‘‘,61.146,-149.9799,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,21,19926,‘‘‘‘,‘‘N‘‘,61.146,-149.9799,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,22,19927,‘‘‘‘,‘‘N‘‘,61.13695,-149.9396,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,23,19928,‘‘‘‘,‘‘N‘‘,61.14104,-149.9519,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,24,19930,‘‘‘‘,‘‘N‘‘,61.13422,-149.962,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,25,19931,‘‘‘‘,‘‘N‘‘,61.13678,-149.9644,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,26,20033,‘‘‘‘,‘‘N‘‘,61.14477,-149.9586,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,27,20034,‘‘‘‘,‘‘N‘‘,61.13466,-149.975,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,28,20035,‘‘‘‘,‘‘N‘‘,61.14142,-149.9668,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,29,20036,‘‘‘‘,‘‘N‘‘,61.13695,-149.9396,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,3,20037,‘‘‘‘,‘‘Y‘‘,61.137,-149.9395,‘‘10.128.16.105‘‘); INSERT INTO WORKSTATIONS VALUES (11,30,20038,‘‘‘‘,‘‘N‘‘,61.13695,-149.9396,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,31,20039,‘‘‘‘,‘‘N‘‘,61.13695,-149.9396,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,32,20040,‘‘‘‘,‘‘N‘‘,61.13695,-149.9396,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,33,20042,‘‘‘‘,‘‘N‘‘,61.12887,-149.9578,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,4,20043,‘‘‘‘,‘‘N‘‘,61.137,-149.9395,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,5,20044,‘‘‘‘,‘‘Y‘‘,61.137,-149.9395,‘‘10.128.32.129‘‘); INSERT INTO WORKSTATIONS VALUES (11,6,20045,‘‘‘‘,‘‘N‘‘,61.137,-149.9395,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,7,20046,‘‘‘‘,‘‘N‘‘,61.137,-149.9395,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (11,8,20047,‘‘‘‘,‘‘N‘‘,61.137,-149.9395,‘‘‘‘); --==== INSERT INTO WORKSTATIONS VALUES (11,9,20048,‘‘‘‘,‘‘Y‘‘,61.137,-149.9395,‘‘10.128.32.169‘‘); INSERT INTO WORKSTATIONS VALUES (12,1,20051,‘‘15706‘‘,‘‘Y‘‘,32.75604,-117.1201,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (12,10,20053,‘‘‘‘,‘‘N‘‘,32.75689,-117.12,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (12,100,20054,‘‘‘‘,‘‘N‘‘,32.7596,-117.124,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (12,101,20056,‘‘‘‘,‘‘N‘‘,32.75689,-117.129,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (12,102,20057,‘‘‘‘,‘‘Y‘‘,32.75677,-117.1241,‘‘10.129.112.25‘‘); INSERT INTO WORKSTATIONS VALUES (12,103,20058,‘‘‘‘,‘‘Y‘‘,32.75662,-117.124,‘‘10.129.112.33‘‘); INSERT INTO WORKSTATIONS VALUES (12,104,20060,‘‘‘‘,‘‘N‘‘,32.7571,-117.1242,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (12,105,20061,‘‘‘‘,‘‘N‘‘,32.75316,-117.1253,‘‘‘‘); INSERT INTO WORKSTATIONS VALUES (12,106,20063,‘‘‘‘,‘‘N‘‘,32.76154,-117.1251,‘‘‘‘); --==== COMMIT; END;‘ AS CLOB); execute_plsql_block(clob_in); END; / |
||||||||||||
Final Demo |
CREATE OR REPLACE PROCEDURE copy( source IN VARCHAR2, destination IN VARCHAR2) IS id_var NUMBER; name_var VARCHAR2(30); birthdate_var DATE; source_cursor INTEGER; destination_cursor INTEGER; ignore INTEGER; BEGIN -- Prepare a cursor to select from the source table: source_cursor := dbms_sql.open_cursor; dbms_sql.parse(source_cursor, ‘SELECT id, name, birthdate FROM ‘ || source, dbms_sql.NATIVE); dbms_sql.DEFINE_COLUMN(source_cursor, 1, id_var); dbms_sql.DEFINE_COLUMN(source_cursor, 2, name_var, 30); dbms_sql.DEFINE_COLUMN(source_cursor, 3, birthdate_var); ignore := dbms_sql.execute(source_cursor); -- Prepare a cursor to insert into the destination table: destination_cursor := dbms_sql.OPEN_CURSOR; dbms_sql.parse(destination_cursor, ‘INSERT INTO ‘ || destination || ‘ VALUES (:id_bind, :name_bind, :birthdate_bind)‘, dbms_sql.NATIVE); -- Fetch a row from the source table and insert it into the destination table: LOOP IF dbms_sql.FETCH_ROWS(source_cursor)>0 THEN -- get column values of the row dbms_sql.COLUMN_VALUE(source_cursor, 1, id_var); dbms_sql.COLUMN_VALUE(source_cursor, 2, name_var); dbms_sql.COLUMN_VALUE(source_cursor, 3, birthdate_var); -- Bind the row into the cursor that inserts into the destination table. You could alter this example to require the use of dynamic SQL by inserting an if condition before the bind. dbms_sql.bind_variable(destination_cursor, ‘:id_bind‘, id_var); dbms_sql.bind_variable(destination_cursor, ‘:name_bind‘, name_var); dbms_sql.bind_variable(destination_cursor, ‘:birthdate_bind‘, birthdate_var); ignore := dbms_sql.execute(destination_cursor); ELSE -- No more rows to copy: EXIT; END IF; END LOOP; -- Commit and close all cursors: COMMIT; dbms_sql.close_cursor(source_cursor); dbms_sql.close_cursor(destination_cursor); EXCEPTION WHEN OTHERS THEN IF dbms_sql.is_open(source_cursor) THEN dbms_sql.close_cursor(source_cursor); END IF; IF dbms_sql.is_open(destination_cursor) THEN dbms_sql.close_cursor(destination_cursor); END IF; RAISE; END; / |
||||||||||||
Bulk Insert Demo | DECLARE stmt varchar2(200); empno_array dbms_sql.number_table; empname_array dbms_sql.varchar2_table; c NUMBER; dummy NUMBER; BEGIN FOR i IN 0..9 LOOP empno_array(i) := 1000 + i; empname_array(I) := get_name(i); END LOOP; stmt := ‘insert into emp values(:num_array, :name_array)‘; c := dbms_sql.open_cursor; dbms_sql.parse(c, stmt, dbms_sql.NATIVE); dbms_sql.bind_array(c, ‘:num_array‘, empno_array); dbms_sql.bind_array(c, ‘:name_array‘, empname_array); 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; RAISE; END; / |
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。