Хранимые процедуры (компонент database engine)stored procedures (database engine)

Содержание:

SQL Учебник
Создание и выполнение определяемых пользователем функций
Именованные параметры в CallableStatement
Шаг 2: Как работать с хранимыми процедурами
Функции для работы с текстом
SQL Справочник
Выходные параметры хранимых процедур
Простые и системные переменные
Создание и выполнение процедур
Параметры в процедурах
- Необязательные параметры
Вызов хранимых процедур с несколькими сигнатурами

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

Создание и выполнение определяемых пользователем функций

Урок 1. создание объектов базы данных и отправка запросов к нимlesson 1: create and query database objects

Определяемые пользователем функции создаются посредством инструкции CREATE FUNCTION, которая имеет следующий синтаксис:



Соглашения по синтаксису

Параметр schema_name определяет имя схемы, которая назначается владельцем создаваемой UDF, а параметр function_name определяет имя этой функции. Параметр @param является входным параметром функции (формальным аргументом), чей тип данных определяется параметром type. Параметры функции — это значения, которые передаются вызывающим объектом определяемой пользователем функции для использования в ней. Параметр default определяет значение по умолчанию для соответствующего параметра функции. (Значением по умолчанию также может быть NULL.)

Предложение RETURNS определяет тип данных значения, возвращаемого UDF. Это может быть почти любой стандартный тип данных, поддерживаемый системой баз данных, включая тип данных TABLE. Единственным типом данных, который нельзя указывать, является тип данных timestamp.

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

Параметр WITH ENCRYPTION в системном каталоге кодирует информацию, содержащую текст инструкции CREATE FUNCTION. Таким образом, предотвращается несанкционированный просмотр текста, который был использован для создания функции. Данная опция позволяет повысить безопасность системы баз данных.

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

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

все представления и другие UDF, к которым обращается определяемая функция, должны быть привязаны к схеме;
все объекты базы данных (таблицы, представления и UDF) должны быть в той же самой базе данных, что и определяемая функция.

Параметр block определяет блок BEGIN/END, содержащий реализацию функции. Последней инструкцией блока должна быть инструкция RETURN с аргументом. (Значением аргумента является возвращаемое функцией значение.) Внутри блока BEGIN/END разрешаются только следующие инструкции:

инструкции присвоения, такие как SET;
инструкции для управления ходом выполнения, такие как WHILE и IF;
инструкции DECLARE, объявляющие локальные переменные;
инструкции SELECT, содержащие списки столбцов выборки с выражениями, значения которых присваиваются переменным, являющимися локальными для данной функции;
инструкции INSERT, UPDATE и DELETE, которые изменяют переменные с типом данных TABLE, являющиеся локальными для данной функции.

По умолчанию инструкцию CREATE FUNCTION могут использовать только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присвоить это право другим пользователям с помощью инструкции GRANT CREATE FUNCTION.

В примере ниже показано создание функции ComputeCosts:

Функция ComputeCosts вычисляет дополнительные расходы, возникающие при увеличении бюджетов проектов. Единственный входной параметр, @percent, определяет процентное значение увеличения бюджетов. В блоке BEGIN/END сначала объявляются две локальные переменные: @addCosts и @sumBudget, а затем с помощью инструкции SELECT переменной @sumBudget присваивается общая сумма всех бюджетов. После этого функция вычисляет общие дополнительные расходы и посредством инструкции RETURN возвращает это значение.

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

Python урок 3. функции и процедуры

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

Следующий пример демонстрирует использование именованных параметров в 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 ");
// выполнить логику

Шаг 2: Как работать с хранимыми процедурами

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

Как исправить ошибку error establishing a database connection в wordpress

DELIMITER //
CREATE PROCEDURE `p2` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT «A procedure»
BEGIN
SELECT «Hello World !»;
END//

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

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

4 характеристики хранимой процедуры:

Language: в целях обеспечения переносимости, по умолчанию указан SQL.
Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию — NOT DETERMINISTIC.
SQL Security: во время вызова идет проверка прав пользователя. INVOKER — это пользователь, вызывающий хранимую процедуру. DEFINER — это “создатель” процедуры. Значение по умолчанию — DEFINER.
Comment: в целях документирования, значение по умолчанию — «»

Вызов хранимой процедуры

Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.

CALL stored_procedure_name (param1, param2, ….)
CALL procedure1(10 , «string parameter» , @parameter_var);

Изменение хранимой процедуры

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.

Удаление хранимой процедуры

DROP PROCEDURE IF EXISTS p2;

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.

Функции для работы с текстом

Хранение длинного текста в вашей базе данных влияет и на производительсноть системы и на требования к объему дискового пространства. Таким образом, рекомендуется сжимать длинный текст с помощью функции compress, а для использования — проводить обратное преобразование с помощью функции uncompress.

SELECT LENGTH(COMPRESS(REPEAT('A', 1000000)));
-- OUTPUT: ~1000

Как вы видите, 1 миллион символов A ’сжимается в строку размером примерно в 1000 позиций.

REGEXP()

Да! REGEXP доступен в запросах MySQL, в представлении не нуждается.

id	name
1	Samsung Widescreen 1080p
2	Geforce GT 8800
3	Mini Tv
4	Audio System
5	HTC PRO 2

SELECT id FROM table WHERE name REGEXP '';
-- OUTPUT: 1, 2, 5

С помощью запроса, описанного выше выделяются id элементов, которые имеют числа в имени. Хотя пример является очень простым, но можно представить возможности использования REGEXP в запросах..

SQL Справочник

SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

Выходные параметры хранимых процедур

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

Хранимая процедура может использовать не только входные параметры, через которые передаются значения в процедуру, но и выходные параметры.
Выходные параметры по сути представляют результат работы процедуры, те значения, которые она возвращает пользователю.

Опять же возьмем наш базу данных и определим в ней следующую хранимую процедуру:

CREATE PROCEDURE .
    @name nvarchar(50),
	@minAge int out,
	@maxAge int out
AS
    SELECT @minAge = MIN(Age), @maxAge = MAX(Age) FROM Users WHERE Name LIKE '%' + @name + '%'
GO

В этой процедуре определено три параметра, с помощью которых мы будем получать минимальный и максимальный возраст в базе данных.
Через параметр @name мы будем получать имя пользователя, для которого осуществляется поиск минимального и максимального возраста. Причем
в данном случае параметр по умолчанию имеет значение ‘%’. Данное значение указывает на произвольную строку. То есть если значение для этого параметра передано,
то процедура будет находить возраст только для пользователей с этим именем.
Если же значение для параметра не передано, то процедура будет находить минимальный и максимальный возраст всех пользователей вне зависимости от имени.

Параметры @minAge и @maxAge являются выходными благодаря указанию ключевого слова в их определении. Через них мы
собственно и будем получать минимальный и максимальный возраст.

Теперь перейдем к программе на C# и определим в ней следующий код:

using System;
using System.Data.SqlClient;
using System.Data;

namespace AdoNetConsoleApp
{
    class Program
    {
        static string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
        static void Main(string[] args) 
        {
            Console.Write("Введите имя пользователя:");
            string name = Console.ReadLine();

            GetAgeRange(name);

            Console.Read();
        }

        private static void GetAgeRange(string name)
        {
            string sqlExpression = "sp_GetAgeRange";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sqlExpression, connection);
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter nameParam = new SqlParameter
                {
                    ParameterName = "@name",
                    Value = name
                };
                command.Parameters.Add(nameParam);

                // определяем первый выходной параметр
                SqlParameter minAgeParam = new SqlParameter
                {
                    ParameterName = "@minAge",
                    SqlDbType = SqlDbType.Int // тип параметра
                };
                // указываем, что параметр будет выходным
                minAgeParam.Direction = ParameterDirection.Output;
                command.Parameters.Add(minAgeParam);

                // определяем второй выходной параметр
                SqlParameter maxAgeParam = new SqlParameter
                {
                    ParameterName = "@maxAge",
                    SqlDbType = SqlDbType.Int
                };
                maxAgeParam.Direction = ParameterDirection.Output;
                command.Parameters.Add(maxAgeParam);

                command.ExecuteNonQuery();

                Console.WriteLine("Минимальный возраст: {0}", command.Parameters.Value);
                Console.WriteLine("Максимальный возраст: {0}", command.Parameters.Value);
            }
        }
    }
}

Выходные параметры команды определяются также, как и входные за тем исключением, что для свойства
нам надо установить значение ParameterDirection.Output. По умолчанию все параметры имеют значение
, которое указывает, что они являются входными.

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

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

НазадВперед

Простые и системные переменные

Объявить переменную
в хранимой подпрограмме можно в любом
месте тела подпрограммы (внутри блока
BEGIN..END).
Синтаксис оператора объявления
переменной:

DECLAREимя … тип_данных

Объявить переменную
в хранимой подпрограмме можно в любом
месте тела подпрограммы. Если параметр
DEFAULTотсутствует, то
переменная инициализируется со значениемNULL.

Для присвоения значения
переменной может быть использован
оператор SET. В следующем
примере переменнойSприсваивается текстовое значение,
которое затем выводится на экран:

CREATE
PROCEDURE Hello_World()

BEGIN

DECLARE S
VARCHAR(20);

SET S=‘Hello, world!’;

SELECT(S);

END

Результат вызова
процедуры на выполнение:

Иногда бывает необходимо
присвоить переменной значение,
возвращаемое в результате запроса. Это
можно сделать при помощи оператора
SELECT..INTO. При этом запрос должен возвращать
только одну строку. Если запрос возвращает
пустой результат, это приведет к ошибке
1329 (No data). Если запрос содержит более
одной строки, это приведет к ошибке 1172
(Result consisted of more than one row). Количество строк,
возвращаемых запросом, можно ограничить
опцией LIMITоператораSELECT. Данная опция имеет
два параметра. Первый параметр указывает
смещение возвращаемого набора строк
относительно начала, второй – количество
возвращаемых строк. При использовании
опции только с одним параметром он
интерпретируется как количество
возвращаемых строк от начала результата.
Таким образом, совместно с оператором
SELECT..INTO можно использовать опциюLIMIT1. Следующая процедура выводит наименование
самой тяжелой детали:

CREATEPROCEDUREHeavy()

BEGIN

DECLARE S
VARCHAR(20);

SELECT weight INTO S FROM Parts
ORDER BY Weight DESC LIMIT 1;

SELECT(S);

END

В процессе выполнения
оператора SELECT..INTO выполняется неявное
приведение типа возвращаемого запросом
значения типу переменной:

CREATE
PROCEDURE Parts_count()

BEGIN

DECLARE S
VARCHAR(20);

SELECT count(*) INTO S FROM
Parts;

SELECT(S);

END

Разница между простыми
и системными переменнымив том, что
системные переменные доступны извне
хранимой процедуры. Системную переменную
не нужно инициализировать. Разница в
простой и системной переменной пользовании
префикса @ в имени системной переменной.

SET
@S=‘Hello, world!’;

Значение системной
переменной можно узнать после выполнения
хранимой процедуры:

CREATE
PROCEDURE Parts_count()

BEGIN

SELECT count(*) INTO @S FROM
Parts;

END

Создание и выполнение процедур

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

Нередко операция с данными представляет набор инструкций, которые необходимо выполнить в определенной последовательности. Например, при добавлении покупке товара
необходимо внести данные в таблицу заказов. Однако перед этим надо проверить, а есть ли покупаемый товар в наличии. Возможно, при этом понадобится проверить еще ряд дополнительных условий.
То есть фактически процесс покупки товара охватывает несколько действий, которые должны выполняться в определенной последовательности.
И в этом случае более оптимально будет инкапсулировать все эти действия в один объект — хранимую процедуру (stored procedure).

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

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

И еще один важный аспект — производительность. Хранимые процедуры обычно выполняются быстрее, чем обычные SQL-инструкции. Все потому что код процедур компилируется один раз при первом ее запуске, а затем сохраняется в скомпилированной форме.

Для создания хранимой процедуры применяется команда CREATE PROCEDURE или CREATE PROC.

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

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

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 ProductSummary AS
SELECT ProductName AS Product, Manufacturer, Price
FROM Products

Поскольку команда CREATE PROCEDURE должна вызываться в отдельном пакете, то после команды USE, которая устанавливает текущую базу данных, используется
команда GO для определения нового пакета.

После имени процедуры должно идти ключевое слово AS.

Для отделения тела процедуры от остальной части скрипта код процедуры нередко помещается в блок BEGIN…END:

USE productsdb;
GO
CREATE PROCEDURE ProductSummary AS
BEGIN
	SELECT ProductName AS Product, Manufacturer, Price
	FROM Products
END;

После добавления процедуры мы ее можем увидеть в узле базы данных в SQL Server Management Studio в подузле Programmability -> Stored Procedures:

И мы сможем управлять процедурой также и через визуальный интерфейс.

Для выполнения хранимой процедуры вызывается команда EXEC или EXECUTE:

EXEC ProductSummary

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

Последнее обновление: 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 в процедуру можно не передавать значение.

НазадВперед

Вызов хранимых процедур с несколькими сигнатурами

Сервер баз данных Informix поддерживает перегружаемые хранимые процедуры (overloaded stored procedures). Можно определить хранимые процедуры с одним и тем же именем, но с различными параметрами (или сигнатурами) для выполнения различных операций в зависимости от принимаемых параметров. В листингах и приведены примеры двух таких процедур.

Листинг 21. Определение хранимой процедуры 1

CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT 
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
    DEFINE W_ORDERN INT;
    DEFINE W_ORDERD DATE;
    DEFINE W_SHIP LVARCHAR;  
    FOREACH
        SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT 
        INTO W_ORDERN,W_ORDERD,W_SHIP
FROM ORDERS WHERE ORDERS.CUSTOMER_NUM=CUS_ID
        RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;
    END FOREACH;
END PROCEDURE;

Листинг 22. Определение хранимой процедуры 2

CREATE PROCEDURE GETORDERS(ORD_DATE DATE) RETURNING INT 
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
    DEFINE W_ORDERN INT;
    DEFINE W_ORDERD DATE;
    DEFINE W_SHIP LVARCHAR;  
    FOREACH
        SELECT ORDER_NUM,ORDER_DATE,SHIP_INSTRUCT 
        INTO W_ORDERN,W_ORDERD,W_SHIP
FROM ORDERS WHERE ORDERS.ORDER_DATE=ORD_DATE
        RETURN W_ORDERN,W_ORDERD,W_SHIP WITH RESUME;
    END FOREACH;
END PROCEDURE;

Обе процедуры имеют одинаковые имена (GETORDERS), но первая использует параметр для получения заказов конкретного клиента, а вторая имеет параметр для возврата заказов за определенную дату (см. листинг 23).

Листинг 23. Пример нескольких сигнатур

CREATE PROCEDURE GETORDERS(CUS_ID INT) RETURNING INT 
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;
                
CREATE PROCEDURE GETORDERS(ORD_DATE DATE) RETURNING INT 
AS ORDER_NUM, DATE AS ORDER_DATE, LVARCHAR AS SHINSTRUC;

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

{call getorders(?::INT)}
{call getorders(?::DATE)}

В листинге 25 показано выполнение процедуры .