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:
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.
