你将FUNCTION MY_FUNCTION RETURN NUMBER; TYPE MY_CURSOR REF CURSOR;的位置换一下,改成TYPE MY_CURSOR REF CURSOR; FUNCTION MY_FUNCTION RETURN NUMBER;我刚才试了一下,位置不对会报错
PACKAGEBODY P_BODY IS FUNCTION MY_FUNCTION RETURN NUMBER IS FAC_STA NUMBER; declare v_cursor MY_cursor; BEGIN OPEN v_CURSOR FOR 'SELECT * FROM T_TABLE; RETURN(0); END MY_FUNCTION; END;
MY_CURSOR只是你声明的一个TYPE,正确的应该像这样 ----------------------------------------------- CREATE OR REPLACE PACKAGE BODY P ISFUNCTION MY_FUNCTION RETURN NUMBER IS C_MY_CURSOR MY_CURSOR; FAC_STA NUMBER; BEGIN SQL_STR := 'SELECT * FROM T_TABLE'; OPEN C_MY_CURSOR FOR SQL_STR; RETURN(0); END MY_FUNCTION;END P; -----------------------------------------------
对 应该加上v_cursor MY_cursor; 要声明引用游标类型的变量,才能使用该变量
FUNCTION MY_FUNCTION RETURN NUMBER; TYPE MY_CURSOR is REF CURSOR; SQL_STR CHAR(2000); . . . PACKAGEBODY P_BODY IS FUNCTION MY_FUNCTION RETURN NUMBER IScur_var MY_CURSOR ;FAC_STA NUMBER; BEGIN SQL_STR := 'SELECT * FROM T_TABLE'; OPEN cur_var FOR SQL_STR; RETURN(0); END MY_FUNCTION; END;
不是返回记录集就根本没需要使用包头定义游标. 直接在函数体内定义: FUNCTION MY_FUNCTION RETURN NUMBER;SQL_STR CHAR(2000); . . . PACKAGEBODY P_BODY IS FUNCTION MY_FUNCTION RETURN NUMBER IS TYPE MY_CURSOR is REF CURSOR; cur_var MY_CURSOR ;FAC_STA NUMBER; BEGIN SQL_STR := 'SELECT * FROM T_TABLE'; OPEN cur_var FOR SQL_STR; RETURN(0); END MY_FUNCTION; END;
一下动态游标在oracle9.2上可以通过,但是在oracle8.0.5上通不过 请教一下,oracle8.0.5是不是不支持动态游标? CREATE OR REPLACE PROCEDURE mydyn_cursor ( DEPT_CODE IN VARCHAR ) ISsql_select1 VARCHAR2(2000); type cursor_dyn is REF CURSOR;GET_DETAILS cursor_dyn; V_DETAILS simple_table%ROWTYPE; -- *** THIS IS WERE THE PROBLEM IS ***begin-- make sql statement for cursor, containing records from multiple tables. sql_select1 :='select emp.emp_no, dep.dept_name from employee@db2 emp, departments@db2 dep where emp.dep_code = dep.dep_code and emp.dep_code = '''||DEPT_CODE||'''';open GET_DETAILS for sql_select1; loop FETCH GET_DETAILS into V_DETAILS; EXIT WHEN GET_DETAILS%NOTFOUND;dbms_output.put_line(V_DETAILS.f1); end loop; close GET_DETAILS; END mydyn_cursor;
报 PLS-00306 "My_cursor" 类型或引用不正确
TYPE MY_CURSOR REF CURSOR;的位置换一下,改成TYPE MY_CURSOR REF CURSOR;
FUNCTION MY_FUNCTION RETURN NUMBER;我刚才试了一下,位置不对会报错
IS
FUNCTION MY_FUNCTION RETURN NUMBER IS
FAC_STA NUMBER;
declare
v_cursor MY_cursor;
BEGIN
OPEN v_CURSOR FOR 'SELECT * FROM T_TABLE;
RETURN(0);
END MY_FUNCTION;
END;
-----------------------------------------------
CREATE OR REPLACE PACKAGE BODY P
ISFUNCTION MY_FUNCTION RETURN NUMBER IS
C_MY_CURSOR MY_CURSOR;
FAC_STA NUMBER;
BEGIN
SQL_STR := 'SELECT * FROM T_TABLE';
OPEN C_MY_CURSOR FOR SQL_STR;
RETURN(0);
END MY_FUNCTION;END P;
-----------------------------------------------
应该加上v_cursor MY_cursor;
要声明引用游标类型的变量,才能使用该变量
TYPE MY_CURSOR is REF CURSOR;
SQL_STR CHAR(2000);
.
.
.
PACKAGEBODY P_BODY
IS
FUNCTION MY_FUNCTION RETURN NUMBER IScur_var MY_CURSOR ;FAC_STA NUMBER;
BEGIN
SQL_STR := 'SELECT * FROM T_TABLE';
OPEN cur_var FOR SQL_STR;
RETURN(0);
END MY_FUNCTION;
END;
FUNCTION MY_FUNCTION RETURN NUMBER;SQL_STR CHAR(2000);
.
.
.
PACKAGEBODY P_BODY
IS
FUNCTION MY_FUNCTION RETURN NUMBER IS
TYPE MY_CURSOR is REF CURSOR;
cur_var MY_CURSOR ;FAC_STA NUMBER;
BEGIN
SQL_STR := 'SELECT * FROM T_TABLE';
OPEN cur_var FOR SQL_STR;
RETURN(0);
END MY_FUNCTION;
END;
请教一下,oracle8.0.5是不是不支持动态游标?
CREATE OR REPLACE PROCEDURE mydyn_cursor
(
DEPT_CODE IN VARCHAR
)
ISsql_select1 VARCHAR2(2000);
type cursor_dyn is REF CURSOR;GET_DETAILS cursor_dyn;
V_DETAILS simple_table%ROWTYPE; -- *** THIS IS WERE THE PROBLEM IS ***begin-- make sql statement for cursor, containing records from multiple tables.
sql_select1 :='select emp.emp_no, dep.dept_name from employee@db2 emp, departments@db2 dep where emp.dep_code = dep.dep_code and emp.dep_code = '''||DEPT_CODE||'''';open GET_DETAILS for sql_select1;
loop
FETCH GET_DETAILS into V_DETAILS;
EXIT WHEN GET_DETAILS%NOTFOUND;dbms_output.put_line(V_DETAILS.f1);
end loop;
close GET_DETAILS;
END mydyn_cursor;