PL/SQL stands for “Procedural Language extensions to the Structured Query Language”. SQL is a popular language for both querying and updating data in the relational database management systems (RDBMS).
PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements.
SQL:SQL is a single query that is used to perform DML and DDL operations.
PL/SQL:PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.
Program 1:
Write PL/SQL block to display a String....My First PL/SQL Program
Step-1 : Open Notepad and then type the pl/sql program .
set serveroutput on
cl scr
declare
n1 varchar2(30):='My First PL/SQL Program';
begin
dbms_output.put_line(n1);
end;
/
cl scr
declare
n1 varchar2(30):='My First PL/SQL Program';
begin
dbms_output.put_line(n1);
end;
/
Step-2 : save the notepad file with an extension .sql
Example : "ram.sql"
Example : "ram.sql"
Step-3 : Execute the file as follows.
@d:\ram.sql;
@d:\ram.sql;
Program 2:
Write PL/SQL block to add 3 numbers(Assign values in Program)
set serveroutput on
cl scr
declare
n1 number(3):=12;
n2 number(3):=13;
n3 number(3):=14;
t number(5);
begin
dbms_output.put_line('---Assigned values in the Progarm---');
t:=n1+n2+n3;
dbms_output.put_line('Sum Of Numbers:'||t);
end;
/
Program 3:
Write PL/SQL block to add 3 numbers(Access values from user)
set serveroutput on
declare
n1 number(3):=&x;
n2 number(3):=&y;
n3 number(3):=&z;
t number(5);
begin
dbms_output.put_line('---Accessed values from User---');
t:=n1+n2+n3;
dbms_output.put_line('Sum Of Numbers:'||t);
end;
/
Program 4:
Write PL/SQL block to compare two numbers(Using If...else construct)
set serveroutput on
declare
n1 number(3):=&x;
n2 number(3):=&y;
begin
if(n1>n2) then
dbms_output.put_line(n1||' is greater');
else
dbms_output.put_line(n2||' is greater');
end if;
end;
/
Program 4.1:
Write PL/SQL block to compare three numbers(Using If...elsif construct)
set serveroutput on
declare
n1 number(3):=&x;
n2 number(3):=&y;
n3 number(3):=&z;
begin
if(n1>n2 and n1>n3) then
dbms_output.put_line(n1||' is largest');
elsif(n2>n1 and n2>n3) then
dbms_output.put_line(n2||' is largest');
else
dbms_output.put_line(n3||' is largest');
end if;
end;
/
Program 5
Write PL/SQL block to generate numbers from 2 to 10(Using while)
set serveroutput on
declare
x number(3):=2;
begin
dbms_output.put_line('---Using While---');
while x<11
loop
dbms_output.put_line(x);
x:=x+1;
end loop;
end;
/
Program 6:
Write PL/SQL block to demonstrate CASE...END CASE
set serveroutput on
declare
n1 number:=&x;
begin
case n1
when 1 then dbms_output.put_line('One');
when 2 then dbms_output.put_line('Two');
when 3 then dbms_output.put_line('Three');
else
dbms_output.put_line('Invalid Input');
end case;
end;
/
Program 7:
Write PL/SQL block to generate numbers from 2 to 10(Using For)
set serveroutput on
declare
x number(3);
begin
dbms_output.put_line('---Using For---');
for x in 2..10
loop
dbms_output.put_line(x);
end loop;
end;
/
Program 8:
Write PL/SQL block to generate numbers from 10 to 2(Using For)
set serveroutput on
declare
x number(3);
begin
dbms_output.put_line('---Using For---');
for x in reverse 2..10
loop
dbms_output.put_line(x);
end loop;
end;
/
Program 9:
Write PL/SQL block to display multiplication table 19(Using While)
set serveroutput on
declare
n number(3):=1;
begin
dbms_output.put_line('---Multiplication Table Using While---');
while n<21
loop
dbms_output.put_line(19||' X '||n||' = '||n*19);
n:=n+1;
end loop;
end;
/
Program 10:
Write PL/SQL block to display multiplication table 19(Using For)
set serveroutput on
declare
n number(3);
begin
dbms_output.put_line('---Multiplication Table Using For---');
for n in 1..20
loop
dbms_output.put_line(19||' X '||n||' = '||n*19);
end loop;
end;
/
Program 11:
Write a PL/SQL script to check given number is an Armstrong number or not.
set serveroutput on
declare
set serveroutput on
declare
n number(9):=1634;
r number(9);
t number(9):=0;
i number(9);
begin
i:=n;
while n>0
loop
r:=mod(n,10);
t:=t+power(r,4);
n:=floor(n/10);
end loop;
if(t=i) then
dbms_output.put_line('Amstrong');
else
dbms_output.put_line('Not Amstrong');
end if;
end;
/
Program 12:
Write PL/SQL block to display numbers in the given range and find thir sum.
set serveroutput on
declare
n1 number(3):=&x;
n2 number(3):=&y;
x number(3);
t number(3):=0;
begin
for x in n1..n2
loop
dbms_output.put_line(x);
t:=t+x;
end loop;
dbms_output.put_line('Sum of numbers in the given range: '||t);
end;
/
Program 13:
Write a PL/SQL script to find the sum of digits in the given number.
set serveroutput on
declare
n number(9):=&x;
r number(9);
t number(9):=0;
begin
while n!=0
loop
r:=mod(n,10);
t:=t+r;
n:=floor(n/10);
end loop;
dbms_output.put_line('Sum Of Digits In The Given Number: '||t);
end;
/
No comments:
Post a Comment