Oracle / PostgreSQL SQL Tips

DICAS ORACLE / POSTGRESQL

1. Como renomear uma tabela ?

PostgreSQL

   alter table clientes
   rename to func;

ORACLE

   rename clientes to func;
  
   ou

   alter table clientes
   rename to func;


2. Como adicionar constraint após tabela criada ?

   alter table tabela
   add [constraint] restricao;

   Ex: alter table clientes
       add constraint check (limite >= 0);  


3. Fazer a constraint ser verificada apenas ao comitar transação

ORACLE

   alter table tabela
   add [constraint] restricao deferrable;

   Ex: alter table clientes
       add check (limite >= 0) deferrable;  

   
4. Como criar uma função que retorna 1 tabela ?

PostgreSQL

   create or replace function lista_func(ACidade varchar(100)) returns setof func 
   as '
   declare 
      registro record;
   begin
     for registro in select * from func
                     where cidade = ACidade
     loop
        return next registro;
     end loop;  
   end;
   '
   language 'plpgsql';

   USO:

   select * from lista_func();


5. Excluir um campo de uma tabela

ORACLE

   alter table clientes 
   drop column nome;


6. Adicionar uma constraint unique para um campo

PostgreSQL

  alter table func
  add constraint nome_uk unique(nome);

ORACLE

  alter table func
  add constraint nome_uk unique(nome);

  ou

  alter table func
  modify nome unique;


7. Criar uma tabela com o conteúdo de uma tabela existente

   create table func_temp as
      select * from func;  

   create table func_temp as
      (select * from func);  


8. Adicionar campo com integridade referêncial

   alter table func
   add dept_id integer references dept;

   alter table func
   add dept_id integer references dept(id);


9. Realizar consultas com relações derivadas (select from select)

PostgreSQL

 
   // Retorna a lista de departamentos em que a 
   // quantidade de funcionários é maior que 1
   
   select nome, count
   from(
     select dept.nome, 
            (select count(1) 
             from func
             where func.dept = dept.id)
             from dept
     ) as dept(nome, count)
   where count > 1;


ORACLE

   select nome, count
   from(
      select dept.nome, 
            (select count(1) 
             from func
             where func.dept = dept.id) as count
             from dept
      )
   where count > 1

10. Qual a ordem de precedência na avaliação dos operadores ?

  ESQUERDA->DIREITA 

   1 - operadores de comparação
   2 - not
   3 - and
   4 - or


   OBS.: Por clareza, sempre use parênteses.
 

11. Exemplos de funções single row para caracter


   Largura da string: 

      select length('ORACLE') from dual;

   Localizar string em outra string:
 
      select instr('everton agilar', 'a') from dual;       

   Preencher uma string com zeros a esquerda
  
      select lpad('7891237812', 13, '0') from dual;

   Preencher uma string com zeros a direita
  
      select rpad('7891237812', 13, '0') from dual;


12. Exemplos de funções single row para data/hora

  
   Obter data atual  

      select sysdate from dual;         -- somente ORACLE
      select current_date from dual;
      select current_timestamp from dual; 

   Obter hora atual
      select current_time from dual;    -- Somente PostgreSQL


   Obter a data/hora em formato texto

      select to_char(current_date-1, 'dd/mm/yyyy') from dual;
      select cast(current_date as varchar(15)) from dual; -- ANSI

   Obter parte da data/hora 

      select to_char(current_date, 'MONTH') from dual;
      select to_char(current_date, 'dd') from dual;

      -- ANSI  
      select extract(month from current_date) from dual;
      select extract(day from current_date) from dual;


13. Converter string de dígitos para número

ORACLE
    select to_number('190.83') from dual;

    select cast('190.83' as numeric(12, 2)) from dual;

PostgreSQL

    select cast('190.83' as numeric(12, 2)) from dual;


14. Formatar números

    Formata valor monetário
       select to_char(190.83, '99999D99') from dual;
    
    Formata código de barra com zeros a esquerda
    select to_char(7891231212, '0999999999999') from dual;  


15. Trocar valor nulo por outro valor em uma expressão

ORACLE

    select coalesce(telefone, celular) from cliente;

    select coalesce(null, null, null, 2) from dual;

    select nvl(null, 2) from dual;

    select nvl2(null, 1, 2) from dual;

PostgreSQL 

    select coalesce(telefone, celular) from cliente;

    select coalesce(null, null, null, 2) from dual;


16. Comentar uma tabela ou coluna de tabela

-- comentário para tabela
comment on table clientes is 'Tabela de clientes';


-- comentário para coluna de tabela
comment on column clientes.tipo is 'F=Fisica J=Juridica';


17. Visualizar a estrutura de uma tabela

ORACLE (sqlplus.exe)

   desc nome_tabela

PostgreSQL (psql.exe)

   \d nome_tabela


18. Excluir os dados da tabela de forma rápida

   truncate table nome_tabela;

   OBS.: Este comando não permite desfazer alterações (rollback) e 
         é executado muito mais rápido que um delete.


19. Utilizando SEQUENCES

    ORACLE

       -- criar a sequence
       create sequence seq_func
       start with 100
       increment by 10
       nocache;

       -- gerar sequencia
       select seq_func.nextval from dual;

       -- obter valor atual
       select seq_func.currval from dual;


    PostgreSQL

       -- criar a sequence
       create sequence seq_func
       start with 100
       increment by 10
       nocache;

       -- gerar sequencia
       select nextval('seq_func') from dual;

       -- obter valor atual
       select currval('seq_func') from dual;


   OBS.: A diferença do PostgreSQL para o ORACLE é a 
   utilização de funções nextval() e currval().
   O SQL ANSI é utilizada a expressão NEXT VALUE FOR.

20. Como reconstruir um índice ?

ORACLE

  // refaz o índice na mesma tablespace
  alter index dept_nome_uk rebuild;

  // refaz o índice em outra tablespace. 
  alter index dept_nome_uk rebuild tablespace indx;

20. Como fazer para retornar parte de uma string ?

ORACLE
  select substr('Oracle 10g', 1, 6) from dual;

  retorna Oracle