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