This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes retrieve rows Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "retrieve rows" Watch "retrieve rows" New topic
Author

retrieve rows

Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
How to get multiple rows using store procedure ? for example for table

WORKER(name, ssn, age)

if I want to select all rows which name like "%john%". I saw an exmaple like

cursor c_worker(
name in WORKER.name%TYPE) is
select *
from WORKEWR where name like '%name%'
)

TYPE c_worker_table is table of c_worker%ROWTYPE

t_worker c_worker_table;

begin
open c_worker('john');
loop
fetch c_worker BULK COLLECT INTO t_worker;
exit when t_worker.count = 0;
end loop;
end;

Question:

1) do I have to create a "TYPE c_worker_table is table of c_worker%ROWTYPE" to make it work ? Can I bypass this without declaring it ?

2) Is this a SQL or store procedure/function ? it looks more like a SQL right ?

3)If I write a java code to call this SQL, what data do I get from it ? is it a resultset or what ? Unliek stored function, it has no return type, so I am confused.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by Raj Ohadi:
How to get multiple rows using store procedure ? for example for table

WORKER(name, ssn, age)

if I want to select all rows which name like "%john%". I saw an exmaple like

cursor c_worker(
name in WORKER.name%TYPE) is
select *
from WORKEWR where name like '%name%'
)

TYPE c_worker_table is table of c_worker%ROWTYPE

t_worker c_worker_table;

begin
open c_worker('john');
loop
fetch c_worker BULK COLLECT INTO t_worker;
exit when t_worker.count = 0;
end loop;
end;

Question:

1) do I have to create a "TYPE c_worker_table is table of c_worker%ROWTYPE" to make it work ? Can I bypass this without declaring it ?

Yes, you can declare the type of your variables in other ways. %ROWTYPE is used to declare a record with the same types as found in the specified database table, view, or cursor.

There is also a %Type in the procedure. %TYPE is used to declare a field with the same type as that of a specified table's column.

Originally posted by Raj Ohadi:

2) Is this a SQL or store procedure/function ? it looks more like a SQL right ?

It is a PL/SQL procedure.
Originally posted by Raj Ohadi:

3)If I write a java code to call this SQL, what data do I get from it ? is it a resultset or what ? Unliek stored function, it has no return type, so I am confused.


It would return an array of columns of the same return types as c_worker... worker, name, ssn data types.

I have to run a couple errands, post more what you're trying to do... or maybe what you have so far and I can get back to it in a couple hours.
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Raj,

This may help... It is from a PL/SQL tutorial that I modified to remove the parts that you don't need yet and would likely confuse the situation for you. Remember %TYPE and %ROWTYPE are used just to avoid data type mismatches ... you can declare your variable to be VARCHAR2(15) or NUMBER(12,2), but if the column data type changes, length changes, precision, scale changes you will have to modify your program to avoid a data type mismatch. Using %TYPE and %ROWTYPE insulates you from those type of changes.

----------------
A cursor is a variable that runs through the rows of some relation. This relation can be a stored table, or it can be the answer to some query. By fetching into the cursor each row of the relation, we can write a program to read and process the value of each such row.

1) DECLARE

/* Output variables to hold the result of the query: */

2) a myTable.e%TYPE; -- a assigned the same data type as e column in myTable.

3) b myTable.f%TYPE; b assigned the same data type as f column in myTable

/* Cursor declaration: */

4) CURSOR myTableCursor IS

5) SELECT e, f

6) FROM myTable

7) WHERE e < f ;

8) BEGIN

9) OPEN myTableCursor; -- This is an explicit cursor as opposed to the implicit cursor in your example.

10) LOOP

/* Retrieve each row of the result of the above query

into PL/SQL variables: */

11) FETCH myTableCursor INTO a, b;

/* If there are no more rows to fetch, exit the loop: */

12) EXIT WHEN myTableCursor%NOTFOUND;

13) END LOOP;

/* Free cursor used by the query. */

14) CLOSE myTableCursor;

15) END;

Here are explanations for the various lines of this program:

* Line (1) introduces the declaration section.

* Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation myTable. Although we know these types are INTEGER, we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type NUMBER).

* Lines (4) through (7) define the cursor myTableCursor. It ranges over a relation defined by the SELECT-FROM-WHERE query. That query selects those rows of myTable whose first component is less than the second component.

* Line (8) begins the executable section of the program.

* Line (9) opens the cursor, an essential step.

* Lines (10) through (13) are a PL/SQL loop. Notice that such a loop is bracketed by LOOP and END LOOP. Within the loop we find:

o On Line (11), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the row retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type.

o On Line (12), a test for the loop-breaking condition. Its meaning should be clear: %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more rows.

* Line (14) closes the cursor.

* Line (15) ends the PL/SQL program.
Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
Paul, thank you so much for extending your help. I will try it after I am done with some other work. Appreciate it !
Raj Ohadi
Ranch Hand

Joined: Jun 30, 2006
Posts: 316
Paul, let's follow the example you offered, so I select out an "array" of varibles a and b.. Since we are talking about using Java, how do I call this PL/SQL procedure (By the way, PL/SQL procedure is exactly also called stored procedure, right ?) ? Just use standard "CallableStatement" method to get this resultset ?
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
The PL/SQL procedure is a stored procedure if it is stored as a database object (since you can in the Oracle world run pl/sql procedures in stream).

Here is an example of how to call it... and I'm giving you the example because I'm the one trying to learn Java... I've got the PL/SQL thing down pretty well.


I guess it would help if I included the link...

http://download-west.oracle.com/docs/cd/B25329_01/doc/appdev.102/b25108/xedev_jdbc.htm

[ October 28, 2007: Message edited by: Paul Campbell ]
 
GeeCON Prague 2014
 
subject: retrieve rows