Oracle Developer👨🏻‍💻. Страница 4

procedure fail_payment(p_payment_id payment.payment_id%type
                      ,p_reason   payment.status_change_reason%type)
is
  v_status               payment.status%type;
  e_status_is_not_active exception;
begin
 
  select status
    into v_status
    from payment
   where payment_id = p_payment_id;
 
  if v_status != c_active then
    raise e_status_is_not_active;
  end if;
 
  update payment
     set status               = c_error
        ,status_change_reason = p_reason
   where payment_id = p_payment_id;
 
exception
  when e_status_is_not_active then
     raise_application_error(c_err_code_status_is_not_active, 'Платеж не активный');
                   
  when no_data_found then
     raise_application_error(c_err_code_payment_is_not_found, 'Платеж не найден');    
end;
/

fetch c_emailaddress into v_brokernumber, v_emailaddress;

exception 
  when others then
     if c_emailaddress%isopen then
       close c_emailaddress;
     end if;
     raise;
end;

create or replace procedure change_domain(p_olddomainname varchar2
                                 ,p_newdomainname varchar2) is
  update broker
     set email_address = ...
   where lower(email_address) like '%@' || lower(p_olddomainname);
 
end;

— справочник типов бизнес-полей
create table client_data_field(
 field_id   number(10) not null,
 field_name varchar2(100 char) not null
);

insert into client_data_field values(1, 'FIRST_NAME');
insert into client_data_field values(2, 'LAST_NAME');
insert into client_data_field values(3, 'MIDDLE_NAME');
insert into client_data_field values(4, 'BIRTH_DAY');

create table client_data_v(
  client_id   number(38) not null,
  field_id    number(10) not null,
  field_value varchar2(1000 char) not null   
);

insert into client_data_v values(777, 1, 'Иван');
insert into client_data_v values(777, 2, 'Иванов');
insert into client_data_v values(777, 4, to_char(date'1984-01-21','dd.mm.yyyy'));

select ... 
  from client_data bd
  join client_data ln on ln.client_id = bd.client_id and ln.field_id = 1 and ln.field_value = ...
 where bd.field_id = 4
   and bd.field_value = ...;

create table client(
  client_id number(38) not null,
  is_active number(1)  not null,
  status    number(10) not null,
  create_dtime date not null
);

create table tab1(
  id number(38)
); 

declare
  v_cnt number(30);
begin
  insert into tab1 values (1);
    
  v_cnt := 1/0;
  
  dbms_output.put_line('1');
  
exception 
  when others then
    select count(*) into v_cnt from tab1;
    dbms_output.put_line(v_cnt);
end;
/