一个查询ORACLE数据库中的存储过程如下:
CREATE OR REPLACE PROCEDURE InquireRpt
(
in_YWDM in SMS.YWDM%TYPE,
out_ID out SMS.ID%TYPE,
out_MSGID out SMS.MSGID%TYPE,
out_DESTNUM out SMS.DESTNUM%TYPE,
out_REPORT out SMS.REPORT%TYPE
)
AS
m_ID SMS.ID%TYPE;
m_MSGID SMS.MSGID%TYPE;
m_DESTNUM SMS.DESTNUM%TYPE;
m_REPORT SMS.REPORT%TYPE;
BEGIN
select ID,MSGID,DESTNUM,REPORT into m_Id,m_Msgid,m_Destnum,m_Report from SMS where YWDM=in_YWDM and CXZT='0'and ZT='2' or ZT='3';
out_id:=m_Id;
out_msgid:=m_Msgid;
out_destnum:=m_Destnum;
out_report:=m_Report;
update SMS set CXZT='1' where YWDM=in_YWDM and CXZT='0'
and ZT='2' or ZT='3';
END InquireRpt;可以查询并修改数据库中的值,但只能返回一条记录,不能返回所有满足条件的记录。
请问要如何修改存储过程?知道的请指导啊。
CREATE OR REPLACE PROCEDURE InquireRpt
(
in_YWDM in SMS.YWDM%TYPE,
out_ID out SMS.ID%TYPE,
out_MSGID out SMS.MSGID%TYPE,
out_DESTNUM out SMS.DESTNUM%TYPE,
out_REPORT out SMS.REPORT%TYPE
)
AS
m_ID SMS.ID%TYPE;
m_MSGID SMS.MSGID%TYPE;
m_DESTNUM SMS.DESTNUM%TYPE;
m_REPORT SMS.REPORT%TYPE;
BEGIN
select ID,MSGID,DESTNUM,REPORT into m_Id,m_Msgid,m_Destnum,m_Report from SMS where YWDM=in_YWDM and CXZT='0'and ZT='2' or ZT='3';
out_id:=m_Id;
out_msgid:=m_Msgid;
out_destnum:=m_Destnum;
out_report:=m_Report;
update SMS set CXZT='1' where YWDM=in_YWDM and CXZT='0'
and ZT='2' or ZT='3';
END InquireRpt;可以查询并修改数据库中的值,但只能返回一条记录,不能返回所有满足条件的记录。
请问要如何修改存储过程?知道的请指导啊。
begin
open Rst for select * from tablename;
end;9i以下的版本需要定义动态游标
is
type resultData is ref cursor;
procedure sp_test(rst out resultData);
end pkg_test;
/
create or replace package body pkg_test
is
procedure sp_test(rst out resultData) is
begin
open rst for select * from tablename;
end;
end pkg_test;
/
In_Ywdm In Sms.Ywdm%Type) As
v_Sql Varchar2(1000);
Begin
v_Sql := '
Select Id, Msgid, Destnum, Report
From Sms
Where Ywdm =' || In_Ywdm || ' And Cxzt = ''0''
And Zt = ''2''
Or Zt = ''3''''';
Update Sms
Set Cxzt = '1'
Where Ywdm = In_Ywdm
And Cxzt = '0'
And Zt = '2'
Or Zt = '3';
Open Cur_Out For v_Sql;
End Inquirerpt;
for cur_test in (select ID,MSGID,DESTNUM,REPORT from SMS where YWDM=in_YWDM and CXZT='0'and ZT='2' or ZT='3'
) loop
m_Id := cur_test.id;
m_Msgid := cur_test.msgid
m_Destnum := ...
m_Report := ... 这里再重新设置一个变量,统计循环的条数,就知道有几条数据符合条件了。 end loop;
end;
另外想问一下执行的时候是调用包里的那个存储过程对吧
包头:
CREATE OR REPLACE PACKAGE Rpt_InquireIS
type resultData is ref cursor;
PROCEDURE RptInquire
(
in_YWDM in SMS.YWDM%TYPE,
rst out resultData
);
end Rpt_Inquire;包体:
CREATE OR REPLACE PACKAGE BODY Rpt_Inquire
IS
PROCEDURE RptInquire
(
in_YWDM in SMS.YWDM%TYPE,
rst out resultData
)
IS
BEGIN
open rst for select ID MSGID,DESTNUM,REPORT from SMS where YWDM='YDJW' and CXZT='0'
and ZT='2' or ZT='3';
END;
END;包头和包体能编译成功
但执行的时候出错:提示好象是resultData有问题我的执行代码:
declare
in_YWDM SMS.YWDM%TYPE;
rst out resultData;
begin
in_YWDM:='JWT'; RptInquire(in_YWDM,rst);
dbms_output.put_line(rst);
end;请问知不知道是什么原因啊?谢谢!