News Update :
Hot News »
Bagikan kepada teman!

Merge Statement

Penulis : Ansar Ali on Sunday, 18 March 2018 | March 18, 2018

Sunday, 18 March 2018

Merge Statement

Merge Statement:- Merge statement is used for insert and update and delete operation simultaneously in Oracle 10g onward.

Limitation in Oracle 9i :-Merge statement introduce in Oracle 9i and there were below  limitations

1) We can not execute only update statement
2) We can not execute only insert statement
3) There was no delete statement
4) Source can be select statement but with only one table means we can not use joins and Set Operators query as a source.

Note:- All above limitation have been removed in Oracle 10g.

Limitation in Oracle 10g :- We can not use only delete statement. Delete statement is used with update statement.


Setup for LAB Test :-


SQL> --Matching records
SQL> SELECT count(*) FROM emp1 a,emp2 b WHERE a.empno=b.empno;

  COUNT(*)
----------
         8

SQL> --Not Matching records
SQL> select count(*) from (select * from emp2 minus select * from emp1);

  COUNT(*)
----------
         6
Example1:-
 
SQL> MERGE INTO emp1 a --Target Table
  2      USING EMP2 b  --Source Table
  3  ON (a.empno=b.empno)
  4  WHEN MATCHED THEN
  5  UPDATE SET ename=b.ename, sal=b.sal,job=b.job
  6  WHEN NOT MATCHED THEN
  7          INSERT (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  8          VALUES (b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno);

14 rows merged.

Example2:-
 
SQL> MERGE INTO emp1 a                 --Target Table
  2      USING (SELECT * FROM emp2) b  --Source Inline View
  3  ON (a.empno=b.empno)
  4  WHEN MATCHED THEN
  5  UPDATE SET ename=b.ename, sal=b.sal,job=b.job
  6  WHEN NOT MATCHED THEN
  7          INSERT (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  8          VALUES (b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno);

14 rows merged.
 
Note:-"AS" clause is not allowed in Merge Statement

SQL>MERGE INTO emp1 AS a --Target Table
  2      USING EMP2 AS b  --Source Table
  3  ON (a.empno=b.empno)
  4  WHEN MATCHED THEN  5  UPDATE SET ename=b.ename, sal=b.sal,job=b.job
  6  WHEN NOT MATCHED THEN
  7          INSERT (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  8          VALUES (b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.comm,b.deptno);
MERGE INTO emp1 AS a --Target Table
                *
ERROR at line 1:
ORA-02012: missing USING keyword       
Error Logging with Merge:-
 
create table copy_emp as select * from employees;

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('copy_emp', 'TAB_ERR_COPY_EMP');

select * from TAB_ERR_COPY_EMP;


MERGE INTO copy_emp c
     USING employees e
     ON (c.employee_id = e.employee_id)
   WHEN MATCHED THEN
     UPDATE SET
       c.first_name     = e.first_name,
       c.last_name      = e.last_name,
       c.email          = e.email,
       c.phone_number   = e.phone_number,
       c.hire_date      = e.hire_date,
       c.job_id         = e.job_id,
       c.salary         = e.salary,
       c.commission_pct = e.commission_pct,
       c.manager_id     = e.manager_id,
       c.department_id  = e.department_id
   WHEN NOT MATCHED THEN
     INSERT VALUES(e.employee_id, e.first_name, e.last_name,
          e.email, e.phone_number, e.hire_date, e.job_id,
          e.salary, e.commission_pct, e.manager_id,
          e.department_id)
LOG ERRORS INTO TAB_ERR_COPY_EMP('TAG_STATEMENT') REJECT LIMIT 1000;
       
       
Note:-There are some limitations for the error_logging_clause.

    1) The following conditions cause the statement to fail and roll back without invoking the error logging capability
        a) Violated deferred constraints.
        b) Any direct-path INSERT or MERGE operation that raises a unique constraint or index violation.
        c) Any update operation UPDATE or MERGE that raises a unique constraint or index violation.

    2) You cannot track errors in the error logging table for LONG, LOB, or object type columns. However, the table that is the target of the DML operation can contain these types of columns.
        a) If you create or modify the corresponding error logging table so that it contains a column of an unsupported type, and if the name of that column corresponds to an unsupported column in the target DML table, then the DML statement fails at parse time.
        b) If the error logging table does not contain any unsupported column types, then all DML errors are logged until the reject limit of errors is reached. For rows on which errors occur, column values with corresponding columns in the error logging table are logged along with the control information.
       

comments | | Read More...

PLSQL Procedure

Penulis : Ansar Ali on Friday, 9 March 2018 | March 09, 2018

Friday, 9 March 2018

PL/SQL PROCEDURE

PL/SQL procedures:-PL/SQL Procedure are PL/SQL Block,used for implement the business logic. PL/SQL Procedure create using "CREATE PROCEDURE" statement.


PL/SQL Procedure Syntax:-

CREATE [OR REPLACE] PROCEDURE [SCHEMA.] procedure_name
        [ (parameter [,parameter]) ]
    IS
        [declaration_section
            variable declarations;
            constant declarations;
        ]
    BEGIN
        [executable_section
            PL/SQL execute/subprogram body
        ]
    [EXCEPTION]
            [exception_section
            PL/SQL Exception block
            ]
    END [procedure_name];      
    /

   
Example1:-

SQL>CREATE or REPLACE PROCEDURE proc_emp_detail(eno in number,detail out emp1%rowtype)
IS
BEGIN
    SELECT * INTO detail FROM emp WHERE empno = eno;
END proc_emp_detail;
/


Procedure created.

Procedure Calling:-Procedure calling is different from function calling. Function always return a value so we can call function in "Select Statement" but we can't call Procedure in "Select Statement". Returning value of Function can be assigned to variable but we can't do the same with procedure.

SQL>DECLARE
    detail emp1%rowtype;
    eno number :=&no;
BEGIN
    proc_emp_detail(eno,detail);
    dbms_output.put_line(detail.empno ||'    '||
                                             detail.ename ||'    '||
                                             detail.deptno ||'    '||
                                             detail.sal);
END;
/


Drop Procedure:-We can drop PL/SQL procedure using DROP PROCEDURE statement,

Syntax

DROP PROCEDURE
procedure_name;

Example
SQL>DROP PROCEDURE proc_emp_detail;

Procedure dropped.
comments | | Read More...

PL/SQL Function

PL/SQL FUNCTION

PL/SQL FUNCTION:-Functions are PLSQL BLocks, used for calculation. PL/SQL functions create using CREATE FUNCTION statement.


PL/SQL Functions Syntax:-

CREATE [OR REPLACE] FUNCTION [SCHEMA.]function_name
        [ (parameter [,parameter]) ]
        RETURN return_datatype
    IS | AS
        [declaration_section
            variable declarations;
            constant declarations;
        ]
    BEGIN
        [executable_section
            PL/SQL executable block;
        ]
    [EXCEPTION]
            [exception_section
            PL/SQL Exception block
            ]
    END [function_name];
    /

PL/SQL Function Example

Example1:-Create Function that take employee number and return their name

SQL>CREATE or REPLACE FUNCTION fun_emp_name(no in number)
RETURN varchar2
IS
    name varchar2(20);
BEGIN
    select ename into name from emp where empno = no;
    return name;
END;
/

Function created.

Function Calling:-
1) Call in select statement
select fun_emp_name(7369) from dual;

2) Call in PLSQL Program

 
declare
    vname varchar2(20);
begin
    vname:=fun_emp_name(7369);
    dbms_output.put_line('Ename is '||vname);
end;
/

OR

SQL>DECLARE
    no number :=&no;
    name varchar2(20);
BEGIN
    name := fun1(no);
    dbms_output.put_line('Name:'||'   '||name);
end;
/


Drop Function:-We can drop PL/SQL function using DROP FUNCTION statements.

Syntax

DROP FUNCTION function_name;

Example
SQL>DROP FUNCTION fun_emp_name;

Function dropped.

comments | | Read More...

Having Clause

Penulis : Ansar Ali on Saturday, 24 February 2018 | February 24, 2018

Saturday, 24 February 2018

Having Clause

SQL HAVING Clause:- This statement is used filter the records of grouping functions like min,max,avg,count ..etc .This is used with "Group by" clause for filtering the result of group by query.


Syntax:-

SELECT
    col1, col2, aggregate_function(column_name), ....
    FROM table_name
    [ WHERE condition ]
    GROUP BY col1,col2
    HAVING condition;

Example :- Queries with "Group by" clause


SQL>select job,count(*) from emp group by job;

SQL>select deptno,count(*),min(sal),max(sal) from emp

group by deptno;

SQL>select deptno,job,count(*) from emp
group by deptno,job
order by 1;
 

ROLLUP & CUBE FUNCTION

 
SQL>select job,count(*) from emp
group by ROLLUP(job);

SQL>select job,count(*) from emp
group by cube(job);

SQL>select deptno,job,count(*) from emp
group by rollup(deptno,job)
order by 1;
 
SQL>select deptno,job,count(*) from emp
group by cube(deptno,job)
order by 1;


HAVING Example


SQL>select deptno,job,count(*) from emp
group by deptno,job having count(*)>1
order by 1;

SQL>select deptno,job,count(*) from emp
group by deptno,job having deptno=10
order by 1;
 
SQL>select deptno,job,count(*) from emp where deptno=10
group by deptno,job
order by 1;
comments | | Read More...

Update Statement

Penulis : Ansar Ali on Saturday, 17 February 2018 | February 17, 2018

Saturday, 17 February 2018

UPDATE STATEMENT

Update Statement :- Update statement is used to modify existing data in Table.Update statement is a DML (Data Manipulation Language)
statement that's why  Rollback is possible for update statement.

Syntax :-  Below is the syntax for delete statement

UPDATE <Table_name> SET <column_name1>=<value1>,<column_name1>=<value1>,.. WHERE <condition>;

Note:- If "where <condition>" is not used in statement then update statement will update all records of table

1) Update all records (Without "Where" clause)Example1:- Update COURSE to 'BSC' in Student table.
SQL>UPDATE STUDENT set COURSE='BSC' ;

2) Update selected records    a) Single column updateExample2:-Update course to BSC where course is BCA
SQL>UPDATE STUDENT set COURSE='BSC' where course='BCA';
Example3:-Update course to BSC where regid is less or equal to 105
SQL>UPDATE STUDENT set COURSE='BSC' where regid<=105;

    b) multiple column update
Example4:-Update course to BSC and DOB to sysdate where course is BCA   
SQL>UPDATE STUDENT set COURSE='BSC',DOB=sysdate  where course='BCA';
Example5:-Update course to BSC and regid to regid+10 where regid less or equal to 105
SQL>UPDATE STUDENT set COURSE='BSC',regid=regid+10  where regid<=105;
  
   c) Update on one table on basis of another table
Example6:-  
SQL>UPDATE STUDENT set COURSE='BSC',DOB=sysdate  where regid in (select regno from student1);
comments | | Read More...

Categories

About

 
Company Info | Contact Us | Privacy policy | Term of use | Widget | Advertise with Us | Site map
Copyright © 2011. Linux Tutorial . All Rights Reserved.
Design Template by basiclearner | Support by VULMS | Powered by Blogger