• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar

Coder Tutorial

  • Home
  • HTML
  • CSS
  • PHP
  • SQL
  • MySQL
  • JS
  • PL/SQL
  • Python
  • Java
  • Oracle

PL/SQL

PL/SQL Cursors

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;

Filed Under: PL/SQL

PL/SQL Stored Procedures

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;

Filed Under: PL/SQL

PL/SQL Functions

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;

Filed Under: PL/SQL

PL/SQL Packages

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; 

Filed Under: PL/SQL

PL/SQL Exception Handling

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;

Filed Under: PL/SQL

  • « Go to Previous Page
  • Go to page 1
  • Go to page 2
  • Go to page 3
  • Go to page 4
  • Interim pages omitted …
  • Go to page 27
  • Go to Next Page »

Primary Sidebar

Tutorials

  • HTML Tutorial
  • CSS Tutorial
  • PHP Tutorial
  • SQL Tutorial
  • MySQL Tutorial
  • JavaScript Tutorial
  • Python Tutorial
  • PL/SQL Tutorial
  • Java Tutorial
  • SQLPlus Tutorial
  • Oracle Tutorial
  • PostgreSQL Tutorial
  • Ruby Tutorial

Secondary Sidebar

Recent Posts

  • PostgreSQL ERROR: cannot begin/end transactions in PL/pgSQL
  • PostgreSQL Column must appear in the GROUP BY clause
  • PostgreSQL Column specified more than once
  • PostgreSQL Create database, Alter database examples
  • PostgreSQL Create schema syntax, Alter schema
  • PostgreSQL Create database user, alter and drop username
  • PostgreSQL Alter table name. Modify column name
  • PostgreSQL Alter Function Examples
  • PostgreSQL Drop function syntax and example
  • PostgreSQL Alter Trigger, Disable trigger, enable
  • PostgreSQL Drop Trigger, syntax and example
  • PostgreSQL Create table type, alter and drop type
  • PostgreSQL Loop – End Loop
  • PostgreSQL Case – When – Then
  • PostgreSQL IF – Elsif – Else syntax
  • PostgreSQL IF – Then – Else syntax
  • PostgreSQL IF – Then syntax
  • PostgreSQL Control Structures syntax: if then else case
  • PostgreSQL Alter table add primary key to existing table
  • PostgreSQL Alter table add foreign key constraint to a table
  • PostgreSQL Alter table add unique key constraint to a table
  • PostgreSQL Alter table add column to existing table
  • PostgreSQL Alter table add multiple columns to existing table
  • PostgreSQL Alter table drop column from existing table
  • PostgreSQL Alter table drop multiple columns from table
  • PostgreSQL Alter table rename column from existing table
  • PostgreSQL Alter table rename table name
  • PostgreSQL Alter table add check constraint to a table
  • PostgreSQL Alter table drop check constraint from a table
  • PostgreSQL tutorial. Error messages help: cause and solution.

Copyright  2018 - 2021 Coder Tutorial

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled

Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.

Non-necessary

Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.

SAVE & ACCEPT