Forum Stats

  • 3,782,589 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

What is the advantage of using sub procedures in a procedure instead of using package?

gg
gg Member Posts: 120 Blue Ribbon
edited Jan 31, 2016 12:41PM in SQL & PL/SQL

Hi,

   I have many procedures in my company's database and when you read them you find that there are  lots of sub procedures inside them. Can somebody suggest what are the advantages of using sub procedures in a procedure over using a Package where we can use/define procedures and functions?

Regards

Tagged:

Answers

  • royalwzy
    royalwzy Member Posts: 104
    edited Jan 31, 2016 8:46AM

    1.Classify according to business unit;

    2.Easy to management lots procedures and functions;

    gg
  • gg
    gg Member Posts: 120 Blue Ribbon
    edited Jan 31, 2016 9:15AM

    Thanks for the quick response. I didn't get you. Can you be a bit more elaborate?

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Jan 31, 2016 11:47AM
  • Unknown
    edited Jan 31, 2016 11:58AM
    580988 wrote:
    
    Hi,
    
       I have many procedures in my company's database and when you read them you find that there are  lots of sub procedures inside them. Can somebody suggest what are the advantages of using sub procedures in a procedure over using a Package where we can use/define procedures and functions?
    
    Regards
    

    which metric measures advantage?

    quantify advantage.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,623 Red Diamond
    edited Jan 31, 2016 12:36PM

    There's a difference in variable scope retention... that's about all.

  • Unknown
    edited Jan 31, 2016 12:41PM
       I have many procedures in my company's database and when you read them you find that there are  lots of sub procedures inside them. Can somebody suggest what are the advantages of using sub procedures in a procedure over using a Package where we can use/define procedures and functions?

    Post an example showing what YOU mean by 'sub procedures'.

    Are you talking about procedures defined WITHIN another procedure?

    The appropriate code hierarchy to use is almost entirely related to the SCOPE needed.

    Code blocks and variables (procedures, functions, packages, variables) should be defined so that they have the SCOPE needed based on what other code blocks and variables need to access them.

    If a 'sub procedure' should ONLY be used by the procedure it is defined in then that is where it should be defined. It will be PRIVATE to the defining procedure, is NOT visible outside the defining procedure and can NOT be used by any code outside the defining procedure.

    Code blocks declared at the SQL level (CREATE PROCEDURE ...) are public to the schema they are defined in. That scope can be expanded by granting privileges to other users.

    Code blocks declared in a package spec are public to the schema the spec is defined in.

    Code blocks declared in a package body are PRIVATE to that package body, are NOT visible outside the body and can NOT be used by code blocks outside the body.

    Define the SCOPE hierarchy and the code hierarchy follows naturally.

This discussion has been closed.