Oacle常用操作语法汇集

---循环插入
begin
for item in (select concat('d',id) as id, fun_code from xxx) loop
insert into table values(SEQ_sequence_name.nextval,item.xx,item.yy);
end loop;
end;

-- 更新字段名
alter table qc_test_table rename column TABLE_NAME to test_table_name;
-- 添加字段
alter table TABLE_NAME add COLUMN_NAME varchar(10);
-- 删除字段
alter table TABLE_NAME drop column COLUMN_NAME;
-- 添加字段并附值
alter table TABLE_NAME ADD COLUMN_NAME NUMBER(1) DEFAULT 1;
-- 修改字段值
update TABLE_NAME set filedname=value where filedname=value;
-- 修改字段数据类型
alter table tablename modify filedname varchar2(20);

-- 建表
create table table_name
(
xx NUMBER(10) not null,
yy NUMBER(10),
zz VARCHAR2(4000)
);
alter table table_name
add constraint PK_pk_name primary key (xx);
comment on table table_name is 'qqq表';
comment on column table_name.xx is 'xxx';
comment on column table_name.yy is 'yyy';

create sequence seq_name
increment by 1
start with 150
maxvalue 999999999;