Mysql5 Stored Procedure writing practices

Stored procedures are supported after MySql5.0, Studied the format:

Create procedure process name ([process parameter [,...])

[Features...] process body

Create function Name ([FUNCTION parameter [,...])

RETURNS return type

[Features...] function body

Process parameters:

[IN | OUT | INOUT] parameter name parameter type

Function parameters:

Parameter name parameter type

Return type:

A valid MySQL data type is enough.

Features:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| {Contains SQL | no SQL | reads SQL data | modifies SQL DATA}

| SQL SECURITY {DEFINER | INVOKER}

| COMMENT 'string'

Process body/function body: the format is as follows:

BEGIN

Valid SQL statements

END

I don't care about proprietary features here. These are not compatible with SQL specifications, so characteristic (feature) related content is not considered.

//

Note the following points during development:

1. stored procedure annotation: MySQL supports -- Or/**/annotation, where the former is a line annotation and the latter is a segment annotation.

2. Use declare to declare a variable. The temporary variable can be directly prefixed with @ for reference.

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

However, when a stored procedure or function is automatically imported into a script, because MySQL uses the separator ";" by default

If MySQL compiles data in a stored procedure, an error is reported during the compilation process. Therefore, use the DELIMITER keyword to declare the DELIMITER of the current segment.

When it is used up, the separator is restored. As follows:

DELIMITER $

Stored Procedures and Functions

DELIMITER;

4. MySQL supports a large number of embedded functions, some of which are consistent with those of large commercial databases such as oracle, informix, and sybase,

However, some functions have different names, but the functions are the same. Or some functions have different names.

Pay attention to the DBA after these databases are developed and transferred.

5. debugging of stored procedures or functions: I have not studied the various tool kits provided by MySQL, but I still do not know that it provides debugging tools.

No, but the compilation error is relatively good for searching. for business process debugging, you can use a stupid method, that is, create

Debugging tables, insert a record for each process point in the package body to observe the program execution process. This is also a convenient dumb.

Method. Pai_^

The following are two examples. A string encryption algorithm is provided. Each call with the same input parameter will get different encryption results,

The algorithm is relatively simple and not powerful. The functions and procedures are implemented as follows:

(1) Functions

Eg:

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 ;*/

/**/

Set len = LENGTH (inpass );

If (len <= 0) or (len> 10) then

Return "";

End if;

Set offset = (SECOND (NOW () mod 39) + 1;/* modulo based on the number of seconds */

/* Insert into testtb values (offset, 'offset :');*/

Set string_out = 'yn8k1jozvurb3mdets5gpl27axwihq94c6f0 #$ _ ';/* Key */

Set string_in = '_ $ # ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ';

Set outpass = 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 outpass = 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

(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 outpass = ';

Set len = LENGTH (inpass );

If (len <= 0) or (len> 10) then

Set outpass = ';

Else

Set offset = (SECOND (NOW () mod 39) + 1;

Set string_out = 'yn8k1jozvurb3mdets5gpl27axwihq94c6f0 #$ _';

Set string_in = '_ $ # ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 ';

Set outpass = CONCAT (outpass, SUBSTRING (string_out, offset, 1 ));

Set string_out = CONCAT (string_out, string_out );

Set @ I = 0;

REPEAT

Set @ I = @ I + 1;

Set outpass = CONCAT (outpass, SUBSTR (string_out, INSTR (string_in, SUBSTRING (inpass, @ I, 1) + offset, 1 ));

UNTIL (@ I> = len)

End REPEAT;

End if;

END

//

The execution result is as follows:

Mysql> call pro_addmm ('zhouys ', @ );

Query OK, 0 rows affected (0.00 sec)

Mysql> SELECT @;

+ --------- +

| @ A |

+ --------- +

| U_PI6 $4 |

+ --------- +

1 row in set (0.00 sec)

Mysql> call pro_addmm ('zhouys ', @ );

Query OK, 0 rows affected (0.00 sec)

Mysql> SELECT @;

+ --------- +

| @ 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)

Encryption algorithms have several weaknesses:

1. Case Insensitive

2. Chinese characters are not supported

3. Insufficient encryption strength

I have explained in detail how to write the mysql5 stored procedure in the above article. I hope you can learn it carefully and learn the knowledge you need from it. I hope this will help you.