DEV Community

Luiz Fernando
Luiz Fernando

Posted on

1

plsql anonymous blocks

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;

Enter fullscreen mode Exit fullscreen mode

Heroku

Build apps, not infrastructure.

Dealing with servers, hardware, and infrastructure can take up your valuable time. Discover the benefits of Heroku, the PaaS of choice for developers since 2007.

Visit Site

Top comments (0)

AWS GenAI LIVE image

How is generative AI increasing efficiency?

Join AWS GenAI LIVE! to find out how gen AI is reshaping productivity, streamlining processes, and driving innovation.

Learn more

πŸ‘‹ Kindness is contagious

Please leave a ❀️ or a friendly comment on this post if you found it helpful!

Okay