In PL/SQL, how does one insert a data entry every several seconds?

In PL/SQL, how does one insert a data entry every several seconds?

In compiling Oracle PL/SQL, if you need to pause the execution for a few seconds and continue the execution, you can use dbms_lock.sleep built in Oracle. However, you must install the dbms_lock package yourself.
[Root @ oraclevm ~] # Su-oracle
[Oracle @ oraclevm ~] $ Sqlplus/as sysdba

SQL * Plus: Release 11.2.0.4.0 Production on Mon May 25 16:36:12 2015

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

Connected:
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; -- grant PUBLIC execution permission

Grant succeeded.

SQL> create table test1 (id number, name varchar2 (40), time date); -- create a temporary table named test1

Table created.

SQL> select * from test1; -- no data

No rows selected

SQL> SET TIMING ON -- enable time display
SQL> begin -- start to execute the test script
2 insert into test1 (id, name, time) values (1, 'and', sysdate );
3 DBMS_LOCK.SLEEP (10); -- temporarily set the program to 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 -- the 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 10:09:03 -- The First insert time is
2 Shirley 10:09:13 -- the insertion time of the second entry is
10 seconds later than the first one

SQL> drop table test1;

Table dropped.

The following is an example:
Insert a data record 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 (50), datetime date );
Create sequence test_seq
Minvalue 1
Max value 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

[Root @ oraclevm ~] # Chmod 755/tmp/11.sh
[Root @ oraclevm ~] # Cd/tmp
[Root @ oraclevm ~] #./11.sh
.

Set the time format first.
Export NLS_DATE_FORMAT = "YYYY-MM-DD HH24: MI: SS"

Final 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 test where hostname = 'hostname' order by id;

Spool usage:
Spool is a command under Oracle SQL * PLUS. It can be used to export a large amount of data in a table. The generated format can be manually controlled by you.
1. Compile the spool. SQL script and store it 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 on to SQL * plus using the scott user and execute the preceding SQL script.
! /Tmp/spool. SQL (in linux)
@ D: \ spool. SQL (in windows)

3.observe the corresponding directory and generate the data.txt file. The content in the txt file is as follows:
10, ACCOUNTING, NEW YORK
20, RESEARCH, DALLAS
30, SALES, CHICAGO
40, OPERATIONS, BOSTON

Summary:
You can use the spool tool to easily export the required tables, and then use the exported data to migrate data between different databases.
The following describes some common spool settings:
Set colsep ''; // The output delimiter of the domain.
Set echo off; // display each SQL command in the start script. The default value is on
Set feedback off; // display the number of records processed by this SQL command. The default value is on.
Set heading off; // output domain title, on by default
Set pagesize 0; // number of output lines per page. The default value is 24. To avoid paging, you can set the value to 0.
Set termout off; // display the execution result of the command in the script. The default value is on (term can be abbreviated as "on)
Set trimout on; // Remove trailing spaces of each standard output line. The default value is off.
Set trimspool on; // remove the trailing space of each row from the redirection (spool) output. The default value is off.

Note:
If you directly execute the preceding spool. SQL command in SQL * plus without executing the script, the generated txt file contains the executed SQL statement.

PL/SQL Developer Practical Skills

Oracle PL/SQL Composite data types

Oracle | PL/SQL Check constraints usage

PL/SQL storage functions and stored procedures