DBMS Lab Cycle 7 (Procedures & Functions)

--> Write a procedure to find whether a given number is odd or even.

CREATE OR REPLACE PROCEDURE oddoreven(num IN NUMBER) IS
BEGIN
IF(num != 0) THEN
IF MOD(num,2) = 0 THEN
      DBMS_OUTPUT.PUT_LINE(num ||' IS EVEN');
ELSE
      DBMS_OUTPUT.PUT_LINE(num ||' IS ODD');
END IF;
ELSE
      DBMS_OUTPUT.PUT_LINE('ZERO IS NEITHER PRIME NOR ODD');
END IF;
END;
/


SET SERVEROUTPUT ON
DECLARE
res NUMBER;
num NUMBER;
BEGIN
num:=&enter_the_number_to_be_checked
oddoreven(num);
END;

---

--> Develop Procedures to Perform Banking Operations. Create a table bank with attributes accno, name ,balance and insert appropriate values. Create procedures Withdrawal and Deposit respective operations with respect to the table and execute them .

CREATE TABLE bank(
accno NUMBER,
name VARCHAR2(60),
balance NUMBER(9,2),
PRIMARY KEY(accno)
);

INSERT INTO BANK (ACCNO, NAME, BALANCE) VALUES ('20175020', 'classic 350', '75000')
INSERT INTO BANK (ACCNO, NAME, BALANCE) VALUES ('20176946', 'himalayan', '35000')
INSERT INTO BANK (ACCNO, NAME, BALANCE) VALUES ('20170099', 'thunder bird', '45000')
INSERT INTO BANK (ACCNO, NAME, BALANCE) VALUES ('20170388', 'continental gt', '50000')


CREATE OR REPLACE PROCEDURE withdrawal(withdrwal_amt IN NUMBER,accno1 IN NUMBER) IS
BEGIN
UPDATE bank SET balance = balance -  withdrwal_amt WHERE accno = accno1;
DBMS_OUTPUT.PUT_LINE(withdrwal_amt || 'debited from account ' || accno1);
END;

CREATE OR REPLACE PROCEDURE deposit(deposit_amt IN NUMBER,accno1 IN NUMBER) IS
BEGIN
UPDATE bank SET balance = balance -  deposit_amt WHERE accno = accno1;
DBMS_OUTPUT.PUT_LINE(deposit_amt || 'credited from account ' || accno1);
END;

SET SERVEROUTPUT ON
DECLARE
action NUMBER;
acc_no NUMBER;
amnt NUMBER;
BEGIN
action=&Enter_1_for_depositing_2_for_withdrawal;
IF action = 1 THEN
    acc_no:=&Enter_the_account_to_credited;
    amnt:=&Enter_the_amount_to_credited;
    deposit(acc_no,amnt);
ELSEIF action = 2 THEN
    acc_no:=&Enter_the_account_to_debited;
    amnt:=&Enter_the_amount_to_debited;
    withdrawal(acc_no,amnt);
ELSE
EXIT;
END IF;
END;

---

--> To write a PL/SQL program using functions to find the factorial of a given number.

CREATE OR REPLACE FUNCTION factorial(num IN NUMBER) RETURN NUMBER IS
fact NUMBER;
BEGIN
IF num > 0 THEN
FACT:=1;
FOR i IN 1 .. num LOOP
fact := fact * i;
END LOOP;
 RETURN fact;
ELSE
RETURN 1;
END IF;
END;
/

SET SERVEROUTPUT ON
DECLARE
res NUMBER;
BEGIN
res := FACTORIAL(5);
DBMS_OUTPUT.PUT_LINE(res);
END;

---

--> Write a PL/SQL function CheckDiv that takes two numbers as arguments and returns the values 1 if the first argument passed to it is divisible by the second argument, else will return the value 0.


 CREATE OR REPLACE FUNCTION dividecheck(num1 IN NUMBER,num2 in NUMBER) RETURN NUMBER IS
BEGIN
IF MOD(num1,num2) = 0 THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
/

SET SERVEROUTPUT ON
DECLARE
res NUMBER;
num1 number;
num2 number;
BEGIN
num1:=&enter_the_first_number;
num2 := &enter_the_second_number;
res := dividecheck(num1,num2);
if res = 1 then
DBMS_OUTPUT.PUT_LINE(num1 || ' is divisible by '|| num2);
else
DBMS_OUTPUT.PUT_LINE(num1 || ' is not divisible by '|| num2);
end if;
END;

---