PL-SQL Concepts

PL/SQL
Developed by Oracle
Corporation in the
late 1980s as Direct call can also be
procedural extension made from external
Procedural language for SQL and programming
Language/Structured the Oracle relational language calls to
Query Language. database. database.

Programming Completely portable,

Language. high-performance
transaction-
processing language.
Features of PL/SQL
• Tightly integrated with SQL
• Support for different data types.
• Structured programming through functions and procedures.
Advantages of PL/SQL

SQL is the standard database language and PL/SQL is

strongly integrated with SQL.

PL/SQL allows sending an entire block of statements to

the database at one time.

This reduces network traffic and provides high

performance for the applications.
PL/SQL - Data Types
Data Type Category Data Description
Scalar Single values with no internal components.

Composite Data items that have internal components that can be accessed individually.

Reference Pointers to other data items. Explained in 

Using Cursor Variables (REF CURSORs).

Large Object (LOB) Pointers to large objects that are stored separately from other data items, such
as text, graphic images, video clips, and sound waveforms.
PL/SQL Numeric Data Types and Subtypes

S.No Data Type & Description

PLS_INTEGER
1 Signed integer in range -2,147,483,648 through 2,147,483,647,
represented in 32 bits
BINARY_INTEGER
2 Signed integer in range -2,147,483,648 through 2,147,483,647,
represented in 32 bits
BINARY_FLOAT
3 Single-precision IEEE 754-format floating-point number

BINARY_DOUBLE
4 Double-precision IEEE 754-format floating-point number
 NUMBER(prec, scale)
Fixed-point or floating-point number with absolute value in range
5 1E-130 to (but not including) 1.0E126. A NUMBER variable can also
represent 0

DEC(prec, scale)
6 ANSI specific fixed-point type with maximum precision of 38
decimal digits
DECIMAL(prec, scale)
7 IBM specific fixed-point type with maximum precision of 38 decimal
digits
NUMERIC(pre, secale)
8 Floating type with maximum precision of 38 decimal digits
 DOUBLE PRECISION
ANSI specific floating-point type with maximum precision of 126 binary
9 digits (approximately 38 decimal digits)

FLOAT
ANSI and IBM specific floating-point type with maximum precision of
126 binary digits (approximately 38 decimal digits)
10

INT
11 ANSI specific integer type with maximum precision of 38 decimal digits
 INTEGER
ANSI and IBM specific integer type with
12 maximum precision of 38 decimal digits

SMALLINT
ANSI and IBM specific integer type with
13 maximum precision of 38 decimal digits

REAL
Floating-point type with maximum precision of
14 63 binary digits (approximately 18 decimal
digits)
PL/SQL Character Data Types and Subtypes
CHAR
1 Fixed-length character string with maximum size of 32,767 bytes

VARCHAR2
2 Variable-length character string with maximum size of 32,767
bytes
RAW
Variable-length binary or byte string with maximum size of
3 32,767 bytes, not interpreted by PL/SQL

NCHAR
4 Fixed-length national character string with maximum size of
32,767 bytes
 NVARCHAR2
Variable-length national character string with maximum size of 32,767
5 bytes

LONG
6 Variable-length character string with maximum size of 32,760 bytes

LONG RAW
Variable-length binary or byte string with maximum size of 32,760 bytes,
7 not interpreted by PL/SQL

ROWID
8
Physical row identifier, the address of a row in an ordinary table
UROWID
9 Universal row identifier (physical, logical, or foreign row identifier)
PL/SQL Datetime and Interval Types

• The DATE datatype is used to store fixed-length datetimes, which

include the time of day in seconds since midnight.
• Valid dates range from January 1, 4712 BC to December 31, 9999 AD.
• The default date format is set by the Oracle initialization parameter
NLS_DATE_FORMAT.
• For example, the default might be 'DD-MON-YY', which includes a
two-digit number for the day of the month, an abbreviation of the
month name, and the last two digits of the year.
• For example, 01-OCT-12.
 Field Name Valid Datetime Values Valid Interval Values
YEAR -4712 to 9999 (excluding year 0) Any nonzero integer
MONTH 01 to 12 0 to 11
01 to 31 (limited by the values of MONTH and YEAR, according to
DAY the rules of the calendar for the locale) Any nonzero integer

HOUR 00 to 23 0 to 23
MINUTE 00 to 59 0 to 59
00 to 59.9(n), where 9(n) is the precision of time fractional seconds 0 to 59.9(n), where 9(n)
is the precision of
SECOND interval fractional
seconds

TIMEZONE_HOUR -12 to 14 (range accommodates daylight savings time changes) Not applicable
TIMEZONE_MINU 00 to 59 Not applicable
TE
TIMEZONE_REGIO Found in the dynamic performance view V$TIMEZONE_NAMES Not applicable
N
Found in the dynamic performance view V$TIMEZONE_NAMES
TIMEZONE_ABBR Not applicable
Block Structure
• SET SERVEROUT ON;
• DECLARE
• pe_ratio NUMBER(3,2);
• BEGIN
• DBMS_OUTPUT.PUT_LINE('DK');
• pe_ratio :=12/0;
• EXCEPTION
• WHEN OTHERS THEN
• DBMS_OUTPUT.PUT_LINE('exception');
• END;
The PL/SQL Comments
Function

CREATE [OR REPLACE] FUNCTION function_name

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Example
Output:
Procedure
CREATE OR REPLACE PROCEDURE PROCEDURE_TEST
(
PARAM1 IN NUMBER
, PARAM2 IN NUMBER
, PARAM3 OUT NUMBER
) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('In the Proceduere');
PARAM3 := PARAM1+PARAM2;
DBMS_OUTPUT.PUT_LINE(PARAM3);
END PROCEDURE_TEST;
Example
Output:
PL/SQL - Cursors

• 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.
Types of Cursors

Implicit cursors

Explicit cursors
Implicit Cursors

Created by Oracle whenever

an SQL statement is
executed.
S.No Attribute & Description
%FOUND
Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or
1 more rows or a SELECT INTO statement returned one or more rows.
Otherwise, it returns FALSE.
%NOTFOUND
The logical opposite of %FOUND. It returns TRUE if an INSERT, UPDATE, or
2 DELETE statement affected no rows, or a SELECT INTO statement returned no
rows. Otherwise, it returns FALSE.
%ISOPEN
3 Always returns FALSE for implicit cursors, because Oracle closes the SQL
cursor automatically after executing its associated SQL statement.
%ROWCOUNT
4 Returns the number of rows affected by an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT INTO statement.
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected
');
END IF;
END;
Example

DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
Explicit Cursors

• 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.

• CURSOR cursor_name IS select_statement;

Steps:

Declaring the Fetching the

cursor for cursor for
initializing the retrieving the
memory data

Opening the Closing the

cursor for cursor to release
allocating the the allocated
memory memory
Declaring the Cursor:
CURSOR c_customers IS
SELECT id, name, address FROM customers;

Opening the Cursor:

 Allocates the memory for the cursor and makes it ready for fetching the rows
returned by the SQL statement

OPEN c_customers;

Fetching the Cursor:

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the Cursor:

CLOSE c_customers;
Example DECLARE
c_id customers.id%type;
c_name customers.Name%type;

CURSOR c_customers is
SELECT id, name FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name);
END LOOP;
CLOSE c_customers;
END;
 SQL Exception
set serveroutput on;
DECLARE
result number(10);
BEGIN
result:= 10/0;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error : '||SQLERRM|| ' SQL Error code: '||SQLCODE);
END;

Error : ORA-01476: divisor is equal to zero SQL Error code: -1476

PL/SQL procedure successfully completed.
PL/SQL - Triggers

Triggers are stored programs, which are

automatically executed or fired when
some events occur.
 Response to any of the following events

• A database manipulation (DML) statement (DELETE, INSERT, or

UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP).
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN).
Triggers can be defined on the
table, view, schema, or database
with which the event is
associated.
Benefits of Triggers
• Generating some derived column values automatically
• Enforcing referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions
Creating Triggers
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)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
 Example
CREATE TABLE CUSTOMER( ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID));

INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Dinesh', 32, 'Himachal', 2000.00 );
INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Mohit', 25, 'Delhi', 1500.00 );
INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'Krish', 23, 'Kota', 2000.00 );
INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Ajay', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Ronald', 27, 'Bhopal', 8500.00 );
INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 );
CUSTOMER TABLE

ID NAME AGE ADDRESS SALARY

Himachal
1Dinesh 29 2000
2Mohit 25Delhi 1500
3Krish 23Kota 2000
Mumbai
4Ajay 25 6500
5Ronald 27Bhopal 8500
6Komal 22MP 4500
Create Trigger
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
Triggering a Trigger INSERT INTO CUSTOMER (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Kumar', 22, 'HP', 7500.00 );
Triggering a Trigger UPDATE customer
SET salary = salary + 500
WHERE id = 2;
Thankyou