What Is macro ?

Macro : 

Macros are SQL statements stored in the Data Directory.

Collection of statements which does simple tasks.

A macro can store one or more SQL statements.

For frequent request we go for it.

We cannot take procedural control statements in side macro (If-Else,For)

Only one DDL(Create,Alter,Drop) statement we can take here

With in macro we can write a DML(SELECT,INSERT,UPDATE,DELETE Statements)

It takes only input parameters

All statements inside macro acts like transactions.

All updates with in a macro are considered a transaction
           If all steps work, all work committed.
           If a single step fails, all the updated rows are automatically rollback         to their original values prior to the macro executing.

We execute macro with EXEC command

It cannot support store procedure language.

CREATE MACRO : Each SQL statement within a macro must have its own semi-colon to help the optimizer delineate one SQL statement from another. All the SQL statements must be enclosed in parentheses to be created and treated as a single transaction.

The following syntax for a CREATE MACRO

CREATE MACRO <macro-name> AS 
(  [ SELECT........; ]
   [ INSERT........; ]
   [ UPDATE.......; ]
   [ DELETE.........;] )

Example :

CREATE MACRO emp1_m AS
( UPDATE emp1 SET job='po' WHERE job='clerk';
SELECT empno,ename,sal,deptno FROM emp1 WHERE job='po';);

In the above example macro contains two SQL statements:one is UPDATE and other one is SELECT.Both statements are enclosed in the parentheses, each of the statements ends with a semi-colon (;)        

The following syntax for a EXECUTE MACRO

EXEC <macro-name> [ (<Parameter-value-list>) ];
                           or
EXECUTE <macro-name> [ (<Parameter-value-list>) ];

The REPLACE MACRO statement is used to modify an existing macro.It replaces an existing macro with a new macro, in its entirety. There fore, the name must be exactly the same.It is very good idea to do a HELP DATABASE before replacing a macro.

The following syntax for REPLACE MACRO

REPLACE MACRO <macro-name> AS
(  [INSERT ......;]
   [UPDATE......;]
   [DELETE.......;]
   [SELECT ......;]
);

Ex: REPLACE MACRO MM AS(SELECT DEPTNO,EMPNO,SAL FROM EMP WHERE DEPTNO=10;);

It is the time to execute them.

DROP MACRO: 

The DROP MACRO statement has only one function. It deletes a macro out of the DD. Therefore, it is a very powerful and easy command to use. Additionally, there is no question that asks if you are sure you want to DROP THE MACRO and there is no undo functionality. If a user has the privilege to DROP a macro and executes a DROP MACRO command, the macro is gone. The following is the syntax of the DROP MACRO command.

An example:

DROP MACRO Myfirst_macro ;

Unlike the CREATE MACRO that had to establish the parameters and provide the SQL, the DROP MACRO does not care. The name is all it needs to eliminate the macro from the DD.

Since there is no undo function for the DROP MACRO, it is a good idea to have the CREATE MACRO statement stored somewhere on disk available for recovery. If it is not saved at creation, before dropping the macro, a SHOW MACRO can be executed to return the CREATE MACRO statement for saving on disk. However, if a large macro is being built, it should be saved initially. Otherwise, if the CREATE MACRO is too large to store in the DD, part of it may be lost using the SHOW MACRO.