When someone wishes to remain anonymous, that person goes unnamed. So it is with the anonymous PL/SQL block. Unlike the other two types of PL/SQL blocks (the procedure and the function), the anonymous block has no name associated with it. In fact, the anonymous block is missing the header section altogether. Instead it simply uses the DECLARE reserved word to mark the beginning of its optional declaration section.
Figure 15.4: An anonymous block without declaration and exception sections
Figure 15.5: An anonymous block defined inside a procedure
Without a name, the anonymous block cannot be called by any other block -- it doesn"t have a handle for reference. Instead, anonymous blocks serve as scripts that execute PL/SQL statements, including calls to procedures and functions. Anonymous blocks can also serve as nested blocks inside procedures, functions, and other anonymous blocks, as shown in .
Because an anonymous block can have its own declaration and exception sections, developers use anonymous blocks to provide a scope for identifiers and exception handling within a larger program.
15.3.1 The Structure of an Anonymous Block
The general format of an anonymous PL/SQL block is as follows:
[ DECLARE ... optional declaration statements ... ] BEGIN ... executable statements ... [ EXCEPTION ... optional exception handler statements ... ] END;
The square brackets indicate an optional part of the syntax. As you can see, just about everything in an anonymous block is optional. You must have BEGIN and END statements and you must have at least one executable statement.
15.3.2 Examples of Anonymous Blocks
The following examples show the different combinations of block sections, all of which comprise valid PL/SQL blocks:
An anonymous block with a declaration section, but no exception section:
DECLARE right_now DATE:= SYSDATE; BEGIN hiredate:= right_now; END
An anonymous block containing declaration, execution, and exception sections:
DECLARE right_now DATE:= SYSDATE; too_late EXCEPTION; BEGIN IF:employee.hiredate < ADD_MONTHS (right_now, 6) THEN RAISE too_late; ELSE:employee.hiredate:= right_now; END IF; EXCEPTION WHEN too_late THEN DBMS_OUTPUT.PUT_LINE ("You no longer qualify for free air."); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ("Error encountered: " || SQLCODE); END;
A block with BEGIN-END terminators, but no declaration or exception sections:
BEGIN hiredate:= SYSDATE; END
Anonymous blocks execute a series of statements and then terminate, thus acting like procedures. In fact, all anonymous blocks are anonymous block procedures.
15.3.3 Anonymous Blocks in the Oracle Tools
Anonymous blocks are used in Oracle tools where PL/SQL code is either executed immediately (SQL*Plus) or attached directly to objects in that tool environment (see Table 15.1). That object then provides the context or name for the PL/SQL code, making the anonymous block both the most appropriate and the most efficient way to attach programmatic functionality to the object.
|
Oracle Developer/2000 |
Place PL/SQL code directly in the Oracle Forms or Oracle Reports trigger, which is then packaged as an anonymous block by the tool and sent to the PL/SQL engine. |
|
|
Database trigger |
Record or column of table |
The body of the trigger is coded in PL/SQL. While the trigger has a name, the PL/SQL code itself is unnamed, hence anonymous. |
|
SQL*Plus and SQL*DBA |
Ad hoc programs and batch processing scripts written in SQL*Plus are always anonymous blocks (which may then call procedures or functions). |
|
|
Embedded PL/SQL programs |
Pro* embedded languages |
Embed PL/SQL blocks to execute statements inside the database server. |
Whenever you attach PL/SQL code to a trigger or field in a tool, that code forms an anonymous PL/SQL block. When you write this code you can enter a fully specified PL/SQL block (declaration, execution, and exception sections), or you can enter only the executable section.
15.3.4 Nested Blocks
PL/SQL allows you to nest or embed anonymous blocks within another PL/SQL block. You can also nest anonymous blocks within anonymous blocks for more than one level, as shown in .
Figure 15.6: Anonymous blocks nested three levels deep
15.3.4.1 Nested block terminology
A PL/SQL block nested within another PL/SQL block may be called by any of the following: nested block, enclosed block, child block or sub-block.
A PL/SQL block that calls another PL/SQL block (anonymous or named) may be referred to as either the enclosing block or the parent block.
15.3.4.2 Nested blocks provide scope
The general advantage of a nested block is that you create a scope for all the declared objects and executable statements in that block. You can use this scope to improve your control over activity in your program. For a discussion, see Section 15.3.5, "Scope and Visibility" later in this chapter.
Consider the following procedure, in which the president and vice-president of the specified company request their salaries be cut in half if their current salaries are more than ten times the average salary in the company. It"s the sort of incentive plan that would encourage executives to "share the wealth."
PROCEDURE update_management (company_id_in IN NUMBER, avgsal_in IN NUMBER) IS BEGIN -- The vice-president shows his generosity... BEGIN SELECT salary INTO v_sal FROM employee WHERE company_id = company_id_in AND title = "VICE-PRESIDENT"; IF v_sal > avgsal_in * 10 THEN UPDATE employee SET salary:= salary * .50 WHERE company_id = company_id_in AND title = "VICE-PRESIDENT"; ELSE DBMS_OUTPUT.PUT_LINE ("The VP is OK!"); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- The president shows her generosity... BEGIN SELECT salary INTO v_sal FROM employee WHERE company_id = company_id_in AND title = "PRESIDENT"; IF v_sal > avgsal_in * 10 THEN UPDATE employee SET salary:= salary * .50 WHERE company_id = company_id_in AND title = `PRESIDENT"; ELSE DBMS_OUTPUT.PUT_LINE ("The Prez is a pal!"); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END;
Each of the two SELECT-UPDATE combinations is embedded in its own anonymous PL/SQL block. Each block has its own exception section. Why go to all this trouble? Why couldn"t the programmer just create a little script that will update the salary for a specified title? The following statement, saved to the updemp.sql file, would "do the trick" (&N is the syntax used to supply arguments to a SQL*Plus script):
UPDATE employee SET salary:= salary * .50 WHERE company_id = &1 AND title = "&2" AND salary > &3 * 10;
and then execute the SQL script in SQL*Plus using the START command:
SQL> start updemp 1100 VICE-PRESIDENT SQL> start updemp 1100 PRESIDENT
The programmer who was assigned this task took several things into account:
The executives might decide they will want to take such actions repeatedly in the coming years; better to package the steps into a reusable chunk of code like a procedure than simply execute a series of SELECT-UPDATEs in SQL*Plus.
Executives come and go frequently at the company. There is no guarantee that there will be a person in the employee table with a title of `PRESIDENT" or `VICE-PRESIDENT" at any given time. The first assumption argues for the encapsulation of these two SELECT-UPDATE steps into a procedure. This second assumption results in the need for embedded PL/SQL blocks around each UPDATE statement. Suppose that the procedure update_management did not make use of the embedded blocks. The code would then look like this:
PROCEDURE update_management (company_id_in IN NUMBER, avgsal_in IN NUMBER) IS BEGIN SELECT salary INTO v_sal FROM employee WHERE company_id = company_id_in AND title = "VICE-PRESIDENT"; IF v_sal > avgsal_in * 10 THEN UPDATE employee SET salary:= salary * .50 WHERE company_id = company_id_in AND title = "VICE-PRESIDENT"; ELSE DBMS_OUTPUT.PUT_LINE ("The VP is OK!"); END IF; SELECT salary INTO v_sal FROM employee WHERE company_id = company_id_in AND title = "PRESIDENT"; IF v_sal > avgsal_in * 10 THEN UPDATE employee SET salary:= salary * .50 WHERE company_id = company_id_in AND title = "PRESIDENT"; ELSE DBMS_OUTPUT.PUT_LINE ("The Prez is a pal!"); END IF; END;
If there is a record in the employee table with the title of "VICE-PRESIDENT" (for the appropriate company_id) and if there is a record in the employee table with the title of "PRESIDENT," then this procedure works just fine. But what if there is no record with a title of "VICE-PRESIDENT"? What if the Vice-President did not want to take a 50% cut in pay and instead quit in disgust?
When the WHERE clause of an implict SELECT statement does not identify any records, PL/SQL raises the NO_DATA_FOUND exception (for more details on this phenomenon see Chapter 6, Database Interaction and Cursors , and pay particular attention to the sections concerning implicit cursors). There is no exception handler section in this procedure. As a result, when there is no Vice-President in sight, the procedure will immediately complete (well, abort, actually) and transfer control to the calling block"s exception section to see if the NO_DATA_FOUND exception is handled there. The SELECT-UPDATE for the President"s salary is therefore not executed at all.
NOTE: You don"t actually need a BEGIN-END block around the second SELECT-UPDATE. This is the last statement, and if it fails nothing will be skipped. It is good practice, however, to include an exception statement for the procedure as a whole to trap errors and handle them gracefully.
Our programmer would hate to see an executive"s wish go unfulfilled, so we need a mechanism to trap the failure of the first SELECT and allow PL/SQL procedure execution to continue on to the next SELECT-UPDATE. The embedded anonymous block offers this protection. By placing the first SELECT within a BEGIN-END envelope, you can also define an exception section just for the SELECT. Then, when the SELECT fails, control is passed to the exception handler for that specific block, not to the exception handler for the procedure as a whole (see ). If you then code an exception to handle NO_DATA_FOUND which allows for continued processing, the next SELECT-UPDATE will be executed.
Figure 15.7: An anonymous block "catches" the exception
15.3.4.3 Named modules offer scoping effect of nested block
You can also restructure the update_management procedure to avoid the problems addressed in the previous section, and also the redundancy in the code for that procedure. The following version of update_management uses an explicit cursor to avoid the problem of the NO_DATA_FOUND exception being raised. It also encapsulates the repetitive logic into a local or nested procedure.
PROCEDURE update_management (company_id_in IN NUMBER, avgsal_in IN NUMBER, decr_in IN NUMBER) IS CURSOR salcur (title_in IN VARCHAR2) IS SELECT salary FROM employee WHERE company_id = company_id_in AND title = title_in AND salary > avgsal_in * 10; PROCEDURE update_exec (title_in IN VARCHAR2) IS salrec salcur%ROWTYPE; BEGIN OPEN salcur (title_in); FETCH salcur INTO salrec; IF salcur%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE ("The " || title_in || " is OK!"); ELSE UPDATE employee SET salary:= salary * decr_in WHERE company_id = company_id_in AND title = title_in; END IF; CLOSE salcur; END; BEGIN update_exec ("VICE-PRESIDENT"); update_exec ("PRESIDENT"); END;
This final version also offers the advantage of consolidating the two SELECTs into a single explicit cursor, and the two UPDATEs into a single statement, thereby reducing the amount of code one would have to test and maintain. Whether you go with named or anonymous blocks, the basic concept of program (and therefore exception) scope remains the same. Use the scoping and visibility rules to your advantage by isolating areas of code and cleaning up the program flow.
15.3.5 Scope and Visibility
Two of the most important concepts related to a PL/SQL block are those of the scope and visibility of identifiers. An identifier is the name of a PL/SQL object, which could be anything from a variable to a program name. In order to manipulate a PL/SQL identifier (assign a value to it, pass it as a parameter, or call it in a module), you have to be able to reference that identifier in such a way that the code will compile.
The scope of an identifier is the part of a program in which you can make a reference to the identifier and have that reference resolved by the compiler. An identifier is visible in a program when it can be referenced using an unqualified name.
15.3.5.1 Qualified identifiers
A qualifier for an identifier can be a package name, module name (procedure or function), or loop label. You qualify the name of an identifer with dot notation, the same way you would qualify a column name with the name of its table.
The names of the following identifiers are qualified:
:GLOBAL.company_id
A global variable in Oracle Forms
Std_types.dollar_amount
A subtype declared in a package
The scope of an identifier is generally the block in which that identifier is declared. The following anonymous block declares and then references two local variables:
DECLARE first_day DATE; last_day DATE; BEGIN first_day:= SYSDATE; last_day:= ADD_MONTHS (first_day, 6); END;
Both the first_day and last_day variables are visible in this block. When I reference the variables in the assignment statements, I do not need to qualify their names. Also, the scope of the two variables is precisely this anonymous block.
I cannot make reference to either of those variables in a second anonymous block or in a procedure. Any attempt to do so will result in a compile failure, because the reference to those variables cannot be resolved.
If an identifier is declared or defined outside of the current PL/SQL block, it is visible (can be referenced without a qualifier) only under the following conditions:
The identifier is the name of a standalone procedure or function on which you have EXECUTE privilege. You can then include a call to this module in your block.
The identifier is declared in a block which encloses the current block.
15.3.5.2 Scope and nested blocks
Let"s take a closer look at nested blocks and the impact on scope and visibility. When you declare a variable in a PL/SQL block, then that variable is local to that block, but is visible in or global to any blocks defined within the first, enclosing block. For example, in the following anonymous block, the variable last_date can be referenced anywhere inside the block:
/* The enclosing or outer anonymous block. */ DECLARE last_date DATE; BEGIN last_date:= LAST_DAY (SYSDATE); /* The inner anonymous block. */ BEGIN IF last_date > :employee.hire_date THEN ... END IF; END; ... END;
Even though last_date is not defined in the inner block, it can still be referenced there without qualification because the inner block is defined inside the outer block. The last_date variable is therefore considered a global variable in the inner block.
Shows additional examples illustrating the concept of scope in PL/SQL blocks.
Figure 15.8: Scope of identifiers in PL/SQL blocks
15.3.5.3 Qualifying identifier names with module names
When necessary, PL/SQL offers many ways to qualify an identifier so that a reference to the identifier can be resolved. Suppose I create a package called company_pkg and declare a variable named last_company_id in that package"s specification, as follows:
PACKAGE company_pkg IS last_company_id NUMBER; ... END company_pkg;
Then, when I reference that variable outside of the package, I must preface the identifer name with the package name:
IF new_company_id = company_pkg.last_company_id THEN ...
Because a variable declared in a package specification is global in your session, the last_company_id variable can be referenced in any program, but it is not visible unless it is qualified.
I can also qualify the name of an identifier with the module in which it is defined:
PROCEDURE calc_totals IS salary NUMBER; BEGIN ... DECLARE salary NUMBER; BEGIN salary:= calc_totals.salary; END; ... END;
The first declaration of salary creates an identifier whose scope is the entire procedure. Inside the inner anonymous block, however, I declare another identifer with the same name. So when I reference the variable "salary" inside the inner block, it will always be resolved first against the declaration in the inner block, where that variable is visible without any qualification. If I wish to make reference to the procedure-wide salary variable inside the inner block, I must qualify that variable name with the name of the procedure.
PL/SQL goes to a lot of trouble and has established many rules for determining how to resolve such naming conflicts. While it is good to be aware of such issues, you would be much better off never having to rely on these guidelines. Use unique names for your identifiers in different blocks so that you can avoid naming conflicts altogether.
15.3.5.4 Cursor scope
To use a cursor, you must declare it. But you cannot refer to that cursor -- whether to OPEN, CLOSE, or FETCH from it -- unless that cursor is accessible in your current PL/SQL block. The scope of a cursor is that part of a PL/SQL program in which you can refer to the cursor.
You can refer to a cursor in the code block in which it was declared and in all blocks defined within that declaring block. You cannot refer to the cursor outside of the declaring block unless the cursor is declared in a package (see Chapter 16, Packages , for more information on global variables and cursors). For example, if a cursor is declared in the get_employees procedure (see below) and within get_employees a second PL/SQL block is defined to calculate total compensation for each employee, the cursor and its attributes may still be referenced in that calculation block:
PROCEDURE get_employees IS CURSOR emp_cur IS SELECT employee_id, sal + bonus FROM employee; BEGIN OPEN emp_cur; DECLARE empid NUMBER; total_comp NUMBER; BEGIN FETCH emp_cur INTO empid, total_comp; IF total_comp < 5000 THEN MESSAGE (" I need a raise!"); END IF; END; END;
If, on the other hand, the cursor is declared within an inner block, then that cursor cannot be referenced in an outer block. In the next procedure, the outer block declares the variables that receive the data fetched from the cursor. The inner block declares the cursor. While the inner block can open the cursor without error, the FETCH statement is outside the scope of the cursor-declaring block and will therefore fail:
PROCEDURE get_employees IS empid NUMBER; total_comp NUMBER; BEGIN DECLARE CURSOR emp_cur IS SELECT employee_id, sal + bonus FROM employee; BEGIN OPEN emp_cur; END; /* This fetch will not be able to identify the cursor. */ FETCH emp_cur INTO empid, total_comp; -- INVALID! IF total_comp < 5000 THEN MESSAGE (" I need a raise!"); END IF; END;
The rules for cursor scope are, therefore, the same as those for all other identifiers.
15.3.6 Block Labels
Anonymous blocks don"t have names under which they can be stored in the database. By using block labels, however, you can give a name to your block for the duration of its execution. A block label is a PL/SQL label which is placed directly in front of the first line of the block (either the DECLARE or the BEGIN keyword). You might use block labels for either of these reasons:
Improve the readability of your code. When you give something a name, you self-document that code. You also clarify your own thinking about what that code is supposed to do, sometimes ferreting out errors in the process.
Qualify the names of elements declared in the block to distinguish between references to elements with a different scope, but the same name.
A PL/SQL label has this format:
where identifier is a valid PL/SQL identifier (up to 30 characters in length, starting with a letter; see Section 2.2, "Identifiers" in Chapter 2 in Chapter 2, PL/SQL Language Fundamentals , for a complete list of rules).
Let"s look at a couple of examples of applying block labels. In the first example, I place a label in front of my block simply to give it a name and document its purpose:
<> DECLARE v_senator VARCHAR2(100) := "THURMOND, JESSE"; BEGIN IF total_contributions (v_senator, "TOBACCO") > 25000 THEN DBMS_OUTPUT.PUT_LINE ("We""re smokin""!"); END IF; END;
In the next example, I use my block labels to allow me to reference all variables declared in my outer and inner blocks:
<> DECLARE v_senator VARCHAR2(100) := "THURMOND, JESSE"; BEGIN IF total_contributions (v_senator, "TOBACCO") > 25000 THEN < > DECLARE v_senator VARCHAR2(100) := "WHATEVERIT, TAKES"; BEGIN IF tobacco_dependency.v_senator = alcohol_dependency.v_senator THEN DBMS_OUTPUT.PUT_LINE ("Maximizing profits - the American way of life!"); END IF; END; END IF; END;
I have used my block labels in this case to distinguish between the two different v_senator variables. Without the use of block labels, there would be no way for me to reference the v_senator of the outer block within the inner block. Of course, I could simply give these variables different names, the much-preferred approach.
Oracle , так как он интегрирован с сервером базы данных и его код выполняется непосредственно сервером, поэтому программы, написанные на PL/SQL , работают быстро и эффективно. Возможность использовать SQL в блоках PL/SQL - одна из важнейших его характеристик. PL/SQL применяется для доступа к базам данных Oracle из различных сред разработки, одной из которой является Oracle Forms . В этой главе мы ознакомимся с декларативной средой PL/SQL и ее возможностями, научимся создавать блоки и переменные PL/SQL .Блоки PL/SQL
PL/SQL, как и любой другой процедурный язык программирования, состоит из логически связанных элементов, объединенных в программные единицы, которые называются блоками. Каждый модуль PL/SQL состоит как минимум из одного блока. Блоки PL/SQL могут содержать любое количество подблоков, то есть иметь различный уровень вложенности . Блок как структурная единица логически связанных элементов определяет область их действия, делает код читабельным и простым для понимания. В PL/SQL различают два типа блока:
- анонимные блоки;
- именованные блоки.
Анонимные блоки
Анонимные блоки - это блоки, которые не имеют имени. Анонимные блоки не могут быть вызваны другими блоками, так как у них нет имени, на которое можно ссылаться.
Триггеры Oracle Forms и Reports, которые также называются клиентскими триггерами, являются анонимными блоками PL/SQL. Триггеры базы данных и сценарии в SQL*Plus, заключенные в операторские скобки BEGIN и END , также являются анонимными блоками. Ниже приведена структура анонимного блока:
DECLARE <имя переменной > <тип данных><(значение)>; BEGIN < исполняемый оператор>; EXCEPTION < оператор обработки исключения >; END;
- DECLARE - раздел объявлений. В этом разделе идентифицируются переменные, курсоры и вложенные блоки , на которые имеются ссылки в исполняемом блоке и блоке исключений. Этот раздел необязательный.
- BEGIN - END - исполняемый раздел. В этом разделе находятся операторы, которые выполняются ядром PL/SQL во время работы вашего приложения. Этот раздел является обязательным.
- EXCEPTION - раздел исключений. В этом разделе обрабатываются различные исключения, предупреждения и ошибки. Этот раздел необязателен.
Из всех ключевых слов в представленной структуре для анонимного блока ключевые слова BEGIN и END являются обязательными, и между ними должен быть как минимум один исполняемый оператор:
BEGIN Null; END;
Несмотря на то что ключевые слова BEGIN и END обязательны, в Oracle Forms их можно опустить. Так, к примеру, простейший анонимный блок имеет вид:
BEGIN Message ("Hello!"); END;
В Oracle Forms этот анонимный блок можно записать и без ключевых слов BEGIN и END :
Message ("Hello!");
Рассмотрим различные виды анонимных блоков.
- Блок с разделом объявлений и исключений:
DECLARE <имя переменной > <тип данных><(значение)>; BEGIN < исполняемый оператор>; EXCEPTION < оператор обработки исключения >; END;
BEGIN NULL; EXCEPTION WHEN OTHERS THEN NULL; END;
- Вложенный блок - это такой вид блока, когда один блок объявляется в исполняемом разделе другого. В большинстве случаев вложенный блок создается с целью размещения
в нем раздела EXCEPTION
для обработки исключений. Это очень удобно, так как в случае возникновения исключения программа не завершается с ошибкой, а продолжает функционировать, обработав ошибку. Ниже приведен пример вложенного блока
:
Синтаксис :
BEGIN < исполняемый оператор >; BEGIN < исполняемый оператор >; EXCEPTION < оператор обработки исключения >; END; EXCEPTION < оператор обработки исключения >; END;
Практический пример:
BEGIN NULL; BEGIN NULL; EXCEPTION WHEN OTHERS THEN NULL; END; NULL; EXCEPTION WHEN OTHERS THEN NULL; END;
- Вложенный анонимный блок с разделом объявлений и исключений:
Синтаксис :
DECLARE <имя переменной > <тип данных><(значение)>; BEGIN < исполняемый оператор >; BEGIN < исполняемый оператор >; EXCEPTION < оператор обработки исключения >; END; EXCEPTION < оператор обработки исключения >; END;
Практический пример:
DECLARE x NUMBER(4); BEGIN x:= 1000; BEGIN x:= x + 100; EXCEPTION WHEN OTHERS THEN x:= x + 2; END; x:= x + 10; DBMS_OUTPUT.PUT_LINE(x); EXCEPTION WHEN OTHERS THEN x:= x + 3; END;
Когда вы связываете код PL/SQL с триггером или полем, пользуясь таким инструментальным средством, как Forms Builder, этот код составляет анонимный блок PL/SQL. При этом можно создать полный блок с объявлениями, исполняемыми операторами и разделом обработки исключений или же ограничиться только исполняемыми операторами.
Именованные блоки
Именованные блоки - это блоки, которые имеют имя, например, функция или процедура. Несмотря на то что анонимные блоки используются часто, каждый разработчик пытается оформить свою PL/SQL-программу как именованный блок. Преимущество именованного блока в том, что у него есть имя и на него можно ссылаться из других блоков. Если считать главным отличием между анонимным блоком и именованным отсутствие имени у первого, то тогда в Forms понятие анонимного блока очень размыто. В Oracle Forms , несмотря на то что пользовательский
PROCEDURE [схема.] имя [(параметр [, параметр...])] FUNCTION [схема.] имя [(параметр [, параметр...])] RETURN тип_возвращаемых_данных
Заголовок функции отличается от заголовка процедуры лишь ключевым словом RETURN .
Именованные блоки, так же как и анонимные, могут быть вложенными, причем анонимный блок может быть вложен в именованный блок. Ниже приведен пример такого блока:
PROCEDURE calc_totals IS year_total NUMBER; BEGIN year_total:= 0; /* Начало вложенного блока */ DECLARE month_total NUMBER; BEGIN month_total:= year_total / 12; END set_month_total; /* Конец вложенного блока */ END;
В этом разделе мы ознакомились с понятием блока в PL/SQL, научились определять типы блоков и их структуру. Остается лишь сделать вывод из всего вышеперечисленного: PL/SQL предоставляет разработчику возможность писать удобочитаемый и гибкий код, подходить к написанию программы как к творческому процессу, требующему нестандартного мышления.
Summary : in this tutorial, you will learn about the PL/SQL anonymous block and how to execute it using SQL*Plus and SQL Developer tools.
PL/SQL anonymous block overview
PL/SQL is a block-structured language whose code is organized into blocks. A PL/SQL block consists of three sections: declaration, executable, and exception-handling sections. In a block, the executable section is mandatory while the declaration and exception-handling sections are optional.
A PL/SQL block has a name. A Function or a Procedure is an example of a named block. A named block is saved into the Oracle Database server first and then can be reused.
A block without a name is an anonymous block. An anonymous block is not saved in the Oracle Database server, so it is just for one-time use. PL/SQL anonymous blocks are useful for testing purposes.
The following picture illustrates the PL/SQL block structure:
A PL/SQL block has a declaration section where you declare variables, allocate memory for cursors, and define data types.
2) Executable section
A PL/SQL block has an executable section. An executable section starts with the keyword BEGIN and ends with the keyword END . The executable section must have a least one executable statement, even if it is the which does nothing.
3) Exception-handling section
A PL/SQL block has an exception-handling section that starts with the keyword EXCEPTION . The exception-handling section is where you catch and handle exceptions raised by the code in the execution section.
Note a block itself is an executable statement, therefore you can nest a block within other blocks.
The following example shows a simple PL/SQL anonymous block with one executable section.
BEGIN DBMS_OUTPUT.put_line("Hello World!" ); END ; |
The executable section calls the DMBS_OUTPUT.PUT_LINE procedure to display the “Hello World” message on the screen.
Execute a PL/SQL anonymous block using SQL*Plus
Once you have the code of an anonymous block, you can execute it using SQL*Plus, which is a command-line interface for executing SQL statement and PL/SQL blocks provided by Oracle Database.
The following picture illustrates how to execute a PL/SQL block using SQL*Plus:
First, connect to the Oracle Database server using a username and password.
Second, turn on the server output using the SET SERVEROUTPUT ON command so that the DBMS_OUTPUT.PUT_LINE procedure will display text on the screen.
Third, type the code of the block and enter a forward slash (/) to instruct SQL*Plus to execute the block. Once you type the forward slash (/), SQL*Plus will execute the block and display the Hello World message on the screen as shown in the illustrations.
Execute a PL/SQL anonymous block using SQL Developer
First, connect to the Oracle Database server using Oracle SQL Developer.
Second, create a new SQL file named anonymous-block.sql resided in the C:\plsql directory that will store the PL/SQL code.
Third, enter the PL/SQL code and execute it by clicking the Execute button or pressing the Ctrl-Enter keyboard shortcut.
The next example block has a declaration section.
DECLARE v_messageVARCHAR2 (255):="Hello World!" ; BEGIN DBMS_OUTPUT.PUT_LINE(l_message); END ; |
In the declaration section, we of the type that holds the string "Hello World!" . The DBMS_OUTPUT.PUT_LINE then used this variable, rather than the literal string, for display.
The next anonymous block example adds an exception-handling section which catches ZERO_DIVIDE exception raised in the executable section and displays the error message.
Базовой единицей любой программы, написанной на PL/SQL, является блок. Из блоков состоят все программы PL/SQL, причем блоки могут
идти один за другим (последовательно) либо вкладываться один в другой.
Существуют два различных типа блоков: анонимные блоки и именованные. Анонимные блоки обычно создаются динамически и выполняются только один раз. Этот тип блоков, как правило, создается в клиентской программе для вызова подпрограммы, хранящейся в базе данных. Именованные блоки отличаются тем, что они имеют имя. Именованные блоки
могут быть разбиты на категории следующим образом:
Помеченные блоки (labeled block) являются анонимными блоками с меткой, которая дает блоку имя. Они создаются обычно динамически и выполняются только один раз. Помеченные блоки используются так же, как и анонимные блоки, но метка позволяет ссылаться на переменные, которые иначе были бы недоступны.
Подпрограммы (subprogram) делятся на процедуры и функции. Они могут храниться в базе данных как автономные объекты, как часть модуля или как методы объектного типа. Подпрограммы обычно не
изменяются после своего создания и выполняются неоднократно. Подпрограммы могут объявляться в других блоках. Независимо от того, где они объявлены, подпрограммы выполняются явно посредством вызова процедуры или функции.
Триггеры (triggers) — это именованные блоки, которые ассоциируются с некоторым событием, происходящим в базе данных. Они, как правило, не изменяются после своего создания и выполняются многократно неявным образом при наступлении соответствующих событий. Событием, активизирующим триггер, может быть выполнение оператора языка манипулирования данными (DML, data manipulation language) над некоторой таблицей базы данных. К операторам DML относятся INSERT (ввести), UPDATE (обновить) и DELETE (удалить). Это может также быть оператор языка определения данных (DDL, data definition language), такой как CREATE или DROP, или событие базы данных, например запуск или останов.
Ниже приводится анонимный блок PL/SQL, с помощью которого в
таблицу temp_table вводятся две строки, затем они выбираются и отображаются на экране. Пример представлен в виде сеанса SQL*Plus:
внимание
Для выполнения приведенного выше примера, как и большинства других примеров этой книги, необходимо создать несколько таблиц, в том числе таблицу temp_table. Это можно сделать при помощи сценария tables, sql. Имена нужных файлов указываются в начале каждого примера, их также можно найти в файле README. Модуль DBMS_OUTPUT описан в главе 2.
Можно превратить этот блок в хранимую процедуру, если заменить ключевое слово DECLARE ключевыми словами CREATE OR REPLACE PROCEDURE (см. главы 9 и 10). Обратите внимание, что имя процедуры указывается после ключевого слова END.
1 .. 43 > .. >> Следующая
О Раздел объявлений. Идентифицирует переменные, курсоры и вложенные блоки, на которые имеются ссылки в исполняемом блоке и блоке исключений. Не обязателен.
О Исполняемый раздел. Операторы, которые выполняются ядром PL/SQL во время работы приложения. Обязателен.
О Раздел исключений. Обрабатывает исключения (предупреждения и ошибки). Не обязателен.
Структура блока PL/SQL для процедуры показана на рис. 3.1.
Заголовок
IS ;
Раздел объявлений)
BEGIN..........j
Исполняемый раздел EXCEPTION Й Раздел исключекий |;END;....." " j
Рис. 3.1. Структура блока PL/SQL
Порядок размещения разделов блока соответствует последовательности написания и выполнения программы.
1. Определяются тип блока (процедура, функция, анонимный) и способ его вызова (заголовок).
Структура блока РЦ/SQL
89
2. Объявляются все переменные и другие объекты PL/SQL, используемые в этом блоке (раздел объявлений).
3. Локальные переменные и другие объекты PL/SQL, объявленные выше, применяются для выполнения необходимых действий.
4. Обрабатываются все проблемные ситуации, которые могут возникнуть во время выполнения блока (раздел исключений).
Процедура, содержащая все четыре раздела, показана на рис. 3.2. В действительности все перечисленные разделы блока создаются не сразу, а в несколько этапов. Не ожидайте, что вы достигнете совершенства с первой попытки!
PROCEDURE getjwppy (enamejn 1Я VARCHAR2) ^- Заголовок
IS _
| ~ hi redate DATE; »-|-Раздел объявлении
BEGIN
- Исполняемый раздел
? Раздел исключений
END;
Рис. 3.2. Процедура, которая содержит все четыре раздела
Анонимные блоки
Когда кто-то хочет остаться неизвестным, он не называет своего имени. То же можно сказать и об анонимном блоке PL/SQL, показанном на рис. 3.3: в нем вообще нет раздела заголовка, блок начинается ключевым словом DECLARE (или BEGIN).
- Исполняемый раздел
Рис 3.3. Анонимный блок без разделов объявлений и исключений
Анонимный блок не может быть вызван другим блоком, поскольку не имеет составляющей, с помощью которой к нему можно обратиться. Таким образом, он является чем-то вроде сценария, который предназначен для выполнения операторов PL/SQL, в большинстве случаев включающих вызовы процедур и функций. Поскольку анонимные блоки могут содержать собственные разделы объявлений
90
Глава 3 Основы языка
и исключений, разработчики часто используют их для определения области видимости идентификаторов и области действия обработки исключений в программе большего размера. Эти свойства блока мы подробно рассмотрим в следующих разделах.
Структура анонимного блока
Общий формат анонимного блока PL/SQL таков:
[ DECLARE
... необязательные операторы объявлений... ]
BEGIN
... исполняемые операторы...
[ EXCEPTION
... необязательные операторы обработки исклвчений... ]
END:
Квадратные скобки указывают на необязательную часть синтаксиса. Ключевые слова BEGIN и END являются обязательными, и между ними должен быть как минимум один исполняемый оператор.
Примеры анонимных блоков
Ниже приведены различные сочетания разделов, которые допустимы для блока PL/SQL.
О Анонимный блок BEGIN.. .END без разделов объявлений и исключений:
BEGIN
-- Вывод текущей даты в стандартном формате DBMS DUTPIIT. PUT LINEfSYSDATE):
END:
О Анонимный блок с разделом объявлений, но без раздела исключений:
DECLARE
l_right now DATE:- SYSDATE:
BEGIN
DBMS OUTPUT.PUT_LXNEСT_rlght_now);
END:
О Анонимный блок с разделом объявлений, исполняемым разделом и разделом исключений:
DECLARE
-- Вызов определенной ранее функции для получения даты
-- найма сотрудника по фамилии TEUERSTEIN".
l_hiredate DATE:- employee_pkg,date_of_hire CFEUERSTEIN");
l_right_now DATE SYSDATE; l_old timer EXCEPTION:
BEGIN
IF l_hiredate < ADD_MONTHS (1 r1ght_now. 6)
THEN
RAISE 1 old_t1mer;
ELSE
l_hiredate right_now:
Структура блока PL/SQL
91
END IF;
EXCEPTION
WHEN l_old_timer THEN
DBMS_OUTPUT.PllT LINEt"Y вас нет доступа к информации.");
UHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE["ОШИБКА: " || SQLCODE);
END;
Анонимные блоки выполняют последовательности операторов и затем завершают свою работу, действуя как процедуры. Фактически все анонимные блоки являются анонимными процедурами.
Анонимные блоки в различных средах
Анонимные блоки используются в средах, где код PL/SQL выполняется либо непосредственно, либо как часть некоторой программы этой среды (табл. 3.1). Объект, включающий конкретный блок, обеспечивает его контекст и в ряде случаев - имя программы.
Таблица 3.1. Анонимные блоки в различных средах
Объект, содержащий блок Среда Описание
Клиентский триггер Средства разработки Orade «Чистые» исполняемые операторы в триггерах Forms Builder или Reports Builder, оформленные соответствующим инструментальным средством как анонимные блоки и отправляемые исполнительному ядру PL/5QL (подробнее о триггерах см. в главе 18)
Триггер базы данных Манипуляции данными на уровне таблицы или события базы данных Тело триггера. Хотя у триггера имеется имя, код PL/SQL анонимен (не имеет имени)
Сценарий 5QL*Plus или его эквивалент Программы и пакетно выполняемые сценарии, вызывающие процедуры и/или функции. Кроме того, команда SQL*Plus EXECUTE транслирует свой аргумент в анонимный блок, заключая его в операторы BEGIN и END
