在sys_refcursor上查询 [英] QUERY ON sys_refcursor

问题描述

您好我正在使用Oracle数据库10g企业版10.2.0.1.0版，我是oracle word的新手，在MS SQL SERVER中工作。



我想知道这个程序是否正确。



1）

我的要求是获取详细信息给定的手机号：，只返回一条记录

，如果没有这样的手机号码也没有。无效的手机没有消息应该给客户

vb.net。







创建或替换程序GetSenderDetails（In_MobNo IN NUMBER，

SenderResultSet OUT SYS_REFCURSOR）

IS

BEGIN

打开SenderResultSet FOR

选择SENDERID，CHAPARTNERID，BCAGENT，SENDERNAME

从SenderMaster

其中SENDERMOBILENO = In_MobNo;

IF SQL％NOTFOUND那么

Raise_application_error（-20011，'（无效的手机号码'）;

END IF;

END;

/





2）调用此过程以获取给定senderid的接收详细信息。结果集将'n'记录和相同如果没有senderid不存在消息应该是'无效的发送者ID'



创建或替换程序GetReceiverDetails（In_SenderID IN NUMBER，

ReceiverResultSet OUT SYS_REFCURSOR）

IS

BEGIN

OPEN ReceiverResultSet FOR

SELECT RECEIVERID，CHAPARTNERID，RECEIVERNAME

来自ReceiverMaster

WHERE SenderID = In_SenderID;



如果SQL％NOTFOUND那么

Raise_application_error（-20011，'（SenderID：'）;

END IF;

END;



我想在sql *中运行以及如何执行此SP。





注意：这是好还是任何更好的回归结果设置在ORACLE。



提前致谢。



问候



Heljeeve

Hi i am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 and i am new to oracle word, working in MS SQL SERVER.

I want to know is this procedure is correct or not.

1)
My requirement is to get details for the given mobile no:, only one record will be returned
and also if there is no such mobile no. Invalid Mobile No message should be given to the client
vb.net.



CREATE OR REPLACE PROCEDURE GetSenderDetails (In_MobNo IN NUMBER,
SenderResultSet OUT SYS_REFCURSOR)
IS
BEGIN
OPEN SenderResultSet FOR
SELECT SENDERID,CHAPARTNERID,BCAGENT,SENDERNAME
FROM SenderMaster
WHERE SENDERMOBILENO=In_MobNo;
IF SQL%NOTFOUND THEN
Raise_application_error(-20011, '(Invalid Mobile No:');
END IF;
END;
/


2) This procedure is called to get recevier details for the given senderid. result set will 'n' records and the same if no senderid is not there message should be 'INVALID SENDER ID'

CREATE OR REPLACE PROCEDURE GetReceiverDetails (In_SenderID IN NUMBER,
ReceiverResultSet OUT SYS_REFCURSOR)
IS
BEGIN
OPEN ReceiverResultSet FOR
SELECT RECEIVERID,CHAPARTNERID,RECEIVERNAME
FROM ReceiverMaster
WHERE SenderID= In_SenderID ;

IF SQL%NOTFOUND THEN
Raise_application_error(-20011, '(SenderID:');
END IF;
END;

I want run in sql*plus how to execute this SP.


NOTE: IS THIS GOOD OR ANY BETTER WAYS TO RETURN RESULT SET IN ORACLE.

Thanks in advance.

With Regards

Heljeeve

推荐答案

我已更改您的发件人程序如下，看看这个，它会给你一个清晰的想法。类似地，您可以为Receiver程序编写。

I've changed your sender procedure as below, have a look at this and it'll give you a clear idea. Similarly you can write for Receiver procedure.

CREATE OR REPLACE PROCEDURE GetSenderDetails (In_MobNo IN NUMBER,
SenderResultSet OUT SYS_REFCURSOR)
IS
V_COUNT NUMBER;
BEGIN

SELECT COUNT(*) INTO V_COUNT FROM SenderMaster
WHERE SENDERMOBILENO=In_MobNo;

IF V_COUNT > 0 THEN
	OPEN SenderResultSet FOR
	SELECT SENDERID,CHAPARTNERID,BCAGENT,SENDERNAME
	FROM SenderMaster
	WHERE SENDERMOBILENO=In_MobNo;
ELSE
	OPEN SenderResultSet FOR 
	SELECT 'INVALID MOBILENO' FROM DUAL;
END IF;

END;

问候，

BlueSathish

Regards,
BlueSathish

这篇关于在sys_refcursor上查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！