Compute Group Salary

---

-- DATA SETUP --

create table emp_t(empno number,
                   ename varchar2(100),
                   mgr   number,
                   sal   number);

Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7839,'KING',null,1000);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7698,'BLAKE',7839,700);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7782,'CLARK',7839,500);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7566,'JONES',7839,800);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7788,'SCOTT',7566,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7902,'FORD',7566,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7369,'SMITH',7902,75);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7499,'ALLEN',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7521,'WARD',7698,200);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7654,'MARTIN',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7844,'TURNER',7698,150);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7876,'ADAMS',7788,50);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7900,'JAMES',7698,100);
Insert into EMP_T (EMPNO,ENAME,MGR,SAL) values (7934,'MILLER',7782,300);

commit;

---

-- Query 1 --

select sum(sal)
from EMP_T
start with ENAME = 'FORD'
connect by PRIOR empno=mgr;

select EMPNO, ENAME, MGR, SAL,(select sum(sal)
                                from EMP_T
                                start with ENAME = a.ENAME
                                connect by PRIOR empno=mgr) group_sal
from emp_t a;

---

-- Query 2 --

CREATE OR REPLACE FUNCTION get_group_sal (
    pin_ename VARCHAR
) RETURN NUMBER AS
    lv_grp_sal   NUMBER;
BEGIN
    SELECT SUM(sal)
    INTO   lv_grp_sal
    FROM   emp_t
    START WITH ename = pin_ename
    CONNECT BY PRIOR empno = mgr;

    RETURN lv_grp_sal;
END;
/
select EMPNO, ENAME, MGR, SAL,get_group_sal(a.ename) group_sal
from emp_t a;

---

-- Query 3 --

with FUNCTION fn_group_sal (
    pin_ename VARCHAR
) RETURN NUMBER AS
    lv_grp_sal   NUMBER;
BEGIN
    SELECT SUM(sal)
    INTO   lv_grp_sal
    FROM   emp_t
    START WITH ename = pin_ename
    CONNECT BY PRIOR empno = mgr;

    RETURN lv_grp_sal;
END;
select EMPNO, ENAME, MGR, SAL,fn_group_sal(ename) group_sal
from emp_t a;

---

Query to Extract Names from mail id

---

---

create table tab1(mail_id varchar2(50));

insert into tab1 values('siva.k.academy@gmail.com');
insert into tab1 values('jeevan.anand@yahoo.com');
insert into tab1 values('ravi@outlook.com');

commit;

select * from tab1;


with d1 as (select mail_id m, 
       substr(mail_id,1, instr(mail_id,'@')-1) n,
       substr(mail_id, instr(mail_id,'@')+1) d
from tab1),
d2 as (select m,d,n,
       instr(n,'.',1,1) f_dot ,
       instr(n,'.',1,2) s_dot 
from d1),
d3 as (select m,d,n,f_dot,s_dot, 
       substr(n,1,decode(f_dot,0,length(n),f_dot-1)) f_name,
       decode(s_dot,0,null,substr(n,f_dot+1,s_dot-f_dot-1)) m_name,
       decode(f_dot+s_dot,0,null,substr(n,decode(s_dot,0,f_dot,s_dot)+1)) l_name
from d2)
select m,f_name,m_name,l_name
from d3;

---

Two dimensional array in PLSQL using VARRAY

Example to understand "Two dimensional array in PLSQL using VARRAY"

---

SET SERVEROUTPUT ON
DECLARE
    TYPE matrix_elem_type IS  VARRAY ( 3 ) OF NUMBER;
    TYPE matrix_type IS       VARRAY ( 3 ) OF matrix_elem_type;
    lv_matrix1       matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix2       matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix3_total matrix_type := matrix_type(NULL,NULL,NULL);
    lv_matrix_elem   matrix_elem_type := matrix_elem_type(NULL,NULL,NULL);
    -- Local Procedure to print the array
    procedure print_array(pin_array matrix_type,pin_desc varchar2) as
    begin
    dbms_output.put_line('Printing the '||pin_desc||' ....');
    for i in pin_array.first..pin_array.last loop
        for j in pin_array(i).first..pin_array(i).last loop
           dbms_output.put(pin_array(i)(j)||'   ');
        end loop;
        dbms_output.put_line('');
    end loop;
    end;
BEGIN
    -- Assing First Array
    lv_matrix_elem := matrix_elem_type(1,2,3);
    lv_matrix1(1) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(4,5,6);
    lv_matrix1(2) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(7,8,9);
    lv_matrix1(3) := lv_matrix_elem;
    
    -- Printing the first array
    print_array(lv_matrix1,'First Array');
    
    -- Assing Second Array
    lv_matrix_elem := matrix_elem_type(11,12,13);
    lv_matrix2(1) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(14,15,16);
    lv_matrix2(2) := lv_matrix_elem;
    lv_matrix_elem := matrix_elem_type(17,18,19);
    lv_matrix2(3) := lv_matrix_elem;
    
    -- Printing the Second array
    print_array(lv_matrix2,'Second Array');    
    
    -- Add Array1+Array2
    
    for i in lv_matrix1.first..lv_matrix1.last loop
        for j in lv_matrix1(i).first..lv_matrix1(i).last loop
        lv_matrix_elem(j) := lv_matrix1(i)(j) + lv_matrix2(i)(j);
        end loop;
        lv_matrix3_total(i) := lv_matrix_elem;
    end loop;
    
    --Print Total Array
    print_array(lv_matrix3_total,'Total Array');    
END;