This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Jul 31, 2013 9:18 AM by GTS (DBA) RSS

Procedure Vs Stored Procedure

GTS (DBA) Journeyer
Currently Being Moderated

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 Expert
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated


    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 Guru
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    > 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 Guru
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    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_Arp Guru
    Currently Being Moderated


    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 Guru
    Currently Being Moderated

    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) Journeyer
    Currently Being Moderated

    Guru

     

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

1 2 Previous Next

Legend

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