Features of pl/sql, PL-SQL Programming

Main features of PL/SQL

A good way to get familiar with PL/SQL is to look at a sample program. The below program processes an order for tennis rackets. At first, it declares a variable of type NUMBER to store the quantity of tennis rackets on hand. After that, it retrieves the quantity on hand from a database table name inventory. If the quantity is larger than zero, the program updates the table and inserts a purchase record into other table named purchase_record. If not, the program inserts an out-of-stock record into the purchase_record table.

-- available online in file 'examp1' 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;

With the PL/SQL, you can use the SQL statements to manipulate Oracle data and flow-of-control statements to process the data. Furthermore, you can declare constants and variables, define procedures and functions, and trap runtime errors. Therefore, PL/SQL combines the data manipulating power of SQL with the data processing power of the procedural languages.

Posted Date: 10/2/2012 1:09:55 AM | Location : United States







Related Discussions:- Features of pl/sql, Assignment Help, Ask Question on Features of pl/sql, Get Answer, Expert's Help, Features of pl/sql Discussions

Write discussion on Features of pl/sql
Your posts are moderated
Related Questions
Use of Table Comparisons - SQL Table comparisons where it is noted that although table expressions cannot be compared, we have TABLE (t) to convert a table expression t into

Information Hiding   With the information hiding, you see only the details that are significant at a given level of algorithm and data structure design. The Information hiding

%ISOPEN The Oracle closes the SQL cursor automatically after executing its related SQL statement. As a result, the %ISOPEN forever yields FALSE.

When Are Constraints Checked Under the model constraints are conceptually checked at all statement boundaries (and only at statement boundaries). By default the same is true

Built-In Functions The PL/SQL provides a lot of powerful functions to help you to manipulate the data. These built-in functions fall into the categories as shown below: error r

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

How Exceptions Are Raised By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number

DECLARE : This keyword signals the beginning of the declarative section of the PL/SQL block, that contains local declarations. The Items declared locally exist only within the

Object Types An object type is a user-defined complex datatype which encapsulates the data structure along with the functions and procedures required to manipulate the data. Th

Advantages of Wrapping   The PL/SQL Wrapper convert the PL/SQL source code into a transitional form of the object code. By hiding the application internals, the Wrapper secure