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
LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Example of Table Literal - SQL Example: A Table Literal (correct version) VALUES ('S1', 'C1', 'Anne'), ('S1', 'C2', 'Anne'), ('S2', 'C1', 'Boris'), ('S3', 'C3'

Overloading: Similar to packaged subprograms, methods of the same type can be overloaded. That is, you can use similar name for various methods if their formal parameters diff

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

Named Notation The second procedure call uses the named notation. An arrow (=>) serve as the relationship operator that associates the formal parameter to the left of the arro

SQL Cursor   The Oracle implicitly opens a cursor to process each SQL statement not related with an explicit cursor. The PL/SQL refers to the most current implicit cursor as t

LIKE Operator You use the LIKE operator to compare the character value to a pattern. The Case is significant. LIKE returns the Boolean value TRUE when the character patterns mat

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

Predicate - SQL Consider the declarative sentence-a proposition-that is used to introduce this topic:  "Student S1, named Anne, is enrolled on course C1." Recall that th

Parameter and Keyword Description: EXIT: An unconditional EXIT statement (i.e., one without a WHEN clause) exits the present loop instantly. The Execution resumes with th