ARRAY IN ORACLE PL/SQL

-- BASIC SYNTAX OF ARRAY IN ORACLE PL/SQL

CREATE OR REPLACE PROCEDURE proc1(p_strarr DBMS_SQL.VARCHAR2S ) AS
BEGIN
dbms_output.put_line('Length of array = ' || p_strarr.COUNT ) ;
FOR i IN 1..p_strarr.COUNT LOOP
dbms_output.put_line('Array element '|| i ||' = '|| p_strarr(i));
END LOOP;
END;
/

-- Test it...
SET SERVEROUTPUT ON
DECLARE
v_strarr DBMS_SQL.VARCHAR2S;
BEGIN
v_strarr(1) := 'mamun';
v_strarr(2) := 'ashik';
v_strarr(3) := 'kamal';
v_strarr(4) := '';
v_strarr(5) := 'tukh';
proc1(v_strarr);
END;
/

SYNTAX OF FOR LOOP

-- for loop sysntax

set serveroutput on
declare
begin
for i in 1..10 loop
DBMS_OUTPUT.PUT_LINE('Loop controll varriable value = ' || i);
end loop;
end;
/

INSERT MULTIPLE ROWS USING SINGLE QUERRY

-- inserting multiple rows at a time by sIngle querry

insert all
when 1=1 then into month(dt, val)
select '11-JAN-2011' dt , 5 val from dual
union all
select '11-JAN-2011' dt , 7 val from dual
union all
select '11-JAN-2011' dt , 9 val from dual
union all
select '1-JAN-2011' dt , 10 val from dual

SHOWING ALL TABLE NAME

-- SHOWING ALL TABLE NAME OF AN USER

select * from ALL_TABLES
where owner='SCOTT'

INSERT MULTIPLE TABLE BY SINGLE QUERRY

-- Inserting multiple table using one single querry
-- Inserting master and chield table at a time using one single querry

insert all
when type = 'MASTER' then
into scott.mst(id, des)
values(id, des)
when type = 'DET' then
into scott.emp(empno, sal)
values(empno, sal)
select 11 id, 'new' des,1 empno,2 sal, 'MASTER' type from dual
union all
select 1,'gg',001 empno, 50 sal, 'DET' type from dual
union all
select 1,'gg', 002 empno, 44 sal, 'DET' type from dual
union all
select 1,'gg',003 empno, 33 sal, 'DET' type from dual
union all
select 1,'gg',004 empno, 44 sal, 'DET' type from dual

CINTINIOUS RATE PRINT

-- when fx rate change then value will insert the fx_rate table.
-- But we need to show the continious rate for each date from the fx_rate table.

--example
-- let the rate table is as follows :

-- date val
-- 1/1/2011 5
-- 1/10/2011 15
-- 1/15/2011 10
-- 1/20/2011 25

-- Now output will be continius rate of 1/1/2011 to 1/20/2011
-- for this the rate from 1/1/2011 to 1/9/2011 will be 5 as the rate change at 1/10/2011
-- and similarly the rate of 1/10/2011 to 1/20/2011 will be 15


create table fx_rate
(
dt date,
val integer
)
commit;

select a.*,(select val from fx_rate where dt=(select max(dt) from fx_rate where dt<=a.value_date)) value from
(
select ((select min(dt) from fx_rate) + level - 1) value_date from dual connect by
level<=((select max(dt) from fx_rate) - (select min(dt) from fx_rate) + 1)
) a

BASIC CURSOR

----- SYMPL CURSOR EXAMPLE

SET SERVEROUTPUT ON
DECLARE
v_emp_id SCOTT.EMP.empno%TYPE;
v_emp_name SCOTT.EMP.ename%TYPE;
v_sal SCOTT.EMP.sal%TYPE;

CURSOR giftCursor IS SELECT empno, ename, sal FROM SCOTT.EMP ORDER BY empno;

BEGIN
OPEN giftCursor;

LOOP
FETCH giftCursor INTO v_emp_id, v_emp_name, v_sal;

EXIT WHEN giftCursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('v_empno = ' || v_emp_id || ', v_emp_name = ' || v_emp_name ||', v_sal = ' || v_sal);
END LOOP;
CLOSE giftCursor;

END;
/

EXTRACT TIME FROM DATE

-- Extract time from date

select to_char(sysdate,'HH12:MI:SS') from dual

-- EXTRACT method (Another approach)

SELECT EXTRACT(YEAR FROM sysdate) FROM DUAL;

-- selecting the employee who join after 1981

SELECT ename, empno, hiredate
FROM scott.emp
WHERE EXTRACT(YEAR FROM
TO_DATE(hiredate, 'DD-MON-RR')) > 1981
ORDER BY hiredate;

--- BUT I CAN NOT EXTRACT TIME USING EXTRACT FUNCTION, I NORMALLY ---EXTRACT TIME USING TO_CHAR FUNCTION....

DELETE DUPLICATE ROW

-- delete duplicate rows from table

delete from scott.det d1
where rowid >
( select min(rowid) from scott.det d2 where d1.id = d2.id )

-- ANOTHER ALTERNATE WAYYYY...

delete from scott.det a
where rowid <> ( select max(rowid)
from scott.det b
where a.id = b.id
and a.des = b.des )

FIND DUPLICATE ROW

-- find duplicate row

select ID from SCOTT.DET
group by id having count(id)>1

GROUP WISE SUM

---group wise sum...
--- SHOWING THE GROUP SUM TO EACH ROWS
--- USE OF PARTITION BY

select ename , deptno, sal,
sum(sal) over (partition by deptno) total_salary
from SCOTT.EMP
order by deptno

DENSE_RANK

--- USE OF DENSE_RANK
--- SHOWS THE RANKING OF THE RECORD IN A GROUP, RESENT FOR EACH GROUP

select ename , deptno, sal,
dense_rank() over (partition by deptno order by sal desc) salary_rank,
sum(sal) over (partition by deptno) total_salary
from SCOTT.EMP
order by deptno

DECODE

----- DECODE function...

SELECT name,decode(upper(sex), 'M', 'Male',
'F', 'Female', 'Unknown') sex
FROM employee;

CASE WHEN

--- USE OF case when
--- IT WILL COUNT THE NUMBER OF EMP WHO GET SALARY BETWEEN
--- 0 TO 3000 AND 3001 TO 10000


select
SUM(CASE WHEN SUM(SAL)
BETWEEN 0 AND 3000
THEN 1 ELSE 0 END) AS "0-3000",
SUM(CASE WHEN SUM(SAL)
BETWEEN 3001 AND 10000
THEN 1 ELSE 0 END) AS "3001-10000"
from SCOTT.EMP
group by SAL

LEAD AND LAG FUNCTION

--- USING THE LEAD AND LAG FUNCTION
--- LAG SHOWS PREVIOUS ROW, LEAD SHOWS NEXT ROWS

select * from stmenu order by parentid

select menuitem,lag(menuitem) over(order by menuitem) previousItm,
lead(menuitem) over(order by menuitem) nextItm
from stmenu

Nth HIGHEST IN A TABLE

-- Nth HIGHEST SALARY OF SCOTT.EMP TABLE

select SAL from
SCOTT.EMP a
where &p = (select count(distinct(b.SAL)) from SCOTT.EMP b where b.SAL>=a.SAL);

CUMULATIVE SUM

-- CUMULATIVE SUM ...

SELECT
TO_CHAR(HIREDATE,'MON-YY') month, SUM(SAL) AS SALARY_AMMOUNT,
SUM(SUM(SAL)) OVER
(ORDER BY TO_CHAR(HIREDATE,'MON-YY') ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS CUMULATIVE_SALARY_AMMOUNT
FROM SCOTT.EMP
GROUP BY TO_CHAR(HIREDATE,'MON-YY')
ORDER BY TO_CHAR(HIREDATE,'MON-YY') ;

TREE VIEW IN ORACLE

-- SUMULATION OF TREE VIEW IN ORACLE
-- PARENT CHIELD RELATION IN ORACLE

CREATE TABLE stmenu (
id int,
Menuitem varchar2(75),
parentid int);

select * from stmenu


INSERT into stmenu values ('1','C:\Documents and Settings\All Users\Start Menu\Programs\','');
INSERT into stmenu values ('2','Startup\','1');
INSERT into stmenu values ('3','Administrative Tools\','1');
INSERT into stmenu values ('4','Accessories\','1');
INSERT into stmenu values ('5','System Tools\','4');
INSERT into stmenu values ('6','Internet Explorer.lnk','1');
INSERT into stmenu values ('7','Address Book.lnk','4');
INSERT into stmenu values ('8','Calculator.lnk','4');
INSERT into stmenu values ('9','Clock.lnk','4');
INSERT into stmenu values ('10','Entertainment','4');
INSERT into stmenu values ('11','Notepad.lnk','4');
INSERT into stmenu values ('12','Paint.lnk','4');
INSERT into stmenu values ('13','Synchronize.lnk','4');
INSERT into stmenu values ('14','Disk Cleanup.lnk','5');
INSERT into stmenu values ('15','Disk Defragmenter.lnk','5');
INSERT into stmenu values ('16','Files and Settings Transfer Wizard.lnk','5');
INSERT into stmenu values ('17','Scheduled Tasks.lnk','5');
INSERT into stmenu values ('18','Authorization Manager.lnk','3');
INSERT into stmenu values ('19','Certification Authority.lnk','3');
INSERT into stmenu values ('20','Cluster Administrator.lnk','3');
INSERT into stmenu values ('21','Component Services.lnk','3');
INSERT into stmenu values ('22','Computer Management.lnk','3');
INSERT into stmenu values ('23','Connection Manager Administration Kit.lnk','3');
INSERT into stmenu values ('24','Data Sources (ODBC).lnk','3');
INSERT into stmenu values ('25','DHCP.lnk','3');
INSERT into stmenu values ('26','Distributed File System.lnk','3');
INSERT into stmenu values ('27','DNS.lnk','3');
INSERT into stmenu values ('28','Event Viewer.lnk','3');
INSERT into stmenu values ('29','Group Policy Management.lnk','3');

select * from stmenu

---- TOTAL TREE VIEW START WITH THE PARENT NODE NULL

select (lpad(' ',10*(level-1)) || menuitem) MENU_ITEAM, parentid
from stmenu
start with parentid is null
connect by prior id=parentid

------ TOTAL TREE SHOWING PARENT NAME ALSO

select lpad(' ',10*(level-1)) || menuitem, parentid,
(select menuitem as parent_name from stmenu where id=x.parentid) parent_name
from stmenu x
start with parentid is null
connect by prior id=parentid

------ SHOWING ONLY 29th PARENT'S AND IT'S CHIELD

select lpad(' ',10*(level-1)) || menuitem, parentid,
(select menuitem as parent_name from stmenu where id=x.parentid) parent_name
from stmenu x
start with id=29
connect by prior parentid=id

--- if change the prior condition as this
--- AT PRIOR CONDITION, PARENTID=ID AND ID= PRANETID IS NOT SAME

select lpad(' ',10*(level-1)) || menuitem, parentid,
(select menuitem as parent_name from stmenu where id=x.parentid) parent_name
from stmenu x
start with id=29
connect by prior id=parentid

----- START WITH PARENT ID = 1 VIEW

SELECT
lpad(' ',5*(parentid-1))|| Menuitem
FROM stmenu
CONNECT BY PRIOR id=parentid
START WITH id=1;


select * from stmenu


---------- documentation ---
-- PRIOR - identifies the parent row in the column.
-- CONNECT BY PRIOR id=parentid will return different results to CONNECT BY PRIOR parentid=id
-- LEVEL - Returns a number indicating the level in the heirarchy: 1 for a root row, 2 for a child of a root, and so on.