Outputting the table or multiple rows from stored procedure or function
SQL Server
SQL Server automatically outputs the result set as shown below
CREATE PROCEDURE SP_OUT_TABLE (@INPARAM1 INT)
AS
BEGIN
SELECT * FROM (
SELECT CAST('OUTPUT SAM1' AS VARCHAR(100)) AS COL1,CAST( 1 AS INT) AS COL2
UNION ALL
SELECT CAST('OUTPUT SAM2' AS VARCHAR(100)) AS COL1,CAST( 2 AS INT) AS COL2
) SAM_TEMP -- replace this sub query with your query
WHERE COL2=@INPARAM1
END
To get the output we need to run below execute statement
EXEC SP_OUT_TABLE 1
When executing the stored procedure we need to just specify EXEC in front of the procedure name and we directly specify passing parameters next to the stored procedure name.
Oracle
When it comes to oracle we can output a reference cursor using a stored procedure and loop through it to see the results but if you are looking for something similar to how it is outputted above, then we need to use function with the help of pipeline (supported version 9i and higher) as shown below
First we need to start with creating Types in Oracle
--create table type object to define format of the rows
CREATE OR REPLACE TYPE OUTPUT_TABLE_TYPE
IS OBJECT (
COL1 VARCHAR(100),
COL2 NUMBER
);
Note: “IS OBJECT” is used if we need to store it in Database and “IS Record” can be used in ad hoc declare scripts
--create table type based on above object
CREATE OR REPLACE TYPE OUTPUT_TABLE
AS TABLE OF OUTPUT_TABLE_TYPE
Note: if we already have a table which our output will look like let’s say table1 then we can use table1%ROWTYPE instead of OUTPUT_TABLE_TYPE and we do not need any table type object
CREATE OR REPLACE FUNCTION FN_OUT_TABLE (INPARAM1 IN NUMBER)
RETURN OUTPUT_TABLE PIPELINED
IS
BEGIN
FOR RECORD_OUTPUT IN (
SELECT * FROM (
SELECT CAST('OUTPUT SAM1' AS VARCHAR(100)) AS COL1,CAST( 1 AS NUMBER) AS COL2 FROM DUAL
UNION ALL
SELECT CAST('OUTPUT SAM2' AS VARCHAR(100)) AS COL1,CAST( 2 AS NUMBER) AS COL2 FROM DUAL
) SAM_TEMP -- replace this sub query with your query
WHERE COL2=INPARAM1
)
LOOP
PIPE ROW (OUTPUT_TABLE_TYPE(RECORD_OUTPUT.COL1,RECORD_OUTPUT.COL2));
END LOOP;
END;
To get the output we need to run below select statement
SELECT * FROM TABLE(FN_OUT_TABLE(2));
The function which needs to be run should be placed inside the table function as shown above and parameters if any needs to be passed in the curly brackets of the actual function
Thank you for reading this article, check out my other SQL Server vs. Oracle posts.