Example of stored procedures and stored functions and triggers

Example of stored procedures and stored functions and triggers 1. Example of stored procedures: SQL> create or replace procedure raiseSalary (empid in number) as pSal emp. sal % type; begin select sal into pSal from emp where empno = empid; update emp set sal = sal * 1.1 where empno = empid; dbms_output.put_line ('employee ID: '| empid | 'salary raise pre' | psal | 'salary raise Post' | psal * 1.1); end; /Procedure created SQL> set serveroutput on SQL> exec raisesalary (7369); employee No.: 7369 800 PL/SQL procedure successfully completed 2. storage function example: query the annual income of a worker. SQL>/** // * query the total income of a certain employee */create or replace function queryEmpSalary (empid in number) return number as pSal number; -- Define the variable to save the employee's salary pComm number; -- Define the variable to save the employee's bonus begin select sal, comm into psal, pcomm from emp where empno = empid; return psal * 12 + nvl (pcomm, 0); end;/Function created SQL> declare v_sal number; begin v_sal: = queryEmpSalary (7934); dbms_output.put_line ('salary is: '| v_sal); end;/salary is: 15600 PL/SQL procedure successfully completed SQL> begin dbms_output.put_line ('salary is: '| queryEmpSalary (7934); end;/salary is: 15600 PL/SQL procedure successfully completed 3. create trigger Example 1: restrict non-working hours to insert data to the database SQL> create or replace trigger securityEmp before insert on emp declare begin if to_char (sysdate, 'day') in ('thurday', 'saturday', 'sunday') or to_number (to_char (sysdate, 'hh24') not between 8 and 18 then raise _ Application_error (-20001, 'data cannot be inserted during non-work hours. '); End if; end;/Trigger created 4. Create Trigger Example 2: confirm the data (check that the modified sal value in the emp table is not lower than the original value) SQL> create or replace trigger checkSal before update of sal on emp for each row declare begin if: new. sal <: old. sal then raise_application_error (-20001, 'salary after the Update'); end if; end;/Trigger created