PL-SQL
What is PL-SQL
It is Procedural Language extensions to the Structured
▪
Query Language (SQL).
▪It is a combination of SQL along with the procedural
features of programming languages.
▪It can process multiple SQL statements simultaneously
as single block hence reducing n/w traffic.
Features of PL/SQL
1)BLOCK structure : Program is written in block(particular task).
2)Control structures: (IF , FOR loop, WHILE).
3)Exception handling: Allows errors to be detected and handled.
4)Modularity: Allows process to be divided into module
5) Procedural SQL (what & How to perform) : Use Function, procedure, Trigger
and cursor.
6) Highly Productive: works with front end
PL SQL block
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
Declaring constant:
A constant is declared using the CONSTANT keyword. It requires an initial value and does not allow that value to be
changed.
For example − pi constant number =3.1415
To Display string:
▪Use keyword message with dbms_output.put_line();
To embed single quotes within a string literal, place two single quotes next to each other .
Example- message varchar2(30):= ‘We SYBBA students’;
Use of %Type:
▪Assign same type to variable as that of relation column declared in database.
▪If any type mismatched assignment and comparison may not work.
▪If you change datatype definition then datatype of variable changes runtime
Example: my_title [Link]%type
Operators: Arithmetic operator
Operator Description Example
+ Adds two operands A + B will give 15
Subtracts second
- A - B will give 5
operand from the first
Multiplies both
* A * B will give 50
operands
Divides numerator by
/ A / B will give 2
de-numerator
Exponentiation
operator, raises one A ** B will give
**
operand to the power 100000
of other
Relational operator
ator Description Example
Checks if the values of two operands are equal or not, if yes then condition
= (A = B) is not true.
becomes true.
!=
Checks if the values of two operands are equal or not, if values are not equal
<> (A != B) is true.
then condition becomes true.
~=
Checks if the value of left operand is greater than the value of right operand,
> (A > B) is not true.
if yes then condition becomes true.
Checks if the value of left operand is less than the value of right operand, if
< (A < B) is true.
yes then condition becomes true.
Checks if the value of left operand is greater than or equal to the value of (A >= B) is not
>=
right operand, if yes then condition becomes true. true.
Checks if the value of left operand is less than or equal to the value of right
<= (A <= B) is true
operand, if yes then condition becomes true.
Logical operators:
Operator Description Examples
Called the logical AND operator. If both the
and operands are true then condition becomes (A and B) is false.
true.
Called the logical OR Operator. If any of the
or two operands is true then condition becomes (A or B) is true.
true.
Called the logical NOT Operator. Used to
reverse the logical state of its operand. If a
not not (A and B) is true.
condition is true then Logical NOT operator
will make it false.
Loops in PL SQL
▪when you need to execute a block of code several number of times.
▪IF -then : condition is true- if statement executes.
condition is false –does nothing.
▪If –else : either of sequence executes.
▪For loop: specify range.
▪Case : conditional logic.
▪
Exception handling plsql
▪An exception is an error condition during a program execution.
▪It is an event that changes the normal flow of the program, during program execution.
DECLARE
BEGIN
EXCEPTION
WHEN ex_name1 THEN
//Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
//Error handling statements
END;
/
TYPES
1)user defined exception:
▪user-defined can raise exceptions according to the need of program.
▪ A user-defined exception must be declared and then raised explicitly, using either a RAISE statement or
the procedure
e.g:
DECLARE
my-exception EXCEPTION;
Predefined Exceptions
PL/SQL provides many pre-defined exceptions, which are executed when any database rule is violated by a program.
For example, the predefined exception NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows.
Exception Oracle Error SQLCODE Description
ACCESS_INTO_NULL 06530 -6530 It is raised when a null object is automatically assigned a value.
CASE_NOT_FOUND 06592 -6592 It is raised when none of the choices in the WHEN clause of a CASE
statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL 06531 -6531 It is raised when a program attempts to apply collection methods other than EXISTS
to an uninitialized nested table or varray,
DUP_VAL_ON_INDEX 00001 -1 It is raised when duplicate values are attempted to be stored in a column with
unique index.
INVALID_CURSOR 01001 -1001 It is raised when attempts are made to make a cursor operation that is
not allowed, such as closing an unopened cursor.
VALID_NUMBER 01722 -1722 It is raised when the conversion of a character string into a number fails
because string does not represent a valid number.
LOGIN_DENIED 01017 -1017 It is raised when a program attempts to log on to the database with
an invalid username or password.
NO_DATA_FOUND 01403 +100 It is raised when a SELECT INTO statement returns no rows.
NOT_LOGGED_ON 01012 -1012 It is raised when a database call is issued without being
connected to the database.
PROGRAM_ERROR 06501 -6501 It is raised when PL/SQL has an internal problem.
ROWTYPE_MISMATCH 06504 -6504 It is raised when a cursor fetches value in a variable having incompatible
data type.
SELF_IS_NULL 30625 -30625 It is raised when a member method is invoked, but the instance of the
object type was not initialized.
STORAGE_ERROR 06500 -6500 It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS 01422 -1422 It is raised when a SELECT INTO statement returns more than one row.
VALUE_ERROR 06502 -6502 It is raised when an arithmetic, conversion, truncation, or sizeconstraint
error occurs.
ZERO_DIVIDE 01476 1476 It is raised when an attempt is made to divide a number by zero.
PL/SQL Procedure
▪It is named block that performs a specific task.
▪We stores the procedure in the database, and you can execute it repeatedly.
▪procedure to encapsulate a reusable code block.
▪Header: The header part contains the name of the procedure and the parameters passed to the procedure.
Body: The body part contains declaration section, execution section and exception
Creating a Procedure
A procedure is created with the CREATE OR REPLACE PROCEDURE statement.
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN /OUT / IN OUT] type [, ...])]
{IS / AS}
BEGIN
< procedure_body >
END procedure_name;
Methods for Passing Parameters
When calling procedures with parameters, we have two main methods:
Positional Notation: We pass parameters in the order they're defined.
In positional notation, you can call the procedure as −
findMin(a, b, c, d);
Named Notation: We specify which parameter each value corresponds to
. named notation, the actual parameter is associated with the formal parameter using the
In
arrow symbol ( => ).
The procedure call will be like the following −
findMin(x => a, y => b, z => c, m => d);
Deleting a Procedure :
DROP PROCEDURE Pro_name;
Cursors:
➢Oracle creates a memory area, known as the context area, for processing an SQL statement.
➢ It contains all the information needed for processing the statement
for example, the number of rows processed, etc.
➢A cursor is a pointer to this context area.
➢PL/SQL controls the context area through a cursor. A cursor holds the rows (one or more) returned by a SQL
statement. The set of rows the cursor holds is referred to as the active set.
Implicit Cursors
Automatically created by Oracle whenever an SQL statement is executed (Programmer cant control)
Cursor associated with
1) Insert – Cursor holds data which need to be inserted.
2) Update- Identifies the row which get affected
3) Delete-
No Attribute & Description
1 %FOUND
Returns TRUE i f an IN SERT, UPDATE, or DELETE statement affected one or more rows or a SELE CT INTO statement returned one or m ore rows. Otherwise, it returns FALSE.
2 %NOTFOUND
The logi cal opposi te of %FOUN D. It returns TRUE i f an IN SERT, UPDATE, or DELETE statement affected no row s, or a SE LECT INTO statement returned no rows. Otherwise, it returns FALSE.
3 %ISOPEN
Always returns FALSE for implicit cursors, because Oracl e closes the SQL cursor automatical ly after executing its associated SQL statement.
4 %ROWCOUNT
Returns the number of rows affected by an INSERT, UP DATE, or DELETE statement, or returned by a SELECT INTO statement.
;
Explicit cursor :
➢Explicit cursors are programmer-defined cursors for gaining more control over the context area.
➢An explicit cursor should be defined in the declaration section of the PL/SQL Block.
➢It is created on a SELECT Statement which returns more than one row.
Syntax: CURSOR cursor_name IS select_statement;
CURSOR c_customers IS SELECT id, name, address FROM customers;
OPEN c_customers;
FETCH c_customers INTO c_id, c_name, c_addr;
CLOSE c_customers;
Functions
➢A function is same as a procedure except that it returns a value.
➢Return Type : The header section defines the return type of function.
➢Functions must return a value using the RETURN statement
➢It performs specific task ,can read list of values but it will explicitly return single value.
Syntax :CREATE [OR REPLACE] FUNCTION function name
[(parameter name [IN | OUT | IN OUT] type [, ...])]
RETURN return datatype
{IS | AS}
BEGIN
< function body >
END [function name];
Function vs Procedure
[Link] Function Procedure
The procedure can return a value using "IN
1. Functions always return a value after the execution of queries.
OUT" and "OUT" arguments.
In SQL, those functions having a DML statement can not be called from
A procedure can not be called using SQL
2. SQL statements. But autonomous transaction functions can be called
queries.
from SQL queries.
Procedures are compiled only once but they can
Each and every time functions are compiled they provide output
3. be called many times as needed without being
according to the given input.
compiled each time.
A Function can not return multiple result sets.
4. A procedure is able to return multiple result sets.
The function can be called using Stored Procedure. While procedures cannot be called from
5.
function.
A procedure can be used to read and modify
6. A function used only to read data. data.
Triggers :
➢Triggers are stored programs, which are automatically executed or fired when some events occur.
➢They are stored in the database and invoked repeatedly in a particular scenario. There are two states of the
triggers, they are enabled and disabled. When the trigger is created it is enabled.
They are associated with response-based events such as a
▪Database Definition Language statements such as CREATE, DROP or ALTER
▪Database Manipulation Language statements such as UPDATE, INSERT or DELETE.
▪Database operations such as LOGON, LOGOFF, STARTUP, and SHUTDOWN .
Key Features of Triggers:
[Link]-driven: Activated by events like INSERT, UPDATE, or DELETE.
[Link]: Can execute BEFORE or AFTER the triggering event.
[Link]: Can be defined for each row (FOR EACH ROW) or for the entire statement
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
DECLARE
-- Optional declarations
BEGIN
-- Trigger logic
END;
Access the value of column inside
trigger
A value of column of row level trigger can be accessed using NEW and OLD variable
1) INSERT : The value of the field to be inserted must be use with :NEW
2) UPDATE : original value accessed with :OLD and the new values will be use by :NEW
3) DELETE : Value in this case must be use with :OLD
Packages
➢Packages are a way to group related procedures, functions, variables, and other PL/SQL constructs into a single unit.
➢They help in organizing code, improving reusability, and enhancing performance by loading the entire package into
memory when any part of it is accessed.
➢Package Specification :
➢It just DECLARES the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from
outside the package.
➢All objects placed in the specification are called public objects.
CREATE OR REPLACE PACKAGE my_package AS
PROCEDURE my_procedure;
FUNCTION my_function RETURN NUMBER;
v_public_variable NUMBER;
END my_package;
Package Body:
▪Contains the implementation of the procedures and functions declared in the specification.
▪Can also include private elements (not accessible outside the package).
CREATE OR REPLACE PACKAGE BODY my_package IS
PROCEDURE greet_user(name IN VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, ' || name || '!');
END greet_user;
FUNCTION calculate_sum(a IN NUMBER, b IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN a + b;
END calculate_sum;
END my_package;
BENEFITS
The needs of the Packages are described below:
• Modularity: Packages provide a modular structure, allowing developers to organize and manage code efficiently.
• Code Reusability: Procedures and functions within a package can be reused across multiple programs, reducing
redundancy.
• Private Elements: Packages support private procedures and functions, limiting access to certain code components.
• Encapsulation: Packages encapsulate related logic, protecting internal details and promoting a clear interface to other
parts of the code.