<<outer>>-- it is label
DECLARE
v_name employess.last_name%TYPE;
v_balance NUMBER(7,2);
v_min_balance v_balance%TYPE := 10;
c_sal CONCTANT REAL := 5000;
v_kk NUMBER NOT NULL := 3.3; -- обязательная инициализация
...
BEGIN
...
    DECLARE
      v_balance NUMBER(7,2);
    BEGIN
      ...
      outer.v_balance := 3000;
      IF ... THEN
        GOTO end_label;
      END IF;
    END;
    <<end_label>>
    NULL; -- пустой оператор
...
EXCEPTION
...
END;

• procedure can be shared by multiple programs.
• procedure will be stored in the database as a schema object
• procedure will execute with the provileges of its owner, by default

• A function called from a DML statement cannot read or modify the particular table being modified by the DML statement
• A function called from a query statement or from a parallelized DML statement cannot execute a DML statement or modify the database
• A function called from a query or DML statement cannot end the current transaction, create or rollback a savepoint, or alter the system or session

LOOP
  ...
  EXIT [WHEN condition];
END LOOP;

WHILE condition LOOP
 ...
END LOOP;

FOR counter IN [REVERSE] (...) LOOP
 ...
END LOOP;

FOR i IN 1..3 LOOP
 ...
END LOOP;

FOR viol IN (select * from ci_violations) LOOP 
 v_WT:=cdapi.instantiate_message (viol.facility,viol.code,viol.p0,viol.p1,viol.p2,viol.p3,viol.p4,viol.p5,viol.p6,viol.p7); 
 IF LENGTH(v_W )+LENGTH(v_WT)<3500 THEN 
	v_W := v_W || '<<>>' || v_WT; 
 END IF; 
END LOOP;

<<outer_label>>
LOOP
 ...
 LOOP
  ...
  EXIT outer_label WHEN condition;
 END LOOP;
END LOOP outer_label;

declare 
p_usrname repos_manager.i$sdd_user.login%type; 
p_id repos_manager.i$sdd_log.id%type; 
begin 
select user into p_usrname from dual; 
Dbms_Output.put_line( p_usrname ); 
select NVL( (select max(id) from i$sdd_log), 0 )+1 into p_id from dual; 
Dbms_Output.put_line( p_id ); 
end;

  PROCEDURE client_unlock(vv_ct_code VARCHAR2) AS
    vn_ct_id     NUMBER;
    vv_sql_str   VARCHAR2(2000);
    vv_address   VARCHAR2(2000) := NULL;
    vv_caption   VARCHAR2(2000);
    vv_text      VARCHAR2(2000);
    vv_user_list VARCHAR2(2000);
	
    CURSOR user_list IS 
		SELECT username, ur_mail, us_id 
			FROM mts_user 
				WHERE ct_id = vn_ct_id;
	
  BEGIN
    SELECT ct_id, ct_mail
      INTO vn_ct_id, vv_address
      FROM mts_client
      WHERE ct_code = vv_ct_code;
	  
    UPDATE mts_client SET cs_id = 1 WHERE ct_id = vn_ct_id;
	
    FOR rec IN user_list LOOP
      IF rec.us_id != 0 THEN
        UPDATE mts_user SET us_id = 0 WHERE username = UPPER(rec.username);
        vv_sql_str := 'ALTER USER "' || UPPER(rec.username) || '" ACCOUNT UNLOCK';
        vv_address := vv_address || ';' || rec.ur_mail;
        IF vv_user_list IS NULL THEN
          vv_user_list := UPPER(rec.username);
        ELSE
          vv_user_list := vv_user_list  ||','|| UPPER(rec.username);
        END IF;
        EXECUTE IMMEDIATE vv_sql_str;
      END IF;
    END LOOP;
	
    vv_caption := 'Компания разблокирована';
    vv_text := 'Ваша компания зарегистрирована.';
    send_lock_mail(vv_address, 'ACE', vv_caption, vv_text);
	
    COMMIT;
  END;

create or replace trigger SDD_USER_ACCESS
  before insert or update or delete on i$sdd_user  
  for each row
declare
 p_usrname i$sdd_user.login%type;
 p_cnt integer := 0;
begin
 select user into p_usrname from dual;  

 select count(*) into p_cnt 
 from repos_manager.i$sdd_user us
 inner join repos_manager.i$sdd_userrole ur on ur.userid = us.id
 inner join repos_manager.i$sdd_roleright rt on rt.roleid = ur.roleid
 where  rt.descriptor=23 and rt.right > 0 and us.login = p_usrname;
 if p_cnt = 0 then
  RAISE_APPLICATION_ERROR(-20011,'Не авторизованная операция');
 end if;
 
end SDD_USER_ACCESS;

create or replace trigger SDD_ROLE_AUDIT
  after insert or delete or update on i$sdd_role  
  for each row
declare
  p_usrname i$sdd_user.login%type;  
  p_id i$sdd_log.id%type;
  p_action char(1);
  p_str varchar2(256);
begin
  
  select user into p_usrname from dual;
  select NVL( (select max(id) from i$sdd_log), 0 )+1 into p_id from dual;
  if INSERTING then
      p_action := 'I';
      p_str := 'Добавлена роль ' || :new.name; 
  elsif DELETING then
      p_action := 'D';
      p_str := 'Удалена роль ' || :old.name; 
  else
      p_action := 'U';
      p_str := 'Изменено имя роли с ' || :old.name || ' на ' || :new.name; 
  end if;     
  
  insert into i$sdd_log(id,datetime,admin,typeid,action,desription)   
  values( p_id, sysdate(), p_usrname, 2, p_action, p_str);
  
end SDD_ROLE_AUDIT;

Create or Replace Trigger name 
 BEFORE INSERT OR UPDATE OF salary ON employees
 FOR EACH ROW
 WHEN (new.job_id = 'SA_REP')
begin
...
end;

Create View emp_details as
 select ...;

Create or Replace Trigger new_emp_dept
INSTEAD OF INSERT OR UPDATE OR DELETE ON emp_details
FOR EACH ROW
begin
 if INSERTING then
	...
 elsif DELETING then
	...
 elsif UPDATING('salary') then
	...
 end if;
end;

SQL%ROWCOUNT
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN

DECLARE
 CURSOR cursor_role IS
 select * from I$sdd_Role WHERE Kind = 'R'; 
 v_role I$SDD_Role%ROWTYPE;
BEGIN
 OPEN cursor_role;
 LOOP
     FETCH cursor_role INTO v_role;
     EXIT WHEN cursor_role%NOTFOUND OR cursor_role%NOTFOUND IS NULL;
     Dbms_Output.put_line(v_role.name);
 END LOOP;
 CLOSE cursor_role;
END;

DECLARE
 CURSOR cursor_role IS
 select * from I$sdd_Role WHERE Kind = 'R'; 
 v_role I$SDD_Role%ROWTYPE;
BEGIN
 FOR v_role IN cursor_role LOOP
     Dbms_Output.put_line(v_role.name);
 END LOOP;
END;

BEGIN
 FOR v_role IN (select * from I$sdd_Role WHERE Kind = 'R') LOOP
     Dbms_Output.put_line(v_role.name);
 END LOOP;
END;

DECLARE
 CURSOR cursor_role(p_kind varchar2) IS
 select * from I$sdd_Role WHERE Kind = p_kind; 
 v_role I$SDD_Role%ROWTYPE;
BEGIN
-- OPEN cursor_role('R');
 FOR v_role IN cursor_role('R') LOOP
     Dbms_Output.put_line(v_role.name);
 END LOOP;
 --CLOSE cursor_role;
 FOR v_role IN cursor_role('G') LOOP
     Dbms_Output.put_line(v_role.name);
 END LOOP;
END;

CURSOR cursor_role IS
select ...
for update [of column_reference] [NOWAIT];
BEGIN
  FOR rec_role IN cursor_role LOOP
	UPDATE role SET name = 'kuku'	
	WHERE CURRENT OF cursor_role
  END LOOP;
	
END;

Используется для создания переменной типа курсор

TYPE type_name
IS REF CURSOR [RETURN return_type];

DECLARE
TYPE EmpCur IS REF CURSOR RETURN emp%ROWTYPE;
v_cur EmpCur;
BEGIN
OPEN v_cur FOR select * from emp;
return v_cur;
END;

DECLARE
-- иммитация массива
 TYPE user_role_rec_type IS TABLE OF
  I$SDD_ROLE.name%TYPE
  INDEX BY BINARY_INTEGER;

 v_user_rec_role user_role_rec_type;

 TYPE user_role_type IS TABLE OF
  I$SDD_ROLE%ROWTYPE
  INDEX BY BINARY_INTEGER;

 v_user_role user_role_type;
-- иммитация записи
 v_role I$SDD_ROLE%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('=========Чтение записи===========');
  select * into v_role from  I$SDD_Role where id = 1;
  DBMS_OUTPUT.put_line( v_role.name || ' created on ' || v_role.date_created );

  
  DBMS_OUTPUT.put_line('=========Иммитация массива========');
   select name into v_user_rec_role(1) from  I$SDD_Role where id = 1;
   select name into v_user_rec_role(2) from  I$SDD_Role where id = 2;
   If v_user_rec_role.EXISTS(1) then
   DBMS_OUTPUT.put_line(v_user_rec_role(1));
   end if;
   DBMS_OUTPUT.put_line('Count:' || v_user_rec_role.COUNT);
   DBMS_OUTPUT.put_line('First:' || v_user_rec_role.FIRST);
   DBMS_OUTPUT.put_line('Next:' || v_user_rec_role.NEXT(1));
   v_user_rec_role.DELETE(2);

  DBMS_OUTPUT.put_line('=========Иммитация массива========');
   select * into v_user_role(1) from  I$SDD_Role where id = 1;
   select * into v_user_role(2) from  I$SDD_Role where id = 2;
   DBMS_OUTPUT.put_line(v_user_role(1).name);
   DBMS_OUTPUT.put_line(v_user_role(2).name);

END;

DECLARE
 invalid_role_data EXCEPTION;
 invalid_data EXCEPTION;
 
 -- Ассоциация нового исключения со стандартным
 PRAGMA EXCEPTION_INIT
 (invalid_role_data,-2292);
 
 v_a NUMBER := 10;
 v_b NUMBER := 0;
 v_c NUMBER;
BEGIN
  
  BEGIN
    v_c := v_a/v_b;
    EXCEPTION
      WHEN ZERO_DIVIDE THEN
       Dbms_Output.put_line(SQLCODE);
       RAISE invalid_data;
  END;

  BEGIN
    SELECT id INTO v_c FROM I$SDD_Role WHERE id =0; 
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
       Dbms_Output.put_line(SQLERRM);
       -- Сообщение об ошибке бросается наружу, но EXCEPTION не генерится
       RAISE_APPLICATION_ERROR( -20001, 'Роль не найдена', FALSE);
  END;
  
  EXCEPTION
      WHEN OTHERS THEN
       Dbms_Output.put_line('Unknown error:(' || SQLCODE || ')' || SQLERRM);
      
END;

CREATE OR REPLACE PROCEDURE add_user (p_id OUT
I$SDD_USER.id%TYPE, p_name IN
 I$SDD_USER.login%TYPE DEFAULT 'new user') IS PROCEDURE
log_exe
  IS BEGIN
  ...
  END
    log_exe;
  BEGIN p_id
	
:=
  User.NEXTVAL; INSERT INTO
  I$SDD_User(id,name) VALUES(p_id,p_name); If SQL%ROWCOUNT
  = 0 Then Raise_Application_error( -20001,
	'User append error'); else log_exe();
  COMMIT;
	end
	if;
  BEGIN --
  
on-time-only
  procedure ... END
  add_user; 
add_user( p_id


= > v_id, p_name = > 'robbit' );

CREATE OR REPLACE FUNCTION tax
(p_value IN NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN (p_value * 0.18);
END tax;

CREATE OR REPLACE PACKAGE pack_name
IS
 g_comm NUMBER := 0.10
 mile_to_kilo CONSTANT NUMBER := 1.6093;

 PROCEDURE reset_comm
 (p_comm IN NUMBER); 


END pack_name;



CREATE OR REPLACE PACKAGE BODY pack_name
IS
 l_comm NUMBER := 0.12

 FUNCTION test_comm (p_comm IN NUMBER)
 RETURN BOOLEAN
 IS
 BEGIN
   RETURN (p_comm > 300);
 END test_comm;

 PROCEDURE reset_comm (p_comm IN NUMBER)
 IS
 BEGIN
   If test_comm(p_comm) Then
	   g_comm = p_comm;	
   end if;
 END reset_comm;

END pack_name;

CREATE OR REPLACE PROCEDURE delete_all
( p_tab_name IN VARCHAR2, p_rows_del OUT NUMBER)
IS
 cursor_name INTEGER;
BEGIN
 cursor_name := DBMS_SQL.OPEN_CURSOR;
 DBMS_SQL.PARSE( cursor_name, 'DELETE FROM ' || p_tab_name, DBMS_SQL.NATIVE );
 p_rows_del := DBMS_SQL.EXECUTE( cursor_name );
 DBMS_SQL.CLOSE_CURSOR( cursor_name );
 
END delete_all;

declare 
 p_vid int;
 sql_stmt  VARCHAR2(200);
begin
  p_vid := 1;
  sql_stmt := 'delete log_import where vid=:1';
  EXECUTE IMMEDIATE sql_stmt USING IN p_vid;
 EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('Invalid Column: ');

end;

declare
     h_cursor integer;
     v_res int;
begin
  h_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(h_cursor,'delete log_import where vid=:VID', dbms_sql.native); 
  dbms_sql.bind_variable(h_cursor, 'VID', 1);
  v_res := dbms_sql.execute(h_cursor);
  dbms_sql.close_cursor(h_cursor);
end;

TYPE name
IS TABLE OF type 
    [NOT NULL]
[INDEX BY BINARY_INTEGER];

DECLARE
TYPE t_EmpTab IS TABLE OF emp%ROWTYPE
INDEX BY BINARY_INTEGER;
v_emp t_EmpTab;
BEGIN
 select * into v_emp(7369) from emp where no=7369;
END;

TYPE name
IS VARRAY|VARYING ARRAY(size)
OF type 
  
  [NOT NULL];

DECLARE
TYPE ListPrice IS VARRAY(100) OF NUMBER;
v_prc ListPrice;
BEGIN
 v_prc := ListPrice(15.0,18.33,...);
END;

• EXISTS(n) - существует n-ый элемент
• COUNT - кол-во элементов
• LIMIT - максимальное кол-во элементов
• FIRST, LAST - индекс первого и последнего элементов
• PRIOR(n), NEXT(n) - индекс предыдущего, следующего элемента
• EXTEND(n[,i]) - увеличивает коллекцию на n элементов, присваивая им значение i-го
• TRIM(n) - удаляет n - последних элементов
• DELETE([m[,n]]) - удаляет все, m-ый или от m..n элементы

• COLLECTION_IS_NULL - коллекция не инициализирована
• NO_DATA_FOUND - индекс ссылает на не существующий элемент
• SUBSCRIPT_BEYOND_COUNT - индекс больше максимального
• SUBSCRIPT_OUTSIDE_LIMIT - индекс за пределами коллекции
• VALUE_ERROR - индекс NULL или не целое число

CREATE TYPE ListPrc 
AS TABLE OF NUMBER(12,2);
CREATE TYPE Summary AS OBJECT (
 id_num INTEGER(4),
 name VARCHAR2(35),
 price ListPrc);
CREATE TABLE Product (
 prod_id NUMBER;
 summary Summary);