PL / SQL – Процедуры
В этой главе мы обсудим процедуры в PL / SQL. Подпрограмма – это программный модуль / модуль, который выполняет определенную задачу. Эти подпрограммы объединяются в большие программы. Это в основном называется «Модульный дизайн». Подпрограмма может быть вызвана другой подпрограммой или программой, которая называется вызывающей программой .
Подпрограмма может быть создана –
- На уровне схемы
- Внутри упаковки
- Внутри блока PL / SQL
На уровне схемы подпрограмма является отдельной подпрограммой . Он создается с помощью CREATE PROCEDURE или оператора CREATE FUNCTION. Он хранится в базе данных и может быть удален с помощью оператора DROP PROCEDURE или DROP FUNCTION.
Подпрограмма, созданная внутри пакета, является упакованной подпрограммой . Он хранится в базе данных и может быть удален только тогда, когда пакет удаляется с помощью оператора DROP PACKAGE. Мы обсудим пакеты в главе «PL / SQL – Пакеты» .
Подпрограммы PL / SQL называются блоками PL / SQL, которые могут быть вызваны с помощью набора параметров. PL / SQL предоставляет два вида подпрограмм –
Функции – эти подпрограммы возвращают одно значение; в основном используется для вычисления и возврата значения.
Процедуры – Эти подпрограммы не возвращают значение напрямую; в основном используется для выполнения действий.
Функции – эти подпрограммы возвращают одно значение; в основном используется для вычисления и возврата значения.
Процедуры – Эти подпрограммы не возвращают значение напрямую; в основном используется для выполнения действий.
В этой главе будут рассмотрены важные аспекты процедуры PL / SQL . Мы обсудим функцию PL / SQL в следующей главе.
Части подпрограммы PL / SQL
Каждая подпрограмма PL / SQL имеет имя и может также иметь список параметров. Как и анонимные блоки PL / SQL, именованные блоки также будут иметь следующие три части:
Декларативная часть
Это необязательная часть. Однако декларативная часть подпрограммы не начинается с ключевого слова DECLARE. Он содержит объявления типов, курсоров, констант, переменных, исключений и вложенных подпрограмм. Эти элементы являются локальными для подпрограммы и перестают существовать, когда подпрограмма завершает выполнение.
Исполняемая часть
Это обязательная часть и содержит операторы, которые выполняют назначенное действие.
Обработка исключений
Это опять необязательная часть. Он содержит код, который обрабатывает ошибки во время выполнения.
Декларативная часть
Это необязательная часть. Однако декларативная часть подпрограммы не начинается с ключевого слова DECLARE. Он содержит объявления типов, курсоров, констант, переменных, исключений и вложенных подпрограмм. Эти элементы являются локальными для подпрограммы и перестают существовать, когда подпрограмма завершает выполнение.
Исполняемая часть
Это обязательная часть и содержит операторы, которые выполняют назначенное действие.
Обработка исключений
Это опять необязательная часть. Он содержит код, который обрабатывает ошибки во время выполнения.
Создание процедуры
Процедура создается с помощью оператора CREATE OR REPLACE PROCEDURE . Упрощенный синтаксис для оператора CREATE OR REPLACE PROCEDURE выглядит следующим образом:
имя-процедуры определяет имя процедуры.
Опция [ИЛИ ЗАМЕНА] позволяет модифицировать существующую процедуру.
Список необязательных параметров содержит имя, режим и типы параметров. IN представляет значение, которое будет передано извне, а OUT представляет параметр, который будет использоваться для возврата значения вне процедуры.
Тело процедуры содержит исполняемую часть.
Ключевое слово AS используется вместо ключевого слова IS для создания отдельной процедуры.
имя-процедуры определяет имя процедуры.
Опция [ИЛИ ЗАМЕНА] позволяет модифицировать существующую процедуру.
Список необязательных параметров содержит имя, режим и типы параметров. IN представляет значение, которое будет передано извне, а OUT представляет параметр, который будет использоваться для возврата значения вне процедуры.
Тело процедуры содержит исполняемую часть.
Ключевое слово AS используется вместо ключевого слова IS для создания отдельной процедуры.
пример
В следующем примере создается простая процедура, которая отображает строку «Hello World!» на экране при исполнении.
Когда приведенный выше код выполняется с использованием подсказки SQL, он даст следующий результат:
Выполнение отдельной процедуры
Автономная процедура может быть вызвана двумя способами:
Использование ключевого слова EXECUTE
Вызов имени процедуры из блока PL / SQL
Использование ключевого слова EXECUTE
Вызов имени процедуры из блока PL / SQL
Вышеупомянутая процедура с именем «приветствия» может быть вызвана с ключевым словом EXECUTE как –
Вышеуказанный вызов будет отображаться –
Процедура также может быть вызвана из другого блока PL / SQL –
Вышеуказанный вызов будет отображаться –
Удаление автономной процедуры
Отдельная процедура удаляется с помощью инструкции DROP PROCEDURE . Синтаксис для удаления процедуры –
Вы можете отказаться от процедуры приветствия, используя следующее утверждение –
Режимы параметров в подпрограммах PL / SQL
В следующей таблице перечислены режимы параметров в подпрограммах PL / SQL –
Параметр IN позволяет передавать значение в подпрограмму. Это параметр только для чтения . Внутри подпрограммы параметр IN действует как константа. Ему нельзя присвоить значение. Вы можете передать константу, литерал, инициализированную переменную или выражение в качестве параметра IN. Вы также можете инициализировать его значением по умолчанию; тем не менее, в этом случае он исключается из вызова подпрограммы. Это режим передачи параметров по умолчанию. Параметры передаются по ссылке .
Параметр OUT возвращает значение вызывающей программе. Внутри подпрограммы параметр OUT действует как переменная. Вы можете изменить его значение и ссылаться на значение после его присвоения. Фактический параметр должен быть переменным, и он передается по значению .
Параметр IN OUT передает начальное значение подпрограмме и возвращает обновленное значение вызывающей стороне. Ему может быть присвоено значение, и значение может быть прочитано.
Фактический параметр, соответствующий формальному параметру IN OUT, должен быть переменной, а не константой или выражением. Формальному параметру должно быть присвоено значение. Фактический параметр передается по значению.
Параметр IN позволяет передавать значение в подпрограмму. Это параметр только для чтения . Внутри подпрограммы параметр IN действует как константа. Ему нельзя присвоить значение. Вы можете передать константу, литерал, инициализированную переменную или выражение в качестве параметра IN. Вы также можете инициализировать его значением по умолчанию; тем не менее, в этом случае он исключается из вызова подпрограммы. Это режим передачи параметров по умолчанию. Параметры передаются по ссылке .
Параметр OUT возвращает значение вызывающей программе. Внутри подпрограммы параметр OUT действует как переменная. Вы можете изменить его значение и ссылаться на значение после его присвоения. Фактический параметр должен быть переменным, и он передается по значению .
Параметр IN OUT передает начальное значение подпрограмме и возвращает обновленное значение вызывающей стороне. Ему может быть присвоено значение, и значение может быть прочитано.
Фактический параметр, соответствующий формальному параметру IN OUT, должен быть переменной, а не константой или выражением. Формальному параметру должно быть присвоено значение. Фактический параметр передается по значению.
Пример режима IN & OUT 1
Эта программа находит минимум двух значений. Здесь процедура берет два числа, используя режим IN, и возвращает их минимум, используя параметры OUT.
Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат –
Пример 2 в режиме IN & OUT
Эта процедура вычисляет квадрат значения переданного значения. В этом примере показано, как мы можем использовать один и тот же параметр, чтобы принять значение и затем вернуть другой результат.
Когда приведенный выше код выполняется в командной строке SQL, он дает следующий результат –
Методы для передачи параметров
Фактические параметры могут быть переданы тремя способами:
- Позиционная запись
- Именное обозначение
- Смешанная запись
Позиционная запись
В позиционной нотации вы можете вызвать процедуру как –
В позиционной нотации первый фактический параметр заменяется первым формальным параметром; второй фактический параметр заменяется вторым формальным параметром и так далее. Таким образом, a заменяет x, b заменяет y, c заменяет z и d заменяет m .
Именное обозначение
В именованной записи фактический параметр связан с формальным параметром с помощью символа стрелки (=>) . Вызов процедуры будет выглядеть следующим образом:
Смешанная запись
В смешанной нотации вы можете смешивать обе нотации в вызове процедуры; однако позиционная нотация должна предшествовать именованной нотации.
Как вызвать процедуру в sql oracle
Use the CALL statement to execute a routine (a standalone procedure or function, or a procedure or function defined within a type or package) from within SQL.
The restrictions on user-defined function expressions specified in «Function Expressions» apply to the CALL statement as well.
Oracle Database PL/SQL Language Reference for information on creating such routine
You must have EXECUTE privilege on the standalone routine or on the type or package in which the routine is defined.
You can execute a routine in two ways. You can issue a call to the routine itself by name, by using the routine_clause , or you can invoke a routine inside the type of an expression, by using an object_access_expression .
Specify the name of the function or procedure being called, or a synonym that resolves to a function or procedure.
When you call a member function or procedure of a type, if the first argument ( SELF ) is a null IN OUT argument, then Oracle Database returns an error. If SELF is a null IN argument, then the database returns null. In both cases, the function or procedure is not invoked.
Restriction on Functions
If the routine is a function, then the INTO clause is required.
Specify the schema in which the standalone routine, or the package or type containing the routine, resides. If you do not specify schema , then Oracle Database assumes the routine is in your own schema.
type or package
Specify the type or package in which the routine is defined.
In a distributed database system, specify the name of the database containing the standalone routine, or the package or function containing the routine. If you omit dblink , then Oracle Database looks in your local database.
«Calling a Procedure: Example» for an example of calling a routine directly
If you have an expression of an object type, such as a type constructor or a bind variable, then you can use this form of expression to call a routine defined within the type. In this context, the object_access_expression is limited to method invocations.
«Object Access Expressions» for syntax and semantics of this form of expression, and «Calling a Procedure Using an Expression of an Object Type: Example» for an example of calling a routine using an expression of an object type
Specify one or more arguments to the routine, if the routine takes arguments. You can use positional, named, or mixed notation for argument . For example, all of the following notations are correct:
Restrictions on Applying Arguments to Routines
The argument is subject to the following restrictions:
The data types of the parameters passed by the CALL statement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN .
An argument cannot be a pseudocolumn or either of the object reference functions VALUE or REF .
Any argument that is an IN OUT or OUT argument of the routine must correspond to a host variable expression.
The number of arguments, including any return argument, is limited to 1000.
You cannot bind arguments of character and raw data types ( CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , RAW , LONG RAW ) that are larger than 4K.
The INTO clause applies only to calls to functions. Specify which host variable will store the return value of the function.
Specify the value or condition of the host variable.
Pro*C/C++ Programmer’s Guide for more information on host variables and indicator variables
Calling a Procedure: Example
The following statement removes the Entertainment department (created in «Inserting Sequence Values: Example» ) using uses the remove_dept procedure. See Oracle Database PL/SQL Language Reference for the example that creates this procedure.
Calling a Procedure Using an Expression of an Object Type: Example
The following examples show how call a procedure by using an expression of an object type in the CALL statement. The example uses the warehouse_typ object type in the order entry sample schema OE :
The next example shows how to use an external function to achieve the same thing:
Русские Блоги
Хранимые процедуры и вызовы функций подпрограмм базы данных Oracle
Хранимые процедуры и вызовы функций подпрограмм базы данных Oracle
Блоки PL / SQL-Безымянный, не хранящиеся в базе данных и не могут быть вызваны из других блоков PL / SQL. Описанная ниже структура — функции, процедуры, пакеты и триггеры — все это хорошо известные блоки, они могут храниться в базе данных и могут вызываться где угодно.
1. Шаги по разработке хранимой процедуры и функции:
(1). Используйте системный редактор или программное обеспечение для обработки текстов, чтобы написать файл сценария, содержащий операторы CREATE PROCEDURE или CREATE FUNCTION; (2.) При написании операторов CREATE PROCEDURE или CREATE FUNCTION вы должны полностью учитывать обработку ошибок времени выполнения, то есть , следует учитывать обработку исключений EXCEPTION; (3). Запустить файл сценария в SQL * PLUS, скомпилировать исходный код процедуры или функции в скомпилированный код P_code и сохранить два кода в базе данных; (4). Отладить ошибки компиляции; (5)). Вызвать разработанную функцию или хранимую процедуру в среде Oracle. К
Независимо от того, успешна ли компиляция, команда процесса / функции создания CREATE PROCEDURE или CREATE FUNCTION автоматически сохранит свой исходный код в базе данных, а скомпилированный код может быть сохранен в базе данных только после успешной компиляции.
Могут быть вызваны только хранимые процедуры и функции, скомпилированный код которых хранится в базе данных. К
2.1. Процесс создания:
Синтаксис оператора процедуры создания следующий:
Описание трех режимов IN, OUT и IN OUT:
Параметры IN (режим по умолчанию)
Он используется для передачи значений из вызывающей среды в хранимую процедуру. Невозможно присвоить значение параметру IN. Значение, передаваемое этому параметру, может быть константой, переменной со значением, выражением, и Т. Д.
Он используется для возврата значения из процедуры вызывающей стороне.Значение этого параметра не может быть присвоено другой переменной и не может быть константой или выражением. В теле процесса параметру OUT должно быть присвоено значение.
Параметр IN OUT
(Входные и выходные параметры)
Вы можете передавать значения от вызывающего объекта к процедуре и возвращать значения, которые могли измениться вызывающему объекту после выполнения процедуры.
Приведите примеры, чтобы проиллюстрировать разницу между тремя параметрами режима и позициями, которые могут появляться в теле процесса:
Используйте процесс обработки запроса, чтобы получить информацию о сотруднике.
2.2. Создайте функцию
Функции аналогичны процедурам. У обоих есть параметры, и у параметров есть шаблоны. Оба они отличаются от блоков PL / SQL с объявлениями, исполняемыми файлами и частями обработки исключений. Оба могут быть сохранены в базе данных или объявлены в блоке. Разница между ними в том, что сам вызов процедуры является оператором PL / SQL, а вызов функции выполняется как часть выражения.
Список параметров не является обязательным. Но тип возврата функции обязателен. Потому что вызов функции является частью выражения. Оператор RETURN используется для управления возвратом значения в вызывающую среду.
Формат синтаксиса оператора RETURN: RETURN (выражение), где: выражение — это возвращаемое значение, а тип значения такой же, как и тип, указанный в предложении RETURN определения функции. Когда выполняется инструкция RETURN, управление немедленно возвращается в вызывающую среду.
В функции можно использовать несколько операторов RETURN, но выполняется только один оператор RETURN.
(1) Использование функции без параметров:
(2) Использование функции с параметрами:
Пример: используйте функцию для реализации запроса, чтобы получить зарплату сотрудника:
Используйте функцию для реализации запроса, чтобы получить зарплату определенной должности:
Функции можно использовать в любом выражении и в следующих ситуациях:
(1) оператор выбора (2), где пункт (3) упорядочивает и группирует по предложениям оператора выбора (4) значения в операторе вставки (5) пункт набора обновления
2.3. Разница между процессом и функцией:
Одно из отличий:
Форма параметра и возвращаемое значение Различные функции имеют ноль или более параметров и только одно возвращаемое значение; процедура имеет ноль или более параметров и не возвращает значение, а возвращаемое значение выводится параметрами OUT; как процедуры, так и функции Могут быть В параметрах IN, и ввод параметров принимается через список параметров; функция не может иметь параметры OUT, и возврат значения функции возвращается предложением RETURN.
Форма звонка другая. Процедура может быть вызвана как один исполняемый оператор.Например, имя процедуры (фактический параметр 1, фактический параметр 2, . ), оператор может появляться отдельно в блоке PL / SQL.
3. Как запросить информацию о процессах и функциях в словаре данных? К
Текст процедуры и функции
Просмотреть словарь данных USER_SOURCE или команду DESCRIBE
Дерево синтаксического анализа
Синтаксический анализ выражений
Код компиляции (p_code)
Синтаксическая ошибка блока PL / SQL
Проверьте словарь данных USER_ERRORS или используйте команду SHOW ERRORS.
Запустить отладочную информацию
Пользовательская отладочная информация для переменных или выражений
Использовать процесс пакета DBMS_OUTPUT
3.1 Исходный код процесса и функции запроса:
Получается путем запроса представления словаря данных USER_SOURCE.
3.2. Информация об ошибках компиляции процесса и функции:
Информация об ошибках компиляции процедур и функций получается через USER_ERRORS. К
4. Вызов процедур и функций:
После того, как процедуры и функции сохранены в базе данных, их можно вызывать из различных сред. Его можно вызвать как из командной строки, так и из конкретного приложения. Но в разных средах вызова синтаксис вызова функций процесса отличается.
Разница между методом вызова процесса и функцией:
В соответствии с законом
Появляется в полном исполняемом заявлении
Может заменить позицию переменной или выражения в операторе возврата
1. Вызов хранимой процедуры в PL / SQL:
Параметр v_empno находится в режиме IN; остальные три — в режиме OUT:
2. Вызовите процедуру в SQL * PLUS:
Вызов процедуры только с параметрами режима IN Метод: в SQL * PLUS используйте команду EXECUTIVE для вызова процедуры. Например: вызовите fire_emp из SQL * PLUS. Процедура fire_emp имеет только параметры режима IN, которые заменены определенными значениями:
Процесс вызова другого пользователя в одном пользователе
Метод: добавьте имя пользователя, которому он принадлежит, перед именем процесса, чтобы ограничить его.
Процесс вызова удаленной базы данных
Метод: добавьте имя ссылки после имени процесса, чтобы ограничить
Например: вызов ссылки базы данных из SQL * PLUS на удаленную базу данных с именем newsoft. К
Вызвать процесс с параметрами OUT
Вызовите процедуру QUERY_EMP из SQL * PLUS. Процедура имеет один параметр IN и три параметра OUT. Три переменные должны быть определены с определением VARIABLE в SQL * PLUS. Присвойте значения этим трем переменным при выполнении процесса. Вы можете использовать следующая реализация сценария SQL * PLUS:
6. Преимущества процедур и функций:
(1) Повысьте безопасность и целостность данных. Используйте разрешения безопасности для управления косвенным доступом к базе данных пользователей, не имеющих достаточных разрешений; путем централизации операций со связанными таблицами для обеспечения согласованного выполнения этих связанных таблиц. Не выполняйте никаких операций или любая операция.
(2) Повышение производительности операций Когда несколько пользователей используют один и тот же оператор SQL, выполняйте только синтаксический анализ; выполняйте синтаксический анализ только во время компиляции и не повторяйте во время выполнения, напрямую вызывайте скомпилированный код.
(3) Экономия места для хранения, использование одного и того же кода для хранения для нескольких различных приложений, высокая ремонтопригодность
How to execute an oracle stored procedure?
I am using oracle 10g express edition. It has a nice ui for db developers. But i am facing some problems executing stored procedures.
it is created successfully. But when i execute:
it shows ORA-00900: invalid SQL statement
So help needed here
7 Answers 7
Execute is sql*plus syntax .. try wrapping your call in begin .. end like this:
(Although Jeffrey says this doesn’t work in APEX .. but you’re trying to get this to run in SQLDeveloper .. try the ‘Run’ menu there.)
Oracle 10g Express Edition ships with Oracle Application Express (Apex) built-in. You’re running this in its SQL Commands window, which doesn’t support SQL*Plus syntax.
That doesn’t matter, because (as you have discovered) the BEGIN. END syntax does work in Apex.
Both ‘is’ and ‘as’ are valid syntax. Output is disabled by default. Try a procedure that also enables output.
. and call it in a PLSQL block.
. as SQL is non-procedural.
I use oracle 12 and it tell me that if you need to invoke the procedure then use call keyword. In your case it should be:
Have you tried to correct the syntax like this?:
In Oracle SQL Developer (GUI), using 12c, also don’t forget to enable the DMBS Output window (click View => Dbms Output, then click "+" sign, and select your connection), by default the window is not enabled.
The following syntax will then output to this window:
You can do simply the following on the Command Window:
-
The Overflow Blog
Linked
Related
Most asked in [oracle]
Hot Network Questions
Subscribe to RSS
To subscribe to this RSS feed, copy and paste this URL into your RSS reader.
Site design / logo © 2023 Stack Exchange Inc; user contributions licensed under CC BY-SA . rev 2023.3.11.43304
By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.