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.

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:

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 ;