5 Replies Latest reply: Aug 3, 2013 9:34 AM by GTS (DBA) RSS

    General doubt  in procedure

    GTS (DBA)

      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

          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)

            @ 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

              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

                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)

                  thanks rp and thanks chris.