Post on 30-May-2018
8/14/2019 [Pl/SQL ] p a g e
1/30
[PL/SQL ] P a g e | 1
Getting Started with PL/SQLBy now you should have a basic understanding of what PL/SQL is and how it relates to other
Oracle products. You should have access to an Oracle database environment either at work
or at home. During the rest of this chapter, you will learn some of the basics of PL/SQL, andyou will write your first Oracle stored function.
PL/SQL Is Block Structured
PL/SQL is referred to as a block structuredlanguage A PL/SQL block is a syntactical unitthat might contain program code, variable declarations, error handlers, procedures,functions, and even other PL/SQL blocks.
The Syntax for a PL/SQL Block
DECLAREvariable_declarations
BEGINprogram_code
EXCEPTIONexception_handlers
END;
In this syntax,-variable_declarations are any variables that you might want to define. Cursor definitions
and nested PL/SQL procedures and functions are also defined here.-program_code refers to the PL/SQL statements that make up the block.
- exception_handlers refers to program code that gets triggered in the event of a runtimeerror or exception.
The declaration section of a PL/SQL block is optional.
New Term - Any variable declarations must immediately follow DECLARE and come beforeBEGIN. The BEGIN and END keywords delimit the procedural portion of the block. This is
where the code goes. The EXCEPTION keyword signifies the end of the main body of code,and begins the section containing exception handling code. The semicolon at the end of the
block, and at the end of each statement, is the PL/SQL statement terminator, and signifies
the end of the block.Blocks such as the one shown in "The Syntax for a PL/SQL Block" form the basis for all
PL/SQL programming. An Oracle stored procedure consists of one PL/SQL block. An Oracle
stored function consists of one PL/SQL block. An Oracle database trigger consists of onePL/SQL block. It is not possible to execute PL/SQL code except as part of a block.
PL/SQL blocks can be nested. One block can contain another block as in the following
example:DECLARE
variable declarations go hereBEGIN
some program codeBEGIN
code in a nested blockEXCEPTION
exception_handling_codeEND;
more program codeEND;
Nesting of blocks is often done for error-handling purposes.
Compiling and Executing a Simple Block
Listing 1.1 Your First PL/SQL Block
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
2/30
[PL/SQL ] P a g e | 2
DECLAREx NUMBER;
BEGIN
x := 72600;END;
/
Tip - The slash at the end tells SQL*Plus that you are done typing PL/SQL code. SQL*Plus
will then transmit that code to the Oracle database for execution. The slash has meaning toSQL*Plus only, not to PL/SQL.
Tip - The slash character must be typed on a line by itself, and it must be the first characteron that line; otherwise, it will get sent to the database and generate an error message.
After you type the slash, SQL*Plus transmits your code to Oracle for execution. After your
code executes, your output should look like the following:
declarex integer;
beginx := 65400;
end;/
PL/SQL procedure successfully completed
OutputWhen it was originally designed, PL/SQL had no output facilities at all. Remember that
PL/SQL is not a standalone language. It is almost always used in conjunction with someother program or tool that handles the input, output, and other user interaction.
Oracle now includes the DBMS_OUTPUT package with PL/SQL, which provides you withsome limited output capabilities. You will learn more about packages during Day 8, "Using
SQL," but for now it's enough to know that you can use the dbms_output.put_lineprocedure as shown in Listing 1.2.
Listing 1.2 PL/SQL Block Showing the Use of the dbms_output.put_line ProcedureDECLARE
x NUMBER;BEGINx := 72600;
dbms_output.put_line('The variable X = ');dbms_output.put_line(x);
END;
/
The dbms_output.put_line() procedure takes exactly one argument and generates a line of
text as output from the database server. In order for you to see that line of text, you musttell SQL*Plus to display it. This is done with the SQL*Plus command:
SQL> SET SERVEROUTPUT ON
Note - It is SQL*Plus that prints the server output on the screen for you to see. You mustremember to execute the SET SERVEROUTPUT ON command, or you won't see any output.
You also can use the SET SERVEROUTPUT OFF command to turn off output when you don'twant to see it.
Alternatives to Retyping
Until now, you have been retyping each PL/SQL block as you tried it. If you made a mistake,
you had to type the code all over again. There are some alternatives to typing PL/SQLstraight into SQL*Plus. Depending on your personal preferences, and on what you are trying
to do, there are three basic ways to go about this:
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
3/30
[PL/SQL ] P a g e | 3
Cut and paste from Notepad.
Execute a text file using the SQL*Plus @ command.
Use the SQL*Plus EDIT command.The first method involves running Windows Notepad, typing your PL/SQL code (or SQL
queries) into it, and then copying and pasting from Notepad into SQL*Plus to execute thedesired code. This method is ideal for experimenting with short snippets of PL/SQL code and
SQL queries. You can keep several related items in the same text file where you can easily
call them up when you want to work on them.The second method makes use of a SQL*Plus command to execute a file. For example, if
you have a text file named test.sql with the code from Listing 1.2, you could execute thatfile by typing this command:
SQL> @c:\a\testThe resulting output would look like:
The variable X =65400
Note - When you're executing a file, the default file extension is .SQL. SQL*Plus looks for
the file first in the default directory and then follows a search path that you can define. Howyou define this path is operating systemspecific and outside the scope of this book. For
details, you should consult the SQL*Plus User's Guide and also your operating systemdocumentation.
Executing commands from a file like this is most useful in cases where you are re-creating a
stored procedure, function, or database trigger and you have the definition already stored inits own text file.
Control Structures
The control structures of PL/SQL are simple yet powerful. Control structures in PL/SQL canbe divided into selection or conditional, iterative and sequential.
Conditional Control (Selection): This structure tests a condition, depending on the
condition is true or false it decides the sequence of statements to be executed. Example
IF-THEN, CASE and searched CASE statements.
IF-THEN
Syntax:IF condition THEN
StatementsEND IF;
Example:
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
4/30
[PL/SQL ] P a g e | 4
IF -THEN-ELSE:
Syntax:
IF condition THEN
StatementsELSEStatements
END IF;
Example:
IF-THEN-ELSIF:
IF condition THEN
StatementsELSIF condition THEN
Statements
ELSEStatementsEND IF;
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
5/30
[PL/SQL ] P a g e | 5
Iterative Control
LOOP statement executes the body statements multiple times. The statements are placed
between LOOP END LOOP keywords.
The simplest form of LOOP statement is an infinite loop. EXIT statement is used inside LOOPto terminate it.
LOOP- END LOOP
Syntax:
LOOPStatements
END LOOP;
Labeling Loops
We can label Loops. A Label is undeclared identifier enclosed between double angle
brackets( Ex. ). The following example demonstrates usage of labels in loops.
Example:
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
6/30
[PL/SQL ] P a g e | 6
WHILE-LOOP
This is similar to LOOP. A condition placed between WHILE and LOOP is evaluated beforeeach iteration. If the condition evaluates to TRUE the statements are executed and the
control resumes at the top of the LOOP. If the condition evaluates to FALSE or NULL thencontrol comes out of the loop.
Syntax:
While condition
LOOPStatements
END LOOP;
FOR LOOP:
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
7/30
[PL/SQL ] P a g e | 7
The FOR LOOP is used to repeatedly execute a set of statements for certain number of
times specified by a starting number and an ending number. The variable value starts at the
starting value given and increments by 1(default and cannot be changed) with eachiteration. The iteration stops when the variable value reaches end value specified.
Syntax:
FOR variable IN rangeLOOP
StatementsEND LOOP;
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
8/30
[PL/SQL ] P a g e | 8
To_Char Function
In Oracle/PLSQL, the to_char function converts a number or date to a string.
Syntax:to_char( value, [ format_mask ], [ nls_language ] )
value can either be a number or date that will be converted to a string.
format_maskis optional. This is the format that will be used to convert value to a string.nls_language is optional. This is the nls language used to convert value to a string.
Examples - Numbers
to_char(1210.73, '9999.9') would return '1210.7'
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73,'$9,999.00')
would return '$1,210.73'
to_char(21, '000099') would return '000021'
Examples - DatesThe following is a list of valid parameters when the to_char function is used to convert adate to a string. These parameters can be used in many combinations.
to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003'
Substr Function
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
9/30
[PL/SQL ] P a g e | 9
In Oracle/PLSQL, the substr functions allows you to extract a substring from a string.
Syntax:
substr( string, start_position, [ length ] )
-string is the source string.
-start_position is the position for extraction. The first position in the string is always 1.
-length is optional. It is the number of characters to extract. If this parameter is omitted,substr will return the entire string.
For example:
substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', 1, 4) would return 'Tech'
substr('TechOnTheNet', -3, 3) would return 'Net'
substr('TechOnTheNet', -6, 3) would return 'The'
substr('TechOnTheNet', -8, 2) would return 'On'
Trunc Function (with numbers)
In Oracle/PLSQL, the trunc function returns a number truncated to a certain number ofdecimal places.
Syntax :trunc( number, [ decimal_places ] )
-numberis the number to truncate.
-decimal_places is the number of decimal places to truncate to. This value must be aninteger. If this parameter is omitted, the trunc function will truncate the number to 0
decimal places.
For example:
trunc(125.815) would return 125
trunc(125.815, 0) would return 125
trunc(125.815, 1) would return 125.8
Mod Function
In Oracle/PLSQL, the mod function returns the remainder ofm divided by n.
Syntaxmod( m, n )
For example:
mod(15, 4) would return 3
mod(15, 0) would return 15
CURSOR
For every SQL statement execution certain area in memory is allocated. PL/SQL allows youto name this area. This private SQL area is called context area or cursor. A cursor acts as a
handle or pointer into the context area. A PL/SQL program controls the context area using
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
10/30
[PL/SQL ]
P a g e |
10
the cursor. Cursor represents a structure in memory and is different from cursor variable.
When you declare a cursor, you get a pointer variable, which does not point anything. Whenthe cursor is opened, memory is allocated and the cursor structure is created. The cursor
variable now points the cursor. When the cursor is closed the memory allocated for thecursor is released.
Cursors allow the programmer to retrieve data from a table and perform actions on that
data one row at a time. There are two types of cursors implicit cursors and explicit cursors.
Implicit cursorsFor SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are
simple SELECT statements and are written in the BEGIN block (executable section) of thePL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL
implicitly declares cursors for all DML statements. The most commonly raised exceptionshere are NO_DATA_FOUND or TOO_MANY_ROWS.
Syntax:
SELECT ename, sal INTO ena, esa FROM EMP WHERE EMPNO = 7844;
Note: Ename and sal are columns of the table EMP and ena and esa are the variables
used to store ename and sal fetched by the query.
Explicit Cursors
Explicit cursors are used in queries that return multiple rows. The set of rows fetched by aquery is called active set. The size of the active set meets the search criteria in the select
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
11/30
[PL/SQL ]
P a g e |
11
statement. Explicit cursor is declared in the DECLARE section of PL/SQL program.
Syntax:CURSOR IS
Sample Code:
DECLARECURSOR emp_cur IS SELECT ename FROM EMP;BEGIN
-------
END;
Processing multiple rows is similar to file processing. For processing a file you need to open
it, process records and then close. Similarly user-defined explicit cursor needs to be opened,before reading the rows, after which it is closed. Like how file pointer marks current position
in file processing, cursor marks the current position in the active set.
Opening CursorSyntax: OPEN ;Example: OPEN emp_cur;
When a cursor is opened the active set is determined, the rows satisfying the where clausein the select statement are added to the active set. A pointer is established and points to
the first row in the active set.
Fetching from the cursor: To get the next row from the cursor we need to use fetchstatement.
Syntax: FETCH INTO ;Example: FETCH emp_cur INTO ena;
FETCH statement retrieves one row at a time. Bulk collect clause need to be used to fetchmore than one row at a time.
Closing the cursor: After retrieving all the rows from active set the cursor should beclosed. Resources allocated for the cursor are now free. Once the cursor is closed the
execution of fetch statement will lead to errors.
Syntax: CLOSE ;
Implicit and Explicit Cursor Attributes
Implicit and Explicitcursorhas 4 attributes. When appended to the cursor name theseattributes let the user access useful information about the execution of a multirow query.
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
12/30
[PL/SQL ]
P a g e |
12
The attributes are:
1. %FOUND: Boolean variable, which evaluates to true if the last fetch, succeeded.2. %NOTFOUND: It is a Boolean attribute, which evaluates to true, if the last fetch
failed. i.e. when there are no rows left in the cursor to fetch.3. %ROWCOUNT: Its a numeric attribute, which returns number of rows fetched by
the cursor so far.4. %ISOPEN: A Boolean variable, which evaluates to true if the cursor is opened
otherwise to false.
Using WHILE:While LOOP can be used as shown in the following example for accessing the cursor values.
Example:
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
13/30
[PL/SQL ]
P a g e |
13
Fetch is used twice in the above example to make %FOUND available.
See below example.
Using Cursor For Loop:
The cursor for Loop can be used to process multiple records. There are two benefits withcursor for Loop
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
14/30
[PL/SQL ]
P a g e |
14
1. It implicitly declares a %ROWTYPE variable, also uses it as LOOP index
2. Cursor For Loop itself opens a cursor, read records then closes the cursor automatically.Hence OPEN, FETCH and CLOSE statements are not necessary in it.
Example:
emp_rec is automatically created variable of %ROWTYPE. We have not used OPEN, FETCH ,and CLOSE in the above example as for cursor loop does it automatically. The above
example can be rewritten as shown in the Fig , with less lines of code. It is called Implicitfor Loop.
ExceptionsAn Exception is an error situation, which arises during program execution. When an error
occurs exception is raised, normal execution is stopped and control transfers to exception-handling part. Exception handlers are routines written to handle the exception. The
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
15/30
[PL/SQL ]
P a g e |
15
exceptions can be internally defined (system-defined or pre-defined) or User-definedexception.
Predefined exception is raised automatically whenever there is a violation of Oracle
coding rules. Predefined exceptions are those like ZERO_DIVIDE, which is raisedautomatically when we try to divide a number by zero. Other built-in exceptions are given
below. You can handle unexpected Oracle errors using OTHERS handler. It can handle allraised exceptions that are not handled by any other handler. It must always be written as
the last handler in exception block. CURSOR_ALREADY_OPEN Raised when we try to openan already open cursor.
DUP_VAL_ON_INDEX When you try to insert a duplicate value into a unique
column
INVALID_CURSOR It occurs when we try accessing an invalid cursor
INVALID_NUMBER On usage of something other than number in place of numbervalue.
LOGIN_DENIED At the time when user login is denied
TOO_MANY_ROWS When a select query returns more than one row and the
destination variable can take only single value.
VALUE_ERROR When an arithmetic, value conversion, truncation, or constraint
error occurs.
Predefined exception handlers are declared globally in package STANDARD. Hence we neednot have to define them rather just use them.
The biggest advantage of exception handling is it improves readability and reliability of thecode.
Errors from many statements of code can be handles with a single handler. Instead ofchecking for an error at every point we can just add an exception handler and if any
exception is raised it is handled by that.For checking errors at a specific spot it is always better to have those statements in a
separate begin end block.
Examples 1: Following example gives the usage of ZERO_DIVIDE exception
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
16/30
[PL/SQL ]
P a g e |
16
Example 2 Following example gives the usage of NO_DATA_FOUND exception
Example 3 Following example gives the usage of DUP_VAL_ON_INDEX. Exception.
The DUP_VAL_ON_INDEXis raised when a SQL statement tries to create a duplicatevalue in a column on which primary key or unique constraints are defined.
More than one Exception can be written in a single handler as shown below.
EXCEPTION
When NO_DATA_FOUND or TOO_MANY_ROWS thenStatements;
END;
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
17/30
[PL/SQL ]
P a g e |
17
User-defined ExceptionsA User-defined exception has to be defined by the programmer. User-defined exceptions are
declared in the declaration section with their type as exception. They must be raisedexplicitly using RAISE statement, unlike pre-defined exceptions that are raised implicitly.
RAISE statement can also be used to raise internal exceptions.
Declaring Exception:
DECLARE
myexception EXCEPTION;BEGIN
------
Raising Exception:
BEGIN
RAISE myexception;-------
Handling Exception:
BEGIN------
----EXCEPTION
WHEN myexception THENStatements;
END;The following example explains the usage of User-defined Exception
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
18/30
[PL/SQL ]
P a g e |
18
What is a Stored Procedure?
A stored procedure is a named PL/SQL block which performs one or more specific task. Aprocedure has a header and a body. The header consists of the name of the procedure and
the parameters or variables passed to the procedure. The body consists or declarationsection, execution section and exception section similar to a general PL/SQL Block.
A procedure is similar to an anonymous PL/SQL Block but it is named for repeated usage.We can pass parameters to procedures in three ways.
1) IN-parameters2) OUT-parameters
3) IN OUT-parametersA procedure may or may not return any value.
General Syntax to create a procedure is:
CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]IS
Declaration section
BEGINExecution section
EXCEPTIONException section
END;
IS - marks the beginning of the body of the procedure and is similar to DECLARE in
anonymous PL/SQL Blocks. The code between IS and BEGIN forms the Declaration section.The syntax within the brackets [ ] indicate they are optional. By using CREATE OR REPLACE
together the procedure is created if no other procedure with the same name exists or theexisting procedure is replaced with the current code.
The below example creates a procedure employer_details which gives the details of the
employee.
CREATE OR REPLACE PROCEDURE employer_detailsIS
CURSOR emp_cur ISSELECT empno, ename, sal FROM emp;
emp_rec emp_cur%rowtype;BEGIN
FOR emp_rec in emp_curLOOP
dbms_output.put_line (emp_rec.empno || ' ' ||emp_rec.ename|| ' ||emp_rec.sal);END LOOP;
END;/
How to execute a Stored Procedure?There are two ways to execute a procedure.
1) From the SQL prompt.
EXECUTE [or EXEC] procedure_name;2) Within another procedure simply use the procedure name.
procedure_name;
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
19/30
[PL/SQL ]
P a g e |
19
PL/SQL Functions
What is a Function in PL/SQL?
A function is a named PL/SQL Block which is similar to a procedure. The major differencebetween a procedure and a function is, a function must always return a value, but a
procedure may or may not return a value.
The General Syntax to create a function is:
CREATE [OR REPLACE] FUNCTION function_name [parameters]RETURN return_datatype;
ISDeclaration_section
BEGINExecution_section
Return return_variable;
EXCEPTIONException section
Return return_variable;END;
-Return Type: The header section defines the return type of the function. The returndatatype can be any of the oracle datatype like varchar, number etc.
- The execution and exception section both should return a value which is of the datatypedefined in the header section.
For example, lets create a function called ''employee_details_func'.
CREATE OR REPLACE FUNCTION employer_details_func
RETURN VARCHAR(20);
ISemp_name VARCHAR(20);BEGIN
SELECT ename INTO emp_nameFROM emp WHERE empno = 8744;
RETURN emp_name;END;
/In the example we are retrieving the first_name of employee with empno 8744 to variable
emp_name.The return type of the function is VARCHAR which is declared in line no 2.
The function returns the 'emp_name' which is of type VARCHAR as the return value in lineno 9.
How to execute a PL/SQL Function?A function can be executed in the following ways.
1) Since a function returns a value we can assign it to a variable.
employee_name:= employee_details_func;If employee_name is of datatype varchar we can store the name of the employee by
assigning the return type of the function to it.2) As a part of a SELECT statement
SELECT employee_details_func FROM dual;3) In a PL/SQL Statements like,
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
20/30
[PL/SQL ]
P a g e |
20
dbms_output.put_line(employee_details_func);This line displays the value returned by the function.
Parameters in Procedure and Functions
How to pass parameters to Procedures and Functions in PL/SQL ?
In PL/SQL, we can pass parameters to procedures and functions in three ways.1) IN type parameter: These types of parameters are used to send values to stored
procedures.
2) OUT type parameter: These types of parameters are used to get values from stored
procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values
from stored procedures.
NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN
type parameter.
1) IN parameter:
This is similar to passing parameters in programming languages. We can pass values to thestored procedure through these parameters or variables. This type of parameter is a read
only parameter. We can assign the value of IN type parameter to a variable or use it in a
query, but we cannot change its value inside the procedure.The General syntax to pass a IN parameter is
CREATE [OR REPLACE] PROCEDURE procedure_name (param_name1 IN datatype,param_name2 IN datatype ...)
param_name1, param_name2... are unique parameter names.
Datatype - defines the datatype of the variable.
IN - is optional, by default it is an IN type parameter.
2) OUT Parameter:
The OUT parameters are used to send the OUTPUT from a procedure or a function. This is awrite-only parameter i.e., we cannot pass values to OUT parameters while executing the
stored procedure, but we can assign values to OUT parameter inside the stored procedureand the calling program can receive this output value.
The General syntax to create an OUT parameter isCREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)
The parameter should be explicitly declared as OUT parameter.
3) IN OUT Parameter:
The IN OUT parameter allows us to pass values into a procedure and get output values fromthe procedure. This parameter is used if the value of the IN parameter can be changed in
the calling program.
By using IN OUT parameter we can pass values into a parameter and return a value to thecalling program using the same parameter. But this is possible only if the value passed to
the procedure and output value have a same datatype. This parameter is used if the valueof the parameter will be changed in the procedure.
The General syntax to create an IN OUT parameter is
CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)
The below examples show how to create stored procedures using the above three types ofparameters.
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
21/30
[PL/SQL ]
P a g e |
21
Example1:Using IN and OUT parameter:
Lets create a procedure which gets the name of the employee when the employee id ispassed.
CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT VARCHAR)
ISBEGIN
SELECT ename INTO emp_nameFROM emp WHERE empno = id;
END;/
We can call the procedure emp_name in this way from a PL/SQL Block.
DECLARE
empName varchar (20);CURSOR id_cur IS SELECT empno FROM emp;
BEGINFOR emp_rec in id_cur
LOOPemp_name (emp_rec.empno, empName);
dbms_output.put_line('The employee ' || empName || ' has empno ' || emp_rec.empno);END LOOP;
END;/
In the above PL/SQL BlockIn line no 3; we are creating a cursor id_cur which contains the employee id.
In line no 7; we are calling the procedure emp_name, we are passing the id as INparameter and empName as OUT parameter.
In line no 8; we are displaying the id and the employee name which we got from theprocedure emp_name.
TriggerA trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete,
Update is executed on a database table. A trigger is triggered automatically when anassociated DML statement is executed.
Syntax of Triggers
The Syntax for creating a trigger is:
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}{INSERT [OR] | UPDATE [OR] | DELETE}[OF col_name]
ON table_name[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]WHEN (condition)
BEGIN--- sql statements
END;
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
22/30
[PL/SQL ]
P a g e |
22
CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with
the given name or overwrites an existing trigger with the same name.
{BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should thetrigger get fired. i.e for example: before or after updating a table. INSTEAD OF is
used to create a trigger on a view. before and after cannot be used to create atrigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggeringevent. More than one triggering events can be used together separated by ORkeyword. The trigger gets fired at all the specified triggering event.
[OF col_name] - This clause is used with update triggers. This clause is used whenyou want to trigger an event only when a specific column is updated.
CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger withthe given name or overwrites an existing trigger with the same name.
[ON table_name] - This clause identifies the name of the table or view to which the
trigger is associated.
[REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old andnew values of the data being changed. By default, you reference the values as
:old.column_name or :new.column_name. The reference names can also be changedfrom old (or new) to any other user-defined name. You cannot reference old values
when inserting a record, or new values when deleting a record, because they do notexist.
[FOR EACH ROW] - This clause is used to determine whether a trigger must fire
when each row gets affected ( i.e. a Row Level Trigger) or just once when the entiresql statement is executed(i.e.statement level Trigger).
WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired
only for rows that satisfy the condition specified.
For Example: The price of a product changes constantly. It is important to maintain the
history of the prices of the products.We can create a trigger to update the 'product_price_history' table when the price of the
product is updated in the 'product' table.1) Create the 'product' table and 'product_price_history' table
CREATE TABLE product_price_history(product_id number(5),
product_name varchar2(32),supplier_name varchar2(32),
unit_price number(7,2) );
CREATE TABLE product(product_id number(5),
product_name varchar2(32),supplier_name varchar2(32),
unit_price number(7,2) );
2) Create the price_history_trigger and execute it.
CREATE or REPLACE TRIGGER price_history_trigger
BEFORE UPDATE OF unit_priceON product
FOR EACH ROWBEGIN
INSERT INTO product_price_history
VALUES(:old.product_id,
:old.product_name,
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
23/30
8/14/2019 [Pl/SQL ] p a g e
24/30
[PL/SQL ]
P a g e |
24
BEGININSERT INTO product_check
Values('After update, statement level', sysdate);End;
/4) AFTER UPDATE, Row Level: This trigger will insert a record into the table
'product_check' after each row is updated.CREATE or REPLACE TRIGGER After_Update_Row_product
AFTERupdate On product
FOR EACH ROWBEGIN
INSERT INTO product_checkValues('After update, Row level',sysdate);
END;/
Now lets execute a update statement on table product.
UPDATE PRODUCT SET unit_price = 800WHERE product_id in (100,101);
Lets check the data in 'product_check' table to see the order in which the trigger is fired.SELECT * FROM product_check;
Output:
Mesage Current_Date------------------------------------------------------------
Before update, statement level 26-Nov-2008Before update, row level 26-Nov-2008
After update, Row level 26-Nov-2008Before update, row level 26-Nov-2008
After update, Row level 26-Nov-2008After update, statement level 26-Nov-2008
The above result shows 'before update' and 'after update' row level events have occured
twice, since two records were updated. But 'before update' and 'after update' statementlevel events are fired only once per sql statement.The above rules apply similarly for INSERT and DELETE statements.
How To know Information about Triggers?We can use the data dictionary view 'USER_TRIGGERS' to obtain information about anytrigger.
The below statement shows the structure of the view 'USER_TRIGGERS'DESC USER_TRIGGERS;
NAME Type--------------------------------------------------------
TRIGGER_NAME VARCHAR2(30)TRIGGER_TYPE VARCHAR2(16)
TRIGGER_EVENT VARCHAR2(75)TABLE_OWNER VARCHAR2(30)BASE_OBJECT_TYPE VARCHAR2(16)
TABLE_NAME VARCHAR2(30)
COLUMN_NAME VARCHAR2(4000)REFERENCING_NAMES VARCHAR2(128)
WHEN_CLAUSE VARCHAR2(4000)STATUS VARCHAR2(8)
DESCRIPTION VARCHAR2(4000)
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
25/30
[PL/SQL ]
P a g e |
25
ACTION_TYPE VARCHAR2(11)TRIGGER_BODY LONG
This view stores information about header and body of the trigger.SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product';
The above sql query provides the header and body of the trigger'Before_Update_Stat_product'.
You can drop a trigger using the following command.DROP TRIGGER trigger_name;
Oracle PackagesA package is a group of procedures, functions, variables and SQL statements created as asingle unit. It is used to store together related objects. A package has two parts,
1. Package Specification or package header2. Package Body.
Package Specification acts as an interface to the package. Declaration of types, variables,
constants, exceptions, cursors and subprograms is done in Package specifications. Packagespecification does not contain any code.
Package body is used to provide implementation for the subprograms, queries for thecursors declared in the package specification or spec.
Advantages:
It allows you to group together related items, types and subprograms as a PL/SQLmodule.
When a procedure in a package is called entire package is loaded, though it happens
to be expensive first time the response is faster for subsequent calls.
Package allows us to create types, variable and subprograms that are private orpublic
Package Syntax:The package specification
PACKAGE package _nameIS [Declarations of variables and types]
[Specifications of cursors][Specifications of modules]
End [package_name];
The package bodyPACKAGE BODY package_name IS
[Declaration of variables and types][Specification and SELECT statement of cursors]
[Specification and body of modules]BEGIN
[Executable statements]EXCEPTION
[exception handlers]
END package_name;
If the specification of the package declares only types, constants, variables, exceptions, and
call specs the package body is not required there. This type of packages only contains globalvariables that will be used by subprograms or cursors.
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
26/30
[PL/SQL ]
P a g e |
26
Example for a bodiless package
For accessing the types, subprograms etc from a package we writePackage_name.type_name
Package_name.subprogram_name
Executing Procedure and function from above package
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
27/30
[PL/SQL ]
P a g e |
27
The following package spec and package body declare and define 2 procedures. First theprocedure is used to insert new records into the table DEPT, where the second procedure
deletes a record from it.
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
28/30
[PL/SQL ]
P a g e |
28
Private and Public Items in packages
Items declared in package body are private. They can only be accessed within the package.
Whereas items declared in package specification is public and is available outside package.It is explained in the following example.
The package shown in Fig 1 contains a variable Age_limit which is accessed by the program
given below.
Overloading Packaged Subprograms:
Similar to the overloading concept in other programming languages PL/SQL also allows you
to overload its subprograms.
We can have more than one subprogram with the same name within a package
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
29/30
[PL/SQL ]
P a g e |
29
Following examples better explain this concept.
Writing Cursor spec and body separately using Packages
The cursor specification can be written separate from its body using packages. It allows you
to change cursor body without changing the cursor specification. Cursor coded in thepackage spec need to have a return type.
CURSOR cursor_name [(parameter [, parameter]...)] RETURN return_type;
The following examples explain how cursor can be declared and defined using packages.
Prepared By: Jaydev Gajera
MJKCC, Rajkot
8/14/2019 [Pl/SQL ] p a g e
30/30
[PL/SQL ]
P a g e |
30
Below given is the example that explains how to access the cursor values.