|
|
You can execute a stored procedure using the CALL keyword in Oracle and DB2 (which is actually the SQL standard) or using the EXECUTE (or the short form EXEC) keyword in SQL Server or Access.
Oracle also supports the EXECUTE sp_name (param) syntax as a shorthand form for the following:
BEGIN sp_name(param); END;
Older versions of Oracle support only this syntax and don't support CALL.
To illustrate this, imagine you have a stored procedure called DeleteRow in each of these databases, which takes as a parameter a numerical ID value corresponding to the row to be deleted. In Oracle or DB2, you could do the following to delete the row with an ID of 5:
CALL DeleteRow(5);
In SQL Server or Access, you can use the following:
EXECUTE DeleteRow 5;
Note that when calling SQL Server or Access stored procedures, you don't enclose the parameters in parentheses.
As you can see, although the syntax does vary, the basic idea is the same—you provide the name of the stored procedure and the parameter(s) it requires. These parameters may also be output parameters; that is, the value will be set by the stored procedure and returned to the caller. In all cases, the meaning of a parameter is determined by its position in the list of parameters of a stored procedure, where multiple parameters are separated by commas. Which parameter goes in which position is determined by the definition of a stored procedure.
You create stored procedures using the SQL keyword combination CREATE PROCEDURE (with the abbreviation or CREATE PROC available in some RDBMSs). Again, the exact usage varies. In the following sections, you'll look at the various RDBMS syntaxes and see them in action with a simple InsertStudent stored procedure that inserts a new student into the Student table.
The syntax for creating a stored procedure (supported by SQL Server, Oracle, DB2, and Access) is as follows:
Although this much is standard, the individual RDBMSs each provide their own extensions to this and in particular their own keywords for programming code within a stored procedure. To show the basic syntax for creating stored procedures in the different RDBMSs, we'll run through a simple example that inserts a new row into the Student table.
| Note |
Oracle supports using the keyword IS instead of AS. |
The basic format for creating a SQL Server stored procedure is identical to the ANSI standard. The most important point to note is that the names of parameters in SQL Server must begin with the @ character. In fact (as you'll see shortly), this is true of variables in SQL Server generally.
 |
Enter this query and execute it against the InstantUniversity database:
CREATE PROCEDURE InsertStudent(@i_StudentID INT,
@i_StudentName VARCHAR(50))
AS
BEGIN
INSERT INTO Student(StudentID, Name)
VALUES (@i_StudentID, @i_StudentName);
END;
This creates a stored procedure called InsertStudent with two input parameters, @i_StudentID and @i_StudentName. These parameters appear in the parameter list after the procedure name in the form param_name data_type. They allow you to pass values into the stored procedure, which you can then access in your SQL statements. In this case, you just insert these values straight into the Student table.
The body of the procedure can consist of a single statement, but you can also include it in a BEGIN...END block, which allows you to create stored procedures consisting of multiple statements. You use this syntax here, even though it's not necessary because there's only one statement in the body of the procedure.
Once you've created the procedure, you can execute it using this statement:
EXECUTE InsertStudent 500, 'Víteslav Novák';
This will enter a row with the name 'Víteslav Novák' and a StudentID of 500 into the Student table.
|
The fundamental syntax for stored procedures in Oracle is similar to SQL Server and the ANSI standard:
CREATE [OR REPLACE] PROCEDURE sp_name (parameter_list) AS BEGIN sp_body; END; /
The first thing to notice here is the OR REPLACE option, available when you're creating most types of database objects in Oracle. If this is specified, an existing stored procedure with the same name will be replaced when you create the new procedure. If this isn't specified and a procedure with the same name exists, an error will be thrown, and the new procedure won't be created.
As with SQL Server, you include the SQL statements for the procedure in a BEGIN...END block (although in the case of Oracle, this is mandatory, even if there's only one statement). The biggest difference in the basic syntax is in the way you use parameters. First, Oracle parameters don't start with an @ character; second, you need to explicitly declare the "direction" of your parameters—that is, whether they're input parameters that you simply pass into the procedure, output parameters that you use to return values to the application that called the procedure, or input/output parameters that both pass data into and out of the procedure.
When you create a stored procedure, Oracle typically attempts to compile the procedure, even if it contains PL/SQL errors. A faulty procedure can't be executed, but it will still be created, so you'll need either to drop it before recompiling or to use the REPLACE option. Also, notice that Oracle won't tell you what's wrong with the procedure unless you specifically ask it to do so. To do this, just enter SHOW ERRORS at the SQL*Plus command prompt.
|
Enter this query into SQL*Plus:
CREATE OR REPLACE PROCEDURE InsertStudent(
i_StudentID IN INT,
i_StudentName IN VARCHAR)
AS
BEGIN
INSERT INTO Student (StudentID, Name)
VALUES (i_StudentID, i_StudentName);
END;
/
Again, you create a stored procedure called InsertStudent with two input parameters, i_StudentID, and i_StudentName. You mark the fact that these are input parameters by placing the IN keyword between the parameter's name and its data type.
You can execute your new procedure from SQL*Plus as follows:
CALL InsertStudent(500, 'Víteslav Novák');
|
And now for something completely different.... Although you use a SQL statement to create stored procedures in DB2, you can't just execute this statement in Command Center or a similar tool: You need to create and compile using the DB2 Development Center.
Development Center can only compile stored procedures if a C++ compiler is installed on the machine on which it's running. On Windows, this means Microsoft Visual C++ must be installed; you'll also need to alter the Path, Include, and Lib system environment variables. Please consult the DB2 documentation for further details.
To create a DB2 stored procedure, start by opening Development Center. You'll be invited to create a new project, as shown in Figure 9-1.
Click the Create Project button, and you'll be asked to give a name for the new project. Call it InstantUniversitySprocs, as shown in Figure 9-2.
The next task is to connect to the InstantUniversity database. First, you have to specify whether you want to work offline or you want to connect to the DB2 server. Ensure that Online is selected and click Next, as shown in Figure 9-3.
You're now asked to supply details of the database you want to connect to, as shown in Figure 9-4.
Enter the alias for the InstantUniversity database (INSTUNI in this case) and the ID and password of the user account you want to connect with, and then click Next. You're now asked whether you want to create a stored procedure or User-Defined Function (UDF) and whether you want to write it in SQL or Java, as shown in Figure 9-5.
Java stored procedures are beyond the scope of the book, so make sure that both Stored Procedure and SQL are selected, and click OK. You're now requested to give a name to the procedure, as shown in Figure 9-6.
Replace the default name with InsertStudent, and click the Next button (you've left the name of the schema where it's stored as the prefix of the procedure name).
DB2 provides a wizard that creates the SQL statements for the stored procedure, but this is a SQL book, so you're going to do this manually. DB2 allows stored procedures to return none, one, or more result sets; this first example doesn't return a result set, so select None for the Result Set field and then click Finish, as shown in Figure 9-7.
Back in the main Development Center window, open the stored procedure by double-clicking it. DB2 has added some default SQL code to create the procedure, as shown in Figure 9-8.
You're going to create the procedure manually, so delete this code and replace it with the code for the stored procedure:
CREATE PROCEDURE DB2ADMIN.InsertStudent (
i_StudentID INT,
i_StudentName VARCHAR(50))
P1: BEGIN
INSERT INTO Student (StudentID, Name)
VALUES (i_StudentID, i_StudentName);
END P1
Now that you've got this far, the SQL syntax for creating the procedure isn't too different from the standard:
DB2 can optionally use labels for BEGIN...END blocks, so it's clear which block the END keyword finishes.
Save the procedure in the editor and return to the main Development Center window. Before you can use this stored procedure, you need to compile it. Do this by right-clicking the procedure name and selecting Build. Once the procedure has compiled, you can call it from Command Center using this statement:
CALL InsertStudent(500, 'Víteslav Novák');
This will enter a row with the specified values into the Student table.
Access doesn't support stored procedures as such, but it does allow you to create stored queries using the CREATE PROCEDURE syntax. You can use stored queries like stored procedures or (if they return a result set) like views, so if you have a query named GetStudents that returns a result set of all the students in the database, you call it just as you call a view:
SELECT StudentID, Name FROM GetStudents;
Or you could call it as you would a stored procedure in SQL Server:
EXECUTE GetStudents;
You should be aware of a couple more restrictions with Access queries. First, queries can only contain one SQL statement, so you can't perform two actions with a query. Second, you can't enter the CREATE PROCEDURE statement directly into the SQL window in Access. When you create an Access query, you're effectively executing a CREATE PROCEDURE statement behind the scenes, so you'll get an error if you type the statement manually into the SQL window. Because you want to show the full syntax, you'll print the whole CREATE PROCEDURE statement. You can run this against the InstantUniversity database from an application via a data-access technology such as ADO.
|
Execute this query against InstantUniversity:
CREATE PROCEDURE InsertStudent(@i_StudentID INT,
@i_StudentName VARCHAR(50))
AS
INSERT INTO Student (StudentID, Name)
VALUES (@i_StudentID, @i_StudentName);
If you want to type this directly into the SQL window in Access, the syntax is as follows:
PARAMETERS @i_StudentID INT, @i_StudentName VARCHAR(50); INSERT INTO Student (StudentID, Name) VALUES (@i_StudentID, @i_StudentName);
Otherwise, the syntax is identical to that for SQL Server stored procedures. As in SQL Server, Access parameter names are all preceded by the @ character. You can execute this query using the EXECUTE keyword:
EXECUTE InsertStudent 500, 'Víteslav Novák';
Note that you don't use parentheses for the arguments passed into an Access query.
|
As with all database objects, you can delete an existing stored procedure using the DROP command:
DROP PROCEDURE ProcName;
This is supported by all platforms that support stored procedures.
You can also modify a stored procedure using the ALTER PROCEDURE statement in SQL Server and Oracle. In SQL Server, the ALTER PROCEDURE statement has the same syntax as the CREATE PROCEDURE statement and simply re-creates the stored procedure with the new specification. As with views, the ALTER PROCEDURE statement in Oracle is used to recompile the view rather than to redefine it.
We mentioned earlier that the parameters to stored procedures can be of three types: input, output, or input/output. As you've seen in the previous examples, input parameters are used solely to pass information to the procedure, for example, to determine which rows to retrieve, update, and so on. On some systems, stored procedures can also return values, in the same way that functions in programming languages (or indeed, SQL UDFs) return values. However, before you look at output parameters and return values in detail, you need to understand how to create and use variables in SQL code.
Variables in SQL are similar to variables in other languages. That's to say that they're basically a means of storing a value in memory, referenced by a unique name. For example, you could have a variable called MyVar that you assign a value to and then use it to send data to a stored procedure via a parameter. Alternatively, you might get a value from a stored procedure into a variable, either via an output parameter or a return value.
As you've seen, you can use literal values as parameters when calling a stored procedure. However, you'll often need to use variables to store and retrieve data values from parameters, and even if you don't you may have to use variables inside a stored procedure.
Note that Access doesn't support variables (apart from input parameters).
SQL variables are simply defined names for a specific item of data. Like parameters, variables must have an @ symbol prefix in SQL Server and MySQL, but not in Oracle or DB2.
Variables are declared using the DECLARE keyword:
DECLARE var_name var_type(length);
You can also declare several variables of the same type simultaneously:
DECLARE var1_name, var2_name, var3_name var_type(length);
The var_type can be any of the data types defined in your RDBMS, such as int or nvarchar; length is an optional integer value saying how much storage to allocate (that is, the width of the column). You can also specify a scale (the number of decimal places) to use for floating-point values. Typically, you'll be declaring variable types that match column data types in tables that are manipulated by the stored procedure you're calling, so you'll often have to take this into account in both var_type and length. Also, some values won't be meaningful for the data type in use, and some variable types don't require a value for this parameter (including int), so you need to be careful here.
In DB2, variables can only be declared within a BEGIN...END block. When these blocks are used in dynamic SQL (that is, not in a stored procedure or function), you need to specify the ATOMIC keyword:
BEGIN ATOMIC DECLARE myvar VARCHAR(50); -- Assign and use variable END;
We'll discuss the atomic properties when you look at transactions in Chapter 10, "Transactions."
If you're entering a compound statement such as this via DB2's Command Center, you need to change the character that marks the end of the statement from the default semicolon, or Command Center won't pass the complete statement to DB2 (it will only reach the first semicolon in the embedded statements). You can change the statement termination character on the General tab of the Tools Ř Tools Settings menu, as shown in Figure 9-9.
In Oracle, the DECLARE statement is placed before the BEGIN block. Any variables you want to use in the compound statement must be declared in this DECLARE statement:
DECLARE myvar VARCHAR(50); myothervar INT; BEGIN -- Assign and use variables END; /
As well as RDBMS-specific types, you can also use the special type of CURSOR:
DECLARE cursor_name CURSOR FOR cursor_spec;
This is a special case of variable, used to loop through rows returned from a query. Cursors are used by Oracle and DB2 to return result sets from a stored procedure, and you'll learn about them in more depth a little later.
Once you've declared a variable, you may need to assign a value to it prior to a procedure call. The syntax for this varies between RDBMSs. In SQL Server you assign values with the SET or SELECT keywords:
SET @VarName = Value; SELECT @VarName = Value;
Here VarName is the name of the variable, and Value is a literal value of the appropriate type or some expression that evaluates to a value of the appropriate type. This could include a SELECT statement used as a subquery.
Variables can also have values assigned to them in other ways, such as via functions or subqueries.
In MySQL and DB2, you can also use SET in the same way as previously described (though DB2 doesn't use the @ character for variable names). However, MySQL also allows values to be assigned to variables in any SQL statement although this requires using the := assignment operator rather than = because = is used as a comparison operator outside of SET statements. For example:
Oracle also uses this syntax (although you don't need @):
VarName := Value;
Oracle and DB2 have a special syntax for storing values from a SELECT query into variables:
SELECT Name FROM Professor INTO ProfessorName WHERE ProfessorID = 1;
This will store the name of the professor with an ID of 1 into the ProfessorName variable.
Once you have a value in a variable, you can then go ahead and use the value in subsequent SQL statements in the same batch of commands. This is important in the body of stored procedures. Effectively, what happens with parameters is that the parameter is a variable declared and assigned when the stored procedure executes. For example, if you had a DeleteStudent stored procedure that deleted a single row from the Student table according to an ID parameter, you could define the stored procedure as follows (with minor variations for the different platforms):
CREATE PROCEDURE DeleteStudent(i_StudentID INT) AS BEGIN DELETE FROM Student WHERE StudentID = i_StudentID; END;
Here the parameter i_StudentID is in fact a variable, which is subsequently used in the WHERE clause to select the correct name. This applies to all RDBMSs with stored procedures.
If you want, you can provide a default value for a parameter. If a value isn't provided for that parameter, the default value will be used. You do this in SQL Server and Access by adding the default value after the parameter's data type, separated by an equals sign. For example, to create an InsertProfessor procedure that uses a default value of 'Prof. A.N. Other' for the professor's name if one isn't supplied, use this:
CREATE PROCEDURE InsertProfessor ( @i_ProfID INT, @i_ProfName VARCHAR(50) = 'Prof. A.N. Other') AS INSERT INTO Professor (ProfessorID, Name) VALUES (@i_ProfID, @i_ProfName);
In Oracle, you use the keyword DEFAULT instead of an equals sign:
CREATE OR REPLACE PROCEDURE InsertProfessor (i_ProfID IN INT, i_ProfName IN VARCHAR DEFAULT 'Prof. A.N. Other') AS BEGIN INSERT INTO Professor (ProfessorID, Name) VALUES (i_ProfID, i_ProfName); END; /
You can then call this procedure as usual.
| Note |
DB2 doesn't support default parameter values. |
Now that you know how to use variables, you can look in more detail at output parameters. Output parameters are used to pass data back from the procedure to the calling application. The parameter is assigned a value within the body of the procedure, and this value is returned to the application.
Return values for procedures do exist on some platforms (although not Oracle or Access) as well as output parameters, but they can only be integers and are used for returning the error status of the operation.
For example, suppose you have a stored procedure that returns the name of a student given an ID. You would pass the ID in as an input parameter and return the name as an output parameter. To show how output parameters work, let's see how you implement this in the various RDBMSs.
Output parameters in SQL Server are marked by adding the keyword OUT or OUTPUT after the parameter's data type. For example:
|
Create the following stored procedure:
CREATE PROCEDURE GetStudentName(
@i_StudentID INT,
@o_StudentName VARCHAR(50) OUTPUT)
AS
BEGIN
SET @o_StudentName = (SELECT Name FROM Student
WHERE StudentID = @i_StudentID);
END;
Here you retrieve the name of a student given the student's ID number. This ID is passed into the stored procedure as an input parameter. You return the name from the procedure as an output parameter called @o_StudentName. You set the value of this parameter using a subquery in the SET statement, selecting the name of the appropriate student.
Once this procedure has been compiled, you can execute it by entering the following statements into Query Analyzer:
DECLARE @StudentName varchar(50); EXEC GetStudentName 4, @StudentName OUTPUT; PRINT @StudentName;
Here you declare the variable that you'll use to store the output parameter and then use the EXEC keyword to execute the parameter. Notice that you have to specify the OUTPUT keyword when you call the procedure, as well as when you define it. Finally, you print the value of the @StudentName variable using Query Analyzer's PRINT function:
Bruce Lee
|
For output parameters in Oracle, you just replace the IN keyword with OUT:
|
Create the GetStudentName procedure by entering this code into SQL*Plus:
CREATE OR REPLACE PROCEDURE GetStudentName(
i_StudentID IN INT,
o_StudentName OUT VARCHAR)
AS
BEGIN
SELECT Name INTO o_StudentName FROM Student
WHERE StudentID = i_StudentID;
END;
/
Here you use Oracle's SELECT INTO syntax to store the name of the student with the specified ID into your output parameter, o_StudentName. When the procedure is executed, this value will be available to the application. You can execute the stored procedure and print the value of o_StudentName in SQL*Plus using the following lines:
SET SERVEROUT ON DECLARE StudentName VARCHAR(50); BEGIN GetStudentName(3, StudentName); dbms_output.put_line(StudentName); END; /
The first line here, SET SERVEROUT ON, is a SQL*Plus command that allows you to print output to SQL*Plus; if you don't set SERVEROUT to on, you won't be able to display the value of your output parameter after the procedure has executed.
Next, you declare a variable called StudentName to hold the output value and pass this into the stored procedure. Notice that you don't use the CALL keyword when calling a stored procedure from inside a BEGIN block. Finally, you display the value by calling SQL*Plus's dbms_output.put_line() function:
Emily Scarlett
|
In DB2, you place the OUT keyword before the parameter name:
(OUT OutputParamName INT)
|
The DB2 code for creating your GetStudentName procedure looks like this:
CREATE PROCEDURE GetStudentName(i_StudentID INT,
OUT o_StudentName VARCHAR(50))
P1: BEGIN
SET o_StudentName = (SELECT Name FROM Student
WHERE StudentID = i_StudentID);
END P1
Create a new stored procedure in the InstantUniversitySprocs project in Development Center, enter this code, and build the procedure.
In DB2, you can set a variable to a value retrieved from a query using the normal SET syntax, assigning the result of the SELECT query to the variable:
SET o_StudentName = (SELECT Name FROM Student
WHERE StudentID = i_StudentID);
To execute the procedure in DB2 Command Center, you just need to enter one line:
CALL GetStudentName(2, ?);
You use a question mark (?) to represent the output parameter. After the procedure has been executed, Command Center displays the values of any output parameters:
Value of output parameters -------------------------- Parameter Name : O_STUDENTNAME Parameter Value : Gary Burton Return Status = 0
|
As well as input and output parameters, DB2 and Oracle both support input/output parameters. These are parameters that you use both to feed data into the stored procedure and to return data from it. Input/output parameters are defined using the INOUT keyword:
(INOUT SomeParamName INT) -- DB2 (SomeParamName INOUT INT) -- Oracle
To use an input/output parameter, you need to declare and initialize a variable and then pass it into the stored procedure. The value of the parameter will then be modified within the stored procedure, and you can read the value of the variable again once the procedure has been executed.
|
|