I have never used a MySQL stored procedure before. I am not used to writing many MySQL stored procedures for the first time. The following is a summary:
The following is a simple MySQL stored procedure.
Delimiter $ create procedure proc_add (in a int, in B INT) Begin declare C int; if A is null then set a = 0; end if; if B is null then set B = 0; end if; set C = a + B; select C; end $ delimiter;
Note that
1. The declare statement can only be placed at the beginning of the stored procedure, and an error will be reported after it is placed.
2. If statements must be followed by then, but do not require in. end if
3. It is the same as MSSQL to determine whether it is null.
4. delimiter refers to the delimiter, which means that the delimiter must be added after the end.
5. end if must be followed by a semicolon; otherwise, the syntax is incorrect.
The following is a common scenario: to determine whether a column in a table has a value, if an operation exists
Delimiter $ create procedure proc_add_book (in $ bookname varchar (200), in $ price float) Begin declare $ existsflag int default 0; select bookid into $ existsflag from book where bookname = $ bookname limit 1; if bookid> 0 then # if not exists (select * from book where booknumber = $ bookname) then insert into book (booknumber, price) values ($ bookname, $ price); end if; end $ delimiter;
It should be noted that if exists cannot be used; exists can be used after where or in create object, but it cannot be used in If statement, and only a work und can be used.
The while statement also needs to be noted that the following is a simple application of while:
Delimiter $ create procedure proc_add_books_looply (in $ bookname varchar (200), in $ price float, in $ inserttimes INT) Begin while $ inserttimes> 0 do insert into book (bookname, price) values ($ bookname, $ price); end while; end $ delimiter;
We can see that while is followed by a condition, and the condition is followed by a DO statement. After the while loop body ends, end while and end with a semicolon.
The above are some simple summaries and hope to be useful.