Category Archives: PL/SQL

Oracle PL/SQL Tutorial

PL/SQL

PL/SQL Tutorial Introduction PL/SQL (Procedural Language / Structured Query Language) is the procedural extension of SQL language. PL/SQL is a programming language that provides accessing data from a relational database-oriented objects. PL/SQL is a language with block structure. PL/SQL Advantages Supports the basic SQL commands Defining and managing blocks of instructions Management of variables, constants and cursors Allow… Read More »

PL/SQL Blocks

PL/SQL Blocks The structure of a PL/SQL block PL/SQL is a language structured on the block. A PL/SQL block consists of 3 parts: declarative (optional), executable (required) and handling exceptions (optional). PL/SQL is a language focused on blocks. These blocks are composed of procedures, functions and anonymous blocks. A PL/SQL block is composed of three parts: Declaration –… Read More »

PL/SQL Variables

PL/SQL Variables Variables can be any SQL data type such as CHAR, DATE, NUMBER or PL/SQL data type such as BOOLEAN or PLS_INTEGER. Declare data type for variables in PL/SQL You can use the special words %ROWTYPE or %TYPE to declare variables that keeps the columns of tables or records of the tables. %TYPE attribute provides the ability… Read More »

PL/SQL Control structures

PL/SQL Control structures IF IF-THEN, IF-THEN-ELSE and IF-THEN-ELSIF. Example: DECLARE x NUMBER:=5; y NUMBER:=3; z NUMBER:=0; BEGIN IF x > y THEN z:=x; DBMS_OUTPUT.PUT_LINE(z); ELSIF x < y THEN z:=y; DBMS_OUTPUT.PUT_LINE(z); ELSE z:=-1; DBMS_OUTPUT.PUT_LINE(z); END IF; END; Example: DECLARE v_dept_id NUMBER; v_id EMPLOYEES.id%TYPE:=3; v_sal_raise NUMBER:=0; BEGIN SELECT dept_id INTO v_dept_id from employees WHERE id = v_id; IF v_dept_id… Read More »

PL/SQL Collections and Records

PL/SQL Collections and Records Collections and recordings are compound data types containing internal elements like array, record or table. Collections Collections are data types that allow simultaneous processing of multiple variables of the same type. Each element has a unique index, which determines its position in the collection. Types of collections: Nested Tables, Varrays, Associative Arrays (Index-By Tables)… Read More »

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… Read More »

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.… Read More »

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)… Read More »

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(… Read More »

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… Read More »