CREATE OR REPLACE PROCEDURE assign_value (newval_in IN NUMBER,p_out out number)
IS
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk PLS_INTEGER;
sqlstr varchar2(100);
BEGIN
sqlstr:='select first_name from students where rownum<='|| ':b';
DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE (cur, ':b', newval_in,100);
fdbk := DBMS_SQL.EXECUTE (cur);
DBMS_SQL.CLOSE_CURSOR (cur);
p_out:=fdbk;
END;--加一输出参数,把执行的结果输出.
IS
cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
fdbk PLS_INTEGER;
sqlstr varchar2(100);
BEGIN
sqlstr:='select first_name from students where rownum<='|| ':b';
DBMS_SQL.PARSE(cur, sqlstr, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE (cur, ':b', newval_in,100);
fdbk := DBMS_SQL.EXECUTE (cur);
DBMS_SQL.CLOSE_CURSOR (cur);
p_out:=fdbk;
END;--加一输出参数,把执行的结果输出.
------------
-- OVERVIEW
--
-- This package provides a means to use dynamic SQL to access the database.
-- -------------------------
-- RULES AND LIMITATIONS
--
-- Bind variables of a SQL statement are identified by their names.
-- When binding a value to a bind variable, the string identifying
-- the bind variable in the statement may optionally contain the
-- leading colon. For example, if the parsed SQL statement is
-- "SELECT ENAME FROM EMP WHERE SAL > :X", on binding the variable
-- to a value, it can be identified using either of the strings ':X'
-- and 'X'.
--
-- Columns of the row being selected in a SELECT statement are identified
-- by their relative positions (1, 2, 3, ...) as they appear on the select
-- list from left to right.
--
-- Privileges are associated with the caller of the procedures/functions
-- in this package as follows:
-- If the caller is an anonymous PL/SQL block, the procedures/functions
-- are run using the privileges of the current user.
-- If the caller is a stored procedure, the procedures/functions are run
-- using the privileges of the owner of the stored procedure.
--
-- WARNING: Using the package to dynamically execute DDL statements can
-- results in the program hanging. For example, a call to a procedure in a
-- package will result in the package being locked until the execution
-- returns to the user side. Any operation that results in a conflicting
-- lock, such as dynamically trying to drop the package, before the first
-- lock is released will result in a hang.
--
-- The flow of procedure calls will typically look like this:
--
-- -----------
-- | open_cursor |
-- -----------
-- |
-- |
-- v
-- -----
-- ------------>| parse |
-- | -----
-- | |
-- | |---------
-- | v |
-- | -------------- |
-- |-------->| bind_variable | |
-- | ^ ------------- |
-- | | | |
-- | -----------| |
-- | |<--------
-- | v
-- | query?---------- yes ---------
-- | | |
-- | no |
-- | | |
-- | v v
-- | ------- -------------
-- |----------->| execute | ->| define_column |
-- | ------- | -------------
-- | |------------ | |
-- | | | ----------|
-- | v | v
-- | -------------- | -------
-- | ->| variable_value | | ------>| execute |
-- | | -------------- | | -------
-- | | | | | |
-- | ----------| | | |
-- | | | | v
-- | | | | ----------
-- | |<----------- |----->| fetch_rows |
-- | | | ----------
-- | | | |
-- | | | v
-- | | | --------------------
-- | | | | column_value |
-- | | | | variable_value |
-- | | | ---------------------
-- | | | |
-- | |<--------------------------
-- | |
-- -----------------|
-- |
-- v
-- ------------
-- | close_cursor |
-- ------------
--
---------------
------------
-- EXAMPLES
--
-- 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;
-- rows_processed 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.define_column(source_cursor, 1, id);
-- dbms_sql.define_column(source_cursor, 2, name, 30);
-- dbms_sql.define_column(source_cursor, 3, birthdate);
-- rows_processed := 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)');
--
-- -- 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 which insert
-- -- into the destination table
-- dbms_sql.bind_variable(destination_cursor, 'id', id);
-- dbms_sql.bind_variable(destination_cursor, 'name', name);
-- dbms_sql.bind_variable(destination_cursor, 'birthdate', birthdate);
-- rows_processed := 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;
--