OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » cursors

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

cursors [message #38278]

Sun, 07 April 2002 11:30

SA
Messages: 37
Registered: March 2002

Member

Hello friends,
I do not understand the use of cursors ,I know it fetches the data but where can we use a cursor only in procedures? how can we see the result of a cursor other than procedures or packages.I mean we can run the result of a procedure on sqlplus environment can we also run the result of the cursor here what is the command? I hope I am clear in my question.

Report message to a moderator

Re: cursors [message #38282 is a reply to message #38278]

Sun, 07 April 2002 19:07

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Hi.
cursor is someting like pointers in C language.
u fetch the data using cursor.( wiz...store it somewhere temporarily). u can do any manipulation to the data that is fetched by the cursor. like trim, padd, concat or validate. all this are done in temporary areas called as context area or the cursor area. u can insert this data again in some other table or do anything u want!!...like setting up some flags etc.
U can display the contents of cursor using the dbms_output only. U can create an anonymous plsql block or a stored procedure. the major advantage of cursors is that you can fetch more thatn one row and u can loop through the resultset and do the manupulations in a secure manner.

set serveroutput on;
declare
cursor c1 is select * from emp;
begin
for var in c1 loop
exit when c1%notfound;
dbms_output.put_line('the employee' || var.ename ||'draws a salary of '|| var.sal);
end loop;
end;

Report message to a moderator

Re: cursors [message #38284 is a reply to message #38278]

Mon, 08 April 2002 01:31

SA
Messages: 37
Registered: March 2002

Member

thankyou mahesh.

Report message to a moderator

Re: explicit cursors [message #38323 is a reply to message #38278]

Wed, 10 April 2002 11:49

Lee, Weimao
Messages: 1
Registered: April 2002

Junior Member

This is my first practice on how to deal with explicit cursor. I'm trying to dynamically loop through all 10 tables in my schema. then list out total number of foreign keys and check condition constraints for each table and detail of all FK and constraints CC. what is the format for declare two explicit cursors and having table cursor be a outer loop in order to get each table, a constraint table cursor be a inner loop to fetch all the FK fields and constrains_CC fields?
see my code below.

SET SERVEROUTPUT ON;
DECLARE
LoopCount1 INTEGER;
LoopCount2 INTEGER;
Currentbl VARCHAR2(30);
CurrentF VARCHAR2(30);
CurrentC VARCHAR2(30);
Currentsc VARCHAR2(30);
CURSOR Tbls_fork IS SELECT table_name FROM user_tables;
CURSOR Tbls_const IS SELECT constraint_name FROM user_constraints;
RowFork Tbls_fork%ROWTYPE;
RowConst Tbls_const%ROWTYPE;
BEGIN
LoopCount1 := 0;
LoopCount2 := 0;
FOR table_name IN Tbls_fork LOOP
Currentbl := Tbls_fork.RowFork;
DBMS_OUTPUT.PUT_LINE('The ' ||Currentbl|| ' table has ' ||totf||' foreign keys defined and '||totc||' check constraints defined.');
FOR constraint_name IN Tbls_const LOOP
WHILE constraint_type = 'F' or 'C' LOOP
IF constraint_type = 'F' THEN
CurrentF := Tbls_const.RowConst;
totf := totf + 1;
DBMS_OUTPUT.PUT_LINE('The foreign constraint name is...: '||CurrentF);
ELSIF constrain_type = 'C' THEN
CurrentC := Tbls_const.RowConst;
Currentsc := search_condition;
totc := totc + 1;
DBMS_OUTPUT.PUT_LINE('The check constraint name is...: '||CurrentC|| 'The condition is...: '||Currentsc);
END IF
LoopCount2 := LoopCount2 + 1;
END loop;
END LOOP;
END LOOP;
LoopCount1 := Loopcount1 + 1;
END;

Report message to a moderator

Previous Topic:	rowid
Next Topic:	please help!!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 05:21:44 CDT 2024