PL/SQL中如何讓程式每隔幾秒插入一條資料

PL/SQL中如何讓程式每隔幾秒插入一條資料

在編寫Oracle PL/SQL中，如果需要程式執行中暫停幾秒鐘再繼續執行，可以通過Oracle內建的dbms_lock.sleep來實現，不過dbms_lock包需要使用者自己安裝。
[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, 2013, 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;　--授權PUBLIC執行許可權 
 
Grant succeeded. 
 
SQL> create table test1(id number,name varchar2(40),time date);  --建立test1暫存資料表 
 
Table created. 
 
SQL> select * from test1;  --無資料 
 
no rows selected   
 
SQL> SET TIMING ON    --開啟時間顯示 
SQL> begin            --開始執行測試指令碼 
  2    insert into test1(id,name,time) values(1,'Andy',sysdate); 
  3    DBMS_LOCK.SLEEP(10);  --讓程式暫時10秒鐘 
  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  --程式執行時間為10.04秒 
 
SQL> SELECT ID,NAME,TO_CHAR(TIME,'YYYY/MM/DD HH24:MI:SS') AS TIME FROM TEST1;　
--查詢執行結果 
 
        ID NAME                          TIME 
---------- -------------------------  ----------------- 
        1 Andy                        2014/12/10 10:09:03  --第一條的插入時間是09:03 
        2 Shirley                    2014/12/10 10:09:13  --第二條的插入時間是09:13
剛好比第一條晚了10秒鐘   
 
SQL> drop table test1; 
 
Table dropped. 

下面給個例子：
每隔一秒插入一條資料
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
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

[root@oraclevm ~]#chmod 755 /tmp/11.sh
[root@oraclevm ~]#cd /tmp
[root@oraclevm ~]#./11.sh
即可執行。

先設定一下時間格式
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

最後進行資料查詢
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用法：
spool是Oracle SQL*PLUS下的命令，可以用它來匯出表中的大量資料，產生格式可以由自己手動控制。
1. 編寫spool.sql指令碼，存放在/tmp目錄下
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. 在sql*plus上用scott使用者登入，執行上面的sql指令碼
!/tmp/spool.sql(linux下)
@d:\spool.sql（windows下）

3.觀察相應目錄，產生了data.txt檔案，txt中的內容如下：
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

總結：
利用spool可以方便地匯出所需要的表，進而可以利用匯出的資料進行不同資料庫間的資料移轉。
下面介紹使用spool的一些常用設定：
set colsep ' ';　　　 //域輸出分隔字元
set echo off;　　　　//顯示start啟動的指令碼中的每個sql命令，預設為on
set feedback off;　　//回顯本次sql命令處理的記錄條數，預設為on
set heading off;　　 //輸出域標題，預設為on
set pagesize 0;　　　//輸出每頁行數，預設為24,為了避免分頁，可設定為0。
set termout off;　　 //顯示指令碼中的命令的執行結果，預設為on(可以縮寫為term)
set trimout on;　　　//去除標準輸出每行的拖尾空格，預設為off
set trimspool on;　　//去除重新導向（spool）輸出每行的拖尾空格，預設為off

注意：
如果直接在sql*plus中執行上面的spool.sql命令，而不通過執行指令碼來進行，那麼在產生的txt檔案中會存在執行的sql語句。

PL/SQL Developer實用技巧分享

Oracle PL/SQL複合資料型別 

Oracle | PL/SQL Check約束用法詳解 

PL/SQL 儲存函數和預存程序