Структура блока PL/SQL

[Marley]

[ DECLARE ... declaration statements ... ]
BEGIN ... one or more executable statements ...
[ EXCEPTION
... exception handler statements ... ]
END;

DECLARE

l_right_now VARCHAR2(9);

BEGIN

l_right_now := SYSDATE;
DBMS_OUTPUT.PUT_LINE (l_right_now);

EXCEPTION

WHEN VALUE_ERROR
THEN
DBMS_OUTPUT.PUT_LINE('I bet l_right_now is too small '
|| 'for the default date format!');

END;

[Marley]

Именованные блоки


Заголовок процедуры:

PROCEDURE [schema.]name [ ( parameter [, parameter ... ] ) ]
[AUTHID {DEFINER | CURRENT_USER}]

Заголовок функции:

  FUNCTION [schema.]name [ ( parameter [, parameter ... ] ) ]
  RETURN return_datatype
  [AUTHID {DEFINER | CURRENT_USER}]
  [DETERMINISTIC]
  [PARALLEL ENABLE ...]
  [PIPELINED [USING...] | AGGREGATE USING...]

[Marley]

Область

PACKAGE scope_demo
IS
g_global NUMBER;
PROCEDURE set_global (number_in IN NUMBER);
END scope_demo;

PACKAGE BODY scope_demo
IS
PROCEDURE set_global (number_in IN NUMBER)
IS
l_salary NUMBER := 10000;
l_count PLS_INTEGER;

BEGIN

<<local_block>>

DECLARE

l_inner PLS_INTEGER;

BEGIN

SELECT COUNT (*)
INTO set_global.l_count
FROM employees e
WHERE e.department_id = local_block.l_inner
AND e.salary > set_global.l_salary;
END local_block;

scope_demo.g_global := set_global.number_in;

END set_global;
END scope_demo;