Start Discovering Solved Questions and Your Course Assignments
TextBooks Included
Active Tutors
Asked Questions
Answered Questions
overloadingthe plsql overloads the subprogram names that is you can use similar name for few different subprograms as long as their formal parameters
parameter aliasing to optimize the subprogram call the plsql compiler can decide between the two techniques of the parameter passing with the
parameter default valuesas the illustration below shows you can initialize the in parameters to the default valuesin that way you can pass various
in out modean in out parameter passes initial values to the subprogram being called and return efficient values to the caller within the subprogram
out modean out parameter returns values to the caller of a subprogram within the subprogram an out parameter act like a variable that means that you
in modean in parameter pass the values to the subprogram being called within the subprogram an in parameter acts like a constant and hence it cannot
parameter modes to define the behavior of formal parameters you use the parameter modes the 3 parameter modes in out amp in out can be used with
mixed notationthe fourth procedure call shows that you can mix the positional and named notation in this situation the first parameter uses the
named notationthe second procedure call uses the named notation an arrow gt serve as the relationship operator that associates the formal parameter
positional notationthe first procedure call uses the positional notation the plsql compiler relates the first actual parameter account with the first
positional and named notationyou can write the actual parameters when calling a subprogram using either positional or named notation that is you can
stored subprogramsnormally tools like oracle forms which incorporate the plsql engine can store subprograms locally for later strictly local
in packagesthe forward declarations also group logically related subprograms in the package the subprogram specifications go in the package
forward declarationsthe plsql needs that you declare an identifier before using it and hence you should declare a subprogram before calling it for
declaring subprograms you can declare subprograms in any plsql subprogram block or package but you should declare subprograms at the end of the
return statementthe return statement instantly completes the execution of a subprogram and returns control to the caller the execution then resumes
functions the function is a subprogram that calculates a value the functions and procedures are structured similar except that the functions have
procedures the procedure is a subprogram which performs a specific action you write procedures using the syntax as shown belowprocedure name
advantages of subprogramsthe subprograms give extensibility that is tailor the plsql language to suit your requirements for illustration if you
what are subprogramsthe subprograms are named plsql blocks which can take parameters and be invoked the plsql has 2 types of subprograms known as the
how exceptions are raisedby the run-time system the internal exceptions are raised implicitly as are user-defined exceptions that you have related
redeclaring predefined exceptionskeep in mind that the plsql declares predefined exceptions globally in the package standard therefore you need not
using raiseapplicationerrorthe package dbmsstandard that is supplied with oracle gives language facilities that help your application to interact
using exceptioninitto handle unnamed internal exceptions you should use the others handler or the pragma exceptioninit the pragma is a compiler
scope rulesyou cannot declare an exception twice in the similar block though you can declare the similar exception in 2 different blocks the