Язык программирования ORACLE PL/SQL презентация

зачем он нужен
Архитектура PL/SQL
Основы PL/SQL

в ORACLE*Forms
Стандартные пакеты
Повторение. Ключевые компоненты PL/SQL

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

пакете
триггер базы данных
библиотечную процедуру (функцию)
триггер в инструментальном средстве ORACLE

PL/SQL - программа может представлять собой:

1.1.-Developer/2000
v 2.0.-Oracle 7.0
и Developer/2000 r.2.1
v 2.1.-Oracle 7.1
v 2.2.-Oracle 7.2
v 2.3.-Oracle 7.3

v 3.0 - Oracle 8 !

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;

CONSTANT REAL := 3.14158265;

x01 * 10;

2) SELECT a*10.3 INTO x02
FROM sqrts WHERE b = 0;

str scott.temp.char_col%TYPE;
CURSOR c1 IS SELECT a,b,c FROM sqrts;
c_emp c1%ROWTYPE;
...

оператора

Курсор - механизм,
позволяющий
манипулировать
информацией
из этой области

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

и возбуждаются оператором RAISE)

исключений>]
END;

.. z
цифры 0 .. 9
табуляцию, пробел, символ возврата каретки
символы ( ) + - * / < > = ! ~ ; : . ‘ @ % , “ # $ ^ & _ | { } ? [ ]
в символьных строках допускается использование кириллицы (русские буквы)

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 слов)

комментарии
друг в друга !

31 декабря 314712 г. н.э.
Тип BOOLEAN:
TRUE
FALSE
NULL

My_Typ IS BINARY_INTEGER RANGE 1..50;
SUBTYPE My_Typ IS NUMBER(12,5);

типа там, где ожидается другой

могут изменяться;
Все переменные, константы и курсоры должны быть объявлены в декларативной части блока;
при объявлении можно присвоить начальное значение и определить ограничение NOT NULL.

Переменные, константы, курсоры

1200.00;
acct_id VARCHAR2(5) NOT NULL:='AP001';
pi CONSTANT REAL := 3.14159;
radius REAL := 1;
area REAL := pi*radius**2;

базы данных.

credit REAL(7,2);
debit credit%TYPE;
my_dname scott.dept.dname%TYPE;
balance NUMBER(7,2);
minimum_balance balance%TYPE:=10.00;

представлении или в курсоре:

DECLARE
emp_rec emp%ROWTYPE;
CURSOR c1 IS SELECT deptno, dname, loc
FROM dept;
dept_rec c1%ROWTYPE;
...

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;

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;

переменную типа RECORD явно, например:

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;
...

используются
квалифицированные ссылки, например:
dept_rec3.adr.town := ‘МОСКВА’;

||
=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN
AND
OR

скобки изменяют
порядок вычисления выражений

одно из трех значений:
TRUE
FALSE
NULL

OR NULL = TRUE
NOT NULL = NULL

Булевскому значению всегда равен результат операции сравнения.
Кроме обычных операторов сравнения используются
LIKE,
BEETWEN,
IN,
IS NULL

IF;

IF-THEN:

<последовательность операторов 2>;
END IF;

IF-THEN-ELSE:

условие 2 THEN
<последовательность операторов 2>;
ELSE
<последовательность операторов 3>;
END IF;

IF-THEN-ELSIF:

LOOP
<последовательность
операторов>;
END LOOP;

SELECT ... ;
BEGIN
...
FOR <запись> IN <курсор>[(параметры)] LOOP
...
END LOOP;
END;

исключением DECODE, доступны также в PL/SQL
Функции SQLCODE и SQLERRM доступны только в PL/SQL.

%ROWTYPE

имя_переменной{курсор%ROWTYPE|
таблица%ROWTYPE|
схема.таблица%ROWTYPE };

переменная%TYPE |
таблица.столбец%TYPE |
таблица%ROWTYPE}
[NOT NULL],...);
имя_переменной имя_типа;

Объявление собственного типа RECORD:

это за тип ?
Объявление таблиц PL/SQL
Обращение к таблицам PL/SQL
Извлечение строк из БД в таблицу PL/SQL и из таблицы PL/SQL в БД
Удаление данных из таблицы PL/SQL

из одного столбца и одного первичного ключа (индекса)
Столбец может принадлежать любому скалярному типу (для версий ниже 2.3)
Тип первичного ключа всегда BINARY_INTEGER или один из его подтипов
Появился в PL/SQL версии 2.0

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

OF CHAR(10)
INDEX BY BINARY_INTEGER;
ename_tab EnameTabTyp;

имя_таблицы_plsql(знач_первичн_ключа)
присвоение значения строке таблицы: имя_таблицы_plsql(знач_первичн_ключа) := выражение_plsql;

ename_tab(3)
ename_tab(-5)
sal_tab(5) := salary + increase;

FOR с курсором

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;

же типа
(для версий 2.3 и выше) - функция DELETE

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

момент элементов :
total_rows := emp_table.COUNT;

из PL/SQL - таблицы:
names_tab.DELETE; -- все строки
names_tab.DELETE(85); -- 85-ю стр
-- строки от -120 до 5:
names_tab.DELETE(-120,5);

с заданным значением индекса:

IF emp_table.EXISTS(5) THEN ...

определен элемент таблицы :

first_row := eployee_table.FIRST;

определен элемент таблицы :

last_row := eployee_table.LAST;

eployee_table.NEXT(curr_index);

или
с параметрами)
FETCH - Извлечь данные
CLOSE - Закрыть курсор
FOR UPDATE - Извлечь
данные для их
изменения

... FROM ... WHERE ... ;
...
С ПАРАМЕТРАМИ
CURSOR имя_курсора(param ТИП) IS
SELECT ... FROM ... WHERE col>parаm ;

приемник;
EXIT WHEN имя_курсора%NOTFOUND;
...
END LOOP;
CLOSE имя_курсора;

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;

имени
Определенные пользователем исключения
Пользовательский код ошибки
Возбуждение исключения
Порядок распространения исключения
Вторичное возбуждение исключения
Возбуждение исключения в декларативном разделе
Возбуждение исключения при обработке исключений
Использование SQLCODE и SQLERRM
Необработанные исключения

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

PRAGMA EXCEPTION_INIT
(имя_исключения,
код_ошибки_ORACLE);
...

-1013 (недостаточно привилегий)
-- в PL/SQL - блок:
DECLARE
insufficient_privileges EXCEPTION;
PRAGMA EXCEPTION_INIT(
insufficient_privileges, -1031);
BEGIN
...
EXCEPTION
WHEN insufficient_privileges THEN
<действия по обработке ошибки ORA-1031>
WHEN OTHERS THEN
<действия по обработке всех неописанных
исключений>
END;

...
IF ... THEN
RAISE имя_исключения;
END IF;
EXCEPTION
WHEN имя_исключения THEN
...
END;

IN VARCHAR2)
код ошибки - от -20000 до -20999,

ROW
WHEN (OLD.JOB='ПРЕЗИДЕНТ')
BEGIN
RAISE_APPLICATION_ERROR(-20005,
'НЕ ТАК ПРОСТО УВОЛИТЬ ПРЕЗИДЕНТА');
END;

PL/SQL-подпрограмм
Создание хранимых подпрограмм

разделы:
DECLARATION (декларативный)
EXECUTABLE (выполняемый)
EXEPTION-HANDLING (обработчик исключений)

[,параметр, ...])]
IS
[локальные объявления]
BEGIN
исполняемые предложения
[EXCEPTION
обработчики исключений]
END [имя];

тип_возвращаемого_
значения
IS
[локальные объявления]
BEGIN
исполняемые предложения
[EXCEPTION
обработчики исключений]
END [имя];

[{:=|DEFAULT} значение];

Спецификатор типа данных в
заголовке процедуры не может
иметь ограничений

подпрограммы вставить команду CREATE или CREATE OR REPLACE, и выполнить полученный скрипт в среде SQL*Plus

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

подпрограмм с одинаковыми именами, но разным количеством и/или типами параметров).
Сохранение значений пакетных переменных на протяжении сессии

которые можно ссылаться извне пакета:

PACKAGE имя_пакета
IS
[объявления переменных
и типов]
[спецификации курсоров]
[спецификации модулей]
END [имя_пакета];

пакета:

PACKAGE BODY имя_пакета
IS
[объявления переменных и типов]
[спецификации
и операторы SELECT курсоров]
[спецификации и текст модулей]
[BEGIN
выполняемые операторы]
[EXCEPTION
обработчики исключений]
END [имя_пакета]

квалифицированные имена:

sp_timer.show_elapsed

system.sp_timer.show_elapsed

функции в SQL-операторах.
Прагма RESTRICT_REFERENCES

PRAGMA
RESTRICT_REFERENCES (имя_функции,
WNDS [,WNPS] [,RNDS] [,RNPS])

param_1 IN VARCHAR2)
RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES
(my_func, WNDS, RNDS);
END my_package;

столбцов
Условные предикаты
Обработка ошибок в триггерах
Ограничения

{[DELETE][OR INSERT][OR UPDATE]}
ON имя_таблицы
[REFERENCING {new|old} AS псевдоним]
[FOR EACH ROW
[WHEN (условие) ]]
DECLARE
<объявления>
BEGIN
<блок PL/SQL>
END;

ENABLE};

ALTER TABLE имя_таблицы
{DISABLE | ENABLE}
ALL TRIGGERS;

ROW
WHEN - (для FOR EACH ROW)
REFERENCING

старое значение.

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

Обработка ошибок в триггерах
Ограничения