我在oracle 中,取游标值时,不能识别fetch命令,请教各位。CREATE OR REPLACE PACKAGE ModuleLogin AS
TYPE cur is REF CURSOR;
--FUNCTION RetuWorkTeamNo (cUserNo VARCHAR2) RETURN ModuleLogin.cur;
PROCEDURE RetuWorkTeamNo(cUserNo IN varchar2,cWordTeamNo out varchar2);
END ModuleLogin;
/CREATE OR REPLACE PACKAGE BODY ModuleLogin AS
/*
FUNCTION RetuWorkTeamNo(cUserNo in VARCHAR2) RETURN ModuleLogin.cur IS
c_temp ModuleLogin.cur;
strSql varchar2(100);
BEGIN
strSql := 'select cWorkTeamNo from smUserPrivilege where bActive=1 AND cUserNo=(select cUserNo from smUserProfile where cUserNo=''' || cUserNo || ''' and bActive=1)';
OPEN c_temp FOR strSql;
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error .....' || sqlcode || ' : ' || sqlerrm );
RETURN c_temp;
END RetuWorkTeamNo;
*/
PROCEDURE RetuWorkTeamNo(cUserNo IN varchar2,cWordTeamNo out varchar2) IS
cursor c_temp(strUserNo smUserPrivilege.cUserNo%TYPE) is select cWorkTeamNo from smUserPrivilege where cUserNo=strUserNo;--c_temp ModuleLogin.cur;
v_WrokTeamNo smUserPrivilege.cWorkTeamNo%TYPE;
strSql varchar2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('A=' || cUserNo);
--strSql := 'select cWorkTeamNo from smUserPrivilege where bActive=1 AND cUserNo=(select cUserNo from smUserProfile where cUserNo=''' || cUserNo || ''' and bActive=1)';
--strSql := 'select cWorkTeamNo from smUserPrivilege where bActive=1 AND cUserNo=(select cUserNo from smUserProfile where cUserNo:=wUserNo and bActive=1)';
--strSql := 'select cWorkTeamNo from smUserPrivilege where bActive=1';
--OPEN c_temp FOR strSql using cUserNo;
OPEN c_temp(cUserNo);
fetch c_temp into v_WorkTeamNo;
close c_temp; /*
if v_WorkTeamNo is null then
RAISE_APPLICATION_ERROR(-2002,'NO RECORD');
else
cWrokTeamNo := v_WorkTeamNo;
end if
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error .....' || sqlcode || ' : ' || sqlerrm );
-- cWordTeamNo :='aa';
*/
END RetuWorkTeamNo;
END ModuleLogin;
/
TYPE cur is REF CURSOR;
--FUNCTION RetuWorkTeamNo (cUserNo VARCHAR2) RETURN ModuleLogin.cur;
PROCEDURE RetuWorkTeamNo(cUserNo IN varchar2,cWordTeamNo out varchar2);
END ModuleLogin;
/CREATE OR REPLACE PACKAGE BODY ModuleLogin AS
/*
FUNCTION RetuWorkTeamNo(cUserNo in VARCHAR2) RETURN ModuleLogin.cur IS
c_temp ModuleLogin.cur;
strSql varchar2(100);
BEGIN
strSql := 'select cWorkTeamNo from smUserPrivilege where bActive=1 AND cUserNo=(select cUserNo from smUserProfile where cUserNo=''' || cUserNo || ''' and bActive=1)';
OPEN c_temp FOR strSql;
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error .....' || sqlcode || ' : ' || sqlerrm );
RETURN c_temp;
END RetuWorkTeamNo;
*/
PROCEDURE RetuWorkTeamNo(cUserNo IN varchar2,cWordTeamNo out varchar2) IS
cursor c_temp(strUserNo smUserPrivilege.cUserNo%TYPE) is select cWorkTeamNo from smUserPrivilege where cUserNo=strUserNo;--c_temp ModuleLogin.cur;
v_WrokTeamNo smUserPrivilege.cWorkTeamNo%TYPE;
strSql varchar2(100);
BEGIN
DBMS_OUTPUT.PUT_LINE('A=' || cUserNo);
--strSql := 'select cWorkTeamNo from smUserPrivilege where bActive=1 AND cUserNo=(select cUserNo from smUserProfile where cUserNo=''' || cUserNo || ''' and bActive=1)';
--strSql := 'select cWorkTeamNo from smUserPrivilege where bActive=1 AND cUserNo=(select cUserNo from smUserProfile where cUserNo:=wUserNo and bActive=1)';
--strSql := 'select cWorkTeamNo from smUserPrivilege where bActive=1';
--OPEN c_temp FOR strSql using cUserNo;
OPEN c_temp(cUserNo);
fetch c_temp into v_WorkTeamNo;
close c_temp; /*
if v_WorkTeamNo is null then
RAISE_APPLICATION_ERROR(-2002,'NO RECORD');
else
cWrokTeamNo := v_WorkTeamNo;
end if
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error .....' || sqlcode || ' : ' || sqlerrm );
-- cWordTeamNo :='aa';
*/
END RetuWorkTeamNo;
END ModuleLogin;
/
解决方案 »
- sql语句的问题
- oracle数据库如何处理树形结构数据的sql写法
- 每月的后五天每天凌晨3点执行一次的计划任务
- Oracle转义符的使用
- 如何登录sql*plus???
- 当前user表的记录数(续) +100
- 请问,我在登录的时候,出现"shared memory realm does not exit"?
- 请问在ora8中字段类型是存在那个系统表中???(急!急!急!)
- 急!!!熟悉unix下Oracle Proc编程的兄弟,请帮忙!谢谢先!
- 在SQL语句中,日期类型的变量应如何表达?
- 我想做一个类似sql*plus类似的文本解释器,那位高手有什么实现思想?
- 当Oracle数据库表结构改动以后,如何导回原来表中的数据?
for v_c_temp in c_temp(cUserNo) loop
--v_c_temp.cWorkTeamNo;
end loop;