Oracle DBA Forum  

Вернуться   Oracle DBA Forum > Oracle University Official Study Notes (ENG) > Oracle 10g PL/SQL Advanced > Oracle 10g PL/SQL Advanced

Ответ
 
Опции темы Опции просмотра
  #1  
Старый 26.12.2009, 18:26
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию 02 Design Considerations

__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:16.
Ответить с цитированием
  #2  
Старый 26.12.2009, 18:26
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Objectives



Objectives

This lesson discusses several concepts that apply to the designing of PL/SQL program units. This lesson explains how to:
  • Design and use cursor variables
  • Describe the predefined data types
  • Create subtypes based on existing data types for an application
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:17.
Ответить с цитированием
  #3  
Старый 26.12.2009, 18:28
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Guidelines for Cursor Design



Guidelines for Cursor Design

When fetching from a cursor, fetch into a record. This way you do not need to declare individual variables, and you reference only the values you want to use. Additionally, you can automatically use the structure of the SELECT column list.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:17.
Ответить с цитированием
  #4  
Старый 26.12.2009, 18:29
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Guidelines for Cursor Design (continued)



Guidelines for Cursor Design (continued)

Whenever you have a need to use a cursor in more than one place with different values for the WHERE clause, create parameters for your cursor. Parameters increase the flexibility and reusability of cursors, because you can pass different values to the WHERE clause when you open a cursor, rather than hard-code a value for the WHERE clause.

Additionally, parameters help you avoid scoping problems, because the result set for the cursor is not tied to a specific variable in a program. You can define a cursor at a higher level and use it in any subblock with variables defined in the local block.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:17.
Ответить с цитированием
  #5  
Старый 26.12.2009, 18:30
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Guidelines for Cursor Design (continued)




Guidelines for Cursor Design (continued)

If you are using an implicit cursor and reference a SQL cursor attribute, make sure you reference it immediately after a SQL statement is executed. This is because SQL cursor attributes are set on the result of the most recently executed SQL statement. The SQL statement can be executed in another program. Referencing a SQL cursor attribute immediately after a SQL statement executes ensures that you are dealing with the result of the correct SQL statement.

In the example in the slide, you cannot rely on the value of SQL%NOTFOUND for the UPDATE statement, because it is likely to be overwritten by the value of another SQL statement in the get_avg_order procedure. To ensure accuracy, the cursor attribute function SQL%NOTFOUND needs to be called immediately after the DML statement:

DECLARE
v_flag BOOLEAN;
BEGIN
UPDATE customers
SET credit_limit = p_credit_limit
WHERE customer_id = p_cust_id;
v_flag := SQL%NOTFOUND
get_avg_order(p_cust_id); -- procedure call
IF v_flag THEN
...
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:17.
Ответить с цитированием
  #6  
Старый 26.12.2009, 18:37
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Guidelines for Cursor Design (continued)



Guidelines for Cursor Design (continued)

Whenever possible, use cursor FOR loops that simplify coding. Cursor FOR loops reduce the volume of code you need to write to fetch data from a cursor and also reduce the chances of introducing loop errors in your code.

A cursor FOR loop automatically handles the open, fetch, and close operations, as well as, defines a record type that matches the cursor definition. After it processes the last row the cursor is closed automatically. If you do not use a CURSOR FOR loop, forgetting to close your cursor results in increased memory usage.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:17.
Ответить с цитированием
  #7  
Старый 26.12.2009, 18:40
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Guidelines for Cursor Design (continued)



Guidelines for Cursor Design (continued)
  • If you do not need a cursor any longer, close it explicitly. If your cursor is in a package, its scope is not limited to any particular PL/SQL block. The cursor remains open until you explicitly close it. An open cursor takes up memory space and continues to maintain row-level locks, if created with the FOR UPDATE clause, until a commit or rollback. Closing the cursor releases memory. Ending the transaction by committing or rolling back releases the locks. Along with a FOR UPDATE clause you can also use a WHERE CURRENT OF clause with the DML statements inside the FOR loop. This automatically performs a DML transaction for the current row in the cursors result set, thereby improving performance.

    Note: It is a good programming practice to explicitly close your cursors. Leaving cursors open can generate an exception because the number of cursors allowed to remain open within a session is limited.
  • Make sure that you use column aliases in your cursor for calculated columns that you fetch into a record declared with a %ROWTYPE declaration. You also need column aliases if you want to reference the calculated column in your program.

    The code in the slide does not compile successfully because it lacks a column alias for the calculation credit_limit*1.1. After you give it an alias, use the same alias later in the code to make a reference to the calculation.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:17.
Ответить с цитированием
  #8  
Старый 26.12.2009, 18:42
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Cursor Variables



Cursor Variables: Overview

Like a cursor, a cursor variable points to the current row in the result set of a multirow query. Cursor variables, however, are like C pointers: they hold the memory location of an item instead of the item itself. In this way, cursor variables differ from cursors the way constants differ from variables. A cursor is static, a cursor variable is dynamic. In PL/SQL, a cursor variable has a REF CURSOR data type, where REF stands for reference, and CURSOR stands for the class of the object.

Using Cursor Variables

To execute a multirow query, the Oracle server opens a work area called a cursor to store processing information. To access the information, you either explicitly name the work area, or you use a cursor variable that points to the work area. Whereas a cursor always refers to the same work area, a cursor variable can refer to different work areas. Therefore, cursors and cursor variables are not interoperable.

An explicit cursor is static and is associated with one SQL statement. A cursor variable can be associated with different statements at run time.

Primarily you use a cursor variable to pass a pointer to query result sets between PL/SQL stored subprograms and various clients such as a Developer Forms application. None of them owns the result set. They simply share a pointer to the query work area that stores the result set.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:17.
Ответить с цитированием
  #9  
Старый 26.12.2009, 18:42
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Working with Cursor Variables



Working with Cursor Variables

There are four steps for handling a cursor variable. The next few sections contain detailed information about each step.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:17.
Ответить с цитированием
  #10  
Старый 26.12.2009, 18:42
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,211
По умолчанию Strong Versus Weak Cursor Variables



Strong Versus Weak Cursor Variables

REF CURSOR types can be strong (restrictive) or weak (nonrestrictive). A strong REF CURSOR type definition specifies a return type, a weak definition does not. PL/SQL enables you to associate a strong type with type-compatible queries only, whereas a weak type can be associated with any query. This makes strong REF CURSOR types less error prone, but weak REF CURSOR types more flexible.

In the following example, the first definition is strong, whereas the second is said to be weak:

DECLARE
TYPE rt_cust IS REF CURSOR RETURN customers%ROWTYPE;
TYPE rt_general_purpose IS REF CURSOR;
...
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 04.10.2015 в 04:18.
Ответить с цитированием
Ответ
Опции темы
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.

Быстрый переход


Текущее время: 13:03. Часовой пояс GMT +3.


Powered by vBulletin®