How to get a program to insert a piece of data every few seconds in PL/SQL

In writing Oracle PL/SQL, if you need to pause the program execution for a few seconds before proceeding, you can do so by using Oracle's built-in Dbms_lock.sleep, but the Dbms_lock package needs to be installed by the user.

[Email protected] ~]# su-oracle

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.4.0 Production on Mon 25 16:36:12 2015

Copyright (c) 1982, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the partitioning, OLAP, Data Mining and Real application testing options

Sql> Startup

Sql> @?/rdbms/admin/dbmslock.sql

Package created.

Synonym created.

Grant succeeded.

Sql> Grant execute on dbms_lock to public; --Authorize public to execute permissions

Grant succeeded.

Sql> CREATE TABLE test1 (ID number,name varchar2 (+), time date); --Create test1 temporary table

Table created.

Sql> select * from Test1; --No data

No rows selected

Sql> SET TIMING on--Open time display

Sql> Begin--Start execution of test scripts

2 INSERT INTO test1 (id,name,time) VALUES (1, ' Andy ', sysdate);

3 Dbms_lock.  SLEEP (10); --Let the program temporarily for 10 seconds

4 INSERT INTO test1 (id,name,time) VALUES (2, ' Shirley ', sysdate);

5 commit;

6 end;

7/

PL/SQL procedure successfully completed.

elapsed:00:00:10.04--Program execution time is 10.04 seconds

Sql> SELECT Id,name,to_char (Time, ' Yyyy/mm/dd HH24:MI:SS ') as time from TEST1;

--Query execution results

ID NAME Time

---------- -------------------------   -----------------

1 Andy 2014/12/10 10:09:03--The insertion time of the first article is 09:03

2 Shirley 2014/12/10 10:09:13--The insertion time of the second article is 09:13

Just 10 seconds later than the first one.

sql> drop table test1;

Table dropped.

Here's an example:

Insert a single piece of data every second

vi/tmp/11.sh

#/bin/sh

Su-oracle <<eof

Sqlplus/as SYSDBA <<eof

drop table test;

Drop sequence test_seq;

CREATE TABLE Test (ID int,hostname varchar2 (), datetime date);

Create sequence Test_seq

MinValue 1

MaxValue 100000

Start with 1

Increment by 1

Cache 20;

Declare

MAXRECORDS constant int:=100000;

I int: = 1;

Begin

For I in 1..maxrecords loop

INSERT INTO Test

(Id,hostname,datetime)

Values

(Test_seq.nextval, ' ORACLEVM ', sysdate);

Commit

Dbms_lock.sleep (1);

End Loop;

End

/

Exit

Eof

[[Email protected] ~] #chmod 755/tmp/11.sh

[[Email protected] ~] #cd/tmp

[Email protected] ~]#./11.sh

can be executed.

Set the time format first

Export nls_date_format= "Yyyy-mm-dd HH24:MI:SS"

Finally, the data query

Spool/tmp/test_oracle

Set Linesize 80

Col hostname format A8;

Set COLSEP ";

Set pagesize 0;

Select To_char (DateTime, ' Yyyy-mm-dd HH24:MI:SS ') from the test where hostname= ' hostname ' ORDER by ID;

Spool usage:

Spool is a command under Oracle Sql*plus that can be used to export a large amount of data in a table, and the resulting format can be manually controlled by itself.

1. Write the spool.sql script, stored in the/tmp directory

Set Trimspool on
Set Linesize 120
Set PageSize 2000
Set NewPage 1
Set Heading off
Set term off
Set echo off
Set Feedback off
Spool/tmp/data.txt
Select Deptno | | ', ' | | Dname | | ', ' | | Loc from Dept;
Spool off

2. Log in with the Scott user on Sql*plus and execute the SQL script above

!/tmp/spool.sql (under Linux)

@d:\spool.sql (under Windows)

3. Observe the corresponding directory, generate the Data.txt file, txt content is as follows:

10,accounting,new YORK
20,research,dallas
30,sales,chicago
40,operations,boston

Summarize:

With spool, you can easily export the tables you need, and you can use the exported data to migrate data between different databases.

Here are some common settings for using spool:

Set COLSEP "; Field Output delimiter
Set echo off; Displays each SQL command in the script started by the start, which defaults to on
Set feedback off; echo the number of record bars processed by this SQL command, default to ON
Set heading off; Output field header, default to On
Set pagesize 0; Output number of rows per page, default is 24, in order to avoid paging, can be set to 0.
Set Termout off; Displays the execution result of the command in the script, which is on by default (can be abbreviated as term)
Set trimout on; Remove trailing spaces per line of standard output, default to Off
Set Trimspool on; Remove redirect (spool) output trailing spaces per line, default to OFF

Attention:

If you execute the above spool.sql command directly in Sql*plus without executing a script, there will be a SQL statement executed in the generated TXT file.

This article is from the "Hollows Jie Sun" blog, be sure to keep this source http://xjsunjie.blog.51cto.com/999372/1655045

How to get a program to insert a piece of data every few seconds in PL/SQL