PIVOT & UNPIVOT functions in Oracle 11g

PIVOT Function is used in oracle 11g to produce query output in matrix style same as what we are doing in xls or xlsx.

in oracle 10g,

select deptid,jobid,sum(sal) from emp
group by deptid,jobid
order by deptno,jobid;  

for to get ouput of a query like in Excel Pivot funcationality.

select deptid, 
sum(decode(jobid, 101, sal)) "Analyst",
sum(decode(jobid, 102, sal)) "Programmer",
sum(decode(jobid, 103, sal)) "DBA",
sum(decode(jobid, 104, sal)) "Developer"
from emp
group by deptid
order by deptid;

Using the same output in oracle 11g with below query.

select * from(select deptid,jobid,sal from emp)
PIVOT (sum(sal) FOR jobid 
in (
101 "Analyst",
102, "Programmer",
103, "DBA",
104, "Developer"
))order by deptid;

same way we can achieve the first query output using UNPIVOT function.

select * from(select deptid,jobid,sal from emp)
UNPIVOT (sum(sal) FOR jobid 
in (
101, "Analyst",
102, "Programmer",
103, "DBA",
104, "Developer",
))order by deptid;

How to increase performance of PL/SQL code using IN/OUT Parameters and NOCOPY directive?

While wring PL/SQL code, keep in mind that 

• if I need any calculations, create a function. 
• if any DB operations which does not return any values from it, create a procedure.

Following ways we can use the parameters passed and returned from functions and procedures:

• All IN parameters of procedure are passed by pointer. Variable is not copied when program block is called.However PL/SQL block is not allowed to modify the variable value.
• All OUT and IN/OUT parameters are passed by value. Variable is copied when program block is called, so the pl/sql code can modify a copy of the variable.When pl/sql block completes without error, the original variable is overwritten with the modified copy of variable. If the program block raises exception, the original value remains unchanged.
• The OUT and IN/OUT parameters can be passed by reference if we use the NOCOPY directive for them.However its not available for function return value.

A function that returns variable as pass by value.

CREATE OR REPLACE FUNCTION func_VarIN(p1 IN VARCHAR2) RETURN VARCHAR2 IS

BEGIN

  RETURN p1;

END;

/

An empty procedure using the IN/OUT parameter.

CREATE OR REPLACE PROCEDURE prc_VarINOUT(p2 IN OUT VARCHAR2) IS

BEGIN

  NULL;

END;

/

An empty procedure using IN/OUT parameter with NOCOPY directive.

CREATE OR REPLACE PROCEDURE prc_VarINOUT_NOCOPY(p3 IN OUT NOCOPY VARCHAR2) IS
BEGIN
  NULL;
END;
/

PL/SQL Code Observations: 

• Function calls perform similar to procedure calls with IN/OUT parameters.
• NOCOPY directive reduces the copy overhead and causes the call to be twice as fast then the function or regular procedure.
• using NOCOPY, bigger value of variable gives bigger boost on performance.

Using of procedures with NOCOPY rather than functions, it pefroms really good - upto 20 -25 times faster and gives us good practices for PL/SQL code clarity.