What is PL/SQL?

SQL

—Structured language to access database

—ANSI Standard

—Declarative

○Specifies what to access but not how to

   - SELECT id, first_name, family_name FROM authors

PL/SQL

—Developed by Oracle as a procedural extension to SQL

—Declare variables, IF..ELSE, WHILE & FOR loops, functions, procedures and other features found in a programming language.

 Server Side Programming 

PL/SQL executes inside DBMS

—DBMS maintains Relational data

—Also stores and executes Procedural code!

 Advantages

—Results from one query can be used as a basis for the next query without having to pull data from DBMS to client side for processing!

DBMS maintains relational data

Client side programs implement procedural logic

Is there a problem?

Performance impact due increased network traffic

—Cumulative effect if many clients run at the same time

Code re-use may not be possible

—Many applications may incorporate processing that are quite similar but sharing may not be easy!

Block structured

—Basic program unit is a block

—Contains variables, code and error handler

A BLOCK is contained within BEGIN and END statements with executable commands in between

—Must contain some commands, even if they do nothing!

PL/SQL programs must at least contain 1 block

Blocks can be nested (block within another block)

EXCEPTIONS

—“unexpected errors” that occur during execution

—occurs at run-time not at compile time!

EXCEPTION HANDLER

—Code that executes when EXCEPTION occurs

—Makes the code more robust

Oracle Server has many pre-defined errors

—no_data_found, value_error, too_many_rows, others.

  PL/SQL - Anonymous Block

DECLARE

  today  date;

BEGIN

    SELECT sysdate INTO today FROM dual;

   DBMS_OUTPUT.PUT_LINE (‘Today -’ || today);

END;

•What exactly happens in this code?

•What is DUAL? (revision test !)

PL/SQL - Named Block

Has a name and stored in Oracle Server

Contains Header section

—name,

—key word - a function, procedure or trigger

—type of value it returns in case of function

At the time of creation, the code within the named block is NOT executed but compiled and stored in Oracle Server

DECLARE

family_name  VARCHAR2 (20);

Age         NUMBER(3);

Assignment

   family_name := ‘Anderson’;

   Age := 21;

PL/SQL – %Type

%TYPE

—To map a variable directly to the same datatype as the table column

DECLARE

author_id  AUTHORS.ID%TYPE;

PL/SQL variable ‘author_id’ is of same datatype

  which is used to define column name ‘id’ of table AUTHORS.

If column type changes PL/SQL Code would still work!

   {Example: VARCHAR2(20) to VARCHAR(30)}

PL/SQL - Scope Rules

Variables, procedures and functions can be referenced by the code executing inside the block in which they are defined

Understanding of scope of variables, functions is especially important in the context of nested blocks!

PL/SQL Scope Example

DECLARE

  father_name VARCHAR2(20):='Patrick';

  date_of_birth DATE:='20-Apr-1972';

  BEGIN

  DECLARE

    child_name VARCHAR2(20):='Mike';

    date_of_birth DATE:='12-Dec-2002';

  BEGIN

    DBMS_OUTPUT.PUT_LINE('Father''s Name: '||father_name);

    DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);

    DBMS_OUTPUT.PUT_LINE('Child''s Name: '||child_name);

  END;

  DBMS_OUTPUT.PUT_LINE('Date of Birth: '||date_of_birth);

END;

/

PL/SQL Operators

Expressions consist of PL/SQL operators and operands

—Arithmetic Operators

○**, *, /, +, -

—Comparison Operators

○=, <>, !=, <, >, <=, >=, LIKE, BETWEEN, IN,  IS NULL

—Logical Operators

○AND, OR, NOT

String Operator – Concatenation using ‘||’

NULL means “UNKNOWN” value

Use IS NULL or IS NOT NULL to check for NULL value

NULL value comparison with ‘= NULL’ or ‘!= NULL’ may produce unpredictable results!

Use NVL function when appropriate

NVL (, )

DECLARE

     name      VARCHAR2(20);

     surname  VARCHAR2(20);

BEGIN

     SELECT first_name, family_name

     INTO name, surname 

     FROM  AUTHORS

     WHERE id = 1 ;

     DBMS_OUTPUT.PUT_LINE

           (‘Row selected is : ‘ || name || ‘-’ || surname);

END;

You can write INSERT, UPDATE, and DELETE statements directly in PL/SQL programs, without any special notation:

%ROWCOUNT Attribute: How Many Rows Affected So Far?

Example

SET SERVEROUTPUT ON;

BEGIN

   UPDATE employees SET salary = salary * 1.05 WHERE ...;

    dbms_output.put_line('Updated ' || SQL%ROWCOUNT || ' salaries.');

END;

/

  

Conditional execution

—IF-THEN, IF-THEN-ELSE, IF-THEN-ELSIF

—CASE

Repeated execution until some condition

— LOOP-END LOOP, FOR-LOOP-END LOOP

—WHILE-LOOP-END LOOP

—EXIT WHEN

Jump to code section

—GOTO 

IF    

THEN

   statement1;

   statement2;

   …..

END  IF;

can be evaluated to TRUE, FALSE or NULL – statement1, statement2 etc., are executed only if it evaluates to TRUE

IF sales > quota THEN compute_bonus(empid);

   UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id; 

END IF;

IF    

THEN

   statement1;

   statement2;

   …..

ELSE

   statement3;

   statement4;

   …..

END  IF;

IF  1>  

THEN

   statement1;

   …..

ELSIF

   statement3;

   ……

ELSE

  …..

END  IF;

  

Example

BEGIN ...

   IF sales > 50000 THEN bonus := 1500;

   ELSIF sales > 35000 THEN bonus := 500;

   ELSE bonus := 100; END IF;

       INSERT INTO payroll VALUES (emp_id,

           bonus, ...);

END;