FROM ORACLE8I DOCUMENTATIONCREATE 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;
/
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;
/
解决方案 »
- 请教大家1条SQL查询
- 求助帮忙优化一条SQL语句
- 头疼致死的问题!!
- 请教:Oracle中临时表替代方案?
- 在eclipse中用JDBC连接oracle数据库总是提示Exception in thread "main" java.lang.ClassNotFoundE
- 修改sys密码不可以,为什么》
- procedure的问题!
- oracle8i for windowsNT实用指南上第四章的source code。看不懂。
- 请问大虾:oracle 有没有象 sql server 那样有 attach database 功能?
- Oracle SQL语句优化
- 数据类型blob的问题
- 如何用UTL_FILE.FOPEN打开客户端的文件?急!
fetch cursor001 into Vabc exit when cursor001%notfound;
end loop;
close cursor001;
里面的:id_bind是什么??
1.表
SQL> desc test;
名称 空? 类型
----------------------------------------- -------- -------------------
A VARCHAR2(5)
B VARCHAR2(10)SQL> select * from test;A B
----- ----------
01 aa
02 bb2.包
CREATE OR REPLACE package pkg_test as/* 定义ref cursor类型
不加return类型,为弱类型,允许动态sql查询, 否则为强类型,无法使用动态sql查询;*/
type myrctype is ref cursor; --函数申明
function get(intID number)
return myrctype;
end pkg_test;
/ CREATE OR REPLACE package body pkg_test as--函数体
function get(intID number) return myrctype is
rc myrctype; --定义ref cursor变量
sqlstr varchar2(500);
begin
if intID=0 then --静态测试,直接用select语句直接返回结果
open rc for select a,b from test;
else --动态sql赋值,用:w_id来申明该变量从外部获得
sqlstr := 'select a,b from test where a =:w_id';
--动态测试,用sqlstr字符串返回结果,用using关键词传递参数
open rc for sqlstr using intid;
end if;
return rc;
end get;
end pkg_test;
/3.测试 1 declare
2 w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量,用于显示结果
3 w_cod test.a%type;
4 w_name test.b%type;
5 begin --调用函数,获得记录集
6 w_rc := pkg_test.get(0); --fetch结果并显示
7 loop
8 fetch w_rc into w_cod,w_name;
9 exit when w_rc%notfound;
10 dbms_output.put_line(w_name);
11 end loop;
12* end;
SQL> /
aa
bbPL/SQL 过程已成功完成。
1 declare
2 w_rc pkg_test.myrctype; --定义ref cursor型变量 --定义临时变量,用于显示结果
3 w_cod test.a%type;
4 w_name test.b%type;
5 begin --调用函数,获得记录集
6 w_rc := pkg_test.get('02'); --fetch结果并显示
7 loop
8 fetch w_rc into w_cod,w_name;
9 exit when w_rc%notfound;
10 dbms_output.put_line(w_name);
11 end loop;
12* end;
SQL> /
bbPL/SQL 过程已成功完成。转自:弱水
//定义
CURSOR cBaud IS
SELECT c_BaudCode,c_BaudName
FROM TBDT_Baud
ORDER BY c_BaudCode;
//调用
OPEN cBaud;
LOOP
FETCH cBaud INTO c_VtBaudCode,c_VtBaudName;
EXIT WHEN cBaud%NOTFOUND;
//处理
...
//
END LOOP;
CLOSE cBaud;
推荐一本《ORACLE8 PL/SQL程序设计》
SCOTT.URMAN
机械工业出版社
上面多的是例子!