DBMS Programs

declare
acc_no varchar2(9);
acc_bal number(11,2);
debit_amount number(5) := 2000;
min_bal number(5,2) := 500.00;
begin
acc_no:=&acc_no;
select balance into acc_bal from account where acc:=acc_no
acc_bal:=acc_bal-debit_amount
if acc_bal > min_bal then
update account set balance :=balance-debit_amount
where acc:=acc_no;
endif;
end;
/



declare
aa number;
bb number;
begin
aa:=&aa;
bb:=&bb;
update a11 set a11.a=aa where a11.b=bb;
if sql%found
then dbms_output.put_line ('yes');
else dbms_output.put_line ('no');
end if;
end;
/
value for aa: 2
5: aa:=&aa;
5: aa:=2;
value for bb: 9
6: bb:=&bb;
6: bb:=9;

L procedure successfully completed.



declare
aa number;
bb number;
begin
aa:=&aa;
bb:=&bb;
insert into a11 values (aa,bb);
if sql%found
then dbms_output.put_line ('yes');
else dbms_output.put_line ('no');
end if;
end;





declare
an varchar2(6);
ab integer(11,2);
da number(5):=2000;
mb integer(5,2):=500;
begin
an:='&an';
select balance into ab from account where acc:=an
ab:=ab-da
if ab >= mb
update account set balance:=balance-da
where acc:=an;
endif;
end;
/



DECLARE
var NUMBER;
BEGIN
/*N.B. for loop variables in pl/sql are new declarations, with scope only inside the loop */
FOR var IN 0...10 LOOP
DBMS_OUTPUT.put_line(var);
END LOOP;
IF (var IS NULL) THEN
DBMS_OUTPUT.put_line('var is null');
ELSE
DBMS_OUTPUT.put_line('var is not null');
END IF;
END;








declare
d number:=&d;
cursor c is select salary, id from emp1 where id=d;
s c%rowtype;
begin
open c;
loop
fetch c into s;
exit when c%notfound;
update emp1 set salary=salary+10 where id=d;
end loop;
close c;
end;
/
value for d: 001
2: d number:=&d;
2: d number:=001;

L procedure successfully completed.



declare
cursor c is select salary, id from emp1;
sal emp1.salary%type;
idd emp1.id%type;
begin
open c;
loop
fetch c into sal, idd;
exit when c%notfound;
update emp1 set salary=sal+10 where id=&idd;
end loop;
close c;
end;

procedure successfully completed.


declare
an varchar2(6);
ab number;
da number(5):=2000;
mb integer(5,2):=500;
begin
an:='&an';
select balance into ab from account where acc=an;
ab:=ab-da;
if ab >= mb
then update account set balance=balance-da where acc=an;
end if;
end;
procedure successfully completed.



create or replace
trigger a_b
before
insert or delete
on a
for each row
begin
RAISE_APPLICATION_ERROR(-20000,'CANNOT CHANGE DATE OF BIRTH');
insert into a_a values(:new.n,:new.s,:new.no);
end;





create procedure dbo.area7
as
set nocount on
declare @radius int
select @radius=3
declare @area float(3)
declare @pi float
select @pi=3.14
while(@radius<=7) begin select @area=@pi*@radius*@radius insert into dbo.area1(radius,area) values(@radius,@area) select @radius=@radius+1 end go exec dbo.area7 create table area1(radius int ,area float(3)) select * from area1; declare acno varchar(9); acbal number; debit number:=2000; min number:=500; begin acno:='&acno'; select bal into acbal from account where accid=acno; acbal:=acbal-debit; if acbal>=min then
update account set bal=bal-debit where accid=acno;
endif;
end


52567



create table a11(a number, b number);
1 declare
2 p number:=2;
3 q number;
4 begin
5 q:=&q;
6 while q<5 7 loop 8 q:=q+1; 9 insert into a11 values(p,q); 0 end loop; 1* end; L> /
ter value for q: 2
d 5: q:=&q;
w 5: q:=2;

/SQL procedure successfully completed.

L> select *from a11;

A B
-------- ----------
2 3
2 4
2 5







declare
p number:=2;
q number;
begin
q:=&q;
if q>=5
then p:=p*q;
else
P:=1;
q:=q+1;
insert into a11 values(p,q);
end if;
end;
/
value for q: 2
5: q:=&q;
5: q:=2;

L procedure successfully completed.