Design Considerations for PL/SQL Code. (Lecture 3) - презентация, доклад, проект скачать

Нажмите для полного просмотра!

Design Considerations for PL/SQL Code. (Lecture 3), слайд №1

Design Considerations for PL/SQL Code. (Lecture 3), слайд №2

Design Considerations for PL/SQL Code. (Lecture 3), слайд №3

Design Considerations for PL/SQL Code. (Lecture 3), слайд №4

Design Considerations for PL/SQL Code. (Lecture 3), слайд №5

Design Considerations for PL/SQL Code. (Lecture 3), слайд №6

Design Considerations for PL/SQL Code. (Lecture 3), слайд №7

Design Considerations for PL/SQL Code. (Lecture 3), слайд №8

Design Considerations for PL/SQL Code. (Lecture 3), слайд №9

Design Considerations for PL/SQL Code. (Lecture 3), слайд №10

Design Considerations for PL/SQL Code. (Lecture 3), слайд №11

Design Considerations for PL/SQL Code. (Lecture 3), слайд №12

Design Considerations for PL/SQL Code. (Lecture 3), слайд №13

Design Considerations for PL/SQL Code. (Lecture 3), слайд №14

Design Considerations for PL/SQL Code. (Lecture 3), слайд №15

Design Considerations for PL/SQL Code. (Lecture 3), слайд №16

Design Considerations for PL/SQL Code. (Lecture 3), слайд №17

Design Considerations for PL/SQL Code. (Lecture 3), слайд №18

Design Considerations for PL/SQL Code. (Lecture 3), слайд №19

Design Considerations for PL/SQL Code. (Lecture 3), слайд №20

Design Considerations for PL/SQL Code. (Lecture 3), слайд №21

Design Considerations for PL/SQL Code. (Lecture 3), слайд №22

Design Considerations for PL/SQL Code. (Lecture 3), слайд №23

Design Considerations for PL/SQL Code. (Lecture 3), слайд №24

Design Considerations for PL/SQL Code. (Lecture 3), слайд №25

Design Considerations for PL/SQL Code. (Lecture 3), слайд №26

Design Considerations for PL/SQL Code. (Lecture 3), слайд №27

Design Considerations for PL/SQL Code. (Lecture 3), слайд №28

Design Considerations for PL/SQL Code. (Lecture 3), слайд №29

Design Considerations for PL/SQL Code. (Lecture 3), слайд №30

Design Considerations for PL/SQL Code. (Lecture 3), слайд №31

Design Considerations for PL/SQL Code. (Lecture 3), слайд №32

Design Considerations for PL/SQL Code. (Lecture 3), слайд №33

Design Considerations for PL/SQL Code. (Lecture 3), слайд №34

Design Considerations for PL/SQL Code. (Lecture 3), слайд №35

Design Considerations for PL/SQL Code. (Lecture 3), слайд №36

Design Considerations for PL/SQL Code. (Lecture 3), слайд №37

Design Considerations for PL/SQL Code. (Lecture 3), слайд №38

Design Considerations for PL/SQL Code. (Lecture 3), слайд №39

Design Considerations for PL/SQL Code. (Lecture 3), слайд №40

Design Considerations for PL/SQL Code. (Lecture 3), слайд №41

Design 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

Слайд 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

Слайд 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

Слайд 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

Слайд 5

Описание слайда:

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

Слайд 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

Слайд 8

Описание слайда:

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.

Слайд 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

Слайд 11

Описание слайда:

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

Слайд 13

Описание слайда:

Using Autonomous Transactions: Example

Слайд 14

Описание слайда:

Слайд 15

Описание слайда:

Слайд 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

Слайд 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.

Слайд 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

Слайд 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

Слайд 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.

Слайд 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.

Слайд 22

Описание слайда:

Declaring and Defining a Result-Cached Function: Example

Слайд 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.

Слайд 25

Описание слайда:

Слайд 26

Описание слайда:

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

Слайд 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.

Слайд 29

Описание слайда:

Слайд 30

Описание слайда:

Bulk Binding FORALL: Example

Слайд 31

Описание слайда:

Слайд 32

Описание слайда:

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.

Слайд 34

Описание слайда:

Using BULK COLLECT INTO with a RETURNING Clause

Слайд 35

Описание слайда:

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

Слайд 37

Описание слайда:

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

Слайд 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

Слайд 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