Here you will find the list of 9 simplest plsql programs fully exploring the control structures. This tutorial covers IF, ELSE, ELSIF, LOOP, EXIT, EXIT WHEN, WHILE, FOR, GOTO and NULL statements. So have a look...
1. Program to insert values in a table student which has two fields student_id and name.
DECLARE
max_id number;
BEGIN
SELECT MAX(student_id) INTO max_id FROM student;
INSERT INTO student (student_id, name) VALUES (max_id + 1, ‘Harry’);
DBMS_OUTPUT.PUT_LINE(’Record Inserted’);
END;
max_id number;
BEGIN
SELECT MAX(student_id) INTO max_id FROM student;
INSERT INTO student (student_id, name) VALUES (max_id + 1, ‘Harry’);
DBMS_OUTPUT.PUT_LINE(’Record Inserted’);
END;
2. Program illustrating the use of IF, ELSE and ELSIF
If Percentage >=80 –> Grade A
If Percentage >=60 –> Grade B
If Percentage >=45 –> Grade C
If Percentage < 45 --> Fail
DECLARE
n number;
BEGIN
–Enter percentage between 1 and 100
n:=&n;
IF(n>=1 AND n< =100) THEN
IF(n>=80) THEN
DBMS_OUTPUT.PUT_LINE(’Grade A’);
ELSIF(n>=60 AND n<80)
DBMS_OUTPUT.PUT_LINE(’Grade B’);
ELSIF(n>=45 AND n<60)
DBMS_OUTPUT.PUT_LINE(’Grade C’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Fail’);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(’Percentage must be between 0 and 100′);
END IF;
END;
n number;
BEGIN
–Enter percentage between 1 and 100
n:=&n;
IF(n>=1 AND n< =100) THEN
IF(n>=80) THEN
DBMS_OUTPUT.PUT_LINE(’Grade A’);
ELSIF(n>=60 AND n<80)
DBMS_OUTPUT.PUT_LINE(’Grade B’);
ELSIF(n>=45 AND n<60)
DBMS_OUTPUT.PUT_LINE(’Grade C’);
ELSE
DBMS_OUTPUT.PUT_LINE(’Fail’);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(’Percentage must be between 0 and 100′);
END IF;
END;
3. Program illustrating the use of LOOP with EXIT statement
DECLARE
n number:=0;
BEGIN
LOOP
n:=n+1;
IF(n>3) THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
n number:=0;
BEGIN
LOOP
n:=n+1;
IF(n>3) THEN
EXIT;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
output: 3
4. Program illustrating the use of LOOP with EXIT WHEN statement
DECLARE
n number:=0;
BEGIN
LOOP
n:=n+1;
EXIT WHEN n>3;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
n number:=0;
BEGIN
LOOP
n:=n+1;
EXIT WHEN n>3;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
output: 3
5. Program illustrating the use of WHILE LOOP statement
DECLARE
n number:=0;
BEGIN
WHILE n<3
LOOP
n:=n+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
n number:=0;
BEGIN
WHILE n<3
LOOP
n:=n+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
output: 3
6. Program illustrating the use of FOR LOOP statement
DECLARE
n number:=0;
BEGIN
FOR i IN 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
FOR i IN 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
output: 6
7. Program illustrating the use of FOR LOOP REVERSE statement
DECLARE
n number:=0;
BEGIN
FOR i IN REVERSE 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
n number:=0;
BEGIN
FOR i IN REVERSE 1..3
LOOP
n:=n+i;
END LOOP;
DBMS_OUTPUT.PUT_LINE(n);
END;
output: 6
8. Program illustrating the use of GOTO statement
BEGIN
DBMS_OUTPUT.PUT_LINE(’First Line’);
GOTO third;
DBMS_OUTPUT.PUT_LINE(’Second Line’);
< >
DBMS_OUTPUT.PUT_LINE(’Third Line’);
END;
DBMS_OUTPUT.PUT_LINE(’First Line’);
GOTO third;
DBMS_OUTPUT.PUT_LINE(’Second Line’);
< >
DBMS_OUTPUT.PUT_LINE(’Third Line’);
END;
output:
First Line
Third LIne
First Line
Third LIne
9. Program illustrating the use of NULL statement
DECLARE
n number:= &n;
BEGIN
IF n MOD 2 = 0 THEN
DBMS_OUTPUT.PUT_LINE(’Number is Even’);
ELSE
NULL;
END IF;
END;
n number:= &n;
BEGIN
IF n MOD 2 = 0 THEN
DBMS_OUTPUT.PUT_LINE(’Number is Even’);
ELSE
NULL;
END IF;
END;
output: If entered no. is even then it will display the message otherwise no message will be displayed.
No comments:
Post a Comment