PREPARE
PL/SQL
Set serverout on;
---- t:=mod(no,10);
declare
no number(4);
re number(4):=0;
t number(4);
begin
no:=&no;
while (no>0)
loop
t:= no mod 10;
re:=((re*10)+t);
no:=trunc(no/10,2);
dbms_output.put_line(‘NO IS ‘ || no);
dbms_output.put_line(‘NO IS ‘ || re);
end loop;
dbms_output.put_line(‘NO IS >>‘ || re);
end;
/
REVERSE
Set serverout on;
declare
temp varchar2(10);
str varchar2(20):=’ABC’;
rev varchar2(20);
len number(4);
begin
dbms_output.put_line(LENGTH(str));
len:= LENGTH(str);
while (len>0)
loop
temp:=substr(str,len,1);
rev:=concat(rev,temp);
len:=len-1;
end loop;
dbms_output.put_line(rev);
end;
/
create or replace Procedure vNPro (str in varchar2,reve out varchar2) is
create or replace Procedure vNPro (str in varchar2) is
temp varchar2(20);
rev varchar2(20);
len number(2);
begin
len:=length(str);
dbms_output.put_line(‘LENGTH’ || len);
while (len>0)
loop
temp:=substr(str,len,1);
rev:=concat(rev,temp);
len:=len-1;
end loop;
dbms_output.put_line(rev);
end;
S REVERSE WITH PROCEDURE >>>>>>>>>>>>>>>>>>>>>>>>>
create or replace Procedure vNProOut (str in varchar,rev out varchar) is
temp varchar2(20);
len number(2);
begin
len:=length(str);
while(len>0)
loop
temp:=substr(str,len,1);
rev:=concat(rev,temp);
len:=len-1;
end loop;
end;
declare
tem varchar2(20);
begin
scott.vNProOut(‘vikaS’,tem);
dbms_output.put_line(‘REVER ‘ || tem);
end;
declare
str varchar2(10):=’ABC’;
temp varchar2 (10);
begin
temp:=substr(str,1,1);
dbms_output.put_line(‘TE’ || temp);
temp:=substr(str,2,1);
dbms_output.put_line(‘TE’ || temp);
temp:=substr(str,3,1);
dbms_output.put_line(‘TE’ || temp);
end;
dbms_output.put_line (ASCII(‘h’));
dbms_output.put_line (ASCII(str));
dbms_output.put_line (substr(str,2,4));
S REVERSE WITH FUNCTION >>>>>>>>>>>>>>>>>>>>>>>>>
Create or Replace Function FunIn (str in varchar)
Return varchar is
temp varchar2(20);
rev varchar2(20);
len number(10);
begin
len:=length(str);
while (len>0)
loop
temp:=substr(str,len,1);
rev:=concat(rev,temp);
len:=len-1;
end loop;
dbms_output.put_line(‘REV is ‘ || rev);
return rev;
end;
Create or Replace Trigger vnTrig
Before insert on Emp
for each row
Begin
Dbms_output.put_line(‘ majaa AYAAA!>>> Trigger badiya HAI’);
End;
Create or Replace Trigger vNTrig
Before insert on Emp
for each row
Begin
raise Err;
Exception when Err then
Dbms_output.put_line(‘ majaa AYAAA!>>> Trigger badiya HAI’);
End;
Insert into emp values (1001,’SARDAR’,’MD’,7566,sysdate,10000,20,20);
on Emp update on Emp delete
commit;
TRIGGER
Create or Replace Trigger vNTrigAcc
Before insert on vNAccount
for each row
declare
Err Exception;
Begin
dbms_output.put_line(‘ HI’);
if(:new.Bal<1) then
dbms_output.put_line (‘Records NOT INSERTED’);
rollback;
raise Err;
else
dbms_output.put_line (‘Records INSERTED’);
end if;
Exception when Err then
dbms_output.put_line(‘Plz Dep more than Rs. 1 >>>>’);
end;
delete from vNAccount;
Select * from vNAccount;
insert into vNAccount values (1,10);
declare
begin
dbms_output.put_line (scott.FunIn(‘vin’));
end;
CURSOR
Create table vNProduct (
ProdNo number(4) primary key,
Descr varchar2(4),
Qty number(4),
Rord number(4)
);
Create table vNOrder(
ProdNo number(4) references vNProduct(ProdNo),
Name varchar2(4),
Qty number(4)
);
insert into vNProduct values(1,’CAT’,10,20);
insert into vNProduct values(2,’MAT’,20,15);
select * from vNProduct;
select * from vNOrder;
commit;
Declare
Cursor CurIns is
Select * from vNProduct;
vRec vNProduct%rowtype;
Begin
Open CurIns;
Loop
Fetch CurIns into vRec;
If (vRec.Qty < vRec.ROrd ) then
Insert into vNOrder values (vRec.ProdNo,vRec.Descr,vRec.Qty);
Else
dbms_output.put_line (‘ WHAT THIS CUR SHOULD DO’);
End if;
Exit when CurIns %notfound;
End loop;
Close CurIns;
End;
/
PRIME NO’S >>>>>>>>>>>>>>>>>>>>>>>>>>
declare
flag numeric(1);
no numeric(2);
i numeric(1):=2;
begin
flag:=1;
no:=&FOR_PRIME;
while(i<no)
loop
if(no mod i=0) then
flag:=0;
i:=no;
else
i:=i+1;
flag:=1;
end if;
end loop;
if(flag=0) then
dbms_output.put_line(no || ‘ >>> NOT A PRIME’);
else
dbms_output.put_line(no || ‘ >>> its a PRIME’);
end if;
end;
PRIME NO’S USING PROC>>>>>>>>>>>>>>>>>>>>>>>
Create or Replace Procedure ProPrime (no in number) is
Create or Replace Procedure ProPrime (no in number,OutPt out varchar) is
div number(2):=2;
flag number(2):=0;
begin
while (div<no)
loop
if(mod(no,div)=0) then
flag:=1;
exit;
else
div:=div+1;
end if;
end loop;
if(flag=0) then
-- OutPt:=’PRIME’;
dbms_output.put_line(‘PRIME’);
else
-- OutPt:=’NOT PRIME’;
dbms_output.put_line(‘not PRIME’);
end if;
end;
declare
no number(2);
val varchar2(30);
begin
no:=&no;
-- scott.ProPrime (no,val);
scott.ProPrime (no);
--dbms_output.put_line(val);
end;
PRIME NO’S USING FUNC>>>>>>>>>>>>>>>>>>>>>>>
Create or Replace Procedure ProPrime (no in number) is
Create or Replace Function FuncPrime (no in number,OutPt out varchar)
Return varchar is
div number(2):=2;
flag number(2):=0;
begin
while (div<no)
loop
if(mod(no,div)=0) then
flag:=1;
exit;
else
div:=div+1;
end if;
end loop;
if(flag=0) then
OutPt:=’PRIME’;
dbms_output.put_line(‘>>>PRIME’);
else
OutPt:=’NOT PRIME’;
dbms_output.put_line(‘>>>not PRIME’);
end if;
return no;
end;
declare
no number(2);
val varchar2(30);
begin
no:=&no;
-- scott.ProPrime (no,val);
dbms_output.put_line(scott.FuncPrime(no,val));
dbms_output.put_line(val);
end;
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
CREATE table de ( c varchar2(2));
select * from de;
desc de;
Roll back;
Commit;
Savepoint st1;
CREATE table de1 ( c varchar2(2));
Roll back st1;
desc de1;
delete savepoint st1; -- how to
delete st1;
>>>>>>>>>>>>>
Declare
Cursor CurNor is
Select * from Emp;
N number(5):=2;
N2 number(5):=0;
Begin
Open CurNor;
-- Open CurNor;
dbms_output.put_line(N/N2);
Close CurNor;
Close CurNor;
Exception
when cursor_already_open OR invalid_cursor then
dbms_output.put_line(‘ Dude the Cursor is alreaDY OPENED OR CLOSED’);
when zero_divide then
dbms_output.put_line(‘ Dude the NO can'’t be divided with zerO %’);
End;
/
Assignment DISPLAY 2nd highest salary in ANOTHER WAY…
Create or Replace Function vNSec
Return varchar is
Cursor CurSec is
Select * from Emp order by sal desc;
vRec Emp %rowtype;
vMax Number(5):=1;
noRows number(5):=0;
Begin
Open CurSec;
Loop
Fetch CurSec into vRec;
dbms_output.put_line(‘NAME IS ‘||vRec.eName);
if(vMax=2) then
return vRec.eName;
end if;
vMax:=vMax+1;
noRows:= noRows+1;
exit when CurSec %notfound;
End Loop;
dbms_output.put_line(‘No of ROWS‘||noRows);
close CurSec;
End vNSec;
/
Declare
na varchar(20);
Begin
na:=Scott.vNSec;
dbms_output.put_line(‘NAME IS DE ‘ ||na);
End;
/
create table vNT (
RolNo number(2),
Nam varchar2(4),
DOB date);
Insert into vNT values (1,’tr’,’1-jan-2006’);
Insert into vNT values (2,’tr’,’2-jan-2006’);
Insert into vNT values (3,’tr’,’2-jan-2006’);
Insert into vNT values (4,’tr’,’2-jan-2006’);
Insert into vNT values (5,’tr’,’1-jan-2006’);
Insert into vNT values (6,’tr’,’1-jan-2006’);
Insert into vNT values (6,’tr’,’2-jan-2006’);
Insert into vNT values (7,’tr’,’17-oct-2005’);
Select * from vNT;
UPDATE vNT set nam=’adf’;
Delete from vNT;
Create or Replace Trigger vnTr
before insert on vNT
for each row
Begin
if(to_char(:new.DOB,’DD’)=’01’) then
dbms_output.put_line (‘ SORRY UR VALUE OF DATE SHOULD BE GREATER THAN ONE 1111111111 ’ || to_char(:new.DOB,’DD’));
rollback;
else
dbms_output.put_line (‘ nJOY’);
-- commit;
end if;
End;
Create or Replace Trigger vnTr
before insert on vNT
for each row
Begin
if(to_char(:new.DOB,’YY’)=’06’) then
dbms_output.put_line (‘ SORRY UR VALUE OF DATE SHOULD BE GREATER THAN ONE 1111111111 ’ || to_char(:new.DOB,’DD’));
rollback;
else
dbms_output.put_line (‘ nJOY’);
-- commit;
end if;
End;
Create or Replace Trigger vnTr1
before insert on vNT
for each row
Begin
if(to_char(:new.DOB,’DY’)=’SUN’) then
dbms_output.put_line (‘ SORRY UR VALUE OF DATE SHOULD BE GREATER THAN ONE 1111111111 ’ || to_char(:new.DOB,’DY’));
rollback;
else
dbms_output.put_line (‘ nJOY’);
-- commit;
end if;
End;
Create or Replace Trigger vnTr1
before insert or update or delete on vNT
for each row
Begin
dbms_output.put_line (‘ SORRY the table is TURNED TO HELL’);
-- rollback;
raise_application_error(-20222,’NOT ALLOWED’);
dbms_output.put_line (‘ nJOY’);
-- commit;
-- end if;
End;
Insert into vNT values (6,’tr’,’2-jan-2006’);
Insert into vNT values (7,’tr’,’17-oct-2005’);
Select * from vNT;
UPDATE vNT set nam=’adf’;
Delete from vNT;
