I intend this article to act as a PL/SQL primer for the database community. This is really important because as a developer I have seen different scenarios of database programming with PL/SQL and I feel that sharing knowledge gets the best out of you. I have included sample code to explain the capabilities of PL/SQL as a language. I have compiled this code with the help of Daffodil DB v3.1. That’s enough about me; now let’s get straight to the point.
PL/SQL: The capabilities revealed
PL/SQL is a database-orientated programming language that extends SQL with procedural capability. The capabilities of PL/SQL prove that PL/SQL is a simple and powerful programming language. SQL commands can be used with in PL/SQL to interact with the database.
Need for PL/SQL
SQL is referred as a declarative language because SQL statements are defined in term of constraints we wish to fix on the result of a query. This contrasts with the so called procedural languages where a program specifies a series of operations to be processed sequentially to achieve the desired result. PL/SQL adds selective (i.e. if...then...else...) and iterative (i.e. loops) constructs to SQL.
PL/SQL is most useful to write triggers and stored procedures. Stored procedures are units of procedural code stored in a compiled form within the database.
PL/SQL is the superset of the Structured Query Language (SQL). PL/SQL can be used to gain the benefits like,
•Codify your business logic through the creation of stored procedures.
•Secure your business logic.
•Improve application performance.
•Save time, efforts and money by reusing the existing procedures.
Now let us discuss the implications of all the listed points at length to find out the way PL/SQL can bring solutions to all your enterprise needs.
Codify your business logic through the creation of stored procedures.
Sophisticated business rules and application logic can be stored as procedures within database. Stored procedures are groups of SQL / PL-SQL statements which enable users to move code that enforces business logic from your application to the database. As a result the processing of complex business rules may be performed within the database by the server. In Client server or three tier applications shifting complex processing from the application to database can bring improvements in overall performance.
Secure your business logic.
The developers shall use a RDBMS which supports PL/SQL and not language migration. To follow the process of language migration, Some RDBMS suggest users to write business logic in any language like Java or C++ in a predefined manner and that class is used for developing database routines. Using this approach can result in damage or exposure of the business logic. Moreover language migration is a temporary arrangement to make available a feature in the application but PL/SQL is a standard. If users use the products using the approach of language migration they can end up in a situation where the other existing products might become incompatible with them. This situation is known as vendor locking, where the user has no option but to use the proprietary product. It is highly recommended that users shall use standardized and universally compatible products.
PL/SQL stored procedures are stored at the database level and hence result in faster execution of business logic. This approach can decrease the execution time of the application. The procedural code is stored within the PL/SQL is fairly static. Thus the second time a procedure is executed; it may be able to take advantage of the parsing that was previously performed, improving the performance of the procedures execution.
Save time, efforts and money by reusing the existing procedures.
Reuse of existing PL/SQL procedures can reduce the time to build new applications, because the application does not have to be reinvented (and rewritten and debugged). Reuse of procedures can make application code easier to understand and debug, because they have already been coded and debugged. Reuse can also reduce maintenance costs, because components can be improved and the benefits recouped by all applications that use them.
How to use PL/SQL with Daffodil DB
I will leave you with a demonstration of PL/SQL capabilities. This sample code demonstrates the use of PL/SQL features listed above. If you wish to get an insight of PL/SQL capabilities, you can get in touch with me at Daffodil DB technical support forums at
Create table test_1 (a int , b int ) // Create a table with name test_1
insert into test_1 values(20,21) // inserting data in existing table
insert into test_1 values(1,2)
insert into test_1 values(2,3)
Create procedure testProcedure() specific stestProcedure //Procedure Query
declare i,a,b int ; // declaration of variables
set i = 0 ; // assign value in variable
for a as select a from test_1 do // For loop with implicit cursor
set i = i 1 ;
label1 : // label statement
for b as select b from test_1 do
set i = i 1 ;
if i = 8 then // if statement
insert into test_1 values(100,100) ;
leave label1; // Break statement
elseif i = 5 then
insert into test_1 values(1,1) ;
iterate label1; // Continue statement
elseif i between 6 and 7 then
update test_1 set a=2 where b=1 ;
end if ;
end for ;