又要麻烦老大了,我一个存储过程是oracle的,你凑合帮我看看。
存储过程如下:CREATE OR REPLACE PROCEDURE listUnreadMails (
param_lgname IN VARCHAR2,param_r IN VARCHAR2,
resultCursor OUT returnPackage.returnCursor
) AS
op_type_receive CHAR(1);
v_grouploginname ts_groups.grouploginname%TYPE;
lgname VARCHAR2(30);BEGIN
op_type_receive := TRIM(param_r); lgname := TRIM(param_lgname); select trim(grouploginname) into v_grouploginname from ts_groups where trim(memberloginname)=lgname;
exception
when others then
v_grouploginname := 'zu_ls'; OPEN resultCursor FOR
select dbid,parentDbid,TRIM(sender) AS sender,TRIM(receipt) AS receipt,ts_mailbox.type,mailDate,subject,attachNums
from ts_mailbox ,ts_users
where ts_mailbox.sender=ts_users.loginname
and dbid not in(select dbid from ts_op_stream where op_user = lgname and op_status=op_type_receive)
and trim(receipt) =lgname
and (receipt like '%'||lgname||'%'
or receipt like '%'||v_grouploginname||'%' )
ORDER BY mailDate DESC;
END listUnreadMails;原先没加这句:select trim(grouploginname) into v_grouploginname from ts_groups where trim(memberloginname)=lgname;
exception
when others then
v_grouploginname := 'zu_ls';是好的,加上这句就会报cursor is closed异常,我把这句放在OPEN resultCursor FOR里面会直接编译不过去,这个是哪个地方写错了呢?谢谢!
存储过程如下:CREATE OR REPLACE PROCEDURE listUnreadMails (
param_lgname IN VARCHAR2,param_r IN VARCHAR2,
resultCursor OUT returnPackage.returnCursor
) AS
op_type_receive CHAR(1);
v_grouploginname ts_groups.grouploginname%TYPE;
lgname VARCHAR2(30);BEGIN
op_type_receive := TRIM(param_r); lgname := TRIM(param_lgname); select trim(grouploginname) into v_grouploginname from ts_groups where trim(memberloginname)=lgname;
exception
when others then
v_grouploginname := 'zu_ls'; OPEN resultCursor FOR
select dbid,parentDbid,TRIM(sender) AS sender,TRIM(receipt) AS receipt,ts_mailbox.type,mailDate,subject,attachNums
from ts_mailbox ,ts_users
where ts_mailbox.sender=ts_users.loginname
and dbid not in(select dbid from ts_op_stream where op_user = lgname and op_status=op_type_receive)
and trim(receipt) =lgname
and (receipt like '%'||lgname||'%'
or receipt like '%'||v_grouploginname||'%' )
ORDER BY mailDate DESC;
END listUnreadMails;原先没加这句:select trim(grouploginname) into v_grouploginname from ts_groups where trim(memberloginname)=lgname;
exception
when others then
v_grouploginname := 'zu_ls';是好的,加上这句就会报cursor is closed异常,我把这句放在OPEN resultCursor FOR里面会直接编译不过去,这个是哪个地方写错了呢?谢谢!
2、建议你转到ORACLE版块;
3、是否应该先声明resultCursor,MYSQL、SQLSERVER都要先声明
游标
DECLARE CURSOR resultCursor IS
select dbid,parentDbid,TRIM(sender) AS sender,TRIM(receipt) AS receipt,ts_mailbox.type,mailDate,subject,attachNums
from ts_mailbox ,ts_users
where ts_mailbox.sender=ts_users.loginname
and dbid not in(select dbid from ts_op_stream where op_user = lgname and op_status=op_type_receive)
and trim(receipt) =lgname
and (receipt like '%'||lgname||'%'
or receipt like '%'||v_grouploginname||'%' )
ORDER BY mailDate DESC;
BEGIN
op_type_receive := TRIM(param_r); lgname := TRIM(param_lgname); select trim(grouploginname) into v_grouploginname from ts_groups where trim(memberloginname)=lgname;
exception
when others then
v_grouploginname := 'zu_ls';OPEN resultCursor;
END试试