1 of 39

PL/SQL

Dr.K.R.PRASANNA KUMAR

CSD

2 of 39

PL/SQL

"PL/SQL is Oracle's procedural extension to SQL, combining the power of SQL with procedural programming constructs."
Key Features:

Procedural capabilities (loops, conditionals, etc.)
Variable declaration and manipulation
Error handling
Database interaction
Ability to create stored procedures, functions, packages and triggers.

3 of 39

Benefits of Using PL/SQL

Improved performance (reduced network traffic).
Enhanced security (data access control).
Code reusability (stored procedures and functions).
Data integrity (constraints and triggers).
Modular design.
Visual: Icons representing performance, security, and reusability.

4 of 39

SQL	PL/SQL
SQL is a single query that is used to perform DML and DDL operations.	PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.
It is declarative, that defines what needs to be done, rather than how things need to be done.	PL/SQL is procedural that defines how the things needs to be done.
Execute as a single statement.	Execute as a whole block.
Mainly used to manipulate data.	Mainly used to create an application.
Cannot contain PL/SQL code in it.	It is an extension of SQL, so it can contain SQL inside it.

5 of 39

PL/SQL - Basic Syntax

DECLARE

BEGIN

EXCEPTION

END;

6 of 39

Data Types

Numeric Data Types:

NUMBER: Explain its versatility for storing integers and floating-point numbers.
PLS_INTEGER: Highlight its efficiency for integer calculations.

Character Data Types:

VARCHAR2: Explain its use for variable-length character strings.
CHAR: Explain its use for fixed-length character strings.
CLOB: Explain its use for storing large amounts of character data.

Date and Time Data Types:

DATE: Explain its use for storing date and time values.
TIMESTAMP: Explain it's use for more precise date and time values.

Boolean Data Type:

BOOLEAN: Explain its use for storing logical values (TRUE, FALSE, NULL).

7 of 39

Variables

variable_name [CONSTANT] datatype [NOT NULL] := initial_value;

Examples:

my_number NUMBER;
my_string VARCHAR2(50);
my_date DATE;
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);

8 of 39

Literal Type & Example
Numeric Literals 050 78 -14 0 +32767 6.6667 0.0 -12.0 3.14159 +7800.00 6E5 1.0E-8 3.14159e0 -1E38 -9.5e-3
Character Literals 'A' '%' '9' ' ' 'z' '('
String Literals 'Hello, world!' 'Tutorials Point' '19-NOV-12'
BOOLEAN Literals TRUE, FALSE, and NULL.
Date and Time Literals DATE '1978-12-25'; TIMESTAMP '2012-10-29 12:01:01';

9 of 39

DECLARE

message varchar2(20):= 'CSD';

BEGIN

dbms_output.put_line(message);

END;

/

10 of 39

Create a Folder in D or E Drive in your computer and create a text file with .sql extension inside the folder

Ex: d:\plsql\sample.sql

Now open the SQL command prompt
Command to open the .sql txt file in command prompt

ed D:\plsql\sample.sql

To execute the sql file

@ D:\plsql\sample.sql

To view output on the serveroutput (ontime process)

set serveroutput on;

11 of 39

Operator	Description	Example
+	Adds two operands	A + B will give 15
-	Subtracts second operand from the first	A - B will give 5
*	Multiplies both operands	A * B will give 50
/	Divides numerator by de-numerator	A / B will give 2
**	Exponentiation operator, raises one operand to the power of other	A ** B will give 100000

12 of 39

	Description	Example
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.	(A = B) is not true.
!= <> ~=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(A != B) is true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(A > B) is not true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(A < B) is true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(A >= B) is not true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.	(A <= B) is true

13 of 39

Operator	Description	Example
LIKE	The LIKE operator compares a character, string, or CLOB value to a pattern and returns TRUE if the value matches the pattern and FALSE if it does not.	If 'Zara Ali' like 'Z% A_i' returns a Boolean true, whereas, 'Nuha Ali' like 'Z% A_i' returns a Boolean false.
BETWEEN	The BETWEEN operator tests whether a value lies in a specified range. x BETWEEN a AND b means that x >= a and x <= b.	If x = 10 then, x between 5 and 20 returns true, x between 5 and 10 returns true, but x between 11 and 20 returns false.
IN	The IN operator tests set membership. x IN (set) means that x is equal to any member of set.	If x = 'm' then, x in ('a', 'b', 'c') returns Boolean false but x in ('m', 'n', 'o') returns Boolean true.
IS NULL	The IS NULL operator returns the BOOLEAN value TRUE if its operand is NULL or FALSE if it is not NULL. Comparisons involving NULL values always yield NULL.	If x = 'm', then 'x is null' returns Boolean false.

14 of 39

Operator	Description	Examples
and	Called the logical AND operator. If both the operands are true then condition becomes true.	(A and B) is false.
or	Called the logical OR Operator. If any of the two operands is true then condition becomes true.	(A or B) is true.
not	Called the logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true then Logical NOT operator will make it false.	not (A and B) is true.

15 of 39

IF

IF condition THEN

S;

END IF;

IF - ELSE

IF condition THEN

S1;

ELSE

S2;

END IF;

16 of 39

IF(boolean_expression 1)THEN

S1; -- Executes when the boolean expression 1 is true

ELSIF( boolean_expression 2) THEN

S2; -- Executes when the boolean expression 2 is true

ELSIF( boolean_expression 3) THEN

S3; -- Executes when the boolean expression 3 is true

ELSE

S4; -- executes when the none of the above condition is true

END IF;

17 of 39

CASE

CASE selector

WHEN 'value1' THEN S1;

WHEN 'value2' THEN S2;

WHEN 'value3' THEN S3;

...

ELSE Sn; -- default case

END CASE;

18 of 39

LOOP

Sequence of statements;

END LOOP;

19 of 39

WHILE condition LOOP

sequence_of_statements

END LOOP;

20 of 39

FOR counter IN initial_value .. final_value LOOP

sequence_of_statements;

END LOOP;

21 of 39

Context area

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.

22 of 39

23 of 39

PL/SQL Cursors

A Cursor in PL/SQL is a pointer to a context area that stores the result set of a query
The set of rows the cursor holds is referred to as the active set.

24 of 39

Types of cursors

Implicit Cursor: If the Oracle engine opened a cursor for its internal processing it is known as an Implicit Cursor. It is created “automatically” for the user by Oracle when a query is executed and is simpler to code.

Explicit Cursor: A Cursor can also be opened for processing data through a PL/SQL block, on demand. Such a user-defined cursor is known as an Explicit Cursor.

25 of 39

Implicit cursors

It automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement.
Programmers cannot control the implicit cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement.
For INSERT operations, the cursor holds the data that needs to be inserted.

For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

26 of 39

Implicit Cursors

SQL cursor attribute will be accessed as sql%attribute_name
%FOUND

Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or 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 DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.

27 of 39

Implicit Cursors

%ISOPEN

Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.

%ROWCOUNT

Returns the number of rows affected by an INSERT, UPDATE, or DELETE statement, or returned by a SELECT INTO statement.

28 of 39

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;

/

29 of 39

Explicit Cursors

Explicit cursors are programmer-defined cursors for gaining more control over the context area.

CURSOR cursor_name IS select_statement;

Where,

cursor_name: A suitable name for the cursor.
select_statement: A select query which returns multiple rows

30 of 39

31 of 39

Four steps in using an Explicit Cursor

DECLARE the cursor in the Declaration section.
OPEN the cursor in the Execution Section.
FETCH the data from the cursor into PL/SQL variables or records in the Execution Section.
CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

32 of 39

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement. For example −

CURSOR c_customers IS

SELECT id, name, address FROM customers;

33 of 39

Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows returned by the SQL statement into it. For example, we will open the above defined cursor as follows −

OPEN c_customers;

Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from the above-opened cursor as follows −

FETCH c_customers INTO c_id, c_name, c_addr;

Closing the cursor means releasing the allocated memory. For example, we will close the above-opened cursor as follows −

CLOSE c_customers;

34 of 39

DECLARE

variables;

records;

CURSOR cursor_name IS select_statement;

BEGIN

OPEN cursor_name;

LOOP

FETCH cursor_name INTO variables OR records;

EXIT WHEN cursor_name%NOTFOUND;

process the records;

END LOOP;

CLOSE cursor_name;

END;

35 of 39

DECLARE

c_id customers.id%type;

c_name customers.name%type;

c_addr customers.address%type;

CURSOR c_customers is

SELECT id, name, address FROM customers;

BEGIN

OPEN c_customers;

LOOP

FETCH c_customers into c_id, c_name, c_addr;

EXIT WHEN c_customers%notfound;

dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);

END LOOP;

CLOSE c_customers;

END;

/

36 of 39

SET SERVEROUTPUT ON;

DECLARE

CURSOR d_cursor IS

SELECT * from Dept;

D_Record Dept % ROWTYPE;

BEGIN

OPEN d_cursor;

LOOP

FETCH d_cursor INTO D_Record;

EXIT WHEN d_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(D_Record.Dname || ' ' || D_Record.Loc);

END LOOP;

CLOSE d_cursor;

END;

/

37 of 39

Triggers

Triggers are stored programs, which are automatically executed or fired when some events occur. Triggers are, in fact, written to be executed in 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.

38 of 39

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

39 of 39

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;