Home » SQL & PL/SQL » SQL & PL/SQL » HELP UNDERSTANDING CODE (Oracle 9i)
HELP UNDERSTANDING CODE [message #339840] Fri, 08 August 2008 18:00 Go to next message
manningda
Messages: 31
Registered: January 2008
Member
All:

Coding inexperience showing through and looking for some help.

Thanks in advance for all who remember when they had similar questions... Confused

Working to truncate all tables within a schema and found some very code to use as an example. However, have a line in the loop that I don't quite understand the "role" of.

First step to disable all existing constraints...
DECLARE

  CURSOR v_ConstraintRefCur IS 

    				SELECT constraint_name, table_name
    				FROM user_constraints
    				WHERE constraint_type='R'
    				AND table_name IN
      				(SELECT table_name
      				FROM user_tables);
  
  v_SQL VARCHAR2(100);


BEGIN
  FOR v_Cntr
  IN v_ConstraintRefCur 
  LOOP
    v_SQL:='ALTER TABLE '||v_Cntr.TABLE_NAME||
           ' DISABLE CONSTRAINT '||v_Cntr.CONSTRAINT_NAME;
           
    EXECUTE IMMEDIATE v_SQL;
  END LOOP;
    
END;
/




Specifically what's confusing me is the line

 FOR v_Cntr


I don't see where v_Cntr is being declared.

(What's a little more embarrassing is that I think Ive asked this question before)

Can someone help me understand how one would use what appears to me to be the use of a variable that appears to me to be undeclared?

Thanks in advance to those willing to share from their experience!!
Re: HELP UNDERSTANDING CODE [message #339841 is a reply to message #339840] Fri, 08 August 2008 18:24 Go to previous messageGo to next message
nazimm
Messages: 1
Registered: August 2008
Junior Member
v_Cntrl is more like an implicit variable, known as the record_index.

Example Syntax
FOR record_index in cursor_name
LOOP
{.statements.}
END LOOP;

Official terminology
where record_index is a record declared implicitly by PL/SQL with the %ROWTYPE attribute against the cursor specified by cursor_name.

Re: HELP UNDERSTANDING CODE [message #339861 is a reply to message #339840] Sat, 09 August 2008 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you better understand if you read PL/SQL User's Guide and Reference. You can't understand a french book if you didn't learn a little bit french before.

Chapter 6 Performing SQL Operations from PL/SQL
Section Querying Data with PL/SQL
Paragrahs Looping Through Multiple Rows: Cursor FOR Loop and followings.

Regards
Michel

[Updated on: Sun, 10 August 2008 08:20]

Report message to a moderator

Re: HELP UNDERSTANDING CODE [message #339963 is a reply to message #339840] Sun, 10 August 2008 07:28 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
IMHO - you can simplify your code by removing :

AND table_name IN
(SELECT table_name
 FROM user_tables)


HTH.
Re: HELP UNDERSTANDING CODE [message #339976 is a reply to message #339963] Sun, 10 August 2008 11:03 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
michael_bialik wrote on Sun, 10 August 2008 13:28
IMHO - you can simplify your code by removing :

AND table_name IN
(SELECT table_name
 FROM user_tables)


Unless this clause is there to exclude object tables Wink (rather unlikely I admit).
Re: HELP UNDERSTANDING CODE [message #340128 is a reply to message #339840] Mon, 11 August 2008 09:47 Go to previous message
manningda
Messages: 31
Registered: January 2008
Member
Thank you all for excellent information!!

Specific thanks to Michael Cadot for a terrific reference.

I promise Mike that i did have that help link as a reference before posting my question, (and did work to review it as well) -- for whatever reason was not finding that page however.

Thank you so much that is exactly what I was trying to understand!!
Previous Topic: how to extract the same date when it is last day of the month?
Next Topic: two oldest values
Goto Forum:
  


Current Time: Fri Dec 02 20:29:24 CST 2016

Total time taken to generate the page: 0.26116 seconds