//
you're reading...
SQL

A Short Guide to Oracle SQL [III] – DML & Transactions

Welcome to the third Part of SQL fundamentals. This part basically deal with DML statements and transaction concepts.

Data Manipulation Language (DML)

DML statements accesses or manipulate data in existing schema objects such as tables. The main difference between DDL and DML statements is whereas DDL changes the structure of database objects, DML used to changes the contents of objects.

Unlike DDL, changes made by DML statement are not permanent on database until changes made permanent by issuing COMMIT command. There comes concept of Transaction.

A Transaction is nothing but the set of statement (treated as logical unit of work) that takes database to one consistent state to another consistent state. More about this later in this post.

Followings are a short introduction to INSERT, SELECT, UPDATE, DELETE, MERGE, Multi-table INSERT and LOCK TABLE followed by TCL commands with little bit concepts about transaction.

Inserting Rows in the Table

INSERT command used to populate tables/views with data.

In its simplest form INSERT statement SYNTAX is –

INSERT INTO table_name (list_of_columns)
       VALUES (list_of_values|expr);

Every value or expression in VALUES clause must have a valid value/expr for the corresponding column in column(s) list.
Using this INSERT command statement you can insert one row at a time.

SQL>DESC student

Name          Null     Type         
------------- -------- ------------ 
STUDENTID     NOT NULL NUMBER(5)    
S_FIRST_NAME  NOT NULL VARCHAR2(15) 
S_LAST_NAME   NOT NULL VARCHAR2(15) 
S_DOB                  DATE         
S_SEX                  CHAR(1)      
S_REG_DATE             DATE         

SQL>INSERT INTO student
(studentID, s_first_name, s_last_name, s_dob, s_sex, s_reg_date)
   VALUES
   (90151,                 -- studentID
   'John',                 -- s_first_name
   'King',                 -- s_last_name
   '11-JUL-1991',          -- s_dob   
   'M',                    -- s_sex  
   '26-JUL-2012',          -- s_reg_date     
   );          
1 row created.        

The column(s) name in the INSERT statement is optional. If omitted, make sure you provided enough valid value in correct order. To verify the column(s) order use DESCRIBE command.

SQL>INSERT INTO student
   VALUES 
   (90152,                 -- studentID
   'Prakash',              -- s_first_name
   'Nanda',                -- s_last_name
   '25-OCT-1991',          -- s_dob   
   'M',                    -- s_sex  
   '26-JUL-2012',          -- s_reg_date     
   );   
1 row created. 

NOTE:
To verify if insertion is successful, use SELECT statement.

Executing the following statement gives us ORA-00947 Error.

SQL>INSERT INTO student
    VALUES
    (90153,Anil,Ray,27-JUL-1991,M);

INSERT INTO student
            *                                *
ERROR at line 1:
ORA-00947: not enough values

It can be rectify providing DEFAULT keyword, since we already define a default value for the column.

SQL>INSERT INTO student
    VALUES
    (90153,'Anil','Ray','27-JUL-1991','M', DEFAULT);

Or you can use the following one also.

SQL>INSERT INTO student
    (studentID, s_first_name, s_last_name, s_dob, s_sex)
    VALUES 
    (90153,'Anil', 'Ray','27-JUL-1991','M');

It’s not necessary to provide literal value/expression to all the columns in a table. If some column in a table can have NULL/DEFAULT values and you don’t want to specify value at the time of insertion, don’t specify the value for those columns. Look at the following two examples:

SQL>INSERT INTO student
    (studentID, s_first_name, s_last_name, s_dob, s_sex)
    VALUES 
    (90154,'Chris','Daves','07-FEB-1992','M');

SQL>DESC class_project

Name                Null     Type         
----------------    -------- ------------ 
CL_PROJECT_CODE     NOT NULL NUMBER(5)    
CL_PROJECT_TITLE    NOT NULL VARCHAR2(70) 
CL_PROJECT_MARKS             NUMBER(5,2) 
CL_PROJ_STU_ID               NUMBER(5)      
CL_PROJ_MOD                  NUMBER(7)    

SQL>INSERT INTO class_project
    (cl_project_code, cl_project_title, cl_proj_stu_ID)
    VALUES 
    ('PR01A', 'Database Development for the WWW', 90151);

If a table has virtual columns, the values to those columns can’t be supplied by an INSERT statement. Value for virtual columns generated during the query.

SQL>DESC vc_test

Name    Null Type   
------- ---- ------ 
NUM1         NUMBER 
NUM2         NUMBER 

SQL>ALTER TABLE vc_test ADD (mul_num AS (num1*num2));
Table altered.

SQL>DESC vc_test

Name    Null Type   
------- ---- ------ 
NUM1         NUMBER 
NUM2         NUMBER
MUL_NUM      NUMBER 

SQL>INSERT INTO vc_test(num1, num2) VALUES(2,4);
1 row created.

SQL>INSERT INTO vc_test(num1, num2) VALUES(3,6,8);
insert into vc_test(num1, num2, mul_num) values(3,6,8)
            *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

Another way to insert data in table is using subquery.

INSERT INTO table_name(list_of_columns)
       subquery;

SQL>INSERT INTO copy_student
       SELECT * FROM student;
31 rows created.

SQL>INSERT INTO class_proj_his
       SELECT * FROM class_project
       WHERE marks IS NOT NULL;
20 rows created.

If any insertion violates a constraint(s) on specified table, then the statement results in a runtime error.

SELECT Statement

Although, SELECT is a part of DML, it also known as Data Query Language.
The SELECT statement is the most commonly used SQL statement and used to retrieves data from tables, view etc. The basic structure of SELECT contains two clause, SELECT keyword followed by column(s) list and FROM clause.

          SELECT  [DISTINCT | UNIQUE] [*|columnname1 [ AS alias], …]
          FROM      tablename;
          [WHERE    condition]
          [GROUP BY group_by_expression]
          [HAVING   group_condition]
          [ORDER BY columnname];

A SELECT clause specifies the columns to be displayed. A FROM clause specifies the table name. Only SELECT and FROM clause is mandatory. A comma used to separate the column(s) name.

The asterisk (*) is used for retrieving all columns from table. You can also specify specific column name to return as in query result. The ability to select specific column of the table to form a result called PROJECTION.

SQL>SELECT * 
    FROM faculty;

F_STAFF_ID F_FIRST_NAME    F_LAST_NAME     F_DOB     F_SEX SUPERFACULTYNO
---------- --------------- --------------- --------- ----- --------------
     15234 R               Ramakrishnan    09-NOV-69 M              15201 
     15241 Sharath         Sasidharan      10-FEB-70 M              15239 
     15201 Anthony         Molinaro        31-JAN-65 M                    
     15223 Navita          Srivastava      02-NOV-67 F              15239 
     15287 Scott           Ambler          05-SEP-69 M              15229 
     15229 Lalit           Sawhney         19-NOV-62 M                    
     15291 Ashish          Wilfred         11-OCT-71 M              15267 
     15239 Ellie           Quigley         09-MAY-64 F                    
     15263 Ahmar           Abbas           09-DEC-70 M              15233 
     15267 David           Evans           09-JUL-64 M                    
     15233 Greg            Wilson          09-JUL-63 M                    
     15213 Savita          Kansal          09-DEC-66 F              15229 
     15203 Tom             Coffing         09-NOV-72 M              15267 
     15285 Susan           Visser          29-SEP-68 F              15233 

 14 rows selected 

SQL>SELECT f_staff_id, f_first_name, f_last_name, superfacultyno 
    FROM faculty;

F_STAFF_ID F_FIRST_NAME    F_LAST_NAME     SUPERFACULTYNO
---------- --------------- --------------- --------------
     15234 R               Ramakrishnan             15201 
     15241 Sharath         Sasidharan               15239 
     15201 Anthony         Molinaro                       
     15223 Navita          Srivastava               15239 
     15287 Scott           Ambler                   15229 
     15229 Lalit           Sawhney                        
     15291 Ashish          Wilfred                  15267 
     15239 Ellie           Quigley                        
     15263 Ahmar           Abbas                    15233 
     15267 David           Evans                          
     15233 Greg            Wilson                         
     15213 Savita          Kansal                   15229 
     15203 Tom             Coffing                  15267 
     15285 Susan           Visser                   15233 

 14 rows selected 

You can specify a meaningful name to a column(s) using column alias (AS clause).
If a column alias contain only one word it can be specify after column name followed by a space.

SQL>SELECT f_staff_id STAFF_ID, f_first_name AS "First Name", f_last_name "Last Name", superfacultyno
    FROM faculty;

STAFF_ID First Name      Last Name       SUPERFACULTYNO
-------- --------------- --------------- --------------
   15234 R               Ramakrishnan             15201 
   15241 Sharath         Sasidharan               15239 
   15201 Anthony         Molinaro                       
   15223 Navita          Srivastava               15239 
   15287 Scott           Ambler                   15229 
   15229 Lalit           Sawhney                        
   15291 Ashish          Wilfred                  15267 
   15239 Ellie           Quigley                        
   15263 Ahmar           Abbas                    15233 
   15267 David           Evans                          
   15233 Greg            Wilson                         
   15213 Savita          Kansal                   15229 
   15203 Tom             Coffing                  15267 
   15285 Susan           Visser                   15233 

 14 rows selected 

AS clause is optional.
If you want to preserve the case of alias or alias contains space or any special characters, use of double quotes is must.

DISTINCT/UNIQUE keyword is used to eliminate the duplicate rows from the result.

SQL>SELECT job_id, salary 
    FROM hr.employees;
JOB_ID       SALARY
---------- --------
ST_CLERK       2100 
ST_CLERK       2200 
ST_CLERK       2200 
ST_CLERK       2400 
ST_CLERK       2400 
SH_CLERK       2500 
PU_CLERK       2500 
ST_CLERK       2500 
ST_CLERK       2500 
SH_CLERK       2500 
ST_CLERK       2500 
. . . . . 
107 rows selected.

SQL>SELECT DISTINCT job_id, salary 
    FROM hr.employees;
JOB_ID       SALARY
---------- --------
ST_CLERK       2100 
ST_CLERK       2200 
ST_CLERK       2400 
PU_CLERK       2500 
SH_CLERK       2500 
ST_CLERK       2500 
PU_CLERK       2600 
SH_CLERK       2600 
ST_CLERK       2600 
ST_CLERK       2700 
PU_CLERK       2800 
. . . . . . 
82 rows selected.

SQL>SELECT UNIQUE job_id, salary 
    FROM hr.employees;
. . . . . . 
82 rows selected.

Simple arithmetic operation can be performed on column specified in SELECT clause.

SQL>SELECT empno "Employee No", sal SALARY
    FROM scott.emp;
Employee No  SALARY
----------- -------
       7369     800 
       7499    1600 
       7521    1250 
       7566    2975 
       7654    1250 
       7698    2850 
       7782    2450 
       7788    3000 
       7839    5000 
       7844    1500 
       7876    1100 
       7900     950 
       7902    3000 
       7934    1300 

 14 rows selected. 

SQL>SELECT empno "Employee No", sal SALARY, sal*12 "Annual Salary"
    FROM scott.emp;
Employee No  SALARY Annual Salary
----------- ------- -------------
       7369     800          9600 
       7499    1600         19200 
       7521    1250         15000 
       7566    2975         35700 
       7654    1250         15000 
       7698    2850         34200 
       7782    2450         29400 
       7788    3000         36000 
       7839    5000         60000 
       7844    1500         18000 
       7876    1100         13200 
       7900     950         11400 
       7902    3000         36000 
       7934    1300         15600 

 14 rows selected. 
 
SQL> SELECT job_id, min_salary, max_salary, max_salary-min_salary "Difference"
     FROM hr.jobs;

JOB_ID     MIN_SALARY MAX_SALARY Difference
---------- ---------- ---------- ----------
AD_PRES         20080      40000      19920
AD_VP           15000      30000      15000
AD_ASST          3000       6000       3000
FI_MGR           8200      16000       7800
FI_ACCOUNT       4200       9000       4800
AC_MGR           8200      16000       7800
AC_ACCOUNT       4200       9000       4800
SA_MAN          10000      20080      10080
SA_REP           6000      12008       6008
PU_MAN           8000      15000       7000
PU_CLERK         2500       5500       3000
ST_MAN           5500       8500       3000
ST_CLERK         2008       5000       2992
SH_CLERK         2500       5500       3000
IT_PROG          4000      10000       6000
MK_MAN           9000      15000       6000
MK_REP           4000       9000       5000
HR_REP           4000       9000       5000
PR_REP           4500      10500       6000

19 rows selected.

Concatenation operator represented by two vertical pipe(||) used to combine the contents of two or more columns or expression to make more meaningful information.

SQL>SELECT studentid, s_first_name ||' ' || s_last_name NAME 
    FROM student;
STUDENTID NAME                          
--------- -------------------------------
    90171 Paul Smith                      
    90151 John King                       
    90152 Prakash Nanda                   
    90153 Anil Ray                        
    90154 Chris Daves                     
    90155 Varun Naidu                     
    90156 Satish Paul                     
    90157 Rajit Nair                      
    90158 Keith Peters                    
    90159 Rucha Sharma                    
    90160 Deepti Dutt                     
    90161 Akshat Verma                    
    90162 Sarah Neilson                   
    90163 Siddhartha Malhotra             
    90164 Ellie Grace                     
    90165 Yusuf Hussain                   
    90166 Steve Kapoor                    
    90167 Sameer George                   
    90168 George Thomson                  
    90169 John Scott                      
    90170 Clara Liu                       
    90172 Jessica Nicholas                
    90173 Abhishek Iyer                   
    90174 Samantha Roy                    
    90175 Tanya Singh                     
    90176 Arvind Krishna                  
    90177 Emily Kim                       
    90178 Donna Baker                     
    90179 Vedant Malik                    
    90180 Sabrina Khan                    
    90181 Rick Irwin                      

 31 rows selected.

SQL>SELECT studentid||' -- '|| s_first_name ||' ' || s_last_name "Student ID With Name"
    FROM student;

Student ID With Name                                                      
-----------------------------------
90171 -- Paul Smith                                                         
90151 -- John King                                                          
90152 -- Prakash Nanda                                                      
90153 -- Anil Ray                                                           
90154 -- Chris Daves                                                        
90155 -- Varun Naidu                                                        
90156 -- Satish Paul                                                        
90157 -- Rajit Nair                                                         
90158 -- Keith Peters                                                       
90159 -- Rucha Sharma                                                       
90160 -- Deepti Dutt                                                        
90161 -- Akshat Verma                                                       
90162 -- Sarah Neilson                                                      
90163 -- Siddhartha Malhotra                                                
90164 -- Ellie Grace                                                        
90165 -- Yusuf Hussain                                                      
90166 -- Steve Kapoor                                                       
90167 -- Sameer George                                                      
90168 -- George Thomson                                                     
90169 -- John Scott                                                         
90170 -- Clara Liu                                                          
90172 -- Jessica Nicholas                                                   
90173 -- Abhishek Iyer                                                      
90174 -- Samantha Roy                                                       
90175 -- Tanya Singh                                                        
90176 -- Arvind Krishna                                                     
90177 -- Emily Kim                                                          
90178 -- Donna Baker                                                        
90179 -- Vedant Malik                                                       
90180 -- Sabrina Khan                                                       
90181 -- Rick Irwin                                                         

 31 rows selected 

The WHERE clause allows you to specify a conditions that limit the number of rows to be retrieved.

SQL>SELECT employee_id, last_name, first_name 
    FROM hr.employees
    WHERE salary = 2500;
EMPLOYEE_ID LAST_NAME                 FIRST_NAME         
----------- ------------------------- --------------------
        119 Colmenares                Karen                
        131 Marlow                    James                
        140 Patel                     Joshua               
        144 Vargas                    Peter                
        182 Sullivan                  Martha               
        191 Perkins                   Randall              

6 rows selected.

ORDER BY clause and conditional, logical operators etc. will cover in the next part.

Inserting rows more than one table [Multi-Table Table]

In a multitable INSERT, you took data from one table and insert those rows into more than one table based on the specified condition. Generally there are two kinds of multitable INSERT statement – Unconditional Multitable INSERT and Conditional Multitable INSERT.

In unconditional multiple INSERT, we use INSERT ALL clause followed by multiple insert_into_clauses without a condition. Following is syntax of unconditional multiple insert.

INSERT ALL
INTO <table_name> [VALUES <column_name_list)]
INTO <table_name> [VALUES <column_name_list)]
...
<SELECT Statement>;

Let’s create few table with same structure as STUDENT and then load data to these table using unconditional INSERT ALL.

SQL>SELECT COUNT(*) FROM student;
  COUNT(*)
----------
        31

SQL>CREATE TABLE copy_student AS SELECT * FROM student
    WHERE 1=2;
Table created.

SQL>CREATE TABLE a_copy_student AS SELECT * FROM student
    WHERE 1=2;
Table created.

SQL>CREATE TABLE b_copy_student AS SELECT * FROM student
    WHERE 1=2;
Table created.

SQL>CREATE TABLE c_copy_student AS SELECT * FROM student
    WHERE 1=2;
Table created.

SQL>INSERT ALL
    INTO copy_student
    
    INTO a_copy_student(studentid, s_first_name, s_last_name)
    VALUES(studentid, s_first_name, s_last_name)
    
    INTO b_copy_student(studentid, s_dob)
    VALUES(studentid, s_dob)
    
    INTO c_copy_student(studentid, s_reg_date)
    VALUES(studentid, s_dob)
 SELECT * FROM student;

124 rows created.

SQL>SELECT COUNT(*) FROM copy_student;
  COUNT(*)
----------
        31

SQL>SELECT COUNT(*) FROM a_student;
  COUNT(*)
----------
        31

SQL>SELECT COUNT(*) FROM b_student;
  COUNT(*)
----------
        31

SQL>SELECT COUNT(*) FROM c_student;
  COUNT(*)
----------
        31

Conditional insert uses WHEN clause before INTO clause. Each WHEN clause’ condition evaluated regardless to any other WHEN clause’s result and for each WHEN clause, whose condition evaluates to TRUE the corresponding INTO clause will execute.

Syntax for Conditional INSERT is:

INSERT ALL|FIRST
WHEN (<condition>) THEN
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
WHEN (<condition>) THEN
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
ELSE
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
SELECT <column_list> FROM <table_name>|subquery;

ALL is default (can be omitted). Optional ELSE clause evaluates when no WHEN clause results in TRUE.

SQL>SELECT * FROM num;

NUM1
----
   1 
   2 
   3 
   4 
   5 
   6 
   7 
   8 
   9 
  10 
  11 
  12 
  13 
  14 
  15 

15 rows selected 

SQL>CREATE TABLE a_num AS SELECT * FROM num
    WHERE 1=2;
Table created.

SQL>CREATE TABLE b_num AS SELECT * FROM num
    WHERE 1=2;
Table created.

SQL>CREATE TABLE c_num AS SELECT * FROM num
    WHERE 1=2;
Table created.

hr@O11R2> INSERT ALL
              WHEN num1>10
              THEN
              INTO a_num
              
              WHEN num1>5
              THEN
              INTO b_num
              
              ELSE
              INTO c_num
            SELECT num1 from num;

20 rows created.

hr@O11R2> select * from a_num;

      NUM1
----------
        11
        12
        13
        14
        15

hr@O11R2> select * from b_num;

      NUM1
----------
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15

10 rows selected.

hr@O11R2> select * from c_num;

      NUM1
----------
         1
         2
         3
         4
         5

Specifying FIRST with INSERT results in evaluating WHEN clause in the same order it appears in statement; however, this may not be true about INSERT ALL.
The first WHEN clause that evaluates to true, corresponding INTO clause executed and subsequent WHEN clauses for the given row will be skipped. For the rows that do not satisfy the first WHEN condition, the rest of the conditions are evaluated just as a conditional INSERT statement.

hr@O11R2> INSERT FIRST
                WHEN num1>10
                THEN
                INTO a_num
                
                WHEN num1>5
                THEN
                INTO b_num
               
                ELSE
                INTO c_num
            SELECT num1 from num;

15 rows created.

hr@O11R2> select * from a_num;

      NUM1
----------
        11
        12
        13
        14
        15

hr@O11R2> select * from b_num;

      NUM1
----------
         6
         7
         8
         9
        10    

hr@O11R2> select * from c_num;

NUM1
----------
         1
         2
         3
         4
         5

If we change the condition WHEN num1>10 THEN INTO a_num to WHEN num1>5 THEN INTO a_num and WHEN num1>5 THEN INTO b_num to WHEN num1>10 THEN INTO b_num, in this case the second insert which insert data to b_num never executed.

NOTE:

    • A single multitable insert statement can contain up to 127 WHEN clauses.
    • Conditional insert with unconditional inserts cannot be performed together in one multitable insert statement.
    • Multitable INSERT cannot be performed on view or materialized view.
    • Some other restriction can be find in SQL language reference.

Updating rows in the Table

UPDATE command modifies the data that is already resided in the table.
An update can change many rows at a time but for only one table.

UPDATE table_name
SET column_name1=new_value/expr [,column_name2=new_value/expr…]
[WHERE condition]

SQL>UPDATE class_project
    SET cl_project_marks = 178
    WHERE cl_proj_stu_ID = 90151;

WHERE clause used to specify a condition which is must TRUE for a row to be updated. Omitting the WHERE clause leads to updating of entire table.

You can update more than one column at a time by specifying them into SET clause, each separated by comma.

SQL>UPDATE class_project
    SET cl_project_marks = 181,
        cl_proj_mod = 0015241
    WHERE cl_project_code = 'PR02A';

Following example uses sub-query in the SET clause to retrieve the value of cl_project_marks and cl_proj_mod.

SQL>UPDATE class_proj_his
    SET (cl_project_marks, cl_proj_mod ) = (SELECT cl_project_marks, cl_proj_mod 
                                            FROM classs_project
                                            WHERE cl_project_code = 'PR01A')
    WHERE cl_proj_stu_ID = 90151;

Deleting rows from the Table

DELETE command is used to remove previously inserted rows from the table.

DELETE FROM tablename
[WHERE condition];

SQL> DELETE FROM student
     WHERE s_reg_date='26-JUL-2012';

9 rows deleted.

Specifying WHERE clause is optional. If omitted, it will remove all the rows from a table.

SQL> DELETE FROM copy_cl_proj;
31 rows deleted.

You cannot mention the column(s) name in DELETE statement.
DELETE will not remove table from the database. The table definition and columns in the table still stored in the database.

MERGE

Oracle introduced MERGE command in release 9i. MERGE is combined the functionality of INSERT, UPDATE and DELETE into one statement. That means it can done all the work done by INSERT, UPDATE and DELETE but in one single statement.

Based on specified condition MERGE insert or update the rows into the target table/view.

MERGE INTO tablename
USING {query|tablename} ON (condition)
[WHEN MATCHED THEN <UPDATE_clause>
    [DELETE condition]]
[WHEN NOT MATCHED THEN <INSERT_clause>]
  
SQL>SELECT cl_project_code, cl_project_marks,cl_proj_stu_id, cl_proj_mod 
    FROM class_project;

CL_PROJECT_CODE CL_PROJECT_MARKS CL_PROJ_STU_ID CL_PROJ_MOD
--------------- ---------------- -------------- -----------
PR30A                        180          90180       15203 
PR01A                        178          90151       15263 
PR02A                        181          90152       15241 
PR03A                        170          90153       15234 
PR04A                        174          90154       15263 
PR05A                        173          90155       15285 
PR06A                        177          90156       15203 
PR07A                        171          90157       15241 
PR08A                        170          90158       15203 
PR09A                        173          90159       15291 
PR10A                        172          90160       15213 
PR11A                        177          90161       15234 
PR12A                        174          90162       15291 
PR13A                        179          90163       15285 
PR14A                        170          90164       15291 
PR15A                        177          90165       15263 
PR16A                        182          90166       15203 
PR17A                        177          90167       15213 
PR18A                        176          90168       15203 
PR19A                        175          90169       15223 
PR20A                        176          90170       15291 
PR21A                        171          90171       15287 
PR22A                        173          90172       15234 
PR23A                        178          90173       15241 
PR24A                        170          90174       15263 
PR25A                        178          90175       15263 
PR26A                        171          90176       15287 
PR27A                        179          90177       15223 
PR28A                        177          90178       15291 
PR29A                        170          90179       15241 
PR31A                        180          90181       15287 

 31 rows selected 

SQL>SELECT cl_project_code, cl_project_marks,cl_proj_stu_id, cl_proj_mod 
    FROM cl_proj_his;

CL_PROJECT_CODE CL_PROJECT_MARKS CL_PROJ_STU_ID CL_PROJ_MOD
--------------- ---------------- -------------- -----------
PR30A                        132          90180       15203 
PR01A                                     90151       15263 
PR02A                                     90152       15241 
PR04A                        187          90154       15263 
PR06A                        167          90156       15203 
PR07A                                     90157       15241 
PR08A                        190          90158       15203 
PR15A                        175          90165       15263 
PR16A                        123          90166       15203 
PR18A                        143          90168       15203 
PR23A                                     90173       15241 
PR24A                        167          90174       15263 
PR25A                                     90175       15263 
PR29A                        178          90179       15241 

14 rows selected. 

SQL>MERGE INTO cl_proj_his o
      USING (SELECT cl_project_code, cl_project_marks,cl_proj_stu_id, cl_proj_mod
               FROM class_project) n
         ON (o.cl_project_code = n.cl_project_code)
      WHEN MATCHED THEN
        UPDATE SET o.cl_project_marks = n.cl_project_marks
      WHEN NOT MATCHED THEN
        INSERT (o.cl_project_code, o.cl_project_marks,o.cl_proj_stu_id,o.cl_proj_mod)
        VALUES (n.cl_project_code, n.cl_project_marks,n.cl_proj_stu_id,n.cl_proj_mod);
		
31 rows merged.

SQL>SELECT cl_project_code, cl_project_marks,cl_proj_stu_id, cl_proj_mod
    FROM cl_proj_his;

CL_PROJECT_CODE CL_PROJECT_MARKS CL_PROJ_STU_ID CL_PROJ_MOD
--------------- ---------------- -------------- -----------
PR30A                        180          90180       15203 
PR01A                        178          90151       15263 
PR02A                        181          90152       15241 
PR04A                        174          90154       15263 
PR06A                        177          90156       15203 
PR07A                        171          90157       15241 
PR08A                        170          90158       15203 
PR15A                        177          90165       15263 
PR16A                        182          90166       15203 
PR18A                        176          90168       15203 
PR23A                        178          90173       15241 
PR24A                        170          90174       15263 
PR25A                        178          90175       15263 
PR29A                        170          90179       15241 
PR11A                        177          90161       15234 
PR22A                        173          90172       15234 
PR12A                        174          90162       15291 
PR31A                        180          90181       15287 
PR28A                        177          90178       15291 
PR21A                        171          90171       15287 
PR05A                        173          90155       15285 
PR10A                        172          90160       15213 
PR13A                        179          90163       15285 
PR26A                        171          90176       15287 
PR17A                        177          90167       15213 
PR14A                        170          90164       15291 
PR09A                        173          90159       15291 
PR19A                        175          90169       15223 
PR20A                        176          90170       15291 
PR27A                        179          90177       15223 
PR03A                        170          90153       15234 

31 rows selected. 

LOCK TABLE

LOCK TABLE used to lock a table or table partition in a specified mode results in limited access for a period of time. It can be use useful in those time when many users concurrently modifying the same data. Oracle automatically implements locks. By default, Oracle performs row-level locking when we issue any DML commands.

A TCL command COMMIT or ROLLBACK issued by user automatically released lock from locked table else locked table will remain locked.

LOCK TABLE tablename 
IN SHARE|EXCLUSIVE MODE
[NOWAIT|NOWAIT];

LOCK TABLE student
   IN EXCLUSIVE MODE 
   NOWAIT;

LOCK TABLE hr.jobs
   IN EXCLUSIVE MODE;

Remember a lock never prevents other users to query that table. In SHARE MODE, other transaction can query the table but updates are allowed only if a single transaction holds the share table locks.

In EXCLUSIVE MODE no other transaction can alter the table structure or perform any kind of DML operation; however, queries on the locked table are permit.

Specifying NOWAIT clause allow database to return control immediate to the user with the message that the table is already locked by some other transaction/user. In the case of WAIT clause the LOCK TABLE statement should wait for specific number of seconds to acquire a DML lock.

If none specified, then database waits indefinitely until the table is available.

Transaction

A transaction is a logical unit of work [a (group of) DML statement that performed together] that takes a database to one consistent state to another consistent state. In SQL when we execute the DML commands, changes made by them on database is not permanent until we save the state by issuing COMMIT or undo the changes by issuing ROLLBACK (bring back to the database in its original state).

COMMIT, ROLLBACK are the Transaction Control Language (TCL) commands.

Suppose a user want to transfer 5000 from account A to account B through ATM. For this he insert ATM card to the machine, provide his pin number. After machine validates his pin number, he chooses option for money transfer and provides details of account B. After verify that account A has sufficient balance, the exact amount will be debited from account A and credited to the account B. Both accounts are updated accordingly. If for any reason any of the steps fails all the transaction must be rolled back and database will be back in its original state. It’s not possible to allow one account to change; otherwise partial updating will lead to data corruption. Transaction means all or nothing.

NOTE:
Any DDL language is implicitly committed, so executing any DDL/DCL in between of transaction will commit any uncommitted work done by that transaction. Another point to remember is exiting the tools like SQL*Plus or SQL Developer also issues implicit commit.

The successful completion of transaction makes the changes permanent and irreversible and also called as COMMIT state. If any of the steps fails then none of the changes will go through and database taken back to the state as it was before the start of the transaction.

A transaction is defined by its four fundamental properties, which is known as ACID.

ATOMICITY

Transaction either completed successfully or none of them made an impact on database. There will be no partial transaction. If anything happen before transaction completes, the database state should not change and data involve in the operation should be restored to the previous consistent state.

CONSISTENCY

A transaction must preserve consistency that means a database which is initially in a consistent state must be in a consistent state when transaction finished executing. For example at least it must satisfy all the constraint on the database.

ISOLATION

It is possible that multiple transactions are running concurrently in a database. The operation led by a transaction should not visible to any other transaction or users until the transaction complete successfully or committed. This

DURABILITY

Once transaction complete successfully, the changes made by it on the database must be preserved and available to all. Even if the machine crashes or OS fails, changes must be there after system restore.

Transaction Control Statements

COMMIT

Issuing COMMIT or COMMIT WORK command (either implicitly or explicitly) ends the transaction by making changes to database permanent and available to other sessions or users.

ROLLBACK

A ROLLBACK command undo all the changes made by transaction/DML commands back to the point when the transaction start or last committed.

SAVEPOINT

SAVEPOINT is a kind of bookmark in a transaction defined by a user. During a transaction a database may pass through multiple steps, you have an option to mark those specific points and if necessary, rollback to those point (by issuing ROLLBACK TO SAVEPOINT) instead of rolling back the complete transaction. It can be useful in long transaction.

 
SQL>SELECT * FROM scott.emp;
EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO
----- ---------- --------- ---- --------- ------- ------- ------
 7369 SMITH      CLERK     7902 17-DEC-80     800             20 
 7499 ALLEN      SALESMAN  7698 20-FEB-81    1600     300     30 
 7521 WARD       SALESMAN  7698 22-FEB-81    1250     500     30 
 7566 JONES      MANAGER   7839 02-APR-81    2975             20 
 7654 MARTIN     SALESMAN  7698 28-SEP-81    1250    1400     30 
 7698 BLAKE      MANAGER   7839 01-MAY-81    2850             30 
 7782 CLARK      MANAGER   7839 09-JUN-81    2450             10 
 7788 SCOTT      ANALYST   7566 19-APR-87    3000             20 
 7839 KING       PRESIDENT      17-NOV-81    5000             10 
 7844 TURNER     SALESMAN  7698 08-SEP-81    1500       0     30 
 7876 ADAMS      CLERK     7788 23-MAY-87    1100             20 
 7900 JAMES      CLERK     7698 03-DEC-81     950             30 
 7902 FORD       ANALYST   7566 03-DEC-81    3000             20 
 7934 MILLER     CLERK     7782 23-JAN-82    1300             10 

14 rows selected.

SQL>UPDATE scott.emp
    SET sal=sal+1000;
14 rows updated

SQL>SAVEPOINT ONE                        --Savepoint ONE.
savepoint ONE created.

SQL>INSERT INTO scott.emp VALUES(8923,'Smith','ANALYST',7566,'14-MAR-82',3700,null,20);
1 rows created.

SQL>SAVEPOINT TWO                        --Savepoint TWO.
savepoint TWO created.

SQL>DELETE FROM scott.emp
    WHERE mgr =7902;
1 rows deleted.

SQL>SAVEPOINT THREE                        --Savepoint THREE.
savepoint THREE created.

SQL>UPDATE scott.emp
    SET job='ANALYST',
        mgr=7566,
        sal=3400,
        comm=null
    WHERE empno=7499

1 rows updated.

SQL>SELECT * 
    FROM scott.emp;

EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO
----- ---------- --------- ---- --------- ------- ------- ------
 7499 ALLEN      ANALYST   7566 20-FEB-81    3400             30 
 7521 WARD       SALESMAN  7698 22-FEB-81    2250     500     30 
 7566 JONES      MANAGER   7839 02-APR-81    3975             20 
 7654 MARTIN     SALESMAN  7698 28-SEP-81    2250    1400     30 
 7698 BLAKE      MANAGER   7839 01-MAY-81    3850             30 
 7782 CLARK      MANAGER   7839 09-JUN-81    3450             10 
 7788 SCOTT      ANALYST   7566 19-APR-87    4000             20 
 7839 KING       PRESIDENT      17-NOV-81    6000             10 
 7844 TURNER     SALESMAN  7698 08-SEP-81    2500       0     30 
 7876 ADAMS      CLERK     7788 23-MAY-87    2100             20 
 7900 JAMES      CLERK     7698 03-DEC-81    1950             30 
 7902 FORD       ANALYST   7566 03-DEC-81    4000             20 
 7934 MILLER     CLERK     7782 23-JAN-82    2300             10 
 8923 Smith      ANALYST   7566 14-MAR-82    3700             20 

14 rows selected.

SQL>ROLLBACK TO SAVEPOINT THREE                        --Discarded UPDATE statement.
rollback complete.

SQL>SELECT * 
    FROM scott.emp;

EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO
----- ---------- --------- ---- --------- ------- ------- ------
 7499 ALLEN      SALESMAN  7698 20-FEB-81    2600     300     30 
 7521 WARD       SALESMAN  7698 22-FEB-81    2250     500     30 
 7566 JONES      MANAGER   7839 02-APR-81    3975             20 
 7654 MARTIN     SALESMAN  7698 28-SEP-81    2250    1400     30 
 7698 BLAKE      MANAGER   7839 01-MAY-81    3850             30 
 7782 CLARK      MANAGER   7839 09-JUN-81    3450             10 
 7788 SCOTT      ANALYST   7566 19-APR-87    4000             20 
 7839 KING       PRESIDENT      17-NOV-81    6000             10 
 7844 TURNER     SALESMAN  7698 08-SEP-81    2500       0     30 
 7876 ADAMS      CLERK     7788 23-MAY-87    2100             20 
 7900 JAMES      CLERK     7698 03-DEC-81    1950             30 
 7902 FORD       ANALYST   7566 03-DEC-81    4000             20 
 7934 MILLER     CLERK     7782 23-JAN-82    2300             10 
 8923 Smith      ANALYST   7566 14-MAR-82    3700             20 

14 rows selected.

SQL>ROLLBACK TO SAVEPOINT TWO                        --Discarded DELETE WHERE mgr=7902 statement.
rollback complete.

SQL>SELECT * 
    FROM scott.emp;

EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO
----- ---------- --------- ---- --------- ------- ------- ------
 7369 SMITH      CLERK     7902 17-DEC-80    1800             20 
 7499 ALLEN      SALESMAN  7698 20-FEB-81    2600     300     30 
 7521 WARD       SALESMAN  7698 22-FEB-81    2250     500     30 
 7566 JONES      MANAGER   7839 02-APR-81    3975             20 
 7654 MARTIN     SALESMAN  7698 28-SEP-81    2250    1400     30 
 7698 BLAKE      MANAGER   7839 01-MAY-81    3850             30 
 7782 CLARK      MANAGER   7839 09-JUN-81    3450             10 
 7788 SCOTT      ANALYST   7566 19-APR-87    4000             20 
 7839 KING       PRESIDENT      17-NOV-81    6000             10 
 7844 TURNER     SALESMAN  7698 08-SEP-81    2500       0     30 
 7876 ADAMS      CLERK     7788 23-MAY-87    2100             20 
 7900 JAMES      CLERK     7698 03-DEC-81    1950             30 
 7902 FORD       ANALYST   7566 03-DEC-81    4000             20 
 7934 MILLER     CLERK     7782 23-JAN-82    2300             10 
 8923 Smith      ANALYST   7566 14-MAR-82    3700             20 

15 rows selected.

SQL>DELETE  
    FROM scott.emp
    WHERE deptno=30;

6 row deleted.

SQL>COMMIT                                            --COMMIT all work done by transaction.
Commit complete.

SQL>SELECT * 
    FROM scott.emp;

EMPNO ENAME      JOB        MGR HIREDATE      SAL    COMM DEPTNO
----- ---------- --------- ---- --------- ------- ------- ------
 7369 SMITH      CLERK     7902 17-DEC-80    1800             20 
 7566 JONES      MANAGER   7839 02-APR-81    3975             20 
 7782 CLARK      MANAGER   7839 09-JUN-81    3450             10 
 7788 SCOTT      ANALYST   7566 19-APR-87    4000             20 
 7839 KING       PRESIDENT      17-NOV-81    6000             10 
 7876 ADAMS      CLERK     7788 23-MAY-87    2100             20 
 7902 FORD       ANALYST   7566 03-DEC-81    4000             20 
 7934 MILLER     CLERK     7782 23-JAN-82    2300             10 
 8923 Smith      ANALYST   7566 14-MAR-82    3700             20 

9 rows selected.

NOTE:
COMMIT and ROLLBACK ends a transaction but ROLLBACK TO SAVEPOINT does not.

The other two TCL commands SET TRANSACTION used to alter the default behavior of transaction like making a transaction as read-only or read/write and SET CONSTRAINT[S] used to defer constraint checking if they are created as deferrable. That means whether a deferrable constraint is checked after each DML statement (IMMEDIATE) or when the transaction is completed (DEFERRED).

REFERENCES
ORACLE DATABASE CONCEPTS – Transactions
ORACLE SQL LANGUAGE REFERENCE
Oracle Database 2 Day Developer’s Guide 11g Release 2 (11.2)
MERGE Operation

Advertisements

Discussion

Trackbacks/Pingbacks

  1. Pingback: A Short Guide to Oracle SQL [I] « musingdba - December 21, 2012

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: