| convert cursor into a select into statement. [message #379479] |
Tue, 06 January 2009 13:17  |
decarlomw
Messages: 12
Registered: January 2009
|
Junior Member |
|
|
is there an easy way to convert this cursor into a select into statement.
create or replace procedure p_print_row is
type text_nt is table of VARCHAR2(256);
v_ename_nt text_nt;
cursor c_emp is select first_name from per_all_people_f where employee_number in ('2404', '2405', '2406');
begin
open c_emp;
loop
fetch c_emp bulk collect into v_eName_nt limit 3;
if v_eName_nt.count=3 then
write_log(v_eName_nt(1)||' '||v_eName_nt(2)||' '||v_eName_nt(3));
elsif v_eName_nt.count=2 then
write_log(v_eName_nt(1)||' '||v_eName_nt(2));
elsif v_eName_nt.count=1 then
write_log(v_eName_nt(1));
end if;
exit when c_emp%NOTFOUND;
end loop;
close c_emp;
end;
call p_print_row();
here is the output.
0106:13:14:33.982|3815763|Decarlo Decarlo Decarlo
0106:13:14:33.983|3815763|Stephen Stephen Stephen
0106:13:14:33.984|3815763|Stephen Terrence Terrence
0106:13:14:33.985|3815763|Terrence Terrence
|
|
|
|
|
|
| Re: convert cursor into a select into statement. [message #379484 is a reply to message #379483] |
Tue, 06 January 2009 14:08  |
decarlomw
Messages: 12
Registered: January 2009
|
Junior Member |
|
|
is there an easy way to convert this cursor into a select into statement.?
/* Formatted on 2009/01/06 14:06 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE p_print_row
IS
TYPE text_nt IS TABLE OF VARCHAR2 (256);
v_ename_nt text_nt;
CURSOR c_emp
IS
SELECT first_name
FROM per_all_people_f
WHERE employee_number IN ('2404', '2405', '2406');
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp
BULK COLLECT INTO v_ename_nt LIMIT 3;
IF v_ename_nt.COUNT = 3
THEN
write_log ( v_ename_nt (1)
|| ' '
|| v_ename_nt (2)
|| ' '
|| v_ename_nt (3)
);
ELSIF v_ename_nt.COUNT = 2
THEN
write_log (v_ename_nt (1) || ' ' || v_ename_nt (2));
ELSIF v_ename_nt.COUNT = 1
THEN
write_log (v_ename_nt (1));
END IF;
EXIT WHEN c_emp%NOTFOUND;
END LOOP;
CLOSE c_emp;
END;
here is the output.
0106:13:14:33.982|3815763|Decarlo Decarlo Decarlo
0106:13:14:33.983|3815763|Stephen Stephen Stephen
0106:13:14:33.984|3815763|Stephen Terrence Terrence
0106:13:14:33.985|3815763|Terrence Terrence
|
|
|
|
| Re: convert cursor into a select into statement. [message #379498 is a reply to message #379479] |
Tue, 06 January 2009 17:01 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
Define easy.
SELECT INTO is designed to return one row and always one row. Since you are intending to return multiple rows, then NO, there is no way at all to convert a cursor that returns multiple rows to a SELECT INTO that returns only one.
Easy is relative. If there is only one way to do it, then is that way easy or not?
If you don't like using a cursor and BULK FETCH, you have options.
1) use a FOR LOOP cursor instead. With the newer optimizing plsql compilers, these do their own bulk collect anyway, there is less coding and query logic is local to plsql logic.
2) use a collection type and select into the collection. this sounds most like what you are asking, but you will have to do some testing on your own to figure out if the rest of your plsql can use the collection the same way. If figure you can. Here is the basic syntax:
1) create a database type that maps to the items in your query
2) define a variable in your procedure based on the database type
3) do this
select cast(multiset(select * from (
<you select statement goes here>
)
) as <your type name goes here>
)
into <your collection variable goes here>
from dual;
This satisfies your requirement of doing a select into to get multiple rows in one go.
Also
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
