The simplest data extraction in history

In history, the simplest data extraction is to be a full-time DBA. In my previous work, I often received such a requirement, I need to work with the developer to regularly extract the data they need from several target tables and store the data in the temporary table. The Developer then extracts the data from the temporary table and presents it to the front-end page. When receiving such a requirement, I think the following points should be taken into account: (1) the target table has

In history, the simplest data extraction is to be a full-time DBA. In my previous work, I often received such a requirement, I need to work with the developer to regularly extract the data they need from several target tables and store the data in the temporary table. The Developer then extracts the data from the temporary table and presents it to the front-end page. When receiving such a requirement, I think the following points should be taken into account: (1) the target table has

The simplest data extraction in history

As a full-time DBA, I often receive such requirements in my previous work. I need to work with developers to regularly extract the data they need from several target tables and store the data in temporary tables, the developers then extract data from the temporary table and present it to the front-end page.

When receiving such a request, I think we should consider the following:

(1) The number of target tables and their associations determine how to retrieve data.

(2) Regular extraction means periodic data extraction. What is the extraction cycle? How often is data extracted?

(3) For periodic data extraction, the name of the temporary table must be named in a regular way. The naming rule is "temporary table name_datetime", which facilitates the development of frontend data extraction.

(4) When periodically extracting data, you must consider retaining and cleaning temporary tables to prevent high table space usage.

(5) If data extraction takes a long time, you need to monitor the data extraction progress.

(6) If a problem occurs during data extraction, You can reuse the data extraction process.

That is to say, the dirty data extracted before this operation should be cleared when you perform the pumping data storage process for 2nd times.

So what is data extraction?

The simplest explanation is to extract the data of interest from the raw data.

For the six points I have summarized above, we started to simulate a set of simplest data extraction cases.

(1) Table Structure and field description

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210132050-0.jpg "style =" float: none; "title =" department table .png "alt =" wKiom1WOSSaSw7E1AAKe9uJmSkA271.jpg "/>

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210131Y7-1.jpg "style =" float: none; "title =" Employee table .png "alt =" wKioL1WOSubDwyk4AAN2ODh7XpU924.jpg "/>

(2) simulate business needs

(1) extracted from the emp and dept tables: emp. empno, emp. ename, emp. job, emp. deptno, dept. dname, dept. the loc and sysdate fields are used to construct the associations between the temporary tables T_EMP_DEPT (2), emp, and dept: The deptno field of the emp table references the deptno field of the dept Table (3) the developer queries the temporary tables generated on the current day at every day (4). the temporary tables generated each time are retained for 30 days. The temporary tables generated over 30 days can be cleared.

(3) provide solutions

(1) query the required fields from the Association of emp and dept tables. The association field is deptno, and a temporary table is created: T_EMP_DEPT (2), which is extracted once a day, we can create a temporary table before every day. (3) Naming rule for the temporary table: T_EMP_DEPT_yyyymmdd (take the year, month, and day of the current system) (4) in the stored process of data extraction, add logical judgment, take the current system time, and clear the temporary table 30 days ago (truncate first, then drop) (5) write a separate stored procedure and table to save the progress of each data extraction, so that we can monitor the data extraction progress (6). During the data extraction storage process, truncate temporary table, drop temporary table, and then extract the data. Reuse Data Extraction

Now that we're all clear, let's get started.

1. Create a log table (storage extraction progress)

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210133505-2.jpg "title =" .png "alt =" wKioL1WOS2GjvszPAAUrB8R9cvE643.jpg "/>

2. Create a storage process for monitoring progress

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210134045-3.jpg "title =" monitoring extraction schedule storage process. PNG "alt =" wKiom1WOSdfCkIcaAASKc1-toA4015.jpg "/>

3. Create a stored procedure for Data Extraction

Create or replace procedure prc_emp_dept authid current_user is table_name_1 varchar2 (100); -- temporary table name table_flag number; -- identify whether a temporary table exists 0: nonexistent 1: create_ SQL varchar2 (5000 ); -- SQL statement insert_ SQL varchar2 (5000) for creating a temporary table; -- Insert SQL statement date_30 varchar2 (20); -- date_cur varchar2 (20) 30 days before data expiration ); -- current date log_detail varchar2 (4000); -- log details parameter begin date_cur: = to_char (sysdate, 'yyyymmdd'); -- current date date_30: = to_char (Sysdate-30, 'yyyymmdd'); -- date earlier than 30 days table_flag: = 0; -- Initial State 0, the target does not exist table_name_1: = 't_emp_dept '| date_cur; --- if a temporary table exists, clear the table first and then kill the table (for function reuse) execute immediate 'select count (*) from user_tables where table_name = ''' | table_name_1 | ''' into table_flag; if table_flag = 1 then execute immediate 'truncate table' | table_name_1; execute immediate 'drop table' | table_name_1; -- log details log_detail: = 'time when a temporary table is deleted: '| To_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept', '00', log_detail ); end if; --- create intermediate table T_EMP_DEPTyyyymmdd create_ SQL: = 'create table' | table_name_1 | 'nologging as select e. EMPNO, e. ENAME, e. JOB, e. MGR, e. HIREDATE, e. SAL, e. COMM, e. DEPTNO, d. DNAME, d. LOC, sysdate as current_time from emp e, dept d where e. deptno = d. deptno'; execute immediate create_ SQL; -- log details log _ Detail: = 'intermediate table creation completion time: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '01', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = '1st: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss '); p_INSERT_LOG (sysdate, 'prc _ emp_dept ', '02', log_detail); insert_ SQL: = 'insert Into '| table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = '2nd: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept', '03', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of 3rd data inserts into the middle table: '| t O_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '04', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of 4th data inserts into the middle table: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '05', log_detail); insert_ SQL: = 'insert into' | table_name_1 |' Select * from '| table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = '5th :' | to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '06', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of 6th data inserts into the middle table: '| to_char (sysdate, 'yyyy-mm-d D hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '07', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of 7th data inserts into the middle table: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '08', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_n Ame_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of data insertion to the intermediate table: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '09', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of 9th data inserts into the middle table: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_L OG (sysdate, 'prc _ emp_dept ', '10', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = '10th: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '11', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate Insert_ SQL; commit; -- log details log_detail: = '11th: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss '); P_INSERT_LOG (sysdate, 'prc _ emp_dept', '12', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of 12th data inserts into the middle table: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dep T', '13', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = '13th: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss '); p_INSERT_LOG (sysdate, 'prc _ emp_dept ', '14', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- day Log_detail: = '14th: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss '); p_INSERT_LOG (sysdate, 'prc _ emp_dept ', '15', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = '15th: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '16', log_detail); in Sert_ SQL: = 'insert' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of 16th data inserts into the middle table: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '17', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = '100 Completion Time of data insertion to the intermediate table 7 times: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '18', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = '18th: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss '); p_INSERT_LOG (sysdate, 'prc _ emp_dept ', '19', log_detail); insert_ SQL: = 'insert '| Table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: =' 19th data insertion completion time to the intermediate table: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept', '20', log_detail); insert_ SQL: = 'insert into' | table_name_1 | 'select * from' | table_name_1; execute immediate insert_ SQL; commit; -- log details log_detail: = 'completion time of 20th data inserts into the middle table: '| to_c Har (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '21', log_detail); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '22', 'Data extraction is complete! '); --- Delete the temporary table execute immediate 30 days ago 'select count (*) from user_TABLES where table_name ='t _ EMP_DEPT '| date_30 | ''' into table_flag; if table_flag = 1 then -- find the table execute immediate 'truncate table T_EMP_DEPT '30 days ago | date_30; execute immediate 'drop table T_EMP_DEPT' | date_30; log_detail: = 'completion time of temporary table deletion 30 days ago: '| to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss'); P_INSERT_LOG (sysdate, 'prc _ emp_dept ', '23', log_detail); end if; end prc_emp_dept ;/

4. Use sys User display to authorize scott to prevent dbms_job from failing to call the Stored Procedure

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210134338-4.jpg "title =" scoscott.jpg "alt =" wKioL1WOTAKRpoGZAAHXpVeEOVM888.jpg "/>

5. view the monitoring log table (there is nothing in the new table)

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210132P5-5.jpg "title =" log table not recorded .png "alt =" wKiom1WOSmbw-b2JAAJxMjK9lKg042.jpg "/>

6. Manually extract the data stored procedure

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210133927-6.jpg "title =" manual execution storage process .png "alt =" wKiom1WOSpHg2MX3AAJZtn0JhQs370.jpg "/>

7. view the monitoring log table (detailed records)

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/121013K41-7.jpg "title =" log table recorded .png "alt =" wKioL1WOTGviF8ReABDY6ps0lqU349.jpg "/>

Dear friends, our data extraction function, monitoring and extraction progress, and business needs are basically complete.

Currently, the only difference is to set the data extraction stored procedure to a scheduled task and then periodically execute the task.

There are two common scheduled tasks:

A. crontab (operating system level)

B. dbms_job (included in oracle)

Add our data extraction and storage process to a scheduled task so that it can be periodically executed.

8. I am using dbms_job. For details, see

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1210131932-8.jpg "title =" Timing task .png "alt =" wKiom1WOSySDpeRVAAVlYlJyX0c416.jpg "/>

Now, my friends, it's time to automatically extract the simplest data in history!

This article covers the following knowledge points:

1. SQL multi-table joint Query

2. Business analysis and consideration

3. automatic data extraction

4. Data Extraction progress monitoring

5. scheduled tasks

6. Table Management

Conclusion:

I believe you have discovered that the Operation user used in this article is scott, who is a basic exercise user in oracle.

This article is a small case of my class. My students have a good response and they say it is easier to accept it.

So I transplanted this small case to the 51 blog and shared it with more friends who needed it!

In fact, everyone has procrastination and is afraid to try new things. So I want to talk about this article:

To friends who want to extract data,

Sent to friends interested in data processing,

For those who want to learn about oracle development,

To friends who want to avoid synchronization,

Send to friends who want to learn

Appendix: the code in this article has been manually tested. If a friend finds an error during the operation, check the code carefully.