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

-- До 23c
create table my_tab(
 id number(38)
);

drop table my_tab;

-- 23c
create table if not exists my_tab(
 id number(38)
);

drop table if exists my_tab;

-- до 23c
select extract(year from t.hire_date) year, count(*)
  from employees t
 group by extract(year from t.hire_date)
 having extract(year from t.hire_date) >= 2014;

-- 23с
select extract(year from t.hire_date) year, count(*)
  from employees t
 group by year
 having year >= 2014;

alter session set group_by_position_enabled = true;

select extract(year from t.hire_date) year, count(*)
  from employees t
 group by 1
 having year >= 2014; 

docker run -d --name oracle23c -p 1524:1521 -e ORACLE_PWD=booble34 -e ORACLE_CHARACTERSET=UTF8 container-registry.oracle.com/database/free:latest

oradb23c =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1524))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREEPDB1)
    )
  )

docker exec -it oracle23c /bin/bash

sqlplus system/booble34@oradb23c  

select * from v$option;

было: select 1 +2  from dual;
стало: select 1+2; 

select trunc(dtime, ‘dd’) day
  from tab
group by day

create table client(
  id number(38),
  is_active boolean
);
insert into client values(1, true);
insert into client values(1, false);

select *
  from hr.departments d
 where d.location_id > 1700 
     and exists (select 1 from hr.employees e where d.department_id = e.department_id);

select * from pg_available_extensions

create or replace function get_note(p_id my_tab.id%type)
returns my_tab.note %type as $$
  select note from my_tab t where t.id = p_id;
$$ language sql;

create user mydb_app password 'apppass';

select my_schema.getNote(1);

grant usage on schema my_schema to mydb_app;

grant select on my_tab to mydb_app;

CREATE OR REPLACE FUNCTION my_schema.my_upper(p_text text)
 RETURNS text
 LANGUAGE plpgsql
 immutable
AS $function$
declare
  v_out text;
begin
    v_out := upper(p_text);    
    raise notice 'In: %. Out: %', p_text, v_out;
  return v_out;
end;
$function$;

select my_upper('my text');