1. cursor

Using cursors to solve the problem that sql statements can't handle row records, we want to be a traversal array in java.

BEGIN
    DECLARE river_id VARCHAR(32); -- Custom variable
        DECLARE river_name VARCHAR(50); -- Custom variable
    DECLARE done INT DEFAULT FALSE; -- Traverse data end flag pay attention to position order
    DECLARE my_cursor CURSOR FOR (SELECT rs.id,rs.river_stage_name from r_river_stage rs
            LEFT JOIN t_s_depart d on d.id = rs.depart_id where d.org_code like CONCAT('A01A06A03','%'));-- Bind control variable to cursor,Cursor cycle end autorotation true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


    OPEN my_cursor; -- Open cursor
        while done != true do
                FETCH my_cursor into river_id,river_name;
                set result1 = CONCAT_WS(',',result1,river_id);
                set result2 = CONCAT_WS(',',result2,river_name);
        end while;
    CLOSE my_cursor;
    select result1;
    seelct result2;
END

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(out result text)
BEGIN
    DECLARE river_id VARCHAR(32); -- Custom variable
    DECLARE done INT DEFAULT FALSE; -- Traverse data end flag pay attention to position order
    DECLARE my_cursor CURSOR FOR (SELECT rs.id from r_river_stage rs
            LEFT JOIN t_s_depart d on d.id = rs.depart_id where d.org_code like CONCAT('A01A06A03','%'));-- Bind control variable to cursor,Cursor cycle end autorotation true
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


    OPEN my_cursor; -- Open cursor
        while done != true do
                FETCH my_cursor into river_id;-- Get data from cursor pointer
                set result = CONCAT_WS(',',result,river_id);
        end while;
    CLOSE my_cursor;
    select result;
END

2. Storage function

Unlike stored procedures, output parameter (OUT) and input output parameter (INOUT) types cannot be specified IN a stored function. S torage functions can only specify input types and cannot have IN. At the same time, the store function can RETURN the processed result to the caller through the RETURN command. Note that the RETURN value type must be specified IN advance IN the RETURNS command after the parameter list. Create a function to calculate the Fibonacci sequence as follows

CREATE FUNCTION `fn_count`(`num` int) RETURNS int(11)
BEGIN
    DECLARE result int DEFAULT 1;
    while num > 1 DO
        set result = result * num;
        set num = num - 1;
    end WHILE;
    RETURN result;
END

In general, function names begin with fn, and stored procedures begin with sp. stored procedures can contain functions, but not vice versa.

As for the difference between stored procedure and stored function, the following points are mainly given.

1. A stored procedure can have multiple in,out,inout parameters, while a stored function has only input parameter types and cannot have in
2. The functions implemented by stored procedures are more complex, and the single functions of stored functions are more specific.
3. A stored procedure can return multiple values; a stored function can only have one return value.
4. Stored procedures are usually executed independently, and stored functions can appear as part of other SQL statements.
5. A stored procedure can call a storage function. But a function cannot call a stored procedure.

3, view

Same as creating a table, the name of the created view must be unique

There is no limit to the number of views created. However, if a view is nested or associated with too many tables, it will also cause performance problems. When deploying in the actual production phase, it is necessary to conduct necessary performance testing.

When filtering condition data, if there is a where condition statement in the sql statement that creates the view, and there is a where condition statement in the statement that uses the view, the two where condition statements will be combined automatically.

Order by can be used in a view, but if the select statement that retrieves data from the view also contains order by, the order by in the view will be overwritten.

Index and trigger cannot be used in view

Use can be used with common tables. It is allowed to edit an sql statement that joins a view and a common table.

CREATE VIEW view view name (column name,...) AS SELECT statement

4. Trigger

I'll summarize it later...