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
Write a stored procedure that accepts the post code in which the customer resides as the input parameter. The procedure should then use an explicit cursor to display comprehensive

BETWEEN and NOT BETWEEN Operator in SQL Example: Restricting exam marks to between 0 and 100 CREATE ASSERTION Marks_between_0_and_100 CHECK (NOT EXISTS (SELECT * FROM

set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_d

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

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

Overloading The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number

Anatomy of a Command Figure, showing a simple SQL command, is almost identical to its counterpart in the theory book. The only difference arises from the fact that SQL uses a

Question: (a) In the context of database security explain how the following database features help to enforce security in the database system: (i) Authorisation (ii) Access

Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Data Types and Representations This explains the concept possible representation, abbreviated possrep, and explains how these can be used in conjunction with constraints to de