Oracle PLSQL
PL/SQL é uma linguagem de programação procedural criada pela Oracle Corporation para uso com o sistema de gerenciamento de banco de dados Oracle. É uma linguagem de programação de alto nível que permite aos desenvolvedores criar procedimentos, funções, gatilhos e pacotes de programação que podem ser armazenados e executados dentro do banco de dados Oracle.
O PL/SQL é uma linguagem de programação completa, com suporte a tipos de dados, controle de fluxo, exceções e outras funcionalidades comuns em linguagens de programação. Ele também possui uma sintaxe semelhante ao SQL, o que o torna fácil de aprender para aqueles já familiarizados com a linguagem de consulta estruturada.
O PL/SQL é amplamente utilizado em aplicativos de banco de dados para realizar tarefas de processamento de dados e lógicas de negócios, como inserir, atualizar e excluir dados, processar transações e executar consultas. Ele também pode ser usado para criar procedimentos armazenados e funções que são chamadas por aplicativos externos, como aplicativos web ou aplicativos de desktop.
Gera dados aleatorios para utilizacao em testes
SELECT cod_material,
'ART '||cod_material descricao,
und_venda,
prc_venda,
prc_venda*.8 prc_custo,
prc_venda*.2 margem
from (
SELECT rownum*1000+rownum*124 cod_material,
'KG' und_venda,
round(dbms_random.value(40,2),2) prc_venda
FROM DUAL d CONNECT BY ROWNUM <= 10
)
order by 1
Utilização de Array em cláusula Where
declare
l table_of_varchar;
begin
l := new table_of_varchar();
-- Test statements here
l.extend(2);
l(1) := 'TABLE';
l(2) := 'VIEW';
for s in ( select o.OBJECT_NAME
from all_objects o
where o.OBJECT_TYPE in ( SELECT COLUMN_VALUE FROM TABLE(l) )
and o.OWNER = 'HUMASTER'
) loop
dbms_output.put_line(s.OBJECT_NAME);
end loop;
end;
Retorna n digitos de um numero, sem converter para string
declare
function retornaNDigitos(n number,q number) return number as
r number := case q when 0 then null else abs(n) end;
begin
if length(r) > q then
r := trunc((r/(power(10,length(r)-q))));
end if;
return r;
end;
begin
dbms_output.put_line(retornaNDigitos(-10, 1));
dbms_output.put_line(retornaNDigitos(-10, 0));
dbms_output.put_line(retornaNDigitos(666, 4));
dbms_output.put_line(retornaNDigitos(987654321, 5));
dbms_output.put_line(retornaNDigitos(123456789, 7));
dbms_output.put_line(retornaNDigitos(35445, 1));
dbms_output.put_line(retornaNDigitos(666555, 6));
end;
Pesquisa referencia de Objetos
versao : 1
select owner
|| '.'
|| name
|| ' ('
|| decode (type,
'MATERIALIZED VIEW', 'MV',
'DIMENSION', 'DIM',
'EVALUATION CONTXT', 'EVALCTXT',
'PACKAGE BODY', 'PKGBDY',
'CUBE.DIMENSION', 'CUBE.DIM',
type
)
|| ')' objdep,
referenced_owner ||'.'|| referenced_name
|| ' ('
|| decode (referenced_type,
'EVALUATION CONTXT', 'EVALCTXT',
'NON-EXISTENT CONTXT', 'NO-EXIST',
'PACKAGE BODY', 'PKGBDY',
'CUBE.DIMENSION', 'CUBE.DIM',
referenced_type
)
|| ')' refr
from all_dependencies
where owner = coalesce('&p_owner','HUMASTER')
and name = '&p_objeto'
and referenced_type not in ('NON-EXISTENT')
order by objdep;
versao : 2
select type
|| ' '
|| owner
|| '.'
|| name
|| ' references '
|| referenced_type
|| ' '
|| referenced_owner
|| '.'
|| referenced_name as dependencies
from all_dependencies
where owner = coalesce('&p_owner','HUMASTER')
and name = '&p_objeto'
and referenced_type not in ('NON-EXISTENT')
-- and (referenced_owner <> 'SYS' and referenced_owner <> 'SYSTEM' and
-- referenced_owner <> 'PUBLIC')
-- and (owner <> 'SYS' and owner <> 'SYSTEM' and owner <> 'PUBLIC')
order by owner, name, referenced_type, referenced_owner, referenced_name
Obter resultados de um REFCURSOR com número desconhecido de colunas em PLSQL
SQL> CREATE OR REPLACE PROCEDURE MYPROC (P_SQL VARCHAR2, CR OUT SYS_REFCURSOR)
2 IS
3 BEGIN
4 OPEN CR FOR P_SQL;
5 END;
6 /
Procedure created.
SQL> CREATE OR REPLACE PROCEDURE PRINT_REFCURSOR (CR IN OUT SYS_REFCURSOR)
2 IS
3 l_curid NUMBER;
4 l_col_cnt INTEGER;
5 rec_tab DBMS_SQL.DESC_TAB;
6 l_text VARCHAR2 (4000);
7 l_flag NUMBER;
8 l_varchar2 VARCHAR2 (4000);
9 l_number NUMBER;
10 l_date DATE;
11
12 BEGIN
13 l_curid := DBMS_SQL.TO_CURSOR_NUMBER(CR);
14
15 -- define columns
16 DBMS_SQL.DESCRIBE_COLUMNS (l_curid, l_col_cnt, rec_tab);
17 FOR pos IN 1 .. l_col_cnt
18 LOOP
19 CASE rec_tab (pos).col_type
20 WHEN 1 THEN
21 DBMS_SQL.DEFINE_COLUMN (l_curid,pos,l_varchar2,2000);
22 WHEN 2 THEN
23 DBMS_SQL.DEFINE_COLUMN (l_curid, pos, l_number);
24 WHEN 12 THEN
25 DBMS_SQL.DEFINE_COLUMN (l_curid, pos, l_date);
26 ELSE
27 DBMS_SQL.DEFINE_COLUMN (l_curid,pos,l_varchar2,2000);
28 END CASE;
29 END LOOP;
30
31 -- Print column names of dynamic sql
32 FOR pos IN 1 .. l_col_cnt
33 LOOP
34 l_text := LTRIM (l_text || ',' || LOWER (rec_tab (pos).col_name), ',');
35 END LOOP;
36
37 DBMS_OUTPUT.PUT_LINE (l_text);
38
39 -- Print data fetched by query
40 LOOP
41 l_flag := DBMS_SQL.FETCH_ROWS (l_curid);
42 EXIT WHEN l_flag = 0;
43 l_text := NULL;
44
45 FOR pos IN 1 .. l_col_cnt
46 LOOP
47 CASE rec_tab(pos).col_type
48 WHEN 1 THEN
49 DBMS_SQL.COLUMN_VALUE (l_curid, pos, l_varchar2);
50 l_text := LTRIM (l_text || ',"' || l_varchar2 || '"', ',');
51 WHEN 2 THEN
52 DBMS_SQL.COLUMN_VALUE (l_curid, pos, l_number);
53 l_text := LTRIM (l_text || ',' || l_number, ',');
54 WHEN 12 THEN
55 DBMS_SQL.COLUMN_VALUE (l_curid, pos, l_date);
56 l_text := LTRIM (l_text|| ','|| TO_CHAR (l_date, 'DD/MM/YYYY HH24:MI:SS'),',');
57 ELSE
58 l_text := LTRIM (l_text || ',"' || l_varchar2 || '"', ',');
59 END CASE;
60 END LOOP;
61 DBMS_OUTPUT.PUT_LINE (l_text);
62 END LOOP;
63
64 DBMS_SQL.CLOSE_CURSOR (l_curid);
65 END;
66 /
Procedure created.
SQL> set serveroutput on
SQL> declare
2 C SYS_REFCURSOR;
3 begin
4 MYPROC('SELECT * FROM DEPT', C);
5 PRINT_REFCURSOR(C);
6 END;
7 /
deptno,dname,loc
10,"ACCOUNTING","NEW YORK"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"
PL/SQL procedure successfully completed.
SQL> declare
2 C SYS_REFCURSOR;
3 begin
4 MYPROC('SELECT EMPNO, ENAME, DNAME, SAL,
5 RANK() OVER (PARTITION BY DNAME ORDER BY SAL DESC) SAL_RANK
6 FROM EMP E, DEPT D
7 WHERE E.DEPTNO = D.DEPTNO
8 ORDER BY DNAME, SAL_RANK', C);
9 PRINT_REFCURSOR(C);
10 END;
11 /
empno,ename,dname,sal,sal_rank
7839,"KING","ACCOUNTING",5000,1
7782,"CLARK","ACCOUNTING",2450,2
7934,"MILLER","ACCOUNTING",1300,3
7902,"FORD","RESEARCH",3000,1
7788,"SCOTT","RESEARCH",3000,1
7566,"JONES","RESEARCH",2975,3
7876,"ADAMS","RESEARCH",1100,4
7369,"SMITH","RESEARCH",800,5
7698,"BLAKE","SALES",2850,1
7499,"ALLEN","SALES",1600,2
7844,"TURNER","SALES",1500,3
7521,"WARD","SALES",1250,4
7654,"MARTIN","SALES",1250,4
7900,"JAMES","SALES",950,6
PL/SQL procedure successfully completed.
Caracteres Curinga
caractere % para indicar um "coringa", ou seja, um texto qualquer que pode aparecer no campo. Sua sintaxe padrão é a seguinte:
SELECT colunas FROM tabela WHERE campo LIKE 'valor'
Nessa instrução, o "valor" pode ser informado de várias formas:
texto: Nesse caso, serão retornados todos os registros que contêm no campo buscado exatamente o "texto" informado no filtro. O funcionamento aqui é equivalente a utilizar o operador de igualdade (=);
%texto%: Serão retornados os registros que contêm no campo buscado o "texto" informado. Por exemplo, podemos buscar os nomes que contêm "Santos", ou que contêm uma sílaba ou letra específica. O registro com nome "Luis da Silva", por exemplo, contém o termo "da", então atenderia ao filtro '%da%';
%texto: Serão retornados os registros cujo valor do campo filtrado termina com o "texto" informado. O %, nesse caso, indica que pode haver qualquer valor no começo do campo, desde que ele termine com o "texto". Por exemplo, o registro com nome "Luis da Silva" atenderia ao filtro '%Silva';
texto%: Serão retornados os registros cujo valor do campo filtrado começa com o "texto" informado. Dessa vez, o % indica que após o "texto" pode haver qualquer valor. Por exemplo, o registro com nome "Luis da Silva", atenderia ao filtro 'Luis%'.
Além do %, existe ainda o símbolo especial underscore ou sublinhado (_), com o qual podemos efetuar filtros mais exatos sobre as colunas textuais, buscando por um termo em uma posição específica do texto. O underscore indica a quantidade de casas/caracteres antes ou depois do texto buscado. A seguir temos alguns exemplos de uso para que possamos compreender sua sintaxe:
'_este': Filtra os registros que contém 1 caractere qualquer no começo e em seguida o termo 'este'. Por exemplo, seriam retornados registros contendo o valor 'teste', 'peste', 'veste';
'b_m': Filtra os registros que comecem com a letra "b", contenham 1 caractere em seguida, e depois a letra "m". Nesse caso, atenderiam a esse filtro, por exemplo, os valores "bom", "bem", "bPm", etc.
'_u%': Filtra os registros cujo campo especificado comece com um caractere qualquer, em seguida contenha uma letra "u", e depois qualquer valor. Por exemplo, os valores "Luis da Silva" e "Gustavo" atenderiam a esse filtro.