Database Tools - PL/SQL programming

Built-in Functions

•Character Functions

–UPPER(string)  converts text to uppercase

–LOWER  converts text to lowercase

–INITCAP  capitalises first letter of each word

–LENGTH  string length including pads

–LTRIM  trims characters from left

–RTRIM  trims characters from right

–RPAD  pads a string on the right

•Number Functions
            –Trigonometric functions
            –Mathematical functions

•Conversion Functions
            –TO_CHAR converts dates, numbers to varchar2
            –TO_DATE converts text to dates (correct format)
            –TO_NUMBER converts text to numbers

•Number Functions

            –Trigonometric functions
            –Mathematical functions

•Conversion Functions

–TO_CHAR  converts dates, numbers to varchar2

–TO_DATE  converts text to dates (correct format)

–TO_NUMBER converts text to numbers

•Grouping Functions (cf SQL)
            –AVG
            –COUNT
            –SUM

•Miscellaneous
            –object functions
            –SQLCODE
            –SQLERRM
            –UID
            –USER

STORED PROCEDURE

Syntax

CREATE [OR REPLACE] PROCEDURE procedure_name

   (arg1 [IN] datatype, .. arg2 OUT datatype, arg3 INOUT datatype)

AS | IS

  {variables;}

BEGIN

  {statements;}

END;

FUNCTION

Syntax

CREATE [OR REPLACE] FUNCTION function_name

 (arg1 [IN] datatype, .. arg2 OUT datatype,  .. arg4  INOUT datatype )

  RETURN datatype

AS | IS

  {variables;}

BEGIN

  {statements;}

RETURN returnstatement;

END;        

PACKAGE

Encapsulated collection of schema objects (procedures, functions, cursors, variables, constants and exceptions)

Advantages

           •Better organisation of the application (each package has clear, well defined interfaces )
           •Privileges are granted more easily
           •Variables and cursors persist for the session
           •Allow procedure and function overloading
           •Reduce I/O by loading schema objects simultaneously
           •Code re-use

Syntax

  CREATE [OR REPLACE] PACKAGE pack_name AS

  {FUNCTION f_name(arg1…);}

  {PROCEDURE p_name(arg1…);}

  {variables;}

  {cursors;}

  END pack_name;

TRIGGER

Trigger Types and Events
 15 triggers

–4 basic types

  Before Statement

  Before Row

  After Row

  After Statement

–for each of 3 statements

  Insert, Update, Delete

–instead of triggers (Views)

  Insert, Update, Delete

Syntax

CREATE [OR REPLACE] TRIGGER name

    { BEFORE | AFTER }

  {INSERT | UPDATE | DELETE [OF column_name]}

  ON table_name

  [FOR EACH ROW [WHEN [(condition)]]]

       DECLARE

  {declarations;}

       BEGIN

  {code statements;}

       END;

Limitations on Views
–limited updatability
           •distinct not specified
           •every element is a column (not an aggregate function)
           •only the many side of a 1:many
–inserts only into one table
           •must contain all not null columns
           •no violation of integrity constraints

Instead of Triggers

CREATE [OR REPLACE] TRIGGER name

    INSTEAD OF {INSERT | UPDATE | DELETE }  ON view_name

  FOR EACH ROW

         BEGIN

  {statements;}

         END;

eg:-

CREATE OR REPLACE TRIGGER credit

AFTER INSERT ON ord

FOR EACH ROW

DECLARE

  balance number;

BEGIN

  SELECT credit_balance INTO balance FROM customer

  WHERE customer_id = :NEW.customer_id;

  balance := balance - :NEW.total;

  UPDATE customer

  SET credit_balance = balance

  WHERE customer_id = :NEW.customer_id;

END;

CURSOR

eg:-

DECLARE

CURSOR c_emp IS

SELECT * FROM emp

WHERE department = ‘LONDON’;

BEGIN

   FOR emp_record IN c_emp LOOP

      IF emp_record.salary_type = ‘HOURLY’ THEN

  emp_record.salary_rate := emp_record.salary_rate * 1.1;

      ELSEIF emp_record.salary_type = ‘MONTHLY’ THEN

  emp_record.salary_rate := emp_record.salary_rate + 90;

      END IF;

   END LOOP;

   COMMIT;

END;