Transcript PL-SQL - IMED MCA 10+

Disadvantages Of SQL:
1.
SQL does not have any procedural capabilities.
2.
SQL statements are passed to Oracle engine one at a time. Each time
the SQL statement is executed , the call is made to engine resources.
This adds traffic on the network. Which decreases speed of data
processing.
3.
While processing SQL statement if an error occurs, the Oracle engine
displays its own error message.
Programming in Oracle
with PL/SQL
Procedural Language Extension to SQL
PL/SQL
• Allows using general programming tools
with SQL, for example: loops, conditions,
functions, etc.
• This allows a lot more freedom than
general SQL.
• We write PL/SQL code in a regular file, for
example PL.sql, and load it with @PL in
the sqlplus console.
PL/SQL Blocks
•
PL/SQL code is built of Blocks, with a unique
structure.
•
There are two types of blocks in PL/SQL:
1. Anonymous Blocks: have no name (like scripts)
•
can be written and executed immediately in SQLPLUS
•
can be used in a trigger
2. Named Blocks:
•
Procedures
•
Functions
Anonymous Block Structure:
DECLARE
(optional)
/* Here you declare the variables you will use in this
block */
BEGIN
(mandatory)
/* Here you define the executable statements (what the
block DOES!)*/
EXCEPTION
(optional)
/* Here you define the actions that take place if an
exception is thrown during the run of this block */
END;
/
(mandatory)
Always put a new line with only a
/ at the end of a block! (This
tells Oracle to run the block)
A correct completion of a block
will generate the following
message:
PL/SQL procedure successfully
completed
DECLARE
Syntax
identifier [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT expr];
Examples
Declare
birthday
age
name
magic
valid
Notice that PL/SQL
includes all SQL types,
and more…
DATE;
NUMBER(2) NOT NULL := 27;
VARCHAR2(13) := 'Levi';
CONSTANT NUMBER := 77;
BOOLEAN NOT NULL := TRUE;
Declaring Variables with the
%TYPE Attribute
Examples
DECLARE
sname
fav_boat
my_fav_boat
...
Accessing column sname
in table Sailors
Sailors.sname%TYPE;
VARCHAR2(30);
fav_boat%TYPE := 'Pinta';
Accessing
another variable
Declaring Variables with the
%ROWTYPE Attribute
Declare a variable with the type of a ROW
Accessing
of a table.
table
Reserves
reserves_record
Reserves%ROWTYPE;
And how do we access the fields in
reserves_record?
reserves_record.sid:=9;
Reserves_record.bid:=877;
• Control Structures:
• Conditional Control
• Iterative Control
• Sequential Control
1. Conditional Control:
if <condition> then
sequence of statements;
end if;
IF-THEN-ELSIF Statements
. . .
IF rating > 7 THEN
v_message := 'You are great';
ELSIF rating >= 5 THEN
v_message := 'Not bad';
ELSE
v_message := 'Pretty bad';
END IF;
. . .
•
Write PL/SQL code that will except an account number from the user and debit amount of
Rs. 2000 from a/c if the balance of a/c remains minimum Rs.500. The process is to be fired
on Account table. account (ac_Id, Name, bal)
Declare
ac_bal number(10,2);
ac_no varchar2(6);
debit_amt number(5) :=2000;
min_bal constant number(5,2):=500;
Begin
ac_no:=&ac_no;
select bal into ac_bal
from account
where ac_id=ac_no;
ac_bal:=ac_bal - debit_amt;
if ac_bal >= min_bal then
update accounts set bal=bal – debit_amt
where ac_Id=ac_no;
end if;
End;
2. Iterative Control:
i) Simple loop
ii) For loop
iii) while loop
• i) Simple loop
loop
sequence of statements;
end loop;
• ii) For loop
for counter in [reverse]lowerbound .. Upperbound
loop
sequence of statements;
end loop;
• iii) while loop
while <condition>
loop
<action>
end loop;
• Write a PL/SQL block to calculate the area of a circle for a value of
radius varying from 3 to 7. Store the radius & the corresponding values
of calculated area in a table, ‘Areas’.
Declare
pi constant number(4,2) := 3.14;
radius number(5);
area number(10,2);
Begin
radius:=3;
while radius<=7
loop
area:=pi*power(radius,2);
insert into areas values(radius,area);
end loop;
End;
• Write a PL/SQL block of code for inverting a number 5639 to 9365.
Declare
given_number(5):=‘5639’;
str_len number(2);
inverted_no varchar2(5);
Begin
str_len:=length(given_number);
for cntr in reverse 1 .. Str_len
loop
inverted number := inverted_number||substr(given_number, cntr,1);
end loop;
dbms_output.put_line(‘The given no. is’ || given_number)
dbms_output.put_line(‘The inverted number is’ || inverted_number)
End;
• Sequential Control:
goto <code block name>;
•
Write PL/SQL block of code to achieve the following. If the price of product p1 is less than
4000, then change the price to 4000. Th price change is to be recorded in the
old_price_table along with the product_no & the date on which the price was last changed.
Declare
selling_price number(10,2)
Begin
select sell_price into selling_price
from product_master
where product_no=‘p1’;
if selling_price<4000 then
goto add_old_price;
else
dbms_output.put_line(‘Current price’ || selling _price);
end if
<<Add_old_price>>
update product_master set sell_price=4000
where product_no= ‘p1’
insert into old_price (prduct_no, date_change, old_price)
values(‘p1’, sysdate, selling_price);
dbms_output.put_line(‘the new price of p1 is 4000’);
End;
• Error Handling in PL/SQL:
when <exception name> then
user defined actions to be carried out;
• Types Of Exceptions:
1. predefined exceptions:
They are raised automatically by the system during run time.
2. user defined Exceptions:
They must be raised explicitly using Raise statement.
• Some Predefined Exceptions:
1. No_data_found
2. Cursor_already_open
3. Dup_val_on_index
4. Srorage_error
5. Program_error
6. Zero_divide
7. Invalid_cursor
8. Login_denied
9. Invalid_cursor
10. Too_many_rows
•
User Defined Exception:
exception name <exception>;
Raise Statement:
raise <exception name>;
•
Declare
exception name <exception>;
Begin
SQL sentense;
if condition then
raise <exception name>;
end if;
Exception
when <exception name> then
user defined actions to be carried out;
End;
• The X company wants to check qty_in_hand.if it is less than 500
the company wants to display msg.
Declare
lo_val exception;
qty item_master.qty_in_hand%type;
Begin
select qty_in_hand into qty
from item_master where itemcode=‘i100’;
if qty<500 then
raise lo_val;
end if;
Exception
when lo_val then
dbms_output.put_line=(‘Qty not enogh’);
End;