tecnologia | consultoria | banco de dados

Banco de Dados - Classificando Dados (ORDER BY)

A cláusula ORDER BY é responsável pela transformação da saída da consulta em dados classificados. Esta cláusula é sempre a última cláusula em uma declaração SELECT.

Classificação Ascendente (ASC):
   SELECT * FROM tb_funcionarios ORDER BY nm_funcionario ASC;
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario;
Cláusulas específicas do Oracle:
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario ASC NULLS FIRST;
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario ASC NULLS LAST;

Classificação Decrescente (DESC):
   SELECT * FROM tb_funcionarios ORDER BY nm_funcionario DESC;
Cláusulas específicas do Oracle:
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario DESC NULLS FIRST;
SELECT * FROM tb_funcionarios ORDER BY nm_funcionario DESC NULLS LAST;
Data publicação: 21:20 25/12/2019

Banco de Dados - Restringindo a Seleção de Dados (WHERE)

A cláusula WHERE estende a declaração SELECT ao fornecer o idioma às linhas restringidas retornadas, baseadas em uma ou mais condições.

Exemplos:
SELECT * FROM tb_funcionarios WHERE nm_funcionario = 'LEANDRO';
SELECT * FROM tb_funcionarios WHERE nm_funcionario IN('LEANDRO', 'Leandro');
SELECT * FROM tb_funcionarios WHERE nm_funcionario LIKE 'Leandro%';
SELECT * FROM tb_funcionarios WHERE nm_funcionario IS NULL;

Ponto de Atenção: O banco de dados Oracle é "Sensível a Maiúsculas" (Case Sensitive), portanto ao filtrar uma coluna desconhecida pode ser necessária a utilização de fórmulas para transformar o texto em maiúsculas (este será tema abordado no próximo tópico).
Data publicação: 21:54 01/10/2019

Banco de Dados - Seleção de Dados (SELECT)

A declaração SELECT é um mecanismo flexível para recuperar informações de uma tabela de banco dados. Esta declaração nunca altera o as informações armazenadas no banco de dados.

Exemplos:
SELECT * FROM tb_funcionarios;
SELECT DISTINCT nm_funcionario FROM tb_funcionarios;

Oracle:
SELECT 'Olá mundo!' FROM dual;

Microsoft SQL Server:
SELECT 'Olá mundo!';

Data publicação: 19:29 29/09/2019

Banco de Dados - Tipos de Armazenamento de Dados (Data Types)

Para armazenar os dados em um banco de dados é necessário ter conhecimento prévio sobre o tipo de dados que serão inseridos em cada coluna das tabelas. Abaixo será listado os principais tipos de dados.

Oracle:
Texto:
CHAR
VARCHAR2
Número:
NUMBER
FLOAT
Data:
DATE
INTERVAL

Microsoft SQL Server:
Texto:
CHAR
VARCHAR
Número:
INT
BIGINT
FLOAT
MONEY
Data:
DATETIME
SMALLDATETIME
Data publicação: 19:27 29/09/2019

SQL Server - Importar tabelas utilizando BCP

Nesta postagem irei mostrar como importar tabelas utilizando o utilitário BCP (Bulk Copy Program) do banco de dados Microsoft SQL Server.

Sintaxe do utilitário BCP:
bcp {table|view|"query"}
{out|queryout|in|format}
{data_file|nul}
{[optional_argument]...}

Parâmetros para indicar importação ou exportação:
- out: Indica que o utilitário irá exportar uma tabela ou view para um arquivo.
- queryout: Indica que o comando irá exportar os dados de uma query para um arquivo.
- in: Indica que o utilitário irá importar os dados de um arquivo para uma tabela.

Para exemplificar a exportação, foi utilizado um arquivo batch (importa_tb_alunos.bat) com o código abaixo.
bcp estudos..tb_alunos in "C:\EXPORT\tb_alunos.txt" -T -c

Explicando brevemente o código acima, "estudos..tb_alunos" é o nome da database seguido do nome da tabela para importação, "in" informa que o arquivo será importado na tabela informada anteriormente, "-T" é o parâmetro para Windows Authentication (utiliza o usuário e senha do Windows para acessar o banco de dados), "-c" indica que as colunas serão exportadas como caracteres.

Parâmetros:
-S     => Servidor
-F2 => Primeira linha a importar (neste exemplo, segunda linha)
-L100 => Última linha a importar (neste exemplo, centésima linha)
-U => Usuário do banco de dados
-P => Senha do usuário do banco de dados
-c => Tipo de dados caractere (delimitadores padrões: linha "ENTER" e coluna "TAB")
-w => Tipo de dados unicode (delimitadores padrões: linha "ENTER" e coluna "TAB")
-t"\t" => Delimitador de coluna (neste exemplo, "TAB")
-r"\n" => Delimitador de linha (neste exemplo, "ENTER")
-T => Windows Authentication
-e => Arquivo para armazenamento dos erros de importação
-o => Arquivo para armazenamento do output após a execução do BCP
-f => Format file cujo define a estrutura da tabela

Publicações relacionadas:
- SQL Server - Exportar tabelas utilizando BCP.
Data publicação: 22:13 16/09/2018

Oracle - Criar tabela sub-particionada

Nesta publicação irei mostrar de forma rápida, através de exemplos, como criar tabelas sub-particionadas no Oracle.

Criar tabela com partição e subpartição:

CREATE TABLE tb_clientes
(
id_cliente NUMBER,
nm_cliente VARCHAR2(200),
ano_mes_cadastro VARCHAR2(6) DEFAULT TO_CHAR(SYSDATE,'RRRRMM'),
nr_grupo_cliente NUMBER
)
PARTITION BY RANGE (ano_mes_cadastro)
SUBPARTITION BY LIST (nr_grupo_cliente)
(
PARTITION P_201809 VALUES LESS THAN (TO_DATE('01/10/2018','DD/MM/YYYY'))
(
SUBPARTITION P_201809_S_1 VALUES (1)
)
);

Adicionar uma nova subpartição em uma partição já existente:
ALTER TABLE tb_clientes MODIFY PARTITION P_201809 ADD SUBPARTITION P_201809_S_2 VALUES (2);

Adicionar uma nova partição com subpartição:
ALTER TABLE tb_clientes ADD PARTITION P_201810 VALUES LESS THAN (TO_DATE('01/11/2018','DD/MM/YYYY'))
(
SUBPARTITION P_201810_S_1 VALUES (1)
);

Publicações relacionadas:
- Oracle - Criar tabela particionada;
- Oracle - Criar tabela com compressão.
Data publicação: 21:55 16/09/2018

SQL Server - Exportar tabelas utilizando BCP

Nesta postagem irei mostrar como exportar tabelas utilizando o utilitário BCP (Bulk Copy Program) do banco de dados Microsoft SQL Server.

Sintaxe do utilitário BCP:
bcp {table|view|"query"}
{out|queryout|in|format}
{data_file|nul}
{[optional_argument]...}

Parâmetros para indicar importação ou exportação:
- out: Indica que o utilitário irá exportar uma tabela ou view para um arquivo.
- queryout: Indica que o comando irá exportar os dados de uma query para um arquivo.
- in: Indica que o utilitário irá importar os dados de um arquivo para uma tabela.

Para exemplificar a exportação, foi utilizado um arquivo batch (exporta_tb_alunos.bat) com o código abaixo.
bcp estudos..tb_alunos out "C:\EXPORT\tb_alunos.txt" -T -c

Explicando brevemente o código acima, "estudos..tb_alunos" é o nome da database seguido do nome da tabela a ser exportada, "out" informa que a tabela será exportada no arquivo informado, "-T" é o parâmetro para Windows Authentication (utiliza o usuário e senha do Windows para acessar o banco de dados), "-c" indica que as colunas serão exportadas como caracteres.

Abaixo será mostrado outra forma de exportar a mesma tabela utilizando query.
bcp "select * from estudos..tb_alunos" queryout "C:\EXPORT\tb_alunos.txt" -T -c

Parâmetros:
-S     => Servidor
-F2 => Primeira linha a importar (neste exemplo, segunda linha)
-L100 => Última linha a importar (neste exemplo, centésima linha)
-U => Usuário do banco de dados
-P => Senha do usuário do banco de dados
-c => Tipo de dados caractere (delimitadores padrões: linha "ENTER" e coluna "TAB")
-w => Tipo de dados unicode (delimitadores padrões: linha "ENTER" e coluna "TAB")
-t"\t" => Delimitador de coluna (neste exemplo, "TAB")
-r"\n" => Delimitador de linha (neste exemplo, "ENTER")
-T => Windows Authentication
-e => Arquivo para armazenamento dos erros de importação
-o => Arquivo para armazenamento do output após a execução do BCP
-f => Format file cujo define a estrutura da tabela

Publicações relacionadas:
- SQL Server - Importar tabelas utilizando BCP.
Data publicação: 22:01 12/07/2018

Oracle - Tratamento de erros e exceções

Irei mostrar nesta publicação duas formas de tratar erros na aplicação ou forçar exceções. Para isso será utilizando a procedure RAISE_APPLICATION_ERROR ou a cláusula RAISE.

Para utilizar a procedure RAISE_APPLICATION_ERROR é necessário informar dois parâmetros. O primeiro parâmetro é um número qualquer entre -20,000 e -20,999. O segundo parâmetro é a mensagem a ser mostrada quando a procedure for executada.

Para utilizar a cláusula RAISE é necessário declarar uma variável do tipo EXCEPTION, adicionar os tratamentos EXCEPTION no final do bloco e acionar a cláusula RAISE para acionar o erro desejada.

Para habilitar a o OUTPUT em alguns softwares como, por exemplo, SQL Plus e Oracle SQL Developer é necessário adicionar os comandos SET SERVEROUTPUT ON antes do bloco (BEGIN / END).

Segue abaixo exemplos de utilização.

--EXEMPLO RAISE_APPLICATION_ERROR
DECLARE
V_NUMERO NUMBER := 99;
BEGIN
IF (V_NUMERO >= 99) THEN
RAISE_APPLICATION_ERROR (-20100, 'LIMITE MÁXIMO ATINGIDO! INFORMAR UM NÚMERO INFERIOR A 99!');
END IF;
END;

--EXEMPLO EXCEPTION
DECLARE
V_NUMERO NUMBER := 99;
EXC_LIMITE EXCEPTION;
BEGIN
IF (V_NUMERO >= 99) THEN
RAISE EXC_LIMITE;
END IF;
EXCEPTION
WHEN EXC_LIMITE THEN
DBMS_OUTPUT.PUT_LINE('LIMITE MÁXIMO ATINGIDO! INFORMAR UM NÚMERO INFERIOR A 99!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERRO NA EXECUÇÃO!');
END;
Data publicação: 21:30 15/06/2017

Oracle - Cláusula GOTO

No Oracle, utilizando PL/SQL, é possível avançar para uma determinada parte do código com o uso da cláusula GOTO.

Exemplo:
BEGIN
<<PARTE_1>>
DBMS_OUTPUT.PUT_LINE('EXECUTANDO PARTE 1');
GOTO PARTE_3;
<<PARTE_2>>
DBMS_OUTPUT.PUT_LINE('EXECUTANDO PARTE 2');
<<PARTE_3>>
DBMS_OUTPUT.PUT_LINE('EXECUTANDO PARTE 3');
END;

Executando o exemplo acima, teremos a seguinte exibição no OUTPUT:
   EXECUTANDO PARTE 1
EXECUTANDO PARTE 2

Data publicação: 23:21 10/01/2017

Oracle - Espaço utilizado por objetos

Abaixo segue código para consulta do espaço utilizado por objetos de um schema específico em Banco de Dados Oracle.


SELECT
l.segment_name AS object_name
,SUM(l.bytes) /1024/1024/1024 AS gbytes
FROM dba_segments L
WHERE l.owner = 'HR'
GROUP BY l.segment_name
ORDER BY gbytes DESC;
Data publicação: 22:48 11/09/2016

Oracle - Consultar objetos com Lock

Segue abaixo código para consulta de objetos com "lock" em Banco de Dados Oracle.


SELECT
o.owner,
l.oracle_username,
l.os_user_name,
l.session_id||','||v.serial# AS sid_serial,
l.oracle_username AS ora_user,
o.object_name,
o.object_type,
l.oracle_username,
DECODE(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR(l.locked_mode)
) AS lock_mode,
o.status,
v.service_name,
TO_CHAR(o.last_ddl_time,'DD.MM.YY HH24:MI') AS last_ddl
o.*,
l.*,
v.*
FROM
dba_objects o,
gv$locked_object l,
v$session v
WHERE
o.object_id = l.object_id
AND l.SESSION_ID = v.sid
ORDER BY
l.oracle_username,
l.os_user_name;
Data publicação: 22:59 04/09/2016

Oracle - Monitorar CREATE / DROP de objetos

Nesta publicação será mostrado um exemplo de Trigger para monitoramento de objetos criados ou deletados do schema em um banco de dados Oracle.

--TABELA DE LOG
CREATE TABLE tb_log_create
(
dt_execucao DATE
,nm_database VARCHAR2(30)
,nm_schema VARCHAR2(30)
,nm_objeto VARCHAR2(30)
,tp_objeto VARCHAR2(30)
,tp_evento VARCHAR2(30)
,nm_usuario_rede VARCHAR2(80)
,nm_equipamento VARCHAR2(80)
)
NOLOGGING;

--TRIGGER
CREATE OR REPLACE TRIGGER trg_log_create
AFTER CREATE
OR DROP
--OR ALTER
--OR TRUNCATE
ON SCHEMA
DECLARE
v_linha hr.tb_log_create%ROWTYPE;
v_dummy INTEGER;
BEGIN
-- DATA DO EVENTO
v_linha.dt_execucao := SYSDATE;
-- BANCO DE DADOS
v_linha.nm_database := upper(sys_context('userenv', 'DB_NAME'));
-- SCHEMA
v_linha.nm_schema := sys_context('userenv', 'CURRENT_USER');
-- NOME DO OBJETO
v_linha.nm_objeto := ora_dict_obj_name;
-- TIPO DE OBJETO
v_linha.tp_objeto := ora_dict_obj_type;
-- TIPO DE EVENTO
v_linha.tp_evento := ora_sysevent;
-- USUARIO DE REDE QUE EXECUTOU O COMANDO
v_linha.nm_usuario_rede := upper(sys_context('userenv', 'OS_USER'));
-- HOSTNAME
v_linha.nm_equipamento := sys_context('userenv', 'HOST');

SELECT COUNT(1)
INTO v_dummy
FROM tb_log_create l
WHERE TRUNC(dt_execucao,'MONTH') = TRUNC(SYSDATE, 'MONTH')
AND l.nm_objeto = v_linha.nm_objeto
AND l.nm_usuario_rede = v_linha.nm_usuario_rede
AND l.tp_objeto = v_linha.tp_evento;
IF v_dummy = 0 THEN
INSERT INTO tb_log_create
VALUES
(
v_linha.dt_execucao
,v_linha.nm_database
,v_linha.nm_schema
,v_linha.nm_objeto
,v_linha.tp_objeto
,v_linha.tp_evento
,v_linha.nm_usuario_rede
,v_linha.nm_equipamento
);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END trg_log_create;
Data publicação: 21:59 27/07/2016

Oracle - Job Scheduler

Nesta publicação será mostrado uma forma para agendamento de execuções automáticas no banco de dados Oracle.

No Oracle, a partir da versão 10G, a package DBMS_JOBS foi depreciada e disponibilizada a package DBMS_SCHEDULER.

Irei mostrar apenas a nova forma de execução, a qual permite consultar históricos de execução e, caso necessário, interromper a execução sem a necessidade de possuir privilégios de DBA (Database Administrator).


Tipos de frequência:
YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, SECONDLY

Tipos de intervalo:
O valor padrão é 1 e o valor máximo é 999

--CRIANDO UM JOB

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME => 'JB_MEU_JOB'
,JOB_TYPE => 'STORED_PROCEDURE'
,JOB_ACTION => 'SCHEMA.PRC_MINHA_PROCEDURE'
,START_DATE => ?FROM_TZ(SYSTIMESTAMP,'-03:00')
,REPEAT_INTERVAL => 'FREQ=SECONDLY;INTERVAL=30'
,AUTO_DROP => FALSE
,COMMENTS => 'JOB QUE EXECUTA A PROCEDURE SCHEMA.PRC_MINHA_PROCEDURE A CADA 30 SEGUNDOS'
,ENABLED => TRUE
);
END;
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME => 'JB_MEU_JOB'
,JOB_TYPE => 'PLSQL_BLOCK'
,JOB_ACTION => '
/*
PROPÓSITO: JOB DE EXEMPLO COM BLOCO PL/SQL
REVISÕES:
Ver Data Autor Descrição
--------- ---------- ------------------ ---------------------------------
1.0 11/05/2016 Leandro Sacramento 1) Criação

*/
DECLARE
V_COUNT NUMBER := NULL;
BEGIN
SELECT COUNT(1)
INTO V_COUNT
FROM VW_MINHA_VIEW
WHERE DS_COLUNA IS NULL;
IF V_COUNT > 0 THEN
PRC_MINHA_PROCEDURE();
END IF;
'
,START_DATE => FROM_TZ(SYSTIMESTAMP,'-03:00')
,REPEAT_INTERVAL => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=8,9,10,11,12,13,14,15; BYMINUTE=0; BYSECOND=0'
,AUTO_DROP => FALSE
,COMMENTS => 'JOB QUE EXECUTA A PROCEDURE SCHEMA.PRC_MINHA_PROCEDURE A CADA 30 SEGUNDOS'
,ENABLED => TRUE
);
END;

--EXECUTAR O JOB

BEGIN
DBMS_SCHEDULER.RUN_JOB('SCHEMA.JB_MEU_JOB',TRUE);
END;

--PARAR EXECUÇÃO DO JOB

BEGIN
DBMS_SCHEDULER.STOP_JOB
(
job_name => 'SCHEMA.JB_MEU_JOB',
force => TRUE
);
END;

--DESABILITAR JOB

BEGIN
DBMS_SCHEDULER.DISABLE(NAME => 'SCHEMA.JB_MEU_JOB', force => TRUE);
END;

--HABILITAR JOB

BEGIN
DBMS_SCHEDULER.ENABLE(NAME => 'SCHEMA.JB_MEU_JOB');
END;

--ALTERANDO UM SCHEDULE

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE
(
NAME => 'SCHEMA.JB_MEU_JOB',
ATTRIBUTE => 'REPEAT_INTERVAL',
VALUE => 'FREQ=DAILY; BYHOUR=15; BYMINUTE=30'
);
END;

--EXCLUINDO UM JOB

BEGIN
DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'SCHEMA.JB_MEU_JOB', FORCE => TRUE);
END;

--LIMPAR LOG MAIORES QUE 10 DIAS

BEGIN
DBMS_SCHEDLER.PURGE_LOG(log_history => 10, job_name => 'SCHEMA.JB_MEU_JOB');
END;

--HISTÓRICO DE EXECUÇÕES DOS JOBS

SELECT * FROM USER_SCHEDULER_JOB_RUN_DETAILS;

--MOSTRAR TODOS OS JOBS

SELECT JOB_NAME, STATE, LAST_START_DATE
FROM USER_SCHEDULER_JOBS;
Data publicação: 21:13 11/05/2016

Oracle - Estruturas de Controle

1) Instrução IF

IF <CONDITION> THEN
STATEMENT1;
STATEMENT2;
...
[ELSIF <CONDITION> THEN
STATEMENT1;
...]
[ELSE
STATEMENT1;
...]
END IF;

2) Instrução CASE

CASE <SELECTOR>
WHEN <EXPRESSION1> THEN <STATEMENT1>
WHEN <EXPRESSION2> THEN <STATEMENT2>
WHEN <EXPRESSION3> THEN <STATEMENT3>
...
[ELSE STATEMENT N]
END;

3) Instrução LOOP

LOOP
<STATEMENT1>;
<STATEMENT2>;
<STATEMENT3>;
...
EXIT [WHEN <CONDITION>];
END LOOP;

WHILE <CONDITION> LOOP
<STATEMENT1>;
<STATEMENT2>;
...
END LOOP;
FOR counter IN [REVERSE] lower..upper LOOP
<STATEMENT1>;
<STATEMENT2>;
<STATEMENT3>;
...
END LOOP;

DECLARE
lower NUMBER := 1;
upper NUMBER := 100;
BEGIN
FOR i IN lower..upper LOOP
DBMS_OUTPUT.PUTLINE('Counter: ' || TO_CHAR(i));
END LOOP;
END;

BEGIN
<<loop1>>
FOR i IN 1..5 LOOP
<<loop2>>
FOR j IN 1..10 LOOP
DBMS_OUTPUT.PUTLINE('i X j ' || TO_CHAR(i * j));
END LOOP loop2;
END LOOP loop1;
END;
Data publicação: 22:28 09/03/2016

Oracle - Exportar dados para Excel (Spool csv)

Excel.bat:
Arquivo com a configuração de conexão com o banco de dados e o arquivo SQL a ser executado.

SQLPLUS schema@servidor/senha @SQL/Excel.sql

Excel.sql:
Arquivo com as configurações do spool e local para geração do arquivo CSV.

SET UNDERLINE OFF 
SET TERM OFF
SET ECHO OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET HEADING OFF
SET FEEDBACK OFF
SET PAUSE OFF
SET PAGESIZE 0
SET LINESIZE 2000
SET VERIFY OFF
SET COLSEP ';'
SPOOL RETORNO/EXCEL.CSV
SELECT
A.EMPLOYEE_ID
,A.FIRST_NAME
,A.LAST_NAME
,A.EMAIL
,A.PHONE_NUMBER
,TO_CHAR(A.HIRE_DATE,'DD/MM/RRRR') AS HIRE_DATE
,A.JOB_ID
,A.SALARY
,TO_CHAR(A.COMMISSION_PCT, 'FM999G999G990D00', 'NLS_NUMERIC_CHARACTERS=,.') AS COMMISSION_PCT
,A.MANAGER_ID
,A.DEPARTMENT_ID
FROM EMPLOYEES A;
SPOOL OFF
EXIT;
Data publicação: 23:53 24/11/2015

Oracle - Expressões regulares

-- Manter apenas números na coluna

SELECT
REGEXP_REPLACE('REGISTRO 123.456.789-10', '[^0-9]+', '')
FROM dual;
-- Resultado: 12345678910
SELECT
REGEXP_REPLACE('ABC12345DEF', '[^[:digit:]]+', '')
FROM dual;
-- Resultado: 12345
SELECT
REGEXP_REPLACE('1A2B3C4D', '\D+', '')
FROM dual;
-- Resultado: 1234

-- Selecionar linhas numéricas

SELECT
e.col1
FROM tb_leandro e
WHERE REGEXP_LIKE(e.col1 , '^[[:digit:]]+$');

-- Selecionar linhas com letras e espaços

SELECT
e.col1
FROM tb_leandro e
WHERE REGEXP_LIKE(e.col1 , '^[[:alpha:][:space:]]+);

-- Contar espaço no início da string antes de algum carácter

SELECT
NVL(LENGTH(REGEXP_SUBSTR(' Four Spaces', '^[[:space:]]+',1,1)),0) AS Result
FROM dual;
-- Resultado: 4
SELECT
REGEXP_INSTR(' Four Spaces', '\S',1,1) AS Result
FROM dual;
-- Resultado: 4
SELECT
NVL(LENGTH(REGEXP_SUBSTR(' Two Spaces', '^[[:space:]]+',1,1)),0) AS Result
FROM dual;
--Resultado: 2

-- Extrair string que possui | como delimitador

SELECT
REGEXP_SUBSTR('One|Two|Three|Four|Five', '[^|]+',1,1) AS Result
FROM dual;
-- Resultado: One
SELECT
REGEXP_SUBSTR('One|Two|Three|Four|Five', '[^|]+',1,4) AS Result
FROM dual;
-- Resultado: Four

-- Extrair primeiro e último valor da string

SELECT
REGEXP_REPLACE('Leandro Sacramento de Paula', ' .+ ', ' ')
From dual;
-- Resultado: Leandro Paula


CARÁCTER FUNÇÃO
\ Carácter de escape
^ Posição inicial da linha (string)
$ Posição final da linha (string)
[ ] Corresponde ao caracteres listados entre os colchetes
[^ ] Não corresponde aos caracteres listados entre os colchetes
( ) Sub-expressão ou grupo. Pode ser uma string ou uma expressão complexa
. Qualquer caractere ("coringa")
+ Uma ou mais ocorrências da sub-expressão precedente
? Zero ou uma ocorrência da sub-expressão precedente
* Zero ou mais ocorrências da sub-expressão precedente
{n} Número de caracteres exatas da sub-expressão precedente
{m,} Número de ocorrências de pelo menos m vezes da sub-expressão precedente
{m,n} Número de ocorrências de pelo menos m vezes, mas não mais que n vezes da sub-expressão precedente
| Alternativa de padrão de busca, por exemplo ('leandro'|'sacramento')
Data publicação: 01:27 13/11/2015

Oracle - Criar tabela particionada

CREATE TABLE tb_clientes
(
id_cliente NUMBER,
nm_cliente VARCHAR2(200),
ano_mes_cadastro VARCHAR2(6) DEFAULT TO_CHAR(SYSDATE,'RRRRMM')
)
PARTITION BY RANGE (ano_mes_cadastro)
(
PARTITION P_198612 VALUES LESS THAN (198701)
);

Criar partição baseado em data:

CREATE TABLE tb_teste
(
data DATE
)
PARTITION BY RANGE(data)
(
PARTITION P_201312 VALUES LESS THAN (TO_DATE('01/01/2014','dd/mm/yyyy'))
);

Criar tabela com particionamento automático:

CREATE TABLE POS_DATA
(
START_DATE DATE,
STORE_ID NUMBER,
INVENTORY_ID NUMBER(6),
QTY_SOLD NUMBER(3)
)
PARTITION BY RANGE (START_DATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION POS_DATA_P2 VALUES LESS THAN (TO_DATE('1-07-2007', 'DD-MM-YYYY')),
PARTITION POS_DATA_P3 VALUES LESS THAN (TO_DATE('1-08-2007', 'DD-MM-YYYY'))
);

Adicionar uma nova partição em uma tabela já particionada:
--SEM COMPRESSÃO
ALTER TABLE tb_clientes ADD PARTITION P_198701 VALUES LESS THAN (198702) NOCOMPRESS;

--COM COMPRESSÃO
ALTER TABLE tb_clientes ADD PARTITION P_198701 VALUES LESS THAN (198702) COMPRESS FOR QUERY HIGH;

Publicações relacionadas:
- Oracle - Criar tabela sub-particionada;
- Oracle - Criar tabela com compressão.
Data publicação: 22:09 11/11/2015

Oracle - Subquery Factoring (WITH)

Gerar dias do ano utilizando Subquery Factoring (WITH):


WITH dias AS
(
SELECT trunc(SYSDATE) + rownum - 0 dia
FROM dual
CONNECT BY LEVEL <= 365
)
SELECT
d.dia
,to_number(to_char(d.dia, 'W')) sema
FROM dias d;
Data publicação: 21:41 11/11/2015

Oracle - Criar tabela com compressão

Exemplo 1:
CREATE TABLE tb_clientes
(
id_cliente NUMBER(6),
nm_cliente VARCHAR2(200),
dt_nascimento DATE,
CONSTRAINT pk_tb_clientes PRIMARY KEY (id_cliente)
)
COMPRESS FOR QUERY HIGH
NOLOGGING;

Exemplo 2 com CTAS (Create Table As Select):

CREATE TABLE TB_TESTE
COMPRESS FOR QUERY HIGH
NOLOGGING
AS
SELECT *
FROM DUAL;

Tipos de compressão:
- BASIC
- OLTP
- QUERY HIGH
- QUERY LOW
- ARCHIVE HIGH
- ARCHIVE LOW

Adicionar/alterar compressão após a criação da tabela:

ALTER TABLE table_name MOVE COMPRESS FOR QUERY HIGH;

Adicionar/alterar compressão após a criação da partição:

ALTER TABLE table_name MOVE PARTITION partition_name COMPRESS FOR QUERY HIGH;

Adicionar/alterar compressão após a criação da subpartição:

ALTER TABLE table_name MOVE SUBPARTITION subpartition_name COMPRESS FOR QUERY HIGH;

OBS: tabelas que possuem índices após serem comprimidas ficam com os índices com status "UNUSABLE", portanto é necessário reconstruir os índices.

Reconstruir índices:

ALTER INDEX index_name REBUILD;

Publicações relacionadas:
- Oracle - Criar tabela particionada;
- Oracle - Criar tabela sub-particionada.
Data publicação: 21:37 11/11/2015

Oracle - Scheduler Job

Scheduler job é uma das formas de agendar tarefas para execuções no Oracle, a partir do 11G. Segue abaixo formas de criar, alterar, excluir, etc...

Criar:

BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
JOB_NAME => 'JB_TESTE'
,JOB_TYPE => 'STORED_PROCEDURE'
,JOB_ACTION => 'HR.PRC_TESTE'
,START_DATE => SYSTIMESTAMP
,REPEAT_INTERVAL => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=8,20;BYMINUTE=0;BYSECOND=0'
,AUTO_DROP => FALSE
,COMMENTS => 'COMENTÁRIO JOB TESTE'
,ENABLED => TRUE
);
END;

Alterar:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE
(
NAME => 'JB_TESTE',
ATTRIBUTE => 'REPEAT_INTERVAL',
VALUE => 'FREQ=DAILY; BYHOUR=15; BYMINUTE=30'
);
END;

Excluir:

BEGIN
DBMS_SCHEDULER.DROP_JOB(JOB_NAME => 'HR.JB_TESTE', FORCE => TRUE);
END;

Verificar status:

SELECT owner, programa_name, program_type, enabled, max_runs
FROM dba_scheduler_programs
WHERE owner = 'HR';

Histórico de execuções:

SELECT * FROM ALL_SCHEDULER_JOB_RUN_DETAILS;

Listar scheduler jobs:

SELECT OWNER, JOB_NAME, STATE, LAST_START_DATE
FROM ALL_SCHEDULER_JOBS
WHERE OWNER = 'HR';

Forçar a execução:

BEGIN
DBMS_SCHEDULER.RUN_JOB('HR.JB_TESTE',TRUE);
END;

Forçar interromper execução:

BEGIN
DBMS_SCHEDULER.STOP_JOB
(
JOB_NAME => 'HR.JB_TESTE',
FORCE => TRUE
);
END;

Desabilitar:
BEGIN
DBMS_SCHEDULER.DISABLE(NAME => 'HR.JB_TESTE', FORCE => TRUE);
END;

Habilitar:

BEGIN
DBMS_SCHEDULER.ENABLE(NAME => 'HR.JB_SRK.JB_CTRL_DEPENDENCIAS');
END;

Limpar log de execução maiores que 10 dias:

BEGIN
DBMS_SCHEDULER.PURGE_LOG(LOG_HISTORY => 10, JOB_NAME => 'JB_TESTE');
END;
Data publicação: 21:57 28/08/2015

Oracle - Tabela temporária

Tabela temporária associada à transação:

Os dados são preservados até o final da transação, por exemplo, até ser executado um COMMIT, ROLLBACK ou uma instrução DDL.

Sintaxe:

CREATE GLOBAL TEMPORARY TABLE TMP_CLIENTES
(CD_CLIENTE NUMBER
,NM_CLIENTE VARCHAR2(100)
,DAT_CADAS DATE
);

Tabela temporária associada à sessão:

Os dados são preservados até o encerramento da sessão, por exemplo, LOG OFF.

Sintaxe:

CREATE GLOBAL TEMPORARY TABLE TMP_CLIENTES
(CD_CLIENTE NUMBER
,NM_CLIENTE VARCHAR2(100)
,DAT_CADAS DATE
) ON COMMIT PRESERVE ROWS;


CREATE GLOBAL TEMPORARY TABLE TMP_CLIENTES
ON COMMIT PRESERVE ROWS
AS
SELECT *
FROM TB_CLIENTES;

Para saber se a tabela é temporária ou não, basta verificar na seguinte VIEW do dicionário:

SELECT
L.TABLE_NAME
,L.TEMPORARY
FROM USER_TABLES L
WHERE L.TABLE_NAME = 'TMP_CLIENTES';

Mostrar código de criação da tabela:

SELECT
DBMS_METADATA.GET_DDL('TABLE','TMP_CLIENTES','HR') AS DDL_TABLE
FROM DUAL;
Data publicação: 22:00 27/08/2015

Oracle - PL/SQL Developer - Auto-replace

Na versão 8 do PL/SQL Developer foi implementado o recurso AUTO-REPLACE.

O recurso facilita na digitação de códigos, por exemplo, com este recurso é possível digitar a seguinte query "SELECT COUNT(1) FROM" apenas digitando "CONTAR" e depois pressionar a tecla tab.

- Clique em Tools > Preferences > Editor (agrupamento User Interface)
- Em AutoReplace marque a opção Enabled
- Clique em Edit
- Informe o código: CONTAR=SELECT COUNT(1) FROM
- Clique em Save e salve na pasta deseja ou na pasta padrão.

Agora ao digitar "CONTAR" e pressionar a tecla tab o texto será convertido em "SELECT COUNT(1) FROM".
Data publicação: 21:48 17/07/2015

Oracle - PL/SQL Developer - Salvar login e senha

Para salvar login e senha no PL/SQL Developer basta seguir os seguintes passos:

- Tools > Preferences > Logon History > Fixed Users
- Informe os dados da conexão neste formato: SCHEMA/senha@servidor
- Clique em OK.
- Para conectar clique em Session > Logon > ... e escolha o schema desejado.
Data publicação: 21:45 17/07/2015

Oracle - Procedure para exclusão de tabelas

Em um bloco de PL/SQL, muitas vezes necessitamos excluir uma tabela, mas não sabemos se a mesma existe no banco de dados Oracle. Ao tentar excluir uma tabela inexistente, em um bloco PL/SQL, a execução do bloco será interrompida com o retorno do erro informando que a tabela não existe.

Para evitar esse erro é necessário verificar a existência da tabela no dicionário de dados do Oracle e após isso efetuar o DROP TABLE, como no código abaixo.


CREATE OR REPLACE PROCEDURE PRC_DROP_TABLE
(
p_nm_object IN VARCHAR2
)
AS
v_nm_object VARCHAR2(61);
v_nm_table VARCHAR2(30);
v_nm_owner VARCHAR2(30);
v_table_name VARCHAR2(30); -- varíavel do banco de dados
BEGIN
IF INSTR(p_nm_object, '.', 1,1) = 0 THEN
v_nm_owner := USER;
v_nm_table := UPPER(p_nm_object);
ELSE
v_nm_owner := UPPER(REGEXP_SUBSTR(p_nm_object, '[^.]+',1,1));
v_nm_table := UPPER(REGEXP_SUBSTR(p_nm_object, '[^.]+',1,2));
END IF;

v_nm_object := v_nm_owner||'.'||v_nm_table;
BEGIN

SELECT table_name
INTO v_table_name
FROM all_tables
WHERE owner = v_nm_owner
AND table_name = v_nm_table;

EXECUTE IMMEDIATE('DROP TABLE '|| v_nm_object ||' PURGE');

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END PRC_DROP_TABLE;
Data publicação: 00:11 20/04/2015

Oracle - Consulta aleatória e inserção de múltiplos registros

No Banco de Dados Oracle, existe a possibilidade de retornar registros aleatórios em uma consulta utilizando a procedure RANDOM da package DBMS_RANDOM.

A procedure RANDOM retorna um número aleatório positivo ou negativo.

SELECT DBMS_RANDOM.RANDOM FROM DUAL;
Resultado 1: -1640736766
Resultado 2: 58909080


Para o exemplo da consulta aleatória, foi criada uma tabela com os meses do ano.

Em cada consulta realizada os registros serão retornados em ordem aleatória:

SELECT *
FROM TB_MESES
ORDER BY DBMS_RANDOM.RANDOM;


Retornar 3 meses aleatórios a cada consulta:

SELECT *
FROM (SELECT *
FROM TB_MESES
ORDER BY DBMS_RANDOM.RANDOM)
WHERE ROWNUM <= 3;


Tabela utilizada no exemplo:


CREATE TABLE TB_MESES
(
CD_MES NUMBER,
NM_MES VARCHAR2(10)
);
INSERT ALL
INTO TB_MESES (CD_MES, NM_MES) VALUES (1, 'Janeiro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (2, 'Fevereiro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (3, 'Março')
INTO TB_MESES (CD_MES, NM_MES) VALUES (4, 'Abril')
INTO TB_MESES (CD_MES, NM_MES) VALUES (5, 'Maio')
INTO TB_MESES (CD_MES, NM_MES) VALUES (6, 'Junho')
INTO TB_MESES (CD_MES, NM_MES) VALUES (7, 'Julho')
INTO TB_MESES (CD_MES, NM_MES) VALUES (8, 'Agosto')
INTO TB_MESES (CD_MES, NM_MES) VALUES (9, 'Setembro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (10, 'Outubro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (11, 'Novembro')
INTO TB_MESES (CD_MES, NM_MES) VALUES (12, 'Dezembro')
SELECT 1 FROM DUAL;
COMMIT;

Segue acima, exemplo de inserção de vários registros com apenas uma query em uma tabela. Também possível inserir registros em mais de uma tabela.
Data publicação: 21:36 24/03/2015

Oracle - Formatação de números

Para formatar números no Oracle para exibição, por exemplo, em relatórios é necessário utilizar a função TO_CHAR.

Função = TO_CHAR(número, ['máscara'], ['nls_parameters'])

Comandos da máscara:
D = Marcador decimal
G = Separador de milhar
FM = Remove os espaços em branco adicionais

Comandos do NLS_PARAMETERS:
NLS_NUMERIC_CHARACTERS = dg
OBS: o primeiro elemento, d, indica o caracter a ser utilizado com marcador decimal, enquanto o segundo elemento, g, indica o caracter a ser utilizado como separador de milhar.


SELECT
TO_CHAR(1013.50, 'FM999G999G990D00', 'NLS_NUMERIC_CHARACTERS=,.')
FROM DUAL;
-- Resultado: 1.013,50
SELECT
TO_CHAR(1013.50, 'FM999G999G990D00', 'NLS_NUMERIC_CHARACTERS=, ')
FROM DUAL;
-- Resultado: 1 013,50
SELECT
TO_CHAR(1013.50, 'FM099G999G990D00', 'NLS_NUMERIC_CHARACTERS=,.')
FROM DUAL;
-- Resultado: 000.001.013,50

Para converter número no formato brasileiro, separador decimal com vírgula, para o formato americano, separador decimal com ponto, basta utilizar a seguiente instrução:


SELECT
TO_NUMBER('1212,545', '9999999999990D000000000000', 'NLS_NUMERIC_CHARACTERS=,.')
FROM DUAL;
-- Resultado: 1212.545

Data publicação: 16:00 01/03/2015

Alterar o idioma padrão do Oracle SQL Developer

Ao instalar o software, gratuito, SQL Developer da Oracle, nativamente, o mesmo assume o idioma padrão do sistema operacional, muitas das vezes não é o idioma desejado.

Para alterar o idioma padrão do SQL Developer para inglês basta seguir os passos abaixo:

1) Localizar o local de instalação do SQL Developer, no meu caso: D:\Program Files (x86)\sqldeveloper

2) Abrir o arquivo ide.conf, localizado em $\sqldeveloper\ide\bin\ide.conf, no bloco de notas

3) Adicionar na última linha, do ide.conf, as 2 linhas abaixo e salvar:
  #Modifica o idioma padrão para o inglês
AddVMOption -Duser.language=en



Versão utilizada no exemplo:
SQL Developer Version 4.0.3.16
Data publicação: 20:49 27/01/2015

Oracle - Números Sequenciais com OVER

Números sequenciais em ordem crescente de first_name:

SELECT
ROW_NUMBER() OVER (ORDER BY e.first_name) AS sequencia,
e.employee_id,
e.first_name
FROM hr.employees e
ORDER BY e.first_name;



Números sequenciais para cada agrupamento de department_id em ordem crescente de employee_id:

SELECT
e.department_id,
e.last_name,
e.employee_id,
ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.employee_id) AS emp_id
FROM hr.employees e
ORDER BY e.department_id,
e.employee_id;



Mostrar registros que estão entre determinadas linhas, no exemplo abaixo, entre as linhas 51 e 100:

SELECT r, last_name
FROM (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R
FROM hr.employees)
WHERE R BETWEEN 51 and 100;



OBS: a cláusula OVER funciona da mesma forma no Oracle e SQL Server
Data publicação: 00:54 06/01/2015

Oracle - Manter somente números em uma coluna

Segue abaixo duas formas de manter somente os números em uma coluna no Oracle através de expressões regulares:


SELECT
REGEXP_REPLACE('!?#TESTE DE 123 COM LETRAS!!!!', '[^[:digit:]]'),
REGEXP_REPLACE('!?#TESTE DE 123 COM LETRAS!!!!', '[^0-9]')
FROM DUAL;

Resultado: 123

OBS: expressões regulares somente estão disponíveis a partir do Oracle 10G
Data publicação: 23:41 01/01/2015

Oracle - Agrupamento por faixa de valores

Para este exemplo, utilizei a tabela Employees do Schema HR padrão do Oracle.


SELECT
(CASE
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 0 AND 4 THEN '0 a 4 anos'
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 5 AND 8 THEN '5 a 8 anos'
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 9 AND 12 THEN '9 a 12 anos'
ELSE 'Superior ou 13 anos'
END) AS "FAIXA",
COUNT(*) AS "TOTAL"
FROM hr.employees
GROUP BY
(CASE
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 0 AND 4 THEN '0 a 4 anos'
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 5 AND 8 THEN '5 a 8 anos'
WHEN TRUNC((MONTHS_BETWEEN(SYSDATE,hire_date)/12),0) BETWEEN 9 AND 12 THEN '9 a 12 anos'
ELSE 'Superior ou 13 anos'
END);

OBS: poderia ser utilizado tanto TRUNC(coluna_data, 0) como FLOOR(coluna_data).
Data publicação: 10:09 30/09/2014

Utilizando Oracle SQL Developer com o MS SQL Server 2008 R2

Oracle SQL Developer é um software gratuito para acesso a banco de dados Oracle. Com ele é possível conectar a outros bancos de dados utilizando drivers JDBC de terceiros como Microsoft SQL Server, MySQL, ACCESS, e SyBase.

Como o driver JDBC da Microsoft não funciona corretamente com o Oracle SQL Developer é necessário utilizar o JTDS. O mesmo está disponível para download na página da Sourceforge. Faça o download da versão JTDS 1.2.7 Dist, testei outras versões do JTDS e as mesmas não funcionaram.

Descompacte o arquivo jtds-1.2.7-dist.zip e copie o jtds-1.2.7.jar para a pasta SQL Developer/Drivers. Crie a pasta Drivers caso não exista.

Para utilizar a autenticação do Windows é necessário copiar o arquivo jtds-1.2.7-dist\x86\SSO\ntlmauth.dll para a pasta \jdk\jre\bin.

Para habilitar o SQL Server, no Oracle SQL Developer, basta adicionar o driver jtds-1.2.7.jar em Ferramentas > Banco de dados > Drivers JDBC de Terceiros.


Feito os passos anteriores, basta criar um nova conexão SQL Server e configurá-la.


Versões utilizadas:
Oracle SQL Developer: 3.2.20.09
Driver JTDS 1.2.7 Dist
Java SE Development Kit 6.45 (JDK 6U45)
Data publicação: 12:08 18/09/2014

Banco de Dados - Declarações SQL

Nesta publicação, irei listar como os comandos de banco de dados estão divididos / classificados.

1. DML - Data Manipulation Language (Linguagem de Manipulação de Dados)

SELECT
INSERT
UPDATE
DELETE
MERGE

OBS: alguns autores não consideram a instrução SELECT como DML.

2. DDL - Data Definition Language (Linguagem de Definição de Dados)

CREATE
ALTER
DROP
TRUNCATE
RENAME
COMMENT

3. DCL - Data Control Language (Linguagem de Controle de Dados)
Instruções Oracle:
GRANT
REVOKE

4. TCL - Transaction Control Language (Linguagem de Controle de Transações)

COMMIT
ROLLBACK
Instruções Oracle:
SAVEPOINT
Instruções Microsoft SQL Server:
BEGIN TRANSACTION

Ponto de Atenção: No banco de dados Oracle, é necessário executar a cláusula COMMIT ou ROLLBACK após a execução de um comando DML (com exceção do SELECT). Comandos DDL possuem a execução do COMMIT implícito.
Data publicação: 16:02 10/09/2014

Oracle - Funções simples e de grupo

"As funções aceitam zero ou mais parâmetros, mas sempre retornam um resultado de um tipo de dado predeterminado".


"As funções de linha única podem ser aninhadas em qualquer nível, mas as funções de grupo podem ser aninhadas até dois níveis de profundidade, no máximo".

Ex.: SUM(AVG(X))

Referência: Oracle Database 11g - Fundamentos I SQL (Exame 1Z0-051) - John Watson, Roopesh Ramklass
Data publicação: 00:22 09/09/2014
Perfil
Olá jovem Padawan, seja bem vindo! Este site foi criado com o intuito de compartilhar um pouco de conhecimento de Tecnologia da Informação e Business Intelligence.

Linkedin  Youtube

"O sorriso é o símbolo do vencedor." (Masaaki Hatsumi)


Leandro Sacramento, Todos os direitos reservados - 2012 - 2020