SQL - Functions (modelo)

CREATE Function AchaDescricaoConta(var_codcoligada number,
var_contafolha varchar,
var_codigointegracao varchar)
return varchar

as

VAR_CONTACONTABIL varchar2(40);
VAR_DESCRICAO varchar2(40);

begin


select contasaldus into VAR_CONTACONTABIL
from pcontas       
where codcoligada=var_codcoligada
and codcolconta=var_codcoligada
and grpconta=var_contafolha
and integrcfunc=var_codigointegracao;


select descricao into VAR_DESCRICAO
from cconta
where codcoligada=var_codcoligada
and codconta=VAR_CONTACONTABIL ;

return(VAR_DESCRICAO);

end;

SQL - Cursor (modelo)

DECLARE @nome_da_tabela varchar(40)
DECLARE meu_cursor CURSOR FOR

SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE'  and table_name like 'P%'
OPEN meu_cursor

FETCH NEXT FROM meu_cursor
INTO @nome_da_tabela

WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE ('delete  FROM ' + @nome_da_tabela)

FETCH NEXT FROM meu_cursor
INTO @nome_da_tabela
END
CLOSE meu_cursor
DEALLOCATE meu_cursor
GO

SQL - Case (Exemplo)

 (SELECT 'diasemana' =
        CASE
         WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 1 THEN 'Domingo'
         WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 2 THEN 'Segunda'
         WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 3 THEN 'Terça'
         WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 4 THEN 'Quarta'
         WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 5 THEN 'Quinta'
         WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 6 THEN 'Sexta'
         WHEN DATEPART(WEEKDAY, ZATENDIMENTOS.DTHENTRADA) = 7 THEN 'Sábado'
    END) DIASEMANA,

Oracle - Exemplos

GRANT CREATE DATABASE LINK TO RM;
/

CREATE TABLE ZTABELASEXCLUIDAS (NOMETABELA VARCHAR(150),
CONSTRAINT PKZTABELASEXCLUIDAS PRIMARY KEY (NOMETABELA));
/
CREATE TABLE ZALIASORIGEM (NOMEALIAS VARCHAR(60),
                           CODCOLIGADA NUMBER(5),
CONSTRAINT PKZALIASORIGEM PRIMARY KEY (NOMEALIAS));
/
CREATE OR REPLACE PROCEDURE SP_ATIVAFOREIGNKEYS (P_ACAO IN VARCHAR)
AS
  P_NOMETABELA VARCHAR(128);
  P_CHAVEESTRANGEIRA VARCHAR(128);
  P_CURSORNRO INTEGER;
  P_EXECUTE INTEGER;
  CURSOR CURSORTABELAS IS SELECT TABLE_NAME
                          FROM USER_TABLES;

  CURSOR CURSORCONSTRAINTS IS SELECT CONSTRAINT_NAME
                           FROM USER_CONSTRAINTS
                           WHERE TABLE_NAME = P_NOMETABELA
                           AND   CONSTRAINT_TYPE = 'R';      /* SOMENTE FOREIGN KEYS */
BEGIN
  OPEN CURSORTABELAS;
  FETCH CURSORTABELAS INTO P_NOMETABELA;
  LOOP
  EXIT WHEN CURSORTABELAS%NOTFOUND;

    OPEN CURSORCONSTRAINTS;
    FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
    LOOP
    EXIT WHEN CURSORCONSTRAINTS%NOTFOUND;
 
      P_CURSORNRO := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(P_CURSORNRO, 'ALTER TABLE ' || P_NOMETABELA || ' ' || P_ACAO || ' CONSTRAINT ' || P_CHAVEESTRANGEIRA, DBMS_SQL.NATIVE);
      P_EXECUTE :=DBMS_SQL.EXECUTE(P_CURSORNRO);
      DBMS_SQL.CLOSE_CURSOR(P_CURSORNRO);
 
    FETCH CURSORCONSTRAINTS INTO P_CHAVEESTRANGEIRA;
    END LOOP;
    CLOSE CURSORCONSTRAINTS;

  FETCH CURSORTABELAS INTO P_NOMETABELA;
  END LOOP;
  CLOSE CURSORTABELAS;
END;
/

Oracle - Verificação

set heading off
set pagesize 0
set verify off
set feedback off
set termout off
set linesize 132

prompt spool count_rows.log
prompt !date +'%X'

spool c:\arquivo1.txt
select trim(name),trim(value) from v$parameter;
spool of

spool c:\arquivo2.txt
select * from nls_database_parameters;
spool of


prompt !date +'%X'
prompt spool off
spool off

Oracle - Site Exemplos

http://www.oracle.matrix.com.br/

Oracle - Procedures (Exemplos)

1
CREATE OR REPLACE PROCEDURE TESTE (COLUNA IN VARCHAR2,TABELA IN VARCHAR2 ) IS
CUR_PESSOA INTEGER;
TEXTO VARCHAR(100);
TEMP NUMBER;
BEGIN
CUR_PESSOA:=DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.parse(CUR_PESSOA,'SELECT  '||COLUNA|| '  FROM  '|| TABELA, DBMS_SQL.V7);
DBMS_SQL.DEFINE_COLUMN (CUR_PESSOA,1,TEXTO,100);
TEMP:=DBMS_SQL.EXECUTE(CUR_PESSOA);
LOOP
IF DBMS_SQL.FETCH_ROWS (CUR_PESSOA)=0
THEN EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE (CUR_PESSOA,1,TEXTO);
DBMS_OUTPUT.PUT_LINE (TEXTO);
END IF;
END LOOP;
DBMS_SQL.CLOSE_CURSOR (CUR_PESSOA);
END TESTE;

2
CREATE OR REPLACE PROCEDURE QUERY (QUERY_IN IN VARCHAR2) IS

CUR_HANDLE INTEGER;
TEMP NUMBER;
BEGIN
  CUR_HANDLE:=DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(CUR_HANDLE,QUERY_IN,DBMS_SQL.V7);
  TEMP:=DBMS_SQL.EXECUTE(CUR_HANDLE);
  DBMS_SQL.CLOSE_CURSOR (CUR_HANDLE);
 
  DBMS_OUTPUT.PUT_LINE (Num. de linha tratadas:' || temp);
 
  end query ;

3
DECLARE
V_TABELA VARCHAR2 (100);
V_COLUNA VARCHAR2 (100);
LINHA VARCHAR2 (200);


out_file UTL_File.File_type;

CURSOR TAB IS

SELECT TABLE_NAME TABELA ,COLUMN_NAME COLUNA FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE 'CODCOLI%';

BEGIN

 out_file := Utl_File.Fopen ('C:\TEMP','DELCOL.SQL','w');

 FOR REG_TAB IN TAB   LOOP
  V_TABELA := REG_TAB.TABELA;
  V_COLUNA := REG_TAB.COLUNA;
  LINHA := 'DELETE FROM ' ||REG_TAB.TABELA || ' WHERE ' || REG_TAB.COLUNA || '=1;';
  
   UTL_FILE.PUT_LINE (OUT_FILE,LINHA);
  END LOOP;
   Utl_File.FClose (out_file);
  Dbms_Output.Put_Line ('ARQUIVO GERADO COM SUCESSO');

 EXCEPTION
  WHEN UTL_FILE.INVALID_OPERATION THEN
  Dbms_Output.Put_Line ('OPERAÇÃO INVALIDA NO ARQUIVO.');
  Utl_File.FClose (OUT_FILE);
  WHEN UTL_FILE.INVALID_PATH THEN
  Dbms_Output.Put_Line ('DIRETORIO INVÁLIDO');
  Utl_File.FClose (OUT_FILE);
END ;