Pages

Sunday, February 20, 2011

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;