Язык программирования PL/SQL1 Язык программирования ORACLE PL/SQL Учебные материалы ИВЦ АИС. - презентация

1

2 Язык программирования PL/SQL1 Язык программирования ORACLE PL/SQL Учебные материалы ИВЦ АИС

3 Язык программирования PL/SQL2 Содержание курса. (1) 4 Общий обзор PL/SQL –Что такое PL/SQL и зачем он нужен –Архитектура PL/SQL 4 Основы PL/SQL

4 Язык программирования PL/SQL3 Содержание курса. (2) 4 Взаимодействие с ORACLE 4 Подпрограммы 4 Пакеты 4 Хранимые функции в SQL-операторах

5 Язык программирования PL/SQL4 Содержание курса. (3) 4 PL/SQL в триггерах базы данных 4 PL/SQL в SQL*Plus 4 PL/SQL в ORACLE*Forms 4 Стандартные пакеты 4 Повторение. Ключевые компоненты PL/SQL

6 Язык программирования PL/SQL5 Общий обзор PL/SQL

7 Язык программирования PL/SQL6 Что такое PL/SQL ? 4 процедурное расширение языка SQL 4 полноценный процедурный язык 4 не самостоятельный продукт 4 технология, включающая механизм PL/SQL 4 этот механизм встраивается в ядро RDBMS (СУБД) или в любое инструментальное средство Oracle

8 Язык программирования PL/SQL7 4анонимный блок 4хранимую процедуру (функцию) в RDBMS 4процедуру (функцию) в хранимом пакете 4триггер базы данных 4библиотечную процедуру (функцию) 4триггер в инструментальном средстве ORACLE PL/SQL - программа может представлять собой:

9 Язык программирования PL/SQL8 История PL/SQL 4 v Oracle 6 (SQL*Plus и SQL*Forms) 4 v 1.1.-Developer/ v 2.0.-Oracle 7.0 и Developer/2000 r v 2.1.-Oracle v 2.2.-Oracle v 2.3.-Oracle 7.3

10 Язык программирования PL/SQL9 Пример программы

11 Язык программирования PL/SQL10 DECLARE CURSOR curs IS SELECT A,B,C FROM SQRTS ORDER BY A; D NUMBER; Z NUMBER; X01 NUMBER; X02 NUMBER; STR VARCHAR2(55); BEGIN FOR K IN curs LOOP D := K.B*K.B - 4*K.A*K.C; Z := 2*K.A; STR := 'A='||TO_CHAR(K.A,'09.99')||, B='||TO_CHAR(K.B,'09.99')|| ', C='||TO_CHAR(K.C,'09.99')||' : '; X01 := (-K.B - SQRT(D))/Z; X02 := (-K.B + SQRT(D))/Z; INSERT INTO TEMP (NUM_COL1,NUM_COL2,CHAR_COL) VALUES (X01,X02,STR); COMMIT; END LOOP; EXCEPTION WHEN ZERO_DIVIDE THEN STR := STR||'Это не квадратное уравнение'; INSERT INTO TEMP (CHAR_COL) VALUES (STR); WHEN VALUE_ERROR THEN STR := STR||'Действительных корней нет'; INSERT INTO TEMP (CHAR_COL) VALUES (STR); END;

12 Язык программирования PL/SQL11 Блочная структура [DECLARE ] BEGIN [EXCEPTION ] END; [DECLARE ] BEGIN [EXCEPTION ] END;

13 Язык программирования PL/SQL12 Переменные и константы 4О4Объявление переменных: X01 NUMBER(9); X02 NUMBER DEFAULT 5.1; STR VARCHAR(55); PI CONSTANT REAL := ; X01 NUMBER(9); X02 NUMBER DEFAULT 5.1; STR VARCHAR(55); PI CONSTANT REAL := ;

14 Язык программирования PL/SQL13 Переменные и константы 4242 способа присвоить значение переменной: 1) x02 := x01 * 10; 2) SELECT a*10.3 INTO x02 FROM sqrts WHERE b = 0; 1) x02 := x01 * 10; 2) SELECT a*10.3 INTO x02 FROM sqrts WHERE b = 0;

15 Язык программирования PL/SQL14 Атрибуты переменных 4%4%TYPE 4%4%ROWTYPE DECLARE x01 NUMBER(9); x02 x01%TYPE; str scott.temp.char_col%TYPE; CURSOR c1 IS SELECT a,b,c FROM sqrts; c_emp c1%ROWTYPE;... DECLARE x01 NUMBER(9); x02 x01%TYPE; str scott.temp.char_col%TYPE; CURSOR c1 IS SELECT a,b,c FROM sqrts; c_emp c1%ROWTYPE;...

16 Язык программирования PL/SQL15 Управляющие структуры 4I4IF-THEN-ELSE; 4F4FOR-LOOP; 4W4WHILE-LOOP; 4E4EXIT-WHEN; 4G4GOTO

17 Язык программирования PL/SQL16 Понятие курсора ORACLE использует рабочую приватную область для выполнения SQL - оператора ORACLE использует рабочую приватную область для выполнения SQL - оператора Курсор - механизм, позволяющий манипулировать информацией из этой области Курсор - механизм, позволяющий манипулировать информацией из этой области

18 Язык программирования PL/SQL17 Понятие курсора 1254КЛЕРК КУЗНЕЦОВ 4567 МЕНЕДЖЕРСИДОРОВ 7643ПРОДАВЕЦИВАНОВ 6543ДИРЕКТОРПЕТРОВ 1254КЛЕРК КУЗНЕЦОВ 4567 МЕНЕДЖЕРСИДОРОВ 7643ПРОДАВЕЦИВАНОВ 6543ДИРЕКТОРПЕТРОВ ПРОГРАММНЫЕ ПЕРЕМЕННЫЕ ПРОГРАММНЫЕ ПЕРЕМЕННЫЕ

19 Язык программирования PL/SQL18 Обработка ошибок PL/SQL позволяет легко обнаруживать и обрабатывать как предопределенные, так и определенные пользователем ошибочные условия, которые называются исключениями

20 Язык программирования PL/SQL19 Обработка ошибок 4И4Исключения: –п–предопределенные (возбуждаются неявно) –о–определенные пользователем (должны быть определены явно и возбуждаются оператором RAISE)

21 Язык программирования PL/SQL20 Модульность PL/SQL 4242 типа подпрограмм PL/SQL: –п–процедуры –ф–функции 4в4возможность объединения в пакет

22 Язык программирования PL/SQL21 Модульность PL/SQL PROCEDURE имя_процедуры (параметр1 ТИП, параметр2 ТИП...) IS BEGIN [EXCEPTION ] END; PROCEDURE имя_процедуры (параметр1 ТИП, параметр2 ТИП...) IS BEGIN [EXCEPTION ] END;

23 Язык программирования PL/SQL22 Архитектура PL/SQL 4В ORACLE - server: PL/SQL БЛОК PL/SQL Engine PL/SQL БЛОК Процедуры SQL Обработчик процедур PL/SQL

24 Язык программирования PL/SQL23 Архитектура PL/SQL 4В ORACLE - tools: Анонимный PL/SQL БЛОК PL/SQL Engine Обработчик SQL - операторов ORACLE - сервер Host - программа или инструмент. средство ORACLE Анонимный PL/SQL БЛОК Процедуры SQL Обработчик процедур PL/SQL

25 Язык программирования PL/SQL24 Основы PL/SQL

26 Язык программирования PL/SQL25 Набор символов 4В4В PL/SQL можно использовать: –б–буквы A.. Z, a.. z –ц–цифры –т–табуляцию, пробел, символ возврата каретки –с–символы ( ) + - * / < > = ! ~ ; %, # $ ^ & _ | { } ? [ ] –в–в символьных строках допускается использование кириллицы (русские буквы)

27 Язык программирования PL/SQL26 Лексические единицы 4Р4Разделители: –п–простые –с–составные 4И4Идентификаторы 4Л4Литералы 4К4Комментарии

28 Язык программирования PL/SQL27 Зарезервированные слова PL/SQL: ABORT COLAUTH ELSE LEVEL PRIVATE SQLERRM ACCEPT COLUMNS ELSIF LIKE PROCEDURE START ACCESS COMMIT END LIMITED PUBLIC STATEMENT ADD COMPRESS ENTRY LOOP RAISE STDDEV ALL CONNECT EXCEPTION MAX RANGE SUBTYPE ALTER CONSTANT EXCEPTION_INIT MIN REAL SUM AND COUNT EXISTS MINUS RECORD TABAUTH ANY CRASH EXIT MLSLABEL RELEASE TABLE ARRAY CREATE FALSE MOD REM TABLES ARRAYLEN CURRENT FETCH NATURAL RENAME TASK AS CURSOR FLOAT NEW RESOURCE TERMINATE ASC CURRVAL FOR NEXTVAL RETURN THEN ASSERT DATABASE FORM NOCOMPRESS REVERSE TO ASSIGN DATA_BASE FROM NOT REVOKE TRUE AT DATE FUNCTION NULL ROLLBACK TYPE AUTHORIZATION DBA GENERIC NUMBER ROWID UNION AVG DEBUGOFF GOTO NUMBER_BASE ROWLABEL UNIQUE BASE_TABLE DEBUGON GRANT OF ROWNUM UPDATE BEGIN DECLARE GROUP ON ROWTYPE USE BETWEEN DECIMAL HAVING OPEN RUN VALUES BINARY_INTEGER DEFAULT IDENTIFIED OPTION SAVEPOINT VARCHAR BODY DEFINITION IF OR SCHEMA VARCHAR2 BOOLEAN DELAY IN ORDER SELECT VARIANCE BY DELETE INDEX OTHERS SEPARATE VIEW CASE DELTA INDEXES OUT SET VIEWS CHAR DESC INDICATOR PACKAGE SIZE WHEN CHAR_BASE DIGITS INSERT PARTITION SMALLINT WHERE CHECK DISPOSE INTEGER PCTFREE SPACE WHILE CLOSE DISTINCT INTERSECT POSITIVE SQL WITH CLUSTER DO INTO PRAGMA SQLCODE WORK CLUSTERS DROP IS PRIOR SQLERRM XOR (~ 200 слов)

29 Язык программирования PL/SQL28 Комментарии это однострочный комментарий /* это многострочный комментарий */ Нельзя вкладывать комментарии друг в друга ! это однострочный комментарий /* это многострочный комментарий */ Нельзя вкладывать комментарии друг в друга !

30 Язык программирования PL/SQL29 Типы данных специфицируют формат хранения констант и переменных

31 Язык программирования PL/SQL30 Предопределенные типы данных

32 Язык программирования PL/SQL31 Категория NUMBER

33 Язык программирования PL/SQL32 Категория CHARACTER

34 Язык программирования PL/SQL33 Типы данных 4Т4Тип DATE: от 01 января г. до н.э. до 31 декабря г. н.э. 4Т4Тип BOOLEAN: TRUE FALSE NULL

35 Язык программирования PL/SQL34 Типы данных 4 Подтипы –Стандартные: SUBTYPE POSITIVE IS BINARY_INTEGER RANGE ; –Скалярные подтипы пользователя: SUBTYPE My_Typ IS BINARY_INTEGER RANGE 1..50; SUBTYPE My_Typ IS NUMBER(12,5);

36 Язык программирования PL/SQL35 Преобразование типов данных

37 Язык программирования PL/SQL36 Неявные преобразования типов позволяет использовать литералы, переменные и параметры одного типа там, где ожидается другой

38 Язык программирования PL/SQL37 Объявления переменных, констант, курсоров

39 Язык программирования PL/SQL38 4 программа хранит значения в переменных и константах; 4 значения констант не могут изменяться; 4 Все переменные, константы и курсоры должны быть объявлены в декларативной части блока; 4 при объявлении можно присвоить начальное значение и определить ограничение NOT NULL. Переменные, константы, курсоры

40 Язык программирования PL/SQL39 Примеры: birthdate DATE; emp_count SMALLINT := 0; sal_ma NUMBER(12.2) DEFAULT ; acct_id VARCHAR2(5) NOT NULL:='AP001'; pi CONSTANT REAL := ; radius REAL := 1; area REAL := pi*radius**2; birthdate DATE; emp_count SMALLINT := 0; sal_ma NUMBER(12.2) DEFAULT ; acct_id VARCHAR2(5) NOT NULL:='AP001'; pi CONSTANT REAL := ; radius REAL := 1; area REAL := pi*radius**2;

41 Язык программирования PL/SQL40 Атрибут %TYPE 4 позволяет ссылаться на: –тип данных ранее объявленной переменной; –существующий столбец базы данных. credit REAL(7,2); debit credit%TYPE; my_dname scott.dept.dname%TYPE; balance NUMBER(7,2); minimum_balance balance%TYPE:=10.00; credit REAL(7,2); debit credit%TYPE; my_dname scott.dept.dname%TYPE; balance NUMBER(7,2); minimum_balance balance%TYPE:=10.00;

42 Язык программирования PL/SQL41 Упражнения

43 Язык программирования PL/SQL42 Атрибут %ROWTYPE. 4 определяет тип записи, представляющей строку в таблице, представлении или в курсоре: DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE;... DECLARE emp_rec emp%ROWTYPE; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec c1%ROWTYPE;...

44 Язык программирования PL/SQL43 DECLARE dept_rec1 dept%ROWTYPE; dept_rec2 dept%ROWTYPE; TYPE DeptRecTyp IS RECORD (deptno NUMBER(2) NOT NULL := 20, dname dept.dname%TYPE, loc dept.loc%TYPE); dept_rec3 DeptRecTyp; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec4 c1%ROWTYPE; BEGIN SELECT deptno, dname, loc INTO dept_rec1 FROM dept WHERE rownum=1; OPEN c1; FETCH c1 INTO dept_rec3; CLOSE c1; dept_rec2 := dept_rec1;... END; DECLARE dept_rec1 dept%ROWTYPE; dept_rec2 dept%ROWTYPE; TYPE DeptRecTyp IS RECORD (deptno NUMBER(2) NOT NULL := 20, dname dept.dname%TYPE, loc dept.loc%TYPE); dept_rec3 DeptRecTyp; CURSOR c1 IS SELECT deptno, dname, loc FROM dept; dept_rec4 c1%ROWTYPE; BEGIN SELECT deptno, dname, loc INTO dept_rec1 FROM dept WHERE rownum=1; OPEN c1; FETCH c1 INTO dept_rec3; CLOSE c1; dept_rec2 := dept_rec1;... END;

45 Язык программирования PL/SQL44 DECLARE CURSOR my_cursor IS SELECT sal+NVL(comm,0) wages, ename FROM emp; my_rec my_cursor%ROWTYPE; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO my_rec; EXIT WHEN my_cursor%NOTFOUND; IF my_rec.wages > 2000 THEN INSERT INTO temp VALUES (null,my_rec.wages,my_rec.ename); END IF; END LOOP; CLOSE my_cursor; END; DECLARE CURSOR my_cursor IS SELECT sal+NVL(comm,0) wages, ename FROM emp; my_rec my_cursor%ROWTYPE; BEGIN OPEN my_cursor; LOOP FETCH my_cursor INTO my_rec; EXIT WHEN my_cursor%NOTFOUND; IF my_rec.wages > 2000 THEN INSERT INTO temp VALUES (null,my_rec.wages,my_rec.ename); END IF; END LOOP; CLOSE my_cursor; END;

46 Язык программирования PL/SQL45 Составной тип RECORD. 4 %ROWTYPE фактически объявляет переменную типа RECORD; 4 можно объявить переменную типа RECORD явно, например:

47 Язык программирования PL/SQL46 Составной тип RECORD. DECLARE TYPE Address IS RECORD (town VARCHAR2(40), street VARCHAR2(40, post_box NUMBER(12)); TYPE DeptRecTyp IS RECORD (deptno NUMBER(2) NOT NULL := 20, dname dept.dname%TYPE, loc dept.loc%TYPE, adr Address ); dept_rec3 DeptRecTyp;... DECLARE TYPE Address IS RECORD (town VARCHAR2(40), street VARCHAR2(40, post_box NUMBER(12)); TYPE DeptRecTyp IS RECORD (deptno NUMBER(2) NOT NULL := 20, dname dept.dname%TYPE, loc dept.loc%TYPE, adr Address ); dept_rec3 DeptRecTyp;...

48 Язык программирования PL/SQL47 Составной тип RECORD. Для обращения к элементам переменной типа RECORD используются квалифицированные ссылки, например: dept_rec3.adr.town := МОСКВА;

49 Язык программирования PL/SQL48 Область действия переменных Внешняя переменная x : Область действия DECLARE x REAL; BEGIN... DECLARE x REAL; BEGIN... END;... END; Область действия DECLARE x REAL; BEGIN... DECLARE x REAL; BEGIN... END;... END; Видимость DECLARE x REAL; BEGIN... DECLARE x REAL; BEGIN... END;... END; Видимость DECLARE x REAL; BEGIN... DECLARE x REAL; BEGIN... END;... END;

50 Язык программирования PL/SQL49 Область действия переменных Внутренняя переменная x : Область действия DECLARE x REAL; BEGIN… DECLARE x REAL; BEGIN... END;... END; Область действия DECLARE x REAL; BEGIN… DECLARE x REAL; BEGIN... END;... END; Видимость DECLARE x REAL; BEGIN... DECLARE x REAL; BEGIN … END;... END; Видимость DECLARE x REAL; BEGIN... DECLARE x REAL; BEGIN … END;... END;

51 Язык программирования PL/SQL50 Порядок выполнения операторов 4 **, NOT 4 +, - 4 *, / 4 +, -, || 4 =, !=,, =, IS NULL, LIKE, BETWEEN, IN 4 AND 4 OR скобки изменяют порядок вычисления выражений

52 Язык программирования PL/SQL51 Управляющие структуры

53 Язык программирования PL/SQL52 Логические выражения и операторы сравнения В PL/SQL логическая (булевская)переменная может принимать одно из трех значений: 4 TRUE 4 FALSE 4 NULL

54 Язык программирования PL/SQL53 Логические выражения и операторы сравнения 4 FALSE AND NULL = FALSE 4 TRUE OR NULL = TRUE 4 NOT NULL = NULL 4 Булевскому значению всегда равен результат операции сравнения. 4 Кроме обычных операторов сравнения используются –LIKE, –BEETWEN, –IN, –IS NULL

55 Язык программирования PL/SQL54 Условные операторы IF условие THEN ; END IF; IF условие THEN ; END IF; 4IF-THEN:

56 Язык программирования PL/SQL55 Условные операторы IF условие THEN ; ELSE ; END IF; IF условие THEN ; ELSE ; END IF; 4IF-THEN-ELSE:

57 Язык программирования PL/SQL56 Условные операторы IF условие 1 THEN ; ELSIF условие 2 THEN ; ELSE ; END IF; IF условие 1 THEN ; ELSIF условие 2 THEN ; ELSE ; END IF; 4IF-THEN-ELSIF:

58 Язык программирования PL/SQL57 4 Бесконечный цикл: LOOP ; END LOOP; LOOP ; END LOOP; Циклы

59 Язык программирования PL/SQL58 4 Цикл с предусловием: WHILE LOOP ; END LOOP; WHILE LOOP ; END LOOP; Циклы

60 Язык программирования PL/SQL59 Циклы 4 Цикл FOR: FOR IN [REVERSE].. LOOP ; END LOOP; FOR IN [REVERSE].. LOOP ; END LOOP;

61 Язык программирования PL/SQL60 Цикл FOR с курсором DECLARE CURSOR [(список параметров)] IS SELECT... ; BEGIN... FOR IN [(параметры)] LOOP... END LOOP; END; DECLARE CURSOR [(список параметров)] IS SELECT... ; BEGIN... FOR IN [(параметры)] LOOP... END LOOP; END;

62 Язык программирования PL/SQL61 Операторы GOTO и NULL BEGIN... GOTO insert_row;... > INSERT INTO emp VALUES END; BEGIN... GOTO insert_row;... > INSERT INTO emp VALUES END;

63 Язык программирования PL/SQL62 Упражнения

64 Язык программирования PL/SQL63 Встроенные функции 4 Все встроенные (built-in) функции, используемые в SQL, за исключением DECODE, доступны также в PL/SQL 4 Функции SQLCODE и SQLERRM доступны только в PL/SQL.

65 Язык программирования PL/SQL64 Тип данных RECORD 2 способа объявления переменной типа RECORD: 4 Использование атрибута %ROWTYPE имя_переменной{курсор%ROWTYPE| таблица%ROWTYPE| схема.таблица%ROWTYPE }; имя_переменной{курсор%ROWTYPE| таблица%ROWTYPE| схема.таблица%ROWTYPE };

66 Язык программирования PL/SQL65 Тип данных RECORD TYPE имя_типа IS RECORD (имя_поля1 {тип_поля | переменная%TYPE | таблица.столбец%TYPE | таблица%ROWTYPE} [NOT NULL],...); имя_переменной имя_типа; TYPE имя_типа IS RECORD (имя_поля1 {тип_поля | переменная%TYPE | таблица.столбец%TYPE | таблица%ROWTYPE} [NOT NULL],...); имя_переменной имя_типа; 4Объявление собственного типа RECORD:

67 Язык программирования PL/SQL66 Новый тип данных PL/SQL - Tables (отличие от v.1.0) 4 Что это за тип ? 4 Объявление таблиц PL/SQL 4 Обращение к таблицам PL/SQL 4 Извлечение строк из БД в таблицу PL/SQL и из таблицы PL/SQL в БД 4 Удаление данных из таблицы PL/SQL

68 Язык программирования PL/SQL67 Тип данных PL/SQL Table 4С4Составной тип данных, моделирующий таблицы БД 4С4Состоит из одного столбца и одного первичного ключа (индекса) –С–Столбец может принадлежать любому скалярному типу (для версий ниже 2.3) –Т–Тип первичного ключа всегда BINARY_INTEGER или один из его подтипов 4П4Появился в PL/SQL версии 2.0

69 Язык программирования PL/SQL68 Объявление таблиц PL/SQL 4 Таблицы PL/SQL объявляются за два шага : - -- Объявить тип -- Таблица PL/SQL: TYPE имя_типа IS TABLE OF {тип_столбца|переменная%TYPE| таблица.столбец%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER; -- Объявить таблицы PL/SQL -- этого типа: имя_переменной имя_типа;

70 Язык программирования PL/SQL69 Объявление таблиц PL/SQL. Пример DECLARE TYPE EnameTabTyp IS TABLE OF CHAR(10) INDEX BY BINARY_INTEGER; ename_tab EnameTabTyp; DECLARE TYPE EnameTabTyp IS TABLE OF CHAR(10) INDEX BY BINARY_INTEGER; ename_tab EnameTabTyp;

71 Язык программирования PL/SQL70 Обращение к таблицам PL/SQL 4 подобно обращению к элементу массива: имя_таблицы_plsql(знач_первичн_ключа) 4 присвоение значения строке таблицы: имя_таблицы_plsql(знач_первичн_ключа) := выражение_plsql; ename_tab(3) ename_tab(-5) sal_tab(5) := salary + increase; ename_tab(3) ename_tab(-5) sal_tab(5) := salary + increase;

72 Язык программирования PL/SQL71 Извлечение строк из БД в таблицу PL/SQL 4удобно использовать цикл FOR с курсором

73 Язык программирования PL/SQL72 DECLARE TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE SalTabTyp IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER; ename_tab EnameTabTyp; sal_tab SalTabTyp; i BINARY_INTEGER := 0;... BEGIN -- загрузить имена и оклады сотрудников -- в таблицы PL/SQL FOR emprec IN (SELECT ename, sal FROM emp) LOOP i := i + 1; ename_tab(i) := emprec.ename; sal_tab(i) := emprec.sal; END LOOP; -- обработать таблицы process_sals(ename_tab, sal_tab);... END; DECLARE TYPE EnameTabTyp IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; TYPE SalTabTyp IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER; ename_tab EnameTabTyp; sal_tab SalTabTyp; i BINARY_INTEGER := 0;... BEGIN -- загрузить имена и оклады сотрудников -- в таблицы PL/SQL FOR emprec IN (SELECT ename, sal FROM emp) LOOP i := i + 1; ename_tab(i) := emprec.ename; sal_tab(i) := emprec.sal; END LOOP; -- обработать таблицы process_sals(ename_tab, sal_tab);... END;

74 Язык программирования PL/SQL73 Удаление данных из таблицы PL/SQL 4 Присвоение пустой таблицы PL/SQL такого же типа 4 (для версий 2.3 и выше) - функция DELETE

75 Язык программирования PL/SQL74 Усовершенствования PL/SQL Table для версии В качестве типа столбца таблицы PL/SQL теперь допускается использование составного типа RECORD 4 Специально для работы с PL/SQL - таблицами в версию 2.3 включены новые функции.

76 Язык программирования PL/SQL75 Функции v2.3 для PL/SQL Table : COUNT DELETE EXIST FIRST LAST NEXT PRIOR

77 Язык программирования PL/SQL76 Функции v2.3 для PL/SQL Table : 4COUNT DELETE EXIST FIRST LAST NEXT PRIOR Найти количество определенных в настоящий момент элементов : total_rows := emp_table.COUNT; Найти количество определенных в настоящий момент элементов : total_rows := emp_table.COUNT;

78 Язык программирования PL/SQL77 Функции v2.3 для PL/SQL Table : COUNT 4DELETE EXISTS FIRST LAST NEXT PRIOR Удалить один или больше элементов из PL/SQL - таблицы: names_tab.DELETE; -- все строки names_tab.DELETE(85); ю стр -- строки от -120 до 5: names_tab.DELETE(-120,5); Удалить один или больше элементов из PL/SQL - таблицы: names_tab.DELETE; -- все строки names_tab.DELETE(85); ю стр -- строки от -120 до 5: names_tab.DELETE(-120,5);

79 Язык программирования PL/SQL78 Функции v2.3 для PL/SQL Table : COUNT DELETE 4EXISTS FIRST LAST NEXT PRIOR Определить, существует ли элемент таблицы с заданным значением индекса: IF emp_table.EXISTS(5) THEN... Определить, существует ли элемент таблицы с заданным значением индекса: IF emp_table.EXISTS(5) THEN...

80 Язык программирования PL/SQL79 Функции v2.3 для PL/SQL Table : COUNT DELETE EXIST 4FIRST LAST NEXT PRIOR Найти наименьшее значение индекса,для которого определен элемент таблицы : first_row := eployee_table.FIRST; Найти наименьшее значение индекса,для которого определен элемент таблицы : first_row := eployee_table.FIRST;

81 Язык программирования PL/SQL80 Функции v2.3 для PL/SQL Table : COUNT DELETE EXIST FIRST 4LAST NEXT PRIOR Найти наибольшее значение индекса,для которого определен элемент таблицы : last_row := eployee_table.LAST; Найти наибольшее значение индекса,для которого определен элемент таблицы : last_row := eployee_table.LAST;

82 Язык программирования PL/SQL81 Функции v2.3 для PL/SQL Table : COUNT DELETE EXIST FIRST LAST 4NEXT PRIOR Найти индекс следующего элемента: next_index := eployee_table.NEXT(curr_index); Найти индекс следующего элемента: next_index := eployee_table.NEXT(curr_index);

83 Язык программирования PL/SQL82 Функции v2.3 для PL/SQL Table : COUNT DELETE EXIST FIRST LAST NEXT 4PRIOR Найти индекс предыдущего элемента: prev_index := employee_table.PRIOR(curr_index); Найти индекс предыдущего элемента: prev_index := employee_table.PRIOR(curr_index);

84 Язык программирования PL/SQL83 Упражнения 4 3-2, 3-3, , 3-3,

85 Язык программирования PL/SQL84 Взаимодействие с ORACLE

86 Язык программирования PL/SQL85 Поддержка SQL 4SELECT 4UPDATE 4DELETE 4INSERT 4SELECT 4UPDATE 4DELETE 4INSERT допустимы все операторы DML: 4COMMIT 4ROLLBACK 4SAVEPOINT 4SET TRANSACTION 4COMMIT 4ROLLBACK 4SAVEPOINT 4SET TRANSACTION а также операторы уп- равления транзакциями: а также операторы уп- равления транзакциями:

87 Язык программирования PL/SQL86 Управление курсором 4 Объявление курсора 4 OPEN - Открыть курсор (без параметров или с параметрами) 4 FETCH - Извлечь данные 4 CLOSE - Закрыть курсор 4 FOR UPDATE - Извлечь данные для их изменения

88 Язык программирования PL/SQL87 Атрибуты курсора 4 %NOTFOUND 4 %FOUND 4 %ISOPEN 4 %ROWCOUNT

89 Язык программирования PL/SQL88 Объявление курсора 4БЕЗ ПАРАМЕТРОВ DECLARE CURSOR имя_курсора IS SELECT... FROM... WHERE... ;... 4С ПАРАМЕТРАМИ CURSOR имя_курсора(param ТИП) IS SELECT... FROM... WHERE col>parаm ; 4БЕЗ ПАРАМЕТРОВ DECLARE CURSOR имя_курсора IS SELECT... FROM... WHERE... ;... 4С ПАРАМЕТРАМИ CURSOR имя_курсора(param ТИП) IS SELECT... FROM... WHERE col>parаm ;

90 Язык программирования PL/SQL89 Открытие курсора 4 БЕЗ ПАРАМЕТРОВ: OPEN имя_курсора; 4 С ПАРАМЕТРАМИ: OPEN имя_курсора(значения парам.); 4 БЕЗ ПАРАМЕТРОВ: OPEN имя_курсора; 4 С ПАРАМЕТРАМИ: OPEN имя_курсора(значения парам.);

91 Язык программирования PL/SQL90 Извлечение данных из курсора OPEN имя_курсора; LOOP FETCH имя_курсора INTO приемник; EXIT WHEN имя_курсора%NOTFOUND;... END LOOP; CLOSE имя_курсора; OPEN имя_курсора; LOOP FETCH имя_курсора INTO приемник; EXIT WHEN имя_курсора%NOTFOUND;... END LOOP; CLOSE имя_курсора;

92 Язык программирования PL/SQL91 Изменение выбранных из курсора строк DECLARE CURSOR c1 IS SELECT empno, sal FROM emp WHERE job = 'SALESMAN' AND comm > sal FOR UPDATE;... BEGIN FOR rec IN c1 LOOP... UPDATE emp SET sal = new_sal WHERE CURRENT OF c1; END LOOP;... END; DECLARE CURSOR c1 IS SELECT empno, sal FROM emp WHERE job = 'SALESMAN' AND comm > sal FOR UPDATE;... BEGIN FOR rec IN c1 LOOP... UPDATE emp SET sal = new_sal WHERE CURRENT OF c1; END LOOP;... END;

93 Язык программирования PL/SQL92 Обработка исключений

94 Язык программирования PL/SQL93 4Внутренние исключения с предопределенными именами 4Внутренние исключения, не имеющие предопределенного имени 4Определенные пользователем исключения 4Пользовательский код ошибки 4Возбуждение исключения 4Порядок распространения исключения 4Вторичное возбуждение исключения 4Возбуждение исключения в декларативном разделе 4Возбуждение исключения при обработке исключений 4Использование SQLCODE и SQLERRM 4Необработанные исключения

95 Язык программирования PL/SQL94 Обработка исключений 4И4Исключение (EXCEPTION) в PL/SQL - это условие, приведшее к ошибке или предупреждению. 4И4Исключения могут быть внутренними или определяться пользователем.

96 Язык программирования PL/SQL95 Внутренние исключения с предопределенными именами

97 Язык программирования PL/SQL96 Внутренние исключения, не имеющие предопределенного имени DECLARE имя_исключения EXCEPTION; PRAGMA EXCEPTION_INIT (имя_исключения, код_ошибки_ORACLE);...

98 Язык программирования PL/SQL97 -- Пример: включить собственную обработку ошибки -- с кодом (недостаточно привилегий) -- в PL/SQL - блок: DECLARE insufficient_privileges EXCEPTION; PRAGMA EXCEPTION_INIT( insufficient_privileges, -1031); BEGIN... EXCEPTION WHEN insufficient_privileges THEN WHEN OTHERS THEN END;

99 Язык программирования PL/SQL98 Исключения, определенные пользователем DECLARE имя_исключения EXCEPTION;... BEGIN... IF... THEN RAISE имя_исключения; END IF; EXCEPTION WHEN имя_исключения THEN... END;

100 Язык программирования PL/SQL99 Пользовательский код ошибки 4 Используйте встроенную процедуру PROCEDURE RAISE_APPLICATION_ERROR(код_ошибки IN NUMBER, строка_сообщения IN VARCHAR2) код ошибки - от до ,

101 Язык программирования PL/SQL100 Пользовательский код ошибки CREATE TRIGGER SAVE_PRESIDENT BEFORE DELETE ON EMP FOR EACH ROW WHEN (OLD.JOB='ПРЕЗИДЕНТ') BEGIN RAISE_APPLICATION_ERROR(-20005, 'НЕ ТАК ПРОСТО УВОЛИТЬ ПРЕЗИДЕНТА'); END; CREATE TRIGGER SAVE_PRESIDENT BEFORE DELETE ON EMP FOR EACH ROW WHEN (OLD.JOB='ПРЕЗИДЕНТ') BEGIN RAISE_APPLICATION_ERROR(-20005, 'НЕ ТАК ПРОСТО УВОЛИТЬ ПРЕЗИДЕНТА'); END;

102 Язык программирования PL/SQL101 Подпрограммы 4 Общие правила написания процедур и функций 4 Где используются подпрограммы PL/SQL 4 Параметры PL/SQL-подпрограмм 4 Создание хранимых подпрограмм

103 Язык программирования PL/SQL102 Подпрограммы 4 поименованные PL/SQL - блоки, могут получать и возвращать параметры: –процедуры –функции 4 имеют разделы: –DECLARATION (декларативный) –EXECUTABLE (выполняемый) –EXEPTION-HANDLING (обработчик исключений)

104 Язык программирования PL/SQL103 Процедуры: PROCEDURE имя [(параметр [,параметр,...])] IS [локальные объявления] BEGIN исполняемые предложения [EXCEPTION обработчики исключений] END [имя];

105 Язык программирования PL/SQL104 Функции: FUNCTION имя [(параметр [,параметр,...])] RETURN тип_возвращаемого_ значения IS [локальные объявления] BEGIN исполняемые предложения [EXCEPTION обработчики исключений] END [имя];

106 Язык программирования PL/SQL105 Параметры PL/SQL - подпрограмм имя_переменной [IN|OUT|IN OUT] тип_данных [{:=|DEFAULT} значение]; 4Спецификатор типа данных в заголовке процедуры не может иметь ограничений

107 Язык программирования PL/SQL106 Создание хранимых подпрограмм 4 Чтобы создать хранимую подпрограмму, достаточно перед текстом подпрограммы вставить команду CREATE или CREATE OR REPLACE, и выполнить полученный скрипт в среде SQL*Plus

108 Язык программирования PL/SQL107 Пакеты 4 Пакет - это собрание различных PL/SQL - объектов, среди которых могут быть: Курсоры Переменные Константы Имена исключений Описания типов данных (записей и таблиц PL/SQL) Процедуры Функции

109 Язык программирования PL/SQL108 Преимущества пакетов: 4Принцип перегрузки (возможность создания в одном пакете нескольких подпрограмм с одинаковыми именами, но разным количеством и/или типами параметров). 4Сохранение значений пакетных переменных на протяжении сессии

110 Язык программирования PL/SQL109 Структура пакета 4 Спецификация содержит определения всех элементов пакета, на которые можно ссылаться извне пакета: PACKAGE имя_пакета IS [объявления переменных и типов] [спецификации курсоров] [спецификации модулей] END [имя_пакета]; PACKAGE имя_пакета IS [объявления переменных и типов] [спецификации курсоров] [спецификации модулей] END [имя_пакета];

111 Язык программирования PL/SQL110 Структура пакета 4 Тело пакета содержит программный код всех элементов пакета: PACKAGE BODY имя_пакета IS [объявления переменных и типов] [спецификации и операторы SELECT курсоров] [спецификации и текст модулей] [BEGIN выполняемые операторы] [EXCEPTION обработчики исключений] END [имя_пакета] PACKAGE BODY имя_пакета IS [объявления переменных и типов] [спецификации и операторы SELECT курсоров] [спецификации и текст модулей] [BEGIN выполняемые операторы] [EXCEPTION обработчики исключений] END [имя_пакета]

112 Язык программирования PL/SQL111 Обращение к элементам пакета 4 Для обращения к элементам пакета используются квалифицированные имена: sp_timer.show_elapsed system.sp_timer.show_elapsed sp_timer.show_elapsed system.sp_timer.show_elapsed

113 Язык программирования PL/SQL112 Хранимые PL/SQL - функции в SQL-операторах 4 Одиночные функции в SQL- операторах 4 Пакетные функции в SQL-операторах. Прагма RESTRICT_REFERENCES PRAGMA RESTRICT_REFERENCES (имя_функции, WNDS [,WNPS] [,RNDS] [,RNPS]) PRAGMA RESTRICT_REFERENCES (имя_функции, WNDS [,WNPS] [,RNDS] [,RNPS])

114 Язык программирования PL/SQL113 Пакетные функции в SQL- операторах. Пример PACKAGE my_package IS FUNCTION my_func( param_1 IN VARCHAR2) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES (my_func, WNDS, RNDS); END my_package; PACKAGE my_package IS FUNCTION my_func( param_1 IN VARCHAR2) RETURN NUMBER; PRAGMA RESTRICT_REFERENCES (my_func, WNDS, RNDS); END my_package;

115 Язык программирования PL/SQL114 Использование PL/SQL в триггерах базы данных 4 Параметры триггера 4 Доступ к значениям столбцов 4 Условные предикаты 4 Обработка ошибок в триггерах 4 Ограничения

116 Язык программирования PL/SQL115 Синтаксис создания триггера CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE|AFTER} {[DELETE][OR INSERT][OR UPDATE]} ON имя_таблицы [REFERENCING {new|old} AS псевдоним] [FOR EACH ROW [WHEN (условие) ]] DECLARE BEGIN END;

117 Язык программирования PL/SQL116 Включение/выключение триггеров ALTER TRIGGER имя_триггера {DISABLE | ENABLE}; ALTER TABLE имя_таблицы {DISABLE | ENABLE} ALL TRIGGERS;

118 Язык программирования PL/SQL117 Параметры триггера 4BEFORE / AFTER 4DELETE, INSERT, UPDATE 4FOR EACH ROW 4WHEN - (для FOR EACH ROW) 4REFERENCING

119 Язык программирования PL/SQL118 Доступ к значениям столбцов 4 корреляционные имена: :NEW.имя_столбца - новое значение; :OLD.имя_столбца - старое значение.

120 Язык программирования PL/SQL119 Условные предикаты 4 INSERTING 4 DELETING 4 UPDATING

121 Язык программирования PL/SQL типов триггеров 4 только команды DML 4 ограничено использование LONG и LONG RAW 4 триггер AFTER … FOR EACH ROW при создании журнала моментальных копий 4при откате не восстанавливаются значения переменных пакета; 4Не пишите триггеров, зависящих от порядка обработки строк командой SQL; 4(Измененная таблица). 4(Защищенная таблица). Обработка ошибок в триггерах Ограничения