Re: How to write a SELECT stored procedure ?

Don't mean to insult anyone 's intelligent here. Just that my NT was about to crash the very moment I wanted to review and would give a better help to the original poster. But, thanks for the correction :)) Kyte :)))

Regards,

Paul Nguyen

Thomas Kyte wrote:

> A copy of this was sent to tjmxyz_at_my-deja.com
> (if that email address didn't require changing)
> On Tue, 03 Aug 1999 09:23:40 GMT, you wrote:
>
> >
> >> Use cursor.
> >> create or replace procedure GetNames()
> >> begin
> >> open mycursor for
> >> select name from tbemployee;
> >> end ;
> >
> >That will do nothing....
> >Plus it wouldn't compile.
> >
>
> well, true, as written it is missing:
>
> create or replace procedures GetNames( mycursor in out some_pkg.some_type )
> as
> begin
> open mycursor for select name from tbemployee;
> end;
>
> it was missing the parameter declare and an "as".
>
> >I think what you want is.
> >To execute the stored procedure and retireve the rows
> >on the client side.
> >
> >Just use:
> >select name from tbemployee
> >
> >there is no advantage to having it as a stored procedure
> >that I can think of.
> >
>
> well, there are a couple
>
> - encapsulation, find a bug, fix the procedure and leave the code alone.
>
> - implement complex logic in the server, eg:
> if ( user = 'BOB' ) then
> open mycursor for select a, b, SALARY from emp;
> else
> open mycursor for select a, b, to_number(null) salary from emp;
> end if;
>
> - hiding the tables -- procedures run with the privs of the owner (can run with
> invokers rights in 8i as well). Therefore, I can open a cursor in a procedure
> for someone who could not open the cursor directly. They must call my api to
> get data from the database --a select will not work for them as they haven't
> been granted select....
>
> are a few...
>
> >The probelm is proecdure won't return the results
> >into a cursor or record set.
> >
>
> With 7.2 on up of the database you have cursor variables. Cursor variables
> are cursors opened by a pl/sql routine and fetched from by another application
> or pl/sql routine (in 7.3 pl/sql routines can fetch from cursor variables as
> well as open them). The cursor variables are opened with the privelegs of the
> owner of the procedure and behave just like they were completely contained
> within the pl/sql routine. It uses the inputs to decide what database it will
> run a query on.
>
> Here is an example:
>
> create or replace package types
> as
> type cursorType is ref cursor;
> end;
> /
>
> create or replace function sp_ListEmp return types.cursortype
> as
> l_cursor types.cursorType;
> begin
> open l_cursor for select ename, empno from emp order by ename;
>
> return l_cursor;
> end;
> /
>
>
> REM SQL*Plus commands to use a cursor variable
>
> variable c refcursor
> exec :c := sp_ListEmp
> print c
>
> -----------------------------------------------------
>
> and the Pro*c to use this would look like:
>
> static void process()
> {
> EXEC SQL BEGIN DECLARE SECTION;
> SQL_CURSOR my_cursor;
> VARCHAR ename[40];
> int empno;
> EXEC SQL END DECLARE SECTION;
>
> EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();
>
> EXEC SQL ALLOCATE :my_cursor;
>
> EXEC SQL EXECUTE BEGIN
> :my_cursor := sp_listEmp;
> END; END-EXEC;
>
> for( ;; )
> {
> EXEC SQL WHENEVER NOTFOUND DO break;
> EXEC SQL FETCH :my_cursor INTO :ename, empno;
>
> printf( "'%.*s', %d\n", ename.len, ename.arr, empno );
> }
> EXEC SQL CLOSE :my_cursor;
> }
>
> And the java to use this could be:
>
> import java.sql.*;
> import java.io.*;
> import oracle.jdbc.driver.*;
>
> class curvar
> {
> public static void main (String args [])
> throws SQLException, ClassNotFoundException
> {
> String driver_class = "oracle.jdbc.driver.OracleDriver";
> String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";
>
> String query = "begin :1 := sp_listEmp; end;";
> Connection conn;
>
> Class.forName(driver_class);
> conn = DriverManager.getConnection(connect_string, "scott", "tiger");
>
> CallableStatement cstmt = conn.prepareCall(query);
> cstmt.registerOutParameter(1,OracleTypes.CURSOR);
> cstmt.execute();
> ResultSet rset = (ResultSet)cstmt.getObject(1);
>
> while (rset.next ())
> System.out.println( rset.getString (1) );
> cstmt.close();
> }
> }
>
> The following is thanks to marktoml_at_hotmail.com (mark tomlinson)..
>
> If you use ODBC here is a working example, but it requires the use of the
> 8.0.5.2.0 or later Oracle ODBC driver, and an 8.0.5 server.
>
> '
> ' 1) Create a form with 1 Text control (Text1) and 1 List Control
> (List1) and
> ' 1 Button (btnExecute).
> ' 2) The only code that you need is a Click method on your button.
> Here is the Code.
> '
> '
> Private Sub btnExecute_Click()
> 'PL/SQL Code
> '===========
> '
> 'CREATE OR REPLACE package reftest as
> ' cursor c1 is select ename from emp;
> ' type empCur is ref cursor return c1%ROWTYPE;
> ' Procedure GetEmpData(en in varchar2,EmpCursor in out empCur);
> 'END;
> '
> '
> 'CREATE OR REPLACE package body reftest as
> ' Procedure GetEmpData
> '(en in varchar2,EmpCursor in out empCur) is
> 'begin
> ' open EmpCursor for select ename from emp where ename LIKE en;
> 'end;
> 'end;
> '
> Dim cn As New rdoConnection
> Dim qd As rdoQuery
> Dim rs As rdoResultset
> Dim cl As rdoColumn
> Static Number As Integer
>
> List1.Clear
> Number = 0
> cn.Connect = "uid=scott; pwd=tiger; DSN=MSLANGORL;"
> 'enable the MS Cursor library
> cn.CursorDriver = rdUseOdbc
> 'Make the connection
> cn.EstablishConnection rdNoDriverPrompt
>
> sSQL = "{call RefTest.GetEmpData(?,?)}"
>
> Set qd = cn.CreateQuery("", sSQL)
>
> qd.rdoParameters(0).Type = rdTypeVARCHAR
> qd(0).Direction = rdParamInputOutput
> qd(0).Value = Text1.Text
> qd.rdoParameters(1).Type = rdTypeVARCHAR
>
> 'Dynamic or Keyset is meaningless here
> Set rs = qd.OpenResultset(rdOpenStatic)
>
> Do
> Debug.Print
> Debug.Print
>
> Do Until rs.EOF
> For Each cl In rs.rdoColumns
> If IsNull(cl.Value) Then
> List1.AddItem "(null)"
> ' Debug.Print " "; cl.Name; "NULL"; Error trap for
> null fields
> Else
> List1.AddItem cl.Value
> ' Debug.Print " "; cl.Name; " "; cl.Value;
> End If
> Next
> Debug.Print
> rs.MoveNext
> Loop
> Loop While rs.MoreResults
> cn.Close
>
> End Sub
>
> >I think you can do it with DBMS_OUTPUT.
> >But I've never even bothered to try.
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Share what you know. Learn what you don't.
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Tue Aug 03 1999 - 15:01:10 CDT