🗊Презентация Design Considerations for PL/SQL Code. (Lecture 3)

Категория: Информатика
Нажмите для полного просмотра!
Design Considerations for PL/SQL Code. (Lecture 3), слайд №1Design Considerations for PL/SQL Code. (Lecture 3), слайд №2Design Considerations for PL/SQL Code. (Lecture 3), слайд №3Design Considerations for PL/SQL Code. (Lecture 3), слайд №4Design Considerations for PL/SQL Code. (Lecture 3), слайд №5Design Considerations for PL/SQL Code. (Lecture 3), слайд №6Design Considerations for PL/SQL Code. (Lecture 3), слайд №7Design Considerations for PL/SQL Code. (Lecture 3), слайд №8Design Considerations for PL/SQL Code. (Lecture 3), слайд №9Design Considerations for PL/SQL Code. (Lecture 3), слайд №10Design Considerations for PL/SQL Code. (Lecture 3), слайд №11Design Considerations for PL/SQL Code. (Lecture 3), слайд №12Design Considerations for PL/SQL Code. (Lecture 3), слайд №13Design Considerations for PL/SQL Code. (Lecture 3), слайд №14Design Considerations for PL/SQL Code. (Lecture 3), слайд №15Design Considerations for PL/SQL Code. (Lecture 3), слайд №16Design Considerations for PL/SQL Code. (Lecture 3), слайд №17Design Considerations for PL/SQL Code. (Lecture 3), слайд №18Design Considerations for PL/SQL Code. (Lecture 3), слайд №19Design Considerations for PL/SQL Code. (Lecture 3), слайд №20Design Considerations for PL/SQL Code. (Lecture 3), слайд №21Design Considerations for PL/SQL Code. (Lecture 3), слайд №22Design Considerations for PL/SQL Code. (Lecture 3), слайд №23Design Considerations for PL/SQL Code. (Lecture 3), слайд №24Design Considerations for PL/SQL Code. (Lecture 3), слайд №25Design Considerations for PL/SQL Code. (Lecture 3), слайд №26Design Considerations for PL/SQL Code. (Lecture 3), слайд №27Design Considerations for PL/SQL Code. (Lecture 3), слайд №28Design Considerations for PL/SQL Code. (Lecture 3), слайд №29Design Considerations for PL/SQL Code. (Lecture 3), слайд №30Design Considerations for PL/SQL Code. (Lecture 3), слайд №31Design Considerations for PL/SQL Code. (Lecture 3), слайд №32Design Considerations for PL/SQL Code. (Lecture 3), слайд №33Design Considerations for PL/SQL Code. (Lecture 3), слайд №34Design Considerations for PL/SQL Code. (Lecture 3), слайд №35Design Considerations for PL/SQL Code. (Lecture 3), слайд №36Design Considerations for PL/SQL Code. (Lecture 3), слайд №37Design Considerations for PL/SQL Code. (Lecture 3), слайд №38Design Considerations for PL/SQL Code. (Lecture 3), слайд №39Design Considerations for PL/SQL Code. (Lecture 3), слайд №40Design Considerations for PL/SQL Code. (Lecture 3), слайд №41Design Considerations for PL/SQL Code. (Lecture 3), слайд №42
Скачать

Содержание ▲

Вы можете ознакомиться и скачать презентацию на тему Design Considerations for PL/SQL Code. (Lecture 3). Доклад-сообщение содержит 42 слайдов. Презентации для любого класса можно скачать бесплатно. Если материал и наш сайт презентаций Mypresentation Вам понравились – поделитесь им с друзьями с помощью социальных кнопок и добавьте в закладки в своем браузере.

Слайды и текст этой презентации

Слайд 1


Design Considerations for PL/SQL Code
Описание слайда:
Design Considerations for PL/SQL Code

Слайд 2


Objectives After completing this lesson, you should be able to do the following: Create standard constants and exceptions Write and call local subprograms Control the run-time privileges of a subprogram Perform autonomous transactions Pass parameters by reference using a NOCOPY hint Use the PARALLEL ENABLE hint for optimization Use the cross-session PL/SQL function result cache Use the DETERMINISTIC clause with functions Use bulk binding and the RETURNING clause with DML
Описание слайда:
Objectives After completing this lesson, you should be able to do the following: Create standard constants and exceptions Write and call local subprograms Control the run-time privileges of a subprogram Perform autonomous transactions Pass parameters by reference using a NOCOPY hint Use the PARALLEL ENABLE hint for optimization Use the cross-session PL/SQL function result cache Use the DETERMINISTIC clause with functions Use bulk binding and the RETURNING clause with DML

Слайд 3


Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram, and performing autonomous transactions Using the NOCOPY and the PARALLEL ENABLE hints, the cross-session PL/SQL function result cache, and the DETERMINISTIC clause Using bulk binding and the RETURNING clause with DML
Описание слайда:
Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram, and performing autonomous transactions Using the NOCOPY and the PARALLEL ENABLE hints, the cross-session PL/SQL function result cache, and the DETERMINISTIC clause Using bulk binding and the RETURNING clause with DML

Слайд 4


Standardizing Constants and Exceptions Constants and exceptions are typically implemented using a bodiless package (that is, a package specification). Standardizing helps to: Develop programs that are consistent Promote a higher degree of code reuse Ease code maintenance Implement company standards across entire applications Start with standardization of: Exception names Constant definitions
Описание слайда:
Standardizing Constants and Exceptions Constants and exceptions are typically implemented using a bodiless package (that is, a package specification). Standardizing helps to: Develop programs that are consistent Promote a higher degree of code reuse Ease code maintenance Implement company standards across entire applications Start with standardization of: Exception names Constant definitions

Слайд 5


Standardizing Exceptions Create a standardized error-handling package that includes all named and programmer-defined exceptions to be used in the application.
Описание слайда:
Standardizing Exceptions Create a standardized error-handling package that includes all named and programmer-defined exceptions to be used in the application.

Слайд 6


Standardizing Exception Handling Consider writing a subprogram for common exception handling to: Display errors based on SQLCODE and SQLERRM values for exceptions Track run-time errors easily by using parameters in your code to identify: The procedure in which the error occurred The location (line number) of the error RAISE_APPLICATION_ERROR using stack trace capabilities, with the third argument set to TRUE
Описание слайда:
Standardizing Exception Handling Consider writing a subprogram for common exception handling to: Display errors based on SQLCODE and SQLERRM values for exceptions Track run-time errors easily by using parameters in your code to identify: The procedure in which the error occurred The location (line number) of the error RAISE_APPLICATION_ERROR using stack trace capabilities, with the third argument set to TRUE

Слайд 7


Standardizing Constants For programs that use local variables whose values should not change: Convert the variables to constants to reduce maintenance and debugging Create one central package specification and place all constants in it
Описание слайда:
Standardizing Constants For programs that use local variables whose values should not change: Convert the variables to constants to reduce maintenance and debugging Create one central package specification and place all constants in it

Слайд 8


Local Subprograms A local subprogram is a PROCEDURE or FUNCTION defined at the end of the declarative section.
Описание слайда:
Local Subprograms A local subprogram is a PROCEDURE or FUNCTION defined at the end of the declarative section.

Слайд 9


Definer’s Rights Versus Invoker’s Rights Definer’s rights: Used prior to Oracle8i Programs execute with the privileges of the creating user. User does not require privileges on underlying objects that the procedure accesses. User requires privilege only to execute a procedure.
Описание слайда:
Definer’s Rights Versus Invoker’s Rights Definer’s rights: Used prior to Oracle8i Programs execute with the privileges of the creating user. User does not require privileges on underlying objects that the procedure accesses. User requires privilege only to execute a procedure.

Слайд 10


Specifying Invoker’s Rights: Setting AUTHID to CURRENT_USER When used with stand-alone functions, procedures, or packages: Names used in queries, DML, Native Dynamic SQL, and DBMS_SQL package are resolved in the invoker’s schema Calls to other packages, functions, and procedures are resolved in the definer’s schema
Описание слайда:
Specifying Invoker’s Rights: Setting AUTHID to CURRENT_USER When used with stand-alone functions, procedures, or packages: Names used in queries, DML, Native Dynamic SQL, and DBMS_SQL package are resolved in the invoker’s schema Calls to other packages, functions, and procedures are resolved in the definer’s schema

Слайд 11


Autonomous Transactions Are independent transactions started by another main transaction Are specified with PRAGMA AUTONOMOUS_TRANSACTION
Описание слайда:
Autonomous Transactions Are independent transactions started by another main transaction Are specified with PRAGMA AUTONOMOUS_TRANSACTION

Слайд 12


Features of Autonomous Transactions Are independent of the main transaction Suspend the calling transaction until the autonomous transactions are completed Are not nested transactions Do not roll back if the main transaction rolls back Enable the changes to become visible to other transactions upon a commit Are started and ended by individual subprograms and not by nested or anonymous PL/SQL blocks
Описание слайда:
Features of Autonomous Transactions Are independent of the main transaction Suspend the calling transaction until the autonomous transactions are completed Are not nested transactions Do not roll back if the main transaction rolls back Enable the changes to become visible to other transactions upon a commit Are started and ended by individual subprograms and not by nested or anonymous PL/SQL blocks

Слайд 13


Using Autonomous Transactions: Example
Описание слайда:
Using Autonomous Transactions: Example

Слайд 14


Design Considerations for PL/SQL Code. (Lecture 3), слайд №14
Описание слайда:

Слайд 15


Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram, and performing autonomous transactions Using the NOCOPY and the PARALLEL ENABLE hints, the cross-session PL/SQL function result cache, and the DETERMINISTIC clause Using bulk binding and the RETURNING clause with DML
Описание слайда:
Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram, and performing autonomous transactions Using the NOCOPY and the PARALLEL ENABLE hints, the cross-session PL/SQL function result cache, and the DETERMINISTIC clause Using bulk binding and the RETURNING clause with DML

Слайд 16


Using the NOCOPY Hint Allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference rather than by value Enhances performance by reducing overhead when passing parameters
Описание слайда:
Using the NOCOPY Hint Allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference rather than by value Enhances performance by reducing overhead when passing parameters

Слайд 17


Effects of the NOCOPY Hint If the subprogram exits with an exception that is not handled: You cannot rely on the values of the actual parameters passed to a NOCOPY parameter Any incomplete modifications are not “rolled back” The remote procedure call (RPC) protocol enables you to pass parameters only by value.
Описание слайда:
Effects of the NOCOPY Hint If the subprogram exits with an exception that is not handled: You cannot rely on the values of the actual parameters passed to a NOCOPY parameter Any incomplete modifications are not “rolled back” The remote procedure call (RPC) protocol enables you to pass parameters only by value.

Слайд 18


When Does the PL/SQL Compiler Ignore the NOCOPY Hint? The NOCOPY hint has no effect if: The actual parameter: Is an element of an index-by table Is constrained (for example, by scale or NOT NULL) And formal parameter are records, where one or both records were declared by using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ Requires an implicit data type conversion The subprogram is involved in an external or remote procedure call
Описание слайда:
When Does the PL/SQL Compiler Ignore the NOCOPY Hint? The NOCOPY hint has no effect if: The actual parameter: Is an element of an index-by table Is constrained (for example, by scale or NOT NULL) And formal parameter are records, where one or both records were declared by using %ROWTYPE or %TYPE, and constraints on corresponding fields in the records differ Requires an implicit data type conversion The subprogram is involved in an external or remote procedure call

Слайд 19


Using the PARALLEL_ENABLE Hint Can be used in functions as an optimization hint Indicates that a function can be used in a parallelized query or parallelized DML statement
Описание слайда:
Using the PARALLEL_ENABLE Hint Can be used in functions as an optimization hint Indicates that a function can be used in a parallelized query or parallelized DML statement

Слайд 20


Using the Cross-Session PL/SQL Function Result Cache Each time a result-cached PL/SQL function is called with different parameter values, those parameters and their results are stored in cache. The function result cache is stored in a shared global area (SGA), making it available to any session that runs your application. Subsequent calls to the same function with the same parameters uses the result from cache. Performance and scalability are improved. This feature is used with functions that are called frequently and dependent on information that changes infrequently.
Описание слайда:
Using the Cross-Session PL/SQL Function Result Cache Each time a result-cached PL/SQL function is called with different parameter values, those parameters and their results are stored in cache. The function result cache is stored in a shared global area (SGA), making it available to any session that runs your application. Subsequent calls to the same function with the same parameters uses the result from cache. Performance and scalability are improved. This feature is used with functions that are called frequently and dependent on information that changes infrequently.

Слайд 21


Enabling Result-Caching for a Function You can make a function result-cached as follows: Include the RESULT_CACHE clause in the following: The function declaration The function definition Include an optional RELIES_ON clause to specify any tables or views on which the function results depend.
Описание слайда:
Enabling Result-Caching for a Function You can make a function result-cached as follows: Include the RESULT_CACHE clause in the following: The function declaration The function definition Include an optional RELIES_ON clause to specify any tables or views on which the function results depend.

Слайд 22


Declaring and Defining a Result-Cached Function: Example
Описание слайда:
Declaring and Defining a Result-Cached Function: Example

Слайд 23


Design Considerations for PL/SQL Code. (Lecture 3), слайд №23
Описание слайда:

Слайд 24


Using the DETERMINISTIC Clause with Functions Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments. This helps the optimizer avoid redundant function calls. If a function was called previously with the same arguments, the optimizer can elect to use the previous result. Do not specify DETERMINISTIC for a function whose result depends on the state of session variables or schema objects.
Описание слайда:
Using the DETERMINISTIC Clause with Functions Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments. This helps the optimizer avoid redundant function calls. If a function was called previously with the same arguments, the optimizer can elect to use the previous result. Do not specify DETERMINISTIC for a function whose result depends on the state of session variables or schema objects.

Слайд 25


Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram, and performing autonomous transactions Using the NOCOPY and the PARALLEL ENABLE hints, the cross-session PL/SQL function result cache, and the DETERMINISTIC clause Using bulk binding and the RETURNING clause with DML
Описание слайда:
Lesson Agenda Standardizing constants and exceptions, using local subprograms, controlling the run-time privileges of a subprogram, and performing autonomous transactions Using the NOCOPY and the PARALLEL ENABLE hints, the cross-session PL/SQL function result cache, and the DETERMINISTIC clause Using bulk binding and the RETURNING clause with DML

Слайд 26


Using the RETURNING Clause Improves performance by returning column values with INSERT, UPDATE, and DELETE statements Eliminates the need for a SELECT statement
Описание слайда:
Using the RETURNING Clause Improves performance by returning column values with INSERT, UPDATE, and DELETE statements Eliminates the need for a SELECT statement

Слайд 27


Bulk Binding Binds whole arrays of values in a single operation, rather than using a loop to perform a FETCH, INSERT, UPDATE, and DELETE operation multiple times
Описание слайда:
Bulk Binding Binds whole arrays of values in a single operation, rather than using a loop to perform a FETCH, INSERT, UPDATE, and DELETE operation multiple times

Слайд 28


Using Bulk Binding: Syntax and Keywords The FORALL keyword instructs the PL/SQL engine to bulk bind input collections before sending them to the SQL engine. The BULK COLLECT keyword instructs the SQL engine to bulk bind output collections before returning them to the PL/SQL engine.
Описание слайда:
Using Bulk Binding: Syntax and Keywords The FORALL keyword instructs the PL/SQL engine to bulk bind input collections before sending them to the SQL engine. The BULK COLLECT keyword instructs the SQL engine to bulk bind output collections before returning them to the PL/SQL engine.

Слайд 29


Design Considerations for PL/SQL Code. (Lecture 3), слайд №29
Описание слайда:

Слайд 30


Bulk Binding FORALL: Example
Описание слайда:
Bulk Binding FORALL: Example

Слайд 31


Design Considerations for PL/SQL Code. (Lecture 3), слайд №31
Описание слайда:

Слайд 32


Using BULK COLLECT INTO with Queries The SELECT statement has been enhanced to support the BULK COLLECT INTO syntax.
Описание слайда:
Using BULK COLLECT INTO with Queries The SELECT statement has been enhanced to support the BULK COLLECT INTO syntax.

Слайд 33


Using BULK COLLECT INTO with Cursors The FETCH statement has been enhanced to support the BULK COLLECT INTO syntax.
Описание слайда:
Using BULK COLLECT INTO with Cursors The FETCH statement has been enhanced to support the BULK COLLECT INTO syntax.

Слайд 34


Using BULK COLLECT INTO with a RETURNING Clause
Описание слайда:
Using BULK COLLECT INTO with a RETURNING Clause

Слайд 35


FORALL Support for Sparse Collections
Описание слайда:
FORALL Support for Sparse Collections

Слайд 36


Using Bulk Binds in Sparse Collections The typical application for this feature is an order entry and order processing system where: Users enter orders through the Web Orders are placed in a staging table before validation Data is later validated based on complex business rules (usually implemented programmatically using PL/SQL) Invalid orders are separated from valid ones Valid orders are inserted into a permanent table for processing
Описание слайда:
Using Bulk Binds in Sparse Collections The typical application for this feature is an order entry and order processing system where: Users enter orders through the Web Orders are placed in a staging table before validation Data is later validated based on complex business rules (usually implemented programmatically using PL/SQL) Invalid orders are separated from valid ones Valid orders are inserted into a permanent table for processing

Слайд 37


Using Bulk Bind with Index Array
Описание слайда:
Using Bulk Bind with Index Array

Слайд 38


Quiz The NOCOPY hint allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference rather than by value. This enhances performance by reducing overhead when passing parameters True False
Описание слайда:
Quiz The NOCOPY hint allows the PL/SQL compiler to pass OUT and IN OUT parameters by reference rather than by value. This enhances performance by reducing overhead when passing parameters True False

Слайд 39


Summary In this lesson, you should have learned how to: Create standard constants and exceptions Write and call local subprograms Control the run-time privileges of a subprogram Perform autonomous transactions Pass parameters by reference using a NOCOPY hint Use the PARALLEL ENABLE hint for optimization Use the cross-session PL/SQL function result cache Use the DETERMINISTIC clause with functions Use bulk binding and the RETURNING clause with DML
Описание слайда:
Summary In this lesson, you should have learned how to: Create standard constants and exceptions Write and call local subprograms Control the run-time privileges of a subprogram Perform autonomous transactions Pass parameters by reference using a NOCOPY hint Use the PARALLEL ENABLE hint for optimization Use the cross-session PL/SQL function result cache Use the DETERMINISTIC clause with functions Use bulk binding and the RETURNING clause with DML

Слайд 40


Practice: Overview This practice covers the following topics: Creating a package that uses bulk fetch operations Creating a local subprogram to perform an autonomous transaction to audit a business operation
Описание слайда:
Practice: Overview This practice covers the following topics: Creating a package that uses bulk fetch operations Creating a local subprogram to perform an autonomous transaction to audit a business operation

Слайд 41


Design Considerations for PL/SQL Code. (Lecture 3), слайд №41
Описание слайда:

Слайд 42


Design Considerations for PL/SQL Code. (Lecture 3), слайд №42
Описание слайда:

Скачать

Похожие презентации
Mypresentation.ru
Загрузить презентацию