Oracle SQL & PL/SQL Tutorial

making table in oracle  (With Primary Key and Not NULL)

create table stu(roll number(3) primary key,
                       name char(20) not null,
                       city char(20))

Create Table from an existing table

create table student as (select * from staff)

[This query will make a table named student with all row of staff table.Student table will take table structure as in staff table.You can make sure it by using desc student statement.You can also apple condition to get selected record from staff table]

Insert record from an existing table

       Syntax

              insert into <table_name> (select column_list from <table_name> [Condition]);

       Example 

              insert into Student (select * from stu);

      [ You can add condition for getting specific row from source table ]

ORDER BY

The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.

     Example

                  Select * from stu order by city

                  Select * from stu order by city desc

     [ Second query will show records in descending order ]

ALTER TABLE STATEMENT

Alter statement is used to alter a table i.e. to add/remove or modify a column

     Modify a column

         Syntax : 

                  alter table <table_Name

                  modify <column_name> <data_type>;

        Example :

                   alter table stu

                    modify name char(30);

       Add a column

         Syntax :

                  Alter table <table_name>

                   add column_name   Data_type

          Example :

                alter table student

                add sub varchar(20);

        Drop Column:

          Syntax:

               alter table <table_name>

               drop column <column_name>

          Example :

             alter table Student

             drop column sub;

         Rename a Column

            Syntax : 

               alter table <table_name>

              rename column Old_Name to New_Name

            Example :

                alter table ali

                rename column city to place
         Rename a Table
            Syntax:
                 alter table <table_name>
                  rename to new_name
            Example :
                alter table Stu
                rename to Student

Getting Last Row

 Oracle associate a column rowid with every record.This column put a unique number of a row in increasing order.We can apply min and max aggregate function on rowid.
you can see it by using following SQL Statement
       select rowid from student
 Last Row
      select * from t1 where rowid=(select max(rowid) from t1)
First Row 
     select * from t1 where rowid=(select min(rowid) from t1)
Delete two identical column.
    If any table has two identical row then rowid is useful to delete particular row row because rowid always has a unique value
Group and Having
Group- The Group by  clause is another section of the select statement . This option clause tells Oracle to group Rows based on distinct values that exist for specified column i.e. it create data set content several sets of records group together based on a condition
      Select sum(marks),class from the student
      group by class
  ( This query adds all the marks of related class )

 Having -The Having Clause can be used in conjunction with group by clause. Having imposes a condition on a group by clause which further filter the groups created by the group by clause

   Tips ! ( Use Rowid and find the unique rowid .Use this row id in delete statement)
SQL does not have not procedure capability that is SQL does not provide
the programming techniques of conditional checking looping and
branching that is vital for data testing before storage.
      Select sum(marks),class from the student
      group by class
      having sum(marks)>50
(This query will show all the class name which sum is greater than 50)
     Find the class marks which got highest marks
         select max(stumark) from (select max(marks) stumark from student group by class)
(This query will show the sum of highest marks form all classes )

Change Date format 
     select to_date(date_column,'YYYY-MM-DD') from table;

• SQL statement are passed to the oracle engine one each time an SQL statement is executed
  a call is made to the engine resources.This adds to the traffic on the network there by deceasing,
  the speed of data processing, specially in a multi user environment.
• While processing an SQL sentence if an error occurs the oracle engine displays its own Error messages. SQL has no facility for program handling of errors that arise during manipulation of data.

Steps for run PL/SQL

• First type your PL /SQL code on oracle command prompt
• Run code using /
• If you want to edit your code use ed to open SQL Editor

Print Your name in PL/SQL

begin
     dbms_output.put_line('Hello PL/SQL');
end;
/

Accessing individual row Using PL/SQL

BEGIN
   FOR employee_rec
      IN (SELECT *
            FROM student
           )
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.roll || employee_rec.name || employee_rec.city);
   END LOOP;
END;
/
 Make sure to check whether set serveroutput is on if its not then dbms_output.put_Line will not print something

Cursor :


A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.
This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set.

Declare
cursor mycur is select * from student
where roll=1;
tmp_var mycur%rowtype;
begin
open mycur;
fetch mycur into  tmp_var
dbms_output.put_line(tmp_var.name);
dbms_output.put_line(tmp_var.city);
close mycur;
end
/  

Print Current Date and Time 
    select sysdate from dual 
     Output: (Current Date) 
              SYSDATE
             ------------
              10-APR-13
select systimestamp from dual
Output : (Current Time )
           SYSTIMESTAMP
         -----------------------------------------------
          10-APR-13 11.51.57.000000 AM +05:30

Loop in PL/SQL

For Loop

begin
     for a in  1..10
      loop
             dbms_output.put_line(a);
      end loop;
end;
/(Print Number 1 to 10 ascending order)

begin
     for a in  reverse 1..10
      loop
             dbms_output.put_line(a);
      end loop;
end;
/(Print Number 1 to 10 descending order)

 While Loop
declare
   a  number(3);
begin
   a:=1;
  while a<=10
   loop
         dbms_output.put_line(a);
         a:=a+1;
   end loop;
end;
/

Exiting A loop

Exit statement is used to terminate a loop forcefully.There are many situation when we don't want to run any loop as it is being set to run. So you can use exit to terminate any type of loop

declare
   a  number(3);
begin
   a:=1;
  while a<=10
   loop
         dbms_output.put_line(a);
         a:=a+1;
         if a=5 then
    exit;
         end if;
   end loop;
end;

/

Goto
declare
     a number(3);
begin
    select sum(marks) into a from stu where city='vns';
    if a>1000 then
        dbms_output.put_line('inside if');
    else
        goto mylabel;
    end if;
    <<mylabel>>
        dbms_output.put_line('Call with goto');
end;
/

Switch Case

In computer programming, a switch statement is a type of selection control statement that exists in most modern imperative programming which enable us to select more than option from multiple option.Switch case reduce the complexity which we face in nested if-else so we can say that switch case is a best alternative of Nested if else
declare

     a number(2);
begin
     a:=20;
    case a
        when 1 then
               dbms_output.put_line('One');
        when 2 then
               dbms_output.put_line('two');
        else
                 dbms_output.put_line('Invalid option');   
    end case;
end;

Identifier

Identifier is use provide name to a scope for identified that scope variables in local scope if local scope has any variable same name as outer.then we can access outer variable using identifier name with . operator see the example<<demo>>
declare
    a number(10);
begin
    a:=10;
    dbms_output.put_line(a);
    declare
        a number(3);
    begin
        a:=20;
        dbms_output.put_line(demo.a);
    end;
    dbms_output.put_line(a);
end;
/

(Output : 10,20,10)

Collection

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.


PL/SQL offers these collection types:

• Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)
•  Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

 Index

declare
type myind is table of varchar2(20) index by binary_integer ;
da varchar2(20);
ob myind;
n number:=0;
begin
    ob(1):='one';
    ob(22):='two';
    ob(3):='three';
    for n in ob.last loop
        dbms_out.put_line(n);
        ob.next;
    end loop;
end;
/

Index By

DECLARE
   CURSOR c_customers is
      select  name from t1;
   TYPE c_list IS TABLE of t1.name%type INDEX BY binary_integer;
   name_list c_list;
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter +1;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter|| '):'||name_list(counter));
  END LOOP;
END;
/

Varray

DECLARE
   type namesarray IS VARRAY(5) OF VARCHAR2(10);
   type grades IS VARRAY(5) OF INTEGER;
   names namesarray;
   marks grades;
   total integer;
BEGIN
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
   marks:= grades(98, 97, 78, 87, 92);
   total := names.count;
   dbms_output.put_line('Total '|| total || ' Students');
   FOR i in 1 .. total LOOP
      dbms_output.put_line('Student: ' || names(i) || '
      Marks: ' || marks(i));
   END LOOP;
END;

/

Varray with Database

declare
    type names IS VARRAY(4) OF VARCHAR2(10);
    str names;
    t varchar2(10);   
begin
    str:=names('','','','');
    for i in 1..4
    loop
        select name into t from t1 where pid=i;
        str(i):=t;
    end loop;
    for i in 1..4 loop
        dbms_output.put_line(str(i));
    end loop;
end;
/

sequence

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value. 

Example : 

create sequence sec
start with 10
increment by 1
nocache
nocycle

Calling :

    select sec.nextval from dual;

Store Procedure

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.
Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure. Coding business logic into a single stored procedure also offers a single point of control for ensuring that business rules are correctly enforced.

Advantages

Stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users. This lowers memory requirements and invocation overhead.

By grouping SQL statements, a stored procedure allows them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time. OLTP applications, in particular, benefit because result set processing eliminates network bottlenecks.

Additionally, stored procedures enable you to take advantage of the computing resources of the server. For example, you can move computation-bound procedures from client to server, where they will execute faster. Likewise, stored functions called from SQL statements enhance performance by executing application logic within the server.
Example :

 CREATE OR REPLACE PROCEDURE insertstu(

roll stu.roll%type,

name stu.name%type,

city stu.city%type)

IS

BEGIN

  INSERT INTO student  VALUES (roll, name,city);

  COMMIT;

END;

/

Calling :

BEGIN

 insertstu(992,'chahat','luxa');

END;

/

Triggers

Database triggers are database objects created via the SQL*PLUS tool on the client and stored on the server in th oracle engine's system table.These database objects consists of the following distinct sections.

• A named database event and
• A PL/SQL block that will execute when the event occurs

Use of Database Triggers:

Since the oracle engine supports database triggers it provides a highly customizable database management system.Some of the uses to which the database triggers can be put, to customize management information by the oracle engine as follows:

• A trigger can permit DML statement against a table only if they are issued, during regular business hours or on predetermined weekdays.
• A trigger can also be used to keep an audit trails of a table(i.e. to store the modified and deleted  records of the table) along with the operation performed and the time on which the operation was performed.
      It Can be used to prevent invalid transactions.

Syntax :

    Create or Replace Trigger [Schema],triggername

            {Before,After}

            {Delete,Insert,Update[Of column..]}

            on [schema],tablename

            [Referencing {Old as old, New as new }]

     Declare

           variable declaration;

           constant declarations

      Begin

          PL/SQL subprogram body;

     Exception

         exception PL/SQL block;

     Ends

Example :

  create trigger audit_trail

after update or delete on client_master

for each row

declare

oper char(10);

client_no char(10);

name varchar2(20);

bal_due int;

begin

if updating then

oper:='update';

end if;

if deleting then

oper:='delete';

end if;

client_no:=:old.client_no;

name:=:old.name;

bal_due:=:old.bal_due;

insert into auditclient

values(client_no,name,bal_due,oper,user,sysdate);

end;

/