Oracle procedure trigger example

Oracle procedure trigger small example oracleproceduretrigger

SQL code -- create or replace procedure p_update_dept (l_remarker in number, -- 0 increment, 1 Delete, 2. Change l_deptno in number, l_dname in varchar2, l_loc in varchar2, l_result out varchar2 -- output parameter) to "is begin l_result: =" 0000 '; if l_remarker = 0 then -- 0 add insert into dept_copy values (l_deptno, l_dname, l_loc); elsif l_remarker = 1 then -- 1 delete from dept_copy p where p. deptno = l_dept No; elsif l_remarker = 2 then -- 2 modify update dept_copy p set p. loc = l_loc, p. dname = l_dname where p. deptno = l_deptno; end if; commit; exception when others then rollback; l_result: = '000000'; end; ----------------------------------------- call a stored procedure (mainly called in java) -- call the stored procedure in the test window (you can view the returned result on the output tab, which table to operate on, and which table to view can also be seen .) Declare -- Local variables here l_result varchar2 (4); -- the returned value length is good to write begin -- Test statements here p_update_dept (, 'accounting', 'New YORK ', l_result ); -- p_update_dept is the name of the stored procedure, and the parameters defined in brackets are used. Dbms_output.put_line (l_result); end; ====================================== trigger small example ================== ====== -- Row-level trigger, when data in one table changes, the values of the fields in the other table are modified at the same time. create or replace trigger trig2 after update on dept_copy for each row -- for each row begin update emp_copy e set e. deptno =: NEW. deptno where e. deptno =: old. deptno; end;/* When the deptno field of dept_copy changes, modify the value of the deptno field of the corresponding table emp_copy */-- the trigger is triggered when the following table is updated. deptno = 99 where d. deptno = 10; statement-Level trigger (rarely used) create or replace trigger t_emp_copy before insert or delete or update on emp_copy begin if inserting then insert into emp_copy_log values (values, 'insert', user, sysdate); elsif deleting then insert into emp_copy_log values (values, 'delete', user, sysdate); elsif updating then insert into emp_copy_log values (values, 'update', user, sysdate); end if; end;