Oracle DBA Forum  

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

Ответ
 
Опции темы Опции просмотра
  #1  
Старый 26.12.2009, 17:17
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию 02 Declaring PL/SQL Variables

__________________
Чат форума (требуется аккаунт на github или twitter)
Ответить с цитированием
  #2  
Старый 26.12.2009, 17:17
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Objectives



Lesson Aim

You have already learned about basic PL/SQL blocks and their sections. In this lesson, you learn about valid and invalid identifiers. You learn how to declare and initialize variables in the declarative section of a PL/SQL block. The lesson describes the various data types. You also learn about the %TYPE attribute and its benefits.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 10:22.
Ответить с цитированием
  #3  
Старый 26.12.2009, 17:18
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Use of Variables



Use of Variables

With PL/SQL you can declare variables and then use them in SQL and procedural statements.

Variables are mainly used for storage of data and manipulation of stored values. Consider the SQL statement shown in the slide. The statement retrieves the first_name and department_id from the table. If you have to manipulate the first_name or the department_id, then you have to store the retrieved value. Variables are used to temporarily store the value. You can use the value stored in these variables for processing and manipulating the data. Variables can store any PL/SQL object, such as variables, types, cursors, and subprograms.

Reusability is another advantage of declaring variables. After they are declared, variables can be used repeatedly in an application by referring to them in the statements.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 10:24.
Ответить с цитированием
  #4  
Старый 26.12.2009, 17:18
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Identifiers



Identifiers

Identifiers are mainly used to provide conventions for naming variables. The rules for naming a variable are listed in the slide.

What Is the Difference Between a Variable and an Identifier?

Identifiers are names of variables. Variables are storage locations of data. Data is stored in memory. Variables point to this memory location where data can be read and modified. Identifiers are used to name PL/SQL objects (such as variables, types, cursors, and subprograms). Variables are used to store PL/SQL objects.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 10:25.
Ответить с цитированием
  #5  
Старый 26.12.2009, 17:18
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Handling Variables in PL/SQL



Handling Variables in PL/SQL

Declared and Initialized in the Declaration Section

You can declare variables in the declarative part of any PL/SQL block, subprogram, or package. Declarations allocate storage space for a value, specify its data type, and name the storage location so that you can reference it. Declarations can also assign an initial value and impose the NOT NULL constraint on the variable. Forward references are not allowed. You must declare a variable before referencing it in other statements, including other declarative statements.

Used and Assigned New Values in the Executable Section

In the executable section, the existing value of the variable can be replaced with the new value.

Passed as Parameters to PL/SQL Subprograms

Subprograms can take parameters. You can pass variables as parameters to subprograms.

Used to Hold the Output of a PL/SQL Subprogram

You have learned that the only difference between procedures and functions is that functions must return a value. Variables can be used to hold the value that is returned by a function.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 10:27.
Ответить с цитированием
  #6  
Старый 26.12.2009, 17:18
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Declaring and Initializing PL/SQL Variables




Declaring and Initializing PL/SQL Variables

You must declare all PL/SQL identifiers in the declaration section before referencing them in the PL/SQL block. You have the option of assigning an initial value to a variable (as shown in the slide). You do not need to assign a value to a variable in order to declare it. If you refer to other variables in a declaration, you must be sure to declare them separately in a previous statement.

In the syntax:
  • identifier Is the name of the variable
  • CONSTANT Constrains the variable so that its value cannot change (Constants must be initialized.)
  • data type Is a scalar, composite, reference, or LOB data type (This course covers only scalar, composite, and LOB data types.)
  • NOT NULL Constrains the variable so that it must contain a value (NOT NULL variables must be initialized.)
  • expr Is any PL/SQL expression that can be a literal expression, another variable, or an expression involving operators and functions

Note: In addition to variables, you can also declare cursors and exceptions in the declarative section. You learn how to declare cursors and exceptions later in the course.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 28.01.2010 в 21:55.
Ответить с цитированием
  #7  
Старый 26.12.2009, 17:18
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Declaring and Initializing PL/SQL Variables




Declaring and Initializing PL/SQL Variables (continued)

Examine the two code blocks in the slide.

1. The variable Myname is declared in the declarative section of the block. This variable can be accessed in the executable section of the same block. A value John is assigned to the variable in the executable section. String literals must be enclosed in single quotation marks. If your string has a quotation mark as in Todays Date, then the string would be Todays Date. := is the assignment operator. The procedure PUT_LINE is invoked by passing the variable Myname. The value of the variable is concatenated with the string My name is: . The output of this anonymous block is:



2. In the second block, the variable Myname is declared and initialized in the declarative section. Myname holds the value John after initialization. This value is manipulated in the executable section of the block. The output of this anonymous block is:

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

Последний раз редактировалось Marley; 28.01.2010 в 22:03.
Ответить с цитированием
  #8  
Старый 26.12.2009, 17:18
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Delimiters in String Literals



Delimiters in String Literals

If your string contains an apostrophe (identical to a single quotation mark), you must double the quotation mark, as in the following example:

Код:
event VARCHAR2(15):='Father''s day';
The first quotation mark acts as the escape character. This makes your string complicated, especially if you have SQL statements as strings. You can specify any character that is not present in the string as delimiter. The slide shows how to use the q' notation to specify the delimiter. The examples use ! and [ as delimiters. Consider the following example:

Код:
event := q'!Father's day!';
You can compare this with the first example on this notes page. You start the string with q' if you want to use a delimiter. The character following the notation is the delimiter used. Enter your string after specifying the delimiter, close the delimiter, and close the notation with a single quotation mark. The following example shows how to use [ as a delimiter:

Код:
event := q'[Mother's day]';
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 10:38.
Ответить с цитированием
  #9  
Старый 26.12.2009, 17:19
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Types of Variables



Types of Variables

All PL/SQL variables have a data type, which specifies a storage format, constraints, and a valid range of values. PL/SQL supports five data type categoriesscalar, composite, reference, large object (LOB), and objectthat you can use for declaring variables, constants, and pointers.
  • Scalar data types: Scalar data types hold a single value. The value depends on the data type of the variable. For example, the variable Myname in the example in slide 7 is of type VARCHAR2. Therefore, Myname can hold a string value. PL/SQL also supports Boolean variables.
  • Composite data types: Composite data types contain internal elements that are either scalar or composite. Record and table are examples of composite data types.
  • Reference data types: Reference data types hold values, called pointers, that point to a storage location.
  • LOB data types: LOB data types hold values, called locators, that specify the location of large objects (such as graphic images) that are stored out of line.

Non-PL/SQL variables include host language variables declared in precompiler programs, screen fields in Forms applications, and iSQL*Plus host variables. You learn about host variables later in this lesson.

For more information about LOBs, see the PL/SQL Users Guide and Reference.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 10:50.
Ответить с цитированием
  #10  
Старый 26.12.2009, 17:19
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Types of Variables (continued)



Types of Variables (continued)

The slide illustrates the following data types:
  • TRUE represents a Boolean value.
  • 25-JAN-01 represents a DATE.
  • The image represents a BLOB.
  • The text of the proverb can represent a VARCHAR2 data type or a CLOB.
  • 256120.08 represents a NUMBER data type with precision and scale.
  • The film reel represents a BFILE.
  • The city name Atlanta represents a VARCHAR2.
__________________
Чат форума (требуется аккаунт на github или twitter)

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

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

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

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


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


Powered by vBulletin®