Home arrow MySQL arrow Page 2 - Examining MySQL 5.0

Syntax - MySQL

MySQL 5.0 gives the developer access to features that earlier versions of MySQL do not support. These include stored procedures and stored functions. This article, the second in a series, begins exploring the potential of these two features. It is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324).

TABLE OF CONTENTS:
  1. Examining MySQL 5.0
  2. Syntax
  3. Stored Procedure Examples
  4. Stored Function Examples
  5. Declaring Variables Within Procedures
By: Apress Publishing
Rating: starstarstarstarstar / 22
April 27, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

As we mentioned already, to create a new stored procedure or stored function, you use the CREATE PROCEDURE or CREATE FUNCTION command, respectively. Let’s examine the syntax for these commands.

CREATE PROCEDURE | FUNCTION procedure_name (parameter1[, parameter2,...] )
[RETURNS type]
[ LANGUAGE SQL | [NOT] DETERMINISTIC |
 
SQL SECURITY {DEFINER | INVOKER} | COMMENT string]
BEGIN
sql_statement(s)
END
delimiter

The CREATE keyword is followed by PROCEDURE or FUNCTION depending on which one you want to create. The procedure_name is simply the name by which you wish to refer to the stored procedure or stored function, and can be any legal MySQL identifier. This is followed by a list of zero or more parameters—even if there are none, the parentheses are required (think of a PHP or JavaScript func tion declaration here). For stored procedures, each parameter takes the form

[ IN | OUT | INOUT ] parameter_name type

where IN , OUT , or INOUT specifies whether the parameter is input, output, or both ( IN is the default value). This is followed by the name of the parameter and its type, which may be any valid MySQL datatype, just as you’d use as part of a column definition in a CREATE TABLE statement. The IN , OUT , and INOUT keywords are not used with stored functions, as stored functions can have input parameters only.


TIP  
If you’ve had experience in programming in languages that make use of them, then you can think of INOUT parameters as being somewhat like passing values by reference or using pointers; the variable itself is changed by the procedure. We’ll provide an example of a stored procedure using INOUT parameters a bit later (see the “IF … ELSEIF … ELSE” section).

If you’re defining a stored function, you can specify a return type using RETURNS . Remember that only a stored function may return a value, and the body of the function must contain a RETURN statement. It’s important to remember that the parentheses are still required even in cases where your procedure doesn’t have any input or output parameters at all; this lets MySQL know not to expect any.


NOTE 
From this point on in our discussion, we’ll use the term “procedure” to mean either a stored procedure or a stored function, and we’ll make it clear if what we’re saying applies only to one or the other.

Each of the next set of clauses, also known as the procedure’s “characteristics,” is optional. In MySQL 5.0.0 and 5.0.1, the LANGUAGE clause accepts only SQL as its argument, although this will probably change in the future, as there are plans to introduce support for additional languages in stored procedures and stored functions. PHP is a very strong candidate in this regard, and it’s likely to be the first external language to be supported. The DETERMINISTIC keyword means that the stored procedure or stored function is always supposed to produce the same result for the same input parameters, and so NOT DETERMINISTIC means that the result may be different for different invocations of the procedure (or function), even if the input is the same. This clause is accepted but not actually supported in MySQL 5.0.0.

In addition, the SQL SECURITY clause can be used to determine whether the privileges of the procedure’s creator or user should be in effect when the procedure is invoked. In the current MySQL 5.0 alpha versions, in order to access tables referenced in the procedure, the user calling the procedure must have the appropriate permissions on those tables. This is almost certain to be fixed by the time MySQL 5.0 is released for production use. MySQL also supports comments for procedures.

Once created, stored procedures and stored functions are stored in the mysql.proc table indefinitely. In order to delete a procedure, you can use DROP PROCEDURE and DROP FUNCTION along with the name of the procedure; for example, in order to delete a stored procedure named myproc, you would execute the statement

DROP PROCEDURE myproc;

To view the statement used to create a procedure, use the SHOW CREATE PROCEDURE or SHOW CREATE FUNCTION statement. To see the definition of a function named myfunc, you’d use

SHOW CREATE FUNCTION myfunc;

You can also alter some characteristics of a procedure using the ALTER PROCEDURE or ALTER FUNCTION command:

ALTER PROCEDURE | FUNCTION procname
 
NAME newname | SQL SECURITY {DEFINER | INVOKER} | COMMENT
comment


CAUTION 
CREATE PROCEDURE , SHOW CREATE PROCEDURE, DROP PROCEDURE , and ALTER PROCEDURE work only with stored procedures. CREATE FUNCTION , SHOW CREATE FUNCTION , DROP FUNCTION, and ALTER FUNCTION work only with stored functions. For example, you can’t use DROP PROCEDURE to drop a stored function.

The body of the stored procedure or stored function comes between the BEGIN and END keywords, and may contain nearly any valid SQL statements, subject only to the limitation that in MySQL 5.0, only stored procedures (but not stored functions) may refer to tables. They may also contain some flow-control, looping, and variable-declaration constructs that are specific to stored procedures and stored functions. See the sections “Flow Control in Stored Procedures,” “Looping in Procedures,” and the sidebar “Declaring Variables Within Procedures” for more about these.


TIP 
While some databases don’t permit you to create, alter, or drop tables within a stored procedure, MySQL does allow you to do so. You’ll see an example of creating a table inside a stored procedure when we discuss cursors later in this section. <



 
 
>>> More MySQL Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: