Шаг 86 - PL/SQL - Язык программирования Oracle (стр. 1 )

Шаг 86 - PL/SQL - Язык программирования Oracle

В "Шаг 73 - PL/SQL - функции PL/SQL осталось немного..." мы с вами закончили близкое рассмотрение функций PL/SQL - вот теперь давайте вернемся к этому вопросу, а именно изучению языка PL/SQL. В "Шаг 38 - PL/SQL - вводный курс" я уже останавливался на описании языка PL/SQL, но кое что, еще осталось и думаю, что это вам будет не безынтересно. Итак SQL - в PL/SQL соответствует стандарту ANSI (American National Standards Institute - Американский национальный институт стандартов.) определенному документом ANSI X3.135-1992 "Database Language SQL". Именно этот стандарт известный как SQL92 (или SQL2), сам по себе определяет язык SQL и не описывает 3GLPL/SQL. Cтандарт SQL92 имеет три уровня согласования: Entry (начальный), Intermediate (промежуточный) и Full (полный). Oracle 7 варианта 7.2 - (и все другие версии более высокого уровня, в том числе Oracle8) соответствует стандартам Entry SQL92, что одобрено NIST (National Institute for Standards and Technology - Национальный Институт стандартов и технологий). В настоящее время Oracle - работает с ANSI, чтобы обеспечить, соответствие будущих версий Oracle и PL/SQL полному стандарту. Может показаться неожиданным, но язык PL/SQL разработан на основе языка третьего поколения Ada (хотя мне лично, как кажется я уже говорил он очень напоминает язык Pascal!). Многие конструкции, применяемые в Ada можно найти в PL/SQL - такие, например как модули - так называемые пакеты. Но о них мы поговорим позже. Как Вы, наверное, поняли базовой единицей языка PL/SQL является блок (block), который имеет следующую структуру:

DECLARE

............

BEGIN

........

EXCEPTION

........

END;

Думаю, знакомая вам конструкция!? :) Это так называемый анонимный блок. Такой блок каждый раз при выполнении компилируется. Так же он не хранится в базе данных и не может быть вызван из другого блока. В данном случае приступим к изучению именованных блоков - процедур и функций. Такие блоки хранятся в базе данных и могут быть использованы повторно. А вот это не маловажный фактор! Итак, собственно первая конструкция, которую мы рассмотрим, это именованный блок или ПРОЦЕДУРА. Синтаксис его объявления таков:

-------------- CREATE [OR REPLACE] PROCEDURE - имя_процедуры --------------------------------- (аргумент [IN] [OUT] [IN OUT] тип, ..... ) AS [IS] ---------------------------- тело процедуры --------------------------------------------------

где:

·  (имя_процедуры) - имя создаваемой процедуры,

·  (аргумент) - имя параметра процедуры,

·  (тип) - это тип соответствующего параметра,

·  (тело процедуры) - это блок PL/SQL в котором содержится текст процедуры.

Для изменения программного кода процедуры ее необходимо удалить, а затем создать вновь, для того, чтобы делать это за один проход применяется дополнительный оператор объявления REPLACE, что буквально понимается, как заменить. Если его не применять, то процедуру необходимо удалить с помощью оператора DROP PROCEDURE имя. Вот таким образом, определяется данная конструкция. Далее мы с вами поучимся создавать процедуры.

Шаг 87 - PL/SQL - Именованные блоки процедуры

Закончив знакомство с синтаксисом и объявлениями, давайте наконец, займемся делом. Напишем нашу с вами первую процедуру и познакомимся со всем, что с этим связано. Итак, запускаем SQL*Plus и отправим на исполнение следующий код: (программирование однако!)

CREATE PROCEDURE TESTPRG

AS

BEGIN

NULL;

END TESTPRG;

/

Получаем после компиляции:

SQL> CREATE PROCEDURE TESTPRG

2 AS

3

4 BEGIN

5

6 NULL;

7

8 END TESTPRG;

9 /

Процедура создана.

SQL>

Ура! Наша первая с вами процедура создана и находится внутри нашего сервера и не только находится, но еще и готова к дальнейшему использованию! Итак, для начала давайте убедимся, что она есть и здорова! Введем следующий запрос:

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS

FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'PROCEDURE'

/

Получаем после прохода:

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS

2 FROM USER_OBJECTS

3 WHERE OBJECT_TYPE = 'PROCEDURE'

4 /

OBJECT_NAME OBJECT_TYPE STATUS

------------ ----------- ------

TESTPRG PROCEDURE VALID

SQL>

Что ж теперь в вашем владении есть процедура TESTPRG и она совершенно исправна, о чем говорит запись в поле STATUS со значением VALID. К слову сказать, при выполнении оператора CREATE PROCEDURE вследствие того, что он является оператором DDL, то неявно вызывается оператор COMMIT. Помните в шаге 6, я намеренно сделал ошибку сказав, что сделайте COMMIT после оператора GRANT! После чего получил кучу нареканий, от некоторых гугу жанра! Но, я это сделал по той простой причине, .... а впрочем какая разница, сделал я это намеренно или нет! :) Идем дальше. Итак, теперь надеюсь кое-что стало яснее. При первом приближении хорошо видно, что наша процедура совершенно бесполезна, так как, в объявлении ее тела стоит только оператор NULL, который как мы знаем ничего собственно не делает кроме как объявляет сам себя. Но это не так ведь мы с вами учимся, значит это не совсем бесполезно! :) Оставим в покое нашу процедуру, в плане полезности и попробуем, немного переделать ее:

CREATE PROCEDURE TESTPRG

IS

BEGIN

-- Empty Operator -------

NULL;

END TESTPRG;

/

Получаем:

SQL> CREATE PROCEDURE TESTPRG

2 IS

3

4 BEGIN

5 -- Empty Operator -------

6 NULL;

7

8 END TESTPRG;

9 /

CREATE PROCEDURE TESTPRG

*

ошибка в строке 1:

ORA-00955: имя уже задействовано для существующего объекта

Ой - а вот здесь, как раз нужно было ее удалить, а потом вновь создать, например вот так:

DROP PROCEDURE TESTPRG

/

Увидим:

SQL> DROP PROCEDURE TESTPRG

2 /

Процедура удалена.

Но, это не совсем рентабельно, давайте снова создадим нашу с вами процедуру, но несколько по другому:

CREATE OR REPLACE PROCEDURE TESTPRG

AS

BEGIN

NULL;

END TESTPRG;

/

Получаем после компиляции:

SQL> CREATE OR REPLACE PROCEDURE TESTPRG

2 AS

3

4 BEGIN

5

6 NULL;

7

8 END TESTPRG;

9 /

Процедура создана.

SQL>

Вот теперь изменить ее можно так же за один проход не применяя DROP PROCEDURE TESTPRG! Попробуем проделать следующее:

CREATE OR REPLACE PROCEDURE TESTPRG

IS

BEGIN

-- Empty Operator -------

NULL;

END TESTPRG;

/

Получаем после компиляции:

SQL> CREATE OR REPLACE PROCEDURE TESTPRG

2 IS

3

4 BEGIN

5 -- Empty Operator -------

6 NULL;

7

8 END TESTPRG;

9 /

Процедура создана.

SQL>

Все что мы сделали - это добавили строку комментария и сменили AS на IS, что само по себе не принципиально, но позволяет показать как можно создать процедуру изменить ее или удалить! Вот и все три действия, которыми мы как правило пользуемся! Думаю, что теперь вы сами можете что-нибудь создать! :)

Шаг 88 - PL/SQL - Работаем с процедурами

Вот теперь давайте создадим, что то осмысленное и попытаемся получить хоть какой-то результат... Снова вступает в бой MILLER/KOLOBOK наперевес с SQL*Plus. Запишем такую процедуру:

CREATE OR REPLACE PROCEDURE TESTPRGTWO

IS

BEGIN

DBMS_OUTPUT. enable;

DBMS_OUTPUT. put_line('HELLO!!! I AM TESTPRGTWO!!! REMEMBER ME?!');

END TESTPRGTWO;

/

После компиляции получаем:

SQL> CREATE OR REPLACE PROCEDURE TESTPRGTWO

2 IS

3

4 BEGIN

5

6 DBMS_OUTPUT. enable;

7 DBMS_OUTPUT. put_line('HELLO!!! I AM TESTPRGTWO!!! REMEMBER ME?!');

8

9 END TESTPRGTWO;

10 /

Процедура создана.

SQL>

Итак, вот теперь наша процедура выводит приветственное послание - HELLO!!! I AM TESTPRGTWO!!! REMEMBER ME?! Но как в этом убедится, что она вообще, что то выводит? Можно вот так - применив, оператор EXECUTE:

SET SERVEROUTPUT ON

EXEC TESTPRGTWO;

Увидим:

SQL> SET SERVEROUTPUT ON

SQL> EXEC TESTPRGTWO;

HELLO!!! I AM TESTPRGTWO!!! I HEVE REPLACE MYSELF NOW!

Процедура PL/SQL успешно завершена.

Вот теперь судя по последней записи, совершенно ясно, что наша процедура откомпилирована, хранится в БД (поэтому она еще называется хранимая процедура) и выполняет определенные действия. Есть еще и второй способ, это вызвать процедуру на исполнение напрямую из анонимного блока, скажем вот так:

SET SERVEROUTPUT ON

DECLARE

BEGIN

TESTPRGTWO;

END;

/

Получаем:

SQL> SET SERVEROUTPUT ON

SQL>

SQL> DECLARE

2

3 BEGIN

4

5 TESTPRGTWO;

6

7 END;

8 /

HELLO!!! I AM TESTPRGTWO!!! I HEVE REPLACE MYSELF NOW!

Процедура PL/SQL успешно завершена.

Теперь, анонимный блок вызвал именованный, и после выполнения завершился. Теперь, у нас с вами две процедуры, одна из которых, вообще ничего не делает, а другая приветствует, да и то, только если попросят! :) Давайте посмотрим запрос из прошлого шага, но более полный:

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, STATUS

2 FROM USER_OBJECTS

3 /

OBJECT_NAME OBJECT_TYPE STATUS

----------------- ------------------ -------

BOYS TABLE VALID

CUSTOMERS TABLE VALID

GIRLS TABLE VALID

OFFICES TABLE VALID

ORDERS TABLE VALID

PRODUCTS TABLE VALID

SALESREPS TABLE VALID

SYS_C003505 INDEX VALID

SYS_C003506 INDEX VALID

SYS_C003507 INDEX VALID

SYS_C003511 INDEX VALID

SYS_C003512 INDEX VALID

SYS_C003513 INDEX VALID

SYS_C003515 INDEX VALID

TESTPRG PROCEDURE VALID

TESTPRGTWO PROCEDURE VALID

16 rows selected

SQL>

Вот теперь хорошо видно, что у нас с вами семь таблиц, семь индексов и, о чудо! Две процедуры! Надеюсь, что вскоре их станет больше! :)

Шаг 89 - PL/SQL - Процедуры и их параметры

Наверное вы уже заметили, что в наших прошлых процедурах, явно чего-то не хватает. Думаю, если кто-либо из вас знаком с языками программирования, например C++ или Pascal, то вы могли задать вопрос, а можно в PL/SQL - процедурах передавать параметры? Да не просто можно, а нужно! И работа с параметрами процедур PL/SQL довольно интересна! Давайте попробуем написать процедуру с параметрами: