1 2 Previous Next 20 Replies Latest reply: Jul 31, 2013 11:18 AM by GTS (DBA) RSS

    Procedure Vs Stored Procedure

    GTS (DBA)

      Hello Good Morning all ;

       

      I am getting confused with Procedure Vs Stored Procedure.

       

      Please say which  is valid one ?

       

      Procedure is  a block of PL/SQL code , it  never stored in database.

      Procedure is  a block of PL/SQL code , it  is named  and  stored within  the database.

      Stored procedure  is  block of PL/SQL code  it  is named  and  stored within  the database.

      someone saying procedure concepts from Oracle for PL/SQL . Stored  procedure concept from  microsoft for SQL SERVER.


      Thanks all ;





        • 1. Re: Procedure Vs Stored Procedure
          Peter vd Zwan

          Hi,

           

          I wouold say both

          1) Procedure is  a block of PL/SQL code , it  never stored in database.

          and 3) Stored procedure  is  block of PL/SQL code  it  is named  and  stored within  the database.

           

          are true.


          sample of 1)

          declare
          procedure hello as
          begin
          DBMS_OUTPUT.PUT_LINE('hello procedure');
          end;
          begin
            DBMS_OUTPUT.PUT_LINE('hello world');
            hello;
          end;
          

          output:

          hello world

          hello procedure

           

          Procedure hello is not stored on the server
          sample of 3)

          create or replace procedure hello_stored as
          begin
          DBMS_OUTPUT.PUT_LINE('hello stored procedure');
          end;
          /
          begin
          hello_stored;
          end;
          

          output:

          hello stored procedure

           

          Procedure hello_stored is first stored on the server and then call in the pl/sql block.

           

          Regards,

          Peter

          • 2. Re: Procedure Vs Stored Procedure
            Frank Kulash

            Hi,

             

            In Oracle,  a stored procedure is named and stored in the database.

             

            The usual name for a block of PL/SQL code that is not stored in the database is Anonymous Block.

             

            It is possible (though not very common) to define a procedure locally within another PL/SQL unit, that is, inside a function, another procedure, or an anonymous block.  These locally-defined procedures are always named.  If they are defined inside a function or another procedure, then they are stored  in the database with that procedure.  If they are defined inside an anonymous block, then they are not stored in the database.

             

            In practice, the word "procedure" is often used loosely.  People who know what they are doing sometimes just say "procedure" to mean either a procedure or a function.  People who don't know what they are doing seem to enjoy saying "procedure" to mean any kind of code, not necessarily PL/SQL.

            • 3. Re: Procedure Vs Stored Procedure
              Etbin

              It's always difficult to grasp the context of such questions:

              At least in Oracle there's also a distinction between Standalone Procedures - Database objects and Packaged Procedures - stored within other Database objects - Package (Specification) and Package Body

               

              Regards

               

              Etbin

              • 4. Re: Procedure Vs Stored Procedure
                GTS (DBA)


                As per  both replies ;  What i understood , is this right ?

                 

                If a PL/SQL  block that is stored in the database without a name .  it is called as  procedure"

                If a PL/SQL  block that is stored in the database with a name.  it is called as  "stored procedure"

                 

                 

                 

                I

                • 5. Re: Procedure Vs Stored Procedure
                  Frank Kulash

                  Hi,

                   

                  I'm not sure what you mean by "a PL/SQL block that is stored in the database without a name".  Everything that is stored in the database has a name.  PL/SQL objects (such procedures, functions, triggers or packages) may contain unnamed blocks, but those unnamed blocks are not procedures.

                   

                  A procedure is basically a PL/SQL block that is stored in the database with a name.  So is a function.  So is a trigger.

                   

                  Etbin has a good point (as usual): it's hard to give a good answer to such short, vague questions without any context.  If you can make your questions details more detailed, we sould have aq better idea of what you want to know.  For example, if you don't understand something you read in a blog or a book, post the entire paragraph that contains the part you don;t understand, as well as a link or reference to the complete work.

                  • 6. Re: Procedure Vs Stored Procedure
                    GTS (DBA)

                    Hi  Frakkulash ;

                     

                    Are you saying  In plsql  there are two types of blocks.  they are ..

                     

                    If blocks  are unnamed , it is called as "Anonymous Block"

                    IF blocks are named that is   stored procedure.

                    • 7. Re: Procedure Vs Stored Procedure
                      SomeoneElse

                      > Please say which  is valid one ?


                      None of the above.

                       

                      Stored procedures can also be written in Java ;-)


                      • 8. Re: Procedure Vs Stored Procedure
                        Frank Kulash

                        Hi,

                         

                         

                         

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

                         

                        Hi  Frakkulash ;

                         

                        Are you saying  In plsql  there are two types of blocks.  they are ..

                         

                        If blocks  are unnamed , it is called as "Anonymous Block"

                        IF blocks are named that is   stored procedure.

                        I wouldn't put it exactly that way.

                         

                        If a PL/SQL block (a complete block, not just a block within some larger PL/SQL unit) is unamed, then it is an anonymous block, and it is not stored in the database

                         

                        PL/SQL that is stored in the database always has a name.  Sometimes these named, stored units are loosely called "stored procedures" for want of a better, standard name, but procedures are only one kind of named, stored PL/SQL object.  Functions, triggers and packages are others.

                        • 9. Re: Procedure Vs Stored Procedure
                          GTS (DBA)

                          Yes . i have confusion on this. but   little knowledge gap.

                           

                          I  am going to collect entire information . i will get back to you. 

                           

                          Thanks for all replies.

                          • 10. Re: Procedure Vs Stored Procedure
                            GTS (DBA)

                            Sorry,  i did not get from  you ! 

                             

                            > Please say which  is valid one ?


                            None of the above.

                             

                            Stored procedures can also be written in Java ;-)


                            - Yes i agree above statement. i have seen this information but i don't know  JAVA.


                             

                            • 11. Re: Procedure Vs Stored Procedure
                              GTS (DBA)

                              Hi  FrankKulash

                               

                              You say in first point  " Everything is stored in the database has a name " .

                               

                              You say in second  point " If a  PL/SQL block,  is unamed, then it is an anonymous block, and it is not stored in the database.

                               

                              First point denotes we can store everything ;  Second point denotes (If PL/SQL is unnamed) we cannot store in database.

                               

                              QUES : what are the things are stored in the database "named and unnamed" ?

                               

                              please clarify , i am getting confusion from given answers.

                               

                              NOTE :  i am learning PL/SQL  individually ,  please provide clean explanations.

                              • 12. Re: Procedure Vs Stored Procedure
                                _Karthick_


                                Pl/SQL offers 4 types of code units

                                 

                                1. Procedure

                                2. Function

                                3. Package

                                4. Anonymous PL/SQL block

                                 

                                Code units 1 , 2 and 3 are named, Meaning need to be given name while defining, Code unit 4 is a unnamed piece of PL/SQL code.

                                 

                                Code units 1 and 2  can be stored in the DB as a stand alone code or can be part of another code unit.

                                 

                                A stand alone Procedure/Function (You can call it Stored procedure/function if you wish) has this syntax.

                                 

                                create or replace procedure <procedure_name>

                                as

                                begin

                                   ...

                                end;

                                /

                                 

                                create or replace function <function_name> return <data_type>

                                as

                                begin

                                   ...

                                end;

                                /

                                 

                                On the other hand a procedure/function which is part of another code unit has this syntax

                                 

                                procedure <procedure_name>

                                is

                                begin

                                   ...

                                end;

                                /

                                 

                                function <function_name> return <data_type>

                                is

                                begin

                                   ...

                                end;

                                /

                                 

                                Generally these will be inside a stand alone code unit like this

                                 

                                create or replace procedure this_is_stored_procedure

                                as

                                  procedure my_internal_procedure

                                  is

                                  begin

                                     ...

                                  end;

                                begin

                                   ...

                                end;

                                 

                                The 3rd coding unit package are only stored in the DB and its syntax is like this

                                 

                                create or replace package <package_name>

                                as

                                   ...

                                end;

                                 

                                create or replace package body <package_name>

                                as

                                   ...

                                end;

                                 

                                Finally the 4th coding unit, the Anonymous block is a Un Named piece of PL/SQL code unit. Something like this

                                 

                                declare

                                   ...

                                begin

                                   ...

                                end;

                                /

                                • 13. Re: Procedure Vs Stored Procedure
                                  Frank Kulash

                                  Hi,

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

                                   

                                  Hi  FrankKulash

                                   

                                  You say in first point  " Everything is stored in the database has a name " .

                                   

                                  You say in second  point " If a  PL/SQL block,  is unamed, then it is an anonymous block, and it is not stored in the database.

                                   

                                  First point denotes we can store everything ;  Second point denotes (If PL/SQL is unnamed) we cannot store in database.

                                   

                                  QUES : what are the things are stored in the database "named and unnamed" ?

                                   

                                  please clarify , i am getting confusion from given answers.

                                   

                                  NOTE :  i am learning PL/SQL  individually ,  please provide clean explanations.

                                  Look carefully.  I said "Everything that is stored in the database has a name."  I suppose it would be more correct to say "Every object that is stored is the database has a name."  A Procedure is an object; it is stored in the database and has a name.  That procedure may contain a nested BEGIN block, which does not have a name.

                                  • 14. Re: Procedure Vs Stored Procedure
                                    GTS (DBA)

                                    Guru

                                     

                                    Many many thanks for crystal clear information. I got it. 

                                    1 2 Previous Next