建议用楼上所说的方法,如果你想用存储过程也可以...如下有一个例子:SQL> CREATE TABLE Emp AS SELECT * FROM scott.emp;表已创建。SQL> CREATE TABLE tstEmp AS SELECT * FROM scott.emp WHERE 1=2;表已创建。SQL> SQL> CREATE OR REPLACE PACKAGE pkg IS 2 TYPE cur IS REF CURSOR; 3 4 PROCEDURE fn_Insert(pCur cur); 5 FUNCTION fn_get(pNo VARCHAR2, pCur OUT cur) RETURN NUMBER; 6 END pkg; 7 /程序包已创建。SQL> SQL> CREATE OR REPLACE PACKAGE BODY pkg IS 2 PROCEDURE fn_Insert(pCur cur) IS 3 tEmp emp%ROWTYPE; 4 BEGIN 5 LOOP 6 FETCH pCur INTO tEmp; 7 EXIT WHEN pCur%NOTFOUND; 8 INSERT INTO tstEmp(Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno) 9 VALUES(tEmp.Empno, tEmp.Ename, tEmp.Job, tEmp.Mgr, tEmp.Hiredate, 10 tEmp.Sal, tEmp.Comm, tEmp.Deptno); 11 END LOOP; 12 CLOSE pCur; 13 COMMIT; 14 END fn_Insert; 15 16 FUNCTION fn_get(pNo VARCHAR2, pCur OUT cur) RETURN NUMBER IS 17 BEGIN 18 OPEN pCur FOR 'SELECT * FROM emp WHERE empno = '''|| pNo || ''''; 19 RETURN 1; 20 EXCEPTION 21 WHEN OTHERS THEN 22 RETURN -1; 23 END fn_get; 24 END pkg; 25 /程序包主体已创建。SQL> SQL> var c_tst refcursor; SQL> var n_tst number; SQL> begin 2 if pkg.fn_get('7839', :c_tst) = 1 then 3 pkg.fn_insert(:c_tst); 4 end if; 5 end; 6 /PL/SQL 过程已成功完成。SQL> SQL> select * from tstemp; EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- ---------- ---------- ---------- DEPTNO ---------- 7839 KING PRESIDENT 17-11月-81 5000 10SQL>
将您的 Access 数据库移植到 Oracle(官方的!!)
http://www.oracle.com/technology/global/cn/pub/notes/technote_access_migration.html
SQL> CREATE OR REPLACE PACKAGE pkg IS
2 TYPE cur IS REF CURSOR;
3
4 PROCEDURE fn_Insert(pCur cur);
5 FUNCTION fn_get(pNo VARCHAR2, pCur OUT cur) RETURN NUMBER;
6 END pkg;
7 /程序包已创建。SQL>
SQL> CREATE OR REPLACE PACKAGE BODY pkg IS
2 PROCEDURE fn_Insert(pCur cur) IS
3 tEmp emp%ROWTYPE;
4 BEGIN
5 LOOP
6 FETCH pCur INTO tEmp;
7 EXIT WHEN pCur%NOTFOUND;
8 INSERT INTO tstEmp(Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)
9 VALUES(tEmp.Empno, tEmp.Ename, tEmp.Job, tEmp.Mgr, tEmp.Hiredate,
10 tEmp.Sal, tEmp.Comm, tEmp.Deptno);
11 END LOOP;
12 CLOSE pCur;
13 COMMIT;
14 END fn_Insert;
15
16 FUNCTION fn_get(pNo VARCHAR2, pCur OUT cur) RETURN NUMBER IS
17 BEGIN
18 OPEN pCur FOR 'SELECT * FROM emp WHERE empno = '''|| pNo || '''';
19 RETURN 1;
20 EXCEPTION
21 WHEN OTHERS THEN
22 RETURN -1;
23 END fn_get;
24 END pkg;
25 /程序包主体已创建。SQL>
SQL> var c_tst refcursor;
SQL> var n_tst number;
SQL> begin
2 if pkg.fn_get('7839', :c_tst) = 1 then
3 pkg.fn_insert(:c_tst);
4 end if;
5 end;
6 /PL/SQL 过程已成功完成。SQL>
SQL> select * from tstemp; EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- ---------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-11月-81 5000
10SQL>