PL/SQL Macro, like in C Programming

Question

So i've been studying C programming about Macros and using them, but in my job i'm using PL/SQL a lot and I was wondering if there is some way to do the same type of thing in PL/SQL. Right now I have it calling a function with 3 different values and then returning a value but the function is so simple that I think I could do it from inside of the original stored procedure. In C a Macro is a line (or lines) of code that just get completely replaced by the call upon compilation, but it is way more efficient than calling a function over and over.

Example from C:

#define query(fieldValue, Attribute, Table) (select fieldValue from Table where record = Attribute)

and when called in the body of the code, query(value, value, value) would get completely replaced by the select statement.

just a rough example, of how it might appear in C because i'm really not sure how it would be in PL/SQL.

Is this possible in SQL? It would have to be for 2-3 lines of code, thats it.

Thanks a lot, SMKS

Answer 1

There is a way to use macros in most any language, but it does require an extra step. The m4 macroprocessor can be used as a pre-processor.

There are quite a few resources available for m4, just google for m4 macro processor.

Here is a simple PL/SQL example

Following is the macro file.

The m4_discard and m4_undiscard code is not strictly necessary, it serves to reduce whitespace in the output.

'define(m4_discard', define(m4_undiscard', `divert'(divnum))divert(-1)')dnl" m4_discard

define(`pl_sleep',`dbms_lock.sleep($1);')
define(`noop',`null;')
define(`useless_loop',
`for i in 1..$1
 loop
   $2($3)
end loop;')
m4_undiscard

Following is the PL/SQL file test.m4sql

include(./macros)

useless_loop(`10',`pl_sleep',`.1')

useless_loop(`10',`noop',`.1')

Now for some PL/SQL pre-processing

    >  m4 test.m4sql





   for i in 1..10
   loop
      dbms_lock.sleep(.1);
   end loop;


   for i in 1..10
   loop
      null;
   end loop;

There is still more white space than I would like, but you can see how m4 might be used.

Whether or not you use this depends on how badly you want to use macros.

As stated in other answers, dynamic SQL may be the answer.

At times dynamic SQL may work well, but for some uses, such as dynamic block of PL/SQL, it is a coding nightmare.

My intent is to use m4 on a current PL/SQL project where I need some logging code at the beginning and end of each procedure. This use is not really a good fit for dynamic SQL in my opinion, and m4 only needs to be used once to retrofit the code with the calls as they need to be.