Database Stored Procedures and SQL/PSM

Database Stored Procedures and SQL/PSM

This section introduces two additional topics related to database programming. In Section 13.4.1, we discuss the concept of stored procedures, which are program modules that are stored by the DBMS at the database server. Then in Section 13.4.2 we discuss the extensions to SQL that are specified in the standard to include general-purpose programming constructs in SQL. These extensions are known as SQL/PSM (SQL/Persistent Stored Modules) and can be used to write stored procedures. SQL/PSM also serves as an example of a database programming language that extends a database model and language—namely, SQL—with some programming constructs, such as conditional statements and loops.

1. Database Stored Procedures and Functions

In our presentation of database programming techniques so far, there was an implicit assumption that the database application program was running on a client machine, or more likely at the application server computer in the middle-tier of a three-tier client-server architecture (see Section 2.5.4 and Figure 2.7). In either case, the machine where the program is executing is different from the machine on which the database server—and the main part of the DBMS software package—is located. Although this is suitable for many applications, it is sometimes useful to create data-base program modules—procedures or functions—that are stored and executed by the DBMS at the database server. These are historically known as database stored procedures, although they can be functions or procedures. The term used in the SQL standard for stored procedures is persistent stored modules because these programs are stored persistently by the DBMS, similarly to the persistent data stored by the DBMS.

Stored procedures are useful in the following circumstances:

        If a database program is needed by several applications, it can be stored at the server and invoked by any of the application programs. This reduces duplication of effort and improves software modularity.

        Executing a program at the server can reduce data transfer and communication cost between the client and server in certain situations.

        These procedures can enhance the modeling power provided by views by allowing more complex types of derived data to be made available to the database users. Additionally, they can be used to check for complex constraints that are beyond the specification power of assertions and triggers.

In general, many commercial DBMSs allow stored procedures and functions to be written in a general-purpose programming language. Alternatively, a stored procedure can be made of simple SQL commands such as retrievals and updates. The general form of declaring stored procedures is as follows:

CREATE PROCEDURE <procedure name> (<parameters>) <local declarations>

<procedure body> ;

The parameters and local declarations are optional, and are specified only if needed. For declaring a function, a return type is necessary, so the declaration form is

CREATE FUNCTION <function name> (<parameters>) RETURNS <return type>

<local declarations> <function body> ;

If the procedure (or function) is written in a general-purpose programming lan-guage, it is typical to specify the language as well as a file name where the program code is stored. For example, the following format can be used:

CREATE PROCEDURE <procedure name> (<parameters>)

LANGUAGE <programming language name>

EXTERNAL NAME <file path name> ;

In general, each parameter should have a parameter type that is one of the SQL data types. Each parameter should also have a parameter mode, which is one of IN, OUT, or INOUT. These correspond to parameters whose values are input only, output (returned) only, or both input and output, respectively.

Because the procedures and functions are stored persistently by the DBMS, it should be possible to call them from the various SQL interfaces and programming techniques. The CALL statement in the SQL standard can be used to invoke a stored procedure—either from an interactive interface or from embedded SQL or SQLJ. The format of the statement is as follows:

CALL <procedure or function name> (<argument list>) ;

If this statement is called from JDBC, it should be assigned to a statement object of type CallableStatement (see Section 13.3.2).

2. SQL/PSM: Extending SQL for Specifying Persistent Stored Modules

SQL/PSM is the part of the SQL standard that specifies how to write persistent stored modules. It includes the statements to create functions and procedures that we described in the previous section. It also includes additional programming con-structs to enhance the power of SQL for the purpose of writing the code (or body) of stored procedures and functions.

In this section, we discuss the SQL/PSM constructs for conditional (branching) statements and for looping statements. These will give a flavor of the type of con-structs that SQL/PSM has incorporated; then we give an example to illustrate how these constructs can be used.

The conditional branching statement in SQL/PSM has the following form:

IF <condition> THEN <statement list>

ELSEIF <condition> THEN <statement list>

...

ELSEIF <condition> THEN <statement list>

ELSE <statement list>

END IF ;

Consider the example in Figure 13.14, which illustrates how the conditional branch structure can be used in an SQL/PSM function. The function returns a string value (line 1) describing the size of a department within a company based on the number of employees. There is one IN integer parameter, deptno, which gives a department number. A local variable NoOfEmps is declared in line 2. The query in lines 3 and 4 returns the number of employees in the department, and the conditional branch in lines 5 to 8 then returns one of the values {‘HUGE’, ‘LARGE’, ‘MEDIUM’, ‘SMALL’} based on the number of employees.

SQL/PSM has several constructs for looping. There are standard while and repeat looping structures, which have the following forms:

Figure 13.14 Declaring a function in SQL/PSM.

//Function PSM1:

     CREATE FUNCTION Dept_size(IN deptno INTEGER)

     RETURNS VARCHAR [7]

     DECLARE No_of_emps INTEGER ;

     SELECT COUNT(*) INTO No_of_emps

     FROM EMPLOYEE WHERE Dno = deptno ;

     IF No_of_emps > 100 THEN RETURN "HUGE"

     ELSEIF No_of_emps > 25 THEN RETURN "LARGE"

     ELSEIF No_of_emps > 10 THEN RETURN "MEDIUM"

     ELSE RETURN "SMALL"

END IF ;

WHILE <condition> DO <statement list>

END WHILE ;

REPEAT

<statement list> UNTIL <condition>

END REPEAT ;

There is also a cursor-based looping structure. The statement list in such a loop is executed once for each tuple in the query result. This has the following form:

FOR <loop name> AS <cursor name> CURSOR FOR <query> DO <statement list>

END FOR ;

Loops can have names, and there is a LEAVE <loop name> statement to break a loop when a condition is satisfied. SQL/PSM has many other features, but they are out-side the scope of our presentation.