PL SQL Document

Document Sample
PL SQL Document Powered By Docstoc
					1. if you do not intialize a value to a local variable then by default a null value will be intialized

2. Not null variable must be intialized only at the place of declaration

3. null value compared with any existing value within an if condition by default causes the control of the
program to execute the statement within the else part.

4. % type is an attribute which when used is enabling the programmar to automatically visualize the
changes in a variable whenever a change is made to the column of a table provided the variable had
been declared with respect to that column

usage of this attribute : <variable name> <table name> . <col                               name> %type;

5. Into operator : This operator is used to copy the data from a column into the variable provided the
variable is used for display or calculation

usage : select <col name> into <output variable name> from                  <table name> where <col name>
= <input variable        name>;

6. %rowtype is an attribute that can be used to declare 1 variable which can hold the data from multiple
columns in the same table

usage : <variable name> <table name> %rowtype;

7. ANY ERROR which had been reported by the data base which is of the form ORA- <Number>, then
that type of error is referred to as a run time error.

8. ANY ERROR which had been reported by the data base which is of the form SP OR PLS - <Number>,
then that type of error is referred to as a COMPILE time error.

9. Raise_application_error() --> This statement has to be used to print a message as a error message.
this statment will accept 2 parameters

p1 --> error no (range : -20001 to +20990).

-20001 is called as a standard error no which is compatbile on every version of oracle starting from
oracle 7.x

p2 --> message

10. in a function, the identifier to be used for a argument and a return variable cannot be the same

11. You can call a function in oracle without passing any parameters is by defining those parameters as
default pars using the default key word.

12. Default pars must be positioned at the last in a function prototype

13. Whenever a function had been defined for performing any updation operation within a table then
that function has to be called from a block and not by using a select command

differences between procedures and functions

1. function returns utmost only 1 value

2. procedure can logically return more than 1 value

1. procedure cannot return any value

2. function returns a value

1. Function cannot be defined with 0 pars but can be called with 0 pars

2. procedure can be defined and called with 0 pars

1. cursors can be defined in a procedure because a procedure can return more than 1 value
2. cursors cannot be defined in a function because function utmost returns only 1 value

1. function can be called from a procedure

2. procedure cannot be called from a function because a procedure cannot return a value. but you can
call one procedure from another procedure

1. functions cannot used to raise an exception

2. procedures can be used to raise and handle exceptions

1. Functions can accept only IN parameters

2. Procedure can accept IN, OUT and IN OUT parameters

syntax for defining a procedure

create [or replace] procedure <procedure name>(args) is

[<local variable declarations>];



end [<procedure name>];

calling a procedure:

a. exec <procedure name>(args)

out parameters:

These type of parameters have to be defined in a procedure for the following reasons:

1. If you expect a procedure to return a value, the return value can be stored in a OUT par
2. If you want the value of a procedure to be stored in a session variable

steps of usinf OUT pars:

1. Out pars should be declared as session variables :

sql > var <variable name> <data type>

2. The session variables have to be used as bind variables in the procedure call

sql > exec <procedure name>(: <session variable name>) (note no space between : and variable name)

if the parameter to be passed to a procedure needs to be modified then that parameter has to be
declared as IN OUT par. In out par has to be declared as a session variable and using the block u can
assign a value to that parameter like:


        : <session var name > := <value>;


call the procedure using in out pars

exec <proc name>(:<session var name>)

packages : It is a collection of procedures, functions, cursors and variables

why to create a package?

1. procedure cannot be compiled from a different user privilage
wheras a package can be compiled from a different user privilage hence the procedure needs to be
defined within a package

how to create a package?

in order to create a package, first of all we have to create a package specification package specification
contains the prototypes of all the procedures, functions and also some local variable declarations.


create or replace package <package name> is

function <function name>(args) return <data type>;

procedure <procedure name>(args);


question : can you define a procedure in the package body which is not declared in the package

procedure cannot be defined in the package body without declaring it's prototype in the package
specificication because the procedure call must always match with the procedure prototype at run time

procedure declared in a package specification and not defined within the package body also amounts to
a compile time error.

question : can you declare a variable in a package spec and intialize that var in the package body?

Shared By: