МОДУЛЬНОЕ ПРОГРАММИРОВАНИЕ. Программная единица PL/SQL Именованные блоки Три основных категории Процедура Функция Пакет Хранятся в базе данных или обрабатываются. - презентация

1 МОДУЛЬНОЕ ПРОГРАММИРОВАНИЕ

2 Программная единица PL/SQL Именованные блоки Три основных категории Процедура Функция Пакет Хранятся в базе данных или обрабатываются в прикладных программах

3 Создание подпрограммы Выбор среды Компиляция Отладка Создание Вызов Вызов

4 Создание процедуры PROCEDURE name [(parameter,...)] IS pl/sql_block; PROCEDURE name [(parameter,...)] IS pl/sql_block; parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expr] parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT} expr] CREATE OR REPLACE При создании из SQL*PLUS необходимо пользоваться командой CREATE OR REPLACE

5 Параметры процедуры Процедура (DECLARE)BEGINEXCEPTIONEND; IN OUT IN OUT Вызывающа я среда

6 Пример создания процедуры PROCEDURE change_salary (v_emp_id IN NUMBER, v_new_salary IN NUMBER) v_new_salary IN NUMBER)ISBEGIN UPDATE s_emp SET salary = v_new_salary SET salary = v_new_salary WHERE id = v_emp_id; WHERE id = v_emp_id;COMMIT; END change_salary; PROCEDURE change_salary (v_emp_id IN NUMBER, v_new_salary IN NUMBER) v_new_salary IN NUMBER)ISBEGIN UPDATE s_emp SET salary = v_new_salary SET salary = v_new_salary WHERE id = v_emp_id; WHERE id = v_emp_id;COMMIT; END change_salary;

7 Процедура или функция Процедура (DECLARE)BEGINEXCEPTIONEND; IN OUT Функция (DECLARE)BEGINEXCEPTIONEND; IN Вызывающая среда

8 Создание функции FUNCTION name [(parameter,...)] RETURN datatype IS pl/sql_block; FUNCTION name [(parameter,...)] RETURN datatype IS pl/sql_block; FUNCTION tax (v_value IN NUMBER) RETURN NUMBER IS BEGIN RETURN (v_value *.07); END tax; FUNCTION tax (v_value IN NUMBER) RETURN NUMBER IS BEGIN RETURN (v_value *.07); END tax;

9 Вызов блока PL/SQL> change_salary (17, 1000); PROCEDURE process_sal (v_emp_id IN NUMBER, (v_emp_id IN NUMBER, v_new_salary IN NUMBER) v_new_salary IN NUMBER)ISBEGIN change_salary (v_emp_id, v_new_salary); change_salary (v_emp_id, v_new_salary); --invoking procedure change_salary --invoking procedure change_salary......END; PROCEDURE process_sal (v_emp_id IN NUMBER, (v_emp_id IN NUMBER, v_new_salary IN NUMBER) v_new_salary IN NUMBER)ISBEGIN change_salary (v_emp_id, v_new_salary); change_salary (v_emp_id, v_new_salary); --invoking procedure change_salary --invoking procedure change_salary......END; PL/SQL> CREATE NUMBER x PRECISION 4 PL/SQL> :x := tax(100); PL/SQL> TEXT_IO.PUT_LINE (TO_CHAR(:x)); PL/SQL> CREATE NUMBER x PRECISION 4 PL/SQL> :x := tax(100); PL/SQL> TEXT_IO.PUT_LINE (TO_CHAR(:x));

10 Практическое занятие 1.Процедура может содержать параметры IN, OUT и IN OUT. (Да/Нет) 2.Процедуру можно использовать в команде SQL. (Да/Нет) 3.По умолчанию используются параметры типа IN OUT. (Да/Нет) 4.Функции выполняются как часть выражения. (Да/Нет) 5.Назовите четыре части синтаксиса подпрограммы. 6.Создайте процедуру MY_PROCEDURE для вывода на экран фразы "My procedure works" ("Моя процедура работает"). 7.Скомпилируйте код. Для успешной компиляции внесите в код 8.Выполните процедуру из командной строки Интерпретатора

11 Пакеты PL/SQL

12 ПАКЕТ - это объект базы данных, который группирует логически связанные типы, программные объекты и подпрограммы PL/SQL. Пакеты обычно состоят из двух частей, спецификации и тела, хотя иногда в теле нет необходимости. 1.СПЕЦИФИКАЦИЯ пакета – это интерфейс с вашими приложениями; она объявляет типы, переменные, константы, исключения, курсоры и подпрограммы, доступные для использования в пакете. 2.ТЕЛО пакета полностью определяет курсоры и подпрограммы, тем самым реализуя спецификацию пакета. Понятие пакета PL/SQL

13 Формат пакета PACKAGE имя IS -- спецификация (видимая часть) -- объявления общих типов и объектов -- спецификации подпрограмм END [имя]; PACKAGE имя IS -- спецификация (видимая часть) -- объявления общих типов и объектов -- спецификации подпрограмм END [имя]; В отличие от подпрограмм, пакеты нельзя вызывать, передавать им параметры или вкладывать их друг в друга. В остальном формат пакета аналогичен формату подпрограммы: PACKAGE BODY имя IS -- тело (скрытая часть) -- объявления личных типов и объектов -- тела подпрограмм [BEGIN -- предложения инициализации] END [имя]; PACKAGE BODY имя IS -- тело (скрытая часть) -- объявления личных типов и объектов -- тела подпрограмм [BEGIN -- предложения инициализации] END [имя];

14 Интерфейс пакета 1. Спецификация содержит ОБЩИЕ объявления, которые видимы вашему приложению. 2. Тело содержит детали реализации и ЛИЧНЫЕ объявления, которые скрыты от вашего приложения. Можно отлаживать, развивать или заменять тело пакета, не изменяя интерфейса к этому телу (т.е. спецификации пакета). спецификация Тело пакета Приложение Пакет База данных

15 Синтаксис создания пакета Пакеты создаются интерактивно в SQL*Plus или SQL*DBA с помощью команд CREATE PACKAGE и CREATE PACKAGE BODY. ПРИМЕР: В пакет формируем тип записи, курсор и две процедуры управления кадрами: CREATE PACKAGE emp_actions AS -- спецификация TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL); CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp; PROCEDURE hire_employee (ename CHAR, job CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions; CREATE PACKAGE emp_actions AS -- спецификация TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL); CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp; PROCEDURE hire_employee (ename CHAR, job CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions;

16 Синтаксис создания пакета CREATE PACKAGE BODY emp_actions AS -- тело CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp IS SELECT empno, sal FROM emp ORDER BY sal DESC; PROCEDURE hire_employee (ename CHAR, job CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, sal, comm, deptno); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions; CREATE PACKAGE BODY emp_actions AS -- тело CURSOR desc_salary (emp_id NUMBER) RETURN EmpRecTyp IS SELECT empno, sal FROM emp ORDER BY sal DESC; PROCEDURE hire_employee (ename CHAR, job CHAR, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, sal, comm, deptno); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions;

17 Преимущество использования пакетов Модульность Облегчение Проектирования приложений Скрытие информации Расширенная функциональность Лучшая производительность Видимыми и доступными для приложений являются лишь объявления в спецификации пакета. Детали реализации в теле пакета скрыты и недоступны. Поэтому вы можете исправлять тело (реализацию), не перекомпилируя вызывающих программ.

18 Если спецификация пакета объявляет лишь типы, константы, переменные и исключения, тело пакета не нужно. Пример пакета, состоящего только из спецификации: PACKAGE trans_data IS TYPE TimeTyp IS RECORD (minute SMALLINT, hour SMALLINT); TYPE TransTyp IS RECORD (category VARCHAR2, account INTEGER, amount REAL, time TimeTyp); minimum_balance CONSTANT REAL := 10.00; number_processed INTEGER; insufficient_funds EXCEPTION; END trans_data; PACKAGE trans_data IS TYPE TimeTyp IS RECORD (minute SMALLINT, hour SMALLINT); TYPE TransTyp IS RECORD (category VARCHAR2, account INTEGER, amount REAL, time TimeTyp); minimum_balance CONSTANT REAL := 10.00; number_processed INTEGER; insufficient_funds EXCEPTION; END trans_data; Спецификация пакета

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

20 Обращение к содержимому пакета имя_пакета.имя_типа имя_пакета.имя_объекта имя_пакета.имя_подпрограммы имя_пакета.имя_типа имя_пакета.имя_объекта имя_пакета.имя_подпрограммы Для обращения к типам, объектам и подпрограммам, объявленным в спецификации пакета, используются квалифицированные ссылки: Вы можете обращаться к содержимому пакета из триггеров базы данных, хранимых подпрограмм, строенных блоков PL/SQL, а также анонимных блоков PL/SQL, посылаемых в ORACLE интерактивно через SQL*Plus или SQL*DBA.

21 Обращение к содержимому пакета DECLARE new_balance REAL;... BEGIN... IF new_balance < trans_data.minimum_balance THEN... END IF;... DECLARE new_balance REAL;... BEGIN... IF new_balance < trans_data.minimum_balance THEN... END IF;... Пример обращения к пакетированной переменной miminum_balance, которая объявлена в пакете trans_data:

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

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

24 Взаимодействие с Oracle

25 Команды SQL в PL/SQL 1.Извлечение строк данных из базы данных производится командой SELECT. 2.Изменение строк базы данных производится командами DML. 3.Управление транзакциями осуществляется командами COMMIT и ROLLBACK. 4.Пакет DBMS_SQL позволяет выполнять команды DML И DCL.

26 Выборка данных: синтаксис SELECT список_выборки INTO имя_переменной| имя_записи FROM таблица WHERE условие; SELECT список_выборки INTO имя_переменной| имя_записи FROM таблица WHERE условие; Предложение INTO обязательно. Должна быть возвращена только одна строка. Доступен полный синтаксис SELECT. SELECT Для выборки данных из базы данных используется команда SELECT

27 Выборка данных: пример PROCEDURE ship_date (v_ord_id IN NUMBER) IS v_date_ordered s_ord.date_ordered%TYPE; v_date_shipped s_ord.date_shipped%TYPE; BEGIN SELECT date_ordered, date_shipped INTO v_date_ordered, v_date_shipped FROM s_ord WHERE id=v_ord_id;... END ship_date; PROCEDURE ship_date (v_ord_id IN NUMBER) IS v_date_ordered s_ord.date_ordered%TYPE; v_date_shipped s_ord.date_shipped%TYPE; BEGIN SELECT date_ordered, date_shipped INTO v_date_ordered, v_date_shipped FROM s_ord WHERE id=v_ord_id;... END ship_date; Выборка даты размещения заказа и даты отгрузки

28 Выборка данных: пример FUNCTION sum_emp (v_dept_id IN NUMBER) RETURN NUMBER IS v_sum_salary s_emp.salary%TYPE; BEGIN SELECT SUM(salary) – групповая функция INTO v_sum_salary FROM s_emp WHERE dept_id=v_dept_id; RETURN (v_sum_salary); END sum_emp; FUNCTION sum_emp (v_dept_id IN NUMBER) RETURN NUMBER IS v_sum_salary s_emp.salary%TYPE; BEGIN SELECT SUM(salary) – групповая функция INTO v_sum_salary FROM s_emp WHERE dept_id=v_dept_id; RETURN (v_sum_salary); END sum_emp; Вывод суммы заработной платы всех сотрудников указанного отдела.

29 Выборка данных: пример PROCEDURE all_dept (v_dept_id IN NUMBER) IS dept_record s_dept%ROWTYPE; BEGIN SELECT * INTO dep_record --запись PL/SQL FROM s_dept WHERE id=v_dept_id;... END all_dept; PROCEDURE all_dept (v_dept_id IN NUMBER) IS dept_record s_dept%ROWTYPE; BEGIN SELECT * INTO dep_record --запись PL/SQL FROM s_dept WHERE id=v_dept_id;... END all_dept; Выборка всей информации об указанном отделе

30 Исключения для команды SELECT Предложение SELECT в PL/SQL должны возвращать только одну строку. Возврат нулевого количества строк или нескольких строк рассматривается как исключение. Исключения для команды SELECT: TOO_MANY_ROWS (слишком много строк) NO_DATA_FOUND (данные не обнаружены)

31 Манипулирование данными Изменения в базу данных вносятся с помощью команд DML. INSERT UPDATE DELETE

32 Команды COMMIT и ROLLBACk Транзакция начинается с первой команды DML, следующей за COMMIT или ROLLBACK. Транзакция завершается явно командой COMMIT или ROLLBACK.

33 Управление транзакциями: пример BEGIN INSERT INTO temp(num_col1, num_col2, char_col) VALUES (1,1,ROW 1); SAVEPOINT a; INSERT INTO temp(num_col, num_col2, char_col) VALUES (2,2,ROW 2); SAVEPOINT b; INSERT INTO temp(num_col, num_col2, char_col) VALUES (3,3,ROW 3); SAVEPOINT c; ROLLBACK TO SAVEPOINT b; END; BEGIN INSERT INTO temp(num_col1, num_col2, char_col) VALUES (1,1,ROW 1); SAVEPOINT a; INSERT INTO temp(num_col, num_col2, char_col) VALUES (2,2,ROW 2); SAVEPOINT b; INSERT INTO temp(num_col, num_col2, char_col) VALUES (3,3,ROW 3); SAVEPOINT c; ROLLBACK TO SAVEPOINT b; END;

34 Практическое занятие 1.Создайте процедуру для включения нового отдела в таблицу S_DEPT. 2.Создайте процедурe, обновляющую номер региона для существующего отдела. 3.Создайте процедуру для удаления отдела, созданного в упражнении 1. 4.Создайте процедуру NEW_EMP для включения записи о новом служащем в таблицу S_EMP.