Selectable stored procedures are so-called because they are designed to be executed using a SELECT statement. To readers accustomed to the server programming techniques available to other DBMSs, the concept of a stored procedure that outputs rows directly to the calling application or procedure, without any form of intermediate “temporary” table, will be much less familiar than the executable procedure.
Uses for Selectable Procedures
Selectable procedures can be used to specify virtually any set, but they are especially useful when you need a set that cannot be extracted, or is slow or difficult to extract, in a single DSQL statement.
The selectable stored procedure technique offers enormous flexibility for extracting sets that defeat the logic available through regular SELECT statement specifications. It literally makes it possible to form a reproducible set from any combination of data you store. You can use calculations and transformations across a number of columns or rows in the set to condition what appears in the output. For example, output sets with running totals are difficult or impossible to achieve from a dynamic query but can be generated and managed speedily and efficiently with a selectable stored procedure.
Selectable procedures can come in handy for generating sets of data that are not stored in the database at all. It is a technique for which we all find a use sometime. In the following trivial example, a comma-separated list of strings, each of 20 or fewer characters, is fed in as input. The procedure returns each string to the application as numbered row:CREATE PROCEDURE BREAKAPART(
Performance Gain for Complex Sets
Often, complex queries involving many joins or subqueries are too slow to be satisfactory for interactive applications. Some queries can be slow because of unpropitious indexes on foreign keys. Because of its ability to operate on sets in nested loops, a stored procedure is capable of producing the required sets much more quickly and, especially, to begin returning rows much sooner than the conventional SQL sequences permit.
The technique for extracting and manipulating the data for the output set uses a cursor to read each row in turn from a SELECT statement into a pre-declared set of variables. Often, it may be the output arguments that the column values are read into, but it can be local variables. Inside the loop, the variables are operated on in whatever manner is required: transformed by calculations, if need be, or used as search arguments for nested loops to pick up values from further queries. At the end of the loop, when all output arguments have their final values, a SUSPEND statement causes execution to pause while the set is passed to the row cache. Execution resumes when the next fetch is called.
As we saw in the previous BREAKAPART example, the SUSPEND statement is the element that causes the procedure to deliver a row.
The FOR SELECT ... DO Construct
To retrieve multiple rows in a procedure, we use the FOR SELECT . .. DO construct. The syntax isFOR
The select-expression can be any select query, using joins, unions, views, other select procedures, expressions, function calls, and so on, in any valid combination.
A FOR SELECT differs from a standard SELECT statement in that it requires variables into which to receive the columns and fields specified.
The <compound-statement> can be a single SUSPEND statement or a block of two or more statements. The <compound-statement> can nest other compound statements.
FOR SELECT ... DO is a loop construct that retrieves the row specified in the select-expression and performs the statement or block following DO for each row in turn.
The INTO <variables> clause is required and must come last.
Processing in the Loop
Figure illustrates some typical activities that can be performed inside loops to generate output for a selectable stored procedure.
Operations in a selectable procedure
In the following examples, we will take a look at how combinations of the operations available in PSQL can meet your less ordinary SQL challenges.
A Simple Procedure with Nested SELECTs
The selectable procedure ORG_CHART, which is in the example employee database, takes no input arguments. It uses a FOR ... SELECT loop to line up a set from a self-referencing join on the DEPARTMENT table and pass column values, one row at a time, to a set of variables—some local, some declared as output arguments.CREATE PROCEDURE ORG_CHART
Each time the loop processes a row, it picks up a key value (MNGR_NO) into the local variable mngr_no. If the variable is null, the procedure fabricates values for the output arguments MNGR_NAME and TITLE. If the variable has a value, it is passed as a search argument to a nested query on the EMPLOYEE table, uniquely targeting a row and extracting the name and job code of a department manager. These values are passed to the remaining output arguments.BEGIN
Then all of the outputs for one row are assigned, a SUSPEND statement passes the row to the cache. Execution resumes back at the start of the loop when the next fetch request is made.SUSPEND;
Notice how the nested query neatly bypasses the problem we have in DSQL with subqueries—we can return one and only one value to a subquery. If we want multiple values and left join logic will not work, we have to use multiple subqueries with multiple sets of aliases to extract each value from its own cursor.
Calling a Selectable Procedure
The syntax pattern for calling a selectable procedure is very similar to that for a table or view. The one difference is that a procedure may have input arguments:SELECT <column-list> from name ([argument [, argument ...]])
The procedure name must be specified.
Input argument rules are identical to those for executable procedures—see the earlier section titled “Calling Rules About Input Argument Values.”
The column-list is a comma-separated list of one or more of the output parameters returned by the procedure, or * to select all columns.
The output set can be limited by a search-condition in a WHERE clause and sorted by an ORDER BY clause.
Calling the ORG_CHART Procedure
This procedure has no input parameters, so the SELECT call looks like a simple select on a table, namely
SELECT * FROM ORG_CHART;
Selecting Aggregates from Procedures
In addition to selecting values from a procedure, you can use aggregate functions. For example, to use our procedure to display a count of the number of departments, use the following statement:
SELECT COUNT(DEPARTMENT) FROM ORG_CHART;
Similarly, to use ORG_CHART to display the maximum and average number of employees in each department, use the following statement:SELECT
A stored procedure can itself execute a stored procedure. Each time a stored procedure
calls another procedure, the call is said to be nested because it occurs in the context of a previous and still active call to the first procedure. A stored procedure called by another stored procedure is known as a nested procedure.
The following procedure returns a listing of users, roles, and privileged objects in a database, with their SQL privileges. Inside the procedure, two calls to another procedure are nested. It is necessary to begin by defining and committing the nested procedure —otherwise, the outer procedure will fail to commit. You should always start at the bottom of the “chain” when creating procedures that nest other procedures.
As it happens, this nested procedure performs no SQL. It simply takes an obscure constant from a set used internally by Firebird to represent object types, and returns a string that is more meaningful to humans:SET TERM ^ ;
Now for the outer procedure. The table it queries for its data is the system table RDB$USER_PRIVILEGES. It uses a number of manipulation techniques, including calls to the internal SQL function CAST( ) and to an external function RTRIM( ), from the standard external function library ib_udf, to massage CHAR(31) items into VARCHAR(31). We do this because we want to concatenate some of these strings and we do not want the trailing blanks.SET TERM ^ ;
First we loop through the table RDB$USER_PRIVILEGES, extracting and massaging some values directly into output arguments and others into local variables:FOR SELECT
Taking the current value of the output variable USER_NAME, we query RDB$ROLES to get the matching role owner and name, in case the “user” in the current row is actually a role. If it is not a role, these fields will be represented in the output by dashes:DO BEGIN
WITH GRANT OPTION is a special privilege that we want to know about in our output so, next, we convert this attribute to 'Y' if it is positive (1) or a blank if it is not:IF (GRANT_OPTION = 1) THEN
Now another query into RDB$ROLES, this time to find out whether the object that the privilege applies to is a role. If it is, we add a helpful prefix to its object name. If it is not a role, we go on to test whether our object is a column in a table and give it a qualified name if it is.IS_ROLE = NULL;
In RDB$USER _PRIVILEGES, tables and views are both object type 0. That is not good enough for us, so the next query checks the table RDB$RELATIONS to discover whether this particular object is a view:IF (OBJECT_TYPE = 0) THEN
At this point in our loop, we have almost everything we need. But our object still has its internal number and we still do not know what sort of “user” we have. Users can be other things besides people. Here is where we make the nested calls to get the internal numbers translated to meaningful strings. When that is done, the record is ready to output to the row cache and we call SUSPEND to complete the loop.
Nested procedure calls in triggers or stored procedures are almost identical to the calls we make from DSQL to execute them. Where the syntax differs is in the handling of the return values. In DSQL, the engine transports the return values back to the client in a record structure. In stored procedures, we use the PSQL keyword RETURNING _VALUES and provide variables to receive the values.EXECUTE PROCEDURE SP_GET_TYPE(:OBJECT_TYPE)
Calling the Procedure
This is another simple call:SELECT * FROM SP_PRIVILEGES;
If we do not want all of the columns, or we want them in a special order, we can do that. For example, suppose we just want to look at the privileges for all humanoid users other than SYSDBA:SELECT
Replaceable search parameters can be used:SELECT
A Procedure with Running Totals
In this procedure, we process records from the SALES table in the EMPLOYEE database. We keep two running totals: one for each sales representative and one for overall sales. As inputs we have just a start and end date for the group of sales records we want.SET TERM ^;
Notice that we are using an ordered set. If you are making a virtual table from a selectable stored procedure and you want an ordered set, it can be valuable to do the ordering set inside the procedure code. The optimizer can help performance here if it has useful indexes to work with, whereas ordering applied to the output set has, by its nature, no indexes to work with.
Once inside the loop, we begin massaging the data for our row and for the two running totals. We do a little magic to avoid repeating the rep’s name —it looks neater on a read -only display —although you would not do this if your application needed to target a row at random and use that column as a search key! We control the customer name in a similar way, to avoid an unnecessary search when the same customer occurs in consecutive records.DO
Our row is now complete and it goes to the row cache with the two running totals updated.END
Calling the Procedure
Our input arguments are of DATE type, a start date and an end date. The procedure is searching a TIMESTAMP to select the rows for the cursor. It adds a day to the input end date, to ensure that we get every record up to the end of that day. That keeps things simple: When we call the procedure, we need only provide the first and last dates, without having to worry about any records with timestamps later than midnight of the final day.
This call is certain to return the whole table:
SELECT * FROM LOG_SALES ('16.05.1970', CURRENT_DATE);
We might want a procedure like this to be parameterized:SELECT * FROM LOG_SALES (?, ?);
Viewing an Array Through a Stored Procedure
If a table contains columns defined as arrays, you cannot view the data in the column with a simple SELECT statement, since only the array ID is stored in the table. A stored procedure can be used to display array values, as long as the dimensions and data type of the array column are known in advance.
The JOB table in the sample database has a column named LANGUAGE_REQ containing the languages required for the position. The column is defined as an array of five VARCHAR(15) elements.
The following example uses a stored procedure to view the contents of the column. The procedure uses a FOR ... SELECT loop to retrieve each row from JOB for which LANGUAGE_REQ is not null. Then a WHILE loop retrieves each element of the array and returns the value to the calling application.SET TERM ^;
SELECT * FROM VIEW_LANGS;CODE GRADE CTY LANG ===== ===== ============ ========= Eng 3 Japan Japanese Eng 3 Japan Mandarin Eng 3 Japan English Eng 3 Japan Eng 3 Japan Eng 4 England English Eng 4 England German Eng 4 England French ...
The procedure could be modified to take input arguments and return a different combination of data as output.
It should be unnecessary to remind developers of the need to test PSQL modules with rigor and skepticism before launching them into production environments where, on a bad day, they could really do some harm. The parser will spank you for PSQL coding errors but, as programs, your modules are at least as vulnerable to logic and runtime errors as any application code you write.
For example, our procedure LOG_SALES works fine as long as every sales record has a non-null SALES_REP value. However, this is a nullable column. The procedure happens to be one that generates a result set in which each output row depends on values in the preceding rows. If we do not address the potential effects of null occurring in that key, our procedure is vulnerable to inconsistent results. In the section “Changing Stored Procedures,” later in this chapter, we add a safeguard to the logic to deal with this particular problem.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.