在oracle8.0.5中:
用dbms_sql来实现,例子:
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;
用dbms_sql来实现,例子:
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;
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_cur (
cursor IN OUT emp_cur_type,
dept_num IN number);
END emp_demo_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS PROCEDURE open_cur (
cursor IN OUT emp_cur_type,
dept_num IN number) IS
BEGIN
OPEN cursor FOR SELECT * FROM emp
WHERE deptno = dept_num
ORDER BY ename ASC;
END;
END emp_demo_pkg;
/
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
PROCEDURE open_cur (
cursor IN OUT emp_cur_type,
dept_num IN number);
END emp_demo_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS PROCEDURE open_cur (
cursor IN OUT emp_cur_type,
dept_num IN number) IS
BEGIN
OPEN cursor FOR SELECT * FROM emp
WHERE deptno = dept_num
ORDER BY ename ASC;
END;
END emp_demo_pkg;
/
call demo("select * from information")
CREATE OR REPLACE PROCEDURE demo(str VARCHAR2()) AS
BEGIN
str;
END demo;
意思大概如此,语法格式什么的不对,但希望能表述清楚,在线等待望指条明道!!!
你调用这个过程好像没有什么意义,不知你的真正用意何在?
CREATE OR REPLACE PACKAGE PKGLOBALUNIT
AS
type gCursor is ref cursor;
--function HextoNumber(vInHex in varchar2) return number;
END PKGLOBALUNIT;
/2. 建立表
CREATE TABLE MESSAGEBOARD (
SID NUMBER (10),
NAME VARCHAR2 (20),
Tile Varchar2(100),
MESSAGE VARCHAR2 (3000),
RECORDTIME DATE ) ;
/3. 建立存储过程
CREATE OR REPLACE PROCEDURE PRMessageBoard
(
tCursor out pkglobalunit.gCursor
)
as
begin
open tCursor for
select * from messageboard ;
end;
/4.应用程序调用建立的存储过程就返回需要的记录数 .
type t_cur is cursor ;
function fn_GetResult(tableName varchar2 ) return t_cur;
end pk_test;
/
create package body pk_test as
function fn_GetResult(tableName varchar2) return t_cur
is
rs t_cur;
begin
open rs for 'select * from ' || tableName;
return rs ;
Exception
when others then
raise;
end fn_GetResult;
end pk_test;
/
it can return a result , call by client application !
动态游标算法如下:(dbms_sql)
1)将SQL语句或PL/SQL块放入到一个字符串中。
2)使用DBMS_SQL.PARSE分析该字符串。
3)使用DBMS_SQL.BIND_VARIABLE联编输入变量。
4)如果该语句不是查询语句,使用DBMS_SQL.EXECUTE和DBMS_SQL.VARIABLE_VALUE执行它。如果是查询语句,继续执行5。
5)如果是查询语句,使用DBMS_SQL.DEFINE_COLUMN定义输出变量。
6)使用DBMS_SQL.EXECUTE,DBMS_SQL.FETCH_ROWS,DBMS_SQL.COLUMN_VALUE以及DBMS_SQL.VARIABLE_VALUE执行查询并提取结果。