Persistent Stored Modules: The capabilities revealed

PSM (Persistent Stored Modules) is a database-orientated programming language that extends SQL with procedural capabilities. Various database developers have given it a different name, such as Oracle's PL/SQL. The capabilities of PSM prove that it is a simple and powerful programming language. SQL commands can be used with in PSM to interact with the database. PSM is based on ISO standards for database programming.

Need for PSM

SQL is referred as a declarative language because SQL statements are defined in term of constraints that we want to fix on the result of a query. This is different with the procedural languages where a program specifies a series of operations to be processed sequentially to acquire the desired result. PSM adds selective (i.e. if...then...else...) and iterative (i.e. loops) constructs to SQL. PSM is most useful for writing triggers and stored procedures. Stored procedures are units of procedural code stored in a compiled form within the database.
Advantages of PSM PSM is the superset of the Structured Query Language (SQL). PSM can be used to gain the benefits, such as:

• Codify your business logic
• 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 PSM can bring solutions to all your enterprise needs. How to use PSM with Daffodil DB? We are providing you a demonstration of PSM capabilities. This sample code demonstrates the use of PSM features listed above. If you wish to get an insight of PSM capabilities, you can get in touch with me at Daffodil DB technical support forums 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 begin 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 end for ; end ;