Showing posts with label PL/SQL Tutorial. Show all posts
Showing posts with label PL/SQL Tutorial. Show all posts

Wednesday, March 21, 2012

Exception Handling in PL/SQL



In PL/SQL, any kind of errors are treated as exceptions. An exception is defined as a special condition that change the program execution flow.

In PL/SQL there are two types of exceptions:
1)Internal exceptions that occur when a PL/SQL block does not comply with a rule of the server
2)External user-defined exceptions, which are declared in section declarativa block, subroutine or package and which are activated explicitly in the executabila block, PL/SQL.

Structure of Exception Handling.
The General Syntax for coding the exception section

DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;

Example :
The following example illustrates the programmer-defined exceptions. We get the salary of an employee and check it with the job’s salary range. If the salary is below the range, we raise exception BELOW_SALARY_RANGE. If the salary is above the range, we raise exception ABOVE_SALARY_RANGE.

SET SERVEROUTPUT ON SIZE 100000;
DECLARE
-- define exceptions
BELOW_SALARY_RANGE EXCEPTION;
ABOVE_SALARY_RANGE EXCEPTION;
-- salary variables
n_salary employees.salary%TYPE;
n_min_salary employees.salary%TYPE;
n_max_salary employees.salary%TYPE;
-- input employee id
n_emp_id employees.employee_id%TYPE := &emp_id;
BEGIN
SELECT salary,
min_salary,
max_salary
INTO n_salary,
n_min_salary,
n_max_salary
FROM employees
INNER JOIN jobs ON jobs.job_id = employees.job_id
WHERE employee_id = n_emp_id;

IF n_salary < n_min_salary THEN
RAISE BELOW_SALARY_RANGE;
ELSIF n_salary > n_max_salary THEN
RAISE ABOVE_SALARY_RANGE;
END IF;

DBMS_OUTPUT.put_line('Employee ' || n_emp_id ||
' has salary $' || n_salary );

EXCEPTION
WHEN BELOW_SALARY_RANGE THEN
DBMS_OUTPUT.put_line('Employee ' || n_emp_id ||
' has salary below the salary range');
WHEN ABOVE_SALARY_RANGE THEN
DBMS_OUTPUT.put_line('Employee ' || n_emp_id ||
' has salary above the salary range');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found');
END;
/
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

What are PL/SQL Triggers



PL/SQL Triggers

A trigger is a PL/SQL block which will run automatically whenever an event occurs. PL/SQL block may be associated with a table, a view or to a database. OR simply, A trigger is a procedure to be run automatically when it is launched on the table associated with a command to the insert, update, or delete.

The general syntax is :


CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | UPDATE | DELETE}
[OF column_name]
ON table_name
[REFERENCING OLD AS old NEW AS new]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- pl/sql statements
END;
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Parameters-Procedure and Function in PL/SQL



How to pass parameters to Procedures and Functions in PL/SQL ?
In PL/SQL, we can pass parameters to procedures and functions in three ways.

1) IN type parameter: These types of parameters are used to send values to stored procedures.
2) OUT type parameter: These types of parameters are used to get values from stored procedures. This is similar to a return type in functions.
3) IN OUT parameter: These types of parameters are used to send values and get values from stored procedures.

Explanation :

1) IN parameter:
This is similar to passing parameters in programming languages. We can pass values to the stored procedure through these parameters or variables. This type of parameter is a read only parameter. We can assign the value of IN type parameter to a variable or use it in a query, but we cannot change its value inside the procedure.

The General syntax to pass a IN parameter is

CREATE [OR REPLACE] PROCEDURE procedure_name (
param_name1 IN datatype, param_name12 IN datatype ... )

param_name1, param_name2... are unique parameter names.
datatype - defines the datatype of the variable.

IN - is optional, by default it is a IN type parameter.

2) OUT Parameter:
The OUT parameters are used to send the OUTPUT from a procedure or a function. This is a write-only parameter i.e, we cannot pass values to OUT paramters while executing the stored procedure, but we can assign values to OUT parameter inside the stored procedure and the calling program can recieve this output value.

The General syntax to create an OUT parameter is

CREATE [OR REPLACE] PROCEDURE proc2 (param_name OUT datatype)

The parameter should be explicity declared as OUT parameter.

3) IN OUT Parameter:

The IN OUT parameter allows us to pass values into a procedure and get output values from the procedure. This parameter is used if the value of the IN parameter can be changed in the calling program.

By using IN OUT parameter we can pass values into a parameter and return a value to the calling program using the same parameter. But this is possible only if the value passed to the procedure and output value have a same datatype. This parameter is used if the value of the parameter will be changed in the procedure.

The General syntax to create an IN OUT parameter is

CREATE [OR REPLACE] PROCEDURE proc3 (param_name IN OUT datatype)

Example :

Using IN and OUT parameter:

Let’s create a procedure which gets the name of the employee when the employee id is passed.

CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT NUMBER)
IS
BEGIN
SELECT first_name INTO emp_name
FROM emp_tbl WHERE empID = id;
END;
/
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Thursday, March 15, 2012

PL/SQL Functions



What is a Function in PL/SQL?
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.

The General Syntax to create a function is:

CREATE [OR REPLACE] FUNCTION function_name [parameters]
RETURN return_datatype;
IS
Declaration_section
BEGIN
Execution_section
Return return_variable;
EXCEPTION
exception section
Return return_variable;
END;

1)The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc.
2)The execution and exception section both should return a value which is of the datatype defined in the header section.

For example, let’s create a frunction called ''employer_details_func' similar to the one created in stored proc

CREATE OR REPLACE FUNCTION employer_details_func
RETURN VARCHAR(20);
IS
emp_name VARCHAR(20);
BEGIN
SELECT first_name INTO emp_name
FROM emp_tbl WHERE empID = '100';
RETURN emp_name;
END;
/
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Sunday, March 04, 2012

PL/SQL Procedures



PL/SQL Stored Procedures

Stored procedures are called blocks that allow grouping and organization of SQL commands and PL/SQL. Both source code and the executable are stored in the database. By storing it in the database, the code is situated in accessible and centralized location. Because the executable code is located in the database, invoke stored procedures is more efficient.

We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters

A procedure may or may not return any value.

General Syntax to create a procedure is:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;


Example Stored Procedure:

CREATE OR REPLACE PROCEDURE raise_salary
(p_id IN employees.employee_id%Type,
p_percent IN NUMBER)
IS
BEGIN
UPDATE employees
SET salary= salary*(1+p_percent/100)
WHERE employee_id = p_id;
END raise_salary;
Begin
raise_salary(200,10);
End;
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Sunday, February 26, 2012

Cursors in PL/SQL



There are two types of cursors in PL SQL
1) Implicit cursor
2) Explicit cursor

Implicit cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors:

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

The attributes of the cursors

The attributes of the implicit cursors can return information about DML and DDL execution commands such as INSERT, UPDATE, DELETE, SELECT INTO, COMMIT or ROLLBACK.

% FOUND

Until SQL data manipulation is executed, the attribute % FOUND is NULL. So, % FOUND is TRUE if the command type of INSERT, UPDATE, or DELETE affects one or more records from the database or SELECT INTO returns one or more recordings.

% ISOPEN

Oracle closes the cursor automatically after execution of commands. As a result,% ISOPEN becomes FALSE.

% NOTFOUND

% NOTFOUND is the opposite attribute of % FOUND.
% NOTFOUND is TRUE if the INSERT, UPDATE, or DELETE your registration does not affect any of the database or SELECT INTO does not return any registration. Otherwise it is FALSE.

% ROWCOUNT

% ROWCOUNT returns the number of records affected by one of the commands that INSERT, UPDATE, or DELETE, or the number of records affected by the SELECT INTO. % ROWCOUNT is 0 if the command INSERT, UPDATE, or DELETE does not affect any registration, or SELECT INTO does not return any record.



For Example: Consider the PL/SQL Block that uses implicit cursor attributes as shown below:

DECLARE var_rows number(5);
BEGIN
UPDATE employee
SET salary = salary + 1000;
IF SQL%NOTFOUND THEN
dbms_output.put_line('None of the salaries where updated');
ELSIF SQL%FOUND THEN
var_rows := SQL%ROWCOUNT;
dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');
END IF;
END;
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Saturday, February 25, 2012

PL/SQL FOR and WHILE LOOP / Iterative Statements



Introduction to PL/SQL LOOP Statement

PL/SQL LOOP is an iterative control structure that allows you to execute a sequence of statements repeatedly.

    There are three types of PL/SQL loop Statements
  • Simple Loop

  • While Loop

  • For Loop



Type 1) Simple Loop
A Simple Loop is used when a set of statements is to be executed at least once before the loop terminates. An EXIT condition must be specified in the loop, otherwise the loop will get into an infinite number of iterations. When the EXIT condition is satisfied the process exits from the loop.

Syntax :

LOOP
statements;
EXIT;
{or EXIT WHEN condition;}
END LOOP;


Type 2) While Loop
A WHILE LOOP is used when a set of statements has to be executed as long as a condition is true. The condition is evaluated at the beginning of each iteration. The iteration continues until the condition becomes false.

Syntax :

WHILE
LOOP statements;
END LOOP;


3) FOR Loop
A FOR LOOP is used to execute a set of statements for a predetermined number of times. Iteration occurs between the start and end integer values given. The counter is always incremented by 1. The loop exits when the counter reachs the value of the end integer.

Syntax :

FOR counter IN value1..value2
LOOP statements;
END LOOP;


value1 - Start integer value.
value2 - End integer value.
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

PL/SQL IF ELSE statement / Conditional statements



Conditional Statements in PL/SQL

The PL/SQL IF statement allows you to execute a sequence of statements conditionally. The IF statements evaluate a condition. The condition can be anything that evaluates to a logical true or false such as comparison expression or combination of multiple comparison expressions. The programming constructs are similar to how you use in programming languages like Java and C++.


IF THEN ELSE STATEMENT

Type 1)

IF condition
THEN
statement 1;
ELSE
statement 2;
END IF;

Type 2)

IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF


Type 3)

IF condition 1
THEN
statement 1;
statement 2;
ELSIF condtion2 THEN
statement 3;
ELSE
statement 4;
END IF;


Type 4)

IF condition1 THEN
ELSE
IF condition2 THEN
statement1;
END IF;
ELSIF condition3 THEN
statement2;
END IF;
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

PL/SQL Records



What is a PL/SQL Record

A PL/SQL record is a composite data structure that is a group of related data stored in fields. Each field in the Pl/SQL record has its own name and data type.

The General Syntax to define a composite datatype is:

TYPE record_type_name IS RECORD
(first_col_name column_datatype,
second_col_name column_datatype, ...);

record_type_name – it is the name of the composite type you want to define.
first_col_name, second_col_name, etc.,- it is the names the fields/columns within the record.
column_datatype defines the scalar datatype of the fields.

Declaring Table-based Record

To declare a table-based record you use a table name with %ROWTYPE attribute. The fields of the PL/SQL record has the same name and data type corresponding to the column of the table. The following illustrates table-based record declaration:

DECLARE
table_based_record table_name%ROWTYPE;
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Friday, February 24, 2012

PL/SQL Variables and Constants



PL/SQL Variables

Variables can be any SQL data type such as CHAR, DATE, or NUMBER, or a type of date PL/SQL such as BOOLEAN or PLS_INTEGER.

These are placeholders that store the values that can change through the PL/SQL Block.
--------------------------------------------------------------------------------
Naming rules for Variables in PL SQL:
--------------------------------------------------------------------------------
The variable name must be less than 31 characters.

The starting of a variable must be an ASCII letter. It can be either lowercase or uppercase.

A variable name can contain numbers, underscore, and dollar sign characters followed by the first character.

Make them meaningful to understand to make it easier to maintain in the future.
---------------------------------------------------------------------------------

PL/SQL Variable Declaration


The General Syntax to declare a variable is:

variable_name datatype [NOT NULL := value ];

variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable.
value or DEFAULT valueis also an optional specification, where you can initialize a variable.
Each variable declaration is a separate statement and must be terminated by a semicolon.

1. Declaring Variables in PL/SQL

DECLARE
part_no NUMBER (6);
part_name VARCHAR2 (20);
in_stock BOOLEAN;
part_price NUMBER (6.2);
part_desc VARCHAR2 (50);

2. Assigning variables using the := operator

DECLARE
hours_worked NUMBER: = 40;
hourly_salary NUMBER: = 22.50;
bonus NUMBER: = 150;
country VARCHAR2 (128);
counter NUMBER: = 0;
done BOOLEAN;
emp_rec1 employees% ROWTYPE;
emp_rec2 employees% ROWTYPE;
BEGIN
wages: = (hours_worked * hourly_salary) + bonus;
country: = ' Italy ';
country: = UPPER (' Spain ');
done: = (counter > 100);
emp_rec1.: first_name = ' John ';
emp_rec1. last_name: = ' Davis ';
emp_rec1: = emp_rec2;
END;

3. Assigning values to a variable using Select Into

DECLARE
bonus NUMBER (8,2);
emp_id NUMBER (6): = 100;
BEGIN
SELECT salary * 0.10 INTO bonus FROM employees
WHERE employee_id = emp_id;
END;

4. Assigning Value to a Variable as a parameter of a subroutine

DECLARE
new_sal NUMBER (8,2);
emp_id NUMBER (6): = 126;
PROCEDURE adjust_salary (emp_id NUMBER, sal IN OUT NUMBER) IS
emp_job VARCHAR2 (10);
avg_sal NUMBER (8,2);
BEGIN
SELECT job_id INTO emp_job FROM employees WHERE emp_id = employee_id;
SELECT AVG (salary) INTO avg_sal FROM employees WHERE job_id = emp_job;
DBMS_OUTPUT.PUT_LINE (' The average salary for ' emp_job || || ' employees: ' || TO_CHAR (avg_sal));
sal: = (sal + avg_sal)/2; --adjust sal value which is returned
END;
BEGIN
SELECT AVG (salary) INTO new_sal FROM employees;
DBMS_OUTPUT.PUT_LINE (' The average salary for all employees: ' || TO_CHAR (new_sal));
adjust_salary (emp_id, new_sal); --assigns a new value to new_sal
DBMS_OUTPUT.PUT_LINE (' The adjusted salary for employee ' || TO_CHAR (emp_id) || ' is ' || TO_CHAR (new_sal)); — sal has new value
END;

Constant
constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.

The General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
constant_name is the name of the constant i.e. similar to a variable name.
The word CONSTANT is a reserved word and ensures that the value does not change.
VALUE - It is a value which must be assigned to a constant when it is declared. You cannot assign a value later.

For example, to declare salary_increase, you can write code as follows:

DECLARE
Salary_increase CONSTANT number (5) := 20;
Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Block structure and Advantages of PL/SQL



PL/SQL is a language focused on blocks, with procedural and processing characteristics of error handling. These blocks are composed of procedures, functions, and anonymous blocks which are grouped together in logical point of view.

    In block there are three basic parts which are declaration, execution, and exception handling. Only execution part is required and the others are optional.

  • Declaration - all block objects must be declared [optional]

  • Execution – the objects are defined for the data processing

  • Exceptions – here are located the error handling routines [optional]



DECLARE
Variable declaration;
BEGIN
Program Execution;
EXCEPTION
Exception handling;
END;



Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.

Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.

Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.

Advantages of PL/SQL

  • Integration with Oracle Server

  • Supports the basic SQL commands

  • Defining and managing blocks of instructions

  • Management of variables, constants and cursors

  • Allow implementation and use of triggers

  • PL SQL consists of blocks of code, which can be nested within each other.

  • PL SQL engine processes multiple SQL statements simultaneously as a single block

  • It also reduces the network traffic.

  • Detection and management execution errors, exceptions

Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Introduction to PL/SQL

PL/SQL stands for procedural language extension of Structured Query Language [SQL].
PL/SQL is a programming language that provides accessing data from a relational database-oriented objects and enables grouping of a multitude of commands into a single block of data handling.It enable programmers to codify procedures, functions, and anonymous blocks that combine SQL statements

PL/SQL is a combination of Structured Query Language along with the procedural features of programming languages which was developed by Oracle Corporation in the early 1990’s to enhance the capabilities of SQL.

Oracle introduced PL/SQL to extend some limitations of SQL to provide a more comprehensive solution for building mission-critical applications running on Oracle database.

This was small introduction to PL/SQL which was necesary before the actual tutorial begin.

Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:

Complete Tutorial on Oracle PL/SQL

Read more
Free Mobile Recharge any time. Join now! Posted by ROCKY 0 comments
Labels:
Subscribe to: Posts (Atom)