13其他数据库对象
<p>[TOC]</p>
<h1>其他数据库对象</h1>
<h2>视图</h2>
<p><strong>视图：</strong>如果这些数据不是直接来自实体表，则可以通过定义视图，使数据库看起来结构简单、清晰，并且可以简化用户的数据查询操作。</p>
<ul>
<li>视图能够简化用户的操作。</li>
<li>视图能使用户从多种角度看待同一数据。</li>
<li>视图对重构数据库提供了一定程序的逻辑独立性。</li>
<li>视图能够对机密数据提供案例保护。</li>
<li>适当地利用视图可以更清晰地表达查询。</li>
</ul>
<h3>创建视图</h3>
<ul>
<li>
<p>创建视图及查看视图</p>
<pre><code>SELECT * FROM emp WHERE sal>2000 ;
CREATE VIEW v_myview AS SELECT * FROM emp WHERE sal>2000;
SELECT * FROM tab WHERE tabtype = 'VIEW';
select * FROM v_myview;</code></pre>
</li>
<li>
<p>权限问题：
如使用11g、12c，创建时会提示<code>权限不足</code>的错误提示，此时需要为用户授权。
<code>sqlplus sys/密码 as sysdba</code>
<code>grant create view to c##scott</code></p>
</li>
<li>
<p>查询系统数据字典查询视图<code>user_views</code>，<code>pl/sql</code>看不到<code>text</code>字段的内容。</p>
<pre><code>SELECT view_name,text_length,text FROM user_views ;</code></pre>
</li>
<li>
<p>命名规范
不成文规定都是以<code>v_</code>开头，方便将表与视图区分开来。</p>
<pre><code>CREATE VIEW v_emp20 AS SELECT * FROM emp WHERE deptno=20 ;
SELECT * FROM v_emp20 ;</code></pre>
</li>
<li>
<p>创建视图时使用<code>or replace</code> 关键字，视图可以修改，存在视图名称则修改，不存在则创建。</p>
<pre><code>CREATE OR REPLACE VIEW v_myview AS
SELECT D.DEPTNO,
D.DNAME,
D.LOC,
COUNT(E.EMPNO) COUNT,
NVL(ROUND(AVG(SAL), 2), 0) AVG,
NVL(SUM(SAL), 0) SUM,
NVL(MAX(SAL), 0) MAX,
NVL(MIN(SAL), 0) MIN
FROM EMP E
RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME, D.LOC;
select * FROM v_myview;
select * FROM v_myview;</code></pre>
</li>
<li>开发中不要使用中文别名，一定的。
<pre><code>CREATE OR REPLACE VIEW v_myview (部门编号,部门名称,位置,部门人数,平均工资,工资总和,最高工资,最低工资) AS
SELECT D.DEPTNO,
D.DNAME,
D.LOC,
COUNT(E.EMPNO) COUNT,
NVL(ROUND(AVG(SAL), 2), 0) AVG,
NVL(SUM(SAL), 0) SUM,
NVL(MAX(SAL), 0) MAX,
NVL(MIN(SAL), 0) MIN
FROM EMP E
RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DEPTNO, D.DNAME, D.LOC;</code></pre></li>
</ul>
<h3>在视图上执行DML操作</h3>
<h4>更新简单视图（单表映射数据）</h4>
<ul>
<li>
<p>创建可替换视图<code>v_emp20</code></p>
<pre><code>CREATE OR REPLACE VIEW v_emp20 AS
SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=20 ;</code></pre>
</li>
<li>
<p>证明视图可以做<code>insert</code>操作</p>
<pre><code>SELECT * FROM v_emp20 ;
INSERT INTO v_emp20 (empno,ename,job,sal,deptno) VALUES(6688,'魔乐','CLERK',1900,20);
SELECT * FROM emp WHERE deptno=20;
SELECT * FROM v_emp20 WHERE deptno=20; </code></pre>
</li>
<li>
<p>证明视图可以做<code>update</code>操作</p>
<pre><code>UPDATE v_emp20 SET ename='MLDNJAVA',job='MANAGER',sal=2300 WHERE empno=6688;</code></pre>
</li>
<li>证明视图可以做<code>DELETE</code>操作
<pre><code>DELETE FROM v_emp20 WHERE empno=6688 ;</code></pre></li>
</ul>
<h4>更新复杂视图（多表映射）</h4>
<ul>
<li>
<p>创建视图</p>
<pre><code>CREATE OR REPLACE VIEW v_myview AS
SELECT e.empno,e.ename,e.job,e.sal,d.deptno,d.dname,d.loc FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE d.deptno=20 ;
SELECT * FROM v_myview ;</code></pre>
</li>
<li>
<p>证明无法<code>INSERT</code>多表视图</p>
<pre><code>INSERT INTO v_myview (empno,ename,job,sal,deptno,dname,loc) VALUES(6688,'魔乐','CLERK',2000,50,'教育','北京');</code></pre>
</li>
<li>
<p>证明无法<code>UPDATE</code>多表视图</p>
<pre><code>UPDATE v_myview SET ename='史密斯',sal=5000,dname='教学' WHERE empno='7369' ;</code></pre>
</li>
<li>
<p>证明<strong>可以</strong><code>DELETE</code>多表视图</p>
<pre><code>DELETE FROM v_myview WHERE empno=7369 ;
SELECT * FROM v_myview WHERE empno=7369 ;</code></pre>
</li>
<li>删除操作虽然针对视图，但是会影响原始数据表(emp)的数据（不解）。
<pre><code>DELETE FROM v_myview WHERE deptno=20 ;
SELECT * FROM emp ;
SELECT * FROM dept;
SELECT * FROM v_myview</code></pre></li>
</ul>
<p><strong>提问：</strong>能否有其他方法通过视图同时更新多张数据表？
<strong>回答：</strong>利用触发器完成。在第三部分学。</p>
<h4>WITH CHECK OPTION 子句</h4>
<ul>
<li>
<p>示范视图条件被修改的情况
<code>deptno=20</code>这是创建视图的条件，也是可以被<code>update</code>的.
防止被修改，需要使用<code>WITH CHECK OPTION CONSTRAINT v_emp20_ck</code>子句，可以起约束名。</p>
<pre><code>CREATE OR REPLACE VIEW v_emp20 AS
SELECT * FROM emp WHERE deptno=20 ;
SELECT view_name,text_length,text FROM user_views ;
SELECT * FROM v_emp20 ;
UPDATE v_emp20 SET deptno=40 WHERE empno=7369 ;
SELECT * FROM v_emp20 WHERE empno=7369 ;
SELECT * FROM emp WHERE empno=7369 ;</code></pre>
</li>
<li>防止视图条件被修改
<pre><code>CREATE OR REPLACE VIEW v_emp20 AS
SELECT * FROM emp WHERE deptno=20
WITH CHECK OPTION CONSTRAINT v_emp20_ck ;
UPDATE v_emp20 SET deptno=40 WHERE empno=7369 ;</code></pre></li>
</ul>
<h4>WITH READ ONLY 子句</h4>
<ul>
<li>起到的作用是禁止通过视图修改数据，是所有数据都不能改。
上一个知识点是视图创建条件不能改。</li>
<li>在视图的数据字典里多了一个<code>user_views.read_only='Y'</code>字段。</li>
</ul>
<pre><code>CREATE OR REPLACE VIEW v_emp20 AS
SELECT * FROM emp WHERE deptno=20
WITH READ ONLY ;
UPDATE v_emp20 SET ename='史密斯' WHERE empno=7369;
UPDATE v_emp20 SET deptno=40 WHERE empno=7369 ;
SELECT view_name,text_length,text,read_only FROM user_views ;</code></pre>
<h4>删除视图</h4>
<ul>
<li>类似表的删除
<pre><code>DROP VIEW v_myview;
DROP VIEW v_emp20 ;</code></pre></li>
</ul>
<h2>序列</h2>
<h3>序列的作用及创建</h3>
<ul>
<li>
<p>很多数据库都有提供自动增长列的操作，Oracle 12c之前都是要使用序列实现自增长。</p>
</li>
<li>创建序列
<pre><code>CREATE SEQUENCE myseq;
SELECT * FROM user_sequences WHERE sequence_name='MYSEQ';</code></pre></li>
<li>
<p>序列的属性如下图：
<img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/521a544e34abd9794616c35de263c226?showdoc=.jpg" alt="" /></p>
</li>
<li>
<p>序列伪列
序列名.currval:表示当前已经增加的结果，多次调用，序列不会增加。
序列名.nextval:表示取当前已经增加的结果加1，每次调用序列都会加1。</p>
<p><strong>注意：</strong>在序列的<code>nextval</code>属性从来没执行过之前就执行<code>currval</code>，则会报错，说序列不存在。</p>
<pre><code>SELECT MYSEQ.currval FROM dual ;
SELECT myseq.nextval FROM dual ;</code></pre>
</li>
<li>
<p>序列自增的使用 </p>
<pre><code>DROP TABLE MEMBER PURGE ;
CREATE TABLE MEMBER (
mid NUMBER,
NAME VARCHAR2(30),
CONSTRAINT pk_mid PRIMARY KEY(mid)
);
SELECT * FROM MEMBER ;</code></pre>
</li>
<li>按照指定步长进行增加
<pre><code>INSERT INTO MEMBER(mid,NAME) VALUES (myseq.nextval,'李兴华'||myseq.currval);</code></pre></li>
</ul>
<h3>序列的删除</h3>
<ul>
<li>序列可以通过<code>drop</code>来删除。
<pre><code>DROP SEQUENCE myseq ;
SELECT * FROM user_sequences WHERE sequence_name='MYSEQ';</code></pre></li>
</ul>
<h3>创建特殊功能的序列</h3>
<ul>
<li>
<p>序列的起始值与步长</p>
<pre><code>DROP SEQUENCE myseq ;
CREATE SEQUENCE myseq
INCREMENT BY 3
START WITH 30 ;
INSERT INTO MEMBER(mid,NAME) VALUES (myseq.nextval,'李兴华'||myseq.currval);
SELECT * FROM user_sequences WHERE sequence_name='MYSEQ';
SELECT * FROM MEMBER ;</code></pre>
</li>
<li>序列的缓存与循环
<pre><code>DROP SEQUENCE myseq ;
CREATE SEQUENCE myseq
INCREMENT BY 2
START WITH 1
MAXVALUE 10
MINVALUE 1
CYCLE
CACHE 3;--NOCACHE是不使用缓存
INSERT INTO MEMBER(mid,NAME) VALUES (myseq.nextval,'李兴华'||myseq.currval);
SELECT * FROM user_sequences WHERE sequence_name='MYSEQ';
SELECT * FROM MEMBER ;</code></pre>
<p><strong>设置缓存可能造成跳号情况</strong></p></li>
</ul>
<h3>修改序列</h3>
<pre><code>DROP SEQUENCE myseq;
CREATE SEQUENCE myseq;
SELECT * FROM user_sequences WHERE sequence_name='MYSEQ';
ALTER SEQUENCE myseq
INCREMENT BY 10
MAXVALUE 9876
CACHE 100;</code></pre>
<h3>自动序列</h3>
<ul>
<li>oracle 12c 才有这个功能。</li>
<li>编码的处理是个问题。</li>
</ul>
<pre><code>DROP TABLE mytab PURGE ;
CREATE TABLE mytab (
mid NUMBER GENERATED BY DEFAULT AS IDENTITY(START WITH 1 INCREMENT BY 1),
NAME VARCHAR2(50),
CONSTRAINT pk_mid PRIMARY KEY(mid)
);
SELECT * FROM user_sequences ;
INSERT INTO mytab(NAME) VALUES('李兴华');
INSERT INTO mytab(NAME) VALUES('魔乐科技');
INSERT INTO mytab(NAME) VALUES('潘栋民');
COMMIT;
SELECT * FROM mytab ;</code></pre>
<p><strong>注意：</strong>
自动序列依附数据表而存在，如果删除表时没有使用<code>purge</code> ，序列不能使用<code>drop sequence</code> 命令删除，只能使用清除回收站的方法了<code>purge recyclubin</code> 。</p>
<h2>同义词</h2>
<ul>
<li><code>dual</code> 表是属于sys的，正常访问应该是sys.dual ，但是oracle 数据库默认将sys.dual 这个表名设置了一个同义词dual。</li>
</ul>
<pre><code>SELECT SYSDATE FROM dual ;
SELECT SYSDATE FROM sys.dual;
select * FROM tab WHERE tname='DUAL';</code></pre>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/b67c92d8f4b46b6645a9933b49f7579d?showdoc=.jpg" alt="" /></p>
<ul>
<li>切换用户才能创建，有权限问题。
<pre><code>CONNECT SYS/oracle AS SYSDBA ;--登陆sys用户
CREATE PUBLIC SYNONYM myuser FOR c##hr.mytab ;--创建同意词
select * from user_synonyms where synonym_name='MYUSER'; --查看同义词
DROP SYNONYM myuser;--删除同义词</code></pre></li>
</ul>
<h2>oracle伪列</h2>
<h3>rowid列（唯一的地址编号）</h3>
<ul>
<li>18位；1-6数据对象号；6-9相对文件号；10-15数据块号；16-18数据行号。</li>
</ul>
<p><code>SELECT ROWID,deptno,dname,loc FROM dept ;</code></p>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/7839566e1f9c7e96b4cfadc128674474?showdoc=.jpg" alt="" /></p>
<p><strong>提示：</strong>rowid的各部分信息可以通过函数取得，这些函数是DBMS_ROWID包中定义的函数。学PL/SQL编程时会接触到包的概念。rowid 的概念在以后的章节中使用比较多。</p>
<pre><code>SELECT ROWID,
DBMS_ROWID.ROWID_OBJECT(ROWID) 数据对象号,
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) 相对文件号,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) 数据块号,
DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) 数据行号,
DEPTNO 部门编号,
DNAME 部门名称,
LOC 位置
FROM DEPT;</code></pre>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/9279de0e4aba859e2bbb1bdce2b8fd6f?showdoc=.jpg" alt="" /></p>
<p><strong>利用伪列rowid能解决的一个问题：</strong>一张表中有重复数据，需要去掉重复数据，每种数据保留1条记录。</p>
<ul>
<li>
<p>准备数据表</p>
<pre><code>DROP TABLE mydept ;
CREATE TABLE mydept AS SELECT * FROM dept ;
SELECT * FROM mydept ;
INSERT INTO mydept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO mydept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO mydept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO mydept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO mydept VALUES (20,'RESEARCH','DALLAS');
COMMIT ;
SELECT ROWID,deptno,dname,loc FROM mydept order by deptno;</code></pre>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/e822f0fda65563a5fcbeb993871fd598?showdoc=.jpg" alt="" /></p>
</li>
<li>解决办法
rowid最小，代表最早。
将不是最早的删除
<pre><code>SELECT deptno,dname,loc,MIN(ROWID) FROM mydept GROUP BY deptno,dname,loc ;
DELETE mydept WHERE ROWID NOT IN(SELECT MIN(ROWID) FROM mydept GROUP BY deptno);
SELECT * FROM mydept ;</code></pre></li>
</ul>
<h3>rownum 伪列</h3>
<ul>
<li>
<p>查看伪列</p>
<pre><code>SELECT ROWNUM,empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp ;</code></pre>
</li>
<li>
<p>伪列是随机分配的</p>
<pre><code>SELECT ROWNUM,empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE deptno=30;</code></pre>
</li>
<li><strong>问题：</strong>
列出薪金高于公司平均薪金的所有员工编号、姓名、基本工资、职位、雇佣日期，所在部门名称、位置，公司的工资等级，但是为了信息浏览方便，要求在每一行数据显示前增加一个行号。
<pre><code>select * FROM emp;
SELECT * FROM deptno;
select * FROM salgrade;
-- 实现
SELECT ROWNUM,
E.EMPNO,
E.ENAME,
E.SAL,
E.JOB,
E.HIREDATE,
D.DEPTNO,
D.DNAME,
D.LOC,
S.GRADE
FROM EMP E
JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
JOIN SALGRADE S
ON E.SAL BETWEEN LOSAL AND HISAL
WHERE E.SAL > (SELECT AVG(SAL) FROM EMP);</code></pre></li>
</ul>
<h4>伪列两个应用</h4>
<h5>取出查询的第一行记录</h5>
<ul>
<li>
<p>这个只能是1</p>
<pre><code>SELECT * FROM emp WHERE ROWNUM=1 ;-- 只能是1
SELECT * FROM emp WHERE ROWNUM=2 ;-- 2无记录</code></pre>
</li>
<li>
<p>取出查询的前N行记录
标准的分页格式写法</p>
<pre><code>SELECT *
FROM (SELECT ROWNUM RN,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM EMP
WHERE ROWNUM <= 5) TEMP
WHERE TEMP.RN > 0;</code></pre>
</li>
<li>分布查询
<pre><code>SELECT *
FROM (SELECT ROWNUM RN,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO
FROM EMP
WHERE ROWNUM <= (3 * 2)) TEMP --3代表要查询的页，2代表一页记录数
WHERE TEMP.RN > (2 * 2); --2代表上一页。（2*2）代表上一页最后一条记录</code></pre></li>
</ul>
<h3>Oracle 12c 新特性fetch</h3>
<ul>
<li>三种用法：取得前5行、取得4，5行、取得10%的数据。</li>
<li>百分比可利用在分布控制中，类似mysql的LIMIT
<pre><code>SELECT * FROM emp FETCH FIRST 5 ROW ONLY ;
SELECT * FROM emp ORDER BY sal DESC FETCH FIRST 5 ROW ONLY;
SELECT * FROM emp ORDER BY sal DESC OFFSET 3 ROWS FETCH NEXT 2 ROWS ONLY;
SELECT * FROM emp ORDER BY sal DESC FETCH NEXT 10 PERCENT ROWS ONLY ;</code></pre></li>
</ul>
<h2>索引</h2>
<ul>
<li>
<p>了解B树索引、降序索引、位图索引、函数索引</p>
</li>
<li>
<p>查看语句执行跟踪，PL/SQL直接按F5</p>
<pre><code>connect sys/oracle as sysdba;
set autotrace on ;
table access full(全表扫描)</code></pre>
</li>
<li>
<p>创建索引</p>
<pre><code>select * FROM emp ;
CREATE INDEX emp_sal_ind ON emp(sal);-- 自动地在内存中将相关的数据形成一棵索引树，以提升查询性能</code></pre>
</li>
<li>
<p>按F5键查看执行过程
<img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/d6733ca795575b9e604c731bf0dcff1f?showdoc=.jpg" alt="" /></p>
</li>
<li>
<p>索引数据字典</p>
<pre><code>SELECT index_name,index_type,table_owner,table_name,uniqueness,status FROM user_indexes WHERE index_name IN('EMP_SAL_IND','PK_DEPT','PK_EMP','EMP_HIREDATE_IND_DESC','EMP_ENAME_IND','EMP_DEPTNO_IND') ;
SELECT * FROM user_ind_columns WHERE index_name IN('EMP_SAL_IND','PK_DEPT','PK_EMP','EMP_HIREDATE_IND_DESC','EMP_ENAME_IND','EMP_DEPTNO_IND') ;</code></pre>
</li>
<li>
<p>索引一定可以提升性能吗？
只有不合理的设计，没有绝对的性能提升。以时间换空间，以空间换时间。</p>
</li>
<li>
<p>降序索引</p>
<pre><code>CREATE INDEX emp_hiredate_ind_desc ON emp(hiredate) ;
SELECT * FROM emp WHERE hiredate BETWEEN to_date('1981-01-01','yyyy-mm-dd') AND to_date('1981-12-31','yyyy-mm-dd') ORDER BY hiredate DESC;</code></pre>
</li>
<li>
<p>创建函数索引</p>
<pre><code>CREATE INDEX emp_ename_ind ON emp(LOWER(ename));
SELECT * FROM emp WHERE LOWER(ename)='smith';</code></pre>
</li>
<li>
<p>位图索引，好像没生效</p>
<pre><code>CREATE BITMAP INDEX emp_deptno_ind ON emp(deptno);
SELECT * FROM emp WHERE deptno=10 ;
SELECT * FROM emp WHERE deptno=10 AND deptno=20 ;</code></pre>
</li>
<li>删除索引
<pre><code>DROP INDEX emp_sal_ind ;</code></pre></li>
</ul>
<h2>小结</h2>
<ul>
<li>通过视图对复杂的SQL语句进行封装。</li>
<li>视图本身不属于真实数据，但有时候也可以修改数据。</li>
<li>可通过序列实现自增。</li>
<li>dual 表就可以举例同义词，要权限才能创建。</li>
<li>rowid 对理解索引有帮忙、rownum 使用在分页查询中。oracle 12c 新增了新特性fetch。</li>
</ul>