RDBMS Do As Directed

 

[1]

Create or Replace Procedure calsalary(PEmp_no in varchar) As

VBasic number(10,2);

VDa number(10,2);

VHra number(10,2);

VTax number(10,2);

RDBMS Do As Directed

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.

ASP.NET Website using VB.NET

 click here to download

RDBMS PLSQL Program 50 program to generate value of primary key column using MAX function automatically

 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;

/

RDBMS PLSQL Program 49 program to generate value of primary key column using sequence automatically

 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;

/

RDBMS PLSQL Program 48 script to create sequence

 CREATE SEQUENCE seq1 start with 1 increment by 1; 

/

RDBMS PLSQL Program 47 program to generate before insert,update and delete row trigger on emp table which will not allow changes in data on weekends using RAISE_APPLICATION_ERROR() function

 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;

/

RDBMS PLSQL Program 46 program to generate before insert,update and delete row trigger on emp table which will not allow changes in data before and after office hours using RAISE_APPLICATION_ERROR() function

 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;

/

RDBMS PLSQL Program 45 program to generate before insert row trigger on emp table which will not allow salary less than 1000 using RAISE_APPLICATION_ERROR() function

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;

/

 

RDBMS PLSQL Program 44 program to generate before insert, update and delete row trigger on emp table

 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;  

/