Easy Learning... (Web Development,.NET Technology,Oracle,Silverlight,REST,SQL,Oracle,MySQL,Data Science, Python and many more ...)
Do as directed (any two)
Train(Train-no,Train-name)
Station(Station-code,Station-name)
Halt(Halt-code,Seq-no,Train-no,Station-no,Time-in,Time-out)
(Halt-code,Seq-no are composite key)
1. Create a procedure which return Time-in and Time-out for given
Train-no and Station-name.
CREATE OR REPLACE TRIGGER PKMAX
BEFORE INSERT ON emp
FOR EACH ROW
DECLARE
CNT NUMBER;
veno emp.eno%TYPE;
BEGIN
SELECT 'E' || LPAD(MAX(TO_NUMBER(SUBSTR(eno,2))),5,'0') INTO veno
FROM emp;
:NEW.eno := veno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:NEW.eno := 'E00001';
END;
/
create or replace trigger t_old
before insert on emp
for each row
declare
veno NUMBER(4);
begin
SELECT seq1.NEXTVAL INTO veno FROM DUAL;
:new.eno:=’E’|| lpad(veno,5,0);
end;
/
CREATE SEQUENCE seq1 start with 1 increment by 1;
/
CREATE OR REPLACE TRIGGER Weekend
BEFORE INSERT OR UPDATE OR DELETE ON emp
for each row
WHEN (TO_CHAR(SYSDATE,'day') IN('SATURDAY','SUNDAY'))
DECLARE
BEGIN
RAISE_APPLICATION_ERROR(-20001,'Enjoy your Weekend!! Do not do work!');
END;
/
CREATE OR REPLACE TRIGGER Working_hrs
BEFORE INSERT OR UPDATE OR DELETE ON emp
DECLARE
WORKING_HOURS NUMBER(2);
BEGIN
WORKING_HOURS := TO_CHAR(SYSDATE,’HH24′);
IF WORKING_HOURS < 9 AND WORKING_HOURS > 17 THEN
RAISE_APPLICATION_ERROR(-20001,'Its not official working hours! Can not do operations on emp');
END IF;
END;
/
CREATE OR REPLACE TRIGGER minSalary
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
IF (:new.sal < 1000) THEN
RAISE_APPLICATION_ERROR (-20004, ‘Minimum Salary should be 1000’);
END IF;
END;
/
SET Serveroutput ON
CREATE OR REPLACE TRIGGER display_salary_changes
BEFORE DELETE OR INSERT OR UPDATE ON EMP
FOR EACH ROW
DECLARE
sal_diff number(10,2);
BEGIN
sal_diff := :NEW.esalary - :OLD.esalary;
dbms_output.put_line('Old salary: ' || :OLD.esalary);
dbms_output.put_line('New salary: ' || :NEW.esalary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/
SET Serveroutput ON
CREATE OR REPLACE TRIGGER log_salary
AFTER UPDATE ON emp
FOR EACH ROW
BEGIN
INSERT INTO Emp_log(Emp_id, Log_date, New_salary, Action)
VALUES (:NEW.Eno, SYSDATE, :NEW.Esalary, 'New Salary');
END;6
/
DROP TABLE Emp_log;
CREATE TABLE Emp_log (
Emp_id CHAR(6),
Log_date DATE,
New_salary NUMBER(10,2),
Action VARCHAR2(20));
SET Serveroutput ON
CREATE OR REPLACE TRIGGER display_salary
BEFORE UPDATE ON EMP
FOR EACH ROW
BEGIN
dbms_output.put_line('Old salary: ' || :OLD.esalary);
END;
/
connect system/password;
CREATE USER bca
IDENTIFIED BY bca
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
-- 2 Roles for DEVELOPER
GRANT CONNECT TO bca CONTAINER=ALL;
GRANT RESOURCE TO bca CONTAINER=ALL;
ALTER USER bca DEFAULT ROLE NONE;
-- 3 System Privileges for DEVELOPER
GRANT CREATE SESSION TO bca;
GRANT CREATE TABLE TO bca;
GRANT CREATE TRIGGER TO bca;
GRANT CREATE SEQUENCE TO bca;
-- 2 Tablespace Quotas for DEVELOPER
ALTER USER bca QUOTA UNLIMITED ON SYSAUX;
ALTER USER bca QUOTA UNLIMITED ON USERS;
SET Serveroutput ON
DECLARE
veno emp.eno%type;
vename emp.ename%type;
vesalary emp.esalary%type;
vedoj emp.edoj%type;
vedno emp.edno%type;
veno1 emp.eno%type;
vename1 emp.ename%type;
vesalary1 emp.esalary%type;
BEGIN
veno := '&veno';
vename := '&vename';
vesalary := &vesalary;
vedoj := '&vedoj';
vedno := '&vedno';
veno1 := '&veno1';
vename1 := '&vename1';
vesalary1 := &vesalary1;
emp_package.addemp(veno1,vename1,vesalary1);
emp_package.addemp(veno,vename,vesalary,vedoj,vedno);
END;
/
CREATE OR REPLACE PACKAGE BODY emp_package AS
PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary emp.esalary%type)
IS
BEGIN
INSERT INTO emp (eno,ename,esalary)
VALUES(veno, vename, vesalary);
END;
PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary emp.esalary%type, vedoj emp.edoj%type, vedno emp.edno%type)
IS
BEGIN
INSERT INTO emp (eno,ename,esalary,edoj,edno)
VALUES(veno, vename, vesalary, vedoj, vedno);
END;
END emp_package;
/
CREATE OR REPLACE PACKAGE emp_package AS
PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary emp.esalary%type);
PROCEDURE addemp(veno emp.eno%type, vename emp.ename%type, vesalary emp.esalary%type, vedoj emp.edoj%type, vedno emp.edno%type);
END emp_package;
/
SET Serveroutput ON
DECLARE
veno emp.eno%type;
BEGIN
veno := '&veno';
DBMS_OUTPUT.PUT_LINE(emp_name.find_name(veno));
END;
/
CREATE OR REPLACE PACKAGE BODY emp_name AS
FUNCTION find_name(veno emp.eno%TYPE) return varchar IS
vename emp.ename%TYPE;
BEGIN
SELECT ename INTO vename
FROM emp
WHERE eno = veno;
RETURN vename;
END;
END emp_name;
/
CREATE OR REPLACE PACKAGE emp_name AS
FUNCTION find_name(veno emp.eno%type)return varchar;
END emp_name;
/
SET Serveroutput ON
DECLARE
veno emp.eno%type;
BEGIN
veno := '&veno';
emp_sal.find_sal(veno);
END;
/
CREATE OR REPLACE PACKAGE BODY emp_sal AS
PROCEDURE find_sal(veno emp.eno%TYPE) IS
vesalary emp.esalary%TYPE;
BEGIN
SELECT esalary INTO vesalary
FROM emp
WHERE eno = veno;
dbms_output.put_line('Salary: '|| vesalary);
END;
END emp_sal;
/
CREATE OR REPLACE PACKAGE emp_sal AS
PROCEDURE find_sal(veno emp.eno%type);
END emp_sal;
/