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

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

CREATE OR REPLACE PROCEDURE TESTPRM(NUM IN NUMBER)
IS

in_COMP VARCHAR2(50);

BEGIN

	SELECT COMPANY INTO in_COMP FROM customers
	WHERE customers.CUST_NUM = NUM;

	DBMS_OUTPUT.enable;
    DBMS_OUTPUT.put_line(in_COMP);

END TESTPRM;
/

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

SQL> CREATE OR REPLACE PROCEDURE TESTPRM(NUM IN NUMBER)
  2  IS
  3  
  4  in_COMP VARCHAR2(50);
  5  
  6  BEGIN
  7  
  8   SELECT COMPANY INTO in_COMP FROM customers
  9   WHERE customers.CUST_NUM = NUM;
 10  
 11   DBMS_OUTPUT.enable;
 12      DBMS_OUTPUT.put_line(in_COMP);
 13  
 14  END TESTPRM;
 15  /

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

Здесь, я применил параметр NUM с типом NUMBER. IN в данном случае определяет, что параметр является входным. Что так же справедливо и по умолчанию. То есть, если параметр имеет только тип IN, то предикат IN можно не указывать. Так же напомню, что NUM является формальным параметром функции (formal parameters). А вот входное значение функции является фактическим параметром (actual parameters). Формальный параметр NUM, является только вместилищем для фактически передаваемого параметра и все операции производятся с формальным параметром (в программировании, если я правильно помню, это называется передача параметра в процедуру по значению).

Сам предикат IN означает следующее: IN - Значение фактического параметра передается в процедуру при ее вызове. Внутри процедуры формальный параметр рассматривается в качестве параметра только для чтения - он не может быть изменен. Когда процедура завершается и управление передается в вызывающую среду, фактический параметр не изменяется.

Что, собственно я и говорил выше. Вернемся к нашей процедуре, она содержит, один оператор SELECT (уже что-то полезное!), который выбирает одну запись из таблицы customers, в данном случае заказчика по его номеру. Для примера вызовем ее из анонимного блока предварительно определив и передав ей параметр:

SET SERVEROUTPUT ON

DECLARE 

BEGIN

TESTPRM(2112);
	
END;
/

Получаем после вызова:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE 
  2  
  3  BEGIN
  4  
  5  TESTPRM(2112);
  6   
  7  END;
  8  /
Апельсин

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

Вот такой "желтый и круглый" заказчик! :) Ясно видно, как сработал оператор SELECT и функция TESTPRM вывела результат. При этом фактический параметр, был передан и не изменялся. А теперь попробуем написать более сложную процедуру:

CREATE OR REPLACE PROCEDURE TESTOUT(NUM IN NUMBER, DT OUT VARCHAR2)
IS

BEGIN

	SELECT COMPANY INTO DT FROM customers
	WHERE customers.CUST_NUM = NUM;

END TESTOUT;
/

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

SQL> CREATE OR REPLACE PROCEDURE TESTOUT(NUM IN NUMBER, DT OUT VARCHAR2)
  2  IS
  3  
  4  BEGIN
  5  
  6   SELECT COMPANY INTO DT FROM customers
  7   WHERE customers.CUST_NUM = NUM;
  8  
  9  END TESTOUT;
 10  /

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

Рассмотрим параметры более внимательно, про первый мы уже все знаем, а вот второй DT определен как OUT и имеет тип VARCHAR2. Что это значит? А, вот что. DT выходной параметр и принимает значение из самой процедуры на возврат, т.е.

OUT - Любое значение имеющее фактический параметр при вызове процедуры игнорируется. Внутри процедуры формальный параметр рассматривается как параметр только для записи - ему можно присвоить значение, но считать из него значение нельзя. (!) Когда процедура завершается и управление передается в вызывающую среду, содержимое формального параметра присваивается фактическому параметру.

Вот так работает тип OUT. Давайте определим анонимный блок и посмотрим, что же получается:

DECLARE 

FRDT VARCHAR2(100);

BEGIN

	FRDT := 'HELLO';
	TESTOUT(2103, FRDT);
	DBMS_OUTPUT.enable;
    DBMS_OUTPUT.put_line('CUSTOMER '||FRDT);

END;
/

Получаем:

SQL> DECLARE 
  2  
  3  FRDT VARCHAR2(100);
  4  
  5  BEGIN
  6  
  7   FRDT := 'HELLO';
  8  
  9   TESTOUT(2103, FRDT);
 10   
 11   DBMS_OUTPUT.enable;
 12      DBMS_OUTPUT.put_line('CUSTOMER '||FRDT);
 13  
 14  END;
 15  /
CUSTOMER Крупное предприятие

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

Заметили, что значение HELLO не появилось? Естественно, так как параметр DT функции TESTOUT является выходным. Если сказать честно, то лично меня такой тип возврата данных из процедуры немного шокирует, так как в классическом программировании такое применяется по моему редко, хотя я и могу ошибаться. Но, тем не менее может быть в каком-то случае такой вызов вполне оправдан! Можете пока поработать со всем этим. :)


Предыдущий Шаг | Следующий Шаг | Оглавление
Автор Летучий Сергей - 03.02.2004