Introduction
Developers have struggled with the power and limitations of SQL. SQL is language oriented to manipulate data set, but enterprise applications always need to implement business logic that goes beyond the reach of SQL. Thus relational database systems such as Oracle and SQL Server have extended SQL with their own SQL-extension languages: such as T-SQL in SQL Server and PL/SQL in Oracle. This article is intended to illustrate the main constructs in these database management systems.
Microsoft SQL Server T-SQL
Variable is a container to hold values. It may be declared at any place inside your code and it's linked to a scope (ad hoc block of codes, functions, stored procedures) using the following syntax (see Listing 1). You must assign a name and a data type. All the variable names must be preceded by the symbol @.
declare @variable_name datetime
Listing 1.
You can declare more than one variable in a statement separating the variables by comma. Variable can hold the NULL value, although there is no way to say that the variable cannot hold NULL values. By default, when a variable is declared, it will have an initial value of NULL.
The SET or SELECT statement can be used to provide a value to the variable, and the variable can be used anywhere in the code by referencing its name (see Listing 2).
declare @Today datetime;
set @Today=getdate();
declare @Price money;
select @Price = ListPrice
from Production.Product
where ProductID=810;
print 'The price is '+ cast(@Price as varchar(20))+' and today is '+cast(@Today as varchar(20));
Listing 2.
As in other structured programming language, in T-SQL we have control statements such as: conditional control and sequential control.
The IF...ELSE statement allows conditional execution. The statement evaluates the Boolean expression and if it's true, then it executes the sql_statement1 otherwise the sql_statement2. The syntax is shown in the following syntax (Listing 3).
if condition {sql_statement1} else {sql_statement2}
Listing 3.
Let's see an example (see Listing 4).
declare @Price money;
select @Price = ListPrice
from Production.Product
where ProductID=810;
if @Price > 530.00
begin
update Production.Product
set ListPrice=@Price+10
where ProductID=810;
end
else
begin
update Production.Product
set ListPrice=@Price+5
where ProductID=810;
end;
Listing 4.
If you need to use more than one IF...ELSE statements, you must include another level (see Listing 5).
declare @Price money;
select @Price = ListPrice
from Production.Product
where ProductID=810;
if @Price > 530.00
begin
update Production.Product
set ListPrice=@Price+10
where ProductID=810;
end
else
if @Price > 400.00 and @Price < 529.00
begin
update Production.Product
set ListPrice=@Price+5
where ProductID=810;
end;
Listing 5.
The CASE statement is used to return a value based on the value of an expression, thus it's mainly used to replace a column value with a different value. According to SQL Server Online Books, the CASE statement has two forms.
The syntax of the simplest form is shown below (see Listing 6).
case input_expression
when when_expression then result_expression
[...n]
[
else alternative_result
]
end
Listing 6.
Let's see an example (see Listing 7).
select DepartmentID, [Name], GroupName,
case GroupName when 'Research and Development' then 'Room 1'
when 'Sales and Marketing' then 'Room 2'
else 'Room 3' end
from HumanResources.Department;
Listing 7.
The other form is the searched CASE which evaluates a set of Boolean expressions to determine the result. The syntax is shown in Listing 8.
case
when Boolean_expression then result_expression
[...n]
[else alternative_result_expression]
end
Listing 8.
Let's illustrate this syntax with an example which transforms the code in the Listing 7 (see Listing 9).
select DepartmentID, [Name], GroupName,
case when GroupName = 'Research and Development' then 'Room 1'
when GroupName = 'Sales and Marketing' then 'Room 2'
else 'Room 3' end
from HumanResources.Department;
Listing 9.
There are other control of flow mechanisms such as RETURN, WHILE and WAITFOR.
RETURN statement is used to exit the current scope of a program block (Listing 10).
create procedure spDecrease_ExpensiveProducts
as
begin
if not exists
(select * from Production.Product where ListPrice > 10000 )
begin
return 0;
end;
update Production.Product
set ListPrice=ListPrice-500;
return 1;
end;
declare @ErrorCode int;
exec @ErrorCode = spDecrease_ExpensiveProducts;
Listing 10.
Another useful statement is the WHILE which allows you to repeat a specific operation while a condition remains true. The syntax is shown in Listing 11.
while Boolean_expression
{sql_statement | statement_block}
break
{sql_statement | statement_block}
Continue
{sql_statement | statement_block}
Listing 11.
WHILE statement will execute the sql_statement or statement_block while the condition Boolean_expression is true. The BREAK clause will exit the innermost loop, and the CONTINUE clause will cause the loop to restart (see Listing 12).
declare @nCursor int;
set @nCursor = 10;
while @nCursor > 0
begin
print '@nCursor is '+ cast(@nCursor as varchar(20));
set @nCursor = @nCursor - 1;
end;
Listing 12.
The WAITFOR statement delays the execution of T-SQL code for a specific length of time. The syntax is shown below (see Listing 13).
waitfor
{
delay 'time_to_pass' | time 'time_to_execute' | (receive_statement)[, timeout timeout]
}
Listing 13.
Let's see an example (see Listing 14).
waitfor delay '00:00:10'
begin
select *
from Production.Product;
end;
Listing 14.
Oracle PL/SQL
PL/SQL is a procedural language. The smallest group of logic code is named block which provides an execution and variable scope as well as exception handling. There are two types of blocks: anonymous and named blocks.
A block has up to four different sections: Block Header, Declaration Section, Execution Section, Exception Section (see Figure 1).
![sql1.gif]()
Figure 1.
The Block Header is used only for named blocks and contains the type of unit, the name of the unit and any parameter used by the unit. The main syntax is program_type name ([parameter_name IN/OUT/IN OUT parameter_type specs],...) were program_type can be either function, procedure, or package. A parameter is used to pass information to the unit and get information back from the unit. Each parameter has a data type assigned and a direction specification such as IN to indicate passing objects to the unit, OUT to get objects from the unit and IN OUT in both directions. You can declare a not null, null value and default value.
The Declaration Section allows declaring variables which are referenced in the execution and exception blocks. The name of the variable is unique identifier and the underlying data type is standard Oracle data type. The constant keyword allows identifying a variable that doesn't change during the execution of the program.
The Execution Section is where the business logic is specified. This section begins with the keyword begin and ends with the keyword end followed by a semi-colon (;). This section is the only mandatory section in PL/SQL.
The Exception Section is the final section of the block. It handles exceptions during the execution of the business logic. The exception handling syntax is as follows (see Listing 15):
EXCEPTION
WHEN exception_name
THEN
Error handling code
[WHEN OTHERS
Generic error handling code]
Listing 15.
The other type of block is anonymous block and its syntax is as follows (see Listing 16):
DECLARE
BEGIN
[EXCEPTION]
END;
Listing 16.
Anonymous blocks can be used in various environments where the PL/SQL programs need to be executed directly or enclosed in some program such as triggers and command or script files.
A variable is content holder and it must be declared on the Declare Section. Whenever you like to declare a variable, you must assign a name and a data type. The keyword (:=) and the SELECT INTO statement can be used to provide a value to a variable (see Listing 17).
declare
v_my_name varchar(20);
v_ename varchar2(10);
v_sal number(7,2);
v_deptno number (2);
begin
v_my_name := 'John Charles';
select ename, sal, deptno
into v_ename, v_sal, v_deptno
from emp
where empno=7499;
dbms_output.put_line('v_ename is '||v_ename||' v_sal is '||v_sal||' and v_deptno is '||v_deptno);
end;
Listing 17.
In PL/SQL, you can also declare constants (see Listing 18).
declare
c_My_Name constant varchar(20) default 'John Charles';
begin
dbms_output.put_line('The constant value is '||c_My_Name);
end;
Listing 18.
Unlike T-SQL, in PL/SQL you can also specify that the variable must be NOT NULL. If you run the code in Listing 19, then an exception is raised.
declare
v_Test_Not_Null int not null default 10;
begin
v_Test_Not_Null := null;
end;
Listing 19.
Let's talk about conditional and sequential control. Almost every piece of code you write uses this kind of sentence.
IF...THEN...ELSE statement is the basic construct in PL/SQL allows designing conditional logic. There are three forms of this statement (see Table 1).
| IF condition THEN business logic END IF; |
The condition determines whether the business logic is executed or not. |
| IF condition THEN primary business logic ELSE alternative business logic END IF; |
If the condition is true, then the primary business logic is executed otherwise the alternative business logic. |
| IF condition THEN primary business logic ELSIF other condition THEN secondary business logic ELSE alternative business logic END IF; |
If the condition is true, then the primary business logic is executed, else if the other condition is true then the secondary business logic is executed (and so on), other the alternative business logic is executed. |
Table 1.
It's remarkable to say that the condition might evaluate to TRUE, FALSE, or NULL. Only when the condition is TRUE the underlying business logic is executed.
Let's illustrate the concepts by implementing a requirement associated to complete bonus logic (see Listing 20).
Declare
rSal number(7,2);
begin
select sal into rSal
from emp
where empno=7783;
if rSal between 1000 and 2000
then
rSal := rSal+500;
elsif rSal between 100 and 999
then
rSal := rSal+200;
else
rSal := rSal+50;
end if;
update emp set sal=rSal
where empno=7783;
commit;
end;
Listing 20.
The CASE statement allows you to choose which several sequences of PL/SQL is executed based on the results of the expressions. The syntax is shown in Listing 21.
case selector
when condition1 then business logic 1
when condition2 then business logic 2
else alternative business logic
end case;
Listing 21.
The selector is a variable to determine which when to choose. The conditions determine the value of the variable to determine the when to choose. Let's see an example (see Listing 22).
Declare
v_nOrigValue int;
v_nTransValue int;
begin
v_nOrigValue := 100;
case v_nOrigValue
when 100 then
v_nOrigValue := 100;
v_nTransValue := v_nOrigValue-10;
when 1000 then
v_nOrigValue := 1000;
v_nTransValue := v_nOrigValue+10;
end case;
dbms_output.put_line('Original value is '||v_nOrigValue||' Tranformed value is '||v_nTransValue);
end;
Listing 22.
Sometimes the selector may be skipped and it's equivalent to a CASE TRUE clause. This type of CASE is named SEARCHED CASE.
Let's transform the former listing in order to illustrate the SEARCHED CASE syntax using an example (see Listing 23).
declare
v_nOrigValue int;
v_nTransValue int;
begin
v_nOrigValue := 101;
case
when v_nOrigValue between 1 and 100 then
v_nTransValue := v_nOrigValue-10;
v_nOrigValue := 100;
when v_nOrigValue between 101 and 1000 then
v_nTransValue := v_nOrigValue+10;
v_nOrigValue := 1000;
end case;
dbms_output.put_line('Original value is '||v_nOrigValue||' Tranformed value is '||v_nTransValue);
end;
Listing 23.
There is another form of CASE statement: the CASE expression. This type of CASE is similar to the SQL Server CASE. It's similar to normal CASE statement, except that it's evaluated as single expression within another expression. This expression returns the value takes the place of the expression, thus we don't need to write the semicolon after each when (see Listing 24).
declare
v_vcState varchar2(50);
v_nStateCode int;
begin
v_nStateCode := 2;
v_vcState :=
case v_nStateCode
when 1 then 'CA'
when 2 then 'MD'
when 3 then 'NY'
else 'Other'
end;
dbms_output.put_line('The state code is '||v_nStateCode||' and its value is '||v_vcState);
end;
Listing 24.
You can also use CASE expressions inside SQL statements (see Listing 25).
select ename, case when ename='JONES' then 'This is Jones' else 'This is not JONES' end
from emp;
Listing 25.
In PL/SQL, we have three alternatives to execute logic repeatedly: simple or infinite loop, FOR loop, and WHILE loop. The loop consists of LOOP and END LOOP keywords and the body which must contain at least one statement. You can terminate the LOOP using the EXIT keyword, although I recommend not to use this approach. Let's see an example (see Listing 26).
declare
v_nCounter int;
begin
v_nCounter := 0;
loop
dbms_output.put_line('The counter value is '||v_nCounter);
dbms_lock.sleep(5);
if v_nCounter = 6
then
exit;
end if;
v_nCounter := v_nCounter+1;
end loop;
end;
Listing 26.
The WHILE loop is a conditional loop that continues executing as long as the Boolean condition is true. The syntax is shown below (see Listing 27).
while condition
loop
executable statement(s)
end loop;
Listing 27.
Let's see an example below (see Listing 28).
declare
v_nCounter int;
begin
v_nCounter := 0;
while v_nCounter<6
loop
v_nCounter := v_nCounter + 1;
dbms_output.put_line('The counter value is '||v_nCounter);
end loop;
end;
Listing 28.
In PL/SQL we have a numeric FOR loop which can used to for numeric loop and cursor loops. The numeric FOR loop is like the traditional FOR in C, Java and C#.
The syntax is shown below (see Listing 29).
for loop index in [reverse] lower_limit..upper_limit
loop
executable statement(s)
end loop;
Listing 29.
Let's see an example (see Listing 30).
begin
for v_nCounter in 1..5
loop
dbms_output.put_line('The counter value is '||v_nCounter);
end loop;
end;
Listing 30.
The Cursor FOR loop is a loop that is associated with an explicit cursor or a select statement incorporated directly within the loop boundary. I recommend using SQL statements whenever possible to implement your business logic and when you don't have any other option then you should use cursors to fetch and process every row in a result set. Using cursor to work directly with result sets is very expensive.
Let's see an example of Cursor FOR loop (see Listing 31).
Declare
cursor v_cur_emp is
select empno, ename, job from emp;
begin
for emp_row in v_cur_emp
loop
dbms_output.put_line('The emp number '||emp_row.empno||' is '||emp_row.ename||' and his job is '||emp_row.job);
end loop;
end;
Listing 32.
Conclusion
In this article, I explained the main constructs of T-SQL and PL/SQL which allow extending the basic SQL language in order to implement your business logic effectively inside your database management systems. After you read this article, you're able to apply these concepts and techniques to your business scenario.