TRANSACTIONS AND OTHER FUNCTIONS:

Transaction:

 

  • Database transactions consist of the following statements.
  • DML statement that makeup one consistent change to the data.
  • One DDL statement.
  • One DCL statement.

 

  • DDL stands for Data Definition Language.
  • DCL stands for Data Control Language.

 

 

NOTE: –

  • Database transaction start when the first executable statement is executed.
  • Transaction and when one of the following events occurs.
  • COMMIT or ROLLBACK is issued.
  • DDL or DCL statement executed.
  • User exits.
  • System crashes (Worst Scenario).

 

 

 

COMMIT or ROLLBACK:

 

 

Transaction

INSERT

UPDATE

INSERT

DELETE

 

 

LAST          SAVEPOINT A                               SAVEPOINT B

COMMIT

 

ROLLBACK TO B

 

ROLLBACK TO A

 

ROLLBACK

 

COMMIT:

  • Ends the current transaction by making all the pending data changes permanent.

 

 

SAVEPOINT name:

  • Marks the save point in the current transaction.

 

 

ROLLBACK TO SAVEPOINT name:

  • Ends the current transaction by discarding the SAVEPOINT and all subsequent changes.

 

 

State Of Data Before COMMIT OR ROLLBACK:

  • Previous state of the data can be recovered.
  • The current user can review the results of the DML operation by using SELECT.
  • Other users cannot view the results of the DML by the current user.
  • The effected rows are locked, other user cannot change the data within the effected rows or the effected rows are locked.

 

 

State Of Data After COMMIT:

  • Data changes are made permanent in the database.
  • The previous state of the data is permanently lost.
  • All users can view the results.
  • Locks on the effected rows are released.

 

Example: (COMMIT)

 

  • All save points are erased

 

Make Change:

 

SQL> UPDATE emp

SET deptno=10

WHERE empno = 7782;

 

Output:

1 row updated.

 

 

 

Commit Change:

 

SQL> COMMIT;

 

Output:

Commit complete.

 

Example: (Rollback)

 

1) SQL> DELETE from emp;

 

Output:

14 rows deleted.

 

 

2) SQL> ROLLBACK;

 

Output:

Rollback complete.

 

Queries:

 

1) SQL> UPDATE emp

SET deptno=20

WHERE empno = 7782;

 

Output:

1 row updated.

 

 

2) SQL> SAVEPOINT update_done;

 

Output:

Savepoint created.

 

 

3) SQL> INSERT INTO dept (deptno, dname, loc)

VALUES (50, ‘DEVELOPMENT’, ‘DETROIT’);

 

Output:

1 row created.

 

 

4) SQL> ROLLBACK TO update_done;

 

Output:

Rollback complete.

 

Creating & Managing Tables:

 

SQL> CREATE TABLE item       Table Name

(itemno  NUMBER(2),

iname   VARCHAR(14),         Data type

loc     VARCHAR(13));

 

Output:

Table created.

 

 

Creating a Table By Using A Sub-Query:

 

SQL> CREATE TABLE employee_in_dept30

AS       Must be Used!

SELECT empno, ename, hiredate, sal, deptno, mgr

FROM emp

WHERE deptno=30;

 

Output:

Table created.

 

 

Adding A Column:

 

command         Table Name

 

SQL> ALTER TABLE dept

 

ADD (job VARCHAR2(9));

 

 

 

 

Keyword   Column        data type

name

 

Output:

Table altered.

 

  • Default value will be “NULL” in the job column after the execution of the upper query.

 

 

 

 

Modifying Column:

SQL> ALTER TABLE dept

MODIFY (dname VARCHAR2(15));

New data type

Output:

Table altered.

 

 

  • Usual conversation is from similar kind of data type for example char can be converted to VARCHAR2 or empno might be change from 4 to 5 numbers.
  • If a column is modified the new data type would be applied only to subsequent addition and all previous rows would have the old data type.

 

 

 

Dropping A Table:

 

SQL> DROP TABLE dept30;

Object

Output:

Table dropped.

 

  • It simply deletes the table.
  • In scripts design to create full database structure all tables that are to created are first dropped.

 

 

 

Changing The Name Of The Object:

 

SQL> RENAME dept TO department;

 

Output:

Table renamed.

 

 

Example Of Object:

  • View
  • Table
  • Index
  • Cursor

 

 

 

Constraints:

 

dept                                                    emp

           deptno, dname, loc                          empno, deptno, ename

 

 

 

 

Primary                                          Primary  Foreign

Ke y                                                Key        Key

 

  • Unique constraint is used for fields that are not primary key and they must be unique, only a single Primary Key per table but multiple unique columns can exist.

 

 

Types Of Constraints:

 

  • Not Null
  • Unique
  • Primary Key                      Mostly Used
  • Foreign Key

 

 

  • Check                   Condition Based

 

 

Example:

 

SQL> CREATE TABLE item(

itemno NUMBER(2),          Type Of Constraint

iname VARCHAR(14),

loc VARCHAR(13),

CONSTRAINT   item_iname_uk    UNIQUE     (iname),

CONSTRAINT   item_itemno_pk PRIMARY KEY  (itemno));

Keyword      Name of constraint                               Constraint

applied on

Output:

Table created.

 

SE1:

Create a table named Empdept20 that contains the ename , sal of all employees in department 20.

 

SE2:

Rename the above table to Emp_dept_20.

 

SE3:

Write a query the ename from the user and then deletes the record of that employee.

 

SE4:

Create a view containing information about all employees in department 20.

 

SE5:

Display information about employees whose name starts with an ‘S’ from SE4.

 

SE6:

Create a table EMP10 that lists employees of department 10. Perform DML on the table after creating savepoints. Perform rollbacks, observe results and give systematic outputs.

Advertisements

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