Database Reference
In-Depth Information
Figure 7.6
Use apply operator.
function. As shown in Figure 7.6, the way to use an APPLY operator is similar to the use of a JOIN
operator. he function fnClassmate returns the student and course information for each class.
7.3.2 Procedures
Unlike functions, procedures do not explicitly return a value. No keyword RETURN is used in
a procedure to return a value. Often, procedures are stored and executed on a database server.
his kind of procedure is called a
stored procedure
. Stored procedures play an important role in
database management and application due to the following advantages:
◾
A stored procedure can be shared by multiple database applications, and it can be accessed
by many users with proper privileges.
◾
Since stored procedures are already executable iles, they can be called by a database applica-
tion without reparsing the code. herefore, using stored procedures can improve database
performance.
◾
Written by experienced programmers and optimized by DBMSs, stored procedures have
fewer programming errors and run faster.
◾
Being installed on servers, stored procedures are better protected by server security measures.
◾
When a database application calls a stored procedure for a certain activity, only the inal
computation results are returned to the database application. herefore, using stored proce-
dures generate less network traic.
Stored procedures are used to accomplish various tasks. Usually, you can use stored procedures
to do the following things:
◾
Perform database management tasks such as adding a user login.
◾
Calculate complicated logics requested by a database application.
◾
Invoke other stored procedures or functions.