Locks

Locks are the mechanism used to ensure data integrity while allowing maximum concurrent access to data.

Applying locks has to be decided on the following cases:
1. Types of Locks to be applied.
2. Level of Locks to be applied.

1. Types of locks:
 Shared lock: placed on the resource whenever a 'Read' operation (select ) is performed. Multiple shared locks can be simultaneously set on a resource.

 Exclusive locks:
  placed on the resource whenever a 'Write' operation (insert , update or delete) is performed.
  Only one exclusive lock can be simultaneously set on a resource.

2. Level of Locks:
 1. Row Level: if the where clause evaluates to only one row in the table.
 2. Page Level: if the where clause evaluates on the set of data.
 3. Table level: if there is no where clause(that is the query access the entire table).

Explicit Locking:
 the technique of the lock taken on a table or its resources by a user is called "Explicit Locking".
1. users can lock the tables which they own or the tables that have been granted to them.
2. tables and rows can be explicitly locked by using either select for update statement or lock table statement.
3.  the "select ... for update "
   used to signal the oracle engine hat data currently being used needs to be updated.

basic syntax:
 lock table1,table2 in {row share|row exclusive|share update|share|share row exclusive|exclusive} [nowait]

Row Share: permits concurrent access to the locked table but prohibits user from locking. the entire table for exclusive access. row share is synonymous with share update, which is included with the compatibility with earlier versions of oracle database.

Row Exclusive: is similar as row share, but it also prohibits locking in shared mode, ROW EXCLUSIVE locks are automatically obtained when updating , inserting, or deleting share update.

Share:  permits the concurrent queries ut prohibits updated to the locked tables.

Share Row Exclusive: Share Row exclusive used to look at the whole table and to allow others to look at the rows in the table but to prohibit others form locking the table i the share.

Exclusive: Exclusive permits queries on the locked table but prohibits any other activity on it.

Nowait : if you want the database to return the control to you immediately if the specified table, partition, or table sub partition is alreday locked by the other user.
if you want to omit this clause, then the database waits until the table is available, ocks it, and returns control to you.

locks are removed under following conditions:
 1. when a transaction is committed successfully.
 2. when a roll back is performed.
 3 a roll back to a save point releases locks set after the specified save point.

Example :
 Command:
   lock table user in exclusive mode nowait;

ACID Properties

Atomicity :
 Ensures that all operations with in the work unit are completed successfully , otherwise the transaction is aborted at the point of failure and previous operations are rolled back to their original state.

Consistency:
 ensures that the database properly changes states upon a successfully committed transactions.

Isolation:
 Enables transactions to operate independently and transparent to each other.

Durability:
 Ensures that result or effect of a committed transaction persist in case of system failure.


Transaction control:

1. Commit : to save the changes.
2. Rollback : to rollback the changes.
3. Savepoint : creates points with in groups of transactions in which to case to rollback.
4. Set transaction: places  name on transaction.

Note:
1. transaction commands can only be used with insert,update and delete. 

Functions

1. Create a simple function which adds two numbers:
Command: 
       create function add
      (
        num1 in number,
        num2 in number
      )
      return number
      is
      num3 number(8);
     begin
     num3 := num1 + num2;
      return num3;
     end;
     /

2. Now call add function :
Command: 
   declare
       num3 number(2);
    begin
       num3 := add(10,20);
       dbms_output.put_line('addition of two number' || num3);
    end;
   /

Procedure

Procedure:
 Data manipulating programs can be stored in Oracle Database. These programs are written in PL/SQL. procedures, functions, packages are saved and stored  in the database and can be used as building blocks for applications.

when programmes are compiled once and stored in the database, they are called stored procedure.

Example : 

1. first create a table.

command: create table user
                 (
                  id number(10) primary key,
                  name varchar2(20)
                 );

2. Now create a procedure which inserts into the above table:

command: create procedure insertuser
                   (
                   id in number,
                   name in varchar2(20)
                   )
                   is
                   begin
                    insert into user values(id,name);
                   end;
                    /

3. Now call the procedure and insert the values::

command:
                 begin
                  insertuser(1,rama);
                  dbms_output.put_line('inserted successfully');
                 end;
                  /

4. To delete the procedure we use following command:

command: drop procedure insertuser;

SQL

SQL  stands for Structured Query Language. It is ANSI (American National Standards Institute).

Purpose: the main purpose of SQL is to communicate with the databases(here i use plural because there are many databases that use SQL for querying), for manipulating the data, deleting the data etc.
Almost all the SQL based databases have similar , in some cases same structure of query. Generally Query stands for asking or searching something. in the preform it is a doubt in certain cases. so to communicate and to retrieve data from the database we use query's that is questions to databases.
the SQL syntax's are very simple. there are some keywords used, they just look like speaking to database. the query language is just like English language. one can learn simply.

lets start with the SQL basic queries.
1. first one need to install Oracle Database. it requires a free registration in Oracle network. After signing in one must download entire database zip file.
2. for installation please go through installation guide.
3. when installing , make sure that user SCOTT is unlocked. because it provides some default tables, which are used for practice purpose. and all privileges are given to the user SCOTT(granting the roles, creating users etc are exception privileges),

lets start with the basic concept of the SQL.
the data stored in the database is in the form of the tables which contains rows and columns. A row is a collection of columns and a table is a collection of rows. Rows are called as Tuples and columns are called as Attributes.



the basic syntax for creating the table is

Command: "create table table-name (attribute-1 attribute-type,attribute-2 attribute-type,........attribute-N attribute-type);"

Example : create table student_form
                 (
                 First_name varchar2(20),
                 Last_name varchar2(50),
                 Email varchar2(30),
                Password varchar2(20),
                Dob Date,
                Address varchar2(50)  
                 );

Note: SQL syntax is not case sensitive, only the data entered in the tables are case sensitive.

In order to know the description of the table, that is , column names and its types we use following commands.

Command : "desc table-name"

Explanation : here desc is a keyword. in place of desc we can also use describe. now i think the command is pretty clear of what it does. it describes the table

Example : desc student_form;

Note : here ";" is a delimiter. it is mandatory for the query's in SQL PLUS, but not in SQL Developer.

Structure of PL/SQL blocks

Here we discuss about the basic structure of the PL/SQL block:

1. Declare

2. Begin

3. Exception

4. End

Declare:

used to define variable, constants,other PL/SQL approaches like cursors, triggers, records, collections,packages,functions,procedures, creating new Exceptions etc.

Begin:

used for writing the main logic that operate on the table data. this logic contains queries, calling the functions that are declared outside or creating the inline functions, loops, if statements,  etc..

Exceptions:

used for handling the errors.

End:

states that it is the end of the PL/SQL block.

declare	<declaration-section>
begin	<functional-section>
exception	<exception-section>
end;

PL/SQL

PL/SQL is used for "Thick Database Paradigm". let's go into deep understanding of the thick database paradigm. if an application is using oracle database and the data is not persisted, then the application is worthless. in order to persist the data, first the implementation details for persisting the data has to be hidden like tables and queries that operate on the data to persist. hiding such implementation details is said to be "Thick Database Paradigm"  approach.

Why it is thick?

Here is the answer , the PL/SQL blocks contains the queries as the code to operate on the data, these queries are written in the form of code such that the data can be changed and viewed  through the PL/SQL blocks. here the PL/SQL block acts as an interface(a medium or a bridge) and hiding the implementation details such has queries and tables. this is same as the approach used in programming. In programming there is a functionality hidden by a interface, only interface is exposed but not the actual implementation of functional details. here also same case, applying the OOPS concept such that the data is persisted, implementation details like tables and queries are hidden by PL/SQL where PL/SQL is acting like an interface. Here the SQL query fired from a PL/SQL block is treated as a special program.

On certain point of view it is possible to write applications only on the top of  SQL is possible that is without using PL/SQL. but using PL/SQL has a few advantages. some of them are listed below:
1. Integration with SQL
2. network traffic reduction.
3. increase in performance.
4. portability.

Conclusion: 
   PL/SQL combines the data manipulating power of SQL with the data processing power of procedural languages.