oracle小记

最新推荐文章于 2024-10-01 21:51:51 发布

哈皮饼干警长

最新推荐文章于 2024-10-01 21:51:51 发布

阅读量128

点赞数 1

文章标签： oracle 数据库 sql

本文链接：https://blog.csdn.net/weixin_46423116/article/details/120697492

版权

oracle学习使用 —— 仅作为自己工作和学习中的记录

#建立主键序列自增
create sequence seq_QZ_JOBSAPPLY
minvalue 1
maxvalue 999999999
start with 1
increment by 1
cache 20
--   create sequence seq_lmu  建立序列号名称  minvalue 最小值为一 最大值 maxvalue 为99999999，  start with 0  cache 50 返回序列号新增的值50个， 从多少开始自增或自减少  increment by 1 每次加减一



#触发器
create or REPLACE TRIGGER tri_QZ_JOBSAPPLY   
before insert 
on WORKSTUDY.QZ_JOBSAPPLY
for each row
begin 
	select seq_QZ_JOBSAPPLY.nextval into :new.ID from dual;
	end;
-- 首先创建一个 触发器并命名，触发的机制在插入某个表操作前，先去查询咱们创建的序列生成的当前值是多少，对应的加一或减一后取得这个值赋给这个表


-- 代码生成

--首先 先将当前库的SUBSIDIZE用户里的 GEN_TABLE 和 GEN_TABLE_COLUMN 传输到当前的开发的模块用户中 列如：WORKSTUDY
--菜单权限表 序列 关联代码生成mapper.xml要查询的值  在当前库的SUBSIDIZE系统表中执行 SYSTEM
create sequence seq_sys_menu
 increment by 1
 start with 2000
 nomaxvalue
 nominvalue
 cache 20;
 
--select SEQ_QZ_JOBSAPPLY.nextval from dual;
-- insert into QZ_JOBSAPPLY (id,unit_name) values(SEQ_QZ_JOBSAPPLY.nextval ,'是的')
 
 --删除触发器
 drop TRIGGER SUBSIDIZE.TRI_QZ_JOBSAPPLY 
 
   -- 添加表字段
  alter table QZ_POST_MANGEAGE add xiaowang NUMBER(11) DEFAULT 1 ;	 
	 
	Alter Table t_si_addr Add CHG_ADDR_FLAG number(1,0)
  
 -- oracle系统自带表    字段和注释表
 SELECT DISTINCT
	atc.COLUMN_ID customcol_id,
	1 custom_type,
	atc.COLUMN_NAME custom_attr,
	ucc.COMMENTs custom_name
FROM
	ALL_TAB_COLUMNS atc
	LEFT JOIN ALL_col_comments ucc ON ucc.COLUMN_NAME = atc.COLUMN_NAME
	AND atc.TABLE_NAME = ucc.TABLE_NAME
WHERE
	atc.TABLE_NAME = 'QZ_POST_MANGEAGE'
	AND atc.COLUMN_NAME != 'NAME_USED'
	AND atc.COLUMN_NAME != 'ID'
	AND atc.COLUMN_NAME != 'UPDATE_BY'
	AND atc.COLUMN_NAME != 'CREATE_BY'
	AND atc.COLUMN_NAME != 'UPDATE_TIME'
	AND atc.COLUMN_NAME != 'CREATE_TIME'
	AND atc.COLUMN_NAME != 'NAME_PINYIN'
	AND atc.COLUMN_NAME != 'IS_DELETE'
	AND atc.COLUMN_NAME != 'IS_DEL'
	AND atc.COLUMN_NAME != 'SEMESTER_APPLY_FOR'
	AND atc.COLUMN_NAME != 'PAY_DAY'
	AND atc.COLUMN_NAME != 'PAY_DAY_STA'
	AND atc.COLUMN_NAME != 'PAY_DAY_END'
	AND atc.COLUMN_NAME != 'POOR_STUDENTS'
	AND atc.COLUMN_NAME != 'APPLY_FOR_LIMIT'
	AND atc.COLUMN_NAME != 'MAX_JOBS'
	AND atc.COLUMN_NAME != 'TIMESET'
	AND atc.COLUMN_NAME != 'JOBS_REPORT_M'
	AND atc.COLUMN_NAME != 'JOBS_REPORT'
	AND atc.COLUMN_NAME != 'PAY_DAY_RE'
	AND atc.COLUMN_NAME != 'PAY_MESSAGE'
	AND atc.COLUMN_NAME != 'WORK_PROCESS'
	AND atc.COLUMN_NAME != 'POST_AUDIT'
	AND atc.COLUMN_NAME != 'SALARY_REVIEW'
	AND atc.COLUMN_NAME != 'JOB_DESCRIBE'
	AND atc.COLUMN_NAME != 'STUDENT_TYPE'
	AND atc.COLUMN_NAME != 'WORK_TIME'
	AND atc.COLUMN_NAME != 'MONITOR'
	AND atc.COLUMN_NAME != 'COUNSELOR'
	AND atc.COLUMN_NAME != 'DEPARTMENT_R'
	AND atc.COLUMN_NAME != 'SCHOOL'
	AND atc.COLUMN_NAME != 'USERID'
ORDER BY
	atc.COLUMN_ID 
 
 
 
 
 
--oracle 创建表  (注意事项目前已知：DATE时间类型不必设置长度,数据库会默认为7，加了会报错误)
CREATE TABLE QZ_APPROVAL (
	"ID" NUMBER(20) PRIMARY KEY  NOT NULL ,
	"ASSOCIATED_ID" NUMBER(20),
	"MONITOR" VARCHAR2(255),
	"COUNSELOR" VARCHAR2(255),
	"DEPARTMENT_R" VARCHAR2(255),
	"SCHOOL" VARCHAR2(255),
	"TEACHER" VARCHAR2(255),
	"USER_ID" NUMBER(20),
	"MONITOR_TIME" DATE,
	"COUNSELOR_TIME" DATE,
	"DEPARTMENT_R_TIME" DATE,
	"SCHOOL_TIME" DATE,
	"TEACHER_TIME" DATE,
	"CREATE_BY" NUMBER(20),
	"CREATE_TIME" DATE
);


--为指定的表加注释  列如上面刚建的表
COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."ID" IS '主键id';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."STATUS" IS '审核状态';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."ASSOCIATED_ID" IS '关联被审核表主键ID';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."MONITOR" IS '班长审核意见';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."COUNSELOR" IS '辅导员审核意见';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."DEPARTMENT_R" IS '院系审核意见';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."SCHOOL" IS '学校审核意见';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."TEACHER" IS '班主任审核意见';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."USER_ID" IS '用户标识';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."MONITOR_TIME" IS '班长审核时间';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."COUNSELOR_TIME" IS '辅导员审核时间'

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."DEPARTMENT_R_TIME" IS '院系审核时间';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."SCHOOL_TIME" IS '学校审核时间'

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."TEACHER_TIME" IS '班主任审核时间'

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."CREATE_BY" IS '创建者';

COMMENT ON COLUMN "WORKSTUDY"."QZ_APPROVAL"."CREATE_TIME" IS '创建时间';