Learning Pain (Database-and stored procedures and functions)

Stored procedures and functions as part of the database, why the pain of learning.

Projects are actually developed, taking into account performance and code maintenance, absolutely no stored procedures.

If you simply write a small program to fool people to play, but also write.

Learning

Define a collection of SQL statements in the database, and then call these stored procedures and functions directly to execute the SQL statements that have already been defined.
Avoid developers writing the same SQL statements repeatedly.
Storage and execution in MySQL server can reduce the data transfer between client and service side.

Delimiter &&create procedure Pro_book (in BT int,out count_num int.) reads SQL Databeginselect count (*) from T_book where Booktypeid=bt;end&&delimiter; call Pro_book (1, @total);

Function

Delimiter &&create function Func_book (bookId int) returns varchar Beginreturn (select BookName from T_book where id=bookid); end&&delimiter; select Func_book (1);

　　

Use of variables
Delimiter &&
CREATE PROCEDURE Pro_user ()
Begin
Declare a, B varchar (20);
INSERT into T_user values (NULL,A,B);
End
&&
delimiter;

Call Pro_user ();

Assigning a value to a variable
Delimiter &&
CREATE PROCEDURE Pro_user2 ()
Begin
Declare a, B varchar (20);
Set a= ' jjj ', b= ' 888 ';
INSERT into T_user values (NULL,A,B);
End
&&
delimiter;

Call Pro_user2 ();

Delimiter &&
CREATE PROCEDURE Pro_user3 ()
Begin
Declare a, B varchar (20);
Select Name,pass into a b from T_user2 where id=1;
INSERT into T_user values (NULL,A,B);
End
&&
delimiter;

Call Pro_user3 ();

Use of Cursors
Delimiter &&
CREATE PROCEDURE Pro_user4 ()
Begin
Declare a, B varchar (20);
Declare cur_t_user2 cursor FOR select Name,pass from T_user2;
Open cur_t_user2;
Fetch Cur_t_user2 into a A, b;
INSERT into T_user values (NULL,A,B);
Close Cur_t_user2;
End
&&
delimiter;

Call Pro_user4 ();

Use of Process Control
Process control can be used in stored procedures and functions. MySQL uses if, case, loop, leave, iterate, repeat, while to control.

If statement
Delimiter &&
CREATE PROCEDURE Pro_user5 (in n int)
Begin
Select COUNT (*) into @num from T_user2 where id=n;
If @num >0 then update t_user2 set name= ' yyy ', pass= ' 555 ' where id=n;
Else insert into t_user2 values (NULL, ' Java ', ' 1234 ');
End If;
End
&&
delimiter;

Call PRO_USER5 (1);
Call PRO_USER5 (3);

Case statement

STOP!!!

STOP!!!

STOP!!!

All right, that's it! Learn the knowledge of the actual application is not!!!

Learning Pain (Database-and stored procedures and functions)