Small white the newbies, pure technology to give directions for counsel,

The DDW database has more than 20 data table (simple point, hypothesis is a, b, c, d, e, five tables, each table field is different), the newly created database DDW_HIS, I built the table inside, exactly the same as the table two libraries are just the latter without the data, now I was asked to write a stored procedure, will the data of the former to the latter, to achieve the effect of the backup data,

Want to directly with Navicat tools at the beginning, or write a stored procedure, but not put forward after the somebody else want to effect, this is not, the way he knows, he want... And mention to let me study the cursor, Internet to insert example, feel almost meaning, but also directly is the statement not stored procedure,

Should write SQL experience is too little, I am too food, now a little meng, strives for the great god, it is best to write directly, so I can more direct understanding of the...

CodePudding user response:

The cursor? First heard asked to use vernier, was also a partial?
Directly using the dynamic statement cycle table name, table name can be in sys. Objects can be inside out,

CodePudding user response:

Er... Anyway, is to complete his request,

Table name me is copied into the blank document one by one in use at any time, sys. Objects that are used, for example, I want to see the DDW database table name, how should search?

CodePudding user response:

Feeling, he wants is the stored procedure after long-term to DDW to do data backup, so the one-time bottom, according to the way he doesn't want to the stored procedure should be how to write?

CodePudding user response:

Give you write a pseudo code, specific procedures or write yourself

(1) the cursor definition: the query select table_name from information_schema. The tables where table_schema='DDW'; - to obtain the original library
(2) using dynamic SQL stitching line:
Hypothesis: the cursor in front of the variable named table_name, SQL variables to store dynamic SQL
SQL=concat (' truncate table DDW_HIS. ', table_name, '; ') - backup before, delete the data in the backup library with watches, avoid conflict caused by primary key, the key problems such as conflict (if you want to increment has been backup, it's a little bit of a problem, you need to check the system tables to obtain primary key/unique key and then insert this association, here I can only give you write down to empty the backup table data before every backup ideas)
The exec (SQL), execute the SQL
SQL=concat (' insert into DDW_HIS. ', table_name, 'select * from DDW.', table_name, '; ')
The exec (SQL), execute the SQL
(3) and the stored procedure, use the event to be a regular task, or you make the code into applications, interface use

CodePudding user response:

reference 1st floor shoppo0505 response:

cursor? First heard asked to use vernier, was also a partial?
Directly using the dynamic statement cycle table name, the name of the table can in sys. Objects can be inside out,

Dynamic statement cycle table name, which the cursor

CodePudding user response:

Refer to this, as long as the cursor statement and the statement of change in can


The BEGIN
Declare v_varchars varchar (200);
Declare v_varchars2 varchar (200);
Declare v_varchars3 varchar (200);
Declare the done int.

Declare cursor_tab cursor for the select TABLE_SCHEMA, TABLE_NAME from information_schema. The tables where table_type='BASE TABLE AND TABLE_SCHEMA not in (' performance_schema', 'mysql', 'information_schema', 'activemq) order by TABLE_SCHEMA, TABLE_NAME;
DECLARE the CONTINUE HANDLER FOR the NOT FOUND SET done=1;

The set @ now=now ();

The open cursor_tab;
PosLoop: LOOP
The FETCH cursor_tab into v_schema v_tbname;

IF done=1 THEN
LEAVE posLoop;
END IF;
# set @ strSql='select' 'database:' | | v_schema | | ', the table name: '| | v_tbname | |', the amount of data: ' '| | the count (*) from' | | v_schema | | '. '| | v_tbname;
The set @ strSql='select count (*) into @ v_cnt from' | | v_schema | | '. '| | v_tbname;
PREPARE STMT FROM @ strSql;
The EXECUTE STMT.
DEALLOCATE PREPARE STMT.

INSERT into stats_tab_rows values (v_schema v_tbname, @ v_cnt, @ now);

END LOOP posLoop;
The CLOSE cursor_tab;

END