Хранимые процедуры

Содержание

Создание хранимой процедуры PL/SQL

Для того чтобы написать собственную программу на PL/SQL, нужно воспользоваться одной из инструкций . Например, если вы хотите создать хранимую функцию именем wordcount для подсчета количества слов в строке, выполните инструкцию :: 


Как и в случае с простыми блоками приводившимися ранее, код этой инструкции в SQL*Plus должен завершаться символом косой черты, который размещается в отдельной строке.

Если администратор базы данных предоставил вам привилегию создания процедур (которая также включает привилегию создания функций), эта инструкция заставит Oracle откомпилировать и сохранить в схеме заданную хранимую функцию. И если код будет откомпилирован успешно, вы увидите следующее сообщение: 

Если в схеме Oracle уже имеется объект (таблица или пакет) с именем , выполнение инструкции завершится сообщением об ошибке:

По этой причине Oracle поддерживает инструкцию — вероятно, вы будете использовать ее в 99 случаях из 100: 

Связка позволяет избежать побочных эффектов, вызванных удалением и повторным созданием программ; она сохраняет все привилегии на объект, предоставленные другим пользователям или ролям. При этом она заменяет только объекты одного типа и не станет автоматически удалять таблицу с именем только потому, что вы решили создать функцию с таким же именем.

Программисты обычно сохраняют подобные команды (равно как и анонимные блоки, предназначенные для повторного использования) в файлах операционной системы. Например, для хранения рассматриваемой функции можно было бы создать файл , а для его запуска применить команду SQL*Plus : 

Как упоминалось ранее, SQL*Plus по умолчанию не выводит содержимое сценария на экран. Для того чтобы исходный код сценария, включая присвоенные Oracle номера строк, отображался на экране, воспользуемся командой . Особенно полезна эта команда в ходе диагностики. Давайте намеренно допустим в программе ошибку, закомментировав объявление переменной: 

Предупреждение сообщает нам о том, что функция была создана, но из-за ошибок компиляции ее выполнение невозможно. Нам удалось сохранить исходный код в базе данных; теперь нужно извлечь подробную информацию об ошибке из базы данных. Проще всего это сделать с помощью команды SQL*Plus которую можно сократить до : 

Вывод других ошибок

Многие программисты Oracle знают только одну форму команды SQL*Plus:

Они ошибочно полагают, что для получения дополнительной информации об ошибках, не встречавшихся при последней компиляции, необходимо обращаться с запросом к представлению . Однако если указать в команде категорию и имя объекта, вы получите информацию о последних связанных с ним ошибках: 

Например, чтобы просмотреть информацию о последних ошибках в процедуре , выполните такую команду:

Будьте внимательны при интерпретации выходного сообщения:

No errors.

Оно выводится в трех случаях: (1) когда код объекта откомпилирован успешно; (2) вы задали неверную категорию (скажем, функцию вместо процедуры); и (3) объект с заданным именем не существует.

Полный список категорий, поддерживаемых этой командой, зависит от версии СУБД, но в него как минимум входят следующие категории: 

Компилятор обнаружил оба вхождения переменной и сообщил точные номера строк и столбцов. Более подробную информацию об ошибке можно найти по идентификатору (в данном случае PLS-00201) в документации Oracle Database Error Messages.

Во внутренней реализации команда обращается с запросом к представлению Oracle из словаря данных. В принципе вы можете обращаться к этому представлению и самостоятельно, но обычно это просто не нужно (см. врезку «Вывод других ошибок»).

Команда часто добавляется послед каждой инструкции , создающей хранимую программу PL/SQL. Поэтому типичный шаблон для построения хранимых процедур в SQL*Plus может начинаться так:


(Обычно я не включаю команду в сценарий, а просто ввожу ее в командной строке, когда это потребуется.)

Если ваша программа содержит ошибку, которая может быть обнаружена компилятором, инструкция сохранит эту программу в базе данных, но в нерабочем состоянии. Если же вы неверно используете синтаксис , то Oracle не поймет, что вы пытаетесь сделать, и не сохранит код в базе данных.

Управление привилегиями и создание синонимов хранимых процедур

Созданную вами программу на PL/SQL обычно не может выполнять никто, кроме вас или администратора базы данных. Предоставить право на ее применение другому пользователю можно с помощью инструкции :

Инструкция лишает пользователя этой привилегии:

Привилегия выполнения также может быть представлена роли:

а также всем пользователям Oracle:

Если привилегия представляется отдельному пользователю (например, с идентификатором ), затем — роли, в которую входит этот пользователь (например, ), и наконец, — всем пользователям, Oracle запомнит все три варианта ее предоставления. Любой из них позволит пользователю выполнять программу. Но если вы захотите лишить данного пользователя этой возможности, то сначала следует отменить привилегию пользователя с идентификатором , а затем аннулировать привилегию на выполнение функции для всех пользователей () и роли (или же исключить пользователя из этой роли).

Для просмотра списка привилегий, предоставленных другим пользователям и ролям, можно запросить информацию представления . Имена программ в этом представлении почему-то выводятся в столбце : 

Если пользователь имеет привилегию на выполнение программы , он, возможно, захочет создать для нее синоним, чтобы ему не приходилось указывать перед именем программы префикс с именем схемы: 

Теперь пользователь может выполнять программу, ссылаясь на ее синоним:

Так удобнее, потому что в случае изменения владельца программы достаточно будет изменить только ее синоним, а не все те хранимые процедуры, из которых она вызывается.

Синоним можно определить для процедуры, функции, пакета или пользовательского типа. В синонимах процедур, функций и пакетов может скрываться не только схема, но и база данных; синонимы для удаленных программ создаются так же просто, как и для локальных. Однако синонимы могут скрывать только идентификаторы схем и баз данных; синоним не может использоваться вместо пакетной подпрограммы.

Созданный синоним удаляется простой командой:

Создание представлений и хранимых процедурCreate views and stored procedures

Представление является хранимой инструкцией SELECT, а хранимая процедура представляет собой одну или более инструкций Transact-SQLTransact-SQL , выполняемых в виде пакета.A view is a stored SELECT statement, and a stored procedure is one or more Transact-SQLTransact-SQL statements that execute as a batch.

Представления запрашиваются так же, как таблицы, и не принимают параметры.Views are queried like tables and do not accept parameters. Хранимые процедуры сложнее, чем представления.Stored procedures are more complex than views. Хранимые процедуры содержат как входные, так и выходные параметры и могут содержать инструкции, которые управляют потоком кода, например IF и WHILE.Stored procedures can have both input and output parameters and can contain statements to control the flow of the code, such as IF and WHILE statements. Использование хранимых процедур для всех повторяющихся действий в базе данных является хорошим стилем программирования.It is good programming practice to use stored procedures for all repetitive actions in the database.

В этом примере используется инструкция CREATE VIEW, чтобы создать представление, которое выбирает только два столбца в таблице Products .For this example, you will use CREATE VIEW to create a view that selects only two of the columns in the Products table. Затем с помощью инструкции CREATE PROCEDURE создается хранимая процедура, которая принимает цену в качестве параметра и возвращает только те продукты, цена которых меньше значения, указанного в качестве параметра.Then, you will use CREATE PROCEDURE to create a stored procedure that accepts a price parameter and returns only those products that cost less than the specified parameter value.

Создание представленияCreate a view

Выполните следующую инструкцию, создающую представление, которое выполняет инструкцию select и возвращает названия и цены продуктов пользователю.Execute the following statement to create a view that executes a select statement, and returns the names and prices of our products to the user.

Тестирование представленияTest the view

С представлениями обращаются так же, как с таблицами.Views are treated just like tables. Используйте инструкцию , чтобы получить доступ к представлению.Use a statement to access a view.

Создание хранимой процедурыCreate a stored procedure

В следующем примере создается хранимая процедура с входным параметром типа .The following statement creates a stored procedure name , accepts an input parameter named of data type . Эта хранимая процедура печатает инструкцию , соединенную операцией сцепления с входным параметром, тип которого преобразуется из в .The stored procedure prints the statement concatenated with the input parameter that is changed from the data type into a character data type. Затем процедура выполняет инструкцию на представлении, передавая входной параметр в предложение .Then, the procedure executes a statement on the view, passing the input parameter as part of the clause. Возвращаются все продукты, цена которых меньше значения входного параметра.This returns all products that cost less than the input parameter value.

Тестирование хранимой процедурыTest the stored procedure


Чтобы выполнить хранимую процедуру, введите и выполните следующую инструкцию.To test the stored procedure, type and execute the following statement. Эта процедура должна возвратить названия двух продуктов, введенных в таблицу на занятии 1, цена которых меньше .The procedure should return the names of the two products entered into the table in Lesson 1 with a price that is less than .

Параметры в процедурах

Последнее обновление: 14.08.2017

Процедуры могут принимать параметры. Параметры бывают входными — с их помощью в процедуру можно передать некоторые значения. И также параметры бывают выходными — они позволяют возвратить из процедуры некоторое значение.

Например, пусть в базе данных будет следующая таблица Products:

USE productsdb;
CREATE TABLE Products
(
    Id INT IDENTITY PRIMARY KEY,
    ProductName NVARCHAR(30) NOT NULL,
    Manufacturer NVARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price MONEY NOT NULL
);

Определим процедуру, которая будет добавлять данные в эту таблицу:

USE productsdb;
GO
CREATE PROCEDURE AddProduct
	@name NVARCHAR(20),
	@manufacturer NVARCHAR(20),
	@count INT,
	@price MONEY
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price) 
VALUES(@name, @manufacturer, @count, @price)

После названия процедуры идет список входных параметров, которые определяются также как и переменные — название начинается с символа @, а после названия идет тип переменной. И с помощью команды INSERT значения этих параметров будут передаваться в таблицу Products.

Используем эту процедуру:

USE productsdb;

DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);
DECLARE @prodCount INT, @price MONEY
SET @prodName = 'Galaxy C7'
SET @company = 'Samsung'
SET @price = 22000
SET @prodCount = 5

EXEC AddProduct @prodName, @company, @prodCount, @price

SELECT * FROM Products

Здесь передаваемые в процедуру значения определяются через переменные. При вызове процедуры ей через запятую передаются значения. При этом значения передаются параметрам процедуры по позиции. Так как первым определен параметр @name, то ему будет передаваться первое значение — значение переменной @prodName. Второму параметру — @manufacturer передается второе значение — значение переменной @company и так далее. Главное, чтобы между передаваемыми значениями и параметрами процедуры было соответствие по типу данных.

Также можно было бы передать непосредственно значения:

EXEC AddProduct 'Galaxy C7', 'Samsung', 5, 22000

Также значения параметрам процедуры можно передавать по имени:

USE productsdb;

DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20);
SET @prodName = 'Honor 9'
SET @company = 'Huawei'

EXEC AddProduct @name = @prodName, 
				@manufacturer=@company,
				@count = 3, 
				@price = 18000

При передаче параметров по имени параметру процедуры присваивается некоторое значение.

Необязательные параметры

Параметры можно отмечать как необязательные, присваивая им некоторое значение по умолчанию. Например, в случае выше мы можем автоматически устанавливать для количества товара значение 1, если соответствующее значение не передано в процедуру:

USE productsdb;
GO
CREATE PROCEDURE AddProductWithOptionalCount
	@name NVARCHAR(20),
	@manufacturer NVARCHAR(20),
	@price MONEY,
	@count INT = 1
AS
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price) 
VALUES(@name, @manufacturer, @count, @price)

При этом необязательные параметры лучше помещать в конце списка параметров процедуры.

DECLARE @prodName NVARCHAR(20), @company NVARCHAR(20), @price MONEY
SET @prodName = 'Redmi Note 5A'
SET @company = 'Xiaomi'
SET @price = 22000

EXEC AddProductWithOptionalCount @prodName, @company, @price

SELECT * FROM Products

И в этом случае для параметра @count в процедуру можно не передавать значение.

НазадВперед

Предварительные требованияPrerequisites

Для работы с этим руководством необходима среда SQL Server Management Studio и доступ к экземпляру SQL Server.To complete this tutorial, you need SQL Server Management Studio and access to a SQL Server instance.

Установите SQL Server Management Studio.Install SQL Server Management Studio.

Если у вас нет экземпляра SQL Server, создайте его.If you don’t have a SQL Server instance, create one. Чтобы создать экземпляр, выберите свою платформу по следующим ссылкам.To create one, select your platform from the following links. При выборе проверки подлинности SQL используйте учетные данные SQL Server.If you choose SQL Authentication, use your SQL Server login credentials.

  • Windows: скачать выпуск SQL Server 2017 Developer Edition.Windows: Download SQL Server 2017 Developer Edition.
  • macOS: скачать SQL Server 2017 для Docker.macOS: Download SQL Server 2017 on Docker.

Вложенные подпрограммы

Каждый раз, когда я пишу сколько-нибудь нетривиальную программу длиной более 20 строк, я создаю один или несколько локальных модулей. Это помогает мне следить за логикой решения; я могу рассматривать свой код на более высоком уровне абстракции, присваивая имя целой последовательности команд, а также выполнять нисходящее проектирование и пошаговую проработку требований. Наконец, модуляризация кода даже в рамках одной программы позволяет легко выделить вложенную подпрограмму и создать действительно автономную процедуру или функцию, пригодную для повтор­ного использования.

Конечно, логику также можно вывести из локальной области действия и преобразовать ее в отдельную программу уровня тела пакета (если предположить, что код пишется в составе пакета). Этот подход сокращает глубину вложения локальных процедур, что может быть полезно. Однако он может привести к появлению тел пакетов, содержащих очень длинные списки программ, многие из которых используются только из другой программы. Мой общий принцип заключается в том, что определение элемента должно располагаться как можно ближе к месту его использования, что естественным образом приводит к созданию вложенных подпрограмм.

Надеюсь, сейчас вам вспомнилась какая-нибудь из написанных вами программ. Вер­нитесь к ней и устраните повторяющийся код, почистите логику — в общем, сделайте программу более понятной для другого читателя. Не боритесь с искушением. Проведите модуляризацию своего кода.

Чтобы помочь вам в определении вложенных подпрограмм и работе с ними в ваших приложениях, я создал пакет . При помощи этого пакета вы тратите немного времени на расстановку в коде «индикаторов» (фактически инструкций относительно того, какие вложенные подпрограммы вы хотите построить и как именно). Затем вы компилируете этот шаблон в базу данных, вызываете для программного модуля — и вложенные подпрограммы строятся автоматически!

Процесс можно повторить на каждом последующем уровне структуры вашей программы. При этом быстро формируется модульная архитектура, которую вы (и ваши коллеги) непременно оцените в будущем.

Более полное описание пакета , исходный код и примеры сценариев приводятся в файле TopDown.zip.

Вас заинтересует / Intresting for you:


Управление приложениями PL/SQL… 2157 просмотров Rasen Fasenger Thu, 16 Jul 2020, 06:20:48

Встроенные методы коллекций PL… 4370 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Работа с числами в PL/SQL на п… 10044 просмотров Antoniy Mon, 28 May 2018, 16:45:11

Основы языка PL/SQL: использов… 2302 просмотров Ирина Светлова Tue, 06 Feb 2018, 14:04:03

Author: Денис

Другие статьи автора:

Именованные параметры в CallableStatement

В предыдущем примере мы использовали для идентификации каждого параметра хранимой процедуры его позицию в списке. Можно идентифицировать параметры по имени; это упрощает код и облегчает его чтение.

Следующий пример демонстрирует использование именованных параметров в Java-приложении. Отметим, что имена параметров соответствуют именам параметров в определении хранимой процедуры.

Листинг 14. Использование именованных параметров в Java-приложении
public static void executeStoredProcOUTParams(Connection con,int o_id) {
    try {
    CallableStatement cstmt = con.prepareCall("{call GetTotal(?, ?)}");
    cstmt.setInt("Order_ID", o_id);
    cstmt.registerOutParameter("TotalPrice", Types.INTEGER);
    cstmt.execute();
    System.out.println("Total price for order"+ o_id +"is $"+cstmt.getInt("TotalPrice"));
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Параметры должны указываться либо по индексу, либо по имени; смешивать эти два метода нельзя. Два приведенных Java-примера выводят суммарную стоимость указанного заказа, как показано ниже:

Листинг 15. Выходная информация двух java-примеров, приведенных в листингах 13 и 14
$java sample_Stored procedure_3
Total price for order 1002 is $1200
$

ПРИМЕЧАНИЕ. Для выполнения хранимой процедуры в этих примерах используется метод класса . Он используется потому, что хранимая процедура не возвращает набор записей. Если бы она возвращала набор записей, нужно было бы использовать метод , как показано в следующем примере.

Листинг 16. Хранимая процедура, иллюстрирующая применение метода executeQuery()
CREATE PROCEDURE GETTOTALBYMANU(CODE CHAR(3),  OUT TOTAL MONEY) 
RETURNING CHAR(3) AS MANU_CODE, CHAR(10) AS MANU_NAME;  
DEFINE W_MANU_CODE CHAR(3);
DEFINE W_MANU_NAME CHAR(10); 
LET TOTAL=(SELECT SUM(TOTAL_PRICE) FROM ITEMS WHERE MANU_CODE=CODE);   
SELECT MANU_CODE,MANU_NAME
  INTO W_MANU_CODE,W_MANU_NAME FROM MANUFACT WHERE MANU_CODE=CODE;
RETURN W_MANU_CODE,W_MANU_NAME;		      
END PROCEDURE;

Метод, приведенный в листинге 17, использует для активизации хранимой процедуры .

Листинг 17. Java-код, демонстрирующий применение метода executeQuery()
public static void executeStoredProcOUTParamsResulset(Connection con,String manu_id) {
    try {
        CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");
        cstmt.setString(1, manu_id);	  	  
        cstmt.registerOutParameter(2, Types.CHAR);
        ResultSet rs = cstmt.executeQuery();
        rs.next();		
        System.out.println("Total for manufacturer '"+rs.getString(2).trim()+
        " ("+rs.getString(1)+") ' is $"+cstmt.getInt(2));
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Выходная информация программы, приведенной в листинге 17, показана в листинге 18.

Листинг 18. Выходная информация Java-примера, приведенного в листинге 17
$java sample_Stored procedure_4
Total for manufacturer 'Hero (HRO)' is $2882
$

ПРИМЕЧАНИЕ. Если неизвестно, как была определена хранимая процедура, для получения информации о хранимой процедуре (например, имен и типов параметров) можно использовать подпрограммы JDBC Metadata.

В следующем примере для получения имени и типа процедуры используется метод .

Листинг 19. Java-код
public static void executeStoredGetOutParams(Connection con,String procname) {
    try {
        DatabaseMetaData dbmd = con.getMetaData();
        ResultSet rs = dbmd.getProcedureColumns("stores7","",procname.toUpperCase(),null);
        while (rs.next())
        if (rs.getShort("COLUMN_TYPE")==DatabaseMetaData.procedureColumnOut) {
        System.out.println("OUT Parame: "+ rs.getString("COLUMN_NAME"));
        System.out.println("Type: "+rs.getString("DATA_TYPE") );		 
        }	  
    }
    catch (Exception e) {
        e.printStackTrace();
    }
}

Альтернативой является проверка хранимой процедуры на наличие параметров при помощи метода . Если она была определена с параметрами , возвращается (см. листинг 20).

Листинг 20. Пример Java-кода
CallableStatement cstmt = con.prepareCall("{ call gettotalbymanu(?,?)}");
if (((IfxCallableStatement) cstmt).hasOutParameter())
System.out.println("Stored procedure has OUT parameters ");
// выполнить логику

Изменение структуры хранимых процедур

Компонент Database Engine также поддерживает инструкцию ALTER PROCEDURE для модификации структуры хранимых процедур. Инструкция ALTER PROCEDURE обычно применяется для изменения инструкций Transact-SQL внутри процедуры. Все параметры инструкции ALTER PROCEDURE имеют такое же значение, как и одноименные параметры инструкции CREATE PROCEDURE. Основной целью использования этой инструкции является избежание переопределения существующих прав хранимой процедуры.

Компонент Database Engine поддерживает тип данных CURSOR. Этот тип данных используется для объявления курсоров в хранимых процедурах. Курсор — это конструкция программирования, применяемая для хранения результатов запроса (обычно набора строк) и для предоставления пользователям возможности отображать этот результат построчно.

Для удаления одной или группы хранимых процедур используется инструкция DROP PROCEDURE. Удалить хранимую процедуру может только ее владелец или члены предопределенных ролей db_owner и sysadmin.

Первая хранимая процедура

Итак, открываем MySQL Administrator, подключаемся к серверу MySQL и создаем новую схему (базу данных): щелкните Catalogs, выберите Create New Schema в области Schemata (Ctrl+N). Назовите ее как-нибудь (например db). Откройте только что созданную схему, выберите вкладку Stored procedures и щелкните кнопку Create Stored Proc. Назовите свою процедуру procedure1. В тело процедуры (между BEGIN и END) впишите следующее:

SELECT "This is my stored procedure";

И нажмите Execute SQL — процедура создана. Откройте MySQL Query Browser, выберите свою схему (db) и впишите следующий запрос:

CALL procedure1();

Вуала! Поздравляю.


С этим читают