plsql anonymous blocks

Luiz Fernando — Thu, 27 Oct 2022 01:35:06 +0000
DECLARE
  vSQL VARCHAR2(4000);
  vUser VARCHAR2(30);
  vCodBeer :NUMBER;
  vNameBeer VARCHAR2(30);
  vMachine VARCHAR2(30);
  vProgram VARCHAR2(50);
  vName Users.name%TYPE;
BEGIN

  vUser := SYS_CONTEXT('USERENV', 'OS_USER');
  vMachine := SYS_CONTEXT('USERENV', 'HOST');
  vProgram := SYS_CONTEXT('USERENV', 'MODULE');   

  FOR LIST IN (SELECT '21'AS COD, '/BEER21' AS NAMEBEER FROM DUAL 
        UNION  SELECT '22'AS COD, 'BEER22.' AS NAMEBEER FROM DUAL 
        UNION  SELECT '23'AS COD, 'BE-ER23' AS NAMEBEER FROM DUAL) 
  LOOP

    --The results of this select - down - cannot be null
    SELECT CODBEER, NAMEBEER 
      INTO vCodBeer, vNameBeer FROM BEERS 
    WHERE BEERS.COD = LIST.COD;

    IF vCodBeer = 0 THEN
      vNameBeer := REPLACE(LIST.NAMEBEER,'.','');
      vNameBeer := REPLACE(vNameBeer,'/','');
      vNameBeer := REPLACE(vNameBeer,'-',''); 

      vSQL := 'INSERT INTO BEERS  '||
              '  (CODBEER         '||
              '  ,NAMEBEER        '||
              '  ,MACHINE         '||
              '  ,PROGRAM         '||
              '  ,USER)           '||                           
              'VALUES (:CODBEER   '||
              '      , :NAMEBEER  '||
              '      , :MACHINE   '||
              '      , :PROGRAM   '||
              '      , :USER)     ';

      EXECUTE IMMEDIATE vSQL 
                  USING CODBEER, NAMEBEER, MACHINE, PROGRAM, USER;
    END IF; 
  END LOOP;
END;
DEV Community: Luiz Fernando

plsql anonymous blocks
