PL/SQL Cursors

PL/SQL uses implicit and explicit cursors.
PL/SQL declare a implicit cursor for each operation to data manipulation in SQL.
If you want precise control over the queries, you can declare a explicit cursor;
You can define a explicit cursor for the queries which returns more than one row.

1. Implicit Cursors

The implicit cursors are managed automatically by the PL/SQL.

2. Explicit Cursors

When you need precise control over the outcome of the queries, must be declared a explicit cursor in PL/SQL.
The commands for a explicit cursor: OPEN, FETCH and CLOSE. First time the cursor must be opened through the OPEN command. Then you can run FETCH repeated until all records have been read or you can use BULK COLLECT clause to read all recording only once. For close a cursor uses the CLOSE command.

The attributes of the cursors

The attributes of the implicit cursors can return information about DML and DDL execution commands such as INSERT, UPDATE, DELETE, SELECT INTO, COMMIT or ROLLBACK.
The cursor attributes are: %FOUND, %ISOPEN, %NOTFOUND and %ROWCOUNT.

SQL%FOUND

Until SQL data manipulation is executed, the attribute %FOUND is NULL.

Example:

CREATE TABLE stu_temp AS SELECT * FROM students;
DECLARE
	v_id NUMBER:=3;
BEGIN
	DELETE FROM stu_temp WHERE student_id = v_id;
	IF SQL%FOUND THEN 
	INSERT INTO stu_temp(student_id, first_name, last_name, gender) 
	SELECT s.student_id, s.first_name, s.last_name, s.gender 
	FROM students s
	WHERE s.student_id=v_id;
	END IF;
END;

SQL%ISOPEN

Oracle database closes the cursor automatically after execution of commands. You must check if the cursor is open.

SQL%NOTFOUND

%NOTFOUND is the opposite attribute of %FOUND.

SQL%ROWCOUNT

%ROWCOUNT returns the number of records affected by one of the commands like INSERT, UPDATE or DELETE.

Example: Using SQL%ROWCOUNT

CREATE TABLE stu_temp AS SELECT * FROM students;
DECLARE
	v_id NUMBER:=3;
BEGIN
	DELETE FROM stu_temp WHERE student_id = v_id;
	DBMS_OUTPUT.PUT_LINE('Number of students deleted: '||SQL%ROWCOUNT); 
END;

%ROWTYPE

DECLARE
	stu_row students%ROWTYPE; 
BEGIN
	SELECT * INTO stu_row 
	FROM students WHERE student_id=3;
	DBMS_OUTPUT.PUT_LINE('First name: '||stu_row.first_name);
	DBMS_OUTPUT.PUT_LINE('Last name: '||stu_row.last_name);
END;

PL/SQL Stored Procedures

Stored procedures are called blocks that allow grouping and organization of SQL and PL/SQL commands.

Stored Procedure

CREATE OR REPLACE PROCEDURE proc_name
	(paramater_name datatype, ...)
IS
BEGIN
 -- pl/sql statement
END;

Parameters are used to transfer data between values and call the procedure.
Parameters can have one of 3 ways: IN, OUT and IN OUT.

Example Stored Procedure:

CREATE OR REPLACE PROCEDURE upd_address
	(p_id    IN NUMBER,
	 p_address   IN VARCHAR2)
IS
BEGIN
	UPDATE students 
	SET address= p_address
	WHERE student_id = p_id;
END;

PL/SQL Functions

A function is a PL/SQL block with names that accept parameters, can be called in a select and can return a value.
Functions and procedures are similar structures.
A function may return a single value, while a procedure may return zero or more values through parameters.

Create Function Example:

CREATE OR REPLACE FUNCTION Stu_Name(p_id IN NUMBER)
RETURN VARCHAR2
IS
	stu_row students%ROWTYPE;
	v_full_name VARCHAR2(255):='';  
BEGIN
	SELECT * INTO stu_row 
	FROM students WHERE student_id=3;
	v_full_name:=stu_row.first_name||' '||stu_row.last_name;
	RETURN v_full_name;
END;

PL/SQL Packages

The package consists of grouped objects as procedures, functions, variables, exceptions, cursors.
Each package consists of two parts:
– Specification of the package – Here we can declare public types, variables, constants and exceptions.
– The body of the package defines its own code.

Exemple of PL/SQL Packages:

CREATE OR REPLACE PACKAGE users AS 
	FUNCTION add_user(	p_user_name VARCHAR2, p_user_email VARCHAR2) 
	RETURN VARCHAR2;
END users; 

CREATE OR REPLACE PACKAGE BODY users AS 
	global_exception exception;
	FUNCTION add_user(	p_user_name VARCHAR2, p_user_email VARCHAR2) 
	RETURN VARCHAR2 IS 
		v_output varchar2(4000):='Inserted';
	BEGIN 
		IF p_user_name IS NULL THEN
			v_output:='p_user_name IS NULL';
			RAISE global_exception;
		END IF;		
		INSERT INTO users(user_name, user_email) 
		VALUES (p_user_name, p_user_email); 
		RETURN v_output;
	EXCEPTION 
		WHEN global_exception THEN	
			RETURN v_output;			
	END; 		
END users; 

PL/SQL Exception Handling

Exception Handling in PL/SQL refers to the concept of exception.
The exception is an error or an warning message generated by the server or application.
Exceptions may be defined, enabled, treated at the level of each block in the program.

In PL/SQL there are two types of exceptions:
– internal exceptions that occur from the server
– external user-defined exceptions which are declared in declarative section

EXCEPTION
  WHEN No_Data_Found THEN
    statement1;
  WHEN Too_Many_Rows THEN
    statement2;
  WHEN Others THEN
    statement3;

For the processing of internal exceptions we can use WHEN OTHERS exceptions or pragma EXCEPTION_INIT.

DECLARE
exception_name EXCEPTION;
PRAGMA EXCEPTION_INIT (exception_name,-50);
BEGIN
--plsql code;
EXCEPTION
WHEN exception_name THEN
 --error processing
END;

PL/SQL RAISE_APPLICATION_ERROR

CREATE PROCEDURE add_user(p_user_name VARCHAR2, p_user_email VARCHAR2)
AS
BEGIN
		IF p_user_name IS NULL THEN
		RAISE_APPLICATION_ERROR (-20100, ' p_user_name IS NULL');
		ELSE
		INSERT INTO users(user_name, user_email) 
		VALUES (p_user_name, p_user_email); 
		END IF;
END add_user;