Mysql5 stored procedure writing practice

Author：Eve Cole Update Time：2009-05-30 23:59:58

MySql5.0 and later supports stored procedures. I recently had time to study this.

Format:

CREATE PROCEDURE process name ([process parameters[,...]])
 [Characteristics...] Process Body

CREATE FUNCTION function name ([function parameters[,...]])
 RETURNS return type
 [Characteristics...] Function body

Procedure parameters:

[ IN | OUT | INOUT ] Parameter name Parameter type

Function parameters:

Parameter name Parameter type

Return type:

Valid MYSQL data type

Characteristics:

LANGUAGE SQL
 | [NOT] DETERMINISTIC
 | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
 | SQL SECURITY { DEFINER | INVOKER }
 | COMMENT 'string'

Procedure body/function body: The format is as follows:

BEGIN
 Valid SQL statement
 END

I don't care about proprietary features here. These are incompatible with the SQL specification, so the relevant content of characteristic (feature) is not considered.

There are several points to note during the development process:

1. Stored procedure comments: MySQL supports the use of -- or /**/ comments, where the former is a line comment and the latter is a segment comment

. 2. Variables are first declared with declare. Temporary variables can be directly modified with the @ prefix for reference.

3. When editing directly using the MySQL Administrator manager, you can directly enter the following function text;

however, if you automatically import stored procedures or functions in the script, because MySQL defaults to " ";" is the delimiter, then each sentence of the procedure body

is compiled by MySQL as a stored procedure, and the compilation process will report an error; therefore, the DELIMITER keyword must be used to declare in advance that

the delimiter will be restored when the current segment delimiter is used up. As shown below:

DELIMITER $＄
 Stored Procedures and Functions
 DELIMITER;

4. MySQL supports a large number of built-in functions, some of which are consistent with large commercial databases such as oracle, informix, sybase, etc., but there are also some functions with inconsistent names but consistent functions; or some with consistent names but different functions. This is especially useful for DBAs who have transferred from database development should pay attention to these.

5. Debugging of stored procedures or functions: I have not yet studied the various toolkits provided by MySQL, and I am not sure whether it provides debugging tools. However, compilation errors are relatively easy to find; as for debugging business processes, you can use a comparison The stupid way is to create a debugging table and insert a record at each process point in the package body to observe the program execution process. This is also a more convenient and stupid method. ^_^

The following are two examples, which provide a string encryption algorithm. Each time you call it with the same input parameters, you will get a different encryption result.

The algorithm is relatively simple and not strong. They are implemented in the form of functions and procedures as follows:

(1) Function

eg:

/**/
 set len=LENGTH(inpass);
 if((len<=0) or (len>10)) then
 return "";
 end if;

 set o ffset=(SECOND(NOW()) mod 39)+1; /*Modulo based on seconds*/
 /*insert into testtb values(offset,'offset: ');*/
 set string_out='YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#＄_'; /*Key*/
 set string_in='_＄#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

 set o utpass=CONCAT(outpass,SUBSTRING(string_out,offset,1));
 /* insert into testtb values(2,outpass);*/
 set string_out=CONCAT(string_out,string_out);
 set @i=0;
 REPEAT
 set @i=@i+1;
 set o utpass=CONCAT(outpass,SUBSTR(string_out,INSTR(string_in,SUBSTRING
 (inpass,@i,1))+offset,1));
 /* insert into testtb values(@i+2,outpass);*/
 UNTIL (@i>=len)
 end REPEAT;

 return outpass;
 END

CREATE FUNCTION fun_addmm(inpass varchar(10)) RETURNS varchar(11)

BEGIN

declare string_in varchar(39);

declare string_out varchar(78);

declare offset tinyint(2);

declare outpass varchar(30) default ';

declare len tinyint;

/*declare i tinyint;*/

(2) Process

CREATE PROCEDURE `pro_addmm`(IN inpass varchar(10),OUT outpass varchar(11))
 BEGIN
 declare string_in varchar(39);
 declare string_out varchar(78);
 declare offset tinyint(2);
 declare len tinyint;

 set o utpass=';

 set len=LENGTH(inpass);
 if((len<=0) or (len>10)) then
 set o utpass=';
 else
 set o ffset=(SECOND(NOW()) mod 39)+1;

 set string_out='YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#＄_';
 set string_in='_＄#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

 set o utpass=CONCAT(outpass,SUBSTRING(string_out,offset,1));

 set string_out=CONCAT(string_out,string_out);
 set @i=0;
 REPEAT
 set @i=@i+1;
 set o utpass=CONCAT(outpass,SUBSTR(string_out,INSTR(string_in,SUBSTRING
 (inpass,@i,1))+offset,1));
 UNTIL (@i>=len)
 end REPEAT;
 end if;
 END

The execution results are as follows:

mysql> call pro_addmm('zhouys',@a);
 Query OK, 0 rows affected (0.00 sec)

 mysql> SELECT @a;
 +---------+
 | @a |
 +---------+
 | U_PI6＄4 |
 +---------+
 1 row in set (0.00 sec)

 mysql> call pro_addmm('zhouys',@a);
 Query OK, 0 rows affected (0.00 sec)

 mysql> SELECT @a;
 +---------+
 | @a |
 +---------+
 | 9P8UEGM |
 +---------+
 1 row in set (0.00 sec)

 mysql> select fun_submm('U_PI6＄4');
 +---------------------+
 | fun_submm('U_PI6＄4') |
 +---------------------+
 | ZHOUYS |
 +---------------------+
 1 row in set (0.00 sec)

The encryption algorithm has several weaknesses:

1. It does not support upper and lower case

2. It does not support Chinese

3. The encryption strength is not enough

. People who are interested can study how to write the decryption function, so I won’t go into details here.