pl/sql selecting variables

Question

I have a simple SQL Server stored proc

CREATE PROCEDURE [dbo].[some_proc]
AS
BEGIN
    DECLARE @CountA INT
    DECLARE @CountB INT
    DECLARE @CountC INT
    DECLARE @CountD INT
    SELECT @CountA = COUNT(*) FROM some_table WHERE Status IN (7,8,9) 
    SELECT @CountB =  COUNT(*)  FROM some_table WHERE Status IN (5)
    SELECT @CountC = COUNT(*) FROM some_table WHERE Status IN (6) 
    SELECT @CountD = COUNT(*) FROM some_table 


    SELECT @CountA AS aCount , @CountB AS bCount , @CountC AS cCount , @CountD AS dCount

END

GO

We support both SQL Server and Oracle database for our product, so I need to convert this to PL/SQL function. What would be the equivalent pl/sql function?

Expected outcome should be one row of data with column names aCount,bCount, cCount, dCount (I already have ADO.net code done and working with SQL server stored proc). This is what I have so far. From limited knowledge that I have I know I need to user cursor somehow, but can't figure out how.

CREATE OR REPLACE FUNCTION some_proc(
    p_ix_sys_error OUT number
)
IS

    DECLARE 
        CountA NUMBER;
        CountB NUMBER;
        CountC NUMBER;
        CountD NUMBER;
    BEGIN
        SELECT COUNT(*) INTO CountA FROM some_table WHERE Status IN (7,8,9); 
        SELECT COUNT(*) INTO CountB FROM some_table WHERE Status IN (5);
        SELECT COUNT(*) INTO CountC FROM some_table WHERE Status IN (6);
        SELECT COUNT(*) INTO  CountD FROM some_table; 


    SELECT CountA AS aCount , CountB AS bCount , CountC AS cCount , CountD AS dCount

END

GO

Justin Cave Justin Cave · Accepted Answer · 2015-01-30T22:18:32

The most straightforward translation would be something like

CREATE OR REPLACE FUNCTION some_proc(
    p_ix_sys_error OUT number,
    p_rc           OUT sys_refcursor
)
IS
    CountA NUMBER;
    CountB NUMBER;
    CountC NUMBER;
    CountD NUMBER;
BEGIN
    SELECT COUNT(*) INTO CountA FROM some_table WHERE Status IN (7,8,9); 
    SELECT COUNT(*) INTO CountB FROM some_table WHERE Status IN (5);
    SELECT COUNT(*) INTO CountC FROM some_table WHERE Status IN (6);
    SELECT COUNT(*) INTO  CountD FROM some_table; 

    OPEN p_rc
     FOR SELECT CountA AS aCount , 
                CountB AS bCount , 
                CountC AS cCount , 
                CountD AS dCount
           FROM dual;
END;

On either system, it would be more efficient to do a single query against the table rather than 4 separate queries

CREATE OR REPLACE FUNCTION some_proc(
    p_ix_sys_error OUT number,
    p_rc           OUT sys_refcursor
)
IS
BEGIN
  OPEN p_rc
   FOR SELECT SUM( CASE WHEN Status IN (7,8,9)
                        THEN 1
                        ELSE 0
                    END ) aCount ,
              SUM( CASE WHEN Status IN (5)
                        THEN 1
                        ELSE 0
                    END ) bCount ,
              SUM( CASE WHEN Status IN (6)
                        THEN 1
                        ELSE 0
                    END ) cCount ,
              COUNT(*) dCount 
         FROM some_table;
END;

If you only have to support Oracle 12.1 and later, you can open an implicit result set rather than declaring a separate OUT parameter but I assume that you want to support earlier versions as well.

pl/sql selecting variables

1 Answers