Funções de valores simples:

ABS(n) = Devolve o valor absoluto de (n).CEIL(n) =Obtém o valor inteiro imediatamente superior ou igual a “n”.FLOOT(n) = Devolve o valor inteiro imediatamente inferior ou igual a “n”.MOD (m, n) = Devolve o resto resultante de dividir “m” entre “n”.NVL (valor, expressão) = Substitui um valor nulo por outro valor.POWER (m, exponente) = Calcula a potência de um número.ROUND (numero [, m]) = Arredonda números com o número de dígitos de precisão indicados.SIGN (valor) = Indica o signo do “valor”.SQRT(n) = Devolve a raiz quadrada de “n”.TRUNC (numero, [m]) = Trunca números para que tenham uma certa quantidade de dígitos de precisão.VAIRANCE (valor) = Devolve a média de um conjunto de valores.

Funções de grupos de valores:

AVG(n ) = Calcula o valor médio de “n” ignorando os valores nulos.COUNT (* | Expressão) = Conta o número de vezes que a expressão avalia algum dado com valor não nulo. A opção “*” conta todas as filas selecionadas.MAX (expressão )= Calcula o máximo.MIN (expressão) = Calcula o mínimo.SUM (expressão) = Obtém a soma dos valores da expressão.GREATEST (valor1, valor2…)= Obtém o maior valor da lista.LEAST (valor1, valor2…) = Obtém o menor valor da lista.

Funções que devolvem valores de caracteres:

CHR(n) = Devolve o caractere cujo valor em binário é equivalente a “n”.CONCAT (cad1, cad2) = Devolve “cad1″ concatenada com “cad2″.LOWER (cad) = Devolve a cadeia “cad” em minúsculas.UPPER (cad) = Devolve a cadeia “cad” em maiúsculas.INITCAP (cad) = Converte a cadeia “cad” a tipo título.LPAD (cad1, n[,cad2]) = Adiciona caracteres à esquerda da cadeia até que tenha uma certa longitude.RPAD (cad1, n[,cad2]) = Adiciona caracteres à direita até que tenha uma certa longitude.LTRIM (cad [,set]) = Suprime um conjunto de caracteres à esquerda da cadeia.RTRIM (cad [,set]) = Suprime um conjunto de caracteres à direita da cadeia.REPLACE (cad, cadeia_busca [, cadeia_substitucao])= Substitui um caractere ou caracteres de uma cadeia com 0 ou mais caracteres.SUBSTR (cad, m [,n]) = Obtém parte de uma cadeia.TRANSLATE (cad1, cad2, cad3)= Converte caracteres de uma cadeia em caracteres diferentes, segundo um plano de substituição marcado pelo usuário.

Funções que devolvem valores numéricos:

ASCII(cad) = Devolve o valor ASCII da primeira letra da cadeia “cad”.INSTR (cad1, cad2 [, comeco [,m]])= Permite uma busca de um conjunto de caracteres em uma cadeia, mas não suprime nenhum caractere depois.LENGTH (cad) = Devolve o número de caracteres de cad.

Funções para o manejo de datas:

SYSDATE= Devolve a data do sistema.ADD_MONTHS (data, n) = Devolve a data “data” incrementada em “n” meses.LASTDAY (data)= Devolve a data do último dia do mês que contém “data”.MONTHS_BETWEEN (data1, data2)= Devolve a diferença em meses entre as datas “data1″ e “data2″.NEXT_DAY (data, cad) = Devolve a data do primeiro dia da semana indicado por “cad” depois da data indicada por “data”.

Funções de conversão:

TO_CHAR = Transforma um tipo DATE ou NUMBER em uma cadeia de caracteres.TO_DATE = Transforma um tipo NUMBER ou CHAR em DATE.TO_NUMBER = Transforma uma cadeia de caracteres em NUMBER.

Outras dicas sobre manipulação de datas

Uma das maiores dificuldades com relação ao gerenciamento e manipulação de bases de dados está relacionado a manipulação de datas, no PL-SQL do Oracle, acho que isso não é um problema. Irei começar com a conversão de string para data (uso da função TO_DATE), mas no Oracle existem várias outras funções de data que são muito fáceis para manipular datas. Abordarei as mesmas em outros artigos.

O TO_DATE recebe 1 parametro obrigatório e dois opcionais como se segue:

to_date( string1, [ format_mask ], [ nls_language ] )

O parametro “string1″ obviamente é a string com o valor que será convertido para a data, ou seja ’01/01/2011′, ou ’2011/01/01′ e muitos outros possíveis dependendo do formato que está configurado no Oracle ou então do formato que você especificar no parametro “format_mask”.

O último parametro, quase não é usado, ele é para definir a linguagem para assim obter qual é o formato padrão daquela lingua.

O parametro “format_mask” será um string com um padrão definido, o qual deve seguir as seguintes opções:

Parametro Explicação

YEAR Ano por extenso

YYYY Ano com 4 digitos

YYYYYY

Ultimos 3, 2 e 1 digitos do ano.

IYYIYI

Ultimos 3, 2 e 1 digitos do ano ISO.

IYYY Ano ISO com 4 digitos

RRRR

Esse foi o modo de se proteger do “bug do milenio”, ele aceita dois digitos de ano se for abaixo de 49 assume como 20XX, se for acima de 49 assume como 19XX.

QTrimestre (1 = JAN-MAR, 2 = ABR-JUN, 3 = JUL-SET, 4 = OUT-DEZ).

MM Mes (01-12; JAN = 01).

MON Mes por extenso abreviado.

MONTH Nome do mes por extenso.

RM Mes em algarismos romanos.

WW Semana do ano.

W Semana do mes.

IW Semana do ano com base no Ano ISO.

D Dia da semana.

DAY Dia da semana por extenso.

DD Dia do mes.

DDD Dia do ano.

DY Abreviação do nome do dia.

JJulian day; O número do dia desde 1, de Janeiro de 4712 Antes de Cristo.

HH Hora do dia no formato 1-12.

HH12 Hora do dia no formato 1-12.

HH24 Hora do dia no formato 0-23.

MI Minuto.

SS Segundos.

SSSSS Segundos passados da meia-noite.

FFMilisegundos, indique a quantidade de digitos na frente do FF, por exemplo 4 digitos seria FF4.

AM, A.M., PM, ou P.M.Indicador de hora (Antes do Meio-dia, Pos meio dia)

TZH Hora com fuso.

TZM Minuto com fuso.

TZR Fuso.

 

Com todos esses padrões você pode usar tanto para obter uma informação, como para formatar uma data. No caso do TO_DATE, será só para formatar a data, ou seja, dizer o que você quer que a data contenha. Vamos a exemplos:

Atribuindo uma data simples:

view source

print ? 1 select to_date('01/01/2011','DD/MM/YYYY') from dual;

Veja no código que inclusive a string ‘/’ usada na separação também é utilizada na máscara, funcionando como uma máscara padrão. Esse exemplo irá retornar uma data no formato oracle, com dia, mes e ano informados e horas, minutos zerados.

Formatando uma data com hora:

view source

print ? 1 select to_date('01/01/2011 21:10:05','DD/MM/YYYY HH24:MI:SS') from dual;

Nesse exemplo, já mostra a adição de hora, minuto e segundo.

Creio que com esses exemplos e a tabela de padrões, seja suficiente para fazer várias outras combinações, caso tenha alguma dúvida, por favor é só fazer nos comentários que terei prazer em responder.

Funções - Functions no Oracle

segunda-feira, julho 20th, 2009

Ao contrário das procedures as Funções (ou Functions) são blocos PL/SQL que devem obrigatoriamente retornar ao menos um valor. Uma função manipula o conteúdo de uma coluna em uma declaração SQL. Ao se usar uma função em uma declaração SQL, o valor da coluna sobre o qual a função é executada é alterado quando apresentado.

Sintaxe Básica:CREATE [OR REPLACE] FUNCTION nome_da_função[( parameter1 [ mode1] datatype1,parameter2 [ mode2] datatype2,. . .)]RETURN tipo_de_dadoIS|ASBloco PL/SQL;Ao contrário das procedures as funções tem que retornar ao menos um valor.CREATE OR REPLACE FUNCTION pega_sal(p_id IN emp.empno%TYPE)RETURN NUMBERISv_sal emp.sal%TYPE :=0;BEGINSELECT salINTO v_salFROM scott.empWHERE empno = p_id;RETURN v_sal;END pega_sal;/ExecutandoVARIABLE g_sal NUMBER

EXECUTE :g_sal := pega_sal(7839)

PRINT g_salFunção para calcular CPMFCREATE OR REPLACE FUNCTION cpmf(p_value IN NUMBER)RETURN NUMBER ISBEGINRETURN (p_value * 0.038);END cpmf;/SELECT empno, ename, sal, cpmf(sal)

FROM scott.empWHERE deptno = 10;

Oracle - Trigger

segunda-feira, julho 13th, 2009

Boa tarde pessoal,

Como foi o final de semana? Deu para descansar um pouco? Espero que sim! Vamos retomar nossos estudos?Você ja ouviu falar em “gatilho do Banco de dados” ou mais conhecido como triggers?Então vamos lá:

Triggers são procedimentos que podem ser gravados em Java, PL/SQL ou C. São executados (ou disparados) implicitamente quando uma tabela é modificada, um objeto é criado ou ocorrem algumas ações de usuário ou de sistema de banco de dados.

As triggers são similares as stored procedures diferindo, apenas, na maneira como são chamadas. A trigger é executada implicitamente quando ocorre algum evento de trigger enquanto a stored procedure deve ser executado explicitamente.

Uma trigger é composta por quatro partes:

- Momento- Evento- Tipo- Corpo

O momento define quando uma trigger irá ser acionada. Pode ser:

- BEFORE (tabela)- AFTER (tabela)- INSTEAD OF (view)

BEFORE indica que os comandos PL/SQL do corpo da trigger serão executados ANTES dos dados da tabela serem alterados. Normalmente usamos BEFORE nos casos em que precisamos incializar variáveis globais, validar regras de negócios, alterar o valor de flags ou para salvar o valor de uma coluna antes de alterarmos o valor delas. Exemplo:

CREATE OR REPLACE TRIGGER novo_funcBEFORE…...END;/

AFTER indica que os comando PL/SQL do corpo da trigger será executado APÓS os dados da tabela serem alterados. Normalmente usamos AFTER para completar os dados de outras tabelas e para completar a atividade de outra trigger de momento BEFORE. Exemplo:

CREATE OR REPLACE TRIGGER novo_funcAFTER…...

END;/

INSTEAD OF indica que a trigger irá ser executada no lugar da instrução que disparou a trigger. Literalmente, a instrução é substituída pela trigger. Essa técnica permite que façamos, por exemplo, alterações em uma tabela através de uma view. É usado nos casos em que a view não pode alterar uma tabela por não referenciar uma coluna com a constraint not null. Nesse caso a trigger pode atualizar a coluna que a view não tem acesso.

Dois detalhes muito importantes sobre INSTEAD OF:

- Só funcionam com views e- É sempre de linha. Será considerado assim, mesmo que “FOR EACH ROW” for omitido.

Exemplo:

CREATE OR REPLACE TRIGGER novo_funcINSTEAD OF INSERT ON vempFOR EACH ROWWHEN …...END;/

O evento define qual é a instrução DML que aciona a trigger. Informa qual instrução SQL irá disparar a trigger. Pode ser:

- INSERT- UPDATE- DELETE

Quando o evento for um UPDATE podemos informar quais colunas que, ao serem alteradas, irão disparar a trigger. O mesmo NÃO ocorre com INSERT e DELETE porque essas instruções sempre afetam a linha por inteiro. Exemplo:

CREATE OR REPLACE TRIGGER novo_funcAFTER INSERT ON emp...END;/

O evento pode conter uma, duas ou todas as três operações DML em uma única linha de comando. Exemplo:

CREATE OR REPLACE TRIGGER novo_funcBEFORE INSERT OR UPDATE OR DELETE ON emp...END;/

O tipo define quantas vezes uma trigger será executada. A trigger pode ser executada uma vez para a instrução que a disparou ou ser disparada para cada linha afetada pela instrução que disparou a trigger. Pode ser:

- Instrução (STATEMENT)- Linha (ROW)

Quando a trigger for do tipo instrução ela será disparada uma vez para cada evento de trigger, mesmo que nenhuma linha tenha sido afetada. São úteis para aquelas trigger que eventualmente não alteram dados ou para situações onde o que queremos é uma resposta da trigger, por exemplo, em uma restrição complexa de negócio. Por DEFAULT toda trigger é deste tipo. Exemplo:

CREATE OR REPLACE TRIGGER novo_funcBEFORE INSERT OR UPDATE OR DELETE ON empFOR EACH STATEMENT...END;/

Quando a trigger for do tipo linha, a trigger será executada toda vez que a tabela for afetada pelo evento da trigger. Se nenhuma linha for afetada a trigger não será executada. São muito úteis quando a ação da trigger depende dos dados afetados pelo evento da trigger. Exemplo:

CREATE OR REPLACE TRIGGER novo_funcBEFORE INSERT OR UPDATE OR DELETE ON empFOR EACH ROW...END;/

O corpo define a ação que uma trigger irá executar quando acionada. O corpo de uma trigger é composto por um bloco PL/SQL, a chamada de uma PROCEDURE ou por um procedimento JAVA. Por definição, o tamanho de uma trigger não pode ultrapassar 32K.

Como, normalmente, precisamos trabalhar com os valores antes e depois da alteração dos dados, a trigger permite que façamos referencia aos valores antes da alteração (OLD) e após a alteração (NEW).

O nome de uma trigger deve ser único dentro de um mesmo esquema, e sua sintaxe básica é:

CREATE [OR REPLACE] TRIGGER [schema.] nome_da_trigger[BEFORE|AFTER][DELETE|OR INSERT|OR UPDATE[OF coluna]]ON [schema.] nome_da_tabela_ou_da_view[REFERENCING [OLD [AS] OLD] [NEW [AS] NEW][FOR EACH ROW][WHEN [condição]]BLOCO PL/SQL

Onde:

Nome_da_trigger é o nome da trigger;

Nome_da_tabela_ou_da_view indica a tabela ou view associada com a trigger;

Corpo_da_trigger é a ação que a trigger irá executar. Inicia por DECLARE ou BEGIN e termina por END. Também pode conter a chamada de um procedimento.

O uso do nome da coluna na cláusula UPDATE pode aumentar a performance porque a trigger só será disparada quando aquela coluna especificada na cláusula for alterada.

Agora que sabemos como criar uma trigger veremos um exemplo completo:

Primeiro vamos criar uma tabela para gravar um registro de todos os usuários que se conectaram ao banco:

CREATE TABLE vigia(marca VARCHAR2(100));

CREATE OR REPLACE TRIGGER marca_logonAFTER LOGON ON DATABASEBEGININSERT INTO sys.vigiaVALUES (USER || ‘ entrou no sistema em ‘ ||TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS’));COMMIT;END;/

Pronto, temos nossa primeira trigger. Ela registra o nome do usuário e a que horas ele entrou. Esse exemplo foi retirado diretamente da documentação Oracle. No nosso exemplo fazemos referencia a um evento do sistema ao invés de referenciarmos uma tabela. Outros eventos do sistema são:

- AFTER SERVERERROR- AFTER LOGON- BEFORE LOGOFF- AFTER STARTUP- BEFORE SHUTDOWN

Você pode criar triggers usando os eventos acima para DATABASE e SCHEMA. As duas exceções são SHUTDOWN e STARTUP que só se aplicam a DATABASE. Exemplo:

CREATE OR REPLACE TRIGGER marca_logoffBEFORE LOGOFF ON SCHEMABEGININSERT INTO sys.vigiaVALUES (USER || ‘ saiu do sistema em ‘ ||TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS’));COMMIT;END;/

Eventualmente podemos ter algum tipo de erro em nossa trigger. Para verificar quais são os erros de compilação que temos na trigger basta usar o comando SHOW ERRORS TRIGGER nome_da_trigger. Caso você queira ver os erros de compilação da última trigger que você compilou pode escrever apenas SHOW ERRORS ou SHO ERR. Ao executarmos esse comando ele mostrará a linha onde está o erro. Atenção: caso a linha onde está o erro se estenda por mais de uma linha, este comando indicará o início da linha. Vamos criar uma trigger com erro para servir como exemplo:

CREATE OR REPLACE TRIGGER marca_logonAFTER LOGON ON DATABASEBEGININSERT INTO sys.vigiaVALUES (USER || ‘ entrou no sistema em ‘ ||TO_CHAR(sysdate, ‘DD-MM-YYYY HH24:MI:SS));COMMIT;

END;/

Gatilho criado com erro de compilação

Qual é o erro desse gatilho? É um erro bem banal, no caso deixamos de fechar a apóstrofe (ou aspas simples ou quote) no final da instrução TO_CHAR. Ao executarmos o SHOW ERROR ele irá mostrar que houve um erro na linha 4. Isso porque ele aponta onde a linha que contem o erro começou a ser escrita e não a linha onde efetivamente ocorreu o erro está.

Caso precise de mais informações sobre sua trigger, a view USER_TRIGGERS pode fornecer informações muito úteis. Exemplo:

SELECT trigger_nameFROM user_triggers;

Com o nome da trigger que você deseja analisar execute o comando:

SELECT trigger_type, table_name, triggering_eventFROM user_triggersWHERE trigger_name = ‘nome_da_trigger’;

Ou, se precisar obter o código usado para gerar a trigger:

SELECT trigger_name, trigger_type, triggering_event,table_name, referencing_names,status, trigger_bodyFROM user_triggersWHERE trigger_name = ‘nome_da_trigger’;

Caso descubra que não precisa mais da trigger existe duas formas de tratar a situação. Eliminar a trigger ou desabilitá-la.

Eliminando a trigger:

DROP TRIGGER nome_da_trigger;

Caso prefira apenas desabilitar a trigger use o comando:

ALTER TRIGGER nome_da_trigger DISABLE;

Quando a trigger é criada pela primeira vez ela é habilitada automaticamente. Para habilitar a trigger novamente basta usar o comando:

ALTER TRIGGER nome_da_trigger ENABLE;

Mas vamos continuar criando nossas triggers. O próximo caso vai nos ajudar a impedir que alguém cadastre um funcionário fora do horário de expediente:

CREATE TABLE nova_empAS SELECT * FROM SCOTT.EMP;

CREATE OR REPLACE TRIGGER hora_expBEFORE INSERT ON nova_empBEGINIF (TO_CHAR(sysdate,’DY’) IN (’SAB’,'DOM’)) OR(TO_CHAR(sysdate,’HH24:MI’)

NOT BETWEEN ‘08:30′ AND ‘17:30′)THEN RAISE_APPLICATION_ERROR (-20500,’Você só podeatualizar os empregados no horário deexpediente’);END IF;END;/

Essa trigger pode ser refinada para testar os predicados condicionais. Por exemplo:

CREATE OR REPLACE TRIGGER hora_expBEFORE INSERT OR UPDATE OR DELETE ON nova_empBEGINIF (TO_CHAR(sysdate,’DY’) IN (’SAB’,'DOM’)) OR(TO_CHAR(sysdate,’HH24:MI’)NOT BETWEEN ‘08:30′ AND ‘17:30′)THENIF DELETING THENRAISE_APPLICATION_ERROR (-20500,’Você só podeexcluir empregados no horário de expediente’);ELSIF INSERTING THENRAISE_APPLICATION_ERROR (-20502,’Você só podeincluir empregados no horário de expediente’);ELSIF UPDATING (’SAL’) THENRAISE_APPLICATION_ERROR (-20504,’Você só podealterar salarios no horário de expediente’);ELSERAISE_APPLICATION_ERROR (-20506,’Você só podeFazer alterações no horário de expediente’);END IF;END IF;END;/

Vamos ver como usar valores OLD e NEW:

Primeiro vamos criar uma tabela para conter os dados do nosso histórico.

CREATE TABLE DDUR(USUARIO VARCHAR2(15),HORARIO DATE,EMPNO NUMBER(4),ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2))/

Agora vamos criar nossa trigger. Ela deve registrar tudo o que fizermos em nossa tabela.

CREATE OR REPLACE TRIGGER hist_empAFTER INSERT OR UPDATE OR DELETE ON nova_empFOR EACH ROWBEGIN

INSERT INTO ddur VALUES(user, sysdate, :OLD.empno, :OLD.ename, :OLD.job,:OLD.mgr, :OLD.hiredate, :OLD.sal, :OLD.comm, :OLD.deptno);END;/

A referência :OLD indica que estamos usando os valores antes da alteração. Caso quiséssemos usar o valor atualizado a referencia seria :NEW. Ambas podem ser usadas na mesma trigger. Por exemplo, nossa trigger poderia ter sido escrita assim:

CREATE OR REPLACE TRIGGER hist_empAFTER INSERT OR UPDATE OR DELETE ON nova_empFOR EACH ROWBEGININSERT INTO ddur VALUES(user, sysdate, :NEW.empno, :NEW.ename, :OLD.JOB,:NEW.MGR, :OLD.HIREDATE, :OLD.sal, :OLD.comm, :OLD.deptno);END;/

Você pode usar :OLD e :NEW em comparações dentro da sua trigger, montando estruturas PL/SQL cada vez mais complexas. Por exemplo:

CREATE OR REPLACE TRIGGER aumentoBEFORE UPDATE OF sal ON empFOR EACH ROWBEGINIF (:NEW.sal - :OLD.sal) < :OLD.sal * 0.025THENRAISE_APPLICATION_ERROR (-20512, ‘Favor corrigir indice’);END IF;END;

No caso acima, se o aumento de salário for inferior a 2,5% o sistema avisa que houve um erro na alteração salarial. Em um outro exemplo, mas agora com WHEN

CREATE OR REPLACE TRIGGER ver_salBEFORE INSERT OR UPDATE OF sal, jobON emplFOR EACH ROWWHEN (NEW.job_id ‘PRESIDENT’)DECLAREv_minsal emp.sal%TYPE;v_maxsal emp.sal%TYPE;BEGINSELECT MIN(sal), MAX(sal)INTO v_minsal, v_maxsalFROM empWHERE job = :NEW.job;IF :NEW.sal v_maxsal THENRAISE_APPLICATION_ERROR(-20515,’Salario inválido’);END IF;END;/

UPDATE empSET sal = 3400WHERE ename = ‘BLAKE’;

Neste caso estamos garantido que ninguém que for contratado com o cargo diferente de PRESIDENT irá receber um salário menor que o menor salário de seu cargo ou um salário maior que o maior salário de seu cargo.

Uma trigger pode ser bem mais simples do que os exemplos acima. Por exemplo, se quisermos implementar uma restrição onde o salário do funcionário nunca possa ser reduzido, basta aplicarmos a trigger:

CREATE OR REPLACE TRIGGER veri_salBEFORE UPDATE OF sal ON empFOR EACH ROWWHEN (NEW.sal < OLD.sal)BEGINRAISE_APPLICATION_ERROR (-20508,‘O salário não pode ser reduzido’);END;/

Para que um usuário crie suas próprias triggers ele precisa ter o privilégio de sistema CREATE TRIGGER e ser o proprietário da tabela onde irá criar a trigger. Caso não seja proprietário ele deve ter o privilégio de sistema ALTER ou ALTER ANY TABLE. Caso precise criar triggers para eventos do banco de dados deve ter o privilégio de ADMINISTER DATABASE TRIGGER. Caso a trigger faça chamada de alguma procedure, quem estiver criando a trigger deve ter o privilégio de EXECUTE na procedure.

Como podemos notar as trigger podem ser usadas de forma bem flexível. Com elas podemos gerar mecanismos de segurança mais flexíveis, auditar dados de tabelas e implementar regras de negócios com mais facilidade.

Fonte: http://www.linhadecodigo.com.br/ArtigoImpressao.aspx?id=322Autor: Milton Goya

Abraços galera!

Postado em PL/SQL | 1 Comentário »

Procedimentos (stored procedure)

quarta-feira, julho 8th, 2009

Boa tarde pessoal,

Resolvi dedicar este post a um assunto muito importante dentro de PL/SQL e importantíssimo para quem deseja trabalhar com Oracle: Procedure. Ainda sente dúvida em Procedure? Então leia este post:

Procedimentos (stored procedure)

Uma procedure nada mais é que um bloco PL/SQL nomeado que pode aceitar argumentos (também chamado de parâmetros) e pode ser chamada por um programa, uma sessão SQL ou uma trigger.Durante a instalação do banco de dados Oracle um script é executado automaticamente e cria toda a estrutura necessária para que as procedures sejam executadas. Eventualmente esse procedimento automático pode falhar devido a alguma falha física no disco rígido, nesse caso o usuário SYS pode recriar a estrutura através do script SQL DBMSSTDX.SQL.Para criar uma procedure o usuário precisa ter o privilégio de sistema CREATE PROCEDURE, para criar a procedure em outros schemas o usuário deve ter o privilégio de CREATE ANY PROCEDURE. Este é um ponto muito interessante sobre as procedures, os privilégios para criação de procedures têm que concedidos

explicitamente, ou seja, não pode ser adquirido através de roles.Para executar uma procedure externa é necessário ter o privilégio de EXECUTE. Caso queira alterar a procedure de outro schema deve ter o privilégio de sistema ALTER ANY PROCEDURE.

A sintaxe básica de uma procedure é:CREATE [OR REPLACE] PROCEDURE [schema.]nome_da_procedure[(parâmetro1 [modo1] tipodedado1,parâmetro2 [modo2] tipodedado2,…)]IS|ASBloco PL/SQL

Onde:REPLACE - indica que caso a procedure exista ela será eliminada e substituída pela nova versão criada pelo comando;BLOCO PL/SQL - inicia com uma cláusula BEGIN e termina com END ou END nome_da_procedure;NOME_DA_PROCEDURE - indica o nome da procedure;PARÂMETRO - indica o nome da variável PL/SQL que é passada na chamada da procedure ou o nome da variável que retornará os valores da procedure ou ambos. O que irá conter em parâmetro depende de MODO;MODO - Indica que o parâmetro é de entrada (IN), saída (OUT) ou ambos (IN OUT). É importante notar que IN é o modo default, ou seja, se não dissermos nada o modo do nosso parâmetro será, automaticamente, IN;TIPODEDADO - indica o tipo de dado do parâmetro. Pode ser qualquer tipo de dado do SQL ou do PL/SQL. Pode usar referencias como %TYPE, %ROWTYPE ou qualquer tipo de dado escalar ou composto. Atenção: não é possível fazer qualquer restrição ao tamanho do tipo de dado neste ponto.IS|AS - a sintaxe do comando aceita tanto IS como AS. Por convenção usamos IS na criação de procedures e AS quando estivermos criando pacotes.BLOCO PL/SQL - indica as ações que serão executadas por aquela procedure.

Vamos ver um exemplo de procedure para ajudar nosso entendimento:CREATE OR REPLACE PROCEDURE aumenta_sal(p_empno IN emp.empno%TYPE)ISBEGINUPDATE scott.empSET sal = sal * 1.10WHERE empno = p_empno;END aumenta_sal;/

Neste exemplo estamos criando uma procedure para aumentar o salário de um funcionário em 10%. A primeira linha define o NOME DA PROCEDURE, que vai ser AUMENTA_SAL.A linha dois define o parâmetro P_EMPNO no modo IN. Ou seja, vai ser um dado informado na chamada da procedure. Em seguida determinamos que ele será do mesmo tipo e tamanho que a coluna EMPNO da tabela EMP. Isso é feito através da referencia EMP.EMPNO%TYPE.

Podemos verificar o estado de nossa procedure através de uma simples consulta:SELECT object_name, statusFROM user_objectsWHERE object_name LIKE ‘%AUMENTA%’;Agora podemos verificar o funcionamento de nossa procedure:SELECT empno, salFROM scott.emp;

EMPNO SAL———- ———-7839 5000

7698 28507782 2450

CALL AUMENTA_SAL(7839);OuEXECUTE AUMENTA_SAL(7839);

SELECT empno, salFROM scott.emp;

EMPNO SAL———- ———-7839 55007698 28507782 2450

Podemos notar que o salário do funcionário 7839 aumentou em 10%. É interessante notar que neste momento é possível executar a instrução ROLLBACK;É possível desfazer as alterações porque os dados passados através dos modos OUT e IN OUT são registrados no arquivo de redo log e no segmento de rollback. Isso é perfeito quando trabalhamos com parâmetros pouco extensos, mas pode causar impacto no sistema quando trabalhamos com parâmetros extensos como, por exemplo, um registro ou um VARRAY. Para resolver esse problema podemos usar a opção de NOCOPY. Nossa procedure ficaria assim com a opção NOCOPY:

CREATE OR REPLACE PROCEDURE aumenta_sal(p_empno IN OUT NOCOPY emp.empno%TYPE)ISBEGINUPDATE scott.empSET sal = sal * 1.10WHERE empno = p_empno;END aumenta_sal;/

Com nossa alteração o valor passado em nosso parâmetro não é gravado no arquivo de redo log e nem no segmento de rollback. Isso implica que, neste caso, NÃO É POSSÍVEL FAZER ROLLBACK. A documentação Oracle afirma que há ganho de performance de 30% a 200% nos casos em que tabelas PL/SQL eram passadas como parâmetro na procedure.Notem que a procedure pôde ser chamada através do comando CALL quanto pelo comando EXECUTE. Isso ocorre porque uma procedure pode ser chamada a partir de qualquer uma das ferramentas de desenvolvimento Oracle como, por exemplo, o SQL*Plus. Uma das vantagens das procedures é que elas podem ser chamadas a partir de uma aplicação, de outra procedure, de uma trigger e até mesmo a partir de uma simples query.Exemplo:

BEGINAUMENTA_SAL(7839);END;/

Durante a criação de nossa procedure pode ocorrer algum erro. Nesse caso será mostrada uma mensagem semelhante a esta:Aviso: Procedimento criado com erros de compilação.

Ou

MGR-00072: Warning: Procedure AUMENTA_SAL created with compilation errorsNesse caso o erro pode ser determinado através do SHOW ERROR pode ser usado para listar a linha/coluna

onde o erro ocorreu. O comando SHOW ERROR sem parâmetros adicionais mostra os erros da última compilação. Podemos qualificar o comando usando o nome de nosso pacote, procedure, função, trigger ou corpo de pacote.Por exemplo:

SHOW ERROR aumenta_salOuSHOW ERROR PROCEDURE aumenta_sal

Vamos criar uma procedure com erro para ver como o comando funciona:CREATE OR REPLACE PROCEDURE mand_embora(emp_num NUMBER) ISBEGINDELETE FROM empWHER empno = emp_num;END/

Notem que falta a letra E em WHERE e falta um ponto-e-vírgula no final de END. Ao executarmos o SHOW ERROR teremos:SQL> SHOW ERRORErros para PROCEDURE MAND_EMBORA:

LINE/COL ERROR——– —————————————————-5/14 PLS-00103:Encontrado o símbolo “EMPNO” quando um dosseguintes símbolos era esperado:; return returning whereO símbolo “where” foi substituído por “EMPNO” paracontinuar.

7/0 PLS-00103: Encontrado o símbolo “end-of-file” quandoum dos seguintes símbolos era esperado:;delete exists priorO símbolo “;” foi substituído por “end-of-file” paracontinuar.Notem que foram listadas as linhas e a colunas onde ocorreram os erros. O ponto-e-vírgula foi mostrado na linha 7 porque só no momento em que foi encerrado o bloco PL/SQL que o compilador “notou” a falta do último ponto-e-vírgula.O SHOW ERROR é muito útil, mas, eventualmente, temos necessidade de obter mais dados sobre os erros. Neste caso é possível consultar as views de dicionário de dados:

• USER_ERRORS• ALL_ERRORS• DBA_ERRORS

Caso haja necessidade é possível obter o código fonte da procedure através das views de dicionário de dados ALL_SOURCE, USER_SOURCE e DBA_SOURCE.Exemplo:

SELECT textFROM user_sourceWHERE name = ‘MAND_EMBORA’ORDER BY line;

Eventualmente podemos precisar ver todas procedures e todas as funçoes do nosso usuário. Nesse caso podemos usar:COL FOR object_name A35

SELECT object_name, object_typeFROM user_objectsWHERE object_type in (’PROCEDURE’,‘FUNCTION’)ORDER BY object_name;

Caso precisemos apenas dos argumentos de nossa procedure o comando DESC permite identifica-los rapidamente.Exemplo:DESC mand_embora

Vejamos o uso de uma chamada de procedure com o uso do modo OUT. Vamos criar uma procedure que consulte a tabela de empregados através do número do empregado e retorne o salário e o cargo do mesmo.CREATE OR REPLACE PROCEDURE query_emp(p_empid IN emp.empno%TYPE,p_sal OUT emp.sal%TYPE,p_job OUT emp.job%TYPE)ISBEGINSELECT sal, jobINTO p_sal, p_jobFROM scott.empWHERE empno = p_empid;END query_emp;/

Agora vamos usar nossa procedure. Note que ela deve ser chamada com um parâmetro de entrada e com dois parâmetros de saída. Vamos declarar duas variáveis globais para receber os valores da procedure: G_SAL e G_JOB:VARIABLE g_sal NUMBERVARIABLE g_job VARCHAR2(15)EXECUTE query_emp (7900, :g_sal, :g_job)PRINT g_salPRINT g_job

Caso não usemos todos os parâmetros definidos para nossa procedure quando formos chamá-la teremos um erro:SQL> call query_emp(7900);call query_emp(7900)*ERRO na linha 1:ORA-06553: PLS-306: número incorreto de tipos de argumentos na chamada para ‘QUERY_EMP’Também ocorrerá um erro caso o empregado pesquisado não exista. Exemplo:SQL> EXECUTE query_emp (120, :g_sal, :g_job)BEGIN query_emp (120, :g_sal, :g_job); END;

*ERRO na linha 1:ORA-01403: dados não encontradosORA-06512: em “SYS.QUERY_EMP”, line 7ORA-06512: em line 1Esse tipo de erro pode ser tratado pelo próprio programador. Vamos criar uma procedure que elimine todos os funcionários com o cargo que for informado pelo usuário e apresente um erro caso o cargo não exista:CREATE OR REPLACE PROCEDURE del_job

(p_jobid IN emp.job%TYPE)ISBEGINDELETE FROM scott.empWHERE job = p_jobid;IF SQL%NOTFOUND THENRAISE_APPLICATION_ERROR(-20203,’Cargo não existe.’);END IF;END DEL_JOB;/

Ao executarmos nossa procedure com um cargo que não exista obteremos a mensagem de erro que definimos:SQL> execute del_job(’Presidente’)BEGIN del_job(’Presidente’); END;

*ERRO na linha 1:ORA-20203: Cargo não existe.ORA-06512: em “SYS.DEL_JOB”, line 8ORA-06512: em line 1Veja, logo após “linha 1:” o código de erro “Cargo não existe”.Vamos ver como uma procedure pode agir como um subprograma. Primeiro vamos criar uma procedure para calcular o valor de Delta.CREATE OR REPLACE PROCEDURE delta(p_a IN number,p_b IN number,p_c IN number,p_delta OUT number)ISBEGINp_delta := (p_b * p_b) - (4 * p_a * p_c);END delta;/Agora vamos criar uma procedure que calcule o valor das raízes de delta:CREATE OR REPLACE PROCEDURE eq2g(p_a IN number,p_b IN number,p_c IN number,p_x1 OUT number,p_x2 OUT number)ISp_delta NUMBER;BEGINdelta(p_a, p_b, p_c, p_delta);IF p_delta < 0 thenp_x1 := -1;p_x2 := -1;ELSEp_x1 := -1 * p_b + sqrt(p_delta)/(2 * p_a);p_x2 := -1 * p_b - sqrt(p_delta)/(2 * p_a);end if;end eq2g;/

Notem que P_DELTA foi declarado depois de IS, mas sem um DECLARE. Isto foi feito porque em uma procedure não aceita DECLARE e sua seção de declaração fica entre IS e BEGIN. Nossa procedure está chamando a procedure DELTA para calcular o delta de nossa equação. Agora vamos executar nossa procedure.

VARIABLE g_x1 NUMBERVARIABLE g_x2 NUMBEREXECUTE eq2g (1, 4, 2, :g_x1, :g_x2)PRINT g_x1PRINT g_x2Em nossa procedure quando o delta for negativo, os valores das raízes X1 e X2 retornam com -1. É claro que existem soluções melhores do que esta, trabalhar com raízes imaginárias ou dar uma mensagem de erro quando isso acontecer.

Fonte: http://www.linhadecodigo.com.br/ArtigoImpressao.aspx?id=335Autor: Milton Goya

Grande abraço e sucesso!

Postado em PL/SQL | 8 Comentário »

PL/SQL - Introdução

quarta-feira, julho 1st, 2009

Bom dia meus amigos,

Como andam os estudos em oracle? Estão achando a tecnologia muito complexa? Acham que não vai dar conta de aprender? Calma… é normal esse tipo de “pensamento” no início. Essa área como qualquer outra exige muita dedicação e persistência. Com o tempo, tudo será claro e de fácil entendimento pra você…basta estudar e práticar bastante!!! Seguindo a ordem, no post anterior, comentei um pouco a respeito de SQL, que é o ponta pé inicial para começar os estudos e entender a Oracle, e também serve de pré-requisito para adentrar em PL/SQL. Vamos lá?

PL/SQL

Sobre PL/SQL

A linguagem PL/SQL (Procedural Language/SQL) é uma extensão de linguagem procedural do SQL, a linguagem de acesso a dados padrão para bancos de dadosrelacionais. O PL/SQL é uma linguagem proprietária da Oracle Corporation.

*Benefícios da Linguagem PL/SQL

Integração

- A linguagem PL/SQL desempenha um papel central tanto para o Oracle Server através de procedimentos armazenados, funções armazenadas, gatilhos de banco de dados e pacotes.- Além de os tipos de dados SQL também serem usados no código PL/SQL.-Combinados com o acesso direto que a linguagem SQL fornece, esses tipos de dados compartilhados integram a linguagem PL/SQL com o dicionário de dados do Oracle Server.

Melhora o Desempenho

- Reduz o Tráfego da Rede, visto que ele agrupa as instruções SQL em um único bloco e envia esse bloco inteiro para o servidor em uma única chamada.- A linguagem PL/SQL também pode cooperar com as ferramentas de desenvolvimento de aplicação do Oracle Server como, por exemplo, Oracle Developer Forms e Reports. Ao adicionar recursos de processamento procedural a essas ferramentas, a linguagem PL/SQL aumenta o desempenho.

*Estrutura de Bloco PL/SQL

DECLARE – OpcionalVariáveis, cursores, exceções definidas pelo usuário

BEGIN – Obrigatório– Instruções SQL– Instruções PL/SQL

EXCEPTION – OpcionalAções a serem desempenhadas quando ocorrem erros• END; – Obrigatório

- A linguagem PL/SQL é uma linguagem estruturada em blocos, o que significa que os programas podem ser divididos em blocos lógicos. Um bloco PL/SQL consiste em até três seções: declarativa (opcional), executável (necessária) e tratamento de exceção (opcional).

Seção Declarativa/Declare: Contém todas as variáveis, constantes, cursores e exceções definidas pelo usuário que são referenciadas nas seções executável e declarativa (Opcional)

Seção Executável/Begin: Contém instruções SQL para manipular dados no banco de dados e instruções PL/SQL para manipular dados no bloco (Obrigatória)

Seção Tratamento de exceção/ Exception: Especifica as ações a desempenhar quando erros e condições anormais surgem na seção executável (Opcional).

Observações:

- Em PL/SQL, um erro é chamado de exceção.- As unidades básicas procedimentos e funções, também são conhecidas como subprogramas.- Uma função é similar a um procedimento, exceto que uma função deve retornar um valor.- Subprogramas são blocos PL/SQL nomeados que podem assumir parâmetros e podem ser chamados. Você pode declará-los como procedimentos ou como funções.

*Tipos de BlocosToda unidade PL/SQL compreende um ou mais blocos. Onde esses blocos podem ser inteiramente separados ou aninhados um dentro do outro. Existem 3 tipos de blocos:

AnônimoBEGIN–statements[EXCEPTION]END;

ProcedimentoPROCEDURE nameISBEGIN–statements[EXCEPTION]END;

FunçãoFUNCTION nameRETURN datatypeISBEGIN–statementsRETURN value;

[EXCEPTION]END;

- Blocos anônimos: são blocos sem nome. Eles são declarados em um ponto do aplicativo onde eles devem ser executados e são passados para o mecanismo PL/SQL para serem executados em tempo de execução.

- Bloco de Procedimento/Procedural: nada mais é do um bloco PL/SQL nomeado. A grande vantagem sobre um bloco PL/SQL anônimo é que pode ser compilado e armazenado no banco de dados como um objeto de schema. Graças a essa característica as procedures são de fácil manutenção, o código é reutilizável e permitem que trabalhemos com módulos de programa.

- Bloco Função: É um bloco PL/SQL nomeado, semelhante ao Procedural, porém retorna valor.