oracle 执行sql,Oracle动态执行SQL

方式1

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);

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;

CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS

cursor_name INTEGER;

ret INTEGER;

BEGIN

cursor_name := DBMS_SQL.OPEN_CURSOR;

--DDL statements are executed by the parse call, which

--performs the implied commit

DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL);

ret := DBMS_SQL.EXECUTE(cursor_name);

DBMS_SQL.CLOSE_CURSOR(cursor_name);

END;

execute immediate "drop table tab_temp";

方式2

CREATE OR REPLACE PROCEDURE copy(source      IN VARCHAR2,

destination IN VARCHAR2) is

-- This procedure copies rows from a given source table to a

-- given destination table assuming that both source and

-- destination tables have the following columns:

--   - ID of type NUMBER,

--   - NAME of type VARCHAR2(30),

--   - BIRTHDATE of type DATE.

id                 NUMBER;

name               VARCHAR2(30);

birthdate          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);

DBMS_SQL.DEFINE_COLUMN(source_cursor, 1, id);

DBMS_SQL.DEFINE_COLUMN(source_cursor, 2, name, 30);

DBMS_SQL.DEFINE_COLUMN(source_cursor, 3, birthdate);

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, :name, :birthdate)',

DBMS_SQL);

-- 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);

DBMS_SQL.COLUMN_VALUE(source_cursor, 2, name);

DBMS_SQL.COLUMN_VALUE(source_cursor, 3, birthdate);

-- 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', id);

DBMS_SQL.BIND_VARIABLE(destination_cursor, 'name', name);

DBMS_SQL.BIND_VARIABLE(destination_cursor, 'birthdate',

birthdate);

ignore := DBMS_SQL.EXECUTE(destination_cursor);

ELSE

-- no more row 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;

方式3

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);

exception when others then

if dbms_sql.is_open(c) then

dbms_sql.close_cursor(c);

end if;

raise;

end;

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);

exception when others then

if dbms_sql.is_open(c) then

dbms_sql.close_cursor(c);

end if;

raise;

end;

declare

stmt varchar2(200);

emp_no_array dbms_sql.Number_Table;

emp_addr_array dbms_sql.Varchar2_Table;

c number;

dummy number;

begin

for i in 0..9 loop

emp_no_array(i) := 1000 + i;

emp_addr_array(I) := get_new_addr(i);

end loop;

stmt := 'update emp set ename = :name_array

where empno = :num_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);

exception when others then

if dbms_sql.is_open(c) then

dbms_sql.close_cursor(c);

end if;

raise;

end;

方式4

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);

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);

exception when others then

if dbms_sql.is_open(c) then

dbms_sql.close_cursor(c);

end if;

raise;

end;

declare

c       number;

d       number;

n_tab  dbms_sql.Number_Table;

d_tab1 dbms_sql.Date_Table;

v_tab  dbms_sql.Varchar2_Table;

d_tab2 dbms_sql.Date_Table;

indx number := 10;

begin

c := dbms_sql.open_cursor;

dbms_sql.parse(c, 'select * from multi_tab order by 1', dbms_sql);

dbms_sql.define_array(c, 1, n_tab,  5, indx);

dbms_sql.define_array(c, 2, d_tab1, 5, indx);

dbms_sql.define_array(c, 3, v_tab,  5, indx);

dbms_sql.define_array(c, 4, d_tab2, 5, indx);

d := dbms_sql.execute(c);

loop

d := dbms_sql.fetch_rows(c);

dbms_sql.column_value(c, 1, n_tab);

dbms_sql.column_value(c, 2, d_tab1);

dbms_sql.column_value(c, 3, v_tab);

dbms_sql.column_value(c, 4, d_tab2);

exit when d != 5;

end loop;

dbms_sql.close_cursor(c);