Formating Output

CREATE TABLE contact_details(emp_id      NUMBER(3),
                             first_name  VARCHAR2(40),
                             contact_num NUMBER(10),
                             email       VARCHAR(40),
                             Join_dt     DATE DEFAULT SYSDATE
                            );
                            
BEGIN
INSERT INTO contact_details VALUES(100,'Senthil', 9953090687, 'senthil.k@oracle.com',SYSDATE);
INSERT INTO contact_details VALUES(101,'Raja', 9953090688, 'raja.m@oracle.com',SYSDATE);
INSERT INTO contact_details VALUES(102,'Karthi', 9953090689, 'karthi.s@oracle.com',SYSDATE);
INSERT INTO contact_details VALUES(103,'Janani', 9953090690, 'janani.h@oracle.com',SYSDATE);
INSERT INTO contact_details VALUES(104,'Bharathi Raja', 9953090691, 'bharathi.raja@oracle.com',SYSDATE);
END;
/

SELECT * FROM contact_details;


CREATE OR REPLACE PROCEDURE contact_rep_sp
AS
/*  *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

Creating procedure to generate report for contact details 
Author   :  Murugappan Annamalai
Date     :  02-MAY-14 06.04.20.859955000 PM +05:30
    *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* */

       v_count   NUMBER(10);
       v_report_name VARCHAR(30) := 'Contact Details';
       v_header      VARCHAR(300);
       v_data        VARCHAR2(1000);

       CURSOR all_data
       IS
       SELECT * 
       FROM contact_detailS;

BEGIN

     --getting total number of records in table
     SELECT COUNT(*) INTO v_count FROM contact_details;

--printing header details
     dbms_output.put_line('Report_name    : '||v_report_name);
     dbms_output.put_line('No of Records  : '||v_count);
     dbms_output.put_line('Generated Time : '||SYSTIMESTAMP);
     
      --generating header  
      v_header := LPAD('Employee Id',10,' ')||'     '||
                  RPAD('Name',20,' ')||'        '||
                  RPAD('Contact Number',16,' ')||'       '||
                  RPAD('Email id',25,' ')||'     '||
                  RPAD('Hire Date',20,' ');

      dbms_output.put_line('-----------------------------------------------------------------------------------------------------------');
 --printing header
      dbms_output.put_line(v_header);
      dbms_output.put_line('-----------------------------------------------------------------------------------------------------------');
      
 --cursor for loop to fetch data and to print
      FOR req_data IN all_data 
      LOOP
          v_data := LPAD(TO_CHAR(req_data.emp_id),10,' ')||'     '||
                    RPAD(req_data.first_name,20,' ')||'        '||
                    RPAD(TO_CHAR(req_data.contact_num),14,' ')||'         '||
                    RPAD(req_data.email,25,' ')||'     '||
                    RPAD(req_data.join_dt,20,' ');
 --printing data
          dbms_output.put_line(v_data);          
      END LOOP;
 --end of cursor for loop
      
      dbms_output.put_line('-----------------------------------------------------------------------------------------------------------');
EXCEPTION
      WHEN others THEN
           dbms_output.put_line('Error   :  '||SQLERRM);
END contact_rep_sp;
/


--Executing contact_rep_sp proc
BEGIN
  contact_rep_sp;
END;
/


--sample output for your reference

--sample output end

DROP TABLE contact_details;
DROP PROCEDURE contac t_rep_sp;

Trigger Example

CREATE TABLE emp_rec_tb 

( 

emp_id   NUMBER(3)    , 

emp_name   VARCHAR2(30) , 

salary NUMBER(8)    , 

department_id NUMBER(3)

);

CREATE TABLE sal_details_log_tb 

(

emp_id NUMBER(3), 

old_sal NUMBER(8), 

new_sal NUMBER(8),

increament NUMBER(8), 

updated_time TIMESTAMP

);

CREATE OR REPLACE TRIGGER sal_update_trg

AFTER UPDATE OF salary ON emp_rec_tb 

FOR EACH ROW

/*  *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

Creating trigger to maintain log about updated salary details 

Author   :  Murugappan Annamalai

Date     :  23-APR-14 12.13.40.047963 PM +05:30

    *-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* */

DECLARE

       v_inc_sal NUMBER(8);

BEGIN

    v_inc_sal := :NEW.salary - :OLD.salary;

INSERT INTO sal_details_log_tb(emp_id, old_sal, new_sal, updated_time, increament) VALUES (:NEW.emp_id, :OLD.salary, :NEW.salary, SYSTIMESTAMP, v_inc_sal);

Dbms_output.put_line('Employee ID :  ' || :NEW.emp_id  );

Dbms_output.put_line('New Salary  :  ' || :NEW.salary  );

Dbms_output.put_line('Old Salary  :  ' || :OLD.salary  );

Dbms_output.put_line('Increament  :  ' ||  v_inc_sal   );

END sal_update_trg;

BEGIN

INSERT INTO emp_rec_tb VALUES ('100', 'Raj'     , 40000 , 10);

INSERT INTO emp_rec_tb VALUES ('101', 'Senthil' , 34000 , 10);

INSERT INTO emp_rec_tb VALUES ('102', 'Karthi'  , 45000 , 20);

INSERT INTO emp_rec_tb VALUES ('103', 'Ramesh'  , 28000 , 30);

INSERT INTO emp_rec_tb VALUES ('104', 'Mohan'   , 12000 , 30);

commit;

END;

/

SELECT * FROM emp_rec_tb;

UPDATE emp_rec_tb 

SET salary = 27000

WHERE emp_id = 104;

UPDATE emp_rec_tb 

SET salary = 42000

WHERE emp_id = 101;

SELECT * FROM sal_details_log_tb;

DROP TABLE emp_rec_tb;

DROP TABLE sal_details_log_tb;

DROP TRIGGER sal_update_trg;

Cursor in ORACLE

It's SQL private work area.

It open's a area of memory where the query get passed and executed.

Implicit cursor

If your query with in a block returns exactly one row then it is call it as implicit.
No need to declare it. implicitly its declared by pl/sql engine


Explicit cursor

If your query returns more than one row then its call it as explicit cursor.
you need to declare it.




Cursor attribute 

%FOUND
%NOTFOUND
%ISOPEN
%ROWCOUNT

Cursor declaration:


DECLARE

       cursor cursor_name
       is
      --your query with out INTO clause.

BEGIN

   NULL;

END;
/





DECLARE
v_First_name Employees.First_name%TYPE;
v_salary Employees.salary%TYPE;
v_hire_date Employees.Hire_date%TYPE;
v_department_id Employees.department_id%TYPE;

CURSOR emp_data
IS
SELECT first_name, salary, hire_date, department_id
FROM   employees
WHERE Department_id =90;

BEGIN
Dbms_output.put_line('-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*');
OPEN emp_data;
LOOP
FETCH emp_data INTO v_first_name, v_salary, v_hire_date, v_department_id;
EXIT WHEN emp_data%NOTFOUND;
Dbms_output.put_line('Name      :   '|| v_first_name);
Dbms_output.put_line('Salary    :   '|| v_salary);
Dbms_output.put_line('Hire Date :   '|| v_hire_date);
Dbms_output.put_line('Dept id   :   '|| v_department_id);
Dbms_output.put_line('-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*');
END LOOP;
CLOSE emp_data;
END;
/













    

PL/SQL Introduction

• Procedural language extension to sql with design feature of programming language

• Data manipulation and query statements of sql are included with in procedural units of code.

• It is mainly used to reduce the network traffic

• Pl/sql statement is used to group the set of sql statement into single block and send the entire block to the server in a single call (here set of sql statement can execute at a single time)

• It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.

PL/sql Engine

• Oracle uses a PL/SQL engine to processes the PL/SQL statements. 
• A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).

The program unit is stored in a database. When an application calls a procedure stored in the database, Oracle loads the compiled program unit into the shared pool in the system global area (SGA). The PL/SQL and SQL statement executors work together to process the statements within the procedure.

Reference : http://docs.oracle.com/cd/B19306_01/server.102/b14220/sqlplsql.htm 

Using Group by clause along with ROLLUP or CUBE operators

ROLLUP

        Use the ROLLUP operator to Produce the sub total values, 

        ROLLUP is an Extension GROUP BY clause       

SELECT Department_id, SUM (salary)

FROM   Employees

WHERE  department_id IN (10,20,30)

GROUP BY ROLLUP(Department_id);

- - - - - - - - - - - - - - - - - - - - - 

Department_id     SUM(salary)

- - - - - - - - - - - - - - - - - - - - - 

           10    4400

           20    19000

           30    24900

                  48300

- - - - - - - - - - - - - - - - - - - - -  

SELECT 4400+19000+24900 FROM dual;

48300

SELECT Department_id "Dep_id", job_id,  SUM (salary)

FROM    Employees

WHERE department_id IN (90,20,30)

GROUP BY ROLLUP(Department_id, Job_id);

- - - - - - - - - - - - - - - - - - 

Dep_id    Job_id       SUM(salary)

- - - - - - - - - - - - - - - - - - 

    20    MK_MAN        13000

    20    MK_REP         6000

    20                 19000

    30    PU_MAN        11000

    30    PU_CLERK      13900

    30                 24900

    90    AD_VP         34000

    90    AD_PRES       24000

    90                 58000

                       101900

- - - - - - - - - - - - - - - - - - 

CUBE

     The cube operator is used to produce results sets that are typically used for cross-tabular reports. 

This means Rollup produces only one possible subtotaling where as Cube produces subtotal for all possible conditions of grouping specified in the group by clause and a grand total

SELECT Department_id "Dept_id", SUM (salary)

FROM    Employees

WHERE department_id IN (10,20,30)

GROUP BY CUBE(Department_id);

- - - - - - - - - - - - 

Dept_id   SUM(salary)

- - - - - - - - - - - - 

           48300

    10    4400

    20    19000

    30    24900

- - - - - - - - - - - - 

The following query produces subtotaling results based on job,based on deptno and based on the individual jobs(MK_MAN or MK_REP or PU_MAN or  AD_ASST or PU_CLERK etc in dept 10, 20 and 30)

SELECT Department_id "Dept_id", job_id, SUM (salary)

FROM    Employees

WHERE department_id IN (10,20,30)

GROUP BY CUBE(Department_id, job_id );

- - - - - - - - - - - - - - - - - - - -

Dept_id       Job_id        SUM(salary)

- - - - - - - - - - - - - - - - - - - -

                         48300

              MK_MAN    13000

              MK_REP      6000

              PU_MAN    11000

              AD_ASST     4400

              PU_CLERK   13900

    10                    4400

    10       AD_ASST      4400

    20                  19000

    20       MK_MAN      13000

    20       MK_REP       6000

    30                  24900

    30        PU_MAN    11000

    30       PU_CLERK    13900

- - - - - - - - - - - - - - - - - - - -

Joins in oracle

                  SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. 

SELECT * FROM EMPLOYEES;

 SELECT * FROM DEPARTMENTS;

SELECT * FROM LOCATIONS;

Types of Joins :

   Equiv Join
   Outer Join
         Right Outer Join
         Left Outer Join
         Full Outer Join
   Inner Join
   Cross Join

Equiv Join :


SELECT
Employees.First_name
,    Employees.Last_name
,    Employees.Department_id
,    Departments.Department_name
,    Departments.Location_id
FROM
Employees,
Departments
WHERE
Employees.Department_id = Departments.Department_id;


Equiv Join by using table Alias name:

SELECT
E.First_name
,   E.Last_name
, E.Department_id
,   D.Department_name
, D.Location_id
FROM
Employees   E
, Departments D
WHERE
E.Department_id = D.Department_id;


select * from employees;


Course students

- - - - - - - -        - - - - - - - -
c_id    c_name s_name   c_id
- - - - - - - -        - - - - - - - -
10 Java name1 20
20 Oracle name2 10
30 dotnet name3 40
40 testing name4 30
50 php name5 60
- - - - - - - -        - - - - - - - -

Join condition : Students.c_id  = course.c_id

Join  :  It will give you matched records only.

- - - - - - - - - -
s_name c_name
 - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet  
 - - - - - - - - - -

Outer Join   :  It will give you matched records + unmatched records

Types :
 
       Right outer join
left outer join
full outer join


Right outer join

 - - - - - - - - -
s_name c_name
- - - - - - - - -
name1   oracle
name2 Java
name3 testing
name4 dotnet
  -     php
 - - - - - - - - -

Left outer join

 - - - - - - - - - - -
s_name c_name
- - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet
  name5        -
 - - - - - - - - - - -

Full outer join

 - - - - - - - - - - -
  s_name c_name
 - - - - - - - - - - -
name1      oracle
name2 Java
name3 testing
name4 dotnet
    -        php
name5 -
 - - - - - - - - - - -

Outer Join : Right Outer Join


SELECT
E.First_name
, E.Last_name
, D.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E
,    Departments D
WHERE
E.Department_id (+) = D.Department_id;



Outer Join  :  Left Outer Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM  
Employees E
, Departments D
WHERE
E.Department_id = D.Department_id(+);


Outer Join : Full Outer Join

         Discuss later


Inner Join :


Select * from Employees;

select
E1.Employee_id
, E1.First_name
, E1.Manager_id
, E2.First_name "MANAGER_NAME"
From
Employees E1
, Employees E2
Where
E1.Manager_id = E2.Employee_id;


Perform Join By using KEY WORDS and ON clause

We can written this above concepts by using following methods

  Instead of WHERE clause we can use ON clause
  No need to include + sign
  Use Following Key words


Key words:


     Join
  Right Outer Join
  Left  Outer Join
  Full  Outer Join
  Inner Join
  Cross Join


Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E   JOIN
Departments D
ON
E.Department_id = D.Department_id;


Right outer Join


SELECT
E.First_name
, E.Last_name
, E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E RIGHT OUTER JOIN
Departments D
ON
E.Department_id = D.Department_id;


Left outer Join


SELECT
E.First_name
, E.Last_name
,  E.Department_id
, D.Department_name
, D.Location_id
FROM
Employees E LEFT OUTER JOIN
Departments D
ON  
E.Department_id = D.Department_id;


Full outer Join


SELECT
E.First_name
, E.Last_name
,  E.Department_id
,  D.Department_name
,  D.Location_id
FROM
Employees E FULL OUTER JOIN
Departments D
ON
E.Department_id = D.Department_id;


Inner Join or Self Join


     Selecting data with in a table.

SELECT
E1.Employee_id
,  E1.First_name
,  E1.Manager_id
,  E2.First_name "MANAGER_NAME"
From
Employees E1 INNER JOIN
Employees E2
ON
E1.Manager_id = E2.Employee_id;


Cross Join


Cross join is a Cartesian product no of rows in the first table join with no of rows in the second table
Cartesian product is form when you ignore the where clause or valid join condition
Cross join is a wrong join
To avoid the cross join we use the where clause or valid join condition

select
E1.Employee_id
,   E1.First_name
,   E1.Manager_id
,   E2.First_name "MANAGER_NAME"
From
Employees E1 CROSS JOIN
Employees E2;

n1 x3
n2 x1
n3 x2

n1   x1
n1   x2
n1   x3
n2   x1
n2   x2
n2   x3
n3   x1
n3   x2
n3   x3


Data From 3 tables  :

Relation
      Employees and Departments table  - Department_id column.
Departments and Locations table - location_id column.

SELECT
E.First_name     ,
E.Last_name      ,
E.Department_id  ,
D.Department_name,
D.Location_id    ,
L.Street_address ,
L.city
FROM
Employees   E ,
Departments D ,
Locations   L
WHERE
E.Department_id = D.Department_id AND
D.Location_id   = L.Location_id;

If you are going to fetch data from 50 tables. You need to write 49 condition ( i.e. n-1 condition)


Additional Examples For Outer Joins


CREATE TABLE COURSE
                 (
                     CID    NUMBER(3)    ,
                     CNAME  VARCHAR2(20)
                  );

INSERT INTO COURSE VALUES (10,'ORACLE');
INSERT INTO COURSE VALUES (20,'DOTNET');
INSERT INTO COURSE VALUES (30,'JAVA');
INSERT INTO COURSE VALUES (40,'UNIX');
INSERT INTO COURSE VALUES (50,'TESTING');


SELECT * FROM COURSE;


CREATE TABLE STUDENTS
(
SNAME  VARCHAR2 (20),
CID    NUMBER (3)
);

INSERT INTO STUDENTS VALUES ('SENTHIL', 20);
INSERT INTO STUDENTS VALUES ('MURUGAPPAN', 10);
INSERT INTO STUDENTS VALUES ('RAMESH', 30);
INSERT INTO STUDENTS VALUES ('KARTHI', 40);
INSERT INTO STUDENTS VALUES ('RAVI', 60);

SELECT * FROM STUDENTS;

Equi Join


SELECT
S.SNAME  ,
    C.CID    ,
    C.CNAME
FROM
STUDENTS S ,
COURSE   C
WHERE
S.CID = C.CID;


OUTER JOIN TYPES:

  RIGHT OUTER JOIN
  LEFT  OUTER JOIN
  FULL  OUTER JOIN

RIGHT OUTER JOIN
   
     It Shows Matched as well as unmatched Records on Right side table
       [By including + on right side]


SELECT
S.SNAME  ,
C.CID    ,
C.CNAME
FROM
STUDENTS S ,
COURSE C
WHERE
S.CID (+) = C.CID;


LEFT OUTER JOIN

   It Shows Matched as well as unmatched Records on Left side table
   [By including + on Left side]


SELECT
S.SNAME ,
C.CID   ,
C.CNAME
FROM
STUDENTS S ,
COURSE C
WHERE
S.CID = C.CID (+);