To the point guide to PL/SQL

PL/SQL allows defining an object type where object type method and it attribute into a single programming construct. An object type must be created before it can be used in a program.This construct of object type allows user to define datatypes of their own and is reusable for use in programs, table of PL/SQL.

To create objects we use CREATE [OR REPLACE] TYPE statement:

Example:

CREATE OR REPLACE TYPE Stud_Details AS OBJECT
(Stud_name varchar2(20),
 stream varchar2(20),
 college_name varchar2(30),
 );
/

Result:

Type created.

Instantiating an Object:

To use an object user need to create instances of the object. An object type provides a blueprint for the object.to access the object methods and attribute by using the name of instance and the access (.) operator.

Example:

DECLARE
   Student details;
BEGIN
   student := details('Neema', 'Computer Science', 'SJCE');
   dbms_output.put_line('Student Name: '|| student.Stud_name);
   dbms_output.put_line('Stream: '|| student.stream);
   dbms_output.put_line('College: '|| student.college_name);
   END;
/

Result:

Student Name:  Neema
Stream: Computer Science
College: SJCE
PL/SQL procedure successfully completed.

Member Methods:

A member method is a function/procedure is for manipulation of object attributes. To invokes method only by objects that have been instantiated. The body of object defines the code for the methods member. The body of object is created by using the statement CREATE TYPE BODY.

The constructors are functions which return a value of a new object. The name of object type and constructor are same and every object has a system defined method of constructor.

Example:

student := details('Neema', 'Computer Science', 'SJCE');

To compare objects the comparison methods are used. The ways to compare objects are:

• The map function maps each objects into scalar data types.a map function which will not accepts any parameters and returns a datatype of scalar like NUMBER/VARCHAR2, DATE for which Oracle already knows a collating sequence.
• The order methods implement internal logic for comparing two objects.an order function accepts two parameters. Same type object and self and it returns an value of integer.

Map Function:

CREATE TYPE Account_t AS OBJECT (
   Account REF Acc_t,
   create_date DATE,
   with_whom REF manager_t,
MAP MEMBER FUNCTION compare RETURN DATE

);

CREATE TYPE BODY Account_t
AS
   MAP MEMBER FUNCTION compare RETURN DATE
   IS
   BEGIN
      RETURN create_date;
   END compare;
END;

Result:

Type body created.

Using Order method:

Example:

CREATE OR REPLACE TYPE typ_location AS OBJECT (
  home_no  NUMBER,
  town         VARCHAR2(40),
  ORDER MEMBER FUNCTION match (l typ_location) RETURN INTEGER);
/
Creating Body:
CREATE OR REPLACE TYPE BODY typ_location AS 
  ORDER MEMBER FUNCTION match (l typ_location) RETURN INTEGER IS 
  BEGIN 
    IF home_no < l.home_no THEN
      RETURN -1;               
    ELSIF home_no > l.home_no THEN 
      RETURN 1;                
    ELSE 
      RETURN 0;
    END IF;
  END;
END;
/

Using the typ_location object and its member functions:

DECLARE
Firstloc typ_location;
Secondloc typ_location;
x number;
 
BEGIN
 Firstloc :=NEW typ_location(21, 'Los Angeles');
 Secondloc :=NEW typ_location(11, 'Los Vegas');
 x := Firstloc.match(Secondloc);
 
DBMS_OUTPUT.PUT_LINE('order (1 is greater, -1 is lesser):' ||x); 
END;
/

Result:

order:1
PL/SQL procedure successfully completed.

Inheritance for PL/SQL Objects:

User can create object from already created base objects and the base objects should be declared as NOT FINAL to implement inheritance.

Example: Create one more object as addition, inheriting from the typ_location object.

CREATE OR REPLACE TYPE typ_location AS OBJECT (
  home_no  NUMBER,
  town VARCHAR2(40),
  ORDER MEMBER FUNCTION match (l typ_location) RETURN INTEGER,
NOT FINAL member procedure addition
);
/

Creating Body:

CREATE OR REPLACE TYPE BODY typ_location AS 
  ORDER MEMBER FUNCTION match (l typ_location) RETURN INTEGER IS 
  BEGIN 
    IF home_no < l.home_no THEN
      RETURN -1;               
    ELSIF home_no > l.home_no THEN 
      RETURN 1;                
    ELSE 
      RETURN 0;
    END IF;
  END;
MEMBER PROCEDURE message IS
   BEGIN
      dbms_output.put_line('Welcome to inheritance !!!');
   END message;
END;
/

Result:

Type body created.

Creating the child object addition:

CREATE OR REPLACE TYPE addition UNDER typ_location
(  
   name varchar2(20);
   OVERRIDING member procedure message
)
/

Result:

Type created.

Creating the type body for the child object addition:

CREATE OR REPLACE TYPE BODY addition AS
OVERRIDING MEMBER PROCEDURE message IS
BEGIN
      dbms_output.put_line('Name: '|| name);
END messgae;
/

Result:

Type body created.

Using the addition object and its member functions:

DECLARE
   a1 addition;
   BEGIN
   a1:= addition('James');
   a1.display;
   END;
/

Result:

Welcome to inheritance!!!
Name: James
PL/SQL procedure successfully completed.

Abstract Objects in PL/SQL:

To declare an abstract object user use NOT INSTANTIABLE clause and user cannot use an object of abstract as it is we need to create a child type like objects to use its functionalities.

Example:

CREATE OR REPLACE TYPE typ_location AS OBJECT (
  home_no  NUMBER,
  town VARCHAR2(40),
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE message) 
  NOT INSTANTIABLE NOT FINAL
/

Result:

Type created.

DBMS Output

PL/SQL provides a built-in package DBMS_OUTPUT which allows user to display information of debugging, output and messages from subprograms, triggers, blocks and packages of PL/SQL. And the package dbms_output has a put_line procedure to allow user to put data to output to a screen of PL/SQL.

Functions:

dbms_output.put: 

To insert text into the buffer of output.

dbms_output.enable:

Enable dbms_output support. The buffer_size represents unlimited buffer size for a NULL value.

dbms_output.disable:

Deactivate dbms_output support.

dbms_output.get_lines:

From the buffer read the array line

dbms_output.get_line:

From the buffer read one line

dbms_output.new_line:

To insert end of line symbol into output buffer

dbms_output.put_line:

Insert a line into the output buffer.

Example:

BEGIN
   dbms_output.put_line  ('Hello');
   dbms_output.put_line('Example of dbms_output!');
   dbms_output.put_line('Welcome to pl/sql!');

END;
/

Result:

Hello
Example of dbms_output!
'Welcome to pl/sql!

That was our quick introduction guide to PL/SQL. We try to do our best to check for correctness but errors do happen. If you find out that something is not working or can be improved. Do let us know.

http://www.oracle.com/technetwork/database/application-evelopment/plsql/overview/index.html

Here is a quick video tutorial on PL/SQL by oraclecoach.com that is very helpful

 Thanks for reading. We hope that got a fair bit of kickstart from this guide. Below are some resources for further reading.