ORACLE常用操作

此文档会不断更新

1、--创建表
create table test (
id varchar2(200) primary key not null,
sort number,
name varchar(200)
)
--字段加注释
comment on column test.id is 'id';
comment on column test.sort is '序号';
--表加注释
comment on table test is '测试表'

dbms_output.put_line --输出

alter table suifangxinxi
add (fujianfuwu* varchar2(2));

comment on column suifangxinxi.fujianfuwu* is '辅检服务态度';

alter table suifangxinxi
add (fujianfuwuzhiliang varchar2(2));

comment on column suifangxinxi.fujianfuwuzhiliang is '辅检服务质量';

2、创建job
declare job number;
begin
dbms_job.submit(
job =>job,
what=>'proc1;proc2;',
next_date => sysdate,
interval =>'trunc(sysdate + 1))';--每天凌晨同步一次
commit;
end;

3、创建db_link

create database link dblink_hyemr
connect to usertestemr identified by "useremr_mhxfy"
using '(description =(address_list =(address = (protocol = tcp)(host = 192.168.1.8)(port = 1521)))(connect_data =(service_name = crds3db)))';

4、同一个数据库中，可以直接通过用户.数据表来比较
select a.dddzhi 马山ddd值,b.dddzhi 福清ddd值, a.*
from 马山用户名.x_yaopin a, 福清用户名.x_yaopin b
where a.yaopinid = b.yaopinid
and a.dddzhi != b.dddzhi