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

begin
  for r in (select first_name || last_name name
              from employees
             order by salary desc) loop
    dbms_output.put_line(r.name);
  end loop;
end;
/

declare
  type name_salary_rt is record(
     name   varchar2(1000)
    ,salary number);

  type name_salary_aat is table of name_salary_rt index by pls_integer;

  l_employees name_salary_aat;
begin
  execute immediate q'[select first_name || ' ' || last_name, salary
           from employees
          order by salary desc]' bulk collect
    into l_employees;

  for indx in 1 .. l_employees.count loop
    dbms_output.put_line(l_employees(indx).name);
  end loop;
end;
/

function get_text_new(p_id t1.id%type)
return t1.text%type
is
  v_text t1.text%type;
begin
  select max(t1.text)
    into v_text
    from t1 where t1.id = p_id;
   return v_text;
end;
/

create or replace function get_text(p_id t1.id%type)
return varchar2
is
  type my_type_cur is ref cursor;
  v_curs my_type_cur;
  v_text t1.text%type;
begin
  open v_curs for
    select text from t1 where id = p_id;

  fetch v_curs into v_text;

  close v_curs;

  return v_text;
end;
/

create or replace trigger my_tab_seq_tg
   before insert on my_tab
   for each row
begin
   if inserting then
      if :new.seq_id is null then
         select some_seq.nextval into :new.seq_id from dual;
      end if;
    end if;
end;

create or replace trigger my_tab_seq_tg
  before insert on my_tab
  for each row
  when (new.seq_id is null)
begin
  :new.seq_id := some_seq.nextval;
end;
/

select * from tab1 where col1 = … or col2 = … 

select * from tab1 where col1 = …
union all
select * from tab1 where col2 = … and col1 not in ...

alter session set events '10053 trace name context forever';
alter session set tracefile_identifier='PLAN_TRC_EXAMPLE1';

select t.first_name
  from hr.employees t
  join hr.departments d on d.department_id = t.department_id
 where t.first_name like 'Alex%';

alter session set events '10053 trace name context off';

select t.first_name
  from hr.employees t
  join hr.departments d on d.department_id = t.department_id
 where t.first_name like 'Alex%';