[go: up one dir, main page]
Scribd
Upload
82 views10 pages

Dbms Lab 7 19IT030

The document discusses PL/SQL blocks and various composite data types. It provides examples of PL/SQL programs that demonstrate working with data types like numbers, variables, and cursors. The examples include programs to add two numbers, find a factorial, check if a number is odd or even, and retrieve data from database tables using cursors. The document aims to help practice creating PL/SQL blocks and using different composite data types.

Uploaded by

GOPINATH K S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
82 views10 pages

Dbms Lab 7 19IT030

The document discusses PL/SQL blocks and various composite data types. It provides examples of PL/SQL programs that demonstrate working with data types like numbers, variables, and cursors. The examples include programs to add two numbers, find a factorial, check if a number is odd or even, and retrieve data from database tables using cursors. The document aims to help practice creating PL/SQL blocks and using different composite data types.

Uploaded by

GOPINATH K S
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 10

Ex.

No: 7
PL/SQL BLOCK CREATION AND USAGE OF
VARIOUS COMPOSITE DATA TYPES
Aim:

To practice with PL/SQL block creation and various composite data types

Description:

PL/SQL is a combination of SQL along with the procedural features of programming languages. It

was developed by Oracle Corporation to enhance the capabilities of SQL.

Syntax of a basic loop in PL/SQL programming language is:

LOOP

//Sequence of statements;//

END LOOP;

Procedure:

1. Execute any 5 sample PL/SQL programs

2. Execute any 5 PL/SQL program for your application

Example:

1. Adding two Numbers


PL/SQL program to add two numbers or integers and fetching the result into a third variable. This

program takes two inputs one for each variable and adds the result to a third variable and prints it.

Declare

Var1 integer;

Var2 integer;

Var3 integer;

Begin

Var1:=&var1;

Var2:=&var2;

Var3:=var1+var2;

Dbms_output.put_line(var3);

End;

/
2. PL/SQL Program to Find Factorial of a Number:

DECLARE

n number;

fac number:=1;

i number;

BEGIN

n:=&n;

FOR i in 1..n

LOOP

fac:=fac*i;

END LOOP;

dbms_output.put_line('factorial='||fac);

end;

/
3. PL/SQL Program for Armstrong Number:

DECLARE

n INT := 407;

s INT := 0;

r INT;

m INT;

BEGIN

m := n;

WHILE n > 0

LOOP

r := mod(n, 10);

s := s + power(r, 3);

n := trunc(n/10);

END LOOP;

IF m = s

THEN

dbms_output.put_line('Armstrong number');

ELSE

dbms_output.put_line('Not armstrong number');

END IF;
END;

4. PL/SQL Program to Check Number is Odd or Even:

DECLARE

n INT := &n;

BEGIN

IF mod(n, 2) = 0

THEN

dbms_output.put_line('Number is even');

ELSE

dbms_output.put_line('Number is odd');

END IF;

END;

/
5. PL/SQL program for finding the maximum of two values:

DECLARE

n1 INT;

n2 INT;

result INT;

BEGIN

n1 := &input1;

n2 := &input2;

IF n1 > n2

THEN

dbms_output.put_line(n1 || ' is the maximum');

ELSIF n1 < n2

THEN

dbms_output.put_line(n2 || ' is the maximum');

ELSE

dbms_output.put_line(n1 || ' is the maximum');

END IF;

END;

/
PL/SQL commands for Election Database Management System

1. PL/SQL program to find the constituency with minimum population from the Constituency
table:

DECLARE
CURSOR cursor1 IS
SELECT population FROM constituency;
pop constituency.population%type;
minimum INTEGER := 1000;
BEGIN
FOR POP IN cursor1
LOOP
IF POP.POPULATION < minimum
THEN
minimum := POP.POPULATION;
END IF;
END LOOP;
dbms_output.put_line('The minimum population of all the constituencies in the table is ' ||
minimum || ' thousands');
END;
/
2. PL/SQL program to find the constituency with maximum population from the Constituency
table:

DECLARE
CURSOR cursor1 IS
SELECT population FROM constituency;
pop constituency.population%type;
maximum INTEGER := 100;
BEGIN
FOR POP IN cursor1
LOOP
IF POP.POPULATION > maximum
THEN
maximum := POP.POPULATION;
END IF;
END LOOP;
dbms_output.put_line('The maximum population of all the constituencies in the table is ' ||
maximum || ' thousands');
END;
/
3. PL/SQL program to find the average population of all the constituencies from the Constituency
table:

DECLARE
CURSOR cursor1 IS
SELECT population FROM constituency;
pop constituency.population%type;
total int := 0.00;
average int := 0.00;
counter int := 0;
BEGIN
FOR pop IN cursor1
LOOP
total := total + POP.population;
counter := counter + 1;
END LOOP;
average := total/counter;
dbms_output.put_line( 'The average population of all the constituencies in the table is ' || average
|| ' thousands');
END;
/

4. PL/SQL program to find the Population of STATE of all the constituencies from the
Constituency table:

DECLARE
CURSOR cursor1 IS
SELECT population FROM constituency;
pop constituency.population%type;
total int:= 0;
BEGIN
FOR pop IN cursor1
LOOP
total := total + POP.population;
END LOOP;
dbms_output.put_line('The total population of all the constituencies in the table is ' || total || '
thousands');
END;
/

5. PL/SQL program to display all the records in the candidate table:

DECLARE
Candidate_name candidate.name%type;
Candidate_id candidate.cand_id%type;
CURSOR cursor1 IS
SELECT name,cand_id FROM candidate;
BEGIN
OPEN cursor1;
LOOP
FETCH cursor1 INTO candidate_name,candidate_id;
EXIT WHEN cursor1%notfound;
dbms_output.put_line(candidate_name|| ' ' || candidate_id);
END LOOP;
CLOSE cursor1;
END;
/
RESULT:

Thus, the PL/SQL block has been created and executed successfully.

You might also like