Data types

there are some of the data types used by the oracle :

1. CHAR :
Fixed length storage of string. The default length of the CHAR is 1 and maximum size of the CHAR is 254.

2. NCHAR :
3. VARCHAR2
4. NVARCHAR2
5. VARCHAR
6. NUMBER
7. DATE
8. LONG
10 TIMESTAMP
11 BFILE
12 BLOB
13 CLOB
14 NCLOB
15 INT

Normalization

Normalization :

 steps to get database design that allows for efficient access and storage of data.
they are mainly used to reduce the inconsistency and redundancy.

there are majorly :

First Normal Form
Second Normal Form
Third Normal Form
Fourth Normal Form
Fifth Normal Form

Collections

     1       It is a composite data type.

     2       Here composite data type is composed of one or more fields that is it consists of one or more than  one fields in it.

     3       A procedure is also a composite data type, here collection is also a composite data type.

Why to use Collections?.

 One of the reason for using the collections is “Performance Optimization”. The following are some of the Use cases for performance optimizations

   1       To select and collect the data in bulk.

   2       To insert, update and delete more than one rows (multiple rows).

   3       To return the bulk data in “Functions”.

Basic Terminology:

 Index value:

 it’s an integer, sometimes a String based on the type of collection.

Element:

Elements in the collections are always same data types. (Records, Strings, dates etc.)

Sparse and Dense:

 A Collection is said to be Sparse if there are no index values defined in between range of the index values. Example: if an element in collection is assigned with the index value 24 and another index value 30. So there are no elements in the collection with index values in between 24 and 30. This type of collections are called as Sparse Collection. Where the Dense Collections has the elements with the index values up to 30.

Method:

Methods in the collections may be “Functions” and “Procedures”. They are attached to the variable in the collection by dot (.) annotation. They are used to manipulate the contents or information of the collection.

Following are the list of Methods :

1   EXISTS(n).

2    Count

3    Delete

4    First

5    Last

6    Trim

7    Prior(n)

8    Next(n)

9   Extend

1   Extend(n)

1   Extend(n1.n2)

   Trim(n)

1 Delete(n)

1Delete(n1,n2)

Types of Collections:

Each Collection type is with specific characteristics and used in different scenarios. There are three different types of Collections.

   1       Associated array (Index Based).

   2       Nested Tables.

   3       Varray.

Joins

Topic: JOINS

Description:  Used for joining or combining the records of the different tables.

Types: there are different types of JOINS:
    1. Inner Join
    2. Outer Join
         1. Left outer join
         2. Right outer join
         3. Left join
         4. Right Join
         5. Full outer join
    3. Natural Join
    4. Equi Join
 

Apart from these we have some set operators:
1. Union
2. Intersect

Drop

Drop:

Dropping the table means deleting the data of the table along with its definition(structure of the table).

The difference between the truncate and drop:

 Truncate : only deleting the content or  entire records  of the data.

  Drop : deleting the entire table along with its content or records.

 lets create a table:

 create table student(sid number primary key,firstname varchar2(50) not null,lastname varchar2(50) not null,email varchar2(50) not null unique);

now lets drop the table:

 drop table student;

the log will be:

drop table student succeeded.

now just see the description of the table by following query:

desc student;

the log will be:

ERROR: object STUDENT does not exist

now if we want to get the table back. fire the following query:

flashback table student to before drop;

the log will be:

flashback table succeeded.

now again see the description of the log:

Name                           Null     Type                                                                                                                                                                                        

------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SID                            NOT NULL NUMBER                                                                                                                                                                                      

FIRSTNAME                      NOT NULL VARCHAR2(50)                                                                                                                                                                                

LASTNAME                       NOT NULL VARCHAR2(50)                                                                                                                                                                                

EMAIL                          NOT NULL VARCHAR2(50)

Flashback:

when the table is dropped along with its records , if we use flashback then the table along with the data is restored. but the triggers which are created on the table does not work. you have to create the trigger again.

we can use rename to with the flashback;

Example: flashback table student to before drop rename to student_new;

Conclusion:

 whenever a table is dropped the associated triggers are also dropped.

Trigger

Trigger : 

Triggers are fired when the following events occur.

    The following events are :

    1. insert,update,delete -- DML 

    2. create,alter,drop -- DDL

    3. Login,Logoff,shutdown of the database.

Why Triggers?

 Suppose when some one is inserting in the table where you are the owner. then you can know that some one is inserting the data in the table that you own. We can say its for handling the events and for Security.

The basic syntax of the trigger is:

CREATE [OR REPLACE] TRIGGER <TRIGGERNAME>

[BEFORE | AFTER ][INSERT|UPDATE|DELETE] ON <TABLENAME>

BEGIN

 <STATEMENTS LIKE DBMS OUTPUT>

END;

/

Note : here triggers are created once and executed when ever a event is fired on the table specified in the creation of the trigger.

we cannot call trigger in the procedure or function , since it is a event handler , it is dynamic, that is it is fired when the specific queries are fired. 

Conclusion :

 Triggers are stored in the database once it is compiled and fired whenever the event occurs.

Below is the example:

 1. Create a table:

    create table student(sid number primary key,firstname varchar2(50) not null,lastname varchar2(50) not null,email varchar2(50) not null unique);

2. set the server output on:

 set serveroutput on;

3. create trigger for insert

  create or replace trigger studentinserttrigger

before insert on student

for each row

begin

 dbms_output.put_line('you are inserting into the student table');

end;

/

when the trigger is compiled you will get the following log:

 TRIGGER STUDENTINSERTTRIGGER compiled.

4. now lets insert into the table:

 insert into student values(1,'raaj','saraff','raajsaraff@self.com')

5. the log on console will be:

   1 rows inserted

    you are inserting into the student table

here in the above example , trigger is fired when the record in inserted.

In the same way, triggers for update,delete are created.

Now lets write a update trigger for the student table.

 create or replace trigger studentupdatetrigger

after update on student

for each row

begin

 dbms_output.put_line('you are updating the student table');

end;

/

the log will be: 

TRIGGER STUDENTUPDATETRIGGER compiled

Now update the record that is inserted.

update student set email = 'raajsaraff@saraff.in' where sid = 1

the console log  will be :

1 rows updated

you are updating the student table .

Now lets create trigger on delete:

create or replace trigger studentdeletetrigger 
before delete on student
begin
 dbms_output.put_line('you are deleting from student');
end;
/

The log will be :
 TRIGGER STUDENTDELETETRIGGER compiled

now just delete the record :
 delete from student;
The log will be :
1 rows deleted
 you are deleting from student.


Dropping a trigger:

drop trigger studentinserttrigger

log will be:
drop trigger studentinserttrigger succeeded.

Session Control Statements

Session Control Statements are not supported by the PL/SQL. They are basic SQL Commands.
ALTER SESSION
SET ROLE