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

引用自:http://blog.csdn.net/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月-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
Name Data Type Value
v6 INTEGER 0
native INTEGER 1
v7 INTEGER 2
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
Error Code Reason
ORA-06562 Inconsistent types: Raised by procedure "column_value" or
"variable_value" if the type of the given out argument where to put the requested value is different from the type of the value
 
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; 
/
posted on 2013-01-23 07:34  Simon Han  阅读(20782)  评论(0)  编辑  收藏  举报

Powered by: 博客园 Copyright © 2024 Simon Han
Powered by .NET 9.0 on Kubernetes