在 Pl/SQl 中使用带有 Refcursor 的 if 条件块

发布于 2024-11-04 09:23:39 字数 962 浏览 7 评论 0原文

我创建了一个 RefCursor 类型的包。

我在存储过程中使用包。

代码如下所示：

CREATE OR REPLACE PACKAGE PRODDB.types
AS
type cursorType is ref cursor;
END;

CREATE OR REPLACE PROCEDURE PRODDB.P_Get_AdminPLCReport         
(        
    Final_Output out TYPES.cursorType

)        
AS

BEGIN       
IF( Criteria='1') THEN        
  OPEN Final_Output FOR 
  SELECT  Personal_Information.F_Salutation
  FROM    Allotment_Information;

END IF;

END P_Get_AdminPLCReport;

我想在此存储过程中使用两个 if 条件。

我是 Pl-Sql 新手。

谁能告诉我这个的语法来帮助我。

感谢

更新

感谢贾斯汀 (在这里回复），

但我想使用两个条件，例如：

IF( criteria = '1') then

// some query; end if

IF( criteria = '2') then

// 一些查询；

如果

我将传递参数标准的值，则结束。

根据 criteria 的值，我想执行不同的查询。

原文

I have created a package of of type RefCursor.

I am using package in my stored procedure.

code looks like this:

CREATE OR REPLACE PACKAGE PRODDB.types
AS
type cursorType is ref cursor;
END;

CREATE OR REPLACE PROCEDURE PRODDB.P_Get_AdminPLCReport         
(        
    Final_Output out TYPES.cursorType

)        
AS

BEGIN       
IF( Criteria='1') THEN        
  OPEN Final_Output FOR 
  SELECT  Personal_Information.F_Salutation
  FROM    Allotment_Information;

END IF;

END P_Get_AdminPLCReport;

I want to use two if conditions in this stored procedure.

I am new to Pl-Sql.

Can anybody help me by telling me the syntax for this.

Thanx

Update

Thanx Justin (response here),

But i want to use two condition like:

IF( criteria = '1') then

// some query; end if

IF( criteria = '2') then

// some query;

end if

I will pass value of param criteria.

Based on the value of criteria , i want to execute different queries.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深白境迁sunset 2024-11-11 09:23:39

首先，10g 中不需要在 TYPES 包中定义弱类型REF CURSOR。这在某些旧版本中是必要的，但 Oracle 现在有一个 SYS_REFCURSOR 类型。

其次，您的过程无法编译，因为没有声明变量 Criteria。我将忽略这一点并假设您已在其他地方声明并初始化了该变量。

根据第二个条件是什么，最简单的选择通常是这样的

CREATE OR REPLACE PROCEDURE PRODDB.P_Get_AdminPLCReport 
(
  Final_Output OUT sys_refcursor
)
AS
BEGIN
  IF( criteria = '1' and someOtherCriteria = '2' )
  THEN
    OPEN final_output FOR
      SELECT personal_information.f_salutation
        FROM allotment_information;
  END IF;
END p_get_adminPLCReport;

First off, there is no need to define a weakly typed REF CURSOR in a TYPES package in 10g. That was necessary in some older releases, but Oracle has a SYS_REFCURSOR type now.

Second, your procedure doesn't compile because there is no declaration of the variable Criteria. I'll ignore that and assume that you have declared and initialized the variable elsewhere.

Depending on what the second condition is, the simplest option would generally be something like

CREATE OR REPLACE PROCEDURE PRODDB.P_Get_AdminPLCReport 
(
  Final_Output OUT sys_refcursor
)
AS
BEGIN
  IF( criteria = '1' and someOtherCriteria = '2' )
  THEN
    OPEN final_output FOR
      SELECT personal_information.f_salutation
        FROM allotment_information;
  END IF;
END p_get_adminPLCReport;

回复收藏 0 原文