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
At times, Brewbean's has changed the id number for existing products. In the past, they have had to add a new product row with the new id to the BB_PRODUCT table, modify all the co

Using Host Arrays The Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. However, this is the well-organized way to pass the colle

Develop Data Business Intelligence Project Project Description: We are linking our Microsoft SQL Database to GoodData Business Intelligence. We are seeking somebody who has e

EXIT Statement   You can use the EXIT statement to exit a loop. The EXIT statement has 2 forms: the conditional EXIT WHEN and the unconditional EXIT. With the either form, you

Use of COUNT in SQL It describes and discusses various general methods of expressing constraints, eventually noting that support for "=" with relation operands is sufficient f

Manipulating Individual Elements Faraway you have manipulated an entire collection. Within the SQL, to manipulate the individual elements of the collection, and then use the ope

Example of EXCEPT Operator - SQL Example, like its counterpart in the theory book, illustrates the convenience of allowing any table expression to be the source for an INSERT

The SQL ‘CREATE TABLE' scripts for all the tables you have implemented. Note that your tables must correspond exactly to the ERD you have provided in 1. above, or you will lose ma

I need SQL , WP SQL Expert Project Description: Expert required to modify WP SQL query. Skills required are MySQL, SQL, PHP, Wordpress

Using %TYPE The %TYPE attribute gives the datatype of a variable or the database column. In the example below, the %TYPE gives the datatype of a variable: credit REAL(7,2); debi