Встроенный динамический SQL

Сентябрь 7, 2022

Главная
Алгебра
Встроенный динамический SQL

Содержание

2. Динамический SQL PL/SQL использует раннее связывание для выполнения операторов SQL. Следствием этого является то, что только
3. Динамический SQL Существуют два способа выполнения динамического SQL в PL/SQL. Первый применяет модуль DBMS_SQL. Второй способ
4. EXECUTE IMMEDIATE. Базовым оператором, используемым в не содержащих запросов операторах (DML и DDL) и блоках PL/SQL,
5. EXECUTE IMMEDIATE. Пример. BEGIN EXECUTE IMMEDIATE 'CREATE TABLE execute_table (call VARCHAR2(10))'; FOR v_Counter IN 1..10 LOOP
6. EXECUTE IMMEDIATE. EXECUTE IMMEDIATE используется также для выполнения операторов со связанными переменными. В этом случае выполняемая
7. EXECUTE IMMEDIATE. Пример 2.. BEGIN - Вставим ECN 103 в таблицу classes, используя строку символов -
8. OPEN FOR Запросы выполняются с помощью оператора OPEN FOR аналогично курсорным переменным. Различие состоит в том,
9. Массовые соединения Операторы SQL в блоках PL/SQL пересылаются системе поддержки SQL, которая в свою очередь может
10. Массовые соединения. Пример. DECLARE TYPE t_Numbers IS TABLE OF temp_table.num_col%TYPE; TYPE t_Strings IS TABLE OF temp_table.char_col%TYPE;
11. Массовые соединения. Пример (продолжение). -- Внесем в базу данных все 1000 элементов с помощью оператора FORALL.
12. Массовые соединения. Пример (продолжение). -- Аналогично, этот DELETE удалит 300 строк. FORALL V_Count IN 401..600 DELETE
13. Особенности использования транзакций Если в массовой операции DML при обработке одной из строк возникает ошибка, то
14. DBMS_SQL DBMS_SQL используется для выполнения динамического SQL в PL/SQL. Он не встроен непосредственно в язык и
15. DBMS_SQL. Пример. CREATE OR REPLACE PROCEDURE UpdateClasses( /* Использует DBMS_SQL для обновления таблицы учебных групп, задания
16. DBMS_SQL. Пример. (продолжение) -- Выполним синтаксический разбор оператора. DBMS_SQL.PARSE(v_CursorID, v_UpdateStrat, DBMS_SQL.NATIVE); -- Свяжем p_NewCredits с позицией
17. Задания 1. Напишите процедуру, возвращающую список товаров, количество каждого товара и его цену. Процедура должна иметь
19. Скачать презентацию

Слайд 2

Динамический SQL
PL/SQL использует раннее связывание для выполнения операторов SQL. Следствием этого

является то, что только операторы DML могут непосредственно включаться в блоки PL/SQL. Однако можно решить эту проблему с помощью динамического SQL.
Динамический SQL разбирается и исполняется во время выполнения, а не синтаксического разбора блока PL/SQL.

Слайд 3

Динамический SQL
Существуют два способа выполнения динамического SQL в PL/SQL.
Первый применяет модуль

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

Слайд 4

EXECUTE IMMEDIATE.
Базовым оператором, используемым в не содержащих запросов операторах (DML и

DDL) и блоках PL/SQL, является оператор EXECUTE IMMEDIATE.
Выполняемая строка может задаваться как литерал, заключенный в одиночные кавычки или как переменная типа символьной строки PL/SQL.
Завершающая точка с запятой не нужна для операторов DML и DDL, но указывается для анонимных блоков.

Слайд 5

EXECUTE IMMEDIATE. Пример.
BEGIN
EXECUTE IMMEDIATE
'CREATE TABLE execute_table (call VARCHAR2(10))';
FOR v_Counter IN

1..10 LOOP
v_SQLString :=
'INSERT INTO execute_table
VALUES ("Row' || v_Counter || '")';
EXECUTE IMMEDIATE v_SQLString;
END LOOP;
v_PLSQLBlock :=
'BEGIN
FOR v_Rec IN (SELECT * FROM execute_table) LOOP
DBMS_OUTPUT.PUT_LINE(v_Rec.call);
END LOOP;
END;';
EXECUTE IMMEDIATE v_PLSQLBlock;
EXECUTE IMMEDIATE 'DROP TABLE execute_table ';
END;

В этом примере показаны различные способы использования EXECUTE IMMEDIATE: для выполнения DDL, DML и анонимных блоков PL/SQL.

Слайд 6

EXECUTE IMMEDIATE.
EXECUTE IMMEDIATE используется также для выполнения операторов со связанными переменными.

В этом случае выполняемая строка содержит специальные позиции, помеченные двоеточием.
Позиции предназначены для размещения переменных PL/SQL, которые указываются в предложении USING оператора EXECUTE IMMEDIATE,

Слайд 7

EXECUTE IMMEDIATE. Пример 2..
BEGIN
- Вставим ECN 103 в таблицу classes, используя

строку символов
- для оператора SQL.
v_SQLString :=
'INSERT INTO CLASSES (department, course, description,
max_students, current_students,
num_credits)
VALU€S(:dep, :course, :descr, :max_s, :cur_s, :num_c)';
EXECUTE IMMEDIATE v_SQLString USING
'ECN', 103, 'Economics 103', 10, 0, 3;
- Зарегистрируем всех выбравших Economics в новой группе.
FOR v_StudentRec IN c_EconMajor LOOP
- Здесь мы имеем литеральный оператор SQL, а переменные PL/SOL
- находятся в предложении USING.
ECECUTE IMMEDIATE
'INSERT INTO registered_students
(student_ID, department, course, grade)
VALUES (:id, :dep, :course, NULL)'
USING v_Studentflec.ID, 'ENC', 103;
END;

Слайд 8

OPEN FOR
Запросы выполняются с помощью оператора OPEN FOR аналогично курсорным переменным.

Различие состоит в том, что строка, содержащая запрос, может быть переменной PL/SQL, а не литералом.
К получаемой курсорной переменной можно обращаться так же, как и к любой другой переменной.
Для связывания используется предложение USING, так же как в операторе EXECUTE IMMEDIATE.

BEGIN
v_SQLStatement := 'SELECT * FROM students ' || p_WhereClause;
OPEN v_ReturnCursor FOR v_SQLStatement;
v_SQLStatement := 'SELECT * FROM students WHERE major = :m';
OPEN v_ReturnCursor FOR v_SQLStatement USING p_Major;
END;

Слайд 9

Массовые соединения
Операторы SQL в блоках PL/SQL пересылаются системе поддержки SQL, которая

в свою очередь может передавать данные назад системе поддержки PL/SQL (как результат запроса). Во многих случаях данные, которые вносятся или обновляются в базе данных, помещаются сначала в сборную конструкцию PL/SQL, и затем эта сборная конструкция просматривается с помощью цикла FOR для отправки информации системе поддержки SQL. Это приводит к переключению контекста между PL/SQL и SQL для каждой строки в сборной конструкции.
Oracle8i и выше позволяет передавать все строки сборной конструкции системе поддержки SQL с помощью одной операции, оставляя только одно переключение контекста. Это называется массовым соединением, оно выполняется с помощью оператора FORALL.

Слайд 10

Массовые соединения. Пример.
DECLARE
TYPE t_Numbers IS TABLE OF temp_table.num_col%TYPE;
TYPE t_Strings IS TABLE

OF temp_table.char_col%TYPE;
v_Numbers t_Numbers := t_Numbers(1);
v_Strings t_Strings := t_Strings(1);
- Печатаем общее число строк таблицы temp_table.
PROCEDURE PrintTotalRows (p_Message IN VARCHAR2) IS
v_Count NUMBER:
BEGIN
SELECT COUNT(*)
INTO v_Count
FROM temp_table;
DBMS_OUTPUT.PUT_LINE(p_Message || ': Count is ' || v_Count);
END PrintTotalRows;
BEGIN
DELETE FROM temp_table;
-- Заполняем вложенные таблицы PL/SQL, используя 1000 значений.
v_Numbers.EXTEND(1000);
v_Strings.EXTEND(1000);
FOR v_Count IN 1..1000 LOOP
v_Numbers(v_Count) := v_Count;
v_Strings(v_count) := 'Element #' || v_Count;
END LOOP;

Слайд 11

Массовые соединения. Пример (продолжение).
-- Внесем в базу данных все 1000

элементов с помощью оператора FORALL.
FORALL v_Count IN 1..1000
INSERT INTO temp_table VALUES
(v_Numbers(v_Count), v_Strings(v_Count));
- Теперь должно быть 1000 строк.
PrintTotalRows('After first insert');
-- Снова внесем в базу данных элементы с 501 по 1000.
FORALL v_Count IN 501..1000
INSERT INTO temp_table VALUES
(v_Numbers(v_count), v_Strings(v_Count));
-- Теперь у нас должно быть 1500 строк.
PrintTotalRows('After second insert');
-- Обновим все строки.
FORALL v_Count IN 1..1000
UPDATE temp_table
SET char_col = 'Changed!'
WHERE num_col = v_Numbers(v_Count);
- Несмотря на то, что имеется только 1000 элементов, этот оператор
- обновляет 1500 строк, так как предложение WHERE соответствует
- 2 строкам для каждой из последних 500 строк.
DBMS_OUTPUT.PUT_LINE(
'Update processed ' || SQL%ROWCOUNT || 'rows.');

Слайд 12

Массовые соединения. Пример (продолжение).
-- Аналогично, этот DELETE удалит 300 строк.
FORALL

V_Count IN 401..600
DELETE FROM tempjtable
WHERE nun_col = v_Numbers(v_Count);
-- Поэтому должно остаться 1200 строк.
PrintTotalRows('After delete');
END;

After first insert: Count is 1000
After second insert: Count is 1500
Update processed 1500 rows.
After delete: Count is 1200

Результатом выполнения примера будет следующее:

FORALL синтаксически аналогичен циклу FOR. Он может использоваться для сборных конструкций любого типа и для операторов INSERT, DELETE и UPDATE. Определяемый в FORALL диапазон должен быть непрерывным, и все элементы в этом диапазоне должны существовать.

Слайд 13

Особенности использования транзакций
Если в массовой операции DML при обработке одной из

строк возникает ошибка, то откатывается только эта строка. Предыдущие строки будут обработаны.
В Oracle9i можно указать в операторе FORALL новую конструкцию SAVE EXCEPTIONS. При этом любая ошибка, возникшая во время пакетной обработки, будет сохранена, а обработка будет продолжена.
Для просмотра исключений можно использовать новый атрибут SQL%BULK_EXCEPTIONS, который действует как таблица PL/SQL.

Слайд 14

DBMS_SQL
DBMS_SQL используется для выполнения динамического SQL в PL/SQL. Он не

встроен непосредственно в язык и поэтому менее эффективен, чем встроенный динамический SQL (который доступен в Огас1е8г и выше).
Модуль DBMS_SQL позволяет непосредственно управлять обработкой операторов в курсоре, выполнять синтаксический разбор оператора, связывать входные переменные и определять выходные переменные.

Слайд 15

DBMS_SQL. Пример.
CREATE OR REPLACE PROCEDURE UpdateClasses(
/* Использует DBMS_SQL для обновления таблицы

учебных групп, задания
числа зачетов для всех групп на указанном факультете.
*/
p_Department IN classes.department%TYPE,
p_NewCredits IN classes. num_credits%TYPE,
p_RowsUpdated OUT INTEGER) AS
v_CursorID INTEGER;
v_UpdateStmt VARCHAR2(100);
BEGIN
- Откроем курсор для обработки.
v_CursorID := DBMS_SQL.OPEN_CURSOR;
- Определим строку SQL.
v_UpdateStmt :=
'UPDATE classes
SET num_credits = :nc
WHERE department = :dept';

Слайд 16

DBMS_SQL. Пример. (продолжение)
-- Выполним синтаксический разбор оператора.
DBMS_SQL.PARSE(v_CursorID, v_UpdateStrat, DBMS_SQL.NATIVE);

-- Свяжем p_NewCredits с позицией :nс. Эта перегруженная версия
-- BIND_VARIABLE привяжет p_NewCredits как NUMBER,
-- поскольку он так объявлен.
DBMS_SQL.BIND_VARIABLE(v_CursorID, ':nc', p_NewCredits);
-- Свяжем p_Department с позицией :dept. Эта перегруженная версия
-- BIND_VARIABLE привяжет p_Department как CHAR, поскольку он
-- так объявлен.
DBMS_SQL.BIND_VARIABLE_CHAR(v_CursorID, ':dept ', p_Department);
-- Выполним оператор
p_RowsUpdated := DBMS_SQL.EXECUTE(v_CursorID);
-- Закроем курсор
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
EXCEPTION
WHEN OTHERS THEN
-- Закроем курсор и снова инициируем ошибку.
DBMS_SQL.CLOSE_CURSOR(v_CursorID);
RAISE;
END UpdateClasses;

Слайд 17

Задания
1. Напишите процедуру, возвращающую список товаров, количество каждого товара и его

цену. Процедура должна иметь два входных параметра - название фирмы и название товара. Если указано название фирмы, выдаётся список всех купленных ею товаров. Если указано название фирмы и дополнительно указано наименование товара, то выдаётся товар, купленный фирмой, наименование которого совпадает с заданным наименованием. Если указано только наименование товара, то выдаётся указанный товар.
Процедуру написать с использованием позиций предназначенных для размещения переменных PL/SQL, указываемых в предложении USING оператора EXECUTE IMMEDIATE.
2. Выполните первое задание используя DBMS_SQL.

Встроенный динамический SQL

Содержание

Динамический SQLPL/SQL использует раннее связывание для выполнения операторов SQL. Следствием этого

Динамический SQLСуществуют два способа выполнения динамического SQL в PL/SQL.Первый применяет модуль

EXECUTE IMMEDIATE.Базовым оператором, используемым в не содержащих запросов операторах (DML и

EXECUTE IMMEDIATE. Пример.BEGINEXECUTE IMMEDIATE 'CREATE TABLE execute_table (call VARCHAR2(10))';FOR v_Counter IN

EXECUTE IMMEDIATE.EXECUTE IMMEDIATE используется также для выполнения операторов со связанными переменными.

EXECUTE IMMEDIATE. Пример 2..BEGIN- Вставим ECN 103 в таблицу classes, используя

OPEN FORЗапросы выполняются с помощью оператора OPEN FOR аналогично курсорным переменным.

Массовые соединенияОператоры SQL в блоках PL/SQL пересылаются системе поддержки SQL, которая

Массовые соединения. Пример.DECLARE TYPE t_Numbers IS TABLE OF temp_table.num_col%TYPE; TYPE t_Strings IS TABLE

Массовые соединения. Пример (продолжение). -- Внесем в базу данных все 1000

Массовые соединения. Пример (продолжение). -- Аналогично, этот DELETE удалит 300 строк.FORALL

Особенности использования транзакцийЕсли в массовой операции DML при обработке одной из

DBMS_SQL DBMS_SQL используется для выполнения динамического SQL в PL/SQL. Он не

DBMS_SQL. Пример.CREATE OR REPLACE PROCEDURE UpdateClasses(/* Использует DBMS_SQL для обновления таблицы

DBMS_SQL. Пример. (продолжение) -- Выполним синтаксический разбор оператора. DBMS_SQL.PARSE(v_CursorID, v_UpdateStrat, DBMS_SQL.NATIVE);

Задания1. Напишите процедуру, возвращающую список товаров, количество каждого товара и его

Похожие презентации