Introduction to the functions and properties of cursors

The function and attributes of the cursor

The function of the cursor is to traverse the records returned by the query database in order to perform corresponding operations; the cursor has the following attributes:

a. The cursor is read-only, that is, it cannot be updated;

b. The cursor cannot be scrolled, that is, it can only be traversed in one direction, and cannot advance or retreat at will between records. , cannot skip certain records;

c. Avoid updating data on a table with a cursor open.

Implement the function, split the nt_m_gpsdata (3000W+) with a relatively large amount of data into nt_m_gpsdata20170501, nt_m_gpsdata20170502, nt_m_gpsdata20170503, etc. by date

CREATE PROCEDURE `new_procedure` ()

BEGIN

-- Need to define variables to receive cursor data

DECLARE a CHAR(16);

-- Define the name of the new table

DECLARE tbname CHAR(30);

-- Define variables to store sql statements

DECLARE sqlstr1 varchar(300);

DECLARE sqlstr2 varchar(300);

-- Traversing data end flag

DECLARE done INT DEFAULT FALSE;

--Define cursor

DECLARE cur CURSOR FOR select DISTINCT DATE_FORMAT(ctime,'% Y%m%d') as ctime from nt_m_gpsdata;

-- Bind the end flag to the cursor

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

-- Open the cursor

OPEN cur;

-- Start loop

read_loop: LOOP

-- Extract the data in the cursor, there is only one here, The same applies to multiple words;

FETCH cur INTO a;

-- At the end of the statement

IF done THEN

LEAVE read_loop;

END IF;

-- Do the loop events you want to do here

set tbname=CONCAT("nt_m_gpsdata",a);

-- select tbname;

-- Copy the table structure, create table newtable select * from oldtable where 1=2 Only table fields can be copied, and attributes such as field primary key, auto-increment, and non-null cannot be copied create table newtable like oldtable can be copied Field attribute

    set sqlstr1 = CONCAT("create table ",tbname," like nt_m_gpsdata");
        set sqlstr2 = CONCAT("insert into ",tbname," select * from nt_m_gpsdata where deleted=0 and DATE_FORMAT(ctime,'%Y%m%d')='",a,"'");
        set @firstsql = sqlstr1;
        PREPARE stmt1 FROM @firstsql;
        EXECUTE stmt1;
        DEALLOCATE PREPARE stmt1;
        set @secondsql = sqlstr2;
        PREPARE stmt2 FROM @secondsql;
        EXECUTE stmt2;
        DEALLOCATE PREPARE stmt2;

END LOOP;

-- Close the cursor

CLOSE cur;

END

Later, this table was divided The strategy is applied to an oracle project, with code

DECLARE
cursor my_cursors is select DISTINCT to_char(ctime,'yyyymmdd') as ctime from NTGIS_GPS_EVENTDATA;
mcursor varchar2(40);
begin
for mcursor in my_cursors loop
DECLARE
tbname VARCHAR2(50) := 'NTGIS_GPS_EVENTDATA'||mcursor.ctime;
sqlstr VARCHAR2(300) := 'CREATE TABLE '||tbname||' as SELECT * from NTGIS_GPS_EVENTDATA where to_char(ctime,''yyyymmdd'')='''||mcursor.ctime||'''';
BEGIN
--dbms_output.put_line(tbname);
execute immediate sqlstr;
END;
end loop;
end;

attached

The above is the detailed content of Introduction to the functions and properties of cursors. For more information, please follow other related articles on the PHP Chinese website!