This discussion is archived
5 Replies Latest reply: Aug 3, 2013 7:34 AM by GTS (DBA) RSS

General doubt  in procedure

GTS (DBA) Journeyer
Currently Being Moderated

Good Morning all;


i am learning procedures  from  PL/SQL.

i don't understand what i red marked following below.


>>  This code collected  from web >>


CREATE OR REPLACE PROCEDURE employer_details

IS

CURSOR emp_cur IS

SELECT first_name, last_name, salary FROM emp_tbl;

emp_rec  emp_cur%rowtype;

BEGIN

FOR emp_rec  in  sales_cur

LOOP

dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name || ' ' ||emp_cur.salary);

END LOOP;

END;

/

 

REF_LINK : http://plsql-tutorial.com/plsql-procedures.htm

 

My questions  are :

sales_cur   indicates  what  ?

when using for loop  can we use variable_name to start loop ?


I want to say i am trying to execute  above code for my personal database  according to my emp table.

if i dont under above doubt i cannot finish my self - practice.


SQL> desc emp;

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

EID                                                      NUMBER

ENAME                                              VARCHAR2(15)

EQUAL                                              VARCHAR2(10)

ESALARY                                           VARCHAR2(15)

ECITY                                                 VARCHAR2(15)

EPERK                                                NUMBER

ECONTACT_NO                                NUMBER

 

Thanks  in  advance !

  • 1. Re: General doubt  in procedure
    chris227 Guru
    Currently Being Moderated

    Reading is one thing. Trying another. Just try to run the code in the DB. (Perhaps you may try apex.oracle.com)

     

    You will find out that this will not compile for several reasons.

     

    One is

     

    emp_cur.first_name: This should refer the current row of the cursor loop, thats emp_rec, so it should be emp_rec.first_name.


    Second is


    sales_cur is not declared, instead it's called emp_cur.


    Below you will find the code altered so that it will work in my schema


    CREATE OR REPLACE PROCEDURE employer_details

    IS

    CURSOR emp_cur IS

    SELECT ename FROM emp;

    emp_rec  emp_cur%rowtype;

    BEGIN

    FOR emp_rec  in  emp_cur LOOP

    dbms_output.put_line(emp_rec.ename );

    END LOOP;

    END;


    you will execute it this way


    begin

    employer_details;

    end;

     

    Output in my case is

    KING

    blake

    clark

    jones

    scott

    ford

    ....

  • 2. Re: General doubt  in procedure
    GTS (DBA) Journeyer
    Currently Being Moderated

    @ chris Thanks  for your help.

     

    Reading is one thing. Trying another

                 Yes ,  you are right. i am learning pl/sql. before executing  sample codes i need to understand .

                 when seeing that code , i suspect few things are coming without any declaration.

                 so i asked before executing that code.

     

    Your code works well.  Thanks.

     

    one more question :

     

      when  calling procedure , if i want to use loop means ,

      Always  should i follow  , given method  instead of  numbers ( for i in 1..10000...)

       > FOR emp_rec  in  emp_cur LOOP

  • 3. Re: General doubt  in procedure
    rp0428 Guru
    Currently Being Moderated

    so i asked before executing that code.

    In the future don't post code unless you have either actually tried it yourself or you tell us that you haven't tried it for some reason.

     

    Don't be afraid of breaking Oracle by actually trying things. You learn by doing.

     

    And with web sites like that you get what you pay for. There doesn't even appear to be any way to even contact the site to let them know that the code example they posted is pure garbage.

     

    The Oracle documentation generally has examples to demonstrate functionality and those examples usually work.

     

    The PL/SQL Language doc has examples that use cursors

    http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/static.htm#CHDBJBJEhttp://docs.oracle.com/cd/E18283_01/appdev.112/e17126/static.htm#BABGJBEA

  • 4. Re: General doubt  in procedure
    chris227 Guru
    Currently Being Moderated

    8f953842-815b-4d8c-833d-f2a3dd51e602 wrote:


      when  calling procedure , if i want to use loop means ,

      Always  should i follow  , given method  instead of  numbers ( for i in 1..10000...)

       > FOR emp_rec  in  emp_cur LOOP

     

    Not exactly.

    There a several kind of for loops.

    This one here is a cursor for loop, a loop like for i in 1..10 is a basic ( according to the docs there might be no dedicated name for this kind of loop) for loop.

    Another loop is the forall loop.

     

    So you might find out, that on several occasions the one or the other loop is more appropiate.

     

    Best thing would be to start here FOR LOOP Statement.

    Oracle docs examples are not always the best educative ones but docs are a good starting point.

    From there on, with a basic knowledge, you can go on to other websites, also having the ability to recognize which on is more helpful and which one less.

     

    One additional remark:

    Yes, your are right, an explicit declaration of the for loop variable is not always needed, e.g.

     

    in

     

    for i in 1..3 loop

     

    the variable i doesnt nedd to be declares explicitly.

     

    Message was edited by: chris227 remark added

  • 5. Re: General doubt  in procedure
    GTS (DBA) Journeyer
    Currently Being Moderated

    thanks rp and thanks chris.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points