| dynamic query [message #295109] |
Mon, 21 January 2008 06:25  |
timor
Messages: 12
Registered: December 2007
Location: egypt
|
Junior Member |
|
|
dear all
i need to make a dynamic query
to decide the conditions dynamic
i did it on the single query
but i want to do it at a multi row return query
something like cursor and we manipulate the data from it
i tried to use ref cursor but it doesn't work
i hope to find it
bye
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: dynamic query [message #295281 is a reply to message #295257] |
Tue, 22 January 2008 00:07  |
akramrabaya
Messages: 41
Registered: November 2007
|
Member |
|
|
/*** this is an example *****/
declare
cursor c1 is select emp_no,emp_name,salary from emplyoee
where salary >5000
v_emp_no number;
v_emp_name varchar2(50);
v_salary number;
begin
open c1;
loop
fetch c1 into v_emp_no,v_emp_name,v_salary
exit when c1% notfound;
update employee set
salary = salary * 1.05
where emp_no=v_emp_no;
insert into increment values(emp_no,emp_name,v_salary*1.05 - v_salary);
end loop;
close c1;
commit;
end;
/****** akram *******/
|
|
|
|
| Re: dynamic query [message #295285 is a reply to message #295281] |
Tue, 22 January 2008 00:24 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
True, this is an example, but isn't related to a problem and, besides, wouldn't work. You fetched values into 'v_' variables but never used them. Also, you've used bunch of PL/SQL statements for something that can be rewritten asINSERT INTO increment
(emp_no, emp_name, salary)
(SELECT emp_no, emp_name, salary * 0.05); |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
;