mysql stored procedures and usage scenarios

Posted by colossus_09 on Sat, 19 Feb 2022 20:14:31 +0100

1, What is a stored procedure

Simply put, it is a set of SQL statements with powerful functions. It can realize some complex logic functions, which is a bit similar to a function of an application.

Stored procedures are similar to triggers. They are a set of SQL sets, but stored procedures are called actively, and their functions are more powerful than triggers. Triggers are called automatically after something is triggered;

2, Stored procedure characteristics

There are input and output parameters, variables can be declared, if/else, case,while and other control statements. Complex logic functions can be realized by writing stored procedures;

General characteristics of functions: modularity, encapsulation, code reuse;

Fast speed, only the first execution needs to go through the compilation and optimization steps, and the subsequent calls can be executed directly, eliminating the above steps;

Based on the above characteristics, some modules with high performance requirements and complex business can be written to the stored procedure and directly called by the application layer.

3, Create simple stored procedures

3.1 basic grammar

CREATE PROCEDURE pro_now() -- Stored procedure name, custom
BEGIN -- Start stored procedure
       # The set of sql statements that need to perform operations. CRUD operations can be performed on data tables
       -- insert some sql here

end; -- End stored procedure

3.2. Create a stored procedure to query the current time:

CREATE  PROCEDURE pro_now() 
BEGIN
    SELECT now();
END;

3.3. Call stored procedure: call pro_now(); – Call keyword, "pro_now()" stored procedure name

3.4. View the created stored procedure: Show procedure status [where name = "pro_now];

3.5. Delete stored procedure: DROP PROCEDURE pro_now; – "pro_now" stored procedure name

4, Create a stored procedure with parameters

4.1. For stored procedures with input parameters, use the keyword: in

a,Examples are as follows:

CREATE PROCEDURE pro_now_in(in time  VARCHAR(20) CHARACTER set "utf8")	
        -- CHARACTER set "utf8",Set the character set to solve Chinese garbled code
BEGIN
	 SELECT now(),time;
end;

b. Call stored procedure:

  set @time='current time ';
  call pro_now_in(@time); --  call pro_now_in('current time '); That's OK

c. The results are as follows:

4.2 for stored procedures with output parameters, use the keyword: out

a. Examples are as follows:

CREATE PROCEDURE pro_now_out(out time  VARCHAR(20),out title VARCHAR(20) CHARACTER set utf8)
BEGIN
	 SELECT now(),'current time ' into time , title;
end;

b. Call stored procedure:

call pro_now_out(@times,@title);
SELECT @title AS "title",@times AS "time";

c. Results:

4.3. For stored procedures with input and output parameters, use the keyword: inout

a. Examples are as follows:

CREATE PROCEDURE pro_now_inout(inout name VARCHAR(20),in title VARCHAR(10), out time VARCHAR(10))
BEGIN
	 SELECT CONCAT(name,'<--->',title) AS name,now() into name,time;
end;

b. Call stored procedure:

set @name='jack';
set @title='toady';
call pro_now_inout(@name,@title,@time);
select @name as 'name and title',@time as 'time';

c. Results:

d. Understanding:

in (input): name, title
out: name, time
CONCAT(name, '< - >', title) string splicing, corresponding to name output and now() corresponding to time output.

5, Create stored procedure with control flow

5.1 if statement
a. Examples are as follows:

 CREATE PROCEDURE pro_if(in num INT)
     BEGIN
	    DECLARE result VARCHAR(20) CHARACTER set  utf8 DEFAULT null;
	    IF num = 0 THEN  -- start if Judge with an equal sign"="
	        set result='num It's 0'; -- Meet the conditions
	    ELSEIF num > 0 THEN -- next if judge
		set result='num Greater than 0';
  	    ELSEIF num < 0 THEN
		set result='num Less than 0';
	    ELSE -- When all conditions are not met
		set result='num is null or other status';
	    end if; -- end if judge 
	    SELECT result;
 end;

b. Call stored procedure:

 call pro_if('33');

c. The results are as follows:
,
5.2 case statement

a. Examples are as follows:

     CREATE PROCEDURE pro_case(in num INT)
BEGIN
	DECLARE result VARCHAR(20) CHARACTER set  utf8 DEFAULT null;
	case num  -- start case judge
	when  2 THEN  -- Execute if conditions are met
		set result='num The value is 2';
 	 when -2 THEN  
		set result='num Value is-2';
	else  -- If all conditions are not met, execute
		set result='num Not equal to 2 and-2';
	end case ; -- end case sentence
	SELECT result;
end;

b. Call stored procedure:

call pro_case(-2);

c. The results are as follows:

5.3. while loop statement

a,Examples are as follows:

CREATE PROCEDURE pro_while(in num INT)
BEGIN
	DECLARE i int;
	DECLARE result int;
	set i=0;
	set result=0;
	while i < num DO -- start while loop
		set result=result+i;
		set i=i+1;
	end while; -- end while loop 
	SELECT result,i;
end;

b. Call stored procedure:

call pro_while(100);

c. The results are as follows:

6, Create a stored procedure with a cursor loop

1. Examples are as follows:

CREATE PROCEDURE pro_cursor(out count int)
BEGIN
	declare  paper_id  VARCHAR(1000) ; -- Thesis primary key id
	declare doctroName VARCHAR(1000) character set gbk; -- Name of doctor
	DECLARE paper_hos VARCHAR(1000); -- hospital id
	DECLARE paper_room      VARCHAR(100); -- Doctor profession
	declare done int DEFAULT false ; -- Custom control cursor loop variable,default false
	DECLARE  my_cursor CURSOR for (SELECT id,authorName,hospitalId,room
							from yee_article_paper_authorid ); -- Define cursor and enter result set  
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; -- Bind control variable to cursor,Automatic rotation at the end of cursor cycle true 
	OPEN my_cursor; -- Open cursor
	myLoop:LOOP -- Start cycle,myLoop Name a custom loop,Used at the end of the cycle  
	FETCH my_cursor into paper_id,doctroName,paper_hos,paper_room ;  -- Assign the data order of the current row read by the cursor to the user-defined variable 12  
	if done THEN -- Determine whether to continue the cycle  
		LEAVE myLoop;-- End of cycle
	END IF;
	 -- What you want to do,stay sql You can directly use custom variables in  
	insert into temp(str_id,name,hospitalId,room) 	
VALUES(paper_id,doctroName,paper_hos,paper_room);
	COMMIT; -- Commit transaction
  END  LOOP myLoop; -- End custom loop body
	CLOSE my_cursor; -- Close cursor
	# After the cycle ends, count the number of imports
	SELECT count(id)  count from temp into count; -- Calculate the number
end

Cursor is a method of processing data. In order to view or process the data in the result set, the cursor provides the ability to browse the data forward or backward one or more rows at a time in the result set.

7, Spring mvc fetching stored procedure

The SimpleJdbcCall class can be used to call a stored procedure containing IN and OUT parameters. You can use this method when dealing with any RDBMS, such as Apache Derby, DB2, MySQL, Microsoft SQL Server, Oracle, and Sybase.

7. 1 create table:

CREATE TABLE ssers(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

7.2 creating stored procedures:

DELIMITER $$
DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id     INTEGER,
OUT out_name VARCHAR(20)  CHARACTER set "utf8",
OUT out_age  INTEGER
)
BEGIN
   SELECT name, age
   INTO out_name,out_age
   FROM users where id = in_id;
END $$
DELIMITER ;

delimiter is a command in MySQL, which has nothing to do with stored procedures.
In fact, it tells the mysql interpreter whether the command has ended and whether mysql can be executed.
That is, change the input terminator.
By default, delimiter is a semicolon ";".

7.3 dao layer calling stored procedure

Public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate myJdbc;

    @Autowired
    private DataSource dataSource;


    private UserModel  userModel;

    @Override
    public UserModel getUser(int id){

        //Create a JDBC call object
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");

        //Call stored procedure
        SqlParameterSource in   = new MapSqlParameterSource().addValue("in_id", id);
        Map out = jdbcCall.execute(in);

        UserModel user = new UserModel(0,null,0);

        user.setId(id);
        user.setName((String) out.get("out_name"));
        user.setAge((Integer) out.get("out_age"));
        return user;

        //Common sql query
        //String SQL     = "select * from users id = ?";
        //UserModel user = myJdbc.queryForObject(SQL,new Object[]{id}, new UserMapper());
        //return user;

    }

}

8, Disadvantages of stored procedure

Different databases have very different syntax and are difficult to transplant. If you change the database, you need to rewrite it;

It is not easy to manage. It is difficult to maintain too much business logic in the storage process, which is not conducive to hierarchical management and easy to be confused. The general storage process is suitable for individual businesses with high performance requirements, and other necessity is not great;

Reference link:

https://blog.csdn.net/HaHa_Sir/article/details/79728854

https://www.cnblogs.com/chenpi/p/5136483.html

https://www.w3cschool.cn/wkspring/3yh61mmc.html

Topics: MySQL

Programmer Think

mysql stored procedures and usage scenarios

Hot Topics