Oracle工作总结(浅)

2022-06-14 17:17:43

我早就想

要这样载着你……

翻山越岭

-----《侧耳倾听》

一.序列、触发器、主键自增长

工作中常常使用序列来生成主键值，名字一般是sq_tableName_字段，比如某个student表的id为主键，则序列名字可以为SQ_STUDENT_ID。

触发器我平常用它主要是在插入一个数据时，懒得去管表的主键，就像自动会给我生成一个主键值，也就是主键自增长，案例如下：

oracle创建主键自增长表需要三个步骤：

1）创建表

CREATE TABLE STUDENT(
  ID NUMBER NOT NULL,
  NAME VARCHAR2(4000) NOT NULL,
  PRIMARY KEY(ID)
)

2）创建序列

CREATE SEQUENCE SEQUENCE_STUDENT_ID MINVALUE 1 NOMAXVALUE 
INCREMENT BY 1 START WITH 1 NOCACHE;

3）创建触发器

注意：如果对应的表删了，触发器也自动没有了

CREATE OR REPLACE TRIGGER TRG_ON_INS_STUDENT 
BEFORE INSERT ON STUDENT FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
  SELECT SEQUENCE_STUDENT_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

4）验证：插入数据

INSERT INTO STUDENT(name) values ('Hello');
INSERT INTO STUDENT(name) values ('Hello');

5）删除序列

DROP SEQUENCE SEQUENCE_STUDENT_ID;

6）查看已存在的序列（区分大小写）

select * from user_sequences;
select * from user_sequences  where  sequence_name='SEQ_T_SELL_BRAND';
select * from user_sequences  where  sequence_name like '%T_SELL_BRAND%';

7）给字段添加默认值

alter table STUDENT modify AGE default 20;

再执行插入操作：

INSERT INTO STUDENT(name) values ('Hello');

如图：

8）拓展（关于赋权-同一个库）：

最近遇到的一个问题：同一个数据库有不同的用户，假如有两个用户分别为：user1、user2与user3，user1能够访问到user2与user3的所有表数据

1）不同用户下表的赋权

grant select,insert,update,delete on user2.表名 to user1;

然后user1去访问user2需要这样写即可

select * from user2.tableName;  --加个前缀即可

2）不同用户下序列的赋权

最近遇到的一个问题，我在user1的用户上往user2的某个表里插入数据，然后序列建在了user2上，运行程序后台提示序列不存在，然后第一次知道了原来序列也要赋权，特此总结下。然后在user1上可以这样玩：select user2.序列名.nextval from dual;

grant select , alter on user2.序列名 to user1;

3)不同用户下的函数赋权

grant execute on 函数名 to 其它用户

grant execute on AHSIMIS_QUERY.wsbs_pk_date_validate to ahsicp3

4）不同用户下包的赋权

grant execute on 包名 to 其他用户；

grant execute on ahsimis_query.pkg_pan_公众服务平台接口 to ahsicp3

二.定时任务

使用plsql工具来创建job任务。我第一家公司经常用这个来晚上跑数据，因为白天担心数据库挂掉，定时任务也不宜过多，job文件夹里在一些传统的IT行业经常用，但是看到有几十个任务。

1）创建表

主键自增略

-- Create table
create table STUDENT
(
  id   NUMBER not null,
  name VARCHAR2(4000) not null,
  age  NUMBER default 20
)

2）创建存储过程

create or replace procedure testJob is
begin
  insert into student(name)
  values('angel');
end;

3）创建定时器

右键该文件夹新建一个任务，最后应用即可。

(1)、在what值中填写待执行的存储过程，以分号结尾；

(2)、在间隔中填写执行时间策略；

4）时间间隔说明

1）.每分钟执行
Interval => TRUNC(sysdate,'mi') + 1/ (24*60)

2）每小时执行
Interval => TRUNC(sysdate,'hh') + 1/ (24)

3）.每天定时执行，例如：每天的凌晨1点执行
Interval => TRUNC(sysdate+ 1) +1/ (24)

4）.每周定时执行，例如：每周一凌晨1点执行
Interval => TRUNC(next_day(sysdate,'星期一'))+1/24

5）.每月定时执行，例如：每月1日凌晨1点执行
Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24

6）.每季度定时执行，例如每季度的第一天凌晨1点执行
Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24

7）.每半年定时执行，例如：每年7月1日和1月1日凌晨1点
Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24

8）.每年定时执行，例如：每年1月1日凌晨1点执行
Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24

越写越离谱，end

5）查看任务

查看运行状态，此时broken为N，表明定时器已经启动，job为改定时器唯一标识；

select job,broken,what,interval,t.* from user_jobs t;

6）停止和启动一个JOB

--调用broken存储的过程 将broken设置为true就停止了一个启动的job
begin
 dbms_job.broken(43, true, sysdate);
 commit;
end;
 
 
--调用dbms_job.run()；启动Job
begin
 dbms_job.run(43);
 commit;
end;

停止以后可以查看job的BROKEN字段来判断，N为运行，Y代表停止！

三.联合索引

数据库索引类似一本书的目录，目录中将书的内容先分为第一层（第一章、第二章……第N章的标题和对应页码），第二层（1.1XXX，1.2XXX……N.nXXX）具体某个小章节的标题和对应页码。读者可以根据目录快速查找到自己想看的内容，不用一页一页翻阅书本查找。

1.最左匹配原则

新建一个原生表并添加测试数据，创建联合索引。

前导列：建立一个复合索引(a,b,c)，a是这个复合索引的第一列称为前导列。

当我们使用到这个复合索引的时候，查询条件中未使用到前导列，
如：select * from table where b=? and c=? ,
一般情况下，不会使用到该复合索引。

生成测试数据

①执行 select * from demo where house_phone='xxx'，会走索引。

②执行select * from demo where house_phone='xxx' and fax='xxx';也会走索引

③执行select * from demo where mobile='xxx' and fax='xxx'; 则走全局扫描

④那么如果是where mobile='xxx' and house_phone='xxx'

或者 where fax='xxx' and house_phone='xxx'

则数据库会自动优化成为以house_phone开头。即b,a或者c,a最终变成a,b或者a,c;

举个例子：

我们到达某个房间时必须依次打开A、B、C三扇门。假如我们想打开B门或者C门，就必须先打开A门，而不能直接打开B门或C门。

索引的最左匹配原则也与此类似，如果想让B，C字段的索引生效，那么需要先在where条件中使用B字段，如果直接使用B或C字段是无法走索引扫描的。

四.强制索引

在某些场景下，可能ORACLE不会自动走索引，这时候，如果对业务清晰，可以尝试使用强制索引，然后测试查询语句的性能。

SELECT /*+index(t pk_emp)*/* FROM EMP T   
--加号index(表名 空格 索引名)。
--如果表用了别名，注释里的表也要使用别名。
要走多个索引可在后面添加比如：

/*+index(t idx_name1)(t idx_name2)*/

码农公寓

Oracle工作总结(浅)

一.序列、触发器、主键自增长

1）创建表

2）创建序列

3）创建触发器

4）验证：插入数据

5）删除序列

6）查看已存在的序列（区分大小写）

7）给字段添加默认值

8）拓展（关于赋权-同一个库）：

1）不同用户下表的赋权

2）不同用户下序列的赋权

3)不同用户下的函数赋权

4）不同用户下包的赋权

二.定时任务

1）创建表

2）创建存储过程

3）创建定时器

4）时间间隔说明

5）查看任务

6）停止和启动一个JOB

三.联合索引

1.最左匹配原则

四.强制索引

五.命名空间

5.oracle分页

6.表分区

7.三大范式

8.dblink用法

9.merge用法

码农公寓

一.序列、触发器、主键自增长

1）创建表

2）创建序列

3）创建触发器

4）验证：插入数据

5）删除序列

6）查看已存在的序列（区分大小写）

7）给字段添加默认值

8）拓展（关于赋权-同一个库）：

1）不同用户下表的赋权

2）不同用户下序列的赋权

3)不同用户下的函数赋权

4）不同用户下包的赋权

二.定时任务

1）创建表

2）创建存储过程

3）创建定时器

4）时间间隔说明

5）查看任务

6）停止和启动一个JOB

三.联合索引

1.最左匹配原则

四.强制索引

五.命名空间

5.oracle分页

6.表分区

7.三大范式

8.dblink用法

9.merge用法

相关文章