要加***处一句
function get (p_id varchar2, p_rc OUT myrctype)
*************************************
return varchar2(500)
**************************************
IS
sqlstr VARCHAR2(500);
BEGIN
IF p_id is null THEN
OPEN p_rc FOR SELECT * FROM FB;
ELSE
sqlstr := 'SELECT * FROM FB WHERE ID =:w_id';
OPEN p_rc FOR sqlstr using p_id;
END IF;
return p_rc;
END get;
END PKG_TEST;
function get (p_id varchar2, p_rc OUT myrctype)
*************************************
return varchar2(500)
**************************************
IS
sqlstr VARCHAR2(500);
BEGIN
IF p_id is null THEN
OPEN p_rc FOR SELECT * FROM FB;
ELSE
sqlstr := 'SELECT * FROM FB WHERE ID =:w_id';
OPEN p_rc FOR sqlstr using p_id;
END IF;
return p_rc;
END get;
END PKG_TEST;
OPEN p_rc FOR SELECT * FROM FB WHERE ID =p_id;
END IF;
TYPE myrctype IS REF CURSOR;
function get(p_id varchar2, p_rc OUT myrctype) ;
end PKG_TEST;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
function get (p_id varchar2, p_rc OUT myrctype)
IS
sqlstr VARCHAR2(500);
BEGIN
IF p_id is null THEN
OPEN p_rc FOR SELECT * FROM FB;
ELSE
sqlstr := 'SELECT * FROM FB WHERE ID =:w_id';
OPEN p_rc FOR sqlstr using p_id;
END IF; END get;
END PKG_TEST;
/不用返回值,OUT参数就可以返回游标了
TYPE myrctype IS REF CURSOR; procedure get(p_id varchar2, p_rc OUT PKG_TEST.myrctype);
end PKG_TEST;CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
procedure get (p_id varchar2, p_rc OUT PKG_TEST.myrctype)
is
sqlstr VARCHAR2(500);
BEGIN
IF p_id is null THEN
OPEN p_rc FOR SELECT * FROM FB;
ELSE
--OPEN p_rc FOR SELECT * FROM FB where ID = p_id;
sqlstr := 'SELECT * FROM FB WHERE ID =:w_id';
OPEN p_rc FOR sqlstr using p_id;
END IF;
END get;
END PKG_TEST;编译错误:
Compilation errors for PACKAGE BODY SYSTEM.PKG_TESTError: PLS-00103: 出现符号 "SQLSTR"在需要下列之一时:
select
Line: 12
Text: OPEN p_rc FOR sqlstr using p_id;但是使用OPEN p_rc FOR SELECT * FROM FB where ID = p_id; 就可以;
是什么原因无法使用上述语句(OPEN p_rc FOR sqlstr using p_id;)以前我一直使用SQL Server,Sybase,现在刚学习ORACLE,希望大家能为我耐心解析,多谢了.
执行select * from v$version得到的信息:Oracle8 Enterprise Edition Release 8.0.6.0.0 - Production
PL/SQL Release 8.0.6.0.0 - Production
CORE Version 4.0.6.0.0 - Production
TNS for 32-bit Windows: Version 8.0.6.0.0 - Production
NLSRTL Version 3.3.3.0.0 - Production
另外的你的函数少了return varchar2
TYPE myrctype IS REF CURSOR;
PROCEDURE get(p_id varchar2, p_rc OUT myrctype) ;
end PKG_TEST;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
PROCEDURE get (p_id varchar2, p_rc OUT myrctype)
IS
sqlstr VARCHAR2(500);
BEGIN
IF p_id is null THEN
OPEN p_rc FOR SELECT * FROM FB;
ELSE
sqlstr := 'SELECT * FROM FB WHERE ID =:w_id';
OPEN p_rc FOR sqlstr using p_id;
END IF; END get;
END PKG_TEST;
/那可能是版本太底了,我测试使用9I可以编译通过。