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.
|
|
|
|
|
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;
|
|
|