creating procedures. lesson agenda using a modularized and layered subprogram design and...
Post on 20-Dec-2015
223 views
TRANSCRIPT
![Page 1: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/1.jpg)
Creating Procedures
![Page 2: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/2.jpg)
Lesson Agenda
Using a modularized and layered subprogram design and identifying the benefits of subprograms
Working with procedures: Creating and calling procedures Identifying the available parameter-passing modes Using formal and actual parameters Using positional, named, or mixed notation
Handling exceptions in procedures, removing a procedure, and displaying the procedures’ information
![Page 3: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/3.jpg)
Creating a Modularized Subprogram Design
Modularize code into subprograms.1. Locate code sequences repeated more than once.2. Create subprogram P containing the repeated code3. Modify original code to invoke the new subprogram.
xx xxx xxxxx xxx xxx
----- --- --- ----- --- --- xx xxx xxxxx xxx xxx----- --- ---
xx xxx xxxxx xxx xxx
P ----- --- -------- --- ---
P ----- --- ---
P
1
23
![Page 4: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/4.jpg)
Creating a Layered Subprogram DesignCreate subprogram layers for your application. Data access subprogram layer with SQL logic Business logic subprogram layer, which may or
may not use the data access layer
![Page 5: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/5.jpg)
Modularizing Developmentwith PL/SQL Blocks PL/SQL is a block-structured language. The PL/SQL
code block helps modularize code by using: Anonymous blocks Procedures and functions Packages Database triggers
The benefits of using modular program constructs are: Easy maintenance Improved data security and integrity Improved performance Improved code clarity
![Page 6: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/6.jpg)
Anonymous Blocks: OverviewAnonymous blocks: Form the basic PL/SQL block structure Initiate PL/SQL processing tasks from applications Can be nested within the executable section of
any PL/SQL block
[DECLARE -- Declaration Section (Optional) variable declarations; ... ]BEGIN -- Executable Section (Mandatory) SQL or PL/SQL statements;[EXCEPTION -- Exception Section (Optional) WHEN exception THEN statements; ]END; -- End of Block (Mandatory)
![Page 7: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/7.jpg)
PL/SQL Execution EnvironmentThe PL/SQL run-time architecture:
PL/SQL block
PL/SQL engine
Procedural
statement
executor
PL/SQL block
Oracle server
SQL statement executor
SQL
procedural
![Page 8: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/8.jpg)
What Are PL/SQL Subprograms? A PL/SQL subprogram is a named PL/SQL block
that can be called with a set of parameters. You can declare and define a subprogram within
either a PL/SQL block or another subprogram. A subprogram consists of a specification and a
body. A subprogram can be a procedure or a function. Typically, you use a procedure to perform an
action and a function to compute and return a value.
![Page 9: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/9.jpg)
The Benefits of Using PL/SQL Subprograms
Easy maintenance
Improved performance
Improved data security and integrity
Improved code clarity
Subprograms:Stored procedures
and functions
![Page 10: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/10.jpg)
Differences Between Anonymous Blocks and Subprograms
Anonymous Blocks Subprograms
Unnamed PL/SQL blocks Named PL/SQL blocks
Compiled every time Compiled only once
Not stored in the database Stored in the database
Cannot be invoked by other applications
Named and, therefore, can be invoked by other applications
Do not return values Subprograms called functions must return values.
Cannot take parameters Can take parameters
![Page 11: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/11.jpg)
Lesson Agenda
Using a modularized and layered subprogram design and identifying the benefits of subprograms
Working with procedures: Creating and calling procedures Identifying the available parameter-passing modes Using formal and actual parameters Using positional, named, or mixed notation
Handling exceptions in procedures, removing a procedure, and displaying the procedures’ information
![Page 12: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/12.jpg)
What Are Procedures? Are a type of subprogram that perform an action Can be stored in the database as a schema object Promote reusability and maintainability
Procedures
![Page 13: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/13.jpg)
Creating Procedures: Overview
Create/edit procedure
Execute procedure
Compilerwarnings/errors?
NO
YESUse SHOW ERRORS
command in SQL*Plus
Use USER/ALL/DBA_ERRORS views
View errors/warnings in SQL Developer
View compilerwarnings/errors
![Page 14: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/14.jpg)
Creating Procedures with the SQL CREATE OR REPLACE Statement Use the CREATE clause to create a stand-alone
procedure that is stored in the Oracle database. Use the OR REPLACE option to overwrite an
existing procedure.
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode] datatype1, parameter2 [mode] datatype2, ...)]IS|AS [local_variable_declarations; ...]BEGIN
-- actions;END [procedure_name];
PL/SQL block
![Page 15: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/15.jpg)
Creating Procedures Using SQL Developer
![Page 16: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/16.jpg)
Compiling Procedures and Displaying Compilation Errors in SQL Developer
1 2
OR
![Page 17: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/17.jpg)
Correcting Compilation Errors in SQL Developer
Edit procedure Correct error
Recompile procedureRecompilation successful
![Page 18: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/18.jpg)
Naming Conventions of PL/SQL Structures Used in This Course
PL/SQL Structure
Convention Example
Variable v_variable_name v_rate
Constant c_constant_name c_rate
Subprogram parameter
p_parameter_name p_id
Bind (host) variable
b_bind_name b_salary
Cursor cur_cursor_name cur_emp
Record rec_record_name rec_emp
Type type_name_type ename_table_type
Exception e_exception_name e_products_invalid
File handle f_file_handle_name
f_file
![Page 19: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/19.jpg)
What Are Parameters and Parameter Modes?
Are declared after the subprogram name in the PL/SQL header
Pass or communicate data between the caller and the subprogram
Are used like local variables but are dependent on their parameter-passing mode: An IN parameter mode (the default) provides values
for a subprogram to process An OUT parameter mode returns a value to the caller An IN OUT parameter mode supplies an input value,
which may be returned (output) as a modified value
![Page 20: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/20.jpg)
Formal and Actual Parameters
Formal parameters: Local variables declared in the parameter list of a subprogram specification
Actual parameters (or arguments): Literal values, variables, and expressions used in the parameter list of the calling subprogram
-- Procedure definition, Formal_parameters
CREATE PROCEDURE raise_sal(p_id NUMBER, p_sal NUMBER) ISBEGIN
. . .
END raise_sal;
-- Procedure calling, Actual parameters (arguments)
v_emp_id := 100;
raise_sal(v_emp_id, 2000)
![Page 21: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/21.jpg)
Procedural Parameter Modes Parameter modes are specified in the formal
parameter declaration, after the parameter name and before its data type.
The IN mode is the default if no mode is specified.
Modes
IN (default)
OUT
IN OUT
CREATE PROCEDURE proc_name(param_name [mode] datatype)...
Procedure
Callingenvironment
![Page 22: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/22.jpg)
Comparing the Parameter Modes
Cannot be assigneda default value
Cannot be assigned a default value
Can be assigned a default value
Must be a variableMust be a variable
Actual parameter can be a literal, expression, constant, or initialized variable
Initialized variableUninitialized variable
Formal parameter acts as a constant
Value is passed into subprogram
Default mode
IN
Must be specified•Must be specified
Passed into subprogram; returned to calling environment
IN OUT
Returned to calling environment
OUT
![Page 23: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/23.jpg)
CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%TYPE, p_percent IN NUMBER) IS BEGIN UPDATE employees SET salary = salary * (1 + p_percent/100) WHERE employee_id = p_id; END raise_salary; /
EXECUTE raise_salary(176, 10)
Using the IN Parameter Mode: Example
![Page 24: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/24.jpg)
CREATE OR REPLACE PROCEDURE query_emp (p_id IN employees.employee_id%TYPE, p_name OUT employees.last_name%TYPE, p_salary OUT employees.salary%TYPE) ISBEGIN SELECT last_name, salary INTO p_name, p_salary FROM employees WHERE employee_id = p_id;END query_emp;/
DECLARE v_emp_name employees.last_name%TYPE; v_emp_sal employees.salary%TYPE;BEGIN query_emp(171, v_emp_name, v_emp_sal); DBMS_OUTPUT.PUT_LINE(v_emp_name||' earns '||
to_char(v_emp_sal, '$999,999.00'));END;/
Using the OUT Parameter Mode: Example
![Page 25: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/25.jpg)
Using the IN OUT Parameter Mode: Example
Calling environment
CREATE OR REPLACE PROCEDURE format_phone (p_phone_no IN OUT VARCHAR2) ISBEGIN p_phone_no := '(' || SUBSTR(p_phone_no,1,3) || ') ' || SUBSTR(p_phone_no,4,3) || '-' || SUBSTR(p_phone_no,7);END format_phone;/
p_phone_no (before the call) p_phone_no (after the call)
'(800) 633-0575''8006330575'
![Page 26: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/26.jpg)
Viewing the OUT Parameters:Using the DBMS_OUTPUT.PUT_LINE SubroutineUse PL/SQL variables that are printed with calls to the DBMS_OUTPUT.PUT_LINE procedure.
SET SERVEROUTPUT ON
DECLARE v_emp_name employees.last_name%TYPE; v_emp_sal employees.salary%TYPE;BEGIN query_emp(171, v_emp_name, v_emp_sal); DBMS_OUTPUT.PUT_LINE('Name: ' || v_emp_name); DBMS_OUTPUT.PUT_LINE('Salary: ' || v_emp_sal);END;
![Page 27: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/27.jpg)
Viewing OUT Parameters:Using SQL*Plus Host Variables1. Use SQL*Plus host variables.2. Execute QUERY_EMP using host variables.3. Print the host variables.
VARIABLE b_name VARCHAR2(25)VARIABLE b_sal NUMBEREXECUTE query_emp(171, :b_name, :b_sal)PRINT b_name b_sal
![Page 28: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/28.jpg)
Available Notations for Passing Actual Parameters
When calling a subprogram, you can write the actual parameters using the following notations: Positional:
Lists the actual parameters in the same order as the formal parameters
Named: Lists the actual parameters in arbitrary order and uses
the association operator (=>) to associate a named formal parameter with its actual parameter
Mixed: Lists some of the actual parameters as positional and
some as named
![Page 29: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/29.jpg)
Passing Actual Parameters: Creating the add_dept Procedure
CREATE OR REPLACE PROCEDURE add_dept( p_name IN departments.department_name%TYPE, p_loc IN departments.location_id%TYPE) ISBEGIN INSERT INTO departments(department_id, department_name, location_id) VALUES (departments_seq.NEXTVAL, p_name , p_loc );END add_dept;/
![Page 30: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/30.jpg)
Passing Actual Parameters: Examples
-- Passing parameters using the positional notation.EXECUTE add_dept ('TRAINING', 2500)
-- Passing parameters using the named notation.EXECUTE add_dept (p_loc=>2400, p_name=>'EDUCATION')
![Page 31: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/31.jpg)
CREATE OR REPLACE PROCEDURE add_dept( p_name departments.department_name%TYPE:='Unknown', p_loc departments.location_id%TYPE DEFAULT 1700)ISBEGIN INSERT INTO departments (department_id,
department_name, location_id) VALUES (departments_seq.NEXTVAL, p_name, p_loc);END add_dept;
Using the DEFAULT Option for the Parameters
Defines default values for parameters. Provides flexibility by combining the positional and
named parameter-passing syntax.
EXECUTE add_deptEXECUTE add_dept ('ADVERTISING', p_loc => 1200)EXECUTE add_dept (p_loc => 1200)
![Page 32: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/32.jpg)
CREATE OR REPLACE PROCEDURE process_employeesIS CURSOR cur_emp_cursor IS SELECT employee_id FROM employees;BEGIN FOR emp_rec IN cur_emp_cursor LOOP raise_salary(emp_rec.employee_id, 10);
END LOOP; COMMIT;END process_employees;/
Calling ProceduresYou can call procedures using anonymous blocks, another procedure, or packages.
![Page 33: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/33.jpg)
Calling Procedures Using SQL Developer
Replace ID and PERCENT with actual values
1
2
3
4
![Page 34: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/34.jpg)
Lesson Agenda
Using a modularized and layered subprogram design and identifying the benefits of subprograms
Working with procedures: Creating and calling procedures Identifying the available parameter-passing modes Using formal and actual parameters Using positional, named, or mixed notation
Handling exceptions in procedures, removing a procedure, and displaying the procedures’ information
![Page 35: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/35.jpg)
Handled Exceptions
PROCEDURE PROC1 ...IS ...BEGIN ... PROC2(arg1); ...EXCEPTION ...END PROC1;
Calling procedure Called procedure
PROCEDURE PROC2 ...IS ...BEGIN ...EXCEPTION ...END PROC2;
Exception raised
Exception handled
Control returnsto calling procedure
![Page 36: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/36.jpg)
CREATE PROCEDURE create_departments ISBEGIN add_department('Media', 100, 1800); add_department('Editing', 99, 1800); add_department('Advertising', 101, 1800);END;
Handled Exceptions: Example
CREATE PROCEDURE add_department( p_name VARCHAR2, p_mgr NUMBER, p_loc NUMBER) ISBEGIN INSERT INTO DEPARTMENTS (department_id, department_name, manager_id, location_id) VALUES (DEPARTMENTS_SEQ.NEXTVAL, p_name, p_mgr, p_loc); DBMS_OUTPUT.PUT_LINE('Added Dept: '|| p_name);EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Err: adding dept: '|| p_name);END;
![Page 37: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/37.jpg)
Exceptions Not Handled
PROCEDURE PROC1 ...IS ...BEGIN ... PROC2(arg1); ...EXCEPTION ...END PROC1;
Calling procedure
Control returnedto exception section of calling procedure
Called procedure
PROCEDURE PROC2 ...IS ...BEGIN ...EXCEPTION ...END PROC2;
Exception raised
Exception not handled
![Page 38: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/38.jpg)
Exceptions Not Handled: Example
CREATE PROCEDURE create_departments_noex ISBEGIN add_department_noex('Media', 100, 1800); add_department_noex('Editing', 99, 1800); add_department_noex('Advertising', 101, 1800);END;
SET SERVEROUTPUT ONCREATE PROCEDURE add_department_noex( p_name VARCHAR2, p_mgr NUMBER, p_loc NUMBER) IS BEGIN INSERT INTO DEPARTMENTS (department_id, department_name, manager_id, location_id) VALUES (DEPARTMENTS_SEQ.NEXTVAL, p_name, p_mgr, p_loc); DBMS_OUTPUT.PUT_LINE('Added Dept: '|| p_name); END;
![Page 39: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/39.jpg)
Removing Procedures: Using the DROP SQL Statement or SQL Developer
Using the DROP statement:
Using SQL Developer:
DROP PROCEDURE raise_salary;
1
2
3
![Page 40: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/40.jpg)
Viewing Procedure Information Using the Data Dictionary Views
SELECT textFROM user_sourceWHERE name = 'ADD_DEPT' AND type = 'PROCEDURE'ORDER BY line;
DESCRIBE user_source
![Page 41: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/41.jpg)
Viewing Procedure Information Using SQL Developer
![Page 42: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/42.jpg)
Creating Functions
![Page 43: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/43.jpg)
ObjectivesAfter completing this lesson, you should be able to do the following: Differentiate between a procedure and a function Describe the uses of functions Create stored functions Invoke a function Remove a function
![Page 44: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/44.jpg)
Overview of Stored FunctionsA function: Is a named PL/SQL block that returns a value Can be stored in the database as a schema object
for repeated execution Is called as part of an expression or is used to
provide a parameter value
![Page 45: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/45.jpg)
CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [mode1] datatype1, . . .)]RETURN datatype IS|AS [local_variable_declarations; . . .]BEGIN -- actions; RETURN expression;END [function_name];
Creating FunctionsThe PL/SQL block must have at least one RETURN statement.
PL/SQL Block
![Page 46: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/46.jpg)
The Difference Between Procedures and Functions
Procedures Functions
Execute as a PL/SQL statement
Invoke as part of an expression
Do not contain RETURN clause in the header
Must contain a RETURN clause in the header
Can pass values (if any) using output parameters
Must return a single value
Can contain a RETURN statement without a value
Must contain at least one RETURN statement
![Page 47: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/47.jpg)
Creating and Running Functions: Overview
Create/edit function
Invoke function
Compilerwarnings/errors?
NO
YESUse SHOW ERRORS
command in SQL*Plus
Use USER/ALL/DBA_ERRORS views
View errors/warnings in SQL Developer
View compilerwarnings/errors
![Page 48: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/48.jpg)
Creating and Invoking a Stored Function Using the CREATE FUNCTION Statement: Example
CREATE OR REPLACE FUNCTION get_sal (p_id employees.employee_id%TYPE) RETURN NUMBER IS v_sal employees.salary%TYPE := 0;BEGIN SELECT salary INTO v_sal FROM employees WHERE employee_id = p_id; RETURN v_sal;END get_sal; /
-- Invoke the function as an expression or as-- a parameter value.
EXECUTE dbms_output.put_line(get_sal(100))
![Page 49: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/49.jpg)
Using Different Methods for Executing Functions-- As a PL/SQL expression, get the results using host variables
VARIABLE b_salary NUMBEREXECUTE :b_salary := get_sal(100)
-- As a PL/SQL expression, get the results using a local -- variable
DECLARE sal employees.salary%type;BEGIN sal := get_sal(100); DBMS_OUTPUT.PUT_LINE('The salary is: '|| sal);END;/
![Page 50: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/50.jpg)
Using Different Methods for Executing Functions-- Use as a parameter to another subprogram
EXECUTE dbms_output.put_line(get_sal(100))
-- Use in a SQL statement (subject to restrictions)
SELECT job_id, get_sal(employee_id) FROM employees;
. . .
![Page 51: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/51.jpg)
Creating and Compiling Functions Using SQL Developer
1 2 3
4
![Page 52: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/52.jpg)
Executing Functions Using SQL Developer
1
2
Replace ID with the actual value`
3`
![Page 53: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/53.jpg)
Advantages of User-Defined Functions in SQL Statements
Can extend SQL where activities are too complex, too awkward, or unavailable with SQL
Can increase efficiency when used in the WHERE clause to filter data, as opposed to filtering the data in the application
Can manipulate data values
![Page 54: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/54.jpg)
Using a Function in a SQL Expression: Example
CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER) RETURN NUMBER ISBEGIN RETURN (p_value * 0.08);END tax;/SELECT employee_id, last_name, salary, tax(salary)FROM employeesWHERE department_id = 100;
![Page 55: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/55.jpg)
Calling User-Defined Functionsin SQL Statements
User-defined functions act like built-in single-row functions and can be used in: The SELECT list or clause of a query Conditional expressions of the WHERE and HAVING
clauses The CONNECT BY, START WITH, ORDER BY, and GROUP
BY clauses of a query The VALUES clause of the INSERT statement The SET clause of the UPDATE statement
![Page 56: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/56.jpg)
Restrictions When Calling Functions from SQL Expressions
User-defined functions that are callable from SQL expressions must: Be stored in the database Accept only IN parameters with valid SQL data types,
not PL/SQL-specific types Return valid SQL data types, not PL/SQL-specific types
When calling functions in SQL statements: You must own the function or have the EXECUTE privilege
![Page 57: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/57.jpg)
Controlling Side Effects When Calling Functions from SQL Expressions
Functions called from: A SELECT statement cannot contain DML statements An UPDATE or DELETE statement on a table T cannot
query or contain DML on the same table T SQL statements cannot end transactions (that is,
cannot execute COMMIT or ROLLBACK operations)Note: Calls to subprograms that break these restrictions are also not allowed in the function.
![Page 58: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/58.jpg)
Restrictions on Calling Functions from SQL: ExampleCREATE OR REPLACE FUNCTION dml_call_sql(p_sal NUMBER) RETURN NUMBER ISBEGIN INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary) VALUES(1, 'Frost', '[email protected]', SYSDATE, 'SA_MAN', p_sal); RETURN (p_sal + 100);END;
UPDATE employees SET salary = dml_call_sql(2000)WHERE employee_id = 170;
![Page 59: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/59.jpg)
Named and Mixed Notation from SQL
PL/SQL allows arguments in a subroutine call to be specified using positional, named, or mixed notation
Prior to Oracle Database 11g, only the positional notation is supported in calls from SQL
Starting in Oracle Database 11g, named and mixed notation can be used for specifying arguments in calls to PL/SQL subroutines from SQL statements
For long parameter lists, with most having default values, you can omit values from the optional parameters
You can avoid duplicating the default value of the optional parameter at each call site
![Page 60: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/60.jpg)
Named and Mixed Notation from SQL: ExampleCREATE OR REPLACE FUNCTION f( p_parameter_1 IN NUMBER DEFAULT 1, p_parameter_5 IN NUMBER DEFAULT 5) RETURN NUMBERIS v_var number;BEGIN v_var := p_parameter_1 + (p_parameter_5 * 2); RETURN v_var;END f;/
SELECT f(p_parameter_5 => 10) FROM DUAL;
![Page 61: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/61.jpg)
Removing Functions: Using the DROP SQL Statement or SQL Developer
• Using the DROP statement:
• Using SQL Developer:
DROP FUNCTION f;
12
3
![Page 62: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/62.jpg)
Viewing Functions Using Data Dictionary Views
SELECT textFROM user_sourceWHERE type = 'FUNCTION'ORDER BY line;
DESCRIBE USER_SOURCE
. . .
![Page 63: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/63.jpg)
SummaryIn this lesson, you should have learned how to: Differentiate between a procedure and a function Describe the uses of functions Create stored functions Invoke a function Remove a function
![Page 64: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/64.jpg)
Creating Triggers
![Page 65: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/65.jpg)
ObjectivesAfter completing this lesson, you should be able to do the following: Describe database triggers and their uses Describe the different types of triggers Create database triggers Describe database trigger-firing rules Remove database triggers Display trigger information
![Page 66: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/66.jpg)
What Are Triggers ? A trigger is a PL/SQL block that is stored in the
database and fired (executed) in response to a specified event.
The Oracle database automatically executes a trigger when specified conditions occur.
![Page 67: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/67.jpg)
Defining TriggersA trigger can be defined on the table, view, schema (schema owner), or database (all users).
Table
View
Schema (owner)
Database (All users)
![Page 68: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/68.jpg)
Trigger Event Types
You can write triggers that fire whenever one of the following operations occurs in the database: A database manipulation (DML) statement (DELETE,
INSERT, or UPDATE). A database definition (DDL) statement (CREATE,
ALTER, or DROP). A database operation such as SERVERERROR, LOGON,
LOGOFF, STARTUP, or SHUTDOWN.
![Page 69: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/69.jpg)
Application and Database Triggers Database trigger (covered in this course):
Fires whenever a DML, a DLL, or system event occurs on a schema or database
Application trigger: Fires whenever an event occurs within a particular
application
Application Trigger Database Trigger
![Page 70: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/70.jpg)
Business Application Scenarios for Implementing TriggersYou can use triggers for: Security Auditing Data integrity Referential integrity Table replication Computing derived data automatically Event logging
![Page 71: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/71.jpg)
Available Trigger Types
Simple DML triggers BEFORE AFTER INSTEAD OF
Compound triggers Non-DML triggers
DDL event triggers Database event triggers
![Page 72: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/72.jpg)
Trigger Event Types and Body
A trigger event type determines which DML statement causes the trigger to execute. The possible events are: INSERT UPDATE [OF column] DELETE
A trigger body determines what action is performed and is a PL/SQL block or a CALL to a procedure
![Page 73: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/73.jpg)
Creating DML Triggers Using the CREATE TRIGGER Statement
CREATE [OR REPLACE] TRIGGER trigger_nametiming –- when to fire the triggerevent1 [OR event2 OR event3] ON object_name[REFERENCING OLD AS old | NEW AS new]FOR EACH ROW –- default is statement level triggerWHEN (condition)]]DECLARE] BEGIN... trigger_body –- executable statements[EXCEPTION . . .]END [trigger_name];
timing = BEFORE | AFTER | INSTEAD OF
event = INSERT | DELETE | UPDATE | UPDATE OF column_list
![Page 74: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/74.jpg)
Specifying the Trigger Firing (Timing)
You can specify the trigger timing as to whether to run the trigger’s action before or after the triggering statement: BEFORE: Executes the trigger body before the triggering
DML event on a table. AFTER: Execute the trigger body after the triggering DML
event on a table. INSTEAD OF: Execute the trigger body instead of the
triggering statement. This is used for views that are not otherwise modifiable.
![Page 75: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/75.jpg)
Statement-Level Triggers Versus Row-Level Triggers
Statement-Level Triggers Row-Level Triggers
Is the default when creating a trigger
Use the FOR EACH ROW clause when creating a trigger.
Fires once for the triggering event
Fires once for each row affected by the triggering event
Fires once even if no rows are affected
Does not fire if the triggering event does not affect any rows
![Page 76: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/76.jpg)
Creating DML Triggers Using SQL Developer
![Page 77: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/77.jpg)
Trigger-Firing Sequence:Single-Row ManipulationUse the following firing sequence for a trigger on a table when a single row is manipulated:
BEFORE statement trigger
BEFORE row trigger
AFTER row trigger
AFTER statement trigger
INSERT INTO departments (department_id,department_name, location_id)VALUES (400, 'CONSULTING', 2400);
. . .
![Page 78: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/78.jpg)
Trigger-Firing Sequence:Multirow Manipulation•Use the following firing sequence for a trigger on a table when many rows are manipulated:
BEFORE statement trigger
AFTER statement trigger
BEFORE row trigger
AFTER row trigger
. . .
BEFORE row trigger
AFTER row trigger. . .
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 30;
![Page 79: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/79.jpg)
EMPLOYEES table
SECURE_EMP trigger
Creating a DML Statement Trigger Example:SECURE_EMP
CREATE OR REPLACE TRIGGER secure_empBEFORE INSERT ON employees BEGIN IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24:MI')
NOT BETWEEN '08:00' AND '18:00') THEN RAISE_APPLICATION_ERROR(-20500, 'You may insert' ||' into EMPLOYEES table only during ' ||' normal business hours.'); END IF; END;
Application
INSERT INTO EMPLOYEES...;
DML statement fires trigger
![Page 80: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/80.jpg)
Testing Trigger SECURE_EMP
INSERT INTO employees (employee_id, last_name, first_name, email, hire_date,
job_id, salary, department_id)VALUES (300, 'Smith', 'Rob', 'RSMITH', SYSDATE, 'IT_PROG', 4500, 60);
![Page 81: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/81.jpg)
Using Conditional PredicatesCREATE OR REPLACE TRIGGER secure_emp BEFOREINSERT OR UPDATE OR DELETE ON employees BEGIN IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR (TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '18') THEN IF DELETING THEN RAISE_APPLICATION_ERROR( -20502,'You may delete from EMPLOYEES table'|| 'only during normal business hours.'); ELSIF INSERTING THEN RAISE_APPLICATION_ERROR( -20500,'You may insert into EMPLOYEES table'|| 'only during normal business hours.'); ELSIF UPDATING ('SALARY') THEN RAISE_APPLICATION_ERROR(-20503, 'You may '|| 'update SALARY only normal during business hours.'); ELSE RAISE_APPLICATION_ERROR(-20504,'You may'|| ' update EMPLOYEES table only during'|| ' normal business hours.'); END IF; END IF; END;
![Page 82: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/82.jpg)
Creating a DML Row TriggerCREATE OR REPLACE TRIGGER restrict_salaryBEFORE INSERT OR UPDATE OF salary ON employeesFOR EACH ROWBEGIN IF NOT (:NEW.job_id IN ('AD_PRES', 'AD_VP')) AND :NEW.salary > 15000 THEN RAISE_APPLICATION_ERROR (-20202, 'Employee cannot earn more than $15,000.'); END IF;END;/
UPDATE employeesSET salary = 15500WHERE last_name = 'Russell';
![Page 83: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/83.jpg)
Using OLD and NEW Qualifiers
When a row-level trigger fires, the PL/SQL run-time engine creates and populates two data structures: OLD: Stores the original values of the record processed
by the trigger NEW: Contains the new values
NEW and OLD have the same structure as a record declared using the %ROWTYPE on the table to which the trigger is attached.
Data Operations
Old Value New Value
INSERT NULL Inserted value
UPDATE Value before update Value after update
DELETE Value before delete NULL
![Page 84: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/84.jpg)
Using OLD and NEW Qualifiers: Example
CREATE OR REPLACE TRIGGER audit_emp_valuesAFTER DELETE OR INSERT OR UPDATE ON employeesFOR EACH ROWBEGIN INSERT INTO audit_emp(user_name, time_stamp, id, old_last_name, new_last_name, old_title, new_title, old_salary, new_salary) VALUES (USER, SYSDATE, :OLD.employee_id, :OLD.last_name, :NEW.last_name, :OLD.job_id, :NEW.job_id, :OLD.salary, :NEW.salary);END;/
![Page 85: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/85.jpg)
Using OLD and NEW Qualifiers:Example Using AUDIT_EMP
INSERT INTO employees (employee_id, last_name, job_id, salary, email, hire_date)VALUES (999, 'Temp emp', 'SA_REP', 6000, 'TEMPEMP', TRUNC(SYSDATE));/UPDATE employees SET salary = 7000, last_name = 'Smith' WHERE employee_id = 999;/SELECT *FROM audit_emp;
![Page 86: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/86.jpg)
Using the WHEN Clause to Fire a Row Trigger Based on a Condition
CREATE OR REPLACE TRIGGER derive_commission_pctBEFORE INSERT OR UPDATE OF salary ON employeesFOR EACH ROWWHEN (NEW.job_id = 'SA_REP')BEGIN IF INSERTING THEN :NEW.commission_pct := 0; ELSIF :OLD.commission_pct IS NULL THEN :NEW.commission_pct := 0; ELSE :NEW.commission_pct := :OLD.commission_pct+0.05; END IF;END;/
![Page 87: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/87.jpg)
Summary of the Trigger Execution Model
1. Execute all BEFORE STATEMENT triggers.2. Loop for each row affected by the SQL statement:
a. Execute all BEFORE ROW triggers for that row.b. Execute the DML statement and perform integrity
constraint checking for that row.c. Execute all AFTER ROW triggers for that row.
3. Execute all AFTER STATEMENT triggers.
![Page 88: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/88.jpg)
-- Integrity constraint violation error –2992 raised.UPDATE employees SET department_id = 999 WHERE employee_id = 170;
Implementing an Integrity Constraint with an After Trigger
CREATE OR REPLACE TRIGGER employee_dept_fk_trgAFTER UPDATE OF department_id
ON employees FOR EACH ROWBEGIN INSERT INTO departments VALUES(:new.department_id,
'Dept '||:new.department_id, NULL, NULL);EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; -- mask exception if department existsEND; /
-- Successful after trigger is firedUPDATE employees SET department_id = 999 WHERE employee_id = 170;
![Page 89: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/89.jpg)
INSTEAD OF Triggers
Application
MY_VIEW
INSTEAD OF trigger
INSERT
UPDATE
INSERT INTO my_view . . . ;
TABLE 2
TABLE 1
![Page 90: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/90.jpg)
Creating an INSTEAD OF Trigger: Example
INSTEAD OF INSERT into EMP_DETAILS
INSERT into NEW_EMPS table
UPDATE NEW_DEPTS table
INSERT INTO emp_detailsVALUES (9001,'ABBOTT',3000, 10, 'Administration');
EMP_DETAILS view
1
2
3
![Page 91: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/91.jpg)
Creating an INSTEAD OF Trigger to Perform DML on Complex Views
CREATE TABLE new_emps AS SELECT employee_id,last_name,salary,department_id
FROM employees;
CREATE TABLE new_depts AS SELECT d.department_id,d.department_name, sum(e.salary) dept_sal
FROM employees e, departments d WHERE e.department_id = d.department_id;
CREATE VIEW emp_details AS SELECT e.employee_id, e.last_name, e.salary, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_idGROUP BY d.department_id,d.department_name;
![Page 92: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/92.jpg)
The Status of a Trigger
A trigger is in either of two distinct modes: Enabled: The trigger runs its trigger action if a
triggering statement is issued and the trigger restriction (if any) evaluates to true (default).
Disabled: The trigger does not run its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to true.
![Page 93: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/93.jpg)
Creating a Disabled Trigger
Before Oracle Database 11g, if you created a trigger whose body had a PL/SQL compilation error, then DML to the table failed.
In Oracle Database 11g, you can create a disabled trigger and then enable it only when you know it will be compiled successfully.
CREATE OR REPLACE TRIGGER mytrg BEFORE INSERT ON mytable FOR EACH ROW DISABLEBEGIN :New.ID := my_seq.Nextval;. . .END;/
![Page 94: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/94.jpg)
Managing Triggers Using the ALTER and DROP SQL Statements
-- Disable or reenable a database trigger:
ALTER TRIGGER trigger_name DISABLE | ENABLE;
-- Disable or reenable all triggers for a table:
ALTER TABLE table_name DISABLE | ENABLE ALL TRIGGERS;
-- Recompile a trigger for a table:
ALTER TRIGGER trigger_name COMPILE;
-- Remove a trigger from the database:
DROP TRIGGER trigger_name;
![Page 95: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/95.jpg)
Managing Triggers Using SQL Developer
![Page 96: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/96.jpg)
Testing Triggers
Test each triggering data operation, as well as non-triggering data operations.
Test each case of the WHEN clause. Cause the trigger to fire directly from a basic data
operation, as well as indirectly from a procedure. Test the effect of the trigger on other triggers. Test the effect of other triggers on the trigger.
![Page 97: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/97.jpg)
Viewing Trigger InformationYou can view the following trigger information:
Data Dictionary View
Description
USER_OBJECTS Displays object information
USER/ALL/DBA_TRIGGERS
Displays trigger information
USER_ERRORS Displays PL/SQL syntax errors for a trigger
![Page 98: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/98.jpg)
Using USER_TRIGGERS
DESCRIBE user_triggers
SELECT trigger_type, trigger_bodyFROM user_triggersWHERE trigger_name = 'SECURE_EMP';
![Page 99: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/99.jpg)
SummaryIn this lesson, you should have learned how to: Create database triggers that are invoked by DML
operations Create statement and row trigger types Use database trigger-firing rules Enable, disable, and manage database triggers Develop a strategy for testing triggers Remove database triggers
![Page 100: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/100.jpg)
Creating Compound, DDL, and Event Database Triggers
![Page 101: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/101.jpg)
Objectives
After completing this lesson, you should be able to do the following: Describe compound triggers Describe mutating tables Create triggers on DDL statements Create triggers on system events Display information about triggers
![Page 102: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/102.jpg)
What Is a Compound Trigger ?
A single trigger on a table that allows you to specify actions for each of the following four timing points: Before the firing statement Before each row that the firing statement affects After each row that the firing statement affects After the firing statement
![Page 103: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/103.jpg)
Working with Compound Triggers
The compound trigger body supports a common PL/SQL state that the code for each timing point can access.
The compound trigger common state is: Established when the triggering statement starts Destroyed when the triggering statement completes
A compound trigger has a declaration section and a section for each of its timing points.
![Page 104: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/104.jpg)
The Benefits of Using a Compound Trigger
You can use compound triggers to: Program an approach where you want the
actions you implement for the various timing points to share common data.
Accumulate rows destined for a second table so that you can periodically bulk-insert them
Avoid the mutating-table error (ORA-04091)by allowing rows destined for a second table to accumulate and then bulk-inserting them
![Page 105: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/105.jpg)
Timing-Point Sections of a Table Compound TriggerA compound trigger defined on a table has one or more of the following timing-point sections. Timing-point sections must appear in the order shown in the table.
Timing Point Compound Trigger Section
Before the triggering statement executes BEFORE statement
After the triggering statement executes AFTER statement
Before each row that the triggering statement affects
BEFORE EACH ROW
After each row that the triggering statement affects
AFTER EACH ROW
![Page 106: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/106.jpg)
Compound Trigger Structure for TablesCREATE OR REPLACE TRIGGER schema.trigger FOR dml_event_clause ON schema.tableCOMPOUND TRIGGER
-- Initial section -- Declarations -- Subprograms
-- Optional section BEFORE STATEMENT IS ...;
-- Optional sectionBEFORE EACH ROW IS ...;
-- Optional section AFTER EACH ROW IS ...;
-- Optional section AFTER STATEMENT IS ...;
1
2
![Page 107: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/107.jpg)
Compound Trigger Structure for Views
CREATE OR REPLACE TRIGGER schema.trigger
FOR dml_event_clause ON schema.view
COMPOUND TRIGGER
-- Optional section (exclusive) INSTEAD OF EACH ROW IS ...;
-- Initial section -- Declarations -- Subprograms
![Page 108: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/108.jpg)
Compound Trigger Restrictions
A compound trigger must be a DML trigger and defined on either a table or a view.
The body of a compound trigger must be compound trigger block, written in PL/SQL.
A compound trigger body cannot have an initialization block; therefore, it cannot have an exception section.
An exception that occurs in one section must be handled in that section. It cannot transfer control to another section.
:OLD and :NEW cannot appear in the declaration, BEFORE STATEMENT, or the AFTER STATEMENT sections.
Only the BEFORE EACH ROW section can change the value of :NEW.
The firing order of compound triggers is not guaranteed unless you use the FOLLOWS clause.
![Page 109: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/109.jpg)
Trigger Restrictions on Mutating Tables
A mutating table is: A table that is being modified by an UPDATE, DELETE,
or INSERT statement, or A table that might be updated by the effects of a
DELETE CASCADE constraint The session that issued the triggering statement
cannot query or modify a mutating table. This restriction prevents a trigger from seeing an
inconsistent set of data. This restriction applies to all triggers that use the
FOR EACH ROW clause. Views being modified in the INSTEAD OF triggers
are not considered mutating.
![Page 110: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/110.jpg)
Mutating Table: Example
CREATE OR REPLACE TRIGGER check_salary BEFORE INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW WHEN (NEW.job_id <> 'AD_PRES')DECLARE v_minsalary employees.salary%TYPE; v_maxsalary employees.salary%TYPE;BEGIN SELECT MIN(salary), MAX(salary) INTOv_minsalary, v_maxsalary FROMemployees WHERE job_id = :NEW.job_id; IF :NEW.salary < v_minsalary OR :NEW.salary > v_maxsalary THEN RAISE_APPLICATION_ERROR(-20505,'Out of range'); END IF; END;/
![Page 111: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/111.jpg)
Mutating Table: Example
UPDATE employeesSET salary = 3400WHERE last_name = 'Stiles';
![Page 112: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/112.jpg)
Using a Compound Trigger to Resolve the Mutating Table Error
CREATE OR REPLACE TRIGGER check_salary FOR INSERT OR UPDATE OF salary, job_id ON employees WHEN (NEW.job_id <> 'AD_PRES') COMPOUND TRIGGER
TYPE salaries_t IS TABLE OF employees.salary%TYPE; min_salaries salaries_t; max_salaries salaries_t;
TYPE department_ids_t IS TABLE OF employees.department_id%TYPE; department_ids department_ids_t;
TYPE department_salaries_t IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(80); department_min_salaries department_salaries_t; department_max_salaries department_salaries_t;
-- example continues on next slide
![Page 113: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/113.jpg)
Using a Compound Trigger to Resolve the Mutating Table Error
. . .BEFORE STATEMENT IS BEGIN SELECT MIN(salary), MAX(salary), NVL(department_id, -1) BULK COLLECT INTO min_Salaries, max_salaries, department_ids FROM employees GROUP BY department_id; FOR j IN 1..department_ids.COUNT() LOOP department_min_salaries(department_ids(j)) := min_salaries(j); department_max_salaries(department_ids(j)) := max_salaries(j); END LOOP;END BEFORE STATEMENT;
AFTER EACH ROW IS BEGIN IF :NEW.salary < department_min_salaries(:NEW.department_id) OR :NEW.salary > department_max_salaries(:NEW.department_id) THEN RAISE_APPLICATION_ERROR(-20505,'New Salary is out of acceptable range'); END IF; END AFTER EACH ROW;END check_salary;
![Page 114: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/114.jpg)
Comparing Database Triggers to Stored Procedures
Triggers Procedures
Defined with CREATE TRIGGER Defined with CREATE PROCEDURE
Data dictionary contains source code in USER_TRIGGERS
Data dictionary contains sourcecode in USER_SOURCE
Implicitly invoked by DML Explicitly invoked
COMMIT, SAVEPOINT, and ROLLBACK are not allowed
COMMIT, SAVEPOINT, and ROLLBACK are allowed
![Page 115: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/115.jpg)
Comparing Database Triggers to Oracle Forms Triggers
Database Trigger Forms Builder Trigger
Executed by actions from any database tool or application
Executed only within a particular Forms Builder application
Always triggered by a SQL DML, DDL, or a certain database
event
Triggered by navigating from field to field, pressing a key, or
many other actions
Can be a statement or row trigger
Can be a statement or row trigger
Upon failure, causes the triggering statement to roll
back
Upon failure, causes the cursor to freeze and the entire
transaction may roll back
Fires independently of, and in addition to, Forms Builder
triggers
Fires independently of, and in addition to, database triggers
Executes under the security domain of the author of the
trigger
Executes under the security domain of the Forms Builder
user
![Page 116: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/116.jpg)
Creating Triggers on DDL Statements
CREATE [OR REPLACE] TRIGGER trigger_nameBEFORE | AFTER -- Timing[ddl_event1 [OR ddl_event2 OR ...]]ON {DATABASE | SCHEMA} trigger_body
Sample DDL Events
Fires When
CREATE Any database object is created using the CREATE command.
ALTER Any database object is altered using the ALTER command.
DROP Any database object is dropped using the DROP command.
![Page 117: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/117.jpg)
Creating Database-Event Triggers
Triggering user event: CREATE, ALTER, or DROP Logging on or off
Triggering database or system event: Shutting down or starting up the database A specific error (or any error) being raised
![Page 118: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/118.jpg)
Creating Triggers on System Events
CREATE [OR REPLACE] TRIGGER trigger_nameBEFORE | AFTER -- timing[database_event1 [OR database_event2 OR ...]]ON {DATABASE | SCHEMA} trigger_body
Database Event Triggers Fires When
AFTER SERVERERROR An Oracle error is raised
AFTER LOGON A user logs on to the database
BEFORE LOGOFF A user logs off the database
AFTER STARTUP The database is opened
BEFORE SHUTDOWN The database is shut down normally
![Page 119: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/119.jpg)
LOGON and LOGOFF Triggers: Example-- Create the log_trig_table shown in the notes page -- first
CREATE OR REPLACE TRIGGER logon_trigAFTER LOGON ON SCHEMABEGIN INSERT INTO log_trig_table(user_id,log_date,action) VALUES (USER, SYSDATE, 'Logging on');END;/
CREATE OR REPLACE TRIGGER logoff_trigBEFORE LOGOFF ON SCHEMABEGIN INSERT INTO log_trig_table(user_id,log_date,action) VALUES (USER, SYSDATE, 'Logging off');END;/
![Page 120: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/120.jpg)
CREATE OR REPLACE PROCEDURE log_execution ISBEGIN DBMS_OUTPUT.PUT_LINE('log_exection: Employee Inserted');END;/CREATE OR REPLACE TRIGGER log_employeeBEFORE INSERT ON EMPLOYEESCALL log_execution –- no semicolon needed/
CALL Statements in Triggers
CREATE [OR REPLACE] TRIGGER trigger_nametiming event1 [OR event2 OR event3]ON table_name [REFERENCING OLD AS old | NEW AS new][FOR EACH ROW][WHEN condition]CALL procedure_name/
![Page 121: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/121.jpg)
Benefits of Database-Event Triggers Improved data security:
Provide enhanced and complex security checks Provide enhanced and complex auditing
Improved data integrity: Enforce dynamic data integrity constraints Enforce complex referential integrity constraints Ensure that related operations are performed together
implicitly
![Page 122: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/122.jpg)
System Privileges Required to Manage Triggers
The following system privileges are required to manage triggers: The CREATE/ALTER/DROP (ANY) TRIGGER privilege that
enables you to create a trigger in any schema The ADMINISTER DATABASE TRIGGER privilege that
enables you to create a trigger on DATABASE The EXECUTE privilege (if your trigger refers to any
objects that are not in your schema)
![Page 123: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/123.jpg)
Guidelines for Designing Triggers You can design triggers to:
Perform related actions Centralize global operations
You must not design triggers: Where functionality is already built into the Oracle
server That duplicate other triggers
You can create stored procedures and invoke them in a trigger, if the PL/SQL code is very lengthy.
Excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications.
![Page 124: Creating Procedures. Lesson Agenda Using a modularized and layered subprogram design and identifying the benefits of subprograms Working with procedures:](https://reader037.vdocuments.us/reader037/viewer/2022102800/56649d4b5503460f94a27ff1/html5/thumbnails/124.jpg)
Summary
In this lesson, you should have learned how to: Describe compound triggers Describe mutating tables Create triggers on DDL statements Create triggers on system events Display information about triggers