PL/SQL

PL/SQL

1-Pivot table in Oracle
2-Multiple CTE in Oracle
3-For Loop
4-Case
5-Nested case
6-Row_Number with Partition
7-Convert from Row to Column
8-update one table base on data from other table
9-Exception Handling 
10-show first date of current month 
11-show last date of current month
12-show first date of current year 
13-show last date of current year
14-show number of day in current month 
15-convert from number to word
16-Convert Comma Separated Values into Table
17-Find the last record from a table
18-Generating Random Data In Oracle
19-Generating Random Number
20-Check if table contains any data
21-Oracle XMLTable
22-Month between 2 dates
23-Merge Statement
24-Replace Blank link
25-Error Exception in PL/SQL
26-Insert special charactor
27-Refresh MView

---

1-Pivot table in Oracle

select  Skill,

           count(decode(Skill,'Oracle',count(*),0)) as Oracle

           count(decode(Skill,'VB',count(*),0)) as VB

from table_x

group by Skill;



select *
from (
select MSISDN,sum(TOtalCost) Expend, to_char(Date_,'yyyy_mm') Month_ from dmcore.tbl_sub_allExpend@dmcore_lnk
where Date_ between '01-May-2017' and '31-Jul-2017'
group by MSISDN,to_char(Date_,'yyyy_mm'))
Pivot
 (sum(Expend) for Month_ in ('2017_05' as May,'2017_06' as Jun,'2017_07' as Jul) )



2-Multiple CTE in Oracle

with mytable1
as(select * from table1 where trunc(Date_)='20-feb-2014'),
mytable2
as(select * from table2 where trunc(Date_)='21-feb-2014' )
select * from mytable2
union
select * from mytable1;

3-For Loop

Begin
For i IN 1..100 LOOP
    DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;

SET SERVEROUTPUT ON
Begin
For i IN 1..730 LOOP
  insert into tbl_date 
  select 
  i+175,to_date('31-Dec-2017','dd-Mon-yyyy')+i from dual;
  commit;
END LOOP;

END;


4-Case

5-Nested Case

select 

     A_MSISDN, B_MSISDN, Calldate,trunc(CallDate) Date_                ,endtime,Duration,Mscid,
case when length(B_MSISDN)<9 then 'ShortCode'
     when length(B_MSISDN)=9 then
          (case when substr(B_MSISDN,0,3) ='01' then 'Onnet'
           when substr(B_MSISDN,0,3) ='08' then 'Onnet'
           when substr(B_MSISDN,0,3) ='09' then 'Onnet'
           when substr(B_MSISDN,0,3) ='07' then 'Onnet'
           else 'Xnet' 

           end)
     when length(B_MSISDN)=10 then 'Xnet'
     when length(B_MSISDN)>10 then 'Internal_Call'
end  Type
from  TabXXX 

6-Row_Number with Partition

Select ContactTel, Name,
Row_number() over (partition by name order by ContactTel) as ContactTel
from tableX 
Where name like 'A%'

7-Convert From row to Column

with mytable
as (
select ContactTel,name,
ROW_NUMBER()
OVER (PARTITION BY name ORDER BY ContactTel) AS Type
from my_contact  A),
mytable1 as
(
select name,
max(decode(Type,1,Contacttel,null)) Contact1,
max(decode(Type,2,Contacttel,null)) Contact2,
max(decode(Type,3,Contacttel,null)) Contact3,
max(decode(Type,4,Contacttel,null)) Contact4,
max(decode(Type,5,Contacttel,null)) Contact5
from mytable group by name)

select *
from Mytable1

8-update one table base on data from other table

update tbl1 x set (col1,col2)=
(select col1,col2 from tbl2 y where x.col3=y.col4)
where x.col3 in (select col3 from tbl2)

9-Exception Handling
declare
error varchar2(4000);
begin


insert into tbl_test values(1/0,'A');
commit;

Exception
when others then
error :=substr(SQLERRM,1,200);
insert into tbl_error_job(jobid,ErrorText)
values('3964',error);

end;

 10-show first date of current month

SELECT TRUNC (SYSDATE, 'MONTH') "First day of current month"
    FROM DUAL;


11-show last date of current month

SELECT TRUNC (LAST_DAY (SYSDATE)) "Last day of current month"

    FROM DUAL;

12-show first date of current year 
 
SELECT TRUNC (SYSDATE, 'YEAR') "Year First Day" FROM DUAL;
13-show last date of current year

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'YEAR'), 12) - 1 "Year Last Day" FROM DUAL

14-show number of day in current month 
SELECT CAST (TO_CHAR (LAST_DAY (SYSDATE), 'dd') AS INT) number_of_days
  FROM DUAL; 

15-convert from number to word
SELECT TO_CHAR (TO_DATE (2, 'j'), 'jsp') FROM DUAL;
two


16-Convert Comma Separated Values into Table
WITH csv
     AS (SELECT 'AA,BB,CC,DD,EE,FF'
                   AS csvdata
           FROM DUAL)
    SELECT REGEXP_SUBSTR (csv.csvdata, '[^,]+', 1, LEVEL) pivot_char
      FROM DUAL, csv
CONNECT BY REGEXP_SUBSTR (csv.csvdata,'[^,]+', 1, LEVEL) IS NOT NULL;

17-Find the last record from a table

SELECT *

  FROM employees

 WHERE ROWID IN (SELECT MAX (ROWID) FROM employees);

(OR)

SELECT * FROM employees

MINUS

SELECT *

  FROM employees

 WHERE ROWNUM < (SELECT COUNT (*) FROM employees);

18-Generating Random Data In Oracle

SELECT LEVEL empl_id,

           MOD (ROWNUM, 50000) dept_id,

           TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2) salary,

           DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)),  1, 'M',  2, 'F') gender,

           TO_DATE (

                 ROUND (DBMS_RANDOM.VALUE (1, 28))

              || '-'

              || ROUND (DBMS_RANDOM.VALUE (1, 12))

              || '-'

              || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),

              'DD-MM-YYYY')

              dob,

           DBMS_RANDOM.STRING ('x', DBMS_RANDOM.VALUE (20, 50)) address

      FROM DUAL

CONNECT BY LEVEL < 10000;

19-Generating Random Number

SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;

20-Check if table contains any data

SELECT 1

  FROM TABLE_NAME

 WHERE ROWNUM = 1;

21-Oracle XMLTable

CREATE TABLE EMPLOYEES

(

   id     NUMBER,

   data   XMLTYPE

);

INSERT INTO EMPLOYEES

     VALUES (1, xmltype ('<Employees>

    <Employee emplid="1111" type="admin">

        <firstname>John</firstname>

        <lastname>Watson</lastname>

        <age>30</age>

        <email>johnwatson@sh.com</email>

    </Employee>

    <Employee emplid="2222" type="admin">

        <firstname>Sherlock</firstname>

        <lastname>Homes</lastname>

        <age>32</age>

        <email>sherlock@sh.com</email>

    </Employee>

    <Employee emplid="3333" type="user">

        <firstname>Jim</firstname>

        <lastname>Moriarty</lastname>

        <age>52</age>

        <email>jim@sh.com</email>

    </Employee>

    <Employee emplid="4444" type="user">

        <firstname>Mycroft</firstname>

        <lastname>Holmes</lastname>

        <age>41</age>

        <email>mycroft@sh.com</email>

    </Employee>

</Employees>'));

SELECT t.id, x.*

     FROM employees t,

          XMLTABLE ('/Employees/Employee'

                    PASSING t.data

                    COLUMNS firstname VARCHAR2(30) PATH 'firstname',

                            lastname VARCHAR2(30) PATH 'lastname') x

    WHERE t.id = 1;

SELECT t.id, x.*

     FROM employees t,

          XMLTABLE ('/Employees/Employee/firstname'

                    PASSING t.data

                    COLUMNS firstname VARCHAR2 (30) PATH 'text()') x

    WHERE t.id = 1;

SELECT emp.id, x.*

     FROM employees emp,

          XMLTABLE ('/Employees/Employee'

                    PASSING emp.data

                    COLUMNS firstname VARCHAR2(30) PATH 'firstname',

                            type VARCHAR2(30) PATH '@type') x;

SELECT t.id, x.*

     FROM employees t,

          XMLTABLE ('/Employees/Employee[@emplid=2222]'

                    PASSING t.data

                    COLUMNS firstname VARCHAR2(30) PATH 'firstname',

                            lastname VARCHAR2(30) PATH 'lastname') x

    WHERE t.id = 1;

SELECT t.id, x.*

     FROM employees t,

          XMLTABLE ('/Employees/Employee[@type="admin"]'

                    PASSING t.data

                    COLUMNS firstname VARCHAR2(30) PATH 'firstname',

                            lastname VARCHAR2(30) PATH 'lastname') x

    WHERE t.id = 1;

SELECT t.id, x.*

     FROM employees t,

          XMLTABLE ('/Employees/Employee[age>40]'

                    PASSING t.data

                    COLUMNS firstname VARCHAR2(30) PATH 'firstname',

                            lastname VARCHAR2(30) PATH 'lastname',

                            age VARCHAR2(30) PATH 'age') x

    WHERE t.id = 1;

 22-Months between 2 dates

months_between(sysdate,startdate)




23-Merge Statement

Use the MERGE command to perform both INSERT and UPDATE in a single command.

 

MERGE INTO EMP a USING

 (Select * FROM HR.employees) b

ON (a.emp_no =b. employee_id )

WHEN MATCHED THEN UPDATE SET a.salary =b.salary

WHEN NOT MATCHED THEN INSERT
 (emp_no, last_name, first_name, dept_no, hire_date, salary)

VALUES
 (employee_id,last_name, first_name, department_id, hire_date, salary);

24-Replace Blank link

 select replace(MSISDN,chr(10),'')
  from huy_sale A

25-Error Exception in Oracle PL sql
 create table tbl_test (Num number);
 
  create table Tbl_Error_Exception
  (server varchar2(30),Table_name varchar2(30),sql_type varchar2(10)
  ,Exception_ varchar2(4000) ,Error_date date);
 
create or replace PROCEDURE  pro_test_exception(A_ in number , B_ in Number)
 as
error varchar2(4000);
  server_ varchar2(30);
  table_name_ varchar2(30);
  sql_type_ varchar2(10);
  begin
  insert into tbl_test select A_/B_ from dual;
  commit;
  EXCEPTION
    WHEN OTHERS THEN
    error :=substr(SQLERRM,1,200);
        dbms_output.put_line (error);
    server_ :='Reporting';
    table_name_ :='MTX_Header';
    sql_type_ :='Insert';
    insert into Tbl_Error_Exception(server,Table_name,sql_type,Exception_,Error_date)
  values(server_,table_name_,sql_type_,error,sysdate);
  end;
 
  begin
  pro_test_exception(666,0);

  end;

select * from tbl_test;
select * from Tbl_Error_Exception;

26-Insert Special Charactor

SQL> set define off;

27-Refresh Mview in oracle
EXEC DBMS_MVIEW.refresh('X.lastsubscriberstatus');