Re: Stored Procedure

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 6 Nov 2001 17:35:55 -0800
Message-ID: <9sa35r01plt@drn.newsguy.com>

In article <8202c755.0111061127.69b9837a_at_posting.google.com>, boah123_at_hotmail.com says...
>
>Hello,
>
>I have a stored procedure in my microsoft sql server 2000.
>This is the code:
>
>CREATE PROCEDURE GetPortalSettings
>(
> @PortalAlias nvarchar(50),
> @TabID int,
> @PortalID int OUTPUT,
> @PortalName nvarchar(128) OUTPUT,
> @AlwaysShowEditButton bit OUTPUT,
> @TabName nvarchar (50) OUTPUT,
> @TabOrder int OUTPUT,
> @MobileTabName nvarchar (50) OUTPUT,
> @AuthRoles nvarchar (256) OUTPUT,
> @ShowMobile bit OUTPUT
>)
>AS
>
>/* First, get Out Params */
>IF @TabID = 0
> SELECT TOP 1
> @PortalID = Portals.PortalID,
> @PortalName = Portals.PortalName,
> @AlwaysShowEditButton = Portals.AlwaysShowEditButton,
> @TabID = Tabs.TabID,
> @TabOrder = Tabs.TabOrder,
> @TabName = Tabs.TabName,
> @MobileTabName = Tabs.MobileTabName,
> @AuthRoles = Tabs.AuthorizedRoles,
> @ShowMobile = Tabs.ShowMobile
>
> FROM
> Tabs
> INNER JOIN
> Portals ON Tabs.PortalID = Portals.PortalID
>
> WHERE
> PortalAlias=@PortalAlias
>
> ORDER BY
> TabOrder
>
>ELSE
> SELECT
> @PortalID = Portals.PortalID,
> @PortalName = Portals.PortalName,
> @AlwaysShowEditButton = Portals.AlwaysShowEditButton,
> @TabName = Tabs.TabName,
> @TabOrder = Tabs.TabOrder,
> @MobileTabName = Tabs.MobileTabName,
> @AuthRoles = Tabs.AuthorizedRoles,
> @ShowMobile = Tabs.ShowMobile
>
> FROM
> Tabs
> INNER JOIN
> Portals ON Tabs.PortalID = Portals.PortalID
>
> WHERE
> TabID=@TabID
>
>/* Get Tabs list */
>SELECT
> TabName,
> AuthorizedRoles,
> TabID,
> TabOrder
>
>FROM
> Tabs
>
>WHERE
> PortalID = @PortalID
>
>ORDER BY
> TabOrder
>
>/* Get Mobile Tabs list */
>SELECT
> MobileTabName,
> AuthorizedRoles,
> TabID,
> ShowMobile
>
>FROM
> Tabs
>
>WHERE
> PortalID = @PortalID
> AND
> ShowMobile = 1
>
>ORDER BY
> TabOrder
>
>/* Then, get the DataTable of module info */
>SELECT
> *
>
>FROM
> Modules
> INNER JOIN
> ModuleDefinitions ON Modules.ModuleDefID = ModuleDefinitions.ModuleDefID
>
>WHERE
> TabID = @TabID
>
>ORDER BY
> ModuleOrder
>
>GO
>
>
>Now I want to convert this to an oracle stored procedure.
>I do not have any experience with this.
>
>First question: Is it possible to convert this stored procedure?
>
>If yes, could somebody do this for me? (Offcourse, only this one)
>
>Many Thanks!
>
>Boah

Welcome aboard. See
http://osi.oracle.com/~tkyte/ResultSets/index.html for a general discussion of getting result sets from Oracle -- a howto.

Here is an example like yours. You don't mention a version of Oracle (sigh, but no one does ;) -- if you have 8i or before -- the inner join syntax isn't supported, in 9i it is. In 8i just "join"

select * from emp, dept where emp.deptno = dept.deptno;

in 9i, if you like the INNER join syntax, you can use it.

in 9i, you can just use SYS_REFCURSOR, you do not need this "types" package. In 8i and before you do.

scott_at_ORA717DEV.US.ORACLE.COM> create or replace package types   2 as
  3 type rc is ref cursor;
  4 end;
  5 /

Package created.

scott_at_ORA717DEV.US.ORACLE.COM> 
scott_at_ORA717DEV.US.ORACLE.COM> 
scott_at_ORA717DEV.US.ORACLE.COM> 
scott_at_ORA717DEV.US.ORACLE.COM> create procedure foo( some_data in number,
  2                        some_more_data out varchar2,
  3                        query1 in out types.rc,
  4                        query2 in out types.rc,
  5                        query3 in out types.rc )

6 as
7 begin

  8      if ( some_data = 0 )
  9      then
 10          select 'Hey, some data was 0'
 11            into some_more_data
 12            from dual;
 13      else
 14          select 'Hey, some data was not 0'
 15            into some_more_data
 16            from dual;
 17      end if;

18
19
20 open query1 for select ename, empno from emp where rownum <= 5; 21
22 open query2 for select deptno from dept; 23
24 open query3 for select sysdate, user from dual; 25 end;
26 /
Procedure created.

scott_at_ORA717DEV.US.ORACLE.COM> variable q1 refcursor
scott_at_ORA717DEV.US.ORACLE.COM> variable q2 refcursor
scott_at_ORA717DEV.US.ORACLE.COM> variable q3 refcursor
scott_at_ORA717DEV.US.ORACLE.COM> variable some_more_data varchar2(255)
scott_at_ORA717DEV.US.ORACLE.COM> set autoprint on

scott_at_ORA717DEV.US.ORACLE.COM> exec foo( 0, :some_more_data, :q1, :q2, :q3 )

PL/SQL procedure successfully completed.

SYSDATE USER

--------- ------------------------------

06-NOV-01 SCOTT DEPTNO

        10
        20
        30
        40


ENAME           EMPNO
---------- ----------
A                7369
ALLEN            7499
WARD             7521
JONES            7566
MARTIN           7654

SOME_MORE_DATA

Hey, some data was 0

scott_at_ORA717DEV.US.ORACLE.COM> exec foo( 1, :some_more_data, :q1, :q2, :q3 )

PL/SQL procedure successfully completed.

SYSDATE USER

--------- ------------------------------

06-NOV-01 SCOTT DEPTNO

        10
        20
        30
        40


ENAME           EMPNO
---------- ----------
A                7369
ALLEN            7499
WARD             7521
JONES            7566
MARTIN           7654

SOME_MORE_DATA

Hey, some data was not 0

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Tue Nov 06 2001 - 19:35:55 CST