Просмотр определения хранимой процедурыview the definition of a stored procedure

Содержание:

Для чего используются хранимые процедуры
РекомендацииBest Practices
Шаг 5: Структуры управления потоками
SQL Справочник
Привязанные к схеме функцииSchema-bound functions
Область действия локальных модулей
Настройки отладчика
Именованные параметры в CallableStatement
Точки останова
Написание универсальной процедуры на Transact-SQL
SQL Учебник
Шаг 6: Курсоры
Вызов определяемой пользователем функции
Отображение контекста доступа6. Display the Access Context
Резюме
- - Похожие темы

Для чего используются хранимые процедуры

Предположим, у нас есть JDBC-приложение, которое должно эффективно выполнять последовательность задач на регулярной основе. Мы можем использовать Java-метод, но сколько раз понадобится организовать клиент-серверное взаимодействие для отправки и получения данных? Сервер базы данных будет готовить и генерировать план запроса для каждого SQL-оператора, отправляемого приложением, что займет определенное процессорное время. С точки зрения производительности использование простых Java-методов с одиночными SQL-операторами может оказаться не лучшей идеей.

А что если использовать хранимую процедуру, создание которой является одноразовой задачей? Создайте одиночный SQL-вызов, и к нему можно будет обращаться из JDBC-приложения при помощи объекта , выступающего в роли вызывающей стороны для хранимой процедуры на сервере. В хранимой процедуре будет размещена основная часть бизнес-логики. Это поможет упростить код клиентского приложения и ускорит выполнение, поскольку включаемые в хранимую процедуру SQL-операторы готовятся и оптимизируются при ее создании.

Шаг 5: Структуры управления потоками

Просмотр dwg файлов онлайн

MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.

Конструкция IF

С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:

DELIMITER //  
  
CREATE PROCEDURE `proc_IF` (IN param1 INT)  
BEGIN  
    DECLARE variable1 INT;  
    SET variable1 = param1 + 1;  
  
    IF variable1 = 0 THEN  
        SELECT variable1;  
    END IF;  
  
    IF param1 = 0 THEN  
        SELECT 'Parameter value = 0';  
    ELSE  
        SELECT 'Parameter value <> 0';  
    END IF;  
END //

Конструкция CASE

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

DELIMITER //  
  
CREATE PROCEDURE `proc_CASE` (IN param1 INT)  
BEGIN  
    DECLARE variable1 INT;  
    SET variable1 = param1 + 1;  
  
    CASE variable1  
        WHEN 0 THEN  
            INSERT INTO table1 VALUES (param1);  
        WHEN 1 THEN  
            INSERT INTO table1 VALUES (variable1);  
        ELSE  
            INSERT INTO table1 VALUES (99);  
    END CASE;  
  
END //

или:

DELIMITER //  
  
CREATE PROCEDURE `proc_CASE` (IN param1 INT)  
BEGIN  
    DECLARE variable1 INT;  
    SET variable1 = param1 + 1;  
  
    CASE  
        WHEN variable1 = 0 THEN  
            INSERT INTO table1 VALUES (param1);  
        WHEN variable1 = 1 THEN  
            INSERT INTO table1 VALUES (variable1);  
        ELSE  
            INSERT INTO table1 VALUES (99);  
    END CASE;  
  
END //

Конструкция WHILE

Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:

DELIMITER //  
  
CREATE PROCEDURE `proc_WHILE` (IN param1 INT)  
BEGIN  
    DECLARE variable1, variable2 INT;  
    SET variable1 = 0;  
  
    WHILE variable1 < param1 DO  
        INSERT INTO table1 VALUES (param1);  
        SELECT COUNT(*) INTO variable2 FROM table1;  
        SET variable1 = variable1 + 1;  
    END WHILE;  
END //

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 Краткий справочник

Привязанные к схеме функцииSchema-bound functions

Инструкция поддерживает предложение , позволяющее привязать функцию к схеме каких-либо объектов, на которые она ссылается, например таблиц, представлений и других пользовательских функций. supports a clause that binds the function to the schema of any objects it references, such as tables, views, and other user-defined functions. Попытка изменения или удаления любого объекта, к которому обращается привязанная к схеме функция, приводит к ошибке.An attempt to alter or drop any object referenced by a schema-bound function fails.

Перед указанием предложения в инструкции CREATE FUNCTION нужно соблюсти перечисленные ниже условия.These conditions must be met before you can specify in CREATE FUNCTION:

Все представления и пользовательские функции, к которым обращается функция, должны быть привязаны к схеме.All views and user-defined functions referenced by the function must be schema-bound.
Все объекты, к которым обращается функция, должны находиться в той же базе данных, что и функция.All objects referenced by the function must be in the same database as the function. Обращение к объектам должно производиться по однокомпонентным либо двухкомпонентным именам.The objects must be referenced using either one-part or two-part names.
Для всех объектов (таблиц, представлений и пользовательских функций), к которым обращается функция, должно быть получено разрешение .You must have permission on all objects (tables, views, and user-defined functions) referenced in the function.

Для удаления привязки к схеме можно использовать инструкцию .You can use to remove the schema binding. В инструкции следует переопределить функцию без указания предложения .The statement should redefine the function without specifying .

Область действия локальных модулей

Модульный раздел объявлений похож на тело пакета. Тело пакета тоже содержит определения модулей. Главное различие между локальными и пакетными модулями связано с их областью действия. Локальные модули могут вызываться только из блока, в котором они определяются; пакетные модули могут (как минимум!) вызываться из любой точки пакета. Если пакетные модули также включены в спецификацию пакета, они могут вызываться другими программными единицами из схем, имеющих привилегии для этого пакета.

Следовательно, локальные модули следует применять только для инкапсуляции кода, который не нужно вызывать за пределами текущей программы. А если нужно — создайте пакет!

Настройки отладчика

Ниже рассмотрены некоторые настройки в окне Options, которые влияют на отладчик SQL-процедур.

Debug Session Timeout (таймаут сеанса отладки): при остановке сеанса отладки и не выполнении в течение указанного числа секунд каких-либо операций, сеанс отладки завершается и процедура выполняется до завершения своей работы. Применимо ко всем платформам z/OS Version 8 и Version 9. Maximum Variable Display Length (максимальная длина отображения переменной): значения переменной, которые длиннее данной настройки, усекаются. Применимо ко всем платформам version 8 и version 9. Debug Daemon Host (хост демона-отладчика): это имя хоста или IP-адрес локальной машины, на которой будет работать демон debug. Обычно эту настройку можно оставить в значении по умолчанию — localhost. Если на вашей машине имеется несколько IP-адресов, необходимо установить это значение в тот IP-адрес, доступный серверу базы данных, на котором выполняется хранимая процедура. Используется только для z/OS Version 8. Debug Daemon Port (порт демона-отладчика): номер порта TCP/IP, который будет использоваться демоном debug. Обычно может использоваться значение по умолчанию. При наличии другой службы, использующей этот же номер порта, измените его на свободное значение. Используется только для z/OS Version 8. Diagnostic Trace Level (уровень диагностической трассировки): объем диагностической трассировки, выполняемой на сервере. Обычно вы должны устанавливать это значение в , пока нет указаний службы поддержки DB2. Session Manager Connection (соединение менеджера сеансов): соединение, которое будет использоваться для работы менеджера сеансов. В большинстве случаев это поле нужно оставлять не заполненным, и менеджер сеансов будет работать с тем же самым соединением, с которым работает отлаживаемая хранимая процедура. Если вы хотите использовать для менеджера сеансов другое соединение, создайте сначала соединение в Server Explorer, а затем установите значение здесь. Применим ко всем платформам, но только version 9. Session Manager Port (порт менеджера сеансов): номер порта, используемого для менеджера сеансов. Если значение по умолчанию конфликтует с другой службой на сервере, на котором работает менеджер сеансов, измените значение на любое неиспользуемое.

Именованные параметры в 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 ");
// выполнить логику

Точки останова

Существует два типа точек останова, поддерживаемых отладчиком SQL-процедур: line breakpoints (точки останова для строки) и variable breakpoints (точки останова для переменной).

Точки останова для строки

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

Рисунок 14. Маркер точки останова указывает месторасположение точки останова для строки

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

Точки останова для переменных

Точки останова для переменных активизируются, когда значение переменной изменяется. Для установки такой точки нажмите левую кнопку мыши на имени переменной в исходном коде хранимой процедуры и правую кнопку для активации контекстного меню. Выберите каскадное меню Breakpoints и пункт Insert variable breakpoint.

Рисунок 15. Создание точки останова для переменной

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

Окно Breakpoints

Для активизации инструментального окна Breakpoints используйте меню верхнего уровня Debug, активизируйте каскадное меню Windows и выберите Breakpoints. Это инструментальное окно может также использоваться для удаления точек останова, включая точки останова для переменных. Для удаления точки останова в окне Breakpoints щелкните правой кнопкой мыши на ней и выберите Delete. Альтернативным способом удаления точки останова является щелчок на ней левой кнопкой мыши в левом поле редактора исходного кода хранимой процедуры.

Рисунок 16. Просмотр и изменение точек останова

Инструментальное окно Breakpoints может использоваться для установки числа проходов (hit count) для точки останова. Для этого щелкните правой кнопкой мыши на точке останова в окне Breakpoints и выберите Hit Count…. Эта настройка в диалоговом окне Breakpoint Hit Count позволяет активизировать точку останова: всегда, только при равенстве числа проходов точки указанному значению, только при значении числа проходов, большему указанного значения, или только тогда, когда число проходов кратно указанному значению.

Отладка SQL-процедур в базах данных DB2 for z/OS Version 8

Имеется несколько важных отличий при отладке баз данных DB2 for z/OS V8.

В DB2 for z/OS V8 процесс отладки требует использования программы-демона debugger, работающего на клиентской рабочей станции, где выполняется Visual Studio 2005. Информация по настройке демона debugger приведена в разделе «».

Кроме того, в DB2 for z/OS V8 отличается режим пошагового выполнения. Невозможно выполнить вход (step into) во вложенную хранимую процедуру. Можно отлаживать только одну хранимую процедуру. Поскольку вложенные хранимые процедуры не поддерживаются, операции step into, step over и step out работают по-разному. Step into и step out работают с блоками кода, а не с хранимыми процедурами. То есть step into будет выполнять вход в блок кода, например, тело цикла WHILE. Step over будет пропускать блок кода. Step out выполняет выход из текущего блока кода.

Написание универсальной процедуры на Transact-SQL

Допустим, у нас есть таблица test_table

   
   CREATE TABLE .(
          IDENTITY(1,1) NOT NULL,
          NULL,
          NULL
   ) ON 
   GO

Со следующими тестовыми данными

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

   
   CREATE PROCEDURE test_proc
        (@id INT)
   AS
   BEGIN
        UPDATE test_table SET column1 = 1, column2 = 1
        WHERE id = COALESCE(@id, id)
   END
   GO

И что здесь происходит, как видите, мы принимаем один параметр, пускаем запрос update, и ставим условие, в котором указываем что если наш входящий параметр пустой, то обновляем все, т.е. функция coalesce проверяет первый параметр @id, и если он пуст, то подставляет просто id, для примера id=id или 1=1, соответственно, под это условие попадают все записи в нашей таблице, а если нет, то только ту запись, которая указанна во входящем параметре.

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

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

  
  EXEC dbo.test_proc 2
  SELECT * FROM test_table

Как видите, все отработало нормально, обновилась только одна запись, которую мы и указывали, теперь запустим со значением NULL

  
  EXEC dbo.test_proc null
  SELECT * FROM test_table

А в этом случае обновились все записи, так как мы передали параметр со значением null.

Вот такой простой совет, который, надеюсь, Вам в чем-нибудь да поможет. Удачи!

НравитсяНе нравится

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 Комментарии

Шаг 6: Курсоры

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

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

DECLARE cursor-name CURSOR FOR SELECT ...;       /*Объявление курсора и его заполнение */  
DECLARE  CONTINUE HANDLER FOR NOT FOUND          /*Что делать, когда больше нет записей*/  
OPEN cursor-name;                                /*Открыть курсор*/  
FETCH cursor-name INTO variable ;    /*Назначить значение переменной, равной текущему значению столбца*/  
CLOSE cursor-name;                               /*Закрыть курсор*/

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

DELIMITER //  
  
CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)  
BEGIN  
    DECLARE a, b, c INT;  
    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;  
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;  
    OPEN cur1;  
  
    SET b = 0;  
    SET c = 0;  
  
    WHILE b = 0 DO  
        FETCH cur1 INTO a;  
        IF b = 0 THEN  
            SET c = c + a;  
    END IF;  
    END WHILE;  
  
    CLOSE cur1;  
    SET param1 = c;  
  
END //

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

Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
Доступен только для чтения: курсоры нельзя изменять.
Без перемотки: курсор способен проходить только в одном направлении — вперед, вы не сможете пропускать строки, не выбирая их.

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

Определенную пользователем функцию можно вызывать с помощью инструкций Transact-SQL, таких как SELECT, INSERT, UPDATE или DELETE. Вызов функции осуществляется, указывая ее имя с парой круглых скобок в конце, в которых можно задать один или несколько аргументов. Аргументы — это значения или выражения, которые передаются входным параметрам, определяемым сразу же после имени функции. При вызове функции, когда для ее параметров не определены значения по умолчанию, для всех этих параметров необходимо предоставить аргументы в том же самом порядке, в каком эти параметры определены в инструкции CREATE FUNCTION.

В примере ниже показан вызов функции ComputeCosts в инструкции SELECT:

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

В инструкциях Transact-SQL имена функций необходимо задавать, используя имена, состоящие из двух частей: schema name и function name, поэтому в примере мы использовали префикс схемы dbo.

Отображение контекста доступа6. Display the Access Context

Для отображения прав, связанных с доступом хранимой процедуры, запустите следующий код, чтобы предоставить права на запуск хранимой процедуры пользователю .To display the rights associated with the stored procedure access, run the following code to grant the rights to run the stored procedure to the user.

После этого с помощью приведенного ниже кода запустите хранимую процедуру от имени входа dbo, которое было использовано на сервере.Next, run the following code to run the stored procedure as the dbo login you used on the server. Просмотрите вывод сведений о контексте пользователя.Observe the output of the user context information. Учетная запись dbo будет показана как контекст со своими собственными правами, а не через членство в группе.It will show the dbo account as the context with its own rights and not through a group membership.

Запустите следующий код, чтобы с помощью инструкции от имени учетной записи выполнить хранимую процедуру.Run the following code to use the statement to become the account and run the stored procedure. На этот раз будет показано, что задан контекст пользователя USER MAPPED TO CERTIFICATE.This time you will see the user context is set to the USER MAPPED TO CERTIFICATE context

Обратите внимание, что этот параметр не поддерживается в автономной базе данных или базе данных SQL Azure или Azure Synapse Analytics.Note that this option is not supported in a contained database or Azure SQL Database or Azure Synapse Analytics

Это означает, что подписывание хранимой процедуры сделало доступным аудит.This shows you the auditing available because you signed the stored procedure.

Примечание

Использование EXECUTE AS для переключения контекстов в базе данных.Use EXECUTE AS to switch contexts within a database.

Резюме

IBM Database Add-Ins for Visual Studio 2005 предоставляет возможности отладки хранимых SQL-процедур, аналогичные функциональным возможностям отладки приложений, написанным на языках Visual Basic, Visual C# и Visual C++. В данной статье показано, как эти возможности отладки SQL-процедур облегчают поиск и исправление ошибок в хранимых SQL-процедурах.