Transações em PostgreeSQL

Artigo sobre transações no iMasters disponível em http://imasters.uol.com.br/artigo/1097/postgresql/transacoes_com_o_postgresql_parte_iii/imprimir/

Usando condições ANY ou SOME

Dado o esquema a seguir:

SQL> desc valores
Nome                Nulo?    Tipo
------------------- -------- ---------
V                            NUMBER(38)

SQL> select * from valores;

V
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

18 linhas selecionadas.

SQL>

Exemplos de Condições SQL

EXEMPLO 1
SQL> select * from valores
  2  where v = some(1, 3, 10);

         V
----------
         1
         3
        10

3 linhas selecionadas.

EXEMPLO 2

  1  select * from valores
  2* where v > some(3, 10, 11)
SQL> /

         V
----------
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18

15 linhas selecionadas.

EXEMPLO 3

  1  select * from valores
  2* where v > all(3, 10, 11)
SQL> /

         V
----------
        12
        13
        14
        15
        16
        17
        18

7 linhas selecionadas.

Desafio SQL: Alias em SQL

Ola, vou iniciar este post com uma questão para resolvermos.

Considere a seguinte questão:
SQL>
1 select id "Produto ID", sum(preco) "Preco"
2 from estoque
3 where Preco > 50
4 group by "Produto ID"
5* order by sum(preco)

Qual linha causa um erro ?

Analisando a questão, pode-se perceber várias partes que podem dar a entender estar errado. Olhando o SQL, vemos que os campos utilizam-se de alíases, e o campo preco tem um alias semelhante ao nome do campo: "Preco". Isso pode passar despercebido ou confundir, fazendo alguém pensar que a linha 3 está errado, pois o alias está sendo referênciado.

Então a primeira regra que temos que saber: na cláusula WHERE, devemos referênciar o campo e não o alias, senão um erro ocorre.

Na consulta acima, agora não resta dúvida, estamos acessando o campo preco, pois SQL é case insensitive, não importa como escrevemos: preco, Preco, PRECO, PReco, etc. Com isso, podemos concluir também que, se fizermos uma prova para certificação em SQL e aparecer um alias na cláusula WHERE, é certo que há um erro ai.

Para não restar dúvida, segue um SQL de exemplo:

SQL> select codigo "Codigo" from estoque
  2  where "Codigo" <> 0;
where "Codigo" <> 0
      *
ERRO na linha 2:
ORA-00904: "Codigo": identificador invßlido

Seguindo o exercício da questão, alguns podem dizer que a linha 4 está errado. E de fato, está. A mesma regra do WHERE vale no GROUP BY, na cláusula WHERE e GROUP BY, devemos referênciar o campo e não o alias, senão um erro ocorre.

Segue dois exemplos, o primeiro SQL referenciou um alias no group by e o segundo o próprio campo:

EXEMPLO 1:
SQL>
  1  select codigo as num
  2  from estoque
  3* group by num
SQL> /
group by num
         *
ERRO na linha 3:
ORA-00904: "NUM": identificador invßlido

EXEMPLO 2:
SQL>
  1  select codigo as num
  2  from estoque
  3* group by codigo
SQL> /

NUM
-------------
1255
124
126
125

Bom, sabemos que o erro na questão está na linha 4, onde é referênciado o alias "Produto ID". Mas quero mostrar outra regra, que nesta questão não se aplica, mas que devemos saber mesmo assim:

Na cláusula ORDER BY, podemos acessar tanto o campo, como o alias ou um número que indica a posição do campo na cláusula SELECT.

Isso pode confundir muitos, pois porque não podemos acessar o alias no WHERE e no GROUP BY mas podemos acessar no ORDER BY. E ainda, podemos acessar o campo pela sua posição na lista de campos do SELECT.

Isso mesmo, segue vários exemplos que mostram isso na prática:

EXEMPLO 1:
SQL>
  1  select codigo as num
  2  from estoque
  3* order by codigo
SQL> /

NUM
-------------
124
124
124
125
125
125
1255
126

8 linhas selecionadas.

EXEMPLO 2:
SQL>
  1  select codigo as num
  2  from estoque
  3* order by num
SQL> /

NUM
-------------
124
124
124
125
125
125
1255
126

8 linhas selecionadas.

EXEMPLO 3:
SQL>
  1  select codigo as num
  2  from estoque
  3* order by 1
SQL> /

NUM
-------------
124
124
124
125
125
125
1255
126

8 linhas selecionadas.

SQL>

Então como vemos, o ORDER BY é a cláusula mais flexivel de todas, podemos acessar os campos da consulta de qualquer maneira e vai funcionar. A cláusula ORDER BY tem como finalidade ordenar as tuplas de uma relação, portanto, ela é a última etapa executada e consegue ver a relação pronta, ao contrário das demais cláusulas.

Mais uma vez, espero ter ajudado a comunidade de desenvolvedores, sempre tive muita dúvida a respeito disso, até que resolvi escrever este post e testar de diversas maneiras, vários SQL e terminar de uma vez por todas, estas dúvidas sobre o uso do alias em consultas SQL.

Até o próximo post!

Números randômicos em ORACLE

Neste post, vamos ver uma dica para obter números aleatórios. A ORACLE disponibiliza a package DBMS_RANDOM. Vamos consultar as funções disponíveis usando o seguinte comando no SQL-Plus:

SQL> desc dbms_random
PROCEDURE INITIALIZE
 Nome do Argumento                  Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            BINARY_INTEGER          IN
FUNCTION NORMAL RETURNS NUMBER
FUNCTION RANDOM RETURNS BINARY_INTEGER
PROCEDURE SEED
 Nome do Argumento                  Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            BINARY_INTEGER          IN
PROCEDURE SEED
 Nome do Argumento                  Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            VARCHAR2                IN
FUNCTION STRING RETURNS VARCHAR2
 Nome do Argumento                  Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OPT                            CHAR                    IN
 LEN                            NUMBER                  IN
PROCEDURE TERMINATE
FUNCTION VALUE RETURNS NUMBER
FUNCTION VALUE RETURNS NUMBER
 Nome do Argumento                  Tipo                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOW                            NUMBER                  IN
 HIGH                           NUMBER                  IN

Como se pode ver, existem diversas funções para gerar números aleatórios. No exemplo a seguir, vou demonstrar como gerar números aleatórios inteiros entre 1 e 100:

SQL> select trunc(dbms_random.value(1, 100)) num  from dual;

       NUM
----------
        89

SQL> select trunc(dbms_random.value(1, 100)) num  from dual;

       NUM
----------
         9

SQL> select trunc(dbms_random.value(1, 100)) num  from dual;

       NUM
----------
        39

No exemplo, três instruções SQL consecutivas exibem como a função DBMS_RANDOM.VALUE funciona. Ela retorna um valor NUMBER. Como sabemos o tipo NUMBER pode armazenar tanto números inteiros como números ponto flutuante. Para retornar um inteiro, foi utilizado a função TRUNC. A função utilizada permite passar o range da randomização, o que pode ser muito útil.

É isso ai, espero ter ajudado a quem procura uma maneira de gerar números randômicos em ORACLE.
Até mais!

SQL - Verificar duplicidade de código de barra

Ola, seguindo o espírito de "Ask Tom", vou postar alguns truques usando SQL que podem ser muito úteis para os desenvolvedores. Sem perdemos tempo, vamos-la!

Últimamente alguns clientes estão reclamando que há mais de 1 produto com o mesmo código. O Analista, ao verificar o banco de dados, descobre que não existe uma chave única no campo do código de barra. Como podemos saber quais são os produtos que possuem o código duplicado ?

Resposta: Existem várias maneiras de construir um SQL para verificar duplicidade de valor em uma coluna. Uma das maneiras que vou ensinar é usar subquery e a cláusula group by como segue:

select id, codigo, nome
from estoque
where codigo in (select codigo
            from estoque
            group by codigo
            having count(codigo) > 1)

Analisando a consulta, verifique que estamos buscando todos os produtos da tabela estoque que tem o código na sub-consulta. A sub-consulta, por sua vez, lista os códigos da tabela estoque que possuem duplicidade.

Para entender a sub-consulta, é simples, baste dizer que "select codigo from estoque group by codigo" lista todos os códigos sem duplicidade e "having count(codigo) > 1" filtra somente os códigos que existe em mais de 1 tupla na tabela estoque.

Ainda me lembro há 5 anos atraz de um programa em Clipper para fazer isso. Apenas um comando SQL fez todo trabalho!

É isso ai, até o próximo post, com mais dicas!

Vou ter que ensinar o Firebird a fazer aritmética

Bem, vou iniciar este post depois de muita depuração. Em nosso ERP PoliGestor, realizamos um cálculo para encontrar o lucro bruto para os produtos cadastrados. Acontece que no Delphi a expressão resulta em um valor diferente que a mesma expressão computada via SQL.

Eis a expressão no código fonte:

Lucro:= TruncFloat((ADO_EstoquePRC_VENDA.AsCurrency -                                                           ADO_EstoquePRC_CUSTO.AsCurrency) / ADO_EstoquePRC_CUSTO.AsCurrency * 100, 9);

A mesma expressão em um formato mais legível é:

vLucro:= trunc(((prc_venda - prc_custo) / prc_custo) * 100, 9);

  Resultado no Delphi usando o tipo REAL para vLucro: 80,375180375

Esta expressão é truncada para 9 casas para ter uma precisão boa em nosso sistema. Então qual é o problema ? Se executarmos esta mesma expressão no servidor RDBMS Firebird, o resultado é 80.370000000 em uma stored procedure (codificando vLucro com tipo NUMERIC(12, 9)) ou 80.37 quando executado via SQL.

A instrução SQL é a seguinte:

select ((200 - 110.88)/110.88)*100 from rdb$database

Resultado: 80.37
Versão do Firebird: 2.5

SOLUÇÂO

A solução encontrada foi ensinar o Firebird a fazer a aritmética, promovendo um número da expressão, como segue:

select ((cast(200 as numeric(12, 9)) -  110.88)/110.88)*100 from rdb$database

Resultado: 80,375180375

Esta solução funcionou mas me deixou preocupado com os demais cálculos realizados pelo sistema. Sabemos que o Firebird realiza cálculos com precisão diferente de outros bancos e de outras linguagens de programação e isso pode acarretar problemas em arredondamentos e cálculos de porcentagens.

CONCLUSÂO

Sabe-se que cada RDBMS usa um esquema diferente para promover seus tipos. A conversão implícita (automática), nem sempre é o que queremos. No ORACLE, por exemplo, é recomendado usar to_number para fazer as conversões. Depois de fazer a promoção de uma parte da expressão para NUMERIC(12, 9) o cálculo ficou correto porque foi informado ao Firebird que os cálculos devem ser entre números com tipos NUMERIC(12, 9).

É isso ai, até mais!

Dicas e Truques PL/SQL - Parte 3

Function ASCII - retorna o código ASCII do caracter.

SQL> select ascii('A') from dual;

ASCII('A')
----------
        65


Function CHR - Retorna um caracter quando recebe seu valor ASCII.

SQL> select chr(65) from dual;

C
-
A


Function CONCAT - Concatena duas strings.

SQL> select concat('ORACLE ', 'PL/SQL') texto from dual;

TEXTO
-------------
ORACLE PL/SQL


Function INITCAP - Primeiro caracter de cada palavra de uma string em maiúsculo.

SQL> select initcap('everton de vargas agilar') my_name from dual;

MY_NAME
------------------------
Everton De Vargas Agilar


Function INSTR - Retorna a localização de uma string dentro de outra string.

EXEMPLO 1

SQL> select instr('ABCDEFGHIJLMNOPQRSTUVXZabcdefghijlmnopqrstuvxzABCDEFGHIJKLMNOPQRSTUVXZ', 'DEF') pos from dual;

       POS
----------
         4


EXEMPLO 2

SQL> select instr('ABCDEFGHIJLMNOPQRSTUVXZabcdefghijlmnopqrstuvxzABCDEFGHIJKLMNOPQRSTUVXZ', 'DEF', 5) pos from dual;

       POS
----------
        50

EXEMPLO 3


SQL> 
  1  create or replace procedure print_values(
  2    pValues in varchar2,
  3    pDesconsiderar in varchar2)
  4  is
  5    v char;
  6  begin
  7    for i in 1..length(pValues) loop
  8      v:= substr(pValues, i, 1);
  9      if instr(pDesconsiderar, v) = 0 then
 10        dbms_output.put_line(v);
 11      end if;
 12    end loop;
 13* end;
SQL> /

Procedimento criado.

SQL> execute print_values('12345', '4');
1
2
3
5

Procedimento PL/SQL concluÝdo com sucesso.


Function LPAD - Preenche uma string no lado esquerdo de caracteres com qualquer string especificada.

SQL> select lpad('789123', 13) codigo from dual;

CODIGO
-------------
       789123

SQL> select lpad('789123', 13, '0') codigo from dual;

CODIGO
-------------
0000000789123

SQL> select lpad('789123', 13, '01') codigo from dual;

CODIGO
-------------
0101010789123

SQL> select lpad('789123', 13, '*') codigo from dual;

CODIGO
-------------
*******789123



Function RPAD - Preenche uma string no lado direito de caracteres com qualquer string especificada.

SQL> select rpad('789123', 13) codigo from dual;

CODIGO
-------------
789123

SQL> select rpad('789123', 13, '0') codigo from dual;

CODIGO
-------------
7891230000000

SQL> select rpad('789123', 13, '01') codigo from dual;

CODIGO
-------------
7891230101010

SQL> select rpad('789123', 13, '-') codigo from dual;

CODIGO
-------------
789123-------

SQL> select rpad('789123', 13, '*') codigo from dual;

CODIGO
-------------
789123*******


Function LENGTH - Retorna o comprimento de uma string.

SQL> select length('ORACLE 10g') text from dual;

      TEXT
----------
        10


Function REPLACE - Substitui toda ocorrência de uma string por outra string.

SQL> select replace('ORACLE 10g', '10g', '11g') text from dual;

TEXT
----------
ORACLE 11g


Function TRANSLATE - Igual a REPLACE mas opera a nível de caracter. Veja um exemplo com REPLACE e TRANSLATE.

-- Converte ad para x. Somente uma ocorrência a ser encontrada.
SQL> select replace('abcdabada', 'ad', 'x') from dual;

REPLACE(
--------
abcdabxa

-- Converte o caracter "a" ou o caracter "d" para x. Várias ocorrências são encontradas.
SQL> select translate('abcdabada', 'ad', 'x') from dual;

TRANSLA
-------
xbcxbxx


Function SOUNDEX - Retorna a representação fonética de uma string. Útil nas palavras que têm ortografia diferente mas com som parecido.

SQL> 
  1  select * from (
  2      select 'Agilar' as nome from dual
  3      union
  4      select 'Aguilar' as nome from dual
  5      union
  6      select 'Stefanello' as nome from dual
  7      union
  8      select 'Agilare' as nome from dual
  9  )
 10* where soundex(nome) = soundex('Agilar')
SQL> /

NOME
----------
Agilar
Agilare
Aguilar


Function SUBSTR - Retorna uma parte de uma string de dentro de uma string.

SQL> select substr('ORACLE 11g', 8, 3) from dual;

SUB
---
11g


Function TRIM, LTRIM, RTRIM - Remove espaços de uma string em ambos lados, a esquerda ou a direita.

SQL> select trim('      8976       ') texto from dual;

TEXT
----
8976

SQL> select ltrim('      8976       ') text from dual;

TEXT
-----------
8976

SQL> select rtrim('      8976       ') text from dual;

TEXT
----------
      8976


Function UPPER, LOWER - Converte a string para maiúsculo ou minúsculo.


SQL> select upper('oracle') texto from dual;

TEXTO
------
ORACLE

SQL> select lower('Oracle') texto from dual;

TEXTO
------
oracle



Function ABS - Retorna o valor absoluto de um número.

SQL> select abs(-11) from dual;

  ABS(-11)
----------
        11

SQL> select abs(11) from dual;

   ABS(11)
----------
        11


Function FLOOR - Retorna o maior integer menor ou igual a um valor decimal.
Function CEIL - Retorna o menor integer maior ou igual a um valor decimal.

SQL> select floor(12345.56) from  dual;

FLOOR(12345.56)
---------------
          12345

SQL> select ceil(12345.56) from  dual;

CEIL(12345.56)
--------------
         12346


Function ROUND - Retorna um número arredondado para n casas.

SQL> select round(10.126, 2) from dual;

ROUND(10.126,2)
---------------
          10,13

SQL> select round(10.126, 2) value from dual;

     VALUE
----------
     10,13

SQL> select round(10.126, 3) value from dual;

     VALUE
----------
    10,126


Function Trunc - Retorna um número truncado em n casas.

SQL> select trunc(10.126, 3) from dual;

TRUNC(10.126,3)
---------------
         10,126

SQL> select trunc(10.126, 2) from dual;

TRUNC(10.126,2)
---------------
          10,12

SQL> select trunc(10.126, 1) from dual;

TRUNC(10.126,1)
---------------
           10,1

SQL> select trunc(123456, -1) from dual;

TRUNC(123456,-1)
----------------
          123450

SQL> select trunc(123456, -2) from dual;

TRUNC(123456,-2)
----------------
          123400

SQL> select trunc(123456, -3) from dual;

TRUNC(123456,-3)
----------------
          123000


Function SIGN - Determina se um número é positivo, negativo ou zero.

SQL> select sign(10) from dual;

  SIGN(10)
----------
         1

SQL> select sign(-10) from dual;

 SIGN(-10)
----------
        -1

SQL> select sign(0) from dual;

   SIGN(0)
----------
         0


Function ADD_MONTHS - Adiciona mês a uma data.

SQL> select add_months(to_date('31/01/2010', 'dd/mm/yyyy'), 1) from dual;

ADD_MONT
--------
28/02/10

SQL> select add_months(to_date('31/01/2010', 'dd/mm/yyyy'), -1) from dual;

ADD_MONT
--------
31/12/09


Function LAST_DAY - Retorna a data do último dia do mês.

SQL> select last_day(to_date('19/01/2010', 'dd/mm/yyyy')) from dual;

LAST_DAY
--------
31/01/10

SQL> select last_day(to_date('01/02/2010', 'dd/mm/yyyy')) from dual;

LAST_DAY
--------
28/02/10


Function MONTHS_BETWEEN - Calcula os meses entre duas datas.

SQL> select months_between(to_date('01/03/2010', 'dd/mm/yyyy'), to_date('01/04/2010', 'dd/mm/yyyy')) meses from dual;

     MESES
----------
        -1

SQL> select months_between(to_date('01/04/2010', 'dd/mm/yyyy'), to_date('01/03/2010', 'dd/mm/yyyy')) meses from dual;

     MESES
----------
         1


Function NEXT_DAY - Retorna a data do primeiro dia da semana especificado em uma string após a data inicial.

SQL> select next_day(sysdate, 'SEXTA') from dual;

NEXT_DAY
--------
22/01/10

SQL> select next_day(sysdate, 'DOMINGO') from dual;

NEXT_DAY
--------
24/01/10


Function TO_CHAR - Converte um número ou data para string.

EXEMPLOS COM DATA

SQL> select to_char(sysdate, 'Month') dt from dual;

DT
------------------------------------
Janeiro

SQL> select to_char(sysdate, 'MON') dt from dual;

DT
------------
JAN

SQL> select to_char(sysdate, 'MONTH') dt from dual;

DT
------------------------------------
JANEIRO

SQL> select to_char(sysdate, 'MM') dt from dual;

DT
--
01

SQL> select to_char(sysdate, 'YYYY') dt from dual;

DT
----
2010

SQL> select to_char(sysdate, 'YYY') dt from dual;

DT
---
010

SQL> select to_char(sysdate, 'YY') dt from dual;

DT
--
10

SQL> select to_char(sysdate, 'RR') dt from dual;

DT
--
10


SQL> select to_char(sysdate, '"Data: "DD/MM/YYYY') dt from dual;

DT
----------------
Data: 19/1/2010

SQL> select to_char(sysdate, 'FMDay, Month, YYYY') dt from dual;

DT
-------------------------------------------------------------------
Terþa-Feira, Janeiro, 2010

SQL> select to_char(sysdate, 'Day, Month, YYYY') dt from dual;

DT
-------------------------------------------------------------------
Terça-Feira  , Janeiro  , 2010


EXEMPLOS COM NÚMEROS

SQL> select to_char(12345.23, '99999999') from dual;

TO_CHAR(1
---------
    12345

SQL> select to_char(12345.23, '99999D99') from dual;

TO_CHAR(1
---------
 12345,23

SQL> select to_char(12345.23, '99999D999') from dual;

TO_CHAR(12
----------
 12345,230

SQL> select to_char(12345.23, '99999D000') from dual;

TO_CHAR(12
----------
 12345,230

SQL> select to_char(12345, '99999D000') from dual;

TO_CHAR(12
----------
 12345,000

SQL> select to_char(12345, '$99999D000') from dual;

TO_CHAR(123
-----------
 $12345,000


SQL> select to_char(12345, 'L99999D000') from dual;

TO_CHAR(12345,'L9999
--------------------
         R$12345,000

-- OBS: Se a máscara for menor que o número, o caracter # será impresso!
SQL> select to_char(12345, '999') from dual;

TO_C
----
####

Dicas e Truques PL/SQL - Parte 2

1 - Uso do WHILE com EXIT WHEN

SQL> 
  1  create or replace
  2  function conta_ate_espaco(texto in varchar2) return number is
  3    i number := 1;
  4    vResult number := 0;
  5  begin
  6    while i <= length(texto) loop
  7       if substr(texto, i, 1) != ' ' then
  8          vResult:= vResult + 1;
  9          i:= i+1;
 10       end if;
 11      exit when substr(texto, i, 1) = ' ';   -- vai sair do loop quando encontrar um espaço
 12    end loop;
 13    -- return é executado após o fim do loop ou quando exit when da linha 11 é executado.
 14    return vResult;
 14* end;
SQL>


2 - Usando rótulos para identificar loops.

EXEMPLO 1:

SQL> 
  1  create or replace
  2  function conta_ate_espaco(texto in varchar2) return number is
  3    i number := 1;
  4    vResult number := 0;
  5  begin
  6    <<conta_char>>  -- define um rótulo
  7    while i <= length(texto) loop
  8       if substr(texto, i, 1) != ' ' then
  9     vResult:= vResult + 1;
 10          i:= i+1;
 11       end if;
 12      exit when substr(texto, i, 1) = ' ';
 13    end loop conta_char;  -- finalize o loop e opcionalmente informe o nome do rótulo
 14    return vResult;
 15* end;
SQL> / 

EXEMPLO 2:

SQL> 
  1  create or replace procedure imprime_tabuada is
  2    vNumero number := 1;
  3    i number := 1;
  4  begin
  5    <<inicio_rotina>>
  6    for vNumero in 1..10 loop
  7      i := 1;
  8      <<tabuada>>
  9      loop
 10        dbms_output.put_line(to_char(vNumero) || 'x' ||
 11                             to_char(i) || ' = ' || to_char(vNumero*i));
 12        exit tabuada when i = 10;
 13        i := i + 1;
 14      end loop tabuada;
 15    end loop inicio_rotina;
 16* end;
SQL>

EXEMPLO 3


SQL> 
  1  create or replace procedure conta_ate_10 is
  2    i number := 1;
  3  begin
  4    <<conta>>
  5    loop
  6      dbms_output.put_line(i);
  7      exit when i = 10;
  8      i := i + 1;
  9    end loop;
 10* end;
SQL>


3 - Usando a instrução GOTO.

SQL> 
  1  create or replace procedure conta_ate_10_com_goto is
  2    i number := 1;
  3  begin
  4    loop
  5      dbms_output.put_line(i);
  6      if i = 10 then
  7        goto fim;
  8      end if;
  9      i := i + 1;
 10    end loop;
 11    <<fim>>
 12    null;  -- necessário uma instrução após o rótulo ou um erro é gerado
 13* end;
SQL>

Dicas e Truques PL/SQL - Parte 1

1 - Nunca esqueça de usar o ; para finalizar uma bloco PL/SQL.


SQL> begin
2     dbms_output('não esqueça de usar ;');
3  end
4  /
end
*
ERRO na linha 3:
ORA-06550: linha 3, coluna 3:
PLS-00103: Encontrado o símbolo "end-of-file" quando um dos seguintes símbolos
era esperado:
; 

O símbolo ";" foi substituído por "end-of-file" para continuar.


SQL> begin
2     dbms_output.put_line('o ; finaliza um bloco PL/SQL');
3* end;
SQL> /

Procedimento PL/SQL concluído com sucesso.

SQL>


2 - Uma tipo VARCHAR2 tem 4000 bytes de comprimento no banco mas em PL/SQL o limite 
sobe para 32767 bytes.


3 - Existem dois subtipos para o tipo VARCHAR2: VARCHAR e STRING. A ORACLE recomenda
que não se usem estes dois subtipos, pois existe apenas para compatibilidade com
outras marcas de banco de dados e também com o padrão SQL.

SQL> 
1  declare
2    x varchar2(100);
3    y string(100);
4    z varchar(100);
5  begin
6    x:= 'Use sempre o tipo VARCHAR2 ';
7    y:= 'em vez de subtipos STRING ';
8    z:= 'ou VARCHAR.';
9    dbms_output.put_line(x || y || z);
10* end;
SQL> /
Use sempre o tipo VARCHAR2 em vez de subtipos STRING ou VARCHAR.

Procedimento PL/SQL concluído com sucesso.

SQL>


4 - Ao declarar uma variável VARCHAR2 dentro de um procedimento 
armazenado sempre especifique o comprimento ou um erro ocorrerá.


5 - Ao invocar uma função que não retorne valor um erro será levantado. 


6 - A comparação entre variáveis VARCHAR2 e CHAR pode retornar falso, pois 
o tipo CHAR é fixo.

SQL> declare
2  x1 varchar2(10);
3  x2 char(10);
4  begin
5     x1:= 'agilar';
6     x2:= 'agilar';
7     if x1 = x2 then
8             dbms_output.put_line('exatamente iguais');
9     else
10             dbms_output.put_line('diferente!');
11     end if;
12  end;
13  /

Procedimento PL/SQL concluído com sucesso.

SQL> set serveroutput on
SQL> /
diferente!

Procedimento PL/SQL concluído com sucesso.


7 - Use funções e procedimentos aninhados para obter modularidade.

SQL> 
1  declare
2     x1 number;
3     x2 number;
4    procedure troca(x in out number, y in out number)
5    as
6      temp number;
7    begin
8      temp:= x;
9      x:= y;
10      y:= temp;
11    end;
12  begin
13     x1:= 100;
14     x2:= 200;
15     dbms_output.put_line(x1);
16     dbms_output.put_line(x2);
17     troca(x1, x2);
18     dbms_output.put_line(x1);
19     dbms_output.put_line(x2);
20* end;
SQL> /
100
200
200
100

Procedimento PL/SQL concluído com sucesso.


8 - Não use os tipos obsoletos: LONG, RAW e LONG RAW.


9 - Use PLS_INTEGER em vez de BINARY_INTEGER em aplicativos novos para obter performance.


10 - Cuidado com o escopo das variáveis. Uma variável pode ser referenciada
apenas dentro do bloco em que foi declarado.

SQL> 
1  declare
2     x varchar2(10):= 'everton';
3  begin
4     declare
5             y varchar2(10):= 'agilar';
6     begin
7             dbms_output.put_line(x);
8             dbms_output.put_line(y);
9     end;
10     dbms_output.put_line(y);   -- erro pois y está declarado dentro do bloco anterior
11* end;
SQL> /
dbms_output.put_line(y);
*
ERRO na linha 10:
ORA-06550: linha 10, coluna 23:
PLS-00357: A referência 'Y' Ó Tabela, View ou Seqência não permitida neste
contexto
ORA-06550: linha 10, coluna 2:
PL/SQL: Statement ignored

SQL> 


11 - Exemplos de uso da função CAST


SQL> select cast(123.1254 as number(12,3)) as num from dual;

NUM
----------
123,125

SQL> select cast(123.1254 as number(12,2)) as num from dual;

NUM
----------
123,13

SQL> select cast(123.1254 as number(12,1)) as num from dual;

NUM
----------
123,1

SQL> select cast(123.1254 as number(12,0)) as num from dual;

NUM
----------
123

SQL> select cast(123.1254 as number(12,-1)) as num from dual;

NUM
----------
120

SQL> select cast(123.1254 as number(12,-2)) as num from dual;

NUM
----------
100

SQL> select cast(123.1254 as number(12,-3)) as num from dual;

NUM
----------
0

SQL>


12 - Você pode usar o operador de exponenciação em PL/SQL mas não em SQL.

SQL> l
1  declare
2     x number:= 10;
3     y number:= 2;
4  begin
5     dbms_output.put_line(x**y);
6* end;
SQL> /
100

Procedimento PL/SQL concluído com sucesso.

SQL> select 10**2 from dual;
select 10**2 from dual
*
ERRO na linha 1:
ORA-00936: expressão não encontrada


13 - É possível atribuir um valor durante a declaração da variável.

SQL> l
1  declare
2     pi number:= 3.14;
3     x number;
4     y number:= 10;
5  begin
6     dbms_output.put_line('O número PI is '|| to_char(pi));
7* end;
SQL>


14 - Operadores de desigualdade da PL/SQL: <>, != e ~=

1  begin
2     if 1 != 2 then
3             dbms_output.put_line('diferente');
4     end if;
5     if 1 <> 2 then
6             dbms_output.put_line('diferente');
7     end if;
8     if 1 ~= 2 then
9             dbms_output.put_line('diferente');
10     end if;
11* end;
SQL>


15 - Expressões envolvendo NULL em PL/SQL

SQL> 
1  declare
2     a number;
3     b number;
4  begin
5    -- Atribui 10 a variável A mas deixa B null.
6    a:= 10;
7    -- A lógica dos três valores diz que o resultado
8    -- de uma expressão pode ser true, false ou null.
9    if a = b then
10      dbms_output.put_line('a = b is true');
11    elsif a <> b then
12     dbms_output.put_line('a = b is not true');
13    else
14      dbms_output.put_line('a = b is null');
15    end if;
16    -- Eis uma expressão que muitos esperam ser true
17    if (a = b) or (a <> b) then
18      dbms_output.put_line('true');
19    else
20     dbms_output.put_line('is not true');
21    end if;
22* end;
SQL> /
a = b is null
is not true

Procedimento PL/SQL concluído com sucesso.

SQL>


16 - Oracle considera uma string vazia como sendo null.

SQL> 
1  declare
2     x varchar2(10);
3     y varchar2(10);
4  begin
5     -- Oracle considera uma string vazia como sendo null
6     x:= '';
7     if x = '' then
8             dbms_output.put_line('x is vazio');
9     elsif x is null then
10             dbms_output.put_line('x is null');
11     else
12             dbms_output.put_line('x contém algo?');
13     end if;
14     -- Comparação entre duas variáveis vazias
15     y:= '';
16     if x = y then
17             dbms_output.put_line('x = y');
18     elsif x is null then
19             dbms_output.put_line('x is null');
20     else
21             dbms_output.put_line('x <> y');
22     end if;
23* end;
SQL> /
x is null
x is null

Procedimento PL/SQL concluído com sucesso.

SQL>

17 - NULL não se propaga na concatenação de strings em ORACLE. Em outros
RDBMS o valor retornado é NULL.

SQL> 
1* select 'everton ' || 'agilar' || null from dual;

'EVERTON'||'AG
--------------
everton agilar

SQL> begin
2     dbms_output.put_line('everton ' || 'agilar' || null);
3  end;
4  /
everton agilar

Procedimento PL/SQL concluído com sucesso.

SQL>


18 - Use a função NVL ou COALESCE para trocar valores nulls por outro valor.


SQL> select nvl(null, 10) from dual;

NVL(NULL,10)
------------
10

SQL> select coalesce(null, 10) from dual;

COALESCE(NULL,10)
-----------------
10



19 - Como comparar duas datas em PL/SQL para saber se o ano é o mesmo ?

SQL> 
1  declare
2    d1 date;
3    d2 date;
4  begin
5    d1:= to_date('30/03/2010', 'dd/mm/yyyy');
6    d2:= sysdate;
7    if trunc(d1, 'yyyy') = trunc(d2, 'yyyy') then
8      dbms_output.put_line('ano igual...');
9    else
10      dbms_output.put_line('ano diferente...');
11    end if;
12* end;
SQL> /
ano igual...

Procedimento PL/SQL concluído com sucesso.

SQL>



20 - Obtendo as partes de uma data através da função TO_CHAR.

SQL> select to_char(to_date('10/01/2010', 'dd/mm/yyyy'), 'dd') from dual;

TO
--
10

SQL> select to_char(to_date('10/01/2010', 'dd/mm/yyyy'), 'mm') from dual;

TO
--
01

SQL> select to_char(to_date('10/01/2010', 'dd/mm/yyyy'), 'yyyy') from dual;

TO_C
----
2010

SQL>

Criando e usando um repositório Subversion

Subversion é um sistema de controle de versão muito utilizado em empresas de desenvolvimento de software para controle de versão de projetos em geral. Permite gerenciar um repositório com os fontes de um ou mais projetos e permitir acesso e alteração por diversos integrantes da equipe de desenvolvimento.

A equipe de desenvolvimento pode trabalhar em diversos arquivos e após sincronizar estes arquivos com o repositório que mantém a cópia oficial do projeto. Cada usuário trabalha em uma cópia local (Work Copy), realizando as alterações necessárias e depois comitando com o repositório.

Abaixo, segue algumas dicas sobre a implantação de um repositório SVN em minha empresa divididos em etapas que devem ser seguidas passo a passo.

Onde posso baixar o Subversion

O Subversion pode ser encontrado no link http://subversion.tigris.org/. É um produto open-source disponibilizado gratuitamente. Existem também diversos outros pacotes de software disponível e plugins para várias IDEs.

Pacotes Necessários para Implantação do Subversion


Servidor VisualSVN - http://www.visualsvn.com/visualsvn/download/
Cliente TortoiseSVN - http://tortoisesvn.tigris.org/


O que é o VisualSVN 


Conjunto de ferramentas necessárias para implementar o SVN junto com um servidor Web para administração remota. Este software é necessário no servidor onde será instalado o SVN.


O que é o TortoiseSVN


Plugin Windows para ter acesso ao repositório através do Windows Explorer. É independente de IDE e os desenvolvedores podem ter acesso aos comandos do SVN de forma muito fácil. Somente este plugin é necessário nas estações de trabaho dos desenvolvedores mas pode ser instalado também no servidor para facilitar o uso do SVN.


Sobre o servidor VisualSVN


Existem diversos pacotes e plugins disponíveis ficando as vezes complicado a escolha de qual instalar. Ao implantar o Subversion optei por instalar o VisualSVN. O link direto pode ser encontrado em: http://www.visualsvn.com/visualsvn/download/.

A escolha do VisualSVN foi permitir gerenciar os usuários e criar o repositório em uma ferramenta visual, ter acesso aos comandos standalone svn e svnadmin quando necessário e ainda permitir gerenciar o repositório remotamente. Os comandos svn, svnadmin são muito úteis para administração do repositório via linha de comando. A Fig. 1 exibe a tela do VisualSVN.

Para instalar o servidor é preciso apenas executar seu setup. Um serviço será instalado chamado VisualSVN. Você pode verificar o serviço através do utilitário services.msc. Durante a instalação é necessário selecionar a porta do serviço ou deixar a porta padrão 443 se a opção SSL for selecionada.

Com o servidor instalado, já podemos abrir o aplicativo VisualSVN Server Manager e criar um repositório SVN.

Instalando o cliente TortoiseSVN


Apos instalado o VisualSVN, vamos instalar o plugin TortoseSVN. Através deste plugin podemos fazer praticamente qualquer atividade necessária: criar um repositório, criar um pasta de trabalho, etc. A intenção de utilizar o TortoiseSVN é aumentar a produtividade no dia-dia, pois não será necessário usar o prompt de comando para sincronizar os fontes com a equipe. Este plugin pode ser instalado tanto no servidor, se também for um ambiente de desenvolvimento como nas demais estações de trabalho.

Para instalar o TortoiseSVN basta executar o setup e sequir confirmando as opções do instalador. Com a instalação concluída perceba que se você clicar no botão direito do mouse verá o menu TortoiseSVN. É através deste menu que você tem acesso a todos os recursos do Subversion para gerenciar o seu SVN. A Fig. 2 exibe o menu com a opção TortoiseSVN e SVN Checkout....

Entendendo o Repositório


Pense no repositório como um banco de dados. Dentro do banco de dados você vai inserir (usamos o termo importar no SVN) a pasta raiz do seu projeto e então poderá através do TortoiseSVN criar uma pasta "local" em qualquer computador na sua rede exatamente igual a pasta importada do seu projeto. Esta pasta local será a Work Copying, ou seja, cópia de trabalho. Você, desenvolvedor, poderá trabalhar nela e alterar qualquer arquivo sem interferir no servidor SVN. Quando desejar poderá querer salvar (comitar) as alterações realizadas com o servidor, quando então, o Subversion vai persistir os arquivos alterados no repositório do projeto.

A cada commit, o Subversion gera uma nova versão. Através deste esquema de geração de versão, pode-se rapidamente "navegar" entre as versões do projeto com muita facilidade e sem medo de perda de dados, uma vez que estamos apenas trabalhando em uma cópia local.

O Subversion tem total controle do que a equipe de desenvolvimento está fazendo. Por exemplo, se dois usuários tentarem comitar um mesmo arquivo sendo alterado, o desenvolvedor será avisado que o arquivo foi alterado por mais de 1 usuário e oferecerá várias opções para eliminar o conflito. Em meus vários testes, obtive muito exito. De fato, o TortoiseSVN oferece uma quantidade enorme de ferramentas, incluindo ferramentas que mostram o que mudou de um arquivo para outro (Diffs).


Criando um Repositório


Com o servidor VisualSVN e o cliente TortoiseSVN instalados vamos criar um repositório no servidor para gerenciar um projeto. Para isto é preciso definir onde será o repositório. O repositório pode ser criado de varias maneiras:

• com o VisualSVN - No VisualSVN você pode criar o repositório facilmente. Será necessário criar pelo menos um usuário antes realizar a criação do repositório. Vários outros usuários podem ser criados para utilizar o SVN.

• com o TortoiseSVN - Através do menu TortoseSVN selecione a opção "Create repository here". A criação do repositório pelo Tortoise seria útil para um desenvolvedor. Para uma equipe use o VisualSVN.

• Pode-se usar a opção de linha de comando - svnadmin create d:\repo

Importando o projeto para o repositório


Para importar o projeto para o repositório navegue até a pasta do projeto e com a opção "Import" do TortoseSVN, informe o repositório onde será armazenado o projeto. Informe o caminho no seguinte formato: https://hostname/svn/nome_repositorio. Clique no botão Ok e aquarde o projeto ser importado para dentro do repositório. Após importado já é possível criar uma pasta de trabalho em qualquer estação de trabalho.

Criando a pasta de trabalho (Working Copy)


Novamente, utilizando o plugin TortoiseSVN, selecione a opção SVN Checkout em alguma pasta vazia, onde será definido a pasta de trabalho. Aguarde a criação de pasta de trabalho e após já pode começar a trabalhar nos fontes.

Pode-se utilizar a linha de comando. Segue um exemplo abaixo:
svn checkout https://pc01/svn/poligestor d:\Projetos\PoliGestor

Comitando as alterações no Repositório


Após qualquer alteração nos fontes do projeto, pode-ser, através da opção SVN Commit, comitar as alterações realizadas no repositório.

Atualizando a pasta de trabalho


Em uma equipe de desenvolvimento vários desenvolvedores estão trabalhando em um mesmo projeto. Para sincronizar a pasta de trabalho com o servidor faz-se um update através do TortoseSVN. A opção utiliada é SVN Update no menu.

Conclusão


O gerenciamento de projeto com SVN torna-se uma necessidade cada vez maior em projetos atuais, pois elimina quande parte da complexidade do gerenciamento e facilita o compartilhamento dos fontes. Espero que com este artigo poder ter ajudo a vários que já tiveram a idéia de implantar um SVN para uma equipe de desenvolvedores e não encontraram material que torna-se fácil esta tarefa.

Exemplo de Programa em JAVA usando JDBC para conectar ao ORACLE

O programa abaixo, demonstra os passos necessários para fazer conexão usando JDBC ao ORACLE.

import java.sql.*;
/*
 * Programa ConexaoOracle.java 
 * 
 * Demonstrar a utilização do driver JDBC para conexão ao ORACLE
 * 
 * Data: 06/01/2010
 * Autor: Everton de Vargas Agilar
 * 
 * Para executar este programa execute o seguinte no prompt de comando:
 *   java -classpath "c:\Arquivos de programas\java\jdk1.7.0\jdbc\ojdbc6.jar"; ConexaoOracle
 *
 *   Obs.: a opção -classpath informa o caminho do driver JDBC para o ORACLE.
 * 
 *  Biblioteca Necessária: Driver JDBC para Oracle
 *  http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/
 *  
 */

public class ConexaoOracle {

 public static void main(String[] args) {
  System.out.println("Conexão Oracle");
  
  Connection conn = null;
  Statement smnt = null;
  ResultSet rs = null;
  String sql = null;

  try {
   Class.forName("oracle.jdbc.OracleDriver");
  } catch (ClassNotFoundException e) {
   System.out.println("Driver para ORACLE não encontrado!\nErro interno: ");
   e.printStackTrace();
  }
  
  try {
   conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "gestorerp", "gestorerp");
  } catch (SQLException e) {
   System.out.println("Erro ao obter conexão ao banco de dados XE\nErro interno: ");
   e.printStackTrace();
  }
  
  System.out.println("Conexão bem sucedida!");
  
  try {
   smnt = conn.createStatement();
  } catch (SQLException e) {
   System.out.println("Erro ao criar instrução SQL\nErro interno: ");
   e.printStackTrace();
  }
  
  sql = "select * from sequence";
  try {
   rs = smnt.executeQuery(sql);
   while (rs.next()){
    System.out.println("SEQ_NAME "+ rs.getString("SEQ_NAME"));
   }
   
  } catch (SQLException e) {
   System.out.println("Ocorreu erro ao executar sql!\nErro interno: ");
   e.printStackTrace();
  }
  
  try {
   conn.close();
  } catch (SQLException e) {
   System.out.println("Ocorreu erro ao terminar a conexão ao banco de dados!\nErro interno: ");
   e.printStackTrace();
  }
  
  System.out.println("FIM...");
 }
}

Dor de cabeça com -classpath com apenas 1 biblioteca

Hoje me deparei com uma situação interessante que pode ser uma dor de cabeça para quem está informando as bibliotecas na opção de linha de comando -classpath ou -cp da máquina virtual Java.

Acontece que precisei informar apenas uma biblioteca para meu aplicativo e o seguinte erro estava ocorrendo: Error: Could not find main class MeuAplicativo

Percebi que ao informar um ; no final da string dos caminhos o erro não acontecia. Veja abaixo as linhas de comando com erro e sem erro:

• Este comando não executa:

C:\>java -classpath "c:\Arquivos de programas\java\jdk1.7.0\jdbc\ojdbc6.jar" MeuAplicativo
Error: Could not find main class MeuaAplicativo
Exception in thread "main" java.lang.NoClassDefFoundError: MeuaAplicativo
        at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:198)
Caused by: java.lang.ClassNotFoundException: MeuaAplicativo
        at java.net.URLClassLoader$1.run(URLClassLoader.java:299)
        at java.net.URLClassLoader$1.run(URLClassLoader.java:288)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(URLClassLoader.java:287)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:422)
        at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:325)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:355)
        at sun.launcher.LauncherHelper.checkAndLoadMain(LauncherHelper.java:195)

• Este comando executa corretamente porque informei o ";" no final da string do caminho:

C:\>java -classpath "c:\Arquivos de programas\java\jdk1.7.0\jdbc\ojdbc6.jar"; MeuAplicativo

• Este comando também funciona:

C:\>java -classpath "c:\Arquivos de programas\java\jdk1.7.0\jdbc\ojdbc6.jar";. MeuAplicativo

Ainda bem que logo ao tentar executar o comando pela segunda vez coloquei o ";". Isso poderia ser uma dor de cabeça sem tamanho, você sabe que a sintaxe do comando está certo mas a máquina virtual não consegue executar o aplicativo. Talvez isso seja o fato do parser do programa java.exe não saber tratar bem o -classpath com apenas uma biblioteca.

Artigo sobre Bloqueios no ORACLE

O artigo de Hudson Santos fala sobre concorrência e consistência de dados explicando como usar a view v$locked_object para saber quais sãos os objetos que estão bloqueados pelos usuários. Através desta visão é possível ao DBA saber, por exemplo, quais tabelas estão bloqueadas, identificar o usuário e a sessão que está bloqueando algum recurso, para que possa ser finalizada com um alter system kill session.

Como se sabe, o mecanismo de lock dos bancos de dados relacionais são utilizados para resolver problemas de integridade, consistência e concorrência de dados, fazendo com que as transações não consigam atualizar os mesmos dados ao mesmo tempo, ou seja, somente uma transação pode, em determinado momento alterar algum recurso, a outra transação terá de aguardar.

Artigos sobre planos de acesso

Segue uma lista de excelentes artigos encontrados sobre planos de acesso para Oracle 10g:

http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php
http://www.oracle-base.com/articles/9i/DBMS_XPLAN.php

10g - Obtendo os últimos 10 waits de uma sessão

Para obter uma lista dos últimos 10 waits que ocorreram para uma sessão específica
utilize a view v$session_wait_history.

Por exemplo:

EVENT                                  P1         P2         P3  WAIT_TIME WAIT_COUNT                                   
------------------------------ ---------- ---------- ---------- ---------- ----------                                   
reliable message                790947952  790944384  791577936          0          1                                   
db file sequential read                 1      15004          1          1          1                                   
db file sequential read                 1      13117          1          1          1                                   
db file sequential read                 1      14938          1          0          1                                   
db file sequential read                 1        879          1          1          1                                   
SQL*Net message to client      1111838976          1          0          0          1                                   
SQL*Net message from client    1111838976          1          0          0          1                                   
SQL*Net message to client      1111838976          1          0          0          1                                   
log file sync                        1805          0          0          0          1                                   
enq: RO - fast object reuse    1380909062      65554          1          1          1                                   

10 linhas selecionadas.

Ajustando buffer_cache, shared pool e log_buffer

Artigo da imaster sobre ajustes de memória em http://imasters.uol.com.br/artigo/5721/oracle/ajustando_o_buffer_cache_shared_pool_e_o_log_buffer/

Como obter somente a data no ORACLE

Esta dica é para obter somente a parte da data em um campo do tipo timestamp ou date do ORACLE, pois o mesmo armazena a data e a hora juntos.

Em uma expressão SQL, na parte where podemos encontrar as seguintes situações:

Situação 1: Não é o que você quer!

where DATA_COMPRA = TRUNC(SYSDATE)

ou

Situação 2: Funciona mas não usa índice!

where TRUNC(DATA_COMPRA) = TRUNC(SYSDATE)

ou

Situação 3: A forma correta

DATA_COMPRA BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 1 - 1/24/60/60

Explicação:

As três situações vão executar sem erro do Oracle, mas a primeira pode não retornar todos os registros pois existe a parte da hora armazenado no campo, o que faz com que a comparação resulte em True somente para os campos exatamente iguais em data e hora.

A segunda situação impede que o ORACLE utilize índices para agilizar as pesquisas. Mesmo se um índice for criado na coluna DATA_COMPRA, o ORACLE não poderá utilizar.


OBS.: Existe um tipo de índice baseado em função que permite que o ORACLE utilize um índice, mas não é interessante sobrecarregar o ORACLE com este tipo de situação.


A terceira situação, é a única forma de realizar a consulta de forma rápida, pois o ORACLE vai conseguir utilizar um índice, se existir é claro. A metodologia consiste em verificar se a data está no período compreendido entre a meia noite até o último segundo do dia.

Trabalhando com Tablespaces

Excelente artigo que mostra comandos e sintaxes para trabalharmos com tablespaces pode ser acesso em Oracle Tablespaces

Verificar códigos de erro do ORACLE

http://www.ora-code.com/

Configurar Linux para o Oracle

http://www.puschitz.com/TuningLinuxForOracle.shtml