PL/SQL PROGRAMS:
Q)WRITE A PROGRAM TO DISPLAY WHETHER A>B OR B>A BY GIVING INPUT TWO NUMBERS A AND B?
A)DECLARE
a NUMBER := &a;
b NUMBER := &b;
BEGIN
IF (a > b)
THEN
DBMS_OUTPUT.put_line ('A IS GREATER THAN B');
ELSE
DBMS_OUTPUT.put_line ('B IS GREATER THAN A');
END IF;
END;
*************************************************************************************
Q)CREATE A PROCEDURE TO INSERT AN EMPLOYEE DETAILS INTO EXISTING TABLE ?
A) CREATE OR REPLACE PROCEDURE xx_insert_employee (
p_empno IN emp.empno%TYPE
,p_ename IN emp.ename%TYPE
,p_job IN emp.job%TYPE
,p_mgr IN emp.mgr%TYPE
,p_hiredate IN emp.hiredate%TYPE
,p_sal IN emp.sal%TYPE
,p_comm IN emp.comm%TYPE
,p_deptno IN emp.deptno%TYPE
)
IS
employee_already_exist EXCEPTION;
L_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO L_COUNT FROM emp
WHERE EMPNO = P_EMPNO;
IF L_COUNT>0
THEN
RAISE employee_already_exist;
END IF;
INSERT INTO emp
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
VALUES (
p_empno
,p_ename
,p_job
,p_mgr
,p_hiredate
,p_sal
,p_comm
,p_deptno
);
COMMIT;
EXCEPTION
WHEN employee_already_exist
THEN
DBMS_OUTPUT.put_line ('Employee number already exist in EMP table.');
END xx_insert_employee;
***********************************************************************************
Q)WRITE A PROGRAM TO DISPLAY WHETHER A>B OR B>A BY GIVING INPUT TWO NUMBERS A AND B?
A)DECLARE
a NUMBER := &a;
b NUMBER := &b;
BEGIN
IF (a > b)
THEN
DBMS_OUTPUT.put_line ('A IS GREATER THAN B');
ELSE
DBMS_OUTPUT.put_line ('B IS GREATER THAN A');
END IF;
END;
*************************************************************************************
Q)CREATE A PROCEDURE TO INSERT AN EMPLOYEE DETAILS INTO EXISTING TABLE ?
A) CREATE OR REPLACE PROCEDURE xx_insert_employee (
p_empno IN emp.empno%TYPE
,p_ename IN emp.ename%TYPE
,p_job IN emp.job%TYPE
,p_mgr IN emp.mgr%TYPE
,p_hiredate IN emp.hiredate%TYPE
,p_sal IN emp.sal%TYPE
,p_comm IN emp.comm%TYPE
,p_deptno IN emp.deptno%TYPE
)
IS
employee_already_exist EXCEPTION;
L_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO L_COUNT FROM emp
WHERE EMPNO = P_EMPNO;
IF L_COUNT>0
THEN
RAISE employee_already_exist;
END IF;
INSERT INTO emp
(
empno
,ename
,job
,mgr
,hiredate
,sal
,comm
,deptno
)
VALUES (
p_empno
,p_ename
,p_job
,p_mgr
,p_hiredate
,p_sal
,p_comm
,p_deptno
);
COMMIT;
EXCEPTION
WHEN employee_already_exist
THEN
DBMS_OUTPUT.put_line ('Employee number already exist in EMP table.');
END xx_insert_employee;
***********************************************************************************
No comments:
Post a Comment