Helps you quickly understand stored functions, stored procedures, and triggers

Help you quickly understand stored functions, stored procedures, triggers 1. create stored procedures 1. create procedure isertEmp as begin insert into emp values (9888, 'www ', 'sdf ', null, 9888, 20); commit; end;/2. create or replace the stored procedure create or replace procedure isertEmp as begin insert into emp values (, 'www ', 'sdf ', null, 20); commit; end; /3. Stored procedure case -- stored procedure case 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; /4. Call a stored procedure-method 1 of calling a stored procedure: SQL> set serveroutput on; -- enable the Service SQL> begin raiseSalary (7521); end;/employee ID: 7521 before salary increase 1250 after salary increase 1375 PL/SQL procedure successfully completed Method 2: SQL> set serveroutput on; SQL> exec raiseSalary (7521); employee ID: 7521 before salary increase 1375 after salary increase 1512.5 PL/SQL procedure successfully completed 2. storage function 1. create a storage function querySal (cno in number) return number as psal number; pcomm number; begin select sal, comm into psal, pcomm from emp where empno = cno; return psal * 12 + pcomm; end; /2. create or replace the storage function create or replace function querySal (cno in number) return number as psal number; pcomm number; begin select sal, comm into psal, pcomm from emp where empno = cno; return psal * 12 + pcomm; end;/3. Case create or replace function queryEmpSalary (empid in number) return number as pComm number; pSal number; begin select sal, comm into pSal, pComm from emp where empno = empid; return pSal * 12 + pComm; end;/4. function call if a field is empty, however, if you want this field to display 0nvl (field name, 0), this field is displayed as 0 declare v_sal number; begin v_sal: = queryEmpSalary (7521); dbms_output.put_line ('salary is: '| v_sal); end;/salary is: 18650 trigger 1. create or replace trigger tig before insert on emp begin dbms_output.put_line ('executed before insert'); end; /Trigger created2. Trigger effect insert into emp values (1234, 'hhh ', 'asd', null, limit, 40); executed before insertion