Manual de PLSQL COM Cdigos Rpidos

DecodeA partir da verso 9i do Oracle, foi disponibilizado o mtodo Decode, que uma ferramenta poderosa nas queries, claro deve ser utilizada com cautela para no sobrecarregar o desempenho, mas muitas vezes mais rpido usar por ela do que condies entre tabelas.

O decode funciona como um if, else e case. Sua sintaxe essa:decode( expression , search , result [, search , result] [, default] ) Expression: a expresso a ser comparada (desde uma varivel at um select desde que os mesmos retornem apenas UM valor); Search: o valor a ser comparado; Result: em caso afirmativo, o valor que ser retornado pela funo; Default: funcionaria como o else, caso nenhuma das condies fosse atendida o valor colocado nele seria assumido como retorno da funo; Para mim o entendimento mais fcil de se fazer atravs de exemplos ento vou tentar abordar alguns casos nesses exemplos, se voc ainda estiver com dvida, escreva um comentrio com sua dvida que eu respondo. /* Formatted on 2012/05/10 08:32 (Formatter Plus v4.8.8) */ SELECT nome_cliente, DECODE (tipo_cliente, 1, 'Pessoa Juridica', 'Pessoa Fisica') FROM clientes;

SELECT nome_cliente, DECODE ((SELECT COUNT (1) FROM notas WHERE id_cliente = t.id_cliente), 0, 'Nenhum pedido', 1, 'Um pedido', 'Varios pedidos' ) FROM clientes;

SELECT nome_cliente, DECODE (status, 1, 'Em aprovacao', 2, 'Aprovado', 3, 'Suspenso', 'Cadastrado' ) FROM clientes;

INSTRINSTR: Retorna a posio de uma substring nuam string. Esta funo pode receber at 4 parametros sendo: String completa Trecho a ser procurado Inicio da pesquisa Ensima ocorrencia da substring

Vamos ver em exemplos: SELECT INSTR('OLHA O AUE AI O','A') FROM DUAL A query acima retornar 4 que representa a posio do primeiro A da frase! SELECT INSTR('OLHA O AUE AI O','A', 5) FROM DUAL J esta query retornar 8!!!! por que a pesquisa comear pelo quinto caractere. SELECT INSTR('OLHA O AUE AI O','A', 5,2) FROM DUAL E essa? Ela retornar 12. Porque estaremos procurando o segundo A partindo da quinta posio da string fornecida!

SUBSTRSintaxe: substr( string, start_position, [ length ] ) string a string de origem. start_position a posio de extraco. A primeira posio na cadeia sempre 1. Length opcional. o nmero de caracteres para extrair. Se este parmetro for omitido, substr retornar a seqncia inteira.

For Example:substr('This is a test', 6, 2) substr('This is a test', 6) would return 'is'

would return 'is a test' would return 'Tech'

substr('TechOnTheNet', 1, 4)

substr('TechOnTheNet', -3, 3) would return 'Net' substr('TechOnTheNet', -6, 3) would return 'The'

substr('TechOnTheNet', -8, 2) would return 'On'

REPLACEThe syntax for the replace function is: replace( string1, string_to_replace, [ replacement_string ] )string1 is

the string to replace a sequence of characters with another set of characters. the string that will be searched for in string1.

string_to_replace is

replacement_string is

optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.

Applies To: Oracle 8i, Oracle 9i, Oracle 10g, Oracle 11g

For Example:replace('123123tech', '123'); replace('123tech123', '123'); replace('222tech', '2', '3'); replace('0000123', '0'); replace('0000123', '0', ' '); would return 'tech'

would return 'tech'

would return '333tech' would return '123' would return ' 123'

Name_IN(built - in)Voc pode fazer referncia a itens indiretamente com o NAME_IN e COPY embutido subprogramas. A funo NAME_IN retorna o contedo de uma varivel indicada ou item. Use a funo NAME_IN para obter o valor de um item sem se referir ao artigo diretamente. As seguintes afirmaes so equivalentes: IF: emp.ename = 'smith' - referncia direta

IF NAME_IN ('emp.ename') = 'smith' - referncia indireta O valor de retorno sempre uma cadeia de caracteres. Para usar NAME_IN para um item de data ou nmero, converter a string para o tipo de dados desejado com a funo de converso apropriada: date_var: = TO_DATE (Name_In ('order.date_item')); num_var: = TO_NUMBER (Name_In ('order.number_item')); Notas sobre NAME_IN:

A funo NAME_IN no pode retornar o contedo de uma varivel global ou local. Em PL / SQL gatilhos que sero executadas em entrar-query modo, voc deve usar NAME_IN ao invs de notao bind-varivel normal para acessar valores no bloco de dados. (Isto porque o utilizador final pode digitar operadores relacionais para o item, produzindo um valor que no est numa forma que pode ser processado por PL / SQL.) O procedimento de cpia o procedimento de cpia atribui um valor indicado com uma varivel indicada ou item. Ao contrrio do padro de consignao PL / SQL, no entanto, usando o procedimento de cpia permite que voc para referenciar o item cujo valor est sendo definido: : Emp.ename: = 'smith'; - referncia direta Copy ('Smith', 'emp.ename'); - referncia indireta

Cpia pode ser utilizada com a funo NAME_IN para atribuir um valor para um item cujo nome armazenado em uma varivel de referncia ou item: /* Coloca o valor 'Smith' no item cujo nome armazenado em ref_item */ Copy ('Smith', Name_In ('control.ref_item'));

Por que usar itens Referencing indiretos referncia indireta permite que voc escreva mais cdigo genrico e reutilizvel. Ao usar variveis no lugar de nomes de itens reais, voc pode escrever um subprograma que pode operar em qualquer item cujo nome foi atribudo varivel indicada. Alm disso, usando referncia indireta obrigatria quando se referir ao valor de uma varivel de ligao formulrio (item, parmetro, varivel global) em PL / SQL que voc escreve em uma biblioteca ou um mdulo de menu. Como as bibliotecas, menus e formulrios so mdulos de aplicativos separados, voc no pode se

referir diretamente ao valor de um item de formulrio em um procedimento de comando ou biblioteca item de menu.

TRUNCMantm apenas a parte desejada de um numro. Ao usar este mtodo podemos fornecer o nmero de casas decimais preservadas e formatar um nmero. Ex:

SELECT TRUNC( 12356.98 , 1 ) FROM DUAL Este cara vai remover todos os algarismos depois do primeiro que sucede a pontuao, ou seja, vai retornar 12356.9. o trunc pode ser usado tambem para pegar o valor mais significativo desejado de um nmero, exemplo:

J neste exemplo o valor retornado ser 12000. O TRUNC zera os 3 caracteres menos significativos do nmero.

SQL DINMICOSQL Dinmico um comando SQL ou um bloco PL/SQL vlido, codificado dentro de uma string (populada em tempo de execuo) e pode ser executado atravs do uso do comando EXECUTE IMMEDIATE, a partir da verso 8i do banco de dados Oracle. Esse tipo de comando SQL pode conter placeholders para bind (host) arguments (como demonstrado na sintaxe adiante). Um placeholder um identificador no declarado, ento o valor que lhe atribudo substitui uma varivel da composio da sintaxe do comando, sendo que preciso prefixar tal coluna com o smbolo : (dois pontos).

Sintaxe:EXECUTE IMMEDIATE SQL string' [INTO {varivel[, varivel]... | record}] [USING [IN | OUT | IN OUT] bind_argument [, [IN | OUT | IN OUT] bind_argument]...];

Exemplos.DECLARE vvl_salario BEGIN EXECUTE IMMEDIATE nm_funcionario) ' ' insert into temp(cd_funcionario, NUMBER (4) := 2000;

|| ' select cd_funcionario, nm_funcionario || ' from || ' where USING vl_salario; FUNCIONARIO '

'

vl_salario > :1 '

COMMIT;

EXECUTE IMMEDIATE 'CREATE INDEX idx_nm_func ON Funcionario (nm_funcionario)'; END; Carga de dados, onde as tabelas so truncadas (no gerando transao), ao invs de serem deletadas, gerando ganho em performance. Criao de tabela de Log para a carga, com a incluso de seus respectivos dados: CREATE OR REPLACE PROCEDURE PRC_CARGA Is VQt_linhas Number;

CURSOR CUR_TEMPO IS Select distinct DT_EMISSAO DT_EMISSAO) Dia, DT_VENDA, EXTRACT(DAY from

EXTRACT(MONTH from DT_EMISSAO) Mes, EXTRACT(YEAR from DT_EMISSAO) Ano, CEIL(EXTRACT(MONTH from DT_EMISSAO) / 2) Bimestre, CEIL(EXTRACT(MONTH from DT_EMISSAO) / 6) Semestre, To_Char(DT_EMISSAO,'Q') Trimestre, To_Char(DT_EMISSAO,'WW') Semana, FROM TB_NOTA_FISCAL;

CURSOR CUR_PRODUTO IS Select p.cd_produto, p.ds_categoria, p.ds_segmento, p.vl_peso_padrao, from Where p.ds_produto, p.ds_divisao,

p.ds_marca,

p.ds_sabor,

tb_produto p, Tb_item_nota i p.cd_produto = i.cd_produto;

CURSOR CUR_CLIENTE IS Select cd.cd_cliente, cd.cd_uf, from tb_cliente cc.ds_categoria, ce.nm_cidade, cd, ve, tb_cidade tb_categoria cd.ds_razao_social, ve.nm_vendedor ce, cc,

tb_vendedor tb_nota_fiscal nf where and and and cd.CD_CIDADE cd.CD_VENDEDOR cd.CD_CATEGORIA nf.cd_cliente

= ce.CD_CIDADE = ve.CD_VENDEDOR = cc.CD_CATEGORIA = cd.cd_cliente;

CURSOR CUR_VENDA IS Select n.ds_serie, n.cd_cliente, n.dt_emissao,

n.nr_nf, i.cd_produto, i.qt_caixa, qt_peso, i.vl_venda from p where and and n.nr_nf n.ds_serie i.cd_produto = = = TB_NOTA_FISCAL n,

i.nr_item,

i.qt_caixa * p.vl_peso_padrao

TB_ITEM_NOTA

i,

TB_PRODUTO

i.nr_nf i.ds_serie p.cd_produto;

BEGIN Execute immediate 'truncate table venda'; Execute immediate 'alter table venda disable constraint fk_produto_venda'; Execute immediate 'alter table venda disable constraint fk_cliente_venda'; Execute immediate 'alter table venda disable constraint fk_tempo_venda'; Execute immediate 'truncate table produto'; Execute immediate 'truncate table clirnte'; Execute immediate 'truncate table tempo'; Execute immediate 'alter table venda enabled constraint fk_produto_venda'; Execute immediate 'alter table venda enabled constraint fk_cliente_venda'; Execute immediate 'alter table venda enabled constraint fk_tempo_venda';

-- Criao da Tabela de Log Execute immediate 'CREATE TABLE TB_LOG'||TO_CHAR(Sysdate,'DDMMYYYY')|| ' (NM_TABELA '|| ' NUMBER(6)) '; DT_FIM DATE, QT_LINHAS CHAR(30), DT_INICIO DATE,

-- Incluso do incio da carga da tabela tempo na tabela de LOG Execute immediate ' INSERT INTO TB_LOG'||TO_CHAR(Sysdate,'DDMMYYYY')|| ' (NM_TABELA, DT_INICIO) '|| ' VALUES ('+ ''''+ 'TEMPO'+ ''''+ ', Sysdate) '; COMMIT;

FOR REG_TEMPO IN CUR_TEMPO LOOP insert into dbexemplo.tempo

(Dt_Data, Ano, Semestre, VALUES (REG_TEMPO.dt_venda, REG_TEMPO.Ano, REG_TEMPO.Trimestre, REG_TEMPO.Semestre, END LOOP; COMMIT;

Dia, Bimestre, Semana)

Mes, Trimestre,

REG_TEMPO.Dia, REG_TEMPO.Bimestre,

REG_TEMPO.Mes,

REG_TEMPO.Semana)

-- Usando a funo PRC_QTLINHAS, criada no exemplo 3 VQt_Linhas := PRC_QTLINHAS('dbexemplo', 'TB_LOG'||TO_CHAR(Sysdate,'DDMMYYYY'), Null);

-- Incluso do fim e qtdd de linhas da carga da tabela tempo na tabela de LOG -- Usando bind (host) variables e placeholders Execute immediate ' UPDATE TB_LOG'||TO_CHAR(Sysdate,'DDMMYYYY')|| ' SET ' ' WHERE USING vqt_linhas; COMMIT; DT_INICIO = Sysdate, '|| QT_LINHAS = :1 '|| NM_TABELA = '+''''+'TEMPO'+''''

-- Continuar com o mesmo processo para a carga de PRODUTOs, CLIENTEs e VENDAs . . .

end PRC_CARGA;

JOINSCross JoinCria um produto carteziano.

Exemplo.

select m.nome, c.modelo from marcas m cross join carros c;

Inner JoinComo se tivesse explcito a chave primria marca, e FK em carros. Associao. select m.nome, c.modelo from marcas m inner join carros c

on c.marca = m.marca

Right JoinMostra todas as marcas com referncia e coloca Null nas marcas que no tem carro correspondente. select m.nome, c.modelo from marcas m right join carros c

on c.marca = m.marca

Full Outer JoinComo se fosse todas as marcas e todos os carros juntos, colocando todas as marcas e carros na busca e se caso no tiver referncia, ir colocar nulo. select m.nome, c.modelo from marcas m full outer join carros c

on c.marca = m.marca

CursoresFOR controla de modo completo a acesso ao cursor, substituindo os trs comandos anteriores, executando, automaticamente as seguintes aes: Cria a varivel (do tipo registro) que ir receber os dados. Essa varivel no dever ser explicitamente declarada (DECLARE); Abre o cursor; Realiza a cpia das linhas uma a uma (FETCH), a cada iterao do comando; Controla o final do cursor, e Fecha o cursor.

ExemplosDECLARE CURSOR c_empresas IS SELECT id FROM tempresas; BEGIN

FOR I IN c_empresas LOOP

DBMS_OUTPUT.PUT_LINE(I.id);

END LOOP;

END;

LPADCompleta String a esquerda.

Sintaxelpad( string1, padded_length, [ pad_string ] ) string1 = Vriavel ou String que deseja-se buscar. Padded_length = Tamanho desejado para a String.

Pad_string = Valor para completar espaos faltantes.

Exemplolpad('tech', 7); would return ' tech' would return 'te' would return '0000tech' would return 'tech on the net' would return 'ztech on the net'

lpad('tech', 2);

lpad('tech', 8, '0');

lpad('tech on the net', 15, 'z');

lpad('tech on the net', 16, 'z');

RPADAo contrrio do LPAD, completa a String a direita.

ExemploRpad(Teste,6,z) Return Testez

LIBSLIB_TEMPLATETPL_RETORNA_PARAMETROFUNCTION TPL_RETORNA_PARAMETRO (CATEGORIA IN PARAMETRO IN W_EMPR_ID IN RETURN VARCHAR2 VARCHAR2, VARCHAR2, NUMBER DEFAULT NULL)

TPL_EXIT_FORMEste procedimento substituir todos os EXIT_FORM, isto pelo fato do mesmo graver logs de logoff.

Built-InsFormsGO_ITEMDeixar cursor em item desejado. GO_ITEM(BLOCO.ITEM);

(GET_ITEM_PROPERTY)Retornar Propriedade do ItemRetornara a cor de Fundo do Item. GET_ITEM_PROPERTY('TOOLBAR.FLAG_EMAIL',BACKGROUND_COLOR)

(FIND_FORM)Retornar Nome do FORMFind_Form(name_in('System.Current_Form'));

(GET_FORM_PROPERTY )Nome do FormRetorna nome do Primeiro bloco do Form. Get_Form_Property(curform,FIRST_BLOCK);

(FIND_BLOCK)ID Do BlocoFIND_BLOCK(Nome do Bloco em VARCHAR);

(GET_BLOCK_PROPERTY) Nome do DataSource Do BlocoRetorna o nome da Tabela do Bloco. GET_BLOCK_PROPERTY (block_name VARCHAR2, QUERY_DATA_SOURCE_NAME);

Retorna o nome do Prximo bloco, no pega restries de navegao. Se for o ltimo bloco retorna NULL. GET_BLOCK_PROPERTY (block_id VARCHAR2, NEXTBLOCK);

GET_BLOCK_PROPERTY(Nome do Bloco,CURRENT_RECORD);

(DELETE_RECORD) Deletar RegistroDelata o registro no bloco corrente e marca como deletado. DELETE_RECORD;

(SET_APPLICATION_PROPERTY )Seta Propriedade da AplicaoColoca o cursor em modo de ampulheta. SET_APPLICATION_PROPERTY(CURSOR_STYLE,busy);

ParmetrosRecuperar Id de Parmetro Pega id de uma lista de parmetro, a varivel que vai ser populada dever ser do TYPE PARAMLIST.

Com o CREATE_PARAMETER_LIST(O nome do PARAM), ser criado o parmetro efetivamente. P_ID ParamList; P_ID := GET_PARAMETER_LIST(Um nome para O Paramtro); Exemplos: PL_ID := GET_PARAMETER_LIST('PL_FPRK0300'); IF NOT ID_NULL(PL_ID) THEN DESTROY_PARAMETER_LIST( PL_ID ); END IF; PL_ID := CREATE_PARAMETER_LIST('PL_FPRK0300');

Adicionar Parmetros Dever ser passado o ID da Lista de Parmetros,o Nome do Parmetro, o tipo do parmetro, e o valor do parmetro de acordo com o TIPO. ADD_PARAMETER(PL_ID,'Nome Parametro,TEXT_PARAMETER , NVL(:SELECAO.LINHAS,1) );

(DO_KEY) Simular Boto PressionadoComo se houvesse sido pressionado um boto. DO_KEY(Built-in); DO_KEY(execute_query);

EXEMPLOS:

Sair do FORMFechar form atual. Com o comando abaixo o form ser fechado sem validao alguma. Sem perguntar ao usurio, sem confirmar alteraes e sem fazer rollback de transaes. EXIT_FORM(NO_VALIDATE,NO_ROLLBACK);

(RUN_PRODUCT) Rodar Programa OracleRoda uma ferramenta suportada pelo forms. No exemplo abaixo ser rodado um REPORTS.

RUN_PRODUCT(REPORTS, 'RPRD0300_INTER', SYNCHRONOUS, RUNTIME, FILESYSTEM, PL_ID, NULL); 1:Nome do Produto, 2:Nome do Mdulo, 3:Modo de Commit, 4:Modo de Execuo, 5:Local do Arquivo, 6: Nome ou Id da Lista de Parmetros, 7: Como Mostrar na tela(Ser apenas requerido quando haver grficos para montar).