PL/SQL sample queries Part-VII

150. Create a package to store the following procedure for multiplication table,even-odd, function for factorial and function for palindrome?
CREATE OR REPLACE PACKAGE DATA
IS
PROCEDURE MULT(A NUMBER);
PROCEDURE EVEN_ODD(N NUMBER);
FUNCTION FACT(N NUMBER) RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(FACT,WNDS);
FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(PALEN,WNDS);
END;
/
CREATE OR REPLACE PACKAGE BODY DATA
IS
PROCEDURE MULT(A NUMBER)
IS
M NUMBER;
BEGIN
FOR I IN 1..10
LOOP
M:=A*I;
DBMS_OUTPUT.PUT_LINE(A||'*'||I||'='||M);
END LOOP;
END;
PROCEDURE EVEN_ODD(N NUMBER)
IS
BEGIN
IF MOD(N,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(N||' IS EVEN NUMBER');
ELSE
DBMS_OUTPUT.PUT_LINE(N||' IS NOT EVEN NUMBER');
END IF;
END;
FUNCTION FACT(N NUMBER) RETURN NUMBER
IS
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
F:=F*I;
END LOOP;
RETURN F;
END;
FUNCTION PALEN(SRT VARCHAR2) RETURN VARCHAR2
IS
S CHAR;
V VARCHAR2(50);
BEGIN
FOR I IN REVERSE 1..LENGTH(SRT)
LOOP
S:=SUBSTR(SRT,I,1);
V:=V||S;
END LOOP;
IF V=SRT THEN
RETURN 'PALINDROME';
ELSE
RETURN 'NOT PALINDROME';
END IF;
END;
END;
/
151.Write a database trigger halt the transaction on Sunday on EMP table
CREATE OR REPLACE TRIGGER SUN_TRI
AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
DY VARCHAR2(200);
BEGIN
DY:=TO_CHAR(SYSDATE,'DY');
IF DY='SUN' THEN
RAISE_APPLICATION_ERROR(-20005,'TODAY IS SUNDAY TRANSACTION NOT ALLOWED TODAY');
END IF;
END;
/
152.Write a database trigger halt the transaction of USER SCOTT on table EMP
CREATE OR REPLACE TRIGGER SCOTT_TRI
BEFORE INSERT OR UPDATE OR DELETE ON EMP
BEGIN
IF USER = 'SCOTT' THEN
RAISE_APPLICATION_ERROR(-20006,'TRANSACTION NOT ALLOWED FOR SCOTT');
END IF;
END;
/
153.Write a database trigger halt the transaction between the the time 6pm to 10am on table emp
CREATE OR REPLACE TRIGGER OVER_TIME_TRI
BEFORE INSERT OR DELETE OR UPDATE ON EMP
DECLARE
T NUMBER;
BEGIN
T:=TO_CHAR(SYSDATE,'HH24');
IF T NOT BETWEEN 10 AND 18 THEN
RAISE_APPLICATION_ERROR(-20007,'TIME ALREADY OVER.....TRANSACTION NOT ALLOWED NOW');
END IF;
END;
154.Write a database trigger to halt the transaction for the employee SALESMAN and
PRESIDENT
CREATE OR REPLACE TRIGGER SALES_PRI
BEFORE INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
WHEN (OLD.JOB IN ('SALESMAN','PRESIDENT') OR
NEW.JOB IN ('SALESMAN','PRESIDENT'))
BEGIN
RAISE_APPLICATION_ERROR(-20008,'TRANSACTION NOT ALLOWED FOR SALESMAN AND PRESIDENT....');
END;
/
155.Write a database trigger stroe the username ,type of transaction ,date of transaction and time of transaction of table emp into the table EMP_LOG
CREATE OR REPLACE TRIGGER TRANS_TYPE
AFTER INSERT OR UPDATE OR DELETE ON EMP
DECLARE
V VARCHAR2(50);
BEGIN
IF INSERTING THEN
V:='I';
ELSIF UPDATING THEN
V:='U';
ELSE
V:='D';
END IF;
INSERT INTO EMP_LOG VALUES (USER,V,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS'));
END;
/
156.Write a database trigger store the deleted data of EMP table in EMPDEL table
CREATE OR REPLACE TRIGGER DEL_TRI
BEFORE DELETE ON EMP
FOR EACH ROW
BEGIN
INSERT INTO EMPDEL
VALUES (:OLD.EMPNO,:OLD.ENAME,:OLD.JOB,:OLD.MGR,:OLD.HIREDATE,:OLD.SAL,:OLD.COMM,
:OLD.DEPTNO,SYSDATE,TO_CHAR(SYSDATE,'HH:MI:SS));
END;
/
157.Write a database trigger display the message when the inserting hiredate is greater than system date
CREATE OR REPLACE TRIGGER HIREDATE_OVER
AFTER INSERT ON EMP
FOR EACH ROW
BEGIN
IF :NEW.HIREDATE > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20009,'INVALID HIREDATE.....');
END IF;
END;
/
158.Write a database trigger halt the transaction of EMP table if the deptno is does not exist in the dept table
CREATE OR REPLACE TRIGGER DEPT_NO
BEFORE INSERT OR UPDATE OR DELETE ON EMP
FOR EACH ROW
DECLARE
DNO NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO DNO FROM DEPT WHERE DEPTNO=:NEW.DEPTNO;
DBMS_OUTPUT.PUT_LINE(DNO);
IF DNO=0 THEN
RAISE_APPLICATION_ERROR(-20009,'DEPTNO NOT EXIST IN DEPT TABLE....');
END IF;
END;
/
159.Write a database trigger add Rs 500 if the inserting salary is less than Rs 1000
CREATE OR REPLACE TRIGGER SAL_ADD
BEFORE INSERT ON EMP
FOR EACH ROW
BEGIN
IF :NEW.SAL <= 1000 THEN
:NEW.SAL:=:NEW.SAL+500;
END IF;
END;
/
160.Write a database trigger give the appropriate message if the record exceed more than 100 on EMP table
CREATE OR REPLACE TRIGGER EMP_OVER_REC
AFTER INSERT ON EMP
DECLARE
R NUMBER;
BEGIN
SELECT COUNT(*) INTO R FROM EMP;
IF R>=100 THEN
RAISE_APPLICATION_ERROR(-20009,'100 RECORD ALLOWED IN EMP TABLE.....');
END IF;
END;
/
161.Write a program to month and year and display the Calendar of that month.
DECLARE
D NUMBER:=1;
M VARCHAR2(10):='&MONTH';
Y NUMBER:=&YEAR;
C CHAR(20);
V VARCHAR2(500);
N NUMBER;
BEGIN
N:=TO_CHAR(LAST_DAY(D||'-'||M||'-'||Y),'DD');
C:= TO_CHAR(TO_DATE(D||'-'||M||'-'||Y),'DY');
dbms_output.put_line('*********************************');
dbms_output.put_line('* '||M||'-'||Y||' *');
dbms_output.put_line('*SUN MON TUE WED THU FRI SAT *');
dbms_output.put_line('**********************************');
IF C='MON' THEN
V:=' ';
ELSIF C='TUE' THEN
V:=' ';
ELSIF C='WED' THEN
V:=' ';
ELSIF C='THU' THEN
V:=' ';
ELSIF C='FRI' THEN
V:=' ';
ELSIF C='SAT' THEN
V:=' ';
END IF;
FOR I IN 1..N
LOOP
V:=V||LPAD(I,4);
IF LENGTH(V)=28 THEN
dbms_output.put_line(LPAD(V,29,'*')||' *');
V:=NULL;
END IF;
END LOOP;
dbms_output.put_line('*'||RPAD(V,29)||'*');
END;
/