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

Опция OR REPLACE позволяет, при изменении объекта сохранить все имеющиеся на объект привилегии.

Пример создания процедуры

Таблицы схемы OE

CREATE OR REPLACE PROCEDURE get_avg_order (p_cust_id NUMBER, p_cast_last_name VARCHAR2, P_order_tot NUMBER) IS v_cust_id customers.customer_id%type; v_cust_name customers.cust_last_name%type; BEGIN SELECT customers.customers_id, customers.cust_last_name INTO v_cust_id, v_cust_name, v_avg_order FROM CUSTOMERS, ORDERS WHERE customers.customer_id=orders.customer_id AND customers.customer_id=101; END;

Функции

• Функция – это блок который возвращает значение
• Функции хранятся в базе данных как объекты схемы
• Функции могут использоваться для вычисления значений
• Функция может использоваться в SQL запросе и в PL/SQL коде (в SQL запросе не могут использоваться функции производящие операции над объектами)

Объявление функции

CREATE [OR REPLACE] FUNCTION function_name [(parameter1 [mode] datetype1, parameter2 [mode] datetype2, …)] RETURN datatype IS|AS [объявление локальных переменных; …] BEGIN ---действия; RETURN возвращаемое значение; End[function_name];

Пример функции

CREATE OR REPLACE FUNCTION get_credit (v_id customers.customer_id%TYPE) RETURN NUMBER IS v_credit customer.credit_limit%TYPE :=0; BEGIN SELECT credit_limit INTO v_credit FROM customers WHERE customer_id=v_id; RETURN v_credit; END;

Пример вызова функции:

EXECUTE dbms_output.put_line(get_credit(101));

Способы вызова функции

• Можно использовать хост переменную для хранения результата:
• VARIABLE v_credit NUMBER
• EXECUTE :v_credit := get_credit(101);
• Можно использовать как локальную переменную для хранения результата:
• v_credit:=get_credit(101);
• Можно использовать в SQL выражении:
• SELECT get_credit(customer_id) FROM customers;

Вложенные блоки

Вложенный блок – блок, который расположен в другом блоке

DECLARE CURSOR cur_emp IS …; BEGIN

DECLARE v_total_sales number; BEGIN

DECLARE v_hiredate date; BEGIN … END;

END;

END;

Условные операторы Оператор IF

Оператор IF позволяет реализовать в программе условную логику.

Примеры использования оператора IF

IF salary>40000 THEN give_bonus(employee_id,500); END IF;

IF salary <= 40000 THEN give_bonus(employee_id, 0) ELSE give_bonus(employee_id, 500); END IF;

IF salary BETWEEN 10000 AND 20000 THEN give_bonus(employee_id,1000); ELSIF salary > 40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END IF;

Оператор CASE

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

• Простой оператор CASE – связывает одну или несколько последовательностей операторов PL/SQL с соответствующим значением (последовательность для выполнения выбирается с учётом результатов вычисления выражения, возвращающего указанное значение).
• Поисковый оператор CASE - выбирает для выполнения одну из последовательностей операторов в зависимости от результатов вычислений списка логических условий (выполняется последовательность операторов, связанная с первым условием, результат проверки которого оказался равным TRUE).
• CASE выражение – позволяет выбрать для вычислений одно или несколько выражений.

Простой оператор CASE

CASE выражение WHEN результат_1 THEN Операторы_1 WHEN результат_2 THEN Операторы_2 … ELSE Операторы_else END CASE;

--Конструкция ELSE не обязательна

CASE employee_type WHEN ‘S’ THEN Award_salary_bonus(employee_id); WHEN ‘H’ THEN Award_hourly_bonus(employee_id); WHEN ‘C’ THEN Award_commissioned_bonus(employee_id); END CASE;

Поисковый оператор CASE

CASE WHEN выражение_1 THEN Операторы_1 WHEN выражение_2 THEN Операторы_2 … ELSE Операторы_else END CASE;

--Конструкция ELSE не обязательна

CASE WHEN salary>=10000 AND salary<=20000 THEN give_bonus(employee_id, 1500); WHEN salary>20000 AND salary<=40000 THEN give_bonus(employee_id, 1000); WHEN salary>40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id,0); END CASE;

Циклы

• Простой цикл LOOP:
• PROCEDURE set_all_ranks(p_max_rank_in INTEGER)
• IS
• v_ranking_level NUMBER(3):=1;
• BEGIN
• LOOP
• EXIT WHEN v_ranking_level>p_max_rank_in;
• set_rank(v_ranking_level);
• v_ranking_level:=v_ranking_level+1;
• END LOOP;
• END;

• Цикл FOR
• PROCEDURE set_all_ranks(p_max_rank_in IN INTEGER)
• IS
• v_ranking_level NUMBER(3);
• BEGIN
• FOR v_ranking_level IN 1…p_max_rank_in
• LOOP
• set_rank(v_ranking_level);
• END LOOP;
• END;

• Цикл WHILE
• PROCEDURE set_all_ranks(p_max_rank_in IN INTEGER)
• IS
• v_ranking_level NUMBER(3):=1;
• BEGIN
• WHILE v_ranking_level<p_max_rank_in
• LOOP
• set_rank(v_ranking_level);
• v_ranking_level:=p_max_rank_in+1;
• END LOOP;
• END;