Home » Developer & Programmer » JDeveloper, Java & XML » Multiple sql statement in 1 store procedure
Multiple sql statement in 1 store procedure [message #91545] Mon, 04 November 2002 20:27 Go to next message
Irene
Messages: 11
Registered: February 2002
Junior Member
Hi all,

Does anyone have any sample on multiple sql statements in one stored procedure?? but the results capture is based on the results of the previous sql statement.

Eg Statement 1: select id from table where condition applies;

Statement 2: select distinct address from table where stud_id = id(which is retrieved from the first statement);

Statement 3: select distinct country from table where add = address(which is retrieved from the second statement);

How can I do about it?? Can anyone help me?? Thanks alot.
Re: Multiple sql statement in 1 store procedure [message #91548 is a reply to message #91545] Tue, 05 November 2002 10:49 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
you can use EXPLICITY CURSORS as shown here (very flexible) OR IMPLICIT CURSORS (just sql).
SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure tnov5
  2  as
  3  dno dept.deptno%type;
  4  cursor c1 is select * from dept;
  5  cursor c2 is select * from emp where deptno=dno;
  6  begin
  7     for mag1 in c1 loop
  8             exit when c1%notfound;
  9             dno:=mag1.deptno;
 10             for mag2 in c2 loop
 11                     exit when c2%notfound;
 12                     dbms_output.put_line(mag2.ename||','||mag2.deptno||','||mag1.loc);
 13             end loop;
 14     end loop;
 15* end;       
SQL> /

Procedure created.

SQL> set serveroutput on
SQL> exec tnov5
CLARK,10,NEW YORK
KING,10,NEW YORK
MILLER,10,NEW YORK
SMITH,20,DALLAS
JONES,20,DALLAS
FORD,20,DALLAS
ALLEN,30,CHICAGO
WARD,30,CHICAGO
MARTIN,30,CHICAGO
BLAKE,30,CHICAGO
TURNER,30,CHICAGO
JAMES,30,CHICAGO

PL/SQL procedure successfully completed.

SQL> 

Re: Multiple sql statement in 1 store procedure [message #91552 is a reply to message #91545] Tue, 05 November 2002 23:27 Go to previous messageGo to next message
Irene
Messages: 11
Registered: February 2002
Junior Member
hi.. can i ask you some questions??
what are you trying to do at this line??
dno dept.deptno%type;

the mag1 and mag2 are variables?? do not need to declare??

by the way, extra one question on my mind.. Can procedure return resultsets back to my jsp pages if my jsp page calls for the procedure??

Sorry to ask you so much. :)
Thanks for your help.
Re: Multiple sql statement in 1 store procedure [message #91554 is a reply to message #91545] Wed, 06 November 2002 18:36 Go to previous messageGo to next message
Irene
Messages: 11
Registered: February 2002
Junior Member
hi.. I make something out.. but didnt display me anything just put procedure successfully completed.. How can I know it had gotten the results I wan.. Here is my procedure

CREATE OR REPLACE PROCEDURE sp_procedure(yearVar IN integer, etyVar IN varchar, optVar IN varchar, valVar IN integer)
AS
cp table.cost_id%TYPE;
ety table.entity%TYPE;
mth table.month%TYPE;
CURSOR cursor1 is SELECT cost_id FROM table WHERE year = yearVar and entity = etyVar and optVar = 'valVar';
BEGIN
OPEN cursor1;
LOOP
FETCH cursor1 INTO cp;
EXIT WHEN cursor1%notfound;
dbms_output.put_line(cp);
END LOOP;
CLOSE cursor1;
END sp_procedure;

Do you think it will capture anything?? I scare that it would be the same as my function capture no data.
Re: Multiple sql statement in 1 store procedure [message #91556 is a reply to message #91545] Thu, 07 November 2002 05:27 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
if you use dbms_output ( sqlplus)
make sure u have issues

set serveroutput on

executing the procedure.
Re: Multiple sql statement in 1 store procedure [message #91684 is a reply to message #91552] Thu, 09 January 2003 09:09 Go to previous message
Ali
Messages: 88
Registered: March 2000
Member
hello
please tell me what is store procedure and how i can
use it in my program.
please send to me some example.
thank you
Previous Topic: 9i, enable java
Next Topic: BLOB Question in Oracle 9i
Goto Forum:
  


Current Time: Fri Mar 29 02:47:09 CDT 2024