MySQL stored procedures

The difference between MySQL stored procedures and MSSQL

1. MSSQL 's keyword procedure can be abbreviated as proc, while MySQL cannot;

mysql parameter to declare is in out or inout mssql no;

3. MySQL calls the stored procedure with call, andMSSQL uses EXEC;

4. MySQL has output parameters to use the variable @ to assign value and query;

keyword parameters, pagingExample 1:

Mysql> CREATE PROCEDURE cs_ro_getquestionlist-in B int,--start position, in L int,--number in nvarchar (100)--keyword---select Id,title from w_question where locate (kw,title) >0 Limi    T b,l; -End;mysql> Call Cs_ro_getquestionlist (0,10, ' China ');

contains input and output parametersExample 2:

Mysql> CREATE PROCEDURE cs_ro_getquestionlist-in B int,--start position, in L int,--number in KW nvarchar (1),--keywords--out T int--Total returns)---    n where locate (Kw,title) >0;    Select Id,title from w_question where locate (kw,title) >0 limit b,l; -End;mysql> Call Cs_ro_getquestionlist (0,10, ' China ', @a);mysql> select @a;

Transaction ProcessingExample 3:

mysql> create procedure cs_px_addclass    ->  (     -> in nid int,    -> in nname nvarchar (20),     -> in nQuestionNum int,    -> in  Npx int,    -> in npycode nvarchar (    ->)  )     -> begin    -> DECLARE t_error  Integer default 0;    -> declare continue handler for  SQLEXCEPTION SET t_error=1;    -> START TRANSACTION;--  Transaction start     -> insert into w_questionclass (Id,name,questionnum,px,pycode)     -> VALUES  (Nid,nname,nquestionnum,npx,npycode);     - > if t_error = 1 then--  rollback if it fails     -> ROLLBACK;    ->  ELSE    -> COMMIT;--  successful execution, end of transaction     -> end  IF;    -> select t_error;--  returns the execution state of the transaction to the person being tuned      -> end;mysql> call cs_px_addclass (1, ' Test ', ' n ', ' CS ');

+--------+

| t_error|

+--------+

| 1 |

+--------+

This article is from the "Sukun" blog, make sure to keep this source http://sukunwu.blog.51cto.com/10453116/1688340

MySQL stored procedures