- 浏览: 86408 次
- 性别:
- 来自: 深圳
文章分类
最新评论
在某些场合下,存储过程或触发器里的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 |
<!--StartFragment -->
附: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; / |
发表评论
-
自定义Oracle聚集函数:模拟SUM,MAX,AVG
2005-10-15 11:57 1634实现三个函数,关键都在于处理ODCIAggregateIter ... -
配置HSQLDB数据库[服务器模式]
2005-11-03 16:28 1990<!--StartFragment -->HSQL ... -
数据库连接池的简单实现
2005-11-15 11:45 723这是我以前写的一段程序,试图按着自己对多线程与JDBC的理解, ... -
Ubuntu 6.10上安装Oracle 10g
2007-02-17 01:40 814如果以前有安装过oracle,可以通过以下命令清除: ...
相关推荐
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包;在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...
全面介绍了PL/SQL语言的包、触发器、动态SQL、外部例程、对象、集合等基本概念,而且还集中讨论了Oracle8...6) 使用动态SQL语言在运行时创建并执行SQL和PL/SQL语句。 7) 允许数据库管道与警告之间进行交互会话,等等。
DBMS_LOGMNR 是 Oracle 中的一个日志分析包,用于分析和解释redo日志文件,以便追踪和诊断数据库中的操作。下面是 DBMS_LOGMNR 的使用方法。 首先,需要安装 LogMiner 工具,包括两个脚本 dbmslm.sql 和 dbmslmd....
6) 使用动态SQL语言在运行时创建并执行SQL和PL/SQL语句。 7) 允许数据库管道与警告之间进行交互会话,等等。本书由刘谦,苏建平、王军平、刘丽云、李新、李江月、张君哲、钱云共同翻译完成,由刘谦校审和统稿。由于...
DBMS_RANDOM是Oracle提供的一个PL/SQL包,用于生成随机数据和字符。它具有以下函数。 其中,initialize,random,terminate函数在Oracle11g中已不推荐使用,主要用于向后兼容。下面对各个函数进行举例说明 1. ...
Oracle 动态 SQL 中的 EXECUTE IMMEDIATE 语句是数据库中执行动态 SQL 语句或非运行时创建的 PL/SQL 块的重要工具。与 DBMS_SQL package 相比,EXECUTE IMMEDIATE 使用较简单,能够满足较常用的需要。 EXECUTE ...
6) 使用动态SQL语言在运行时创建并执行SQL和PL/SQL语句。 7) 允许数据库管道与警告之间进行交互会话,等等。本书由刘谦,苏建平、王军平、刘丽云、李新、李江月、张君哲、钱云共同翻译完成,由刘谦校审和统稿。由于...
代码如下:/*If语句:判断用户输入的数字。*/set serveroutput on –接收键盘输入accept num prompt ‘请输入一个数字:’; declare –将屏幕输入的数字付给变量 pnum number := #begin if pnum = 0 then dbms_...
通过学习书中的代码和图例,您不仅能访问和修改数据库信息,编写功能强大的PL/SQL 语句,执行有效的查询和部署稳固的安全性,还能轻松实现C、C++ 和Java 过程,建立可启用Web 的数据库,缩短开发时间和优化性能。...
oracle有三种方式跟踪sql: 1、审计 2、trigger 3、利用 dbms_support 查看sql_trace 现在主要讲第三种方式: 参见网址:https://www.cnblogs.com/shenfeng/p/oracle_sql_trace.html,步聚如下:
通过学习本书,读者不仅可以掌握oracle常用工具oracle universal installer、net comfiguration assistant、sql developer、sql*plus的作用及使用方法,而且可以掌握sql语句和pl/sql的各种基础知识和高级特征(记录...
Oracle 8i PL SQL高级程序设计(PDF) ...6) 使用动态SQL语言在运行时创建并执行SQL和PL/SQL语句。 由于本书内容较新,篇幅较多,再加上译者的时间和水平有限,在翻译过程中难免有疏漏和错误,敬请读者给予批评指正。
通过学习本书,读者不仅可以掌握oracle常用工具oracle universal installer、net comfiguration assistant、sql developer、sql*plus的作用及使用方法,而且可以掌握sql语句和pl/sql的各种基础知识和高级特征(记录...
通过学习本书,读者不仅可以掌握Oracle常用工具Oracle Universal Installer、Net Comfiguration Assistant、SQL Developer、SQL*Plus的作用及使用方法,而且可以掌握SQL语句和PL/SQL的各种基础知识和高级特征(记录...
每个调整工具集都包含一个或几个SQL语句,以及正确解释它们所需的上下文信息。SQL Tuning Advisor用一个调整工具集作为输入,检查其中的语句并为它们提出优化建议。Oracle 10g第二版(release 2)中增加了移动SQL ...
7.1 DBMS_SQL 程序包 7.2 本机动态SQL 7.2.1 执行 DDL 语句 7.2.2 使用绑定变量 7.2.3 执行 PL/SQL 块 第八章 显示数据 8.1 DBMS_OUTPUT 程序包 8.1.1 开启屏幕显示 8.1.2 关闭屏幕显示 8.1.3 其他函数 8.1.4 引发的...
Oracle 中动态 SQL 可以通过本地动态 SQL 来执行,也可以通过 DBMS_SQL 包来执行。下面就这两种情况分别进行说明: 一、本地动态 SQL 本地动态 SQL 是使用 EXECUTE IMMEDIATE 语句来实现的。下面是一个简单的示例...
8.5.5 使用 AUTOTRACE 获得 SQL 语句 执行计划和统计数据 8.6 优化回退段 8.6.1 最小化回退段争用 8.6.2 使动态扩充最小化 8.6.3 分布回退段的 I/O 8.7 优化索引 8.7.1 怎样确定和重建产生碎片的索引 8.7.2 ...