---创建dblink 连向192.168.0.7
Create Public Database Link dna2 Connect To gdna2 Identified By "gdna2" Using'
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DNA2)
)
)
';
Commit;
Select * from notification @dna2;
--建一张中间结果表
Create Table zcmTestResult(
Id Number Primary Key,
Name Varchar2(50) Not Null,
waitReviewCount Number Default 0,
totalCount Number Default 0,
affirmCount Number Default 0,
remark Varchar2(100)
);
--创建序列
Create Sequence zcmSequence Increment By 1 Start With 1;
--创建触发器自增
Create Or Replace Trigger zcmTestResultTri Before Insert
On zcmTestResult For Each Row
Begin
Select zcmSequence.Nextval Into:New.Id From dual;
End;
--插入数据
Insert Into zcmTestResult Values(Null,'test','','','','');
Insert Into zcmTestResult(Name,remark)Values('sdf','2222221');
Select * From zcmTestResult;
---创建存储过程
Create Or Replace Procedure zcmTestResultPro
Is
tempNum Number :=floor(dbms_random.value(1,5));
Begin
Update zcmTestResult Set Name=tempNum,Waitreviewcount=Waitreviewcount+tempNum,Totalcount=Totalcount+tempNum,
Affirmcount=Affirmcount+tempNum,remark='周喜雷' Where Id='23';
Commit;
End;
--创建job
Declare
myJob Number;
Begin
sys.Dbms_Job.submit(
job => myJob,
what =>'zcmTestResultPro;',
next_date=>Sysdate,
Interval =>'sysdate+1/24/120');
Commit;
End;
Select floor(dbms_random.value(1,5)) From dual;
Select * From zcmTestResult;
Update zcmTestResult Set Name='55',Waitreviewcount=Waitreviewcount+5,Totalcount=Totalcount+5,
Affirmcount=Affirmcount+5,remark='周喜雷' Where Id='24';
Drop Public Database Link dna2;
----查找所有定时任务job
Select * From all_jobs;
----删除job
Begin
dbms_job.remove(8);
End;
Commit;
Create Public Database Link dna2 Connect To gdna2 Identified By "gdna2" Using'
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.7)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DNA2)
)
)
';
Commit;
Select * from notification @dna2;
--建一张中间结果表
Create Table zcmTestResult(
Id Number Primary Key,
Name Varchar2(50) Not Null,
waitReviewCount Number Default 0,
totalCount Number Default 0,
affirmCount Number Default 0,
remark Varchar2(100)
);
--创建序列
Create Sequence zcmSequence Increment By 1 Start With 1;
--创建触发器自增
Create Or Replace Trigger zcmTestResultTri Before Insert
On zcmTestResult For Each Row
Begin
Select zcmSequence.Nextval Into:New.Id From dual;
End;
--插入数据
Insert Into zcmTestResult Values(Null,'test','','','','');
Insert Into zcmTestResult(Name,remark)Values('sdf','2222221');
Select * From zcmTestResult;
---创建存储过程
Create Or Replace Procedure zcmTestResultPro
Is
tempNum Number :=floor(dbms_random.value(1,5));
Begin
Update zcmTestResult Set Name=tempNum,Waitreviewcount=Waitreviewcount+tempNum,Totalcount=Totalcount+tempNum,
Affirmcount=Affirmcount+tempNum,remark='周喜雷' Where Id='23';
Commit;
End;
--创建job
Declare
myJob Number;
Begin
sys.Dbms_Job.submit(
job => myJob,
what =>'zcmTestResultPro;',
next_date=>Sysdate,
Interval =>'sysdate+1/24/120');
Commit;
End;
Select floor(dbms_random.value(1,5)) From dual;
Select * From zcmTestResult;
Update zcmTestResult Set Name='55',Waitreviewcount=Waitreviewcount+5,Totalcount=Totalcount+5,
Affirmcount=Affirmcount+5,remark='周喜雷' Where Id='24';
Drop Public Database Link dna2;
----查找所有定时任务job
Select * From all_jobs;
----删除job
Begin
dbms_job.remove(8);
End;
Commit;