Scribd
Upload
4 views3 pages

PL/SQL Blocks: Procedures & Triggers Guide

The document contains multiple PL/SQL blocks demonstrating various programming constructs. It includes procedures for division with exception handling, looping and decision-making for even/odd identification and factorial calculation, user-defined exceptions with error raising, and a trigger for assigning grades based on student marks. Additionally, it features functions and procedures for checking voting eligibility based on age.

Uploaded by

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

PL/SQL Blocks: Procedures & Triggers Guide

The document contains multiple PL/SQL blocks demonstrating various programming constructs. It includes procedures for division with exception handling, looping and decision-making for even/odd identification and factorial calculation, user-defined exceptions with error raising, and a trigger for assigning grades based on student marks. Additionally, it features functions and procedures for checking voting eligibility based on age.

Uploaded by

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

1) Create a PL/SQL block for implementing declare, begin, and exception blocks.

CREATE OR REPLACE PROCEDURE divide_number(


num1 IN NUMBER,
num2 IN NUMBER
) AS
result NUMBER;
BEGIN
-- Perform division
result := num1 / num2;
DBMS_OUTPUT.PUT_LINE('The result is: ' || result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
END;
/

2) Create a PL/SQL block for implementing decision and looping statements.

SQL> CREATE OR REPLACE PROCEDURE P_loop(n IN NUMBER) IS


2 BEGIN
3 FOR i IN 1..n LOOP
4 IF MOD(i, 2) = 0 THEN
5 DBMS_OUTPUT.PUT_LINE(i || ' is Even');
6 ELSE
7 DBMS_OUTPUT.PUT_LINE(i || ' is Odd');
8 END IF;
9 END LOOP;
10 END;
11 /

SQL> CREATE OR REPLACE PROCEDURE factorial_of_number(n IN NUMBER) IS


2 factorial NUMBER := 1; -- To store the factorial result
3 BEGIN
4 -- Decision-making: Handle invalid input
5 IF n < 0 THEN
6 DBMS_OUTPUT.PUT_LINE('Invalid input: Factorial of a negative number is
not defined.');
7 ELSIF n = 0 THEN
8 DBMS_OUTPUT.PUT_LINE('The factorial of 0 is 1.');
9 ELSE
10 -- Loop to calculate factorial
11 FOR i IN 1..n LOOP
12 factorial := factorial * i;
13 END LOOP;
14 DBMS_OUTPUT.PUT_LINE('The factorial of ' || n || ' is: ' || factorial);
15 END IF;
16 END ;
17 /

3)Create a user-defined exception and raise, raise_application_error.


SQL> DECLARE
2 un [Link]%TYPE;
3 vid [Link]%TYPE;
4 login_exp EXCEPTION;
5 BEGIN
6 vid := &vid;
7 SELECT sname INTO un FROM student2 WHERE sid = vid;
8 IF un IS NULL THEN
9 RAISE login_exp;
10 ELSE
11 DBMS_OUTPUT.PUT_LINE('Login is Successful');
12 END IF;
13
14 EXCEPTION
15 WHEN NO_DATA_FOUND THEN
16 RAISE_APPLICATION_ERROR(-20001, 'No student found with the
given ID.');
17
18 WHEN login_exp THEN
19 RAISE_APPLICATION_ERROR(-20002, 'Login failed: Student name is
NULL.');
20 END;
21 /

CREATE OR REPLACE FUNCTION CHECK_ELIGIBILITYs(


AGE IN NUMBER
) RETURN VARCHAR2 IS
BEGIN
IF AGE >= 18 THEN
RETURN 'Eligible to vote';
ELSE
RETURN 'Not eligible to vote';
END IF;
END;
/

Function created.

CREATE OR REPLACE PROCEDURE VOTE_ELIGIBILITYs(


AGE IN NUMBER,
RESULT OUT VARCHAR2
) AS
BEGIN
RESULT := CHECK_ELIGIBILITYs(AGE);
END;
/

Procedure created.

SQL> set serveroutput on;

DECLARE
eligibility_result VARCHAR2(50);
BEGIN
VOTE_ELIGIBILITYs(20, eligibility_result);
DBMS_OUTPUT.PUT_LINE('Result: ' || eligibility_result);
END;
/

CREATE OR REPLACE TRIGGER trg_assign_grade BEFORE


INSERT OR UPDATE ON Student_Marks
FOR EACH ROW
BEGIN
IF :[Link] >= 90 THEN
:[Link] := 'A';
ELSIF :[Link] >= 80 THEN
:[Link] := 'B';
ELSIF :[Link] >= 70 THEN
:[Link] := 'C';
ELSIF :[Link] >= 60 THEN
:[Link] := 'D';
ELSIF :[Link] >= 50 THEN
:[Link] := 'E';
ELSE :[Link] := 'F';
END IF;
END;
/

You might also like