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:24
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию 03 Writing Executable Statements

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



Lesson Aim

You have learned how to declare variables and write executable statements in a PL/SQL block. In this lesson, you learn how lexical units make up a PL/SQL block. You learn to write nested blocks. You also learn about the scope and visibility of variables in the nested blocks and about qualifying them with labels.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 15:34.
Ответить с цитированием
  #3  
Старый 26.12.2009, 17:24
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Lexical Units in a PL/SQL Block



Lexical Units in a PL/SQL Block

Lexical units include letters, numerals, special characters, tabs, spaces, returns, and symbols.

Identifiers: Identifiers are the names given to PL/SQL objects. You have learned to identify valid and invalid identifiers. Recall that keywords cannot be used as identifiers.

Quoted Identifiers :

- Make identifiers case sensitive
- Include characters such as spaces
- Use reserved words

Examples:

"begin date" DATE;
"end date" DATE;
"exception thrown" BOOLEAN DEFAULT TRUE;

All subsequent usage of these variables should have double quotation marks.

Delimiters: Delimiters are symbols that have special meaning. You have already learned that the semicolon (;) is used to terminate a SQL or PL/SQL statement. Therefore, ; is the best example of a delimiter.

For more information, please refer to the PL/SQL Users Guide and Reference.

Delimiters are simple or compound symbols that have special meaning in PL/SQL.

Simple Symbols


Картинки

Compound Symbols


Картинки


Note: This is only a subset and not a complete list of delimiters.

Literals: Any value that is assigned to a variable is a literal. Any character, numeral, Boolean, or date value that is not an identifier is a literal. Literals are classified as:

- Character literals: All string literals have the data type CHAR and are therefore called character literals (for example, John, 12C, 1234, and 12-JAN-1923).
- Numeric literals: A numeric literal represents an integer or real value (for example, 428 and 1.276).
- Boolean literals: Values that are assigned to Boolean variables are Boolean literals. TRUE, FALSE, and NULL are Boolean literals or keywords.

Comments: It is good programming practice to explain what a piece of code is trying to achieve. When you include the explanation in a PL/SQL block, the compiler cannot interpret these instructions. There should be a way in which you can indicate that these instructions need not be compiled. Comments are mainly used for this purpose. Any instruction that is commented is not interpreted by the compiler.

- Two hyphens (--) are used to comment a single line.
- The beginning and ending comment delimiters (/* and */) are used to comment multiple lines.
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 15:42.
Ответить с цитированием
  #4  
Старый 26.12.2009, 17:24
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию PL/SQL Block Syntax and Guidelines



PL/SQL Block Syntax and Guidelines

A literal is an explicit numeric, character string, date, or Boolean value that is not represented by an identifier.
  • Character literals include all the printable characters in the PL/SQL character set: letters, numerals, spaces, and special symbols.
  • Numeric literals can be represented either by a simple value (for example, 32.5) or in scientific notation (for example, 2E5 means 2 * 105 = 200,000).
__________________
Чат форума (требуется аккаунт на github или twitter)

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



Commenting Code

You should comment code to document each phase and to assist debugging. Comment the PL/SQL code with two hyphens (--) if the comment is on a single line, or enclose the comment between the symbols /* and */ if the comment spans several lines.

Comments are strictly informational and do not enforce any conditions or behavior on logic or data. Well-placed comments are extremely valuable for code readability and future code maintenance. In the example in the slide, the lines enclosed within /* and */ indicate a comment that explains the following code.
__________________
Чат форума (требуется аккаунт на github или twitter)

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



SQL Functions in PL/SQL

SQL provides a number of predefined functions that can be used in SQL statements. Most of these functions are valid in PL/SQL expressions.

The following functions are not available in procedural statements:
  • DECODE
  • Group functions: AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE Group functions apply to groups of rows in a table and therefore are available only in SQL statements in a PL/SQL block.

The functions mentioned here are only a subset of the complete list.
__________________
Чат форума (требуется аккаунт на github или twitter)

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



SQL Functions in PL/SQL: Examples

SQL functions help you to manipulate data. They are grouped into the following categories:
  • Number
  • Character
  • Conversion
  • Date
  • Miscellaneous
__________________
Чат форума (требуется аккаунт на github или twitter)

Последний раз редактировалось Marley; 26.01.2010 в 15:47.
Ответить с цитированием
  #8  
Старый 26.12.2009, 17:25
Marley Marley вне форума
Senior Member
 
Регистрация: 19.09.2009
Сообщений: 7,384
По умолчанию Data Type Conversion



Data Type Conversion

In any programming language, converting one data type to another is a common requirement. PL/SQL can handle such conversions with scalar data types. Data type conversions can be of two types:

Implicit conversions: PL/SQL attempts to convert data types dynamically if they are mixed in a statement. Consider the following example:

DECLARE
salary NUMBER(6):=6000;
sal_hike VARCHAR2(5):='1000';
total_salary salary%TYPE;
BEGIN
total_salary:=salary+sal_hike;
END;
/


In the example shown, the variable sal_hike is of type VARCHAR2. While calculating the total salary, PL/SQL first converts sal_hike to NUMBER and then performs the operation. The result is of the NUMBER type.

Implicit conversions can be between:
  • Characters and numbers
  • Characters and dates


Explicit conversions: To convert values from one data type to another, use built-in functions. For example, to convert a CHAR value to a DATE or NUMBER value, use TO_DATE or TO_NUMBER, respectively.
__________________
Чат форума (требуется аккаунт на github или twitter)

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



Data Type Conversion (continued)

Implicit and explicit conversions of the DATE data type:

1. This example of implicit conversion assigns the date date_of_joining.

2. PL/SQL gives you an error because the date that is being assigned is not in the default format.

3. Use the TO_DATE function to explicitly convert the given date in a particular format and assign it to the DATE data type variable date_of_joining.
__________________
Чат форума (требуется аккаунт на github или twitter)

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



Nested Blocks

One of the advantages of PL/SQL (compared to SQL) is the ability to nest statements. You can nest blocks wherever an executable statement is allowed, thus making the nested block a statement. If your executable section has code for many logically related functionalities to support multiple business requirements, you can divide the executable section into smaller blocks. The exception section can also contain nested blocks.
__________________
Чат форума (требуется аккаунт на github или twitter)

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

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

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

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


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


Powered by vBulletin®