INTRODUCTION TO PL/SQL



INTRODUCTION TO PL/SQL

PL/SQL is the procedural extension to ORACLE SQL. Using PL/SQL you create applications that includes custom SQL and programming logic. PL/SQL modules are fundamental to enter, manipulate and report data. PL/SQL is a block-structured language. Basic units of PL/SQL procedures, functions, and anonymous blocks are logical blocks. These blocks can can include any number of nested sub-blocks. A block (or sub-block) lets you group logically related declarations and statements. That way, you can place declarations close to where they are used. The declarations are local to the block and cease to exist when the block completes.

PL/SQL BLOCK

DECLARE

Procedural

Procedural

BEGIN

SQL or/and Procedural

[EXCEPTION HANDLERS]

END;

Points to Remember

1. Blocks can include other nested blocks

2. Statements end with a ;

3. Two ways of commenting – precede with a – or surround with /* */

4. Declared objects/variables have certain scope

Example of a PL/SQL Block

DECLARE

qty_on_hand NUMBER(5);

BEGIN

SELECT quantity INTO qty_on_hand FROM inventory

WHERE product = 'TENNIS RACKET'

FOR UPDATE OF quantity;

IF qty_on_hand > 0 THEN -- check quantity

UPDATE inventory SET quantity = quantity - 1

WHERE product = 'TENNIS RACKET';

INSERT INTO purchase_record VALUES ('Tennis racket purchased', SYSDATE);

ELSE

INSERT INTO purchase_record VALUES ('Out of tennis rackets', SYSDATE);

END IF;

COMMIT;

END;

Declaration of Variables and Constants:

Variables in PL/SQL can be of any regular SQL datatype such as CHAR, DATE, and NUMBER. In addition there are special PL/SQL datatypes such as BOOLEAN and BINARY_INTEGER and Composite data types such and records using the TABLE, and RECORD.

For example, assume that you want to declare a variable named part_no to hold 4-digit numbers and a variable named in_stock to hold the Boolean value TRUE or FALSE. You declare these variables as follows:

part_no NUMBER(4);

in_stock BOOLEAN;

Declaring a constant is like declaring a variable except that you must add the keyword CONSTANT and immediately assign a value to the constant. Thereafter, no more assignments to the constant are allowed. In the following example, you declare a constant named upper_sal:

upper_sal CONSTANT REAL := 999999.99;

Scalar Data types:

|CHAR |fixed length character data |

|VARCHAR 2 |variable character length data |

|NUMBER |Fixed or floating point numbers of vitually any size |

|BINARY INTEGER |Integers |

|DATE |date values |

|BOOLEAN |TRUE or FALSE Note: there is no BOOLEAN data type in database table. |

Composite Data types:

Table

1. Is similar but not the same as a database table

2. Must contain only one column of any scalar datatype

3. Is like a one-dimensional array of any size

4. Has its elements indexed with a binary integer column called the primary key of the table

Record

1. Contains uniquely defined columns of different data types,

2. Enables us to treat dissimilar columns that are logically related as a single unit.

Assigning values to a variable

You can assign values to a variable in two ways. The first way uses the assignment operator (:=), a colon followed by an equal sign. You place the variable to the left of the operator and an expression to the right. Some examples follow:

tax := price * tax_rate;

bonus := current_salary * 0.10;

amount := TO_NUMBER(SUBSTR('750 dollars', 1, 3));

valid := FALSE;

The second way to assign values to a variable is to select or fetch database values into it. In the following example, you have Oracle compute a 10% price hike to an existing price for an item.

SELECT item_price * 0.10 INTO new_price FROM item WHERE item_id = itemnum;

Then, you can use the variable new_price in another computation or insert its value into a database table.

Some examples:

DECLARE

Name, city char(20); illegal because of comma

DECLARE

Name CHAR(20); ---legal

City CHAR(20); -- legal

Note: you can use not null to disallow null values to a variable. Only one identifier per line is allowed.

Icounter BINARY INTEGER;

Salary NUMBER(10,2);

Pcnt_increase CONSTANT NUMBER := 10;--cannot later change this value

Anniversary DATE := ‘27-May-1987’

Inv_on_hand BOOLEAN := NULL;

DECLARE

TYPE item_name_tabtype IS TABLE OF VARCHAR2(20)

INDEX BY BINARY INTEGER;

Item_name_tab item_name_tabtype;

Here item_name_tab represents an entire PL/SQL table.

DECLARE

TYPE item_rec_type IS RECORD

(item_id char(3) NOT NULL,

item_name item.item_name%TYPE,

item_price item.item_price%TYPE);

item_rec item_rec_type;

item_rec represents an entire record.

%TYPE pulls datatypes from the corresponding table or other variable. The advantage of doing this is that you do not have to change your program when the underlying table changes.

DECLARE

Item_row item%rowtype

:= is the assignment operator

= is the equality operator

|DECLARE |

| |

|TYPE item_rec_type IS RECORD |

|(item_id char(3) NOT NULL, |

|item_name item.item_name%TYPE, |

|item_price item.item_price%TYPE); |

|item_rec1 item_rec_type; |

|item_rec2 item_rec_type; |

|emp_name VARCHAR2(20); |

|icount BINARY_INTEGER; |

|invonhand BOOLEAN; |

|BEGIN |

|Icount := icount + 1; |

|Emp_name := ‘Manning’; |

|Item_rec1.item_id := ‘010’; |

|Item_rec1.item_name := ‘Television’; |

|Item_rec1.item_price := 12.23; |

|Item_rec2 := Item_rec1; |

|Invonhand := TRUE; |

|END; |

Note: PL/SQL automatically tries to convert datatypes if the correct data type is not used. It can convert expressions from CHAR or VARCHAR2 to NUMBER but NOT the other way around. Similarly it converts CHAR or VARCHAR2 to DATE but NOT from DATE to CHAR/VARCHAR2.

SCOPE OF THE VARIABLE

DECLARE

Upper_sal CONSTANT NUMBER(10,2) := 999999.99;

Emp_id CHAR(6);

BEGIN

DECLARE

Dept_no CHAR(4)

BEGIN

Dept_no := ‘1011’;

END;

DECLARE

Old_dept CHAR(4)

BEGIN

Old_dept := ‘1000’; /* cannot refer to dept_no here because it is in the prvious subblock but can refer to upper_sal or emp_id which are declare in the main section. */

END;

END;

SQL STATEMENTS IN PL/SQL

Any Oracle SQL statement may be used within PL/SQL. Full oracle syntax is allowed in such SQL statements. Within such SQL statements PL/SQL variables may be used wherever it is legal to use a constant in SQL. You can only use DML statements. DDL and DCL statements are not allowed within a PL/SQL block.

DECLARE

Item_price NUMBER(5,2) := 43.45;

Item_name VARCHAR2(20) := ‘Television’;

BEGIN

INSERT INTO item (item_id, item_name, item_price)

VALUES (‘020’, item_name, item_price);

END;

Similarly, you can use DELETE and update statements as well.

SELECT statements within PL/SQL block needs further study as it is only the SELECT statement that returns data. We must provide a loation for this data to be stored via an INTO clause. A SELECT INTO statement must return only one row. If there are multiple rows returned or if there are no rows, it will result in a PL/SQL error. It is possible to handle multiple rows through the use of a cursor.

DECLARE

Item_nam item.item_name%type;

Item_pri item.item_price%type;

BEGIN

SELECT item_name, item_price FROM item INTO item_nam, item_pri

WHERE item_id = ‘020’;

----work with the retrived data

END;

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download