PL/SQL

Syllabus

PL/SQL: Introduction to PL/SQL, Procedures and Functions, Triggers. (7.2)

Introduction to PL/SQL

• Procedural Language extension for SQL

• PL/SQL is a combination of SQL along with the procedural features of programming languages.

• It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

• Portable within Oracle data bases

• Callable from any client

Need of PL/SQL

• Acts as host language for stored procedures and triggers.

• Provides the ability to add middle tier business logic to client/server applications.

• Provides Portability of code from one environment to another

• Improves performance of multi-query transactions.

• Provides error handling

Features of PL/SQL

• PL/SQL is tightly integrated with SQL.

• It offers extensive error checking.

• It offers numerous data types.

• It offers a variety of programming structures.

• It supports structured programming through functions and procedures.

• It supports object-oriented programming.

• It supports developing web applications and server pages.

Advantages of PL/SQL

• SQL is the standard database language and PL/SQL is strongly integrated with SQL.

• PL/SQL supports both static and dynamic SQL.

• PL/SQL allows sending an entire block of statements to the database at one time.

• PL/SQL gives high productivity to programmers.

• PL/SQL saves time on design and debugging.

• Applications written in PL/SQL are fully portable.

• PL/SQL provides high security level.

• PL/SQL provides access to predefined SQL packages.

• PL/SQL provides support for Object-Oriented Programming.

• PL/SQL provides support for Developing Web Applications and Server Pages.

Structure of PL/SQL

PL/SQL is Block Structured: A block is the basic unit from which all PL/SQL programs are built.

Sections of a block

• Header Section
• Declaration Section (optional)
• Executable Section (required)
• Exception Section (optional)

DECLARE
   -- variable declarations
BEGIN
   -- SQL/PLSQL statements
EXCEPTION
   -- error handling
END;

Example:

DECLARE
  message varchar2(20):= 'Hello, World!';
BEGIN
  dbms_output.put_line(message);
END;
/

Data Types for specific columns

Variable_name Table_name.Column_name%type;

Declaring a variable

variable_name datatype [NOT NULL := value];

Assigning values

Direct assignment

variable_name := value;

Assignment from database

SELECT column_name INTO variable_name 
	FROM table_name [WHERE condition];

Example:

DECLARE
  var_salary number(10,2);
  var_ssn char(9) := '888665555';
BEGIN
  SELECT salary INTO var_salary FROM employee WHERE ssn=var_ssn;
  dbms_output.put_line(var_salary);
  dbms_output.put_line(var_ssn);
  dbms_output.put_line('The employee ' || var_ssn || ' has salary ' || var_salary);
END;
/

PL/SQL Constants

constant_name CONSTANT datatype := VALUE;

Valid:

DECLARE salary_increase CONSTANT number(3) := 10;

Invalid:

DECLARE salary_increase CONSTANT number(3);
BEGIN
  salary_increase := 100; -- not allowed
END;
/

PL/SQL Scalar Data Types

DataType	Description
Numeric	Numeric values
Character	Alphanumeric values
Boolean	Logical values (TRUE, FALSE, NULL)
Datetime	Date and time values

Example:

DECLARE
  num1 INTEGER;
  num2 REAL;
  num3 DOUBLE PRECISION;
BEGIN
  null;
END;
/

PL/SQL Boolean Data Types

BOOLEAN values: TRUE, FALSE, NULL

NULLs in PL/SQL

NULL is not the same as an empty string. Cannot be compared with anything, including itself

PL/SQL Control Structure

• Conditional Controls
• Iterative/Loop Controls
• Exception/Error Controls

Conditional Controls

IF statements

IF condition THEN
  -- statements
END IF;

IF-ELSE statements

IF condition THEN
  -- true block
ELSE
  -- false block
END IF;

CASE statements

CASE selector
  WHEN 'value1' THEN S1;
  WHEN 'value2' THEN S2;
  ELSE Sn;
END CASE;

Example:

DECLARE grade char(1);
BEGIN
  grade := :g;
  CASE grade
    WHEN 'A' THEN dbms_output.put_line('Excellent');
    WHEN 'B' THEN dbms_output.put_line('Very good');
    WHEN 'C' THEN dbms_output.put_line('Well done');
    WHEN 'D' THEN dbms_output.put_line('You passed');
    WHEN 'F' THEN dbms_output.put_line('Better try again');
    ELSE dbms_output.put_line('No such grade');
  END CASE;
END;
/

Loop Structures

Simple loop

LOOP
  -- statements
  EXIT;
END LOOP;

WHILE loop

WHILE condition LOOP
  -- statements
END LOOP;

FOR loop

FOR i IN [REVERSE] lower..upper LOOP
  -- statements
END LOOP;