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
Parameter Modes   To define the behavior of formal parameters you use the parameter modes. The 3 parameter modes, IN, OUT, & IN OUT, can be used with any subprogram. Though, a

Count Operator in SQL Example: Counting the students who have scored more than 50 in some exam (SELECT COUNT (*) FROM (SELECT DISTINCT StudentId FROM EXAM_MARK WHE

Example of WRAP Operator - SQL The effect of Example can be obtained in SQL but note that one needs to write down not only the names of the columns being wrapped but also the

Procedures   The procedure is a subprogram which performs a specific action. You write procedures using the syntax as shown below: PROCEDURE name [(parameter[, parameter, .

Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

Consider the schema for FreeChecking Bank, that we designed given below. Translate the given ER schema into SQL CREATE TABLE statements (indicating primary key, unique and foreign

Scoping Within the similar scope, all the declared identifiers should be unique. So, even if their datatypes differ, the variables and parameters cannot share the similar name.

Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

Based on the EMPLOYEE table created in Assignment #1, write a PL/SQL anonymous block that accepts an employee ID from the user input and finds whether the employee ID is in the EMP

Third Step at defining type SID in SQL CREATE DOMAIN SID AS VARCHAR(5) CHECK ( VALUE IS NOT NULL AND SUBSTRING(VALUE FROM 1 FOR 1) = 'S' AND CAST('+'||SUBSTRING(VALUE