PQ/SQL examples

This blog is a Java Programmer's answer for that moment when you're told to design a crucial piece of corporate PL/SQL that will hold the world together and save the empire. In this situation, as an ace developer your first response should be: "How hard can it be..."

So, introducing the Captain Debug PL/SQL quick reference guide, I'm going to demonstrate...

• A simple PL/SQL Block
• A simple PL/SQL Block using a Cursor and while loop
• A simple PL/SQL Block using a Cursor and FOR loop
• A PL/SQL Block using SQL attributes.
• A simple procedure.
• A procedure with input and output parameters
• EXCEPTIONS
  
  • Named EXCEPTIONs
  • Unnamed EXCEPTIONs
• Functions

A simple PL/SQL Block

DECLARE
  messages MY_MESSAGES%ROWTYPE;
BEGIN

  select pk, created_on into messages.pk, messages.created_on from MY_MESSAGES
      where pk=10007;
END;

A Simple PL/SQL Block using a Cursor and WHILE loop

DECLARE
  CURSOR msg_cur IS
  select pk, created_on from MY_MESSAGES;

  msg_rec msg_cur%ROWTYPE;

BEGIN

  IF NOT msg_cur%ISOPEN THEN
    OPEN msg_cur;
  END IF;

  FETCH msg_cur INTO msg_rec;

  WHILE msg_cur%FOUND
  LOOP
    dbms_output.put_line(msg_rec.pk || ' - ' || msg_rec.created_on || '-- EOL');
    FETCH msg_cur INTO msg_rec;
  END LOOP;

END;

A simple PL/SQL Block using a Cursor and FOR loop

DECLARE
  CURSOR msg_cur IS
  select pk, created_on from MY_MESSAGES;

  msg_rec msg_cur%ROWTYPE;

BEGIN

  FOR msg_rec IN msg_cur
  LOOP
    dbms_output.put_line(msg_rec.pk || ' - ' || msg_rec.created_on || '-- EOL');
  END LOOP;

END;

A PL/SQL Block using SQL attributes.

DECLARE

 var_rows number(5);

BEGIN

  dbms_output.enable;

  UPDATE MY_MESSAGES
  SET LAST_UPDATED = systimestamp;

  IF SQL%NOTFOUND THEN
    dbms_output.put_line('None of the messages were updated');
  ELSIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
    dbms_output.put_line('Number of rows updates: ' || var_rows);
  END IF;
END;

A simple procedure.

CREATE OR REPLACE PROCEDURE msg_update
IS

 var_rows number(5);

BEGIN

  dbms_output.enable;

  UPDATE MY_MESSAGES
  SET LAST_UPDATED = systimestamp;

  IF SQL%NOTFOUND THEN
    dbms_output.put_line('None of the messages were updated');
  ELSEIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
    dbms_output.put_line('Number of rows updates: ' || var_rows);
  END IF;
END;

A procedure with input and output parameters

CREATE OR REPLACE PROCEDURE update_msg_date(id IN NUMBER, lastUpdated OUT DATE)
IS

BEGIN

  select LAST_UPDATED into lastUpdated FROM MY_MESSAGES
  where pk=id;

END;

The above procedure can be call using:

DECLARE

  lastUpdated DATE;

  CURSOR msg_cur is select pk from MY_MESSAGES;

  msg_rec msg_cur%ROWTYPE;

BEGIN

  for msg_rec in msg_cur
  LOOP
    update_msg_date(msg_rec.pk,lastUpdated);

    dbms_output.put_line('PK ' || msg_rec.pk || ' was last updated on: '|| lastUpdated);
  END LOOP;

END;

which will get all the PKs from the MY_MESSAGES and callthe procedure with each one.

Contrived example of an IN OUT parameter.

CREATE OR REPLACE PROCEDURE getValidUntil(theDate IN OUT my_dates_range_table.VALID_UNTIL%TYPE)
IS

BEGIN

  SELECT VALID_UNTIL INTO theDate FROM my_dates_range_table
         where VALID_FROM = theDate;

EXCEPTION
  WHEN no_data_found THEN

  theDate := sysdate;

END;

The above procedure can be called using:

DECLARE

  validDate DATE;

  msg VARCHAR2(2000);

  CURSOR my_thingy_cur is select valid_from from my_dates_range_table;

  my_thingy_rec my_thingy_cur%ROWTYPE;

BEGIN

  for my_thingy_rec in my_thingy_cur
  LOOP

    validDate := my_thingy_rec.valid_from;
    getvaliduntil(validDate);

    msg := 'Valid_From ' || my_thingy_rec.valid_from || 'valid_until: ' || validDate;

  END  LOOP;

END;

EXCEPTION

Named EXCEPTIONs

Examples (not an exhaustive list):

• CURSOR_ALREADY_OPEN
• NO_DATA_FOUND
• TOO_MANY_ROWS
• ZERO_DIVIDE

Each of these and other match up to an Oracle Error code (and SQL error code).

DECLARE
  messages MY_MESSAGES%ROWTYPE;
BEGIN

  -- Simple SQL that'll throw because of no rows found
  select pk, created_on into messages.pk, messages.created_on from MY_MESSAGES
      where pk=1;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
       dbms_output.put_line('No data found');

END;

Unnamed EXCEPTIONs

DECLARE

  child_rec_EXCEPTION EXCEPTION;
  PRAGMA
  EXCEPTION_INIT(child_rec_EXCEPTION,-2292);

BEGIN

  delete from MY_MESSAGES where pk=1;


EXCEPTION
  -- This is the unnamed EXCEPTION
  WHEN child_rec_EXCEPTION THEN
    dbms_output.put_line('Child records are present...');

  -- This is a standard named EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('No  rows found...');

END;

There are also user defined EXCEPTIONs.... but are they worth doing as they're connected to business logic, which should be put into the business layer of the application.

In SQL*PLUS you can execute a procedure using

execute msg_update;

or

exec msg_update;

This is how you create a database link

create database link test_link connect to mySchema identified by password
using 'mySID';

Then you can test the link using:

select sysdate from dual@test_link;

Functions:

This won't work because you can't do updates in a function

create or replace FUNCTION msg_update_counter
RETURN number

IS

 var_rows number(5);

BEGIN

  UPDATE MY_MESSAGES
  SET LAST_UPDATED = systimestamp;

  IF SQL%NOTFOUND THEN
    var_rows := 0;
  ELSIF SQL%FOUND THEN
    var_rows := SQL%ROWCOUNT;
  END IF;

  RETURN var_rows;
END;

This does work because it only does a select

create or replace FUNCTION msg_update_counter
RETURN DATE

IS

 lastUpdate DATE;

BEGIN

  select LAST_UPDATED into lastUpdate FROM MY_MESSAGES
      where pk='10007';

  return lastUpdate;
END;

To execute a function:

1. Assign to a variable:
   

   count := msg_update_counter;
   

2. Use as part of an SQL select statement:
   

   select msg_update_counter from dual;
   

3. Within a PL/SQL statement:
   

   dbms_output.put_line(msg_update_counter);
   

TABLES TO ACCESS ARE:

CREATE TABLE MY_MESSAGES (
    PK NUMBER(15,0) NOT NULL ENABLE,
    CREATED_ON DATE,
    LAST_UPDATED DATE,
    DOCUMENT_SIZE NUMBER(15,0),
    XML_DOCUMENT BLOB NOT NULL ENABLE,
    PRIMARY KEY ("PK"));

  CREATE TABLE UK_DOMAIN_CODES (
    CATEGORY VARCHAR2(80 CHAR) NOT NULL ENABLE,
    CODE VARCHAR2(12 CHAR) NOT NULL ENABLE,
    DESCRIPTION VARCHAR2(3999 CHAR),
    VALID_FROM DATE,
    VALID_UNTIL DATE,
    FLAG VARCHAR2(1 CHAR));

Defintions above have been simplified.. constraints, indexes and primary keys have been removed fro simplicity.