My SQL remarks

1. SP writing:

Drop procedure if exists userregister;
Create procedure userregister (
In emailpara varchar (300 ),
In pwdpara varchar (100 ),
In userippara varchar (100 ),
Out outputvalue INT)
Begin
Declare _ err int;
Declare continue handler for sqlexception, sqlwarning, not found SET _ err = 1;
Start transaction;
If exists (select 1 from userinfo where email = emailpara) then
Set outputvalue =-1;
Else
Insert into userinfo (email, PWD, createdate) values (emailpara, pwdpara, now ());
Set outputvalue = last_insert_id ();
Insert into userloginhistory (userid, createdate, userip, issuccess) values (last_insert_id (), now (), userippara, 1 );
End if;
If (_ err = 1) then
Set outputvalue = 0;
Rollback;
Else
Commit;
End if;
End

2. SP call

Call userlogin ('blackpei @ 163.com ', 'hpa7whdmbje =', '192. 0.0.1 ', @ outvalue );

3. SP notes:

The simple syntax for creating a MySQL stored procedure is:

Create procedure stored procedure name () ([in | Out | inout] datatype) Begin MySQL statement; end; if "in", "out", and "inout" are not explicitly specified for MySQL stored procedure parameters, the default value is "in ". Traditionally, we will not explicitly specify the "in" parameter.

1. The "()" after the MySQL stored procedure name is required. Even if there is no parameter, "()" is required.

2. For MySQL stored procedure parameters, you cannot add "@" before the parameter name, for example, int ". The following syntax for creating a stored procedure is incorrect in MySQL (correct in SQL Server ). You do not need to add "@" before the variable name in the MySQL stored procedure, although the mysql client user variable requires "@".

Create procedure pr_add (@ A int, -- error B INT -- correct) 3. The default value cannot be specified for MySQL stored procedure parameters.

4. You do not need to add "as" before procedure body to the MySQL stored procedure ". The SQL Server Stored Procedure must contain the "as" keyword.

Create procedure pr_add (A int, B INT) as -- error. MySQL does not need "as" begin MySQL statement ...; end; 5. if the MySQL Stored Procedure contains multiple MySQL statements, the begin end keyword is required.

Create procedure pr_add (A int, B INT) Begin MySQL Statement 1 ...; mySQL Statement 2 ...; end; 6. add a semicolon (;) to the end of each statement in the MySQL stored procedure.

...

Declare C int;

If a is null then set a = 0; end if;

... End; 7. Notes in the MySQL stored procedure.

/* This is a multi-line MySQL comment. */

Declare C int; -- this is a single-line MySQL comment (Note that there must be at least one space after)

If a is null then # this is also a single-row MySQL comment set a = 0; end if;

... End; 8. The "return" keyword cannot be used in the MySQL stored procedure.

Set c = A + B;

Select C as sum;

/* Return C; -- cannot be used in MySQL stored procedures. Return can only appear in functions. */End; 9. when calling the MySQL stored procedure, you need to add "()" after the process name. Even if there is no parameter, you also need "()"

Call pr_no_param (); 10. because MySQL stored procedure parameters do not have default values, you cannot omit the parameters when calling the MySQL stored procedure. It can be replaced by null.

Call pr_add (10, null );