Procedure:
Syntax:
PROCEDURE [schema.]name [( parameter [, parameter ...] ) ]
[AUTHID DEFINER | CURRENT_USER]
IS
[declarations]
BEGIN
executable statements
[ EXCEPTION
exception handlers]
END [name];
Function:
Syntax:
FUNCTION [schema.]name [( parameter [, parameter ...] ) ]
RETURN return_datatype
[AUTHID DEFINER | CURRENT_USER]
[DETERMINISTIC]
[PARALLEL ENABLE ...]
[PIPELINED]
IS
[declaration statements]
BEGIN
executable statements
[EXCEPTION
exception handler statements]
END [ name ];
Note:
A function's execution section should have a RETURN statement in it,although it is not necessary for the function to compile. If, however, your function finishes executing without processing a RETURN statement, Oracle will raise the following error:
ORA-06503: PL/SQL: Function returned without value
PARAMETERS
1. A parameter has a passing mode.
2. A parameter declaration must be unconstrained.
Unconstrained means, the size or the upper limit of the datatype must not be set
Unconstrained Example: VARCHAR2
NUMBER
Constrainted Example : VARCHAR2(60)
The formal parameter and the actual parameter that corresponds to it (when called) must be of the same or compatible datatypes.
The actual parameter values are copied to the formal parameters in the function header and those will be used within the function.
Parameter Modes:
1. IN ( Read only ) : Default Mode
2. OUT ( Write only ):
Any assignments made to OUT parameters are rolled back when an exception is raised in the program. Because the value for an OUT parameter is not actually assigned until a program completes successfully, any intermediate assignments are therefore ignored. Unless an exception handler traps the exception and then assigns a value to the OUT parameter,no assignment is made to that parameter. The variable will retain the same value it had before the program was called.
Example:
Create or Replace Procedure test_out_par
(my_out_var out varchar2,
my_in_var in varchar2)
Is
my_exception exception;
Begin
my_out_var:=my_in_var;
raise my_exception;
End;
/
Declare
my_out_var_kk varchar2(200);
my_in_var_kk varchar2(200);
Begin
my_in_var_kk :='kiran';
my_out_var_kk :='kodavati';
dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
test_out_par (my_out_var_kk,my_in_var_kk);
Exception
when others then
dbms_output.put_line('my_in_var_kk:'|| my_in_var_kk);
dbms_output.put_line('my_out_var_kk:'|| my_out_var_kk);
End;
/
3. IN OUT ( Read / Write )
An IN OUT parameter cannot have a default value.
Matching Actual and Formal Parameters:
1. Positional notation
2. Named notation : (formal parameter => actual parameter)
Note: If we do mix notation, however, we must list all of our positional parameters before any named notation parameters. Positional notation has to have a starting point from which to keep track of positions, and the only starting point is the first parameter.
Advantages of Named notation:
-- We can list the parameters in any order we want.
-- We can also include only the parameters we want or need in the parameter list.
-- Self documenting.
As a general rule, named notation is the best way to write code that is readable and more easily maintained.
Default values:
There are two ways to specify a default value:
1.with the keyword DEFAULT
2.with the assignment operator (:=)
No comments:
Post a Comment