The differenced between procedures and functions

• Both take arguments which can be of any mode.
• Both are different forms of PL/SQL blocks, with a declarative, executable, and exception section.
• Both can be stored in the database or declared within a block.
• Both can be called using positional or named notation.
• Procedure call is a PL/SQL statement by itself, while a function call is called as part of any expression.
• A function call is an rvalue.
• If there is more than one returns value, use a procedure, if there is only one return value, a function can be used.

Restrictions on calling functions from SQL statements

• Purity level are WNDS (write no database state) , RNDS (read no database state),WNPS (writes no package state),RNPS (reads no package state).
• The PL/SQL engine can determine the purity level of stand-alone functions.
• When the function is called from a SQL statement the purity level is checked. Not like the package functions.
• Any function called from a SQL statement cannot modify any database tables (WNDS).
• In order to be executed remotely function must have WNPS, RNPS.
• Functions called from the SELECT, VALUES, SET can write packages variables.
• A function is only as pure as the subprograms it calls.
• Stored function cannot be called in CHECK constraint clause of CREATE TABLE or ALTER TABLE.
• You have to use positional notation and not named notation and all parameters must be specified.
• User defined function must also meet the following requirements.
• The function has to be stored in the database either stand-alone or as part of a package.
• The function can take only IN parameter not IN OUT or OUT
• The formal Parameter must use only database types.
• The return type must be a database type.
• The function must not end the current transaction with COMMIT or ROLLBACK.
• It must not issue any ALTER SESSION or ALTER SYSTEM command

List how a function can be invoked

• From SQL statement if it has the restrictions.
• When you call a function from SQL*Plus you need to assign the returned value a bind variable, and you need to EXECUTE command to execute the function like create a SQL*plus environment variable X and issue the command EXECUTE :X := function();

Create a function

• There can be more than one RETURN statement in a function, only one of them will be executed.
• The return value cannot have length or precision or any constraint.
• It is an error for a function to end without executing a RETURN, the function return type is required, because the function call is part of an expression.

Describe the DEFAULT option for parameters

• The formal parameters to ap procedure or function can have default values.
• If parameter has a default value, it does not have to be passed from the calling environment.
• If it is passed, the valued of the actual parameter will be used instead of the default.
• If positional notation is used, all parameters with default values that don’t have an associated actual parameter must be at the end of the parameter list.

List the methods for calling a procedure with parameters

• Positional notation: The actual parameters are associated with the formal parameters by position.
• Named notation: The formal parameter and the actual parameter are both included for each argument.
• In the positional the parameters with default values must be at the end of the argument list.
• Positional and named notation can be mixed in the same call as well, The first arguments must be specified by position, and the remaining arguments can be specified by name.
• The subprogram can be with no parameters.

Create a procedure with parameters

In a procedure declaration, it is illegal to constrain CHAR and VARCHAR2 parameters with a length and NUMBER parameters with a precision and /or scale because the constraints will be taken from the actual parameters.

List the types of parameter modes

• Formal parameters can have three modes – IN, OUT or IN OUT. The default is IN.
• We can’t change the value of IN parameter, it is considered read-only and cannot be changed, if we do so we got a compilation error.
• Any value the actual parameter has when the procedure is called is ignored. So it has the value of NULL.
• If the procedure raises an exception, the values of IN OUT, OUT format parameters are not copied to their corresponding actual parameters.
• Actual IN, IN OUT parameters must be a variable and cannot be a constant or expression.
• You can read from OUT parameter not prior to 7.3.4 or 8.0.3

Describe the difference between formal and actual parameters

• Parameters in the procedure declaration known as formal parameters. Actual parameters contain the values passed to the procedure when it is called.

• Actual parameters contain the values passed to the procedure when it is called.
• The formal parameters are the placeholders for the values of the actual parameters.
• The constraints CHAR and VARCHAR2 for parameters should always come from the actual parameters.
• The only way to get constraint on a formal parameter is to use %TYPE.

Create a procedure

• We create a procedure with the CREATE OR REPLACE PROCEDURE.
• When a procedure is created, it is first compiled and then stored in the database in compiled form.
• A procedure call is a PL/SQL statement by itself, it is not called as part of an expression.
• A procedure is PL/SQL block, with a declarative section, an executable section, and an exception-handling section. Only the executable section is required.
• The create statement is DDL operation, so an implicit commit is done both before and after the procedure is created.
• Either IS or AS keyword can be used.
• There is no declaring keyword in a procedure or function declaration.

Define a subprogram in the declarative section of a procedure

o We called local subprogram.
o It is only visible in the block in which it is declared.
o Its scope extends from the point of declaration until the end of the end of the block.
o Any local subprogram must be declared at the end of the declarative section.
o We can have overloading local subprograms.

Define what a stored procedure is

• Procedures is also known as subprograms.
• A stored procedure must have at least one executable statement in the procedure body.

Overview of PL/SQL Programs

• Describe a PL/SQL program construct:
• There are two main kinds of PL/SQL blocks anonymous and named.
• An anonymous block is compiled each time it is issued. And is not stored in the database, and cannot be called directly from other PL/SQL blocks.
• Named blocks are procedures, functions, packages, and triggers.
• They can be stored in the database and run when appropriate.
• PL/SQL uses early binding to execute SQL statement. So only DML statements can be included directly in PL/SQL block but we can with the DBMS_SQL and execute immediate.
• Describe how a stored procedure /function is invoked:
• Once procedure is created , we can call it from another PL/SQL block procedure_name(parameters);
• EXECUTE will invoke a procedure form iSQL*Plus.