Returning Result Sets from SQL Server and Oracle
Overview
You may need the Result Set from a Query in your .NET or Java Code by calling a Stored Procedure. Here are some samples for SQL Server and Oracle 10g.
Returning simple Result Sets using SQL Server
With SQL Server 2000/2005 it is very easy to return a Result Set from a Query to the calling environment. Here is an example using SQL Query Analyzer.
USE Northwind
GO
CREATE PROCEDURE MyOrders
AS
SELECT * FROM Orders
WHERE RequiredDate < GETDATE() AND ShippedDate IS NULL
GO
EXEC MyOrders
GO
Returning complex Result Sets using SQL Server
It's not always possible to build the required recordset from a single SQL query, especially when trying to produce complex reports. Sometimes several passes through the data are required to get the desired data.
CREATE FUNCTION CustAdresse(@PersID bigint, @KontaktArtID bigint)
RETURNS @addresstable TABLE
(PersonID bigint,
Adresse varchar(50) COLLATE database_default,
PLZ varchar(10) COLLATE database_default,
Ort varchar(50) COLLATE database_default,
Land varchar(50) COLLATE database_default)
AS
BEGIN
DECLARE @KontaktID bigint
IF (@KontaktArtID = 1)
BEGIN
SET @KontaktID = (SELECT k.KontaktID
FROM Kontakt k
WHERE k.KontaktArtID = 1
AND k.PersonID = @PersID)
END
IF (@KontaktID IS NULL)
BEGIN
SET @KontaktID = (SELECT k.KontaktID
FROM Kontakt k
WHERE k.KontaktArtID = 2
AND k.PersonID = @PersID
END
INSERT INTO @addresstable
(k.PersonID, Adresse, PlZ, Ort, Land)
SELECT k.Personid, k.Adresse, o.PLZ, l.Land
FROM Kontakt k, Ort o, Land l
WHERE k.OrtID = o.OrtID
AND o.LandID = l.LandID
AND k.KontaktID = @KontaktID
RETURN
ENDIn SQL Server, you can declare a temporary Table (@addresstable) within the T_SQL Code. In the code you fill this Table using your own Business Logic and then return this Table back to the calling environment.
Returning simple Result Sets using Oracle 10g
Using a
REF CURSORit is quite easy to give access to the content of a database table from either PL/SQL or Java, or most other programming languages, for that matter.
REF CURSORShave been available which allow recordsets to be returned from stored procedures, functions and packages. The example below uses a ref cursor to return a subset of the records in theEMPtable.
Create the simple EMP and DEPT Tables.
DROP TABLE EMP;
DROP TABLE DEPT;
CREATE TABLE EMP
(EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2));
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'2-APR-81',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1-MAY-81',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'9-JUN-81',2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'09-DEC-82',3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'17-NOV-81',5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'8-SEP-81',1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'12-JAN-83',1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'3-DEC-81',950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'3-DEC-81',3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'23-JAN-82',1300,NULL,10);
COMMIT;
CREATE TABLE DEPT
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13) );
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
COMMIT;
ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY (EMPNO);
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);
Create a Package with a REF CURSOR
CREATE OR REPLACE PACKAGE GetEmpPckg IS
-- Strongly Typed REF CURSOR
-- A REF CURSOR that specifies a specific return type
TYPE emp_ref_cursor IS REF CURSOR RETURN emp%ROWTYPE;
--
-- Weakly Typed
-- A REF CURSOR that does not specify the return type such as SYS_REFCURSOR
-- TYPE emp_ref_cursor IS REF CURSOR;
PROCEDURE GetEmp
(p_dep IN emp.deptno%TYPE,
p_ref OUT emp_ref_cursor);
END GetEmpPckg;
/
CREATE OR REPLACE PACKAGE BODY GetEmpPckg IS
PROCEDURE GetEmp
(p_dep IN emp.deptno%TYPE,
p_ref OUT emp_ref_cursor) IS
BEGIN
OPEN p_ref FOR
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE deptno = p_dep
ORDER BY ename;
END;
END GetEmpPckg;
/In Oracle9i the SYS_REFCURSOR type has been added making the TYPE emp_ref_cursor step unnecessary. If you are using Oracle9i or later simply ignore this and replace any references to emp_ref_cursor with SYS_REFCURSOR.
Test the REF CURSOR Procedure from within PL/SQL
The resulting cursor can be referenced from PL/SQL as follows:
SET SERVEROUTPUT ON SIZE 1000000 DECLARE v_cursor GetEmpPckg.emp_ref_cursor; v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_job emp.job%TYPE; v_mgr emp.mgr%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_deptno emp.deptno%TYPE; BEGIN GetEmpPckg.GetEmp (p_dep => 30, p_ref => v_cursor); LOOP FETCH v_cursor INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno); END LOOP; CLOSE v_cursor; END; /ALLEN | 7499 | 30
BLAKE | 7698 | 30
JAMES | 7900 | 30
MARTIN | 7654 | 30
TURNER | 7844 | 30
WARD | 7521 | 30
Test the REF CURSOR Procedure from within C#
using System;
using System.Data;
using Oracle.DataAccess.Client;
class Sample
{
static void Main()
{
// Connect to Oracle
string constr = "User Id=scott;Password=tiger;Data Source=AKI1.WORLD";
OracleConnection con = new OracleConnection(constr);
con.Open();
// Display Version Number
Console.WriteLine("Connected to Oracle " + con.ServerVersion);
// Read REF CURSOR into DataSet
DataSet ds = new DataSet();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "GetEmpPckg.GetEmp";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_dep", OracleDbType.Int16).Value = 20;
cmd.Parameters.Add("p_ref", OracleDbType.RefCursor).Direction
= ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Emp", "Emp");
da.Fill(ds);
// Close and Dispose OracleConnection
con.Close();
con.Dispose();
// Show Message
Console.WriteLine("DataSet filled");
}
}
Returning complex Result Sets using Oracle 10g
It's not always possible to build the required recordset from a single SQL query, especially when trying to produce complex reports. Sometimes several passes through the data are required to acheive the desired data. Oracle allows you to do this using temporary tables or PL/SQL tables. Although the examples in this article could easily be done using SQL alone they do illustrate the methods available
Temporary Tables
Using this method we can create a temporary table to hold the data while we process it. Once the processing is complete we can use a
REF CURSORto pass the recordset out to an application.
First we create a temporary table to hold the data during the processing:
CREATE GLOBAL TEMPORARY TABLE my_temp_table
(
empno NUMBER(4),
ename VARCHAR2(10),
sal NUMBER(7,2),
complex NUMBER(7,2)
) ON COMMIT PRESERVE ROWS;
Create a Package with a REF CURSOR and the Temporary Table
CREATE OR REPLACE PACKAGE GetComplexPkg IS
PROCEDURE GetEmp
(p_dep IN emp.deptno%TYPE,
p_ref OUT SYS_REFCURSOR);
END GetComplexPkg ;
/CREATE OR REPLACE PACKAGE BODY GetComplexPkg IS
PROCEDURE GetEmp
(p_dep IN emp.deptno%TYPE,
p_ref OUT SYS_REFCURSOR) AS
BEGIN
-- Populate temporary table.
INSERT INTO my_temp_table
(empno, ename, sal)
SELECT empno, ename, sal
FROM emp
WHERE deptno = p_dep;
-- Do complex processing that can't be done from SQL alone.
FOR cur_row IN (SELECT * FROM my_temp_table) LOOP
UPDATE my_temp_table
SET complex = cur_row.sal + 1
WHERE empno = cur_row.empno;
END LOOP;
-- Open REF CURSOR for Output.
OPEN p_ref FOR
SELECT empno,
ename,
sal,
complex
FROM my_temp_table
ORDER BY ename;
END;
END GetComplexPkg;
/
PL/SQL Tables
This method is essentially the same only the temporary table is replaced by a PL/SQL table.
First we create the relevant database types
CREATE TYPE My_Row_Type AS OBJECT (
empno NUMBER(4),
ename VARCHAR2(10),
sal NUMBER(7,2),
complex NUMBER(7,2));
/
Next a table type is defined using the previous object type as a rowtype:
CREATE TYPE My_Tab_Type IS TABLE OF My_Row_Type;
/
Create a Package with a REF CURSOR and the PL/SQL Table
CREATE OR REPLACE PACKAGE GetComplexTabPkg IS
PROCEDURE GetEmp
(p_dep IN emp.deptno%TYPE,
p_ref OUT SYS_REFCURSOR);
END GetComplexTabPkg;
/
CREATE OR REPLACE PACKAGE BODY GetComplexTabPkg IS
PROCEDURE GetEmp
(p_dep IN emp.deptno%TYPE,
p_ref OUT SYS_REFCURSOR) AS
v_tab My_Tab_Type := My_Tab_Type();
BEGIN
-- Populate PL/SQL table.
FOR cur_row IN (SELECT * FROM emp WHERE deptno = p_dep) LOOP
v_tab.extend;
v_tab(v_tab.Last) := My_Row_Type(cur_row.empno,
cur_row.ename, cur_row.sal, NULL);
END LOOP;
-- Do complex processing that can't be done from SQL alone.
FOR cur_row IN 1 .. v_tab.count LOOP
v_tab(cur_row).complex := v_tab(cur_row).sal + 1;
END LOOP;
-- Open REF CURSOR for Output.
OPEN p_ref FOR
SELECT empno,
ename,
sal,
complex
FROM Table(Cast(v_tab As My_Tab_Type))
ORDER BY ename;
END;
END GetComplexTabPkg;
/
Test the REF CURSOR Procedure from within C#
using System;
using System.Data;
using Oracle.DataAccess.Client;
class Sample
{
static void Main()
{
// Connect to Oracle
string constr = "User Id=scott;Password=tiger;Data Source=AKI1.WORLD";
OracleConnection con = new OracleConnection(constr);
con.Open();
// Display Version Number
Console.WriteLine("Connected to Oracle " + con.ServerVersion);
// Read REF CURSOR into DataSet
DataSet ds = new DataSet();
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "GetComplexTabPkg.GetEmp";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("p_dep", OracleDbType.Int16).Value = 20;
cmd.Parameters.Add("p_ref", OracleDbType.RefCursor).Direction
= ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Emp", "Emp");
da.Fill(ds);
// Close and Dispose OracleConnection
con.Close();
con.Dispose();
// Show Message
Console.WriteLine("DataSet filled");
}
}
Referenced Documents
http://www.psoug.org/reference/ref_cursors.html
http://www.oracle-base.com/index.php
http://msdn2.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter.aspx
WHERE Klausel Generierung mit .NET, Urs Meier, Trivadis AG