Thursday, 8 October 2015

Triggers

A trigger is special type of stored procedure and is procedure and is defined on table or views. Triggers are automatically executed automatically fire when an update, insert, or delete command executed in table. Triggers are a powerful tool that is used to enforce the database rules automatically while you modify the data. The advantage of Triggers To generate the resulting data automatically Avoiding invalid transaction in database. Provide transparent event logging and advanced auditing data modifications. Maintain synchronous table replicates and check the status of the access.

Part of Triggers

A database Trigger has five part.

1: Trigger timing
2: Trigger command
3: Trigger level
4: Trigger restriction
5: Trigger body

Procedures

In a database management system (DBMS), a procedure is a set of structured Query Language (SQL) statements with an assigned name that's stored in the database executed form so that it can be shared by a number of programs.

It is like as Function the main difference between a procedure and function is the, a Function must always return a value, but a Procedure does not return a value.

The procedures can be helpful in controlling access to data (end-users may enter or change data but do not write procedures), preserving data integrity (information is entered in a consistent manner) and improving productivity (statements in a stored procedure only need to be written one time.

Stored procedures provide a powerful way to code application logic that can be stored on the server.

A stored procedure or in simple a proc is a named PL/SQL block which performs one or more specific task. Oracle also uses an additional type of subprogram called a function.

A Procedure has two parts:

1: Header 2: Body

The header contains of the name of the procedure and the parameters or variables passed to the procedure. The body contains declaration section, execution section and exception section it is similar to a normal PL/SQL block.

Packages

A package is an oracle object, which holds other objects within it. Objects commonly held within a package are Procedures, variables, constants, cursors and exceptions. It is method of creating generic, encapsulation re-useable code. A package once written and debugged is compiled and stored in Oracle's system tables held in an Oracle Database.

Advantages of PL/SQL Packages

Packages have many advantages like modularity, easier application design, added functionality and increase performance.

Function

A function is a named PL/SQL Block like as the procedure, and a Function must always return a value. Using CREATE FUNCTION statement to create a standalone stored function or a call specification. A stored function (also called a user function or user defined function) is a set of PL/SQL statements you can call by name. stored functions are very similar to procedures, except that a function returns a value to the environment in which it is called. User functions can be used as part of a SQL expression.

Syntax of create function

Create (or replace) Function function_name[parameters]
    Return return_datatype;
Is
    Declaration_part
Begin
    Execution_part
    Return return_variable;
Exeception
    Exeception_part
    Return return_variable;
End;

Return Type: The header section defines the return type of the fuction. The return data type can be any of the data type like varchar, number etc.

Cursors

Cursors are the most common and important terms in terms of database terminology. It is a core database programing concepts, which forms a basic unit of execution of SQL statement. A cursor is created temporary work area in the system memory when a SQL statement is executed. A cursor holds information on a select statement and the row of data accessed by it. There are two types of Cursors in PL/SQL. Implicit Cursors: These are created by default when DML statement like, Insert, Update, and Delete command executed. Explicit Cursors: this is created when Select command executed and that return more than one row.

Cursor Execution Cycle:

1: Open Stage
2: Fetch Stage
3: Close Stage

Exception Handling

PL/SQL has a feature to handle the Exceptions which are occurs in a PL/SQL Block and this feature called exception Handling. Using this Exception Handling you can test the code and avoid it from exiting shortly. When an exception occur messages which explains its cause is received. Exception message in PL/SQL blocks consists of three parts.

1: Type of Exception
2: An Error Code
3: A message

Attribute Data types

Attribute Data type is column data type. Attribute Data types is to inherit the specification of database to program variable of PL/SQL. Attribute Data type is built on pointer concepts to receive the specification of database columns into PL/SQL for the variables. Attribute Data type is work on dynamic memory allocation, which improve the purpose of program with in usage of memory.

%type attribute: %type attribute provides the data type of a variables or database column. Attribute Data type isused to declare the scalar PL/SQL variables. Its syntax is Tablename.columnname%type;

Example:

Declare
    A table1.emp_no%type;
    B table1.emp_name%type;
    C table1.emp_sal%type;
    D table1.Dept_no%type;
Begin
Select emp_name, emp_sal, dept_no, into B, C, D from table1 where emp_no=&A;
    Dbms_output.put_line(B);
    Dbms_output.put_line(C);
    Dbms_output.put_line(D);
End;

% rowtype attribute:

Its syntax is Variablename Tablename %rowtype;

Example:

Declare
    A table1%rowtype;
Begin
Select *into A from Table1 where empno=&empno;
    Dbms_output.put_line(A.emp_name);
    Dbms_output.put_line(A.emp_sal);
    Dbms_output.put_line(A.dept_no);
End;

Embedded SQL

Embedded SQL is a technique of joining the computing power of a programming language and the database management capabilities of SQL. Embedded SQL statements are SQL statements are written inline with the program source code of the host language. Embedded SQL provides environments to develop application programs. The main idea behind embedded SQL is to allow statement in a program written in high level programing language like C, C++.

Example of Embedded SQL

Declare
   A number;
   B number;
   C number;
   D varchar2(10);
Begin
Select emp_name, dept_name, emp_sal into B, C, D from table_emp where emp_no=&A;
   Dbms_output.put_line(B);
   Dbms_output.put_line(C);
   Dbms_output.put_line(D);
End;

Control Statements

If we need to check Some relation or condition between two or more than two variable or expressions than we use conditional statements. Every Programing languages supports this conditional statements, these are.

Simple if

If-then-else

Nested if

Switch case

1: Simple if

Syntax:

If (Condition) then
Statement1;
Statement2; End if;

In PL/SQL every if condition should end with END IF Statement.

2: If-then-else

Syntax:

If (Condition) then
Statement1;
Else
Statement2;
End if;

3: Nested if

Syntax:

If (Condition) then
If (Condition) then
If (Condition) then
Statement1;
Else
Statement2;
End if;
Else Statement3; End if; Else Statement4; End if;

4: Switch case

Blocks

A PL/SQL compilation and run time system is an engine that compiles and executes PL/SQL blocks and programs. That engine installed in an Oracle server or in application development tool. The Pl/SQL engine accepts as input any valid blocks or program. The PL/SQL engine processing an anonymous block, and PL/SQL engine executes procedural but sends SQL statements to the SQL engine in the Oracle Database.

Block: Block is grouped code or set of statement, the blocks are two types.

1: Anonymous block or Un Named PL/SQL Blocks

2: Stored sub program or Named PL/SQL Blocks

Monday, 28 September 2015

Architecture

Architecture of PL/SQL

What is PL/SQL

PL/SQL stands for procedural Language extension of SQL. PL/SQL is the combination of the SQL along with the some procedural features of programing language. The PL/SQL is the developed by the ORACLE Corporation for improving the ability of the SQL.

Main Features of the PL/SQL

1: It Combine the Data manipulating power of SQL with the power of procedural Language.
2: We can define, variable, Procedures, and functions in Pl/SQL.
3: We can break complex problems into easily understandable procedural code.
4: Code can be reused for multiple applications.

There are many advantage of the PL/SQL

Block Structures
Procedural Language Capability
Better Performance
Error handling
Portable and Transaction Processing Language

Subquery

A Subquery is a query inside a query. In Oracle, you can make sub queries inside your main statements. The Outside query is called as main query and inner query is called as subquery.

These subqueries can exist in the WHERE clause, the From Clause and the SELECT clause, A subquery is basically a select statement which is used instead of another statement.

1: WHERE clause: Most frequently subquery will be start with the WHERE clause. These subqueries are also called Nested subqueries. There are 255 sub-queries in oracle.

2: FORM clause: Subqueries are also found in the FORM clause. These are called inline views.

3: SELECT clause:

Index

An index is a performance-tuning method of allowing faster retrieval of records. Indexes are optional structures associated with tables and clusters that allow SQL statements to execute more quickly against a table.
An index creates an entry for each value that appears in the indexed columns. By default, by default, Oracle creates B-tree indexes.

There are many types of indexes in Oracle all designed for different circumstances:

Sequence

A Sequence is a database object that generates unique numbers; mostly Sequence is used for the primary key values. A Sequence is an object in oracle database that used to generates the number Sequence and this number Sequence useful when you need to create a unique number which act as primary key.

The Sequence of number can be generates in either ascending or descending order.

Syntax:

Create sequence<sequence_name> increment by <integer> start with<integer>maxvalue <integer> minvalue<integer>cache<integer>;

CACHE: Cache the specified number of sequence values into buffer in the SGA. This speed access, but it lost when database shutdown. The default values is 20 and we give maximum value=maxvalue - minvalue.

Example:

Create sequence seq_1 minvalue1 maxvalue 99 start with 1 increment by 1 cache 20;

Create sequence seq_2 increment by 1 start with 1 maxvalue 1000 minvalue 1 cache 20;

Synonyms

A Synonym is a substitute name for an object in database. A Synonym is an alias name for object such as table.

Friday, 25 September 2015

View

A View is object consisting of stored query, it doesn’t contain data. Views are logical tables of data extracted from existing tables. A view can be thought of as a stored query or virtual table; we can views in most places where a table can be used.

A View is a method of organizing table data a specified need and View are baesd on SELECT statements, which derive their data real tables.

Use of View in oracle

1: It is used for hiding sensitive columns.

2: It is used for hiding complex queries which is involving multiple tables.

3: Views can be created with check option and prevents the updating of other rows and columns.

4: Views can be offer an extra level of table security by limiting to a predetermined set of rows and/or columns of a table.

5: Views isolate applications from changes in definitions of base tables.

6: View provides data in a different side than of a base table by renaming columns without affecting the base table.

Syntax:

Create or replace view view_name as sql_query;

Example

Create or replace view view_1 as select e_name, e_dept from table_1;

Type of Views

1: Simple Views

2: Complex Views

3: Read-only Views

4: Inline Views

5: Force Views

What is a Materialized View?

It is a database object that stores the results of a query, and it can be stored in the same database as its base table or different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

Materialized view give the indirect access to table data by storing the results of a query in a distinct schema object. Not like as ordinary view, which is does not take up any storage space or contain any data.

The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. In updatable materialized view you can insert, update, and delete the data.

Features of Materialized View

1: In can be partitioned and indexed

2: Can be queried directly

3: Can have DML applied against it

4: Several refresh options are available

5: Best in read-intensive environments

Table Partitioning

Dividing the large table into different smaller part is known as the table partitioning and partitioned table can stored in the different table.

There are many advantages of Table Partitioning.

Increases the performance of servers and we can retrieve data from existing table easily.
Backup/Recovery operation is easy for DBA.

Note: We can divide the table into different part only with the during the creation of the table. Once table can create it is not possible to dividing the table in to different part. It means that unpartition the table cannot be partition.

There are various type of partitions technique supported by oracle.

1: Range Partition

2: Hash Partition

3: List Partition

4: Composite Partition

1: Range Partition: In this type of partition we can divide the table based on some range, generaly we can divide into numeric part.

Syntax

Create table table_name(colum1 datatype(size), colum2 datatype(size), -------- column datatype(size) ) partition by range(column_name) (partition partition_name values less than (value) ----- partition partition_name values less than (value));

Example:

Create table table1(emp_id number(5), emp_name varchar2(20), emp_sal number(6)) partition by range (sal)(partition p1 values less than (5000), partition p2 values less than (10000));

Syntax to retrieve the data from specific partition

Select * from table_name partition (partition_name);
Select * from table1 partition (p1);
Select * from table1 partition (p2);

2: Hash Partition:Hash partitioning is a partitioning method where a hash key is used to distribute rows consistently across the different partitions of table. This is usually used where ranges aren't appropriate.
The hash Partitioning is a best choice than range partition when.
You do not know beforehand how much data will map into a given range.
Sizes of range partition would differ quite substantially.

Example:

create table table1 ( emp_id number(4), emp_name varchar2(30),emp_sal number(6)) partition by hash(emp_id) ( partition p1 tablespace emp1, partition p2 tablespace emp2, partition p3 tablespace emp3);

3: List Partition: List partitioning is a partitioning method where we specify a list of separate values for the partitioning key in the description for each partition.

Example:

create table table1(emp_id number(3), emp_name varchar2(15), join_month varchar2(10)) partition by list(join_month) (partition p1 values(‘jan’,’fab’,’march’,’apr’), partition p1 values(‘may’,’jun’,’jul’,’aug’), partition p1 values(‘sep’,’oct’,’nov’,’dec’);

Cross join

A cross join is a Cartesian product in database.

Syntax and Example of Cross Join

We have Two given table name table1 and table2 and how the cross join.

Table1

Table2

Cross Join of These tables

select * from table1,table2; (non ansi type)
select * from table1 cross join table2; (ANSI type)

Output of non ansi type Cross Join

Output of ansi type Cross Join

Self Join

If any table join to itself then it is called self join.