CBSE Book Class 12 Informatics Practices PL SQL

Chapter – 1

PL/SQL

(Informatics Practices)

PL/SQL = Procedural Language extensions to SQL

An Oracle-specific language combining features of:

• modern, block-structured programming language
• database interaction via SQL

Designed to overcome declarative SQL's inability to specify control aspects of DB interaction.

Used to add procedural capabilities to Oracle tools.

PL/SQL is implemented via a PL/SQL engine (cf. JVM)

• which can be embedded in clients (e.g. Forms, SQL*Plus)
• which is also usually available in the Oracle server

Why PL/SQL?

Consider trying to implement the following in SQL (SQL*Plus):

If a user attempts to withdraw more funds than they have from their account, then

indicate "Insufficient Funds", otherwise update the account

A possible implementation:

ACCEPT person PROMPT 'Name of account holder: '

ACCEPT amount PROMPT 'How much to withdraw: '

UPDATE Accounts

SET balance = balance - &amount

WHERE holder = '&person' AND balance > &amount;

SELECT 'Insufficient Funds'

FROM Accounts

WHERE holder = '&person' AND balance < = &amount;

Two problems:

• doesn't express the "business logic" nicely
• performs both actions when (balance-amount < amount)

We could fix the second problem by reversing the order (SELECT then UPDATE).

But in SQL there's no way to avoid executing both the SELECT and the UPDATE

PL/SQL allows us to specify the control more naturally:

-- A sample PL/SQL procedure

PROCEDURE withdrawal(person IN varchar(20), amount IN REAL ) IS

current REAL;

BEGIN

    SELECT balance INTO current

    FROM Accounts

    WHERE holder = person;

     IF (amount > current)

    dbms_output.put_line('Insufficient Funds');

    ELSE

    UPDATE Accounts

    SET balance = balance - amount

    WHERE holder = person AND balance > amount;

    COMMIT;

    END IF;

END;

And package it up into a useful function, which could be used as:

SQL> EXECUTE withdrawal('John Shepherd', 100.00);

Please refer to attached file for CBSE Classs 12 Informatics Practices PL-SQL