Programming Basics with PL/SQL
PL/SQL features ---
- PL/SQL is an extension of SQL
- It is an application development language containing procedural statements and commands along with SQL commands
- It bridges the gap between database technology and procedural programming languages
- It allows you to process data using flow control statements like iterative loops and conditional branching
- Uses procedural techniques of control, looping and branching
Supports SQL i.e. cursor operations, exceptions, functions and transactional commands ,Variables and constants, robust error handling and functions
- Adds functionality to non-procedural tools such as SQL*Forms
- Developers using SQL*Forms can enter an entire PL/SQL block using a single trigger
Structure of PL/SQL
Standard PL/SQL code segment is called a Block
A block consists of three parts or sections
Declaration Part
Executable Part
Exception Handling Part
DECLARE
Declarations
BEGIN
Executable Statements
EXCEPTION
Exception Handlers
END;
Sections of a PL/SQL Block
Declaration Part : optional part where variables are defined
Executable Part : mandatory part which consists of executable statements
Exception Handling Part : optional part which consists of code for handling errors (runtime)
PL/SQL Files -
PL/SQL programs can be written in any editor and saved as files with .sql extension
Can also use “ED” command in SQL*Plus to create a PL/SQL program file
Use the “@ ” command to execute a PL/SQL program file
Variables -
Used to store results of a query for later processing, or to calculate values to be inserted into database tables
Can be used anywhere in an expression, either in SQL or PL/SQL statements
Must be declared before referencing it in other statements, including other declarative statements
Are declared by specifying the name along with the datatype
Can be declared to be of any datatype native to Oracle
Examples
oldfare NUMBER(5);
m_name VARCHAR(15);
(Note – Set Serveroutput On has to be given when a session starts for displaying the output statements_)
declare
x number;
begin
x := 67;
dbms_output.put_line(x);
dbms_output.put_line('The value of x is '|| x);
end;
Declaring variable in declare block.
Assigning value in in begin block using := .
Output statement is dbms_output.put_line
Concatenation operator is ||
Command terminator is ; after end
Declaring and initializing variables together
declare
y number := 100;
begin
dbms_output.put_line('The value of y is '|| y);
end;
-------------------------------------------------------------------------------
Taking value from the user using &
declare
z number;
a varchar2(10);
begin
z := &z;
a := '&a';
dbms_output.put_line('Z is '|| z);
dbms_output.put_line('A is '|| a);
end;
-------------------------------------------------------------------------------
/*Cannot declare or initialize more than one variable simultaneously*/
declare
a number;
b number;
c number;
begin
a := 67; b := 90; c := 87;
dbms_output.put_line(a);
dbms_output.put_line(b);
end;
A constant number has to declared and initialized in the declare block only using
CONSTANT keyword. Value cannot be changed
declare
r CONSTANT number :=100;
begin
/* r := r 100; Not possible*/
dbms_output.put_line(r);
end;
/*Assigning value to variable from a column of a table using select into clause*/
declare
x number;
begin
Select sal Into x from emp
where ename = 'SMITH';
dbms_output.put_line('Salary of Smith is '|| x);
end;
/* Selecting ename,sal from emp
Use of more than one columns value with Into clause*/
declare
n varchar2(50);
s number;
begin
select ename, sal Into n, s
from emp
where ename = 'SMITH';
dbms_output.put_line(n);
dbms_output.put_line(s);
end;
% Type Attribute –
Provides datatype of a variable or column
Useful when declaring a variable that refers to a column in a database
exact datatype of column need not be known
if column definition changes, variable datatype changes accordingly at runtime
Example
oldfare fare.first_fare%TYPE;
newfare oldfare%TYPE;
declare
a emp.ename%type;
b emp.sal%type;
c emp.deptno%type;
/*Using %TYPE attribute for variable data type*/
begin
select ename,sal,deptno
into a,b,c
from emp
where ename = 'KING';
dbms_output.put_line(a ||'-'|| b ||'-' || c);
end;
%RowType Attribute –
Useful when declaring a record variable having same structure as a row in a table or view, or as a row fetched from a cursor Fields in the record have same names and datatypes as the columns in the table/view
Example
emp_rec employee%ROWTYPE;
A specific field can be referenced using
emp_rec.emp_num;
declare
E emp%rowtype;
/*rowtype attribute holds the datatype of the columns of the
entire row*/
begin
select * INTO E
from emp
where ename = 'MARTIN';
dbms_output.put_line(E.sal);
dbms_output.put_line(E.ename);
dbms_output.put_line(e.deptno);
end;
Oracle 9i - Programming basics with PL/SQL
| Author | Body |
Rahul
7/11/2009 7:42 AM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you.
|