Brief tutorial on oracle PL/SQL


PL SQL Introduction

Generic Section

Declare

<>
Begin
<>
Exception
<>
End;


Literals

  1. Numeric Literals = 25.32, 25e-04, +12, -5
  2. String Literals = ‘Don’’t go’
  3. Character Literals = ‘*’, ‘A’
  4. Logical Literals = TRUE, FALSE, NULL



PL SQL Data Types

1. Number, char, date, Boolean.

2. %Type.

  • NOT NULL causes to set a value without NULL.

Assigning values to a Variables ->
  1. :=
  2. Selecting and fetching Table Data Values into a variables

3. Constants = pi constant number(4,2) :=3.41

4. RAW = Used to store binary data.

5. Rowid = Used to store Rowid Data.

6. LOB Types ->
a. BLOB (Binary LOB) = Store binary data upto 4 GB.
b. CLOB (Character LOB) = Store single Byte characters upto 4 GB.
c. BFILE (Binary File) = Pointer to read binary data stored as external file outside the database.

Create Table <Table Name> (<column name> <data type> <size()>, <column name> CLOB)






Displaying User Messages

SERVEROUTPUT [ON/OFF]
DBMS_OUTPUT.PUT_LINE(‘Hello World’)

Comments
  1. –
  2. /* */





CONTROL STRUCTURE

  1. Conditional Control

IF <Condition> THEN
<Action>
ELSIF <Condition> THEN
<Action>
ELSE
<Action>
END IF;


@ Write a PL SQL block that will accept account number from user, check if user balance is less than the minimum balance, only then deduct Rs. 100/- from main account.


DECLARE
mCurrent_Balance number(4,2);
mFine_Amount constant number(4,2) := 100.00;
mMinimum_Balance constant Number(4,2) := 5000.00;
mAccount_Number number(11,2);
BEGIN
mAccount_Number := &mAccount_Number;
select current_balance into mCurrent_Balance
from account_master
where account_number= mAccount_Number;

IF mCurrent_Balance< mMinimum_Balance THEN
Update account_master
Set current_balance= current_balance- mFine_Amount;
where account_number= mAccount_Number;
END IF;

END;




  1. Iterative Control

  1. Simple Loop

Declare
i number :=0;
BEGIN
LOOP
i := i+1;
Exit when i>10;
End Loop;
End;

  1. While Loop

While <Condition>
Loop
<Action>
End Loop;

DECLARE
Radius number(5);
Area number(5,2);
Pi constant number(4,2) := 3.41;

BEGIN
Radius := 2;
WHILE Radius<6
Loop
Area := Pi * power(Radius,2);
Dbms_output.put_line(‘Area = ‘ || Area);
Radius := radius+1;
End loop;
END;



  1. For LOOP

FOR Variable IN [REVERSE] start..end
LOOP
<action>
End loop;

Declare
Given_number number(5) := '1234';
Str_length number(2);
Inverted_number number(5);
Begin
Str_length := length(Given_number);
For for_variable IN reverse 0.. Str_length
Loop
Inverted_number :=
Inverted_number || substr(Given_number, for_variable, 1);
End loop;
Dbms_output.put_line(to_char(Inverted_number));
End;




  1. Sequential Control

Goto <codeblock name>

Declare
Begin
IF 2>3 Then
Goto mark_status;
End If
<<mark_status>>
dbms_output.put_line(‘Inside mark_status’));
End;






























PL SQL Transaction


    1. Commit

    1. Rollback

Rollback using save point name ->

DECLARE
BEGIN
INSERT into Table1 values(3, 'A','2000');
SAVEPOINT no_insert;
INSERT into Table1 values(4, 'C','2000');
INSERT into Table1 values(5, 'D','2000');
ROLLBACK TO SAVEPOINT no_insert;
commit;END;



CURSOR

CURSOR has four attributes ->

  1. %ISOPEN = True if cursor is open.
  2. %FOUND= True if records is fetched successfully
  3. %NOTFOUND=
  4. %ROWCOUNT= Returns no. of records processed by cursor.


  1. Implicit Cursor

It is used to access information for the status of last insert, update, delete or single row select statement.





DECLARE
i number(2) := 4;BEGIN
WHILE i<8
LOOP
UPDATE table1 set phone = '5000000' where id = i;
IF(SQL%FOUND) THEN
dbms_output.put_line('Updated Phone for ID = ' || to_char(i));
END IF;
IF(SQL%NOTFOUND) THEN
dbms_output.put_line('NOt Updated Phone for ID = ' || to_char(i));
END IF;
i := i+1;
END LOOP;END;
Result ->
Updated Phone for ID = 4
NOt Updated Phone for ID = 5
NOt Updated Phone for ID = 6
NOt Updated Phone for ID = 7

Use of %RowCount ->

DECLARE
BEGIN
UPDATE table1 set phone = '5000000' where id = 4;
DBMS_OUTPUT.PUT_LINE(to_char(SQL%ROWCOUNT));END;


B. Explicit Cursor


  1. OPEN
  2. FETCH
  3. CLOSE


DECLARE
CURSOR c1 IS SELECT NAME,PHONE from table1;
mName table1.name%Type;
mPhone table1.Phone%Type;BEGIN
OPEN c1;
IF c1%ISOPEN Then
LOOP
Fetch c1 into mName, mPhone;
EXIT when c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(mName|| mPhone);
END LOOP;
ELSE
Close c1;
end If; END;


@ Show the Name and phone no. of the only two customers having largest ID.

DECLARE
CURSOR c1 IS SELECT NAME,PHONE from table1 ORDER BY ID desc;
mName table1.name%Type;
mPhone table1.Phone%Type;BEGIN
OPEN c1;
LOOP
Fetch c1 into mName, mPhone;
DBMS_OUTPUT.PUT_LINE(mName|| mPhone);
Exit when c1%ROWCOUNT = 2 or c1%NOTFOUND;
END LOOP;
Close c1; END;


CURSOR For Loops

****

DECLARE
CURSOR c1 IS SELECT NAME,PHONE from table1 ORDER BY ID desc;BEGIN
FOR for_variables IN c1
LOOP
DBMS_OUTPUT.PUT_LINE( for_variables.Name ||
for_variables.Phone);
END LOOP; END;


































































Labels:

No comments :

Post a Comment

Subscribe to: Post Comments ( Atom )