PL/SQL is a powerful, yet straightforward database programming language. It is easy to both write and read and comes packed with lots of out-of-the-box optimizations and security features. [1]

This post is not to speak about PL/SQL in a deep way. There are many tutorials that you can find to learn how to program with that. What I'd like to do in this post is to share a scenario that I needed. It's a simple example that will make you get an idea of the use. PS: It's a beginner scenario.

The PL/SQL is a great option when you need a complex SQL with dependencies. A common scenario is when you need to select a list of results and use this in another select.

Of course, you can just run a simple SQL. The best choice will depend on what you need.

DECLARE
  quantity integer :=0;
BEGIN
  SELECT count(\*) INTO quantity
  FROM TABLE_TESTE
  WHERE TABLE_TESTE.TYPE = 'Type A';
  dbms_output.put_line ('Quantity: ' ||quantity );
END;

An example where you need to use the result of a query is when you need a list of IDs to use in another query.

The first example is using cursor. The code below iterates in the cursor and create a string with ids split by comma.

The string will be used in the second SQL inside the 'EXECUTE IMMEDIATE'. The second result will be stored in 'quantity' attribute.

DECLARE
  CURSOR c_tableTest is
    SELECT IDTESTE FROM TABLE_TESTE
    WHERE TTYPE_TESTE = 'Type A';
  listIds_str varchar2(1000);
  quantity integer :=0;

  BEGIN

    FOR id IN c_tableTest LOOP
        listIds_str := id.idteste || ',' || listIds_str;
    END LOOP;

    listIds_str := Substr(listIds_str, 0, Length(listIds_str)-1);

    dbms_output.put_line(listIds_str);

    execute immediate 'SELECT count(\*)
         FROM TABLETESTE_TABLESUPERTESTE
         WHERE TABLETESTE_TABLESUPERTESTE.IDTESTE IN ( '||listIds_str|| ' )'
         INTO quantity;

    dbms_output.put_line ( 'Quantity: ' ||quantity );

END;

If you don’t want to use CURSOR, another option is..

DECLARE
  TYPE IdsList IS TABLE OF NUMBER;
  ids IdsList;
  quantity integer :=0;
BEGIN

  EXECUTE IMMEDIATE 'SELECT IDTESTE FROM TABLE_TESTE
  WHERE TYPE_TESTE = ''Type A'' '
  BULK COLLECT INTO ids;

  FORALL i IN ids.first..ids.last
    execute immediate
      'SELECT count(\*)
       FROM TABLETESTE_TABLESUPERTESTE
       WHERE TABLETESTE_TABLESUPERTESTE.IDTESTE = :1'
       USING ids(i);
    END;

In my case, the scenario that I had to solve, I had to storage attributes to reuse in the second query. To this case, the query that I used was...

DECLARE
  TYPE IdsCurTyp IS REF CURSOR;
  TYPE IdsTesteAList IS TABLE OF NUMBER;
  TYPE IdsTesteBList IS TABLE OF NUMBER;
  ids_cv IdsCurTyp;
  testea IdsTesteAList;
  testeb IdsTesteBList;
  sql_stm VARCHAR(1000);
BEGIN

  sql_stm := 'SELECT TESTEA_TESTEB.IDTESTEA, TESTEA_TESTEB.IDTESTEB
              FROM TESTEA
                JOIN TESTEA_TESTEB ON TESTEA.IDTESTEA = TESTEA_TESTEB.IDTESTEB
                JOIN TESTEA_TESTEB ON TESTEA_TESTEB.IDTESTEB = TESTEB.TESTEB
              WHERE CDTESTEA.TYPE_TESTE = ''Type A'' ';

  OPEN ids_cv FOR sql_stm;
  FETCH ids_cv BULK COLLECT INTO testeb, testea;
  CLOSE ids_cv;

  dbms_output.put_line('testea.count: ' || testea.count);
  dbms_output.put_line('testeb.count: ' || testeb.count);

  FORALL i IN testea.first..testea.last
     execute immediate
       'DELETE FROM TESTEA_TESTEB WHERE TESTEA_TESTEB.IDTESTEA = :1 and TESTEA_TESTEB.IDTESTEB = :2'
     USING testea(i), testeb(i);

END;


Statement

EXECUTE IMMEDIATE : “prepares (parses) and immediately executes a dynamic SQL statement or an anonymous PL/SQL block” [2]. It use a string argument that is the SQL and can be use with other clauses:

  • INTO: used to an individual result
  • BULK COLLECT: used to more than one result
  • USING: used to pass arguments to the query

FORALL: It do process to all elements. It’s similar to the loop.

TYPE … IS TABLE OF …. TYPE: It can be used to create an attribute that will be stored in a temporary table. It can be used instead of varrays.

CURSOR: it is a pointer to a memory area where is possible control the rows returned by a SQL statement. [3]

Conclusion

These are simple examples of how to use PL/SQL. Maybe some of these can be simpler. The idea is to see the possibilities.

A point to say here is that you cannot use an array inside the clause IN of the SQL. And that why In some cases I created a string and put the result inside the SQL that wherein the "EXECUTE IMMEDIATE".


References