13.5. SQL: Routines

The SQL standard uses the generic term routineto describe a body of code that can be invoked from some other code—this covers functions, procedures, and the methods of user-defined data types.

User Defined Functions (UDFs)

We’ll look first at functions, which, in simple terms, are bodies of code that return a result. The SQL standard defines a large number of built-in functions for calculating logarithms, generating random numbers, transforming character strings into all uppercase or all lowercase, and so on. These can be used wherever appropriate in SQL code. It’s also convenient to be able to define new functions to encapsulate pieces of code that are likely to be reused. In SQL, functions can return scalar (single) values of a given data type. The SQL:2003 standard extended the concept to include functions that return tables. The basic SQL:2003 syntax for creating a function is as follows.

create function functionname ([ parameter-list ])
  returns { data-type | table col-name data-type [,.... ] }
  function-body

The parameter-list is a (possibly empty) comma-separated list of parameter name, parameter type pairs. The data-types for the returned value can be any legitimate SQL type. The function body can be composed of any valid combination of SQL statements, but must include a return clause that returns a parameter of the type specified in the returns clause. UDFs can be removed by using drop function function-name.

Commercial DBMSs may place other restrictions. For example, in SQL Server, UDFs cannot use SQL statements that would produce different results if the function was invoked repeatedly with the same parameters. Examples of such statements include getting the current time, and generating random numbers. SQL Server also prohibits side effects in UDFs so, for instance, a function cannot be used to update a base table.

The following example shows a scalar UDF for SQL Server that takes a currency amount (defined as a decimal value) and a currency symbol as parameters, and returns a string consisting of the currency symbol concatenated with a string version of the amount. SQL Server uses “+” for string concatenation, as opposed to the standard “II”.

create function moneyString ( @amount decimal( 9, 2 ), @symbol char ( 1 ) )
  returns varchar ( 11 )
  return @symbol + cast ( @amount as varchar(10) )

Once defined, functions can generally be used at any point where a value of the return type could be used. So taking the example just given, we could use a call to moneyString() at any point where a varchar(11) value would be legitimate.

Stored Procedures

Stored procedures, commonly referred to as “sprocs”, are bodies of code that are explicitly called from other code. In this section we’ll assume that the body of the procedure is written in SQL, but it’s possible for the procedure actions to be written in some other language, such as ’C, and then called from SQL. Stored procedures can (and often do) have side effects, such as updating tables during their execution. Encapsulating code in a stored procedure offer several potential benefits.

Modularity. Code in a stored procedure is readily reusable in other code. A well-maintained library of stored procedures can be a valuable resource when developing new applications. Modularity also helps to ensure consistency. For example, a business rule can be implemented once in a stored procedure and called as needed, instead of having the same rule implemented in perhaps slightly different ways in different places. The same argument applies to maintainability. If a change is needed, it need only be applied in one place. As long as the previous interface is maintained, other programs that call the stored procedure need not be aware of any changes that have been made.

Security. Users of a stored procedure are offered only the functionality that the procedure provides. Limiting access in this way drastically reduces the possibility of damage to the database, whether deliberate or accidental. The authority given to users of a stored procedure can be different from the authority to access any underlying tables, so any actions can only be taken in the context of the procedure. For example, all modifications to an “Orders” table can be channeled through a stored procedure that imposes business rules and other constraints on what changes can be made, instead of giving users direct access to the base table.

Performance. Stored procedures are typically compiled, optimized, and cached ready for use when needed. The details vary from one implementation to another, and extensions to the basic syntax are usually provided to address issues such as the need to redo compilation and optimization if the usage circumstances change. Stored procedures can also reduce network traffic: instead of constructing a query in an application and passing the resulting SQL across a network, only the parameters need be passed to a stored procedure.

The basic syntax for creating and dropping a stored procedure is as follows.

create procedure procedurename
  ( [{in | out | inout } parmname parmtype [ .... ] ])
         procedure-body

drop procedure procedurename

A stored procedure takes zero or more parameters. Each parameter can be specified as in (external values are provided to the procedure), out (the procedure makes its results available externally), or inout (the input parameter may be modified by the procedure for external access). As usual, commercial systems may vary from the standard syntax. For example, in SQL Server any parameters of a stored procedure are considered as in parameters unless they are explicitly denoted as out, and there is no inout option.

The procedure body is made up of any legal combination of SQL statements. Stored procedures may make calls to other procedures, and a procedure may call itself recursively. An implementation may place restrictions on the number of levels of nesting permitted, for example, SQL Server allows nesting up to 32 levels deep. Stored procedures can also be called from within a trigger, which gives both the advantage of automatic invocation (when the relevant trigger event occurs) and the modularity offered by a stored procedure. For instance, we may want to apply the same constraint under several different circumstances, which we could do by having several different triggers call the same stored procedure.

One SQL statement that requires some attention in a stored procedure is the return statement. Once a return is encountered, the stored procedure is exited immediately, and so a return is often associated with a branch of a test condition, along the lines of: ”ifx is the case then return, otherwise,...”. Although not necessarily enforced by an implementation, a recommended practice is to use a value associated with the return statement to indicate status. Typically, a return code of zero would indicate “all is well”, whereas a nonzero value could be used to indicate a specific error condition that had been encountered. The results of any computation carried out by the stored procedure should be returned through out parameters rather than using the return value.

Exercise 13.5

The following schema shows part of a database that holds information about repair technicians and the types of assemblies that they are competent to repair. Different technicians may be competent at repairing different assemblies.

1.Write a stored procedure that inserts a new row into the Technician table and returns a result of zero if it succeeds. If the new row would create a loop in the technician-supervisor hierarchy the insert should not be performed and the procedure should return the result code 99. Use recursion to check for loops in the hierarchy. You can assume that no hierarchy will be more than 10 levels deep.
2.Most commercial systems provide a function that returns the last identity value used in the update of a table. The empNr column in the Technician table would be a typical application for an identity column. Write a stored procedure that adds a new row to the Technician table and returns as an out parameter the identity value that was generated for the new row.
3.Assume that we have a table with the schema Currency(code, symbol, rate), where code identifies a particular currency, symbol is a single Unicode character, and rate is a conversion rate. Write a function that takes a currency code and a decimal number as parameters and returns a string consisting of the number provided multiplied by the rate for the currency code appended to the appropriate currency symbol. For example, if the Currency table contains a row (’USD’, ’$’, 2.0), given the parameters ’USD’ and 100.00 the function should return ’$200.00’. The returned amount should have two decimal digits.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.147.67.166