Responder a: script

[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;