Menu de navegação

[douglas.ribeiro.idParticipante]

Bom dia a todos

a varios anos que nao trabalho com oracle e infelizmente nao é como andar de bicicleta, estou precisando criar um script que me parece bem simples mas para piorar as coisas nao tenho o oracle instalado na maquina, o problema é este:
Crie um segundo script (SCRIPT2.SQL) que possua um bloco de código para que gere registros fictícios para as tabelas criadas. Gere 1000 registros para a tabela EXAME_NF. Para cada EXAME_NF gere 3 registros. Faça com que a DATACADASTRO do EXAME_NF comece em 10 dias atrás, fazendo com que a cada 100 registros a data seja aumentada em 1 dia, distribuindo assim os 1000 registros em 10 dias diferentes de cadastro.

[José Laurindo ChiappaModerador]

Blz ? Então, sua lógica é um tanto complexa, então Acredito que ninguém vai conseguir escrever isso pra vc, por falta de tempo mesmo… O que eu VOU fazer é ter dar alguns Exemplos das técnicas necessárias pra gerar um segundo script, pra criar dados, pra fazer aritmética de datas, pra fazer um LOOP em PL/SQL… VAI FICAR POR SUA CONTA, porém, juntar/utilizar as técnicas do modo necessário pra chegar no seu resultado desejado, okdoc ??
E um OUTRO detalhe importante : vc ABSOLUTAMENTE NÃO DIZ qual é teu Sistema Operacional, qual FERRAMENTA CLIENTE vai ser usada tanto pra gerar quanto pra executar os scripts, não diz nem a VERSÂO nem a Edição do RDBMS Oracle…. SENDO ASSIM, já Aviso que vou usar um banco 12cR2 EE de teste que está instalado no meu notebook Windows 8.1 , SQLPLUS como a ferramenta-cliente (vai ser o sqlplus EM MODO TEXTO portanto, nada de sqlplusw.exe mas sim o sqlplus.exe normal), blz ??

Isso dito, segue :

1) script que gera um segundo scrit, sendo que esse segundo script contém um bloco PL/SQL : basicamente vamos usar o comando nativo do sqlplus SPOOL pra gerar um arquivo-texto (SCRIPTs são arquivos-texto!!) com o output da tela, E vamos setar a formatação de tela pra não trazer títulos, quebras de página, nem nada assim… E, Óbvio, dou DE BARATO AQUI que o programador conhece PL/SQL, então SABE que (por exemplo) um bloco PL/SQL é delimitado por BEGIN e END; , e TAMBÈM conhece sqlplus, então OBEDECE aos ditames sqlplus, como EXIT pra sair do script e uma / no final do arquivo….
Segue um exemplo, no caso um script que gera (e depois executa) um segundo script que faz DBMS_OUTPUT pra cada registro na tabela EMPLOYEES aonde SALARY for > 15000 :

==> primeiro, os dados serão sercuperados por uma query tipo :

container=ORCL12C:HR@ORCL12C> select first_name, last_name, salary from employees where salary > 15000;

FIRST_NAME           LAST_NAME                     SALARY

<hr />

Steven               King                           24000
Neena                Kochhar                        17000
Lex                  De Haan                        17000

container=ORCL12C:HR@ORCL12C>

==> a técnica de programação é SIMPLESMENTE montar uma string com os comandos desejados, tipo :

container=ORCL12C:HR@ORCL12C> l
  1  select 'DBMS_OUTPUT.PUT_LINE(''Empregado=' || first_name || ' ' || last_name
  2     || ' ganha salario de:' || salary
  3*    || ''')'';' from employees where salary > 15000
container=ORCL12C:HR@ORCL12C> /

<h2>'DBMS_OUTPUT.PUT_LINE(''EMPREGADO='||FIRST_NAME||''||LAST_NAME||'GANHASALARIODE:</h2>

DBMS_OUTPUT.PUT_LINE('Empregado=Steven King ganha salario de:24000')';
DBMS_OUTPUT.PUT_LINE('Empregado=Neena Kochhar ganha salario de:17000')';
DBMS_OUTPUT.PUT_LINE('Empregado=Lex De Haan ganha salario de:17000')';

container=ORCL12C:HR@ORCL12C>

==> tá vendo a saída acima ?? Pois é, é essa saída que será garavada pelo OUTPUT...  ok, eis o script, que eu programei pra ser executado diretamente pelo sqlplus :

C:\Users\jlchi_000>type script1.sql
set term off feedback off verify off pages 0 lines 500 trimspool on head off serveroutput on size 1000000
spool script_com_bloco_PLSQL.sql
select 'BEGIN' from dual;
select 'DBMS_OUTPUT.PUT_LINE(''Empregado=' || first_name || ' ' || last_name
     || ' ganha salario de:' || salary
     || ''');' from employees where salary > 15000;
select 'END;' from dual;
select '/' from dual;
spool off
set term ON feedback ON verify ON head ON
@script_com_bloco_PLSQL.sql
exit
/

===>> okdoc, basta eu EXECUTAR o primeiro script pelo sqlplus :

C:\Users\jlchi_000>sqlplus hr/hr @script1.sql

SQL*Plus: Release 12.2.0.1.0 Production on Sex Mar 15 14:41:17 2019

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Horário do último log-in bem-sucedido: Sex Mar 15 2019 14:40:10 -03:00

Conectado a:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Empregado=Steven King ganha salario de:24000
Empregado=Neena Kochhar ganha salario de:17000
Empregado=Lex De Haan ganha salario de:17000

Procedimento PL/SQL concluído com sucesso.

Desconectado de Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

C:\Users\jlchi_000>

==> okdoc ??? Tá vendo a saída acima gerada pela EXECUÇÃO do script dinamicamente construído ?? É isso aí.. Só CONFIRMANDO que realmente foi gerado ok :

C:\Users\jlchi_000>type script_com_bloco_PLSQL.sql
BEGIN
DBMS_OUTPUT.PUT_LINE('Empregado=Steven King ganha salario de:24000');
DBMS_OUTPUT.PUT_LINE('Empregado=Neena Kochhar ganha salario de:17000');
DBMS_OUTPUT.PUT_LINE('Empregado=Lex De Haan ganha salario de:17000');
END;
/

C:\Users\jlchi_000>

2) aritmética de datas : no RDBMS Oracle, basta vc somar ou subtrair números numa variável ou coluna DATE, que já se assume que esses numerós são DIAS (ou fração de dias, se for número não-inteiro) e PRONTO, o RDBMS já faz a aritmética cfrme preciso…

3) geração de dados fictícios : nós temos 2 maneiras principais de se fazer isso… São elas :

a. vc pode fazer um SELECT baseado numa tabela ou view que já tenha a quantidade de linhas necessária, como (digamos) a ALL_OBJECTS – tipicamente, ela tem algumas dezenas de milhares de linhas….

OU

b. vc pode usar o truque do CONNECT BY

OU

c. pode abrir um LOOP num bloco PL/SQL, com a sintaxe : for i in 1..máximodesejado

==>  No exemplo abaixo, quero gerar um RESULTSET de 10 linhas, com uma coluna NUMBER com um número qquer, uma coluna com uma STRING que contenha o número da linha (vou usar ROWNUM portanto), e a última coluna é a data de hoje mais 1 dia pra primeira linha, 2 dias pra segunda linha, assim por diante...
 Exemplo com SELECT numa tabela grande :

container=ORCL12C:HR@ORCL12C> select object_id AS coluna1, 'Linha=' || rownum AS coluna2, sysdate+rownum from all_objects
  2  where rownum < 11;

COLUNA1 COLUNA2                                        SYSDATE+

<hr />

133 Linha=1 16/03/19
142 Linha=2 17/03/19
143 Linha=3 18/03/19
417 Linha=4 19/03/19
418 Linha=5 20/03/19
520 Linha=6 21/03/19
521 Linha=7 22/03/19
522 Linha=8 23/03/19
523 Linha=9 24/03/19
524 Linha=10 25/03/19

10 linhas selecionadas.

container=ORCL12C:HR@ORCL12C>

Exemplo com a técnica do CONNECT BY :

container=ORCL12C:HR@ORCL12C> select l as COLUNA1, 'Linha=' || l as coluna2, sysdate+l from (select level l from dual connect by level < 10);

COLUNA1 COLUNA2                                        SYSDATE+

<hr />

 1 Linha=1                                        16/03/19
 2 Linha=2                                        17/03/19
 3 Linha=3                                        18/03/19
 4 Linha=4                                        19/03/19
 5 Linha=5                                        20/03/19
 6 Linha=6                                        21/03/19
 7 Linha=7                                        22/03/19
 8 Linha=8                                        23/03/19
 9 Linha=9                                        24/03/19

9 linhas selecionadas.

container=ORCL12C:HR@ORCL12C>

====>>> OKDOC, tá demonstrado ??? Agora cabe A VOCÊ juntar as técnicas conforme necessário pra tua lógica E escrever teu script….

[]s

Chiappa

[douglas.ribeiro.idParticipante]

Fico muito agradecido pela sua ajuda, vou explorar a sua dicas

[José Laurindo ChiappaModerador]

Jóia, fico contente de poder ter ajudado ao menos um pouco…. E como sempre, se durante o desenvolvimento vc ficar nalguma dúvida, é só montar um caso-exemplo bem pequeno e simples englobando a técnica/procedimento em dúvida e mandar pra cá numa msg, que podemos tentar palpitar em cima….

[]s

Chiappa

[douglas.ribeiro.idParticipante]

Boa tarde a todos

gostaria de deixar registrado que consegui criar o script acima, espero que algum membro com mais conhecimento nao venha ater um mal subito mas é o que se pode arrumar com pouco tempo.

BEGIN
EXECUTE IMMEDIATE ‘DROP TABLE EXAME_ITEMNF_TEMP’;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

CREATE GLOBAL TEMPORARY TABLE EXAME_ITEMNF_TEMP
(
IDNF INTEGER,
QTDE NUMBER,
VALOR FLOAT,
VALORTOTAL FLOAT
);

INSERT INTO EXAME_NF(NUMERO, DATACADASTRO, TOTALGERAL)
WITH TEN AS (SELECT 1 AS id FROM DUAL UNION
SELECT 2 AS id FROM DUAL UNION
SELECT 3 AS id FROM DUAL UNION
SELECT 4 AS id FROM DUAL UNION
SELECT 5 AS id FROM DUAL UNION
SELECT 6 AS id FROM DUAL UNION
SELECT 7 AS id FROM DUAL UNION
SELECT 8 AS id FROM DUAL UNION
SELECT 9 AS id FROM DUAL UNION
SELECT 10 AS id FROM DUAL)
SELECT ROWNUM AS NOTA, SYSDATE-10 AS DATA,ROUND(DBMS_RANDOM.VALUE(0, 99), 2) AS VALOR FROM TEN T1
CROSS JOIN TEN T2
CROSS JOIN TEN T3;

INSERT INTO EXAME_ITEMNF(IDNF, IDPRODUTO, QTDE, VALOR)
WITH THREE AS (SELECT 1 AS id FROM DUAL UNION
SELECT 2 AS id FROM DUAL UNION
SELECT 3 AS id FROM DUAL)
SELECT IDNF,
ROUND(DBMS_RANDOM.VALUE(1,5500))AS PRODUTO,
ROUND(DBMS_RANDOM.VALUE(1,10))AS QUANT,
ROUND(DBMS_RANDOM.VALUE(0, 99), 2) AS VALOR
FROM THREE
CROSS JOIN EXAME_NF E;

DELETE FROM EXAME_ITEMNF_TEMP;
INSERT INTO EXAME_ITEMNF_TEMP (IDNF, QTDE, VALOR, VALORTOTAL)
SELECT IDNF, QTDE, VALOR, QTDE*VALOR FROM EXAME_ITEMNF ORDER BY IDNF;

BEGIN
FOR ITERATOR IN 1..1000
LOOP
UPDATE EXAME_NF SET TOTALGERAL = (SELECT SUM(VALORTOTAL) FROM EXAME_ITEMNF_TEMP WHERE IDNF = ITERATOR)
,DATACADASTRO = DATACADASTRO + TRUNC(ITERATOR/100)
WHERE IDNF = ITERATOR;
END LOOP;
END;

[Autor]

Posts