Содержание
- 2. Objectives After completing this lesson, you should be able to do the following: Create standard constants
- 3. Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram,
- 4. Standardizing Constants and Exceptions Constants and exceptions are typically implemented using a bodiless package (that is,
- 5. Standardizing Exceptions Create a standardized error-handling package that includes all named and programmer-defined exceptions to be
- 6. Standardizing Exception Handling Consider writing a subprogram for common exception handling to: Display errors based on
- 7. Standardizing Constants For programs that use local variables whose values should not change: Convert the variables
- 8. Local Subprograms A local subprogram is a PROCEDURE or FUNCTION defined at the end of the
- 9. Definer’s Rights Versus Invoker’s Rights Definer’s rights: Used prior to Oracle8i Programs execute with the privileges
- 10. CREATE OR REPLACE PROCEDURE add_dept( p_id NUMBER, p_name VARCHAR2) AUTHID CURRENT_USER IS BEGIN INSERT INTO departments
- 11. PROCEDURE proc1 IS emp_id NUMBER; BEGIN emp_id := 1234; COMMIT; INSERT ... proc2; DELETE ... COMMIT;
- 12. Features of Autonomous Transactions Are independent of the main transaction Suspend the calling transaction until the
- 13. Using Autonomous Transactions: Example PROCEDURE bank_trans(p_cardnbr NUMBER, p_loc NUMBER) IS BEGIN log_usage(p_cardnbr, p_loc); INSERT INTO txn
- 15. Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram,
- 16. Using the NOCOPY Hint Allows the PL/SQL compiler to pass OUT and IN OUT parameters by
- 17. Effects of the NOCOPY Hint If the subprogram exits with an exception that is not handled:
- 18. When Does the PL/SQL Compiler Ignore the NOCOPY Hint? The NOCOPY hint has no effect if:
- 19. Using the PARALLEL_ENABLE Hint Can be used in functions as an optimization hint Indicates that a
- 20. Using the Cross-Session PL/SQL Function Result Cache Each time a result-cached PL/SQL function is called with
- 21. Enabling Result-Caching for a Function You can make a function result-cached as follows: Include the RESULT_CACHE
- 22. Declaring and Defining a Result-Cached Function: Example CREATE OR REPLACE FUNCTION emp_hire_date (p_emp_id NUMBER) RETURN VARCHAR
- 24. Using the DETERMINISTIC Clause with Functions Specify DETERMINISTIC to indicate that the function returns the same
- 25. Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram,
- 26. CREATE PROCEDURE update_salary(p_emp_id NUMBER) IS v_name employees.last_name%TYPE; v_new_sal employees.salary%TYPE; BEGIN UPDATE employees SET salary = salary
- 27. SQL engine Bulk Binding Binds whole arrays of values in a single operation, rather than using
- 28. Using Bulk Binding: Syntax and Keywords The FORALL keyword instructs the PL/SQL engine to bulk bind
- 30. Bulk Binding FORALL: Example CREATE PROCEDURE raise_salary(p_percent NUMBER) IS TYPE numlist_type IS TABLE OF NUMBER INDEX
- 32. CREATE PROCEDURE get_departments(p_loc NUMBER) IS TYPE dept_tab_type IS TABLE OF departments%ROWTYPE; v_depts dept_tab_type; BEGIN SELECT *
- 33. Using BULK COLLECT INTO with Cursors The FETCH statement has been enhanced to support the BULK
- 34. CREATE PROCEDURE raise_salary(p_rate NUMBER) IS TYPE emplist_type IS TABLE OF NUMBER; TYPE numlist_type IS TABLE OF
- 35. FORALL Support for Sparse Collections -- The new INDICES OF syntax allows the bound arrays --
- 36. Using Bulk Binds in Sparse Collections The typical application for this feature is an order entry
- 37. Using Bulk Bind with Index Array CREATE OR REPLACE PROCEDURE ins_emp2 AS TYPE emptab_type IS TABLE
- 38. Quiz The NOCOPY hint allows the PL/SQL compiler to pass OUT and IN OUT parameters by
- 39. Summary In this lesson, you should have learned how to: Create standard constants and exceptions Write
- 40. Practice: Overview This practice covers the following topics: Creating a package that uses bulk fetch operations
- 43. Скачать презентацию