Oracle найти процедуру по тексту
Хранимые процедуры и вызовы функций подпрограмм базы данных Oracle
Представления словаря данных
Словарь данных Oracle — настоящие джунгли! Он изобилует полезной информацией, но найти путь к цели порой бывает очень непросто. В нем сотни представлений, основанных на сотнях таблиц, множество сложных взаимосвязей, специальных кодов и слишком много неоптимизированных определений представлений. Вскоре мы рассмотрим подмножество важнейших представлений, а пока выделим три типа (или уровня) представлений словаря данных:
- USER_* — представления с информацией об объектах базы данных, принадлежащих текущей схеме.
- ALL_* — представления с информацией об объектах базы данных, доступных в текущей схеме (либо принадлежащих ей, либо доступных благодаря соответствующим привилегиям). Обычно они содержат те же столбцы, что и соответствующие представления USER , с добавлением столбца OWNER в представлениях ALL .
- DBA_* — представления с информацией обо всех объектах базы данных. Обычно содержат те же столбцы, что и соответствующие представления ALL . Исключение составляют представления v$ , gx$ и x$ .
В этой статье мы будем работать с представлениями USER ; вы можете легко изменить любые сценарии и приемы, чтобы они работали с представлениями ALL , добавив в свою логику столбец OWNER . Вероятно, для разработчика PL/SQL самыми полезными будут следующие представления:
- USER_ARGUMENTS — аргументы (параметры) всех процедур и функций схемы.
- USER_DEPENDENCIES — все зависимости (входящие и исходящие) для принадлежащих текущей схеме объектов. Представление в основном используется Oracle для пометки неработоспособных объектов, а также средой разработки для вывода информации зависимостей в программах просмотра объектов. Примечание: для полного анализа зависимостей следует использовать представление ALL_DEPENDENCIES на случай, если программная единица будет вызвана другой программной единицей, принадлежащей другой схеме.
- USER_ERRORS — текущий набор ошибок компиляции для всех хранимых объектов (включая триггеры). Представление используется командой SQL*Plus SHOW ERRORS , описанной в этом блоге. Вы также можете писать собственные запросы к этому представлению.
- USER_IDENTIFIERS — представление появилось в Oracle11g , а для его заполнения используется утилита PL/Scope . Содержит информацию обо всех идентификаторах (имена программ, переменных и т. д.) в кодовой базе; исключительно полезный инструмент анализа кода.
- USER_0BJECTS — объекты, принадлежащие текущей схеме. Например, при помощи этого представления можно узнать, помечен ли объект как неработоспособный ( INVALID ), найти все пакеты, в имени которых присутствует EMP, и т. д.
- USER_0BJECT_SIZE — размер принадлежащих текущей схеме объектов. Точнее, это представление возвращает информацию о размере исходного, разобранного и откомпилированного кода. И хотя оно в основном используется компилятором и ядром времени выполнения, вы можете воспользоваться им для поиска больших программ в вашей среде — кандидатов для размещения в SGA .
- USER_PLSQL_0B3ECT_SETTINGS — представление появилось в Oracle10g. Содержит информацию о характеристиках объектов PL/SQL, которые могут изменяться командами DDL ALTER и SET : уровни оптимизации, параметры отладки и т. д.
- USER_PR0CEDURES — информация о хранимых программах (не только процедурах, как можно было бы подумать по названию) — например, модель AUTHID, признак детерминированности функций и т. д.).
- USER_S0URCE — исходный код всех принадлежащих текущей схеме объектов (в Oracle9i и выше — вместе с триггерами баз данных и исходным кодом Java). Это очень удобное представление — вы можете применять к нему всевозможные средства анализа исходного кода, используя SQL и особенно Oracle Text.
- USER_ST0RED_SETTINGS — флаги компилятора PL/SQL. Это представление поможет узнать, какие программы были откомпилированы в код аппаратной платформы.
- USER_TRIGGERS и USER_TRIG_C0LUMNS — триггеры базы данных, принадлежащие текущей схеме (включая исходный код и описание инициирующих событий), а также столбцы, связанные с триггерами.
Для просмотра структуры любого из этих представлений можно либо воспользоваться
командой DESCRIBE в SQI*Plus , либо обратиться к документации Oracle. Примеры использования этих представлений приведены в следующем разделе.
Вывод информации о хранимых объектах
В представлении USER_0BJECTS содержится ключевая информация об объекте:
- 0BJECT_name — имя объекта.
- OBJECT_TYPE — тип объекта ( PACKAGE, FUNCTI0N, TRIGGER и т. д.).
- STATUS — состояние объекта ( VALID или INVALID ).
- LAST_DDL_TIME — время последнего изменения объекта.
Следующий сценарий SQL*Plus выводит информацию о состоянии объектов PL/SQL :
Результат работы сценария выглядит примерно так:
OBJECT_TYPE | OBJECT_NAME | STATUS |
FUNCTION | DEVELOP ANALYSIS | INVALID |
NUMBER OF ATOMICS | INVALID | |
PACKAGE | CONFIG_PKG | VALID |
EXCHDLR PKG | VALID |
Обратите внимание на два модуля с пометкой INVALID . О том, как вернуть программный модуль в действительное состояние VALID , будет рассказано далее.
3.1 Исходный код процесса и функции запроса:
Получается путем запроса представления словаря данных USER_SOURCE.
2.1. Процесс создания:
Синтаксис оператора процедуры создания следующий:
Описание трех режимов IN, OUT и IN OUT:
Параметры IN (режим по умолчанию)
Он используется для передачи значений из вызывающей среды в хранимую процедуру. Невозможно присвоить значение параметру IN. Значение, передаваемое этому параметру, может быть константой, переменной со значением, выражением, и Т. Д.
Он используется для возврата значения из процедуры вызывающей стороне.Значение этого параметра не может быть присвоено другой переменной и не может быть константой или выражением. В теле процесса параметру OUT должно быть присвоено значение.
Параметр IN OUT
Вы можете передавать значения от вызывающего объекта к процедуре и возвращать значения, которые могли измениться вызывающему объекту после выполнения процедуры.
Приведите примеры, чтобы проиллюстрировать разницу между тремя параметрами режима и позициями, которые могут появляться в теле процесса:
Используйте процесс обработки запроса, чтобы получить информацию о сотруднике.
1. Вызов хранимой процедуры в PL / SQL:
Параметр v_empno находится в режиме IN; остальные три - в режиме OUT:
2.3. Разница между процессом и функцией:
Одно из отличий:
Форма параметра и возвращаемое значение Различные функции имеют ноль или более параметров и только одно возвращаемое значение; процедура имеет ноль или более параметров и не возвращает значение, а возвращаемое значение выводится параметрами OUT; как процедуры, так и функции Могут быть В параметрах IN, и ввод параметров принимается через список параметров; функция не может иметь параметры OUT, и возврат значения функции возвращается предложением RETURN.
Форма звонка другая. Процедура может быть вызвана как один исполняемый оператор.Например, имя процедуры (фактический параметр 1, фактический параметр 2, . ), оператор может появляться отдельно в блоке PL / SQL.
3. Как запросить информацию о процессах и функциях в словаре данных? К
Текст процедуры и функции
Просмотреть словарь данных USER_SOURCE или команду DESCRIBE
Дерево синтаксического анализа
Синтаксический анализ выражений
Код компиляции (p_code)
Синтаксическая ошибка блока PL / SQL
Проверьте словарь данных USER_ERRORS или используйте команду SHOW ERRORS.
Запустить отладочную информацию
Пользовательская отладочная информация для переменных или выражений
Использовать процесс пакета DBMS_OUTPUT
Анализ и изменение состояний триггеров
Запросы к триггерным представлениям ( USER_TRIGGERS, USER_TRIG_COLUMNS ) обычно используются для решения следующих задач:
- Включение или отключение всех триггеров для заданной таблицы. Вместо того чтобы писать код вручную, вы выполняете соответствующие команды DDL из кода PL/SQL. Пример такой программы приведен в статье «Сопровождение триггеров».
- Поиск триггеров, выполняемых при изменении некоторых столбцов, но не имеющих предложения секции WHEN . Следующий запрос поможет найти триггеры, не имеющие секции WHEN , которые являются источниками потенциальных проблем:
4.1 Overview of Queries
The basic Oracle Text query takes a query expression, usually a word with or without operators, as input. Oracle Text returns all documents (previously indexed) that satisfy the expression along with a relevance score for each document. Scores can be used to order the documents in the result set.
To enter an Oracle Text query, use the SQL SELECT statement. Depending on the type of index you create, you use either the CONTAINS or CATSEARCH operator in the WHERE clause. You can use these operators programatically wherever you can use the SELECT statement, such as in PL/SQL cursors.
Use the MATCHES operator to classify documents with a CTXRULE index.
4.1.1 Querying with CONTAINS
When you create an index of type CONTEXT , you must use the CONTAINS operator to enter your query. An index of type CONTEXT is suited for indexing collections of large coherent documents.
With the CONTAINS operator, you can use a number of operators to define your search criteria. These operators enable you to enter logical, proximity, fuzzy, stemming, thesaurus and wildcard searches. With a correctly configured index, you can also enter section searches on documents that have internal structure such as HTML and XML.
With CONTAINS , you can also use the ABOUT operator to search on document themes.
4.1.1.1 CONTAINS SQL Example
In the SELECT statement, specify the query in the WHERE clause with the CONTAINS operator. Also specify the SCORE operator to return the score of each hit in the hitlist. The following example shows how to enter a query:
You can order the results from the highest scoring documents to the lowest scoring documents using the ORDER BY clause as follows:
The CONTAINS operator must always be followed by the > 0 syntax, which specifies that the score value returned by the CONTAINS operator must be greater than zero for the row to be returned.
When the SCORE operator is called in the SELECT statement, the CONTAINS operator must reference the score label value in the third parameter as in the previous example.
4.1.1.2 CONTAINS PL/SQL Example
In a PL/SQL application, you can use a cursor to fetch the results of the query.
The following example enters a CONTAINS query against the NEWS table to find all articles that contain the word oracle . The titles and scores of the first ten hits are output.
This example uses a cursor FOR loop to retrieve the first ten hits. An alias score is declared for the return value of the SCORE operator. The score and title are output to standard out using cursor dot notation.
4.1.1.3 Structured Query with CONTAINS
A structured query, also called a mixed query, is a query that has a CONTAINS predicate to query a text column and has another predicate to query a structured data column.
To enter a structured query, you specify the structured clause in the WHERE condition of the SELECT statement.
For example, the following SELECT statement returns all articles that contain the word oracle that were written on or after October 1, 1997:
Even though you can enter structured queries with CONTAINS , consider creating a CTXCAT index and issuing the query with CATSEARCH , which offers better structured query performance.
4.1.2 Querying with CATSEARCH
When you create an index of type CTXCAT , you must use the CATSEARCH operator to enter your query. An index of type CTXCAT is best suited when your application stores short text fragments in the text column and other associated information in related columns.
For example, an application serving an online auction site might have a table that stores item description in a text column and associated information such as date and price in other columns. With a CTXCAT index, you can create b-tree indexes on one or more of these columns. The result is that when you use the CATSEARCH operator to search a CTXCAT index, query performance is generally faster for mixed queries.
The operators available for CATSEARCH queries are limited to logical operations such as AND or OR . The operators you can use to define your structured criteria are greater than, less than, equality, BETWEEN , and IN .
4.1.2.1 CATSEARCH SQL Query
A typical query with CATSEARCH might include a structured clause as follows to find all rows that contain the word camera ordered by the bid_close date:
The type of structured query you can enter depends on how you create your sub-indexes.
As shown in the previous example, you specify the structured part of a CATSEARCH query with the third structured_query parameter. The columns you name in the structured expression must have a corresponding sub-index.
For example, assuming that category_id and bid_close have a sub-index in the ctxcat index for the AUCTION table, enter the following structured query:
4.1.2.2 CATSEARCH Example
The following example shows a field section search against a CTXCAT index using CONTEXT grammar by means of a query template in a CATSEARCH query.
4.1.3 Querying with MATCHES
When you create an index of type CTXRULE , you must use the MATCHES operator to classify your documents. The CTXRULE index is essentially an index on the set of queries that define your classifications.
For example, if you have an incoming stream of documents that need to be routed according to content, you can create a set of queries that define your categories. You create the queries as rows in a text column. It is possible to create this type of table with the CTX_CLS.TRAIN procedure.
You then index the table to create a CTXRULE index. When documents arrive, you use the MATCHES operator to classify each document
4.1.3.1 MATCHES SQL Query
A MATCHES query finds all rows in a query table that match a given document. Assuming that a table querytable has a CTXRULE index associated with it, enter the following query:
Note the bind variable :doc_text which contains the document CLOB to be classified.
Combining everything into a simple example:
This query will return queries 1 (the word oracle appears in the document) and 4 (the phrase market share appears in the document), but not 2 (neither the word larry nor the word ellison appears, and not 3 (there is no text in the document, so it does not match the query).
Note that, in this example, the document was passed in as a string for simplicity. Typically, your document would be passed in a bind variable.
The document text used in a matches query can be VARCHAR2 or CLOB . It does not accept BLOB input, so you cannot match filtered documents directly. Instead, you must filter the binary content to CLOB using the AUTO_FILTER filter. For the following example, we make two assumptions: one, that the document data is in the bind variable :doc_blob ; and, two, that we have already defined a policy, my_policy , that CTX_DOC.POLICY_FILTER can use. For example:
The procedure CTX_DOC.POLICY_FILTER filters the BLOB into the CLOB data, because you need to get the text into a CLOB to enter a MATCHES query. It takes as one argument the name of a policy you have already created with CTX_DDL.CREATE_POLICY .
Oracle Text Reference for information on CTX_DOC.POLICY_FILTER
If your file is text in the database character set, then you can create a BFILE and load it to a CLOB using the function DBMS_LOB.LOADFROMFILE , or you can use UTL_FILE to read the file into a temp CLOB locator.
If your file needs AUTO_FILTER filtering, then you can load the file into a BLOB instead, and call CTX_DOC.POLICY_FILTER , as previously shown.
Classifying Documents in Oracle Text for more extended classification examples
4.1.3.2 MATCHES PL/SQL Example
The following example assumes that the table of queries profiles has a CTXRULE index associated with it. It also assumes that the table newsfeed contains a set of news articles to be categorized.
This example loops through the newsfeed table, categorizing each article using the MATCHES operator. The results are stored in the results table.
The following example displays the categorized articles by category.
Classifying Documents in Oracle Text for more extended classification examples
4.1.4 Word and Phrase Queries
A word query is a query on a word or phrase. For example, to find all the rows in your text table that contain the word dog , enter a query specifying dog as your query term.
You can enter word queries with both CONTAINS and CATSEARCH SQL operators. However, phrase queries are interpreted differently.
4.1.4.1 CONTAINS Phrase Queries
If multiple words are contained in a query expression, separated only by blank spaces (no operators), the string of words is considered a phrase and Oracle Text searches for the entire string during a query.
For example, to find all documents that contain the phrase international law , enter your query with the phrase international law .
4.1.4.2 CATSEARCH Phrase Queries
With the CATSEARCH operator, the AND operator is inserted between words in phrases. For example, a query such as international law is interpreted as international AND law .
4.1.5 Querying Stopwords
Stopwords are words for which Oracle Text does not create an index entry. They are usually common words in your language that are unlikely to be searched on by themselves.
Oracle Text includes a default list of stopwords for your language. This list is called a stoplist. For example, in English, the words this and that are defined as stopwords in the default stoplist. You can modify the default stoplist or create new stoplists with the CTX_DDL package. You can also add stopwords after indexing with the ALTER INDEX statement.
You cannot query on a stopword by itself or on a phrase composed of only stopwords. For example, a query on the word this returns no hits when this is defined as a stopword.
You can query on phrases that contain stopwords as well as non-stopwords such as this boy talks to that girl . This is possible because the Oracle Text index records the position of stopwords even though it does not create an index entry for them.
When you include a stopword within your query phrase, the stopword matches any word. For example, the query:
matches phrases such as Jack is big and Jack grew big assuming was is a stopword. Note that this query matches grew, even though it is not a stopword.
Starting with Oracle Database 12 c Release 2 (12.2), stopwords and any unary operators on stopwords are ignored at the initial stages of a query resulting in different query results than earlier releases. For example:
The above query does not return any documents as the is a stopword and $ operator along with the stopword is ignored during query processing.
The above query returns documents containing first as the is a stopword, so it is ignored along with the $ operator. No expansion is performed irrespective of the specified $ operator.
4.1.6 ABOUT Queries and Themes
An ABOUT query is a query on a document theme. A document theme is a concept that is sufficiently developed in the text. For example, an ABOUT query on US politics might return documents containing information about US presidential elections and US foreign policy. Documents need not contain the exact phrase US politics to be returned.
During indexing, document themes are derived from the knowledge base, which is a hierarchical list of categories and concepts that represents a view of the world. Some examples of themes in the knowledge catalog are concrete concepts such as jazz music , football , or Nelson Mandela . Themes can also be abstract concepts such as happiness or honesty .
During indexing, the system can also identify and index document themes that are sufficiently developed in the document, but do not exist in the knowledge base.
You can augment the knowledge base to define concepts and terms specific to your industry or query application. When you do so, ABOUT queries are more precise for the added concepts.
ABOUT queries perform best when you create a theme component in your index. Theme components are created by default for English and French.
Oracle Text enables you to query on themes with the ABOUT operator. A stoptheme is a theme that is not to be indexed. You can add and remove stopthemes with the CTX_DDL package. You can add stopthemes after indexing with the ALTER INDEX statement.
4.1.7 Query Expressions
A query expression is everything in between the single quotes in the text_query argument of the CONTAINS or CATSEARCH operator. What you can include in a query expression in a CONTAINS query is different from what you can include in a CATSEARCH operator.
4.1.7.1 CONTAINS Operators
A CONTAINS query expression can contain query operators that enable logical, proximity, thesaural, fuzzy, and wildcard searching. Querying with stored expressions is also possible. Within the query expression, you can use grouping characters to alter operator precedence. This book refers to these operators as the CONTEXT grammar.
With CONTAINS , you can also use the ABOUT query to query document themes.
4.1.7.2 CATSEARCH Operator
With the CATSEARCH operator, you specify your query expression with the text_query argument and your optional structured criteria with the structured_query argument. The text_query argument enables you to query words and phrases. You can use logical operations, such as logical and, or, and not. This book refers to these operators as the CTXCAT grammar.
If you want to use the much richer set of operators supported by the CONTEXT grammar, you can use the query template feature with CATSEARCH .
With structured_query argument, you specify your structured criteria. You can use the following SQL operations:
Данные пакета PL/SQL состоят из переменных и констант, определенных на уровне пакета, а не в конкретной его функции или процедуре. Их областью видимости является не отдельная программа, а весь пакет. Структуры данных пакета существуют (и сохраняют свои значения) на протяжении всего сеанса, а не только во время выполнения программы. Если данные пакета объявлены в его теле, они сохраняются в течение сеанса, но доступны только элементам пакета (то есть являются приватными).
Если данные пакета объявлены в его спецификации, они также сохраняются в течение всего сеанса, но их чтение и изменение разрешено любой пользовательской программе, обладающей привилегией EXECUTE для пакета. Общие данные пакета похожи на глобальные переменные Oracle Forms (а их использование сопряжено с таким же риском).
Если пакетная процедура открывает курсор, он остается открытым и доступным в ходе всего сеанса. Нет необходимости объявлять курсор в каждой программе. Один модуль может его открыть, а другой — выполнить выборку данных. Переменные пакета могут использоваться для передачи данных между транзакциями, поскольку они привязаны не к транзакции, а к сеансу.
Получение свойств хранимого кода
Представление USER_PLSQL_0BJECT_SETTINGS (появившееся в Oracle10g) содержит информацию о следующих параметрах компиляции хранимого объекта PL/SQL :
- PLSQL_OPTIMIZE_LEVEL — уровень оптимизации, использовавшийся при компиляции объекта.
- PLSQL_CODE_TYPE — режим компиляции объекта.
- PLSQL_DEBUG — признак отладочной компиляции.
- PLSQL_WARNINGS — настройки предупреждений, использовавшиеся при компиляции объекта.
- NLS_LENGTH_SEMANTICS — семантика длины NLS, использовавшаяся при компиляции объекта.
Пара примеров возможного применения этого представления:
- Поиск программ, не использующих все возможности оптимизирующего компилятора (уровень оптимизации 1 или 0):
- Проверка отключения предупреждений у хранимых программ:
В представлении USER_PROCEDURES перечисляются все функции и процедуры с их свойствами. В частности, в представление USER_PROCEDURES включается настройка модели authid для программы ( definer или current_user ). Эта информация позволяет быстро определить, какие программы в пакете используют модель привилегий вызывающей стороны или создателя. Пример такого запроса:
Хранимые процедуры и вызовы функций подпрограмм базы данных Oracle
Блоки PL / SQL-Безымянный, не хранящиеся в базе данных и не могут быть вызваны из других блоков PL / SQL. Описанная ниже структура - функции, процедуры, пакеты и триггеры - все это хорошо известные блоки, они могут храниться в базе данных и могут вызываться где угодно.
6. Преимущества процедур и функций:
(1) Повысьте безопасность и целостность данных. Используйте разрешения безопасности для управления косвенным доступом к базе данных пользователей, не имеющих достаточных разрешений; путем централизации операций со связанными таблицами для обеспечения согласованного выполнения этих связанных таблиц. Не выполняйте никаких операций или любая операция.
(2) Повышение производительности операций Когда несколько пользователей используют один и тот же оператор SQL, выполняйте только синтаксический анализ; выполняйте синтаксический анализ только во время компиляции и не повторяйте во время выполнения, напрямую вызывайте скомпилированный код.
(3) Экономия места для хранения, использование одного и того же кода для хранения для нескольких различных приложений, высокая ремонтопригодность
При компиляции программного модуля PL/SQL его исходный код сохраняется в базе данных Oracle. Это дает разработчикам два важных преимущества:
- Информацию о программном коде можно получить с помощью запросов SQL. Разработчики могут писать запросы и даже целые программы PL/SQL , которые читают информацию из представлений словаря данных и даже могут изменять состояние кода приложения.
- База данных Oracle управляет зависимостями между хранимыми объектами. В мире PL/ SQL не существует процесса «компоновки» исполняемых файлов, которые затем запускаются пользователями. База данных берет на себя все служебные операции, позволяя разработчику сосредоточиться на реализации бизнес-логики.
В следующих разделах представлены основные источники информации в словаре данных СУБД Oracle.
4. Вызов процедур и функций:
После того, как процедуры и функции сохранены в базе данных, их можно вызывать из различных сред. Его можно вызвать как из командной строки, так и из конкретного приложения. Но в разных средах вызова синтаксис вызова функций процесса отличается.
Разница между методом вызова процесса и функцией:
В соответствии с законом
Появляется в полном исполняемом заявлении
Может заменить позицию переменной или выражения в операторе возврата
Вызвать процесс с параметрами OUT
Вызовите процедуру QUERY_EMP из SQL * PLUS. Процедура имеет один параметр IN и три параметра OUT. Три переменные должны быть определены с определением VARIABLE в SQL * PLUS. Присвойте значения этим трем переменным при выполнении процесса. Вы можете использовать следующая реализация сценария SQL * PLUS:
Анализ аргументов
Представление USER_ARGUMENTS может оказаться исключительно полезным для программиста: оно содержит информацию о каждом аргументе каждой хранимой программы в вашей схеме. Оно одновременно предоставляет разнообразную информацию об аргументах в разобранном виде и запутанную структуру, с которой очень трудно работать. Простой сценарий SQL*Plus для вывода содержимого USER_ARGUMENTS для всех программ в заданном пакете:
Более совершенная программа на базе PL/SQL для вывода содержимого USER_ARGUMENTS находится в файле show_aN_arguments.sp на сайте книги.
Вы также можете создавать более конкретные запросы к представлению USER_ARGUMENTS для выявления возможных проблем с качеством кодовой базой. Например, Oracle рекомендует воздерживаться от использования типа LONG и использовать вместо него LOB . Кроме того, тип данных CHAR с фиксированной длиной может создать проблемы; намного лучше использовать VARCHAR2 . Следующий запрос выявляет использование этих типов в определениях аргументов:
Представление USER_ARGUMENTS может использоваться даже для получения информации о программах пакета, которую трудно получить другим способом. Предположим, я хочу получить список всех процедур и функций, определенных в спецификации пакета. Что вы говорите? «Нет проблем — просто выдать запрос к USER_PROCEDURES ». И это был бы хороший ответ. вот только USER_PROCEDURES не сообщит вам, является ли программа функцией или процедурой (причем в зависимости от перегрузки она может быть и той и другой!)
Представление USER_ARGUMENTS содержит нужную информацию, но она хранится в далеко не очевидном формате. Чтобы определить, является ли программа функцией или процедурой, можно поискать в USER_ARGUMENTS строку данной комбинации «пакет/ программа», у которой значение POSITION равно 0. Это значение Oracle использует для хранения «аргумента» RETURN функции. Если оно отсутствует, значит, программа должна быть процедурой.
Следующая функция использует эту логику для возвращения строки, обозначающей тип программы (если она перегружена для обоих типов, функция возвращает строку « FUNCTION, PROCEDURE »). Обратите внимание: функция list_to_string , используемая в теле функции, определяется в файле:
Наконец, следует сказать, что встроенный пакет DBMS_DESCRIBE предоставляет программный интерфейс PL/SQL , который возвращает почти ту же информацию, что и USER_ARGUMENTS . Впрочем, эти два механизма отличаются некоторыми особенностями работы с типами данных.
Глобальные данные в сеансе Oracle
В среде PL/SQL структуры данных пакета функционируют как глобальные. Однако следует помнить, что они доступны только в пределах одного сеанса или подключения к базе данных Oracle и не могут совместно использоваться несколькими сеансами. Если доступ к данным нужно обеспечить для нескольких сеансов Oracle, используйте пакет DBMS_PIPE (его описание имеется в документации Oracle Built-In Packages).
Будьте осторожны с предположением о том, что разные части приложения всегда работают с Oracle через одно подключение. В некоторых случаях среда, из которой выполняется компонент приложения, устанавливает для него новое подключение. При этом данные пакета, записанные первым подключением, будут недоступны для второго.
Допустим, приложение Oracle Forms сохранило значение в пакетной структуре данных. Когда форма вызывает хранимую процедуру, эта процедура может обращаться к тем же пакетным переменным и значениям, что и форма, потому что они используют одно подключение к базе данных. Но допустим, форма генерирует отчет с использованием Oracle Reports. По умолчанию Oracle Reports создает для отчета отдельное подключение к базе данных с тем же именем пользователя и паролем. Даже если отчет обратится к тому же пакету и структурам данных, что и форма, значения, хранимые в структурах данных, доступных форме и отчету, будут разными, поскольку сеанс отчета имеет свой экземпляр пакета и всех его структур.
По аналогии с двумя типами структур данных в пакетах (общедоступные и приватные) также существуют два типа глобальных данных пакетов: глобальные общедоступные данные и глобальные приватные данные. В следующих трех разделах статьи рассматриваются различные способы использования данных пакетов.
Пакетные курсоры
Одним из самых интересных типов пакетных данных является явный курсор PL/SQL. Его можно объявить в теле либо в спецификации пакета. Состояние курсора (открыт или закрыт), а также указатель на его набор данных сохраняются в течение всего сеанса. Это означает, что открыть пакетный курсор можно в одной программе, выбрать из него данные — в другой, а закрыть — в третьей. Такая гибкость курсоров предоставляет большие возможности, но в то же время она может стать источником проблем. Сначала мы рассмотрим некоторые тонкости объявления пакетных курсоров, а затем перейдем к открытию, выборке данных и закрытию таких курсоров.
Объявление пакетных курсоров
Явный курсор в спецификации пакета можно объявлять двумя способами:
- Полностью (заголовок курсора и запрос). Именно так объявляются курсоры в локальных блоках PL/SQL .
- Частично (только заголовок курсора). В этом случае запрос определяется в теле пакета, поэтому реализация курсора скрыта от использующего пакет разработчика. При использовании второго способа в объявление нужно добавить секцию RETURN , указывающую, какие данные будут возвращены при выборке из курсора. На самом деле эти данные определяются инструкцией SELECT , которая присутствует только в теле, но не в спецификации
В секции RETURN можно задать одну из следующих структур данных:
- О запись, объявленная на основе таблицы базы данных с использованием атрибута %rowtype ;
- О запись, определенная программистом.
Объявление курсора в теле пакета осуществляется так же, как в локальном блоке PL/ SQL . Следующий пример спецификации пакета демонстрирует оба подхода:
Логика программы описана в следующей таблице.
Строки | Описание |
3-9 | Типичное определение явного курсора, полностью заданное в спецификации пакета |
11-13 | Определение курсора без запроса. Спецификация указывает, что открыв курсор и выбрав из него данные, пользователь получит одну строку из таблицы books под действием заданного фильтра |
15-18 | Определение нового типа записи для хранения информации об авторе |
20-22 | Объявление курсора, возвращающего сводную информацию о заданном авторе (всего три значения) |
Рассмотрим тело пакета и выясним, какой код необходимо написать для работы с каждым из этих курсоров:
Работа с пакетными курсорами
Теперь давайте посмотрим, как пользоваться пакетными курсорами. Прежде всего для открытия, выборки данных и закрытия вам не придется изучать новый синтаксис — нужно только задать имя пакета перед именем курсора. Например, чтобы запросить информацию о книгах по PL/SQL, можно выполнить такой блок кода:
Как видите, на основе пакетного курсора точно так же можно объявить переменную с использованием %ROWTYPE и проверить атрибуты. Ничего нового!
Однако и в этом простом фрагменте кода есть скрытый нюанс. Поскольку курсор объявлен в спецификации пакета, его область видимости не ограничивается конкретным блоком PL/SQL . Предположим, мы выполняем следующий код:
Дело в том, что блок, выполненный первым, не закрыл курсор, и по завершении его работы курсор остался открытым.
При работе с пакетными курсорами необходимо всегда соблюдать следующие правила:
- Никогда не рассчитывайте на то, что курсор закрыт (и готов к открытию).
- Никогда не рассчитывайте на то, что курсор открыт (и готов к закрытию).
- Всегда явно закрывайте курсор после завершения работы с ним. Эту логику также необходимо включить в обработчики исключений; убедитесь в том, что курсор закрывается на всех путях выхода из программы.
Если пренебречь этими правилами, то ваши приложения будут работать нестабильно, а в процессе их функционирования могут появиться неожиданные необработанные исключения. Поэтому лучше написать процедуры, которые открывают и закрывают курсоры и учитывают все возможные их состояния. Этот подход реализован в следующем пакете:
Как видите, вместе с курсором объявлены две сопутствующие процедуры для его открытия и закрытия. Если нам, скажем, потребуется перебрать в цикле строки курсора, это можно сделать так:
В этом фрагменте не используются явные вызовы OPEN и CLOSE . Вместо них вызываются соответствующие процедуры, скрывающие особенности работы с пакетными курсорами.
Можно взглянуть на ситуацию под другим углом:
. Вместо того чтобы работать с пакетными курсорами, можно добиться точно такого же эффекта посредством инкапсуляции логики и данных в представлениях и опубликовать их для разработчиков. В этом случае разработчики будут нести ответственность за сопровождение своих курсоров; дело в том, что обеспечить нормальное сопровождение с инструментарием, существующим для общедоступных пакетных курсоров, невозможно. А именно, насколько мне известно, невозможно гарантировать использование процедур открытия/закрытия, но курсор всегда будет оставаться видимым для разработчика, который открывает/закрывает его напрямую; следовательно, такая конструкция остается уязвимой. Проблема усугубляется тем, что использование общедоступных пакетных курсоров и процедур открытия/ закрытия может породить в группе ложное чувство безопасности и защищенности».
Пакетное создание курсоров и предоставление доступа к ним всем разработчикам, участвующим в проекте, приносит большую пользу. Проектирование оптимальных структур данных приложения — непростая и кропотливая работа. Эти структуры — и хранящиеся в них данные — используются в программах PL/SQL, а работа с ними почти всегда осуществляется через курсоры. Если вы не определите свои курсоры в пакетах и не предоставите их «в готовом виде» всем разработчикам, то каждый будет писать собственную реализацию курсора, а это создаст массу проблем с производительностью и сопровождением кода. Пакетные курсоры являются лишь одним из примеров инкапсуляции доступа к структурам данных.
2. Вызовите процедуру в SQL * PLUS:
Вызов процедуры только с параметрами режима IN Метод: в SQL * PLUS используйте команду EXECUTIVE для вызова процедуры. Например: вызовите fire_emp из SQL * PLUS. Процедура fire_emp имеет только параметры режима IN, которые заменены определенными значениями:
Процесс вызова другого пользователя в одном пользователе
Метод: добавьте имя пользователя, которому он принадлежит, перед именем процесса, чтобы ограничить его.
Повторно инициализируемые пакеты
По умолчанию пакетные данные сохраняются в течение всего сеанса (или до перекомпиляции пакета). Это исключительно удобное свойство пакетов, но и у него имеются определенные недостатки:
- Постоянство глобально доступных (общих и приватных) структур данных сопровождается нежелательными побочными эффектами. В частности, можно случайно оставить пакетный курсор открытым, а в другой программе попытаться открыть его без предварительной проверки, что приведет к ошибке.
- Если данные хранятся в структурах уровня пакетов, то программа может занять слишком большой объем памяти, не освобождая ее.
Для оптимизации использования памяти при работе с пакетами можно использовать директиву SERIALLY_REUSABLE . Она указывает Oracle, что пакет является повторно инициализируемым, то есть его состояние (состояние переменных, открытых пакетных курсоров и т. п.) нужно сохранять не на протяжении сеанса, а на время одного вызова пакетной программы.
Рассмотрим действие этой директивы на примере пакета book_info . В нем имеются две отдельные программы: для заполнения списка книг и для вывода этого списка.
Как видно из приведенного ниже тела пакета, список объявляется как приватный глобальный ассоциативный массив:
Чтобы увидеть, как работает эта директива, заполним список и выведем его на экран. В первом варианте оба шага выполняются в одном блоке:
Заполнение и вывод в одном блоке:
Во второй версии заполнение и вывод списка производятся в разных блоках. В результате коллекция окажется пустой:
Заполнение в первом блоке
Вывод во втором блоке:
Работая с повторно инициализируемыми пакетами, необходимо учитывать некоторые особенности:
2.2. Создайте функцию
Функции аналогичны процедурам. У обоих есть параметры, и у параметров есть шаблоны. Оба они отличаются от блоков PL / SQL с объявлениями, исполняемыми файлами и частями обработки исключений. Оба могут быть сохранены в базе данных или объявлены в блоке. Разница между ними в том, что сам вызов процедуры является оператором PL / SQL, а вызов функции выполняется как часть выражения.
Список параметров не является обязательным. Но тип возврата функции обязателен. Потому что вызов функции является частью выражения. Оператор RETURN используется для управления возвратом значения в вызывающую среду.
Формат синтаксиса оператора RETURN: RETURN (выражение), где: выражение - это возвращаемое значение, а тип значения такой же, как и тип, указанный в предложении RETURN определения функции. Когда выполняется инструкция RETURN, управление немедленно возвращается в вызывающую среду.
В функции можно использовать несколько операторов RETURN, но выполняется только один оператор RETURN.
(1) Использование функции без параметров:
(2) Использование функции с параметрами:
Пример: используйте функцию для реализации запроса, чтобы получить зарплату сотрудника:
Используйте функцию для реализации запроса, чтобы получить зарплату определенной должности:
Функции можно использовать в любом выражении и в следующих ситуациях:
(1) оператор выбора (2), где пункт (3) упорядочивает и группирует по предложениям оператора выбора (4) значения в операторе вставки (5) пункт набора обновления
Проверка ограничений размера
Представление USER_0BJECT_SIZE предоставляет информацию о размере программ, хранимых в базе данных:
- S0URCE_SIZE — размер исходного кода в байтах. Код должен находиться в памяти во время компиляции (включая динамическую/автоматическую перекомпиляцию).
- PARSED_SIZE — размер объекта в байтах после разбора. Эта форма должна находиться в памяти при компиляции любого объекта, ссылающегося на данный объект.
- C0DE_SIZE — размер кода в байтах. Код должен находиться в памяти при выполнении объекта.
Следующий запрос выводит кодовые объекты с размером больше заданного. Например, вы можете выполнить этот запрос для идентификации программ, закрепляемых в базе данных средствами DBMS_SHARED_P00L для того, чтобы свести к минимуму подгрузку кода в SGA :
Анализ использования идентификаторов (Oracle Database 11g)
Проходит совсем немного времени, и рост объема и сложности кодовой базы создает серьезные проблемы с сопровождением и эволюцией. Допустим, мне потребовалось реализовать новую возможность в части существующей программы. Как убедиться в том, что я правильно оцениваю последствия от появления новой функции, и внести все необходимые изменения? До выхода Oracle Database 11g инструменты, которые могли использоваться для анализа последствий, в основном ограничивались запросами к ALL_DEPENDENCIES и ALL_SOURCE . Теперь, с появлением PL/Scope , я могу выполнять намного более подробный и полезный анализ.
PL/Scope собирает информацию об идентификаторах в исходном коде PL/SQL при компиляции кода и предоставляет собранную информацию в статических представлениях словарей данных. Собранная информация, доступная через USER_IDENTIFIERS , содержит очень подробные сведения о типах и использовании (включая объявления, ссылки, присваивание и т. д.) каждого идентификатора, а также о местонахождении использования в исходном коде.
Описание представления USER_IDENTIFIERS :
Name | Null? | Type |
NAME | VARCHAR2(128) | |
SIGNATURE | VARCHAR2(32) | |
TYPE | VARCHAR2(18) | |
OBJECT_NAME | NOT NULL | VARCHAR2(128) |
OBJECT_TYPE | VARCHAR2(13) | |
USAGE | VARCHAR2(11) | |
USAGE_ID | NUMBER | |
LINE | NUMBER | |
COL | NUMBER | |
USAGE_CONTEXT_ID | NUMBER |
Вы можете писать запросы к USER_IDENTIFIERS для поиска в коде разнообразной информации, включая нарушения правил об именах. Такие редакторы P L/SQL , как Toad, наверняка скоро начнут предоставлять доступ к данным PL/Scope , упрощая их использование для анализа кода. А пока этого не произошло, вам придется строить собственные запросы (или использовать написанные и опубликованные другими разработчиками).
Чтобы использовать PL/Scope , необходимо сначала приказать компилятору PL/SQL проанализировать идентификаторы программы в процессе компиляции. Для этого следует изменить значение параметра компилятора PLSC0PE_SETTINGS . Это можно делать на уровне сеанса и даже для отдельной программы, как в следующем примере:
Чтобы узнать значение PLSC0PE_SETTINGS для любой конкретной программы, обратитесь с запросом к USER_PLSQL_0BJECT_SETTINGS .
После включения PL/Scope при компиляции программы Oracle будет заполнять словарь данных подробной информацией об использовании каждого идентификатора в программе (переменные, типы, программы и т. д.).
Рассмотрим несколько примеров использования PL/Scope . Допустим, я создаю следующую спецификацию пакета и процедуру с включенной поддержкой PL/Scope:
Настройки PL/Scope проверяются следующим образом:
Проверка объявлений, обнаруженных в процессе компиляции этих двух программ:
Теперь я могу получить информацию обо всех локально объявляемых переменных:
Впечатляет, однако возможности PL/Scope этим не ограничиваются. Я могу получить информацию обо всех местах программы, в которых используется эта переменная, а также о типе использования:
Даже из этих простых примеров видно, что PL/Scope предоставляет выдающиеся возможности для того, чтобы лучше разобраться в коде и проанализировать изменения. Лукас Джеллема из AMIS предоставил более интересные и сложные примеры использования PL/Scope для проверки имен. Соответствующие запросы содержатся в файле 11g_plscope_amis.sql на сайте книги.
Кроме того, я создал вспомогательный пакет и демонстрационные сценарии, которые помогут вам начать работу с PL/Scope. Просмотрите файлы plscope_helper*.* , а также другие файлы plscope*.*.
This chapter describes Oracle Text querying and associated features.
This chapter contains the following topics:
Вывод и поиск исходного кода
Исходный код программ следует всегда хранить в текстовых файлах (или в средах разработки, предназначенных для хранения и работы с кодом PL/SQL за пределами Oracle). Однако хранение программ в базе данных позволяет использовать SQL-запросы для анализа исходного кода по всем модулям, что непросто сделать в текстовом редакторе.
Представление USER_SOURCE содержит исходный код всех объектов, принадлежащих текущему пользователю. Его структура такова:
Name | Null? | Type |
NAME | NOT NULL | VARCHAR2(30) |
TYPE | VARCHAR2(12) | |
LINE | NOT NULL | NUMBER |
TEXT | VARCHAR2(4000) |
Здесь NAME — имя объекта, TYPE — его тип (от программ PL/SQL до блоков Java и исходного кода триггеров), LINE — номер строки, а TEXT — текст исходного кода. Представление USER_SOURCE является чрезвычайно ценным источником информации для разработчиков. При помощи соответствующих запросов разработчик может:
- вывести строку исходного кода с заданным номером;
- проверить стандарты кодирования;
- выявить возможные ошибки и дефекты в исходном коде;
- найти программные конструкции, не выявляемые из других представлений. Предположим, в проекте действует правило, согласно которому отдельные разработчики никогда не должны жестко кодировать пользовательские номера ошибок из диапазона от -20 999 до -20 000 (поскольку это может привести к возникновению конфликтов). Конечно, руководитель проекта не может помешать разработчику написать следующую строку:
Но зато он может создать пакет, который находит все программы с подобными фрагментами. Это очень простой пакет, а его главная процедура имеет вид:
После компиляции этого пакета в схеме можно проверить присутствие в программах значений -20NNN :
Обратите внимание: третья строка не противоречит правилам; она выводится только потому, что условие отбора сформулировано недостаточно жестко.
Конечно, этот аналитический инструмент весьма примитивен, но при желании его можно усовершенствовать. Можно, к примеру, генерировать HTML-документ с информацией, размещая его в интрасети, или выполнять сценарии valstd каждое воскресенье средствами DBMS_JOB , чтобы в понедельник утром администратор мог проверить наличие данных обратной связи в интрасети.
3.2. Информация об ошибках компиляции процесса и функции:
Информация об ошибках компиляции процедур и функций получается через USER_ERRORS. К
Процесс вызова удаленной базы данных
Метод: добавьте имя ссылки после имени процесса, чтобы ограничить
Например: вызов ссылки базы данных из SQL * PLUS на удаленную базу данных с именем newsoft. К
Глобальные общедоступные данные
Любая структура данных, объявленная в спецификации пакета, является глобальной общедоступной структурой данных; это означает, что к ней может обратиться любая
программа за пределами пакета. Например, вы можете определить коллекцию PL/ SQL в спецификации пакета и использовать ее для ведения списка работников, заслуживших повышение. Вы также можете создать пакет с константами, которые должны использоваться во всех программах. Другие разработчики будут ссылаться на пакетные константы вместо использования фиксированных значений в программах. Глобальные общедоступные структуры данных также могут изменяться, если только они не были объявлены с ключевым словом CONSTANT .
Глобальные данные обычно считаются источником повышенной опасности в программировании. Их очень удобно объявлять, они прекрасно подходят для того, чтобы вся информация была доступна в любой момент времени — однако зависимость от глобальных структур данных приводит к созданию неструктурированного кода с множеством побочных эффектов.
Вспомните, что спецификация модуля должна сообщать полную информацию, необходимую для вызова и использования этого модуля. Однако по спецификации пакета невозможно определить, выполняет ли пакет чтение и/или запись в глобальные структуры данных. По этой причине вы не можете быть уверены в том, что происходит в приложении и какая программа изменяет те или иные данные.
Передачу данных модулям и из них всегда рекомендуется осуществлять через параметры. В этом случае зависимость от структур данных документируется в спецификации и может учитываться разработчиками. С другой стороны, именованные глобальные структуры данных должны создаваться для информации, действительно глобальной по отношению к приложению — например, констант и параметров конфигурации.
Такие данные следует разместить в централизованном пакете. Однако учтите, что при такой архитектуре в приложении возникает «единая точка перекомпиляции»: каждый раз, когда вы вносите изменение в пакет и перекомпилируете спецификацию, многие программы приложения теряют работоспособность.
1. Шаги по разработке хранимой процедуры и функции:
(1). Используйте системный редактор или программное обеспечение для обработки текстов, чтобы написать файл сценария, содержащий операторы CREATE PROCEDURE или CREATE FUNCTION; (2.) При написании операторов CREATE PROCEDURE или CREATE FUNCTION вы должны полностью учитывать обработку ошибок времени выполнения, то есть , следует учитывать обработку исключений EXCEPTION; (3). Запустить файл сценария в SQL * PLUS, скомпилировать исходный код процедуры или функции в скомпилированный код P_code и сохранить два кода в базе данных; (4). Отладить ошибки компиляции; (5)). Вызвать разработанную функцию или хранимую процедуру в среде Oracle. К
Независимо от того, успешна ли компиляция, команда процесса / функции создания CREATE PROCEDURE или CREATE FUNCTION автоматически сохранит свой исходный код в базе данных, а скомпилированный код может быть сохранен в базе данных только после успешной компиляции.
Могут быть вызваны только хранимые процедуры и функции, скомпилированный код которых хранится в базе данных. К
Читайте также: