Characteristics
When creating stored routines we can specify a number of characteristics which allow us to tell MySQL some important information with regards to how the procedure will function. Currently a most of these characteristics are for future support only. The four we can currently specify are as follows
LANGUAGE SQL
[NOT] DETERMINISTIC
SQL SECURITY {DEFINER | INVOKER}
COMMENT ´string´
Contents |
[edit] LANGUAGE
In the future it will be possible to write stored routines not just in ANSI SQL but also in a number of other different programming languages. MySQL have highlighted PHP in particular and Oracles PL/SQL has been mentioned on a number of websites. This will open stored procedure programming to developers who have skills in other programming languages. But for now we are limited to ANSI SQL therefore we can only use the SQL characteristic like so.
drop function helloworld // create function helloworld() returns varchar(20) language SQL return "Hello World"; //
Download characteristic1.myp
As you can tell all that needs to be done is to specify the characteristic after the initial create function command. Currently SQL is the only supported value for the language characteristic, you do not need to specify this as it is the default value and is likely to be when new languages become supported.
[edit] DETERMINISTIC
The deterministic characteristic is used only within functions. A function is deterministic if it returns the same value each time for the same set of parameters. This will allow the optimizer to make decisions on how to handle the function and make improvements in speed based on this information. We can specifiy this characteristic like so.
drop function helloworld // create function helloworld() returns varchar(20) deterministic return "Hello World"; //
Download characteristic2.myp
There is little point in using this characteristic at present however as the optimizer currently ignores it and is only included for future compatibility.
[edit] SQL SECURITY
This is possibly the most important of the characteristics at present. The SQL Security characteristic can be set to either definer or invoker. This means that when the procedure is run any SQL statments will run against the security permissions of either the definer (the user who created the stored routine) or the invoker (the user calling the stored routine). This is set like so.
drop function helloworld // create function helloworld() returns varchar(20) sql security definer return "Hello World"; //
Download characteristic3.myp
[edit] COMMENT
The final characteristic is comment. As the name suggests the comment charactersitic is used to add a comment to the function. This comment is in the form of a string enclosed in quote marks. This is done like so.
drop function helloworld // create function helloworld() returns varchar(20) comment ´This is a function to return the string helloworld´ return "Hello World"; //
Download characteristic4.myp
The comment can be seen when using the show command as we will see in the next section. In addition to adding a routine level comment you can also added inline comments. These can be either a single line comment or multiple lines. To use a single line comment we use two - characters and to use a multiple line comment we place the comment between /* and */. This is done like so
drop function helloworld // create function helloworld() returns varchar(20) comment ´This is a function to return the string helloworld´ begin -- this is a single line comment /* this is a multiple line comment */ return "Hello World"; end //