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.

Difference between ROW_NUMBER(),RANK(),DENSE_RANK

Let us assume, i have a emp table columns ename,sal. The emp table shows below.

ENAME	SAL
SMITH	800
ALLEN	1600
WARD	1250
JONES	2975
MARTIN	1250
BLAKE	2850
CLARK	2450
SCOTT	3000
KING	5000
TURNER	1500
ADAMS	1100
JAMES	950
FORD	3000
MILLER	1300

Now lets query the table to get the salaries of all employee name with their salaries in descending order.

Query like this.

SELECT ENAME,SAL,ROW_NUMBER() OVER(ORDER BY SAL DESC) ROW_NUMBER, RANK() OVER(ORDER BY SAL DESC)RANK, DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK FROM EMP

Out put

ENAME	SAL	ROW_NUMBER	RANK	DENSE_RANK
KING	5000	1	1	1
SCOTT	3000	2	2	2
FORD	3000	3	2	2
JONES	2975	4	4	3
BLAKE	2850	5	5	4
CLARK	2450	6	6	5
ALLEN	1600	7	7	6
TURNER	1500	8	8	7
MILLER	1300	9	9	8
WARD	1250	10	10	9
MARTIN	1250	11	10	9
ADAMS	1100	12	12	10
JAMES	950	13	13	11
SMITH	800	14	14	12

So question is which one to use? 

Its all depends on your requirement and business rule you are following.
1. Row_number to be used only when you just want to have serial number on result set. It is not as intelligent as RANK and DENSE_RANK.
2. Choice between RANK and DENSE_RANK depends on business rule you are following. Rank leaves the gaps between number when it sees common values in 2 or more rows. DENSE_RANK don't leave any gaps between ranks.
So while assigning the next rank to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.
So If you are selecting employee’s rank according to their salaries you should be using DENSE_RANK and if you are ranking students according to there marks you should be using RANK(Though it is not mandatory, depends on your requirement.)

How to get max sal,ename,dname,sal,deptno from emp, dept table ?

Hi friends the above question asked by one of CMM 5 level company.Using row_number we will find the each department wise maximum salary.

Query : SELECT E.ENAME,D.DNAME,E.DEPTNO,E.SAL FROM(

SELECT E.ENAME,E.SAL,E.DEPTNO,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN

FROM EMP E) E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO

WHERE RN=1;

The same approach using DENSE_RANK() 

Query :  SELECT E.ENAME,D.DNAME,E.SAL,E.DEPTNO FROM(

 SELECT E.ENAME,E.SAL,E.DEPTNO,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) DR 

 FROM EMP E) E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE DR=1;

Using rank() 

Query : SELECT E.ENAME,D.DNAME,E.SAL,E.DEPTNO FROM(
SELECT E.ENAME,E.SAL,E.DEPTNO,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN FROM EMP E)
E INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO WHERE RAN=1

Please comments here if you know any other methods regarding this topic.