This is the 17th day of my participation in the August Challenge

What is a view?

What is a view

A view is a virtual table that does not actually exist and contains no data. It is a virtual aggregation of the data in the corresponding table based on the SELECT statement in the create statement. It is accessed in the same way as a real table.

Syntax for creating views

mysql

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
   [WITH [CASCADED | LOCAL] CHECK OPTION]
Copy the code

oracle

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    [(alias[, alias]...)]

AS subquery
     [WITH CHECK OPTION [CONSTRAINT constraint]]
     [WITH READ ONLY]
Copy the code

It all sums up

create View viewname as select ~
Copy the code

Advantages of views

  1. Simple: For the programmer, this view can be called directly to get the corresponding data, and the corresponding table structure is not concerned with association and filtering criteria
  2. Security: You can set permissions for the user individually, because you can’t restrict permissions for a row or a column in the table, but you can design for the user
  3. Data independence: Once the structure of the view is determined, we can shield the user from changes in the table structure, and the view is unaffected by the addition of columns to the source table.

Disadvantages of Views

  1. Not traceable: Because our view is in the database, changes to the view are not recorded. If a change is made by someone, it cannot be immediately traced back to who made the change, and if you use code concatenation data, every change to the code can be recorded by code management tools!
  2. Modification restrictions: If we want to modify some of the corresponding rows, it becomes very difficult to modify more complex views, such as Group By or Union statements.
  3. Performance penalty: When we use this view, we may not use all of the fields in the view, and sometimes we return a lot of useless data, wasting memory and bandwidth.

What is a cursor?

What is a cursor

Cursors are database queries stored on the MySQL server. Cursors are generally used in stored procedures to perform traversal of result sets retrieved by SELECT statements in stored procedures. It’s kind of like a pointer to a row, you can do something with that row.

The advantages and disadvantages

Advantages: You can do what you want for each row. Disadvantages: Traversal is very time-consuming when the amount of data is too large. Now it is usually traversed by code in the server’s memory.

Steps to use the cursor

Define cursor -> Open cursor -> Use cursor -> Close cursor 1. Define cursor

DECLARE cursor_name CURSOR FOR select_statement;
Copy the code

You can declare multiple cursors after variable and condition declarations. 2. Open the cursor

OPEN cursor_name;
Copy the code

3. Use a cursor

FETCH  cursor_name INTO var_name [, var_name] ...
Copy the code

Take the result one line at a time and store the required data in each defined variable. A row is automatically moved to the next row. 4. Close the cursor

CLOSE cursor_name;
Copy the code

A simple cursor example

DELIMITER $$ 
CREATE PROCEDURE build_title_list (INOUT title_list varchar(4000)) 
BEGIN 
DECLARE v_finished INTEGER DEFAULT 0; 
DECLARE v_title varchar(100) DEFAULT ""; 
-- declare cursor for film title 
DEClARE title_cursor CURSOR FOR SELECT title FROM film; 
-- declare NOT FOUND handler 
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; 
OPEN title_cursor; 
get_title: LOOP 
FETCH title_cursor INTO v_title; 
IF v_finished = 1 THEN 
LEAVE get_title; 
END IF; 
-- build title list 
SET title_list =CONCAT(v_title,";" ,title_list);END LOOP get_title; 
CLOSE title_cursor; 
END$$ 
DELIMITER ;

Copy the code

call

SET @title_list = "";
CALL build_title_list ( @title_list );
SELECT
	@title_list;

Copy the code