OUT в Oracle PL/SQL — как возвращать значения из процедур

OUT — это один из трёх режимов параметров в Oracle PL/SQL, наряду с IN и IN OUT. Он используется для передачи значений из процедуры или функции наружу — вызывающему коду. То есть переменная, переданная как OUT, получает значение внутри процедуры и используется после её завершения.

🖋️ Синтаксис параметра OUT

PROCEDURE имя_процедуры(параметр OUT тип)

Пример объявления:

CREATE OR REPLACE PROCEDURE get_bonus (
  emp_id IN NUMBER,
  bonus OUT NUMBER
) AS
BEGIN
  SELECT salary * 0.1 INTO bonus FROM employees WHERE employee_id = emp_id;
END;

Вызов:

DECLARE
  v_bonus NUMBER;
BEGIN
  get_bonus(100, v_bonus);
  DBMS_OUTPUT.PUT_LINE('Бонус: ' || v_bonus);
END;

🔄 Где используется OUT в Oracle PL/SQL

• Получение результатов из процедуры
• Возврат нескольких значений
• Передача переменных из курсоров
• Использование с RETURNING INTO
• Управление флагами, статусами, сообщениями

📊 Примеры использования OUT в Oracle SQL/PLSQL (1–50 из 50)

1. Простая процедура с OUT:

CREATE PROCEDURE get_name (emp_id IN NUMBER, emp_name OUT VARCHAR2) AS
BEGIN
  SELECT first_name INTO emp_name FROM employees WHERE employee_id = emp_id;
END;

2. Возврат нескольких значений:

CREATE PROCEDURE get_info (
  emp_id IN NUMBER,
  emp_name OUT VARCHAR2,
  emp_salary OUT NUMBER
) AS
BEGIN
  SELECT first_name, salary INTO emp_name, emp_salary FROM employees WHERE employee_id = emp_id;
END;

3. Использование OUT с BOOLEAN:

CREATE PROCEDURE check_salary (
  emp_id IN NUMBER,
  is_high OUT BOOLEAN
) AS
  v_salary NUMBER;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE employee_id = emp_id;
  is_high := v_salary > 10000;
END;

4. OUT с RETURNING INTO:

CREATE PROCEDURE delete_employee (
  emp_id IN NUMBER,
  emp_name OUT VARCHAR2
) AS
BEGIN
  DELETE FROM employees WHERE employee_id = emp_id RETURNING first_name INTO emp_name;
END;

5. OUT с курсором:

CREATE PROCEDURE fetch_departments (cur OUT SYS_REFCURSOR) AS
BEGIN
  OPEN cur FOR SELECT * FROM departments;
END;

6. OUT-параметр для генерации ID:

CREATE PROCEDURE create_order (
  customer_id IN NUMBER,
  new_order_id OUT NUMBER
) AS
BEGIN
  INSERT INTO orders (customer_id, order_date)
  VALUES (customer_id, SYSDATE)
  RETURNING order_id INTO new_order_id;
END;

7. Передача сообщения об ошибке:

CREATE PROCEDURE validate_input (
  p_value IN NUMBER,
  p_status OUT VARCHAR2
) AS
BEGIN
  IF p_value < 0 THEN
    p_status := 'Ошибка: отрицательное значение';
  ELSE
    p_status := 'OK';
  END IF;
END;

8. Вывод текущего пользователя:

CREATE PROCEDURE who_am_i (login OUT VARCHAR2) AS
BEGIN
  login := USER;
END;

9. OUT с SELECT COUNT:

CREATE PROCEDURE count_employees (
  dept_id IN NUMBER,
  total OUT NUMBER
) AS
BEGIN
  SELECT COUNT(*) INTO total FROM employees WHERE department_id = dept_id;
END;

10. OUT как индикатор успеха:

CREATE PROCEDURE do_something (
  success OUT BOOLEAN
) AS
BEGIN
  success := TRUE;
END;

11. OUT с записью в лог:

CREATE PROCEDURE write_log (
  msg IN VARCHAR2,
  status OUT VARCHAR2
) AS
BEGIN
  INSERT INTO logs(message) VALUES(msg);
  status := 'Сохранено';
END;

12. OUT с датой:

CREATE PROCEDURE get_today (p_date OUT DATE) AS
BEGIN
  p_date := SYSDATE;
END;

13. OUT с расчётом налога:

CREATE PROCEDURE calc_tax (
  amount IN NUMBER,
  tax OUT NUMBER
) AS
BEGIN
  tax := amount * 0.2;
END;

14. OUT с флагом активности:

CREATE PROCEDURE is_active (
  user_id IN NUMBER,
  active OUT BOOLEAN
) AS
  v_status VARCHAR2(1);
BEGIN
  SELECT status INTO v_status FROM users WHERE id = user_id;
  active := (v_status = 'A');
END;

15. OUT для вычисления площади:

CREATE PROCEDURE calc_area (
  a IN NUMBER,
  b IN NUMBER,
  area OUT NUMBER
) AS
BEGIN
  area := a * b;
END;

16. OUT с REF CURSOR и фильтрацией:

CREATE PROCEDURE get_high_salary (cur OUT SYS_REFCURSOR) AS
BEGIN
  OPEN cur FOR SELECT * FROM employees WHERE salary > 10000;
END;

17. OUT с мультипараметрами:

CREATE PROCEDURE user_stats (
  user_id IN NUMBER,
  last_login OUT DATE,
  total_orders OUT NUMBER
) AS
BEGIN
  SELECT MAX(login_date) INTO last_login FROM logins WHERE id = user_id;
  SELECT COUNT(*) INTO total_orders FROM orders WHERE customer_id = user_id;
END;

18. OUT и NULL значение:

CREATE PROCEDURE get_middle_name (
  emp_id IN NUMBER,
  mid_name OUT VARCHAR2
) AS
BEGIN
  SELECT middle_name INTO mid_name FROM employees WHERE employee_id = emp_id;
  IF mid_name IS NULL THEN
    mid_name := '—';
  END IF;
END;

19. OUT с UUID генерацией:

CREATE PROCEDURE generate_uuid (uuid OUT VARCHAR2) AS
BEGIN
  SELECT SYS_GUID() INTO uuid FROM dual;
END;

20. OUT для вычисления скидки:

CREATE PROCEDURE discount (
  category IN VARCHAR2,
  result OUT NUMBER
) AS
BEGIN
  result := CASE category
    WHEN 'Gold' THEN 0.2
    WHEN 'Silver' THEN 0.1
    ELSE 0.05
  END;
END;

21. OUT с сообщением статуса:

CREATE PROCEDURE process_data (
  p_id IN NUMBER,
  message OUT VARCHAR2
) AS
BEGIN
  -- логика
  message := 'Данные обработаны';
END;

22. OUT в функции через процедуру:

CREATE OR REPLACE FUNCTION get_emp_email (emp_id NUMBER) RETURN VARCHAR2 IS
  email VARCHAR2(100);
BEGIN
  get_email(emp_id, email);
  RETURN email;
END;

23. OUT с PL/SQL record:

TYPE emp_rec IS RECORD (name VARCHAR2(50), sal NUMBER);
CREATE PROCEDURE fetch_emp (
  emp_id IN NUMBER,
  emp OUT emp_rec
) AS
BEGIN
  SELECT first_name, salary INTO emp.name, emp.sal FROM employees WHERE employee_id = emp_id;
END;

24. OUT с SYS_REFCURSOR и параметром:

CREATE PROCEDURE dept_emps (
  dept_id IN NUMBER,
  cur OUT SYS_REFCURSOR
) AS
BEGIN
  OPEN cur FOR SELECT * FROM employees WHERE department_id = dept_id;
END;

25. OUT с булевым результатом проверки:

CREATE PROCEDURE is_manager (
  emp_id IN NUMBER,
  result OUT BOOLEAN
) AS
  cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO cnt FROM employees WHERE employee_id = emp_id AND manager_id IS NOT NULL;
  result := cnt > 0;
END;

26. OUT с возвратом роли пользователя:

CREATE PROCEDURE get_user_role (
  user_id IN NUMBER,
  role OUT VARCHAR2
) AS
BEGIN
  SELECT user_role INTO role FROM users WHERE id = user_id;
END;

27. OUT с флагом успеха транзакции:

CREATE PROCEDURE make_payment (
  user_id IN NUMBER,
  amount IN NUMBER,
  status OUT VARCHAR2
) AS
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = user_id;
  status := 'Оплата прошла';
END;

28. OUT с деталями продукта:

CREATE PROCEDURE get_product_details (
  prod_id IN NUMBER,
  name OUT VARCHAR2,
  price OUT NUMBER
) AS
BEGIN
  SELECT product_name, price INTO name, price FROM products WHERE id = prod_id;
END;

29. OUT как дата следующего платежа:

CREATE PROCEDURE next_payment_date (
  contract_id IN NUMBER,
  due_date OUT DATE
) AS
BEGIN
  SELECT payment_due INTO due_date FROM contracts WHERE id = contract_id;
END;

30. OUT как количество сообщений:

CREATE PROCEDURE count_messages (
  user_id IN NUMBER,
  msg_count OUT NUMBER
) AS
BEGIN
  SELECT COUNT(*) INTO msg_count FROM messages WHERE recipient_id = user_id;
END;

31. OUT со средним рейтингом:

CREATE PROCEDURE avg_rating (
  product_id IN NUMBER,
  rating OUT NUMBER
) AS
BEGIN
  SELECT AVG(score) INTO rating FROM reviews WHERE product_id = product_id;
END;

32. OUT и динамический SQL:

CREATE PROCEDURE get_count_dynamic (
  table_name IN VARCHAR2,
  total OUT NUMBER
) AS
  stmt VARCHAR2(1000);
BEGIN
  stmt := 'SELECT COUNT(*) FROM ' || table_name;
  EXECUTE IMMEDIATE stmt INTO total;
END;

33. OUT как результат авторизации:

CREATE PROCEDURE login_check (
  username IN VARCHAR2,
  password IN VARCHAR2,
  success OUT BOOLEAN
) AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO v_count FROM users WHERE login = username AND passwd = password;
  success := v_count = 1;
END;

34. OUT как дата последнего визита:

CREATE PROCEDURE last_visit (
  user_id IN NUMBER,
  visit_date OUT DATE
) AS
BEGIN
  SELECT MAX(login_date) INTO visit_date FROM logins WHERE id = user_id;
END;

35. OUT со статусом активности:

CREATE PROCEDURE get_status (
  id IN NUMBER,
  is_active OUT BOOLEAN
) AS
  state VARCHAR2(10);
BEGIN
  SELECT status INTO state FROM resources WHERE resource_id = id;
  is_active := (state = 'ACTIVE');
END;

36. OUT как URL ресурса:

CREATE PROCEDURE get_link (
  resource_id IN NUMBER,
  link OUT VARCHAR2
) AS
BEGIN
  SELECT url INTO link FROM resource_links WHERE id = resource_id;
END;

37. OUT с вычисленным коэффициентом:

CREATE PROCEDURE calc_ratio (
  a IN NUMBER,
  b IN NUMBER,
  result OUT NUMBER
) AS
BEGIN
  IF b = 0 THEN
    result := NULL;
  ELSE
    result := a / b;
  END IF;
END;

38. OUT с поиском минимальной цены:

CREATE PROCEDURE min_price (
  category IN VARCHAR2,
  price OUT NUMBER
) AS
BEGIN
  SELECT MIN(price) INTO price FROM products WHERE category = category;
END;

39. OUT с набором данных по фильтру:

CREATE PROCEDURE get_recent_users (cur OUT SYS_REFCURSOR) AS
BEGIN
  OPEN cur FOR SELECT * FROM users WHERE created_at > SYSDATE - 30;
END;

40. OUT с результатом логического выражения:

CREATE PROCEDURE is_even (
  num IN NUMBER,
  result OUT BOOLEAN
) AS
BEGIN
  result := MOD(num, 2) = 0;
END;

41. OUT с вычислением комиссионных:

CREATE PROCEDURE calc_commission (
  sales IN NUMBER,
  comm OUT NUMBER
) AS
BEGIN
  comm := sales * 0.05;
END;

42. OUT с конкатенацией имени:

CREATE PROCEDURE full_name (
  emp_id IN NUMBER,
  name OUT VARCHAR2
) AS
  fn VARCHAR2(50);
  ln VARCHAR2(50);
BEGIN
  SELECT first_name, last_name INTO fn, ln FROM employees WHERE employee_id = emp_id;
  name := fn || ' ' || ln;
END;

43. OUT с определением статуса:

CREATE PROCEDURE check_status (
  doc_id IN NUMBER,
  status OUT VARCHAR2
) AS
BEGIN
  SELECT current_status INTO status FROM documents WHERE id = doc_id;
END;

44. OUT с расчётом процента выполнения:

CREATE PROCEDURE completion_rate (
  done IN NUMBER,
  total IN NUMBER,
  percent OUT NUMBER
) AS
BEGIN
  percent := ROUND(done / total * 100, 2);
END;

45. OUT со сводкой по категории:

CREATE PROCEDURE category_summary (
  cat IN VARCHAR2,
  summary OUT VARCHAR2
) AS
  cnt NUMBER;
BEGIN
  SELECT COUNT(*) INTO cnt FROM items WHERE category = cat;
  summary := 'Всего позиций: ' || cnt;
END;

46. OUT с анализом времени выполнения:

CREATE PROCEDURE log_time (
  t_start IN TIMESTAMP,
  elapsed OUT INTERVAL DAY TO SECOND
) AS
BEGIN
  elapsed := SYSTIMESTAMP - t_start;
END;

47. OUT с кодом ошибки:

CREATE PROCEDURE risky_operation (
  code OUT VARCHAR2
) AS
BEGIN
  BEGIN
    NULL; -- ошибка
  EXCEPTION
    WHEN OTHERS THEN
      code := SQLERRM;
  END;
END;

48. OUT с переводом валют:

CREATE PROCEDURE convert_usd_to_eur (
  usd IN NUMBER,
  eur OUT NUMBER
) AS
BEGIN
  eur := usd * 0.95;
END;

49. OUT с расчётом налога по типу:

CREATE PROCEDURE tax_calc (
  type IN VARCHAR2,
  base IN NUMBER,
  tax OUT NUMBER
) AS
BEGIN
  IF type = 'A' THEN
    tax := base * 0.15;
  ELSE
    tax := base * 0.2;
  END IF;
END;

50. OUT с формированием ссылки:

CREATE PROCEDURE generate_link (
  id IN NUMBER,
  link OUT VARCHAR2
) AS
BEGIN
  link := 'https://example.com/view/' || id;
END;