Oracle

Oracle

0.概念

存储结构：
       物理存储结构=>存储数据的纯文件  
       逻辑存储结构=>对磁盘空间使用情况进行精细控制
 特点：
     移植性好
     高吞吐量
     功能强 
组成：
    数据库=>数据库是一组存储数据的文件
    数据库实例=>数据库实例则是管理数据库文件的内存结构    

1.数据库对象

1.1 视图 View

View也被称为虚表，即虚拟的表，是一组数据的逻辑表示，其本质是对应一条SELECT语句,结果集被赋予一个名字，即视图名字。
View本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也会随之变化

1.1.1 创建视图的语法

CREATE [OR REPLACE] [FORCE] VIEW 视图名 [视图别名] AS [SELECT..] [WITH READ ONLY可选只读约束] 

1.1.2 视图的查询

SELECT * FROM [视图名]

1.1.3 视图的作用

如果需要经常执行某项复杂查询，可以基于这个复杂查询建立视图，以后直接查询此视图即可，简化复杂查询

1.1.4 授权创建视图

管理员执行以下语句即可授权用户创建视图权限
GRANT CREATE VIEW TO [用户名]

1.1.5 创建简单视图(单表)

CREATE VIEW V_EMP_10
AS
SELECT emp_no,name,sal,dept_no
FROM emp
WHERE dept_no=10

单表视图可以通过DML操作，影响到基表数据(INSERT UPDATE DELETE)

1.1.6 创建复杂视图(多表）

CREATE VIEW v_emp_salary
AS
SELECT d.dname, avg(e.sal) avg_sal, sum(e.sal) sum_sal, 
max(e.sal) max_sal, min(e.sal) min_sal 
FROM emp e 
JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname;

1.1.7 查询当前用户名下的所有视图

SELECT * FROM all_views WHERE OWNER = '当前用户名'
SELECT * FROM user_views

//查询当前用户下的某一个视图
SELECT * FROM user_views WHERE view_name = '视图名'

1.1.8 删除视图

DROP VIEW view_name
视图的删除不会影响到基表的数据，删除的只是一组逻辑关系

1.1.9 同一数据库，两个不同账户之间表或者视图的授权

GRANT [SELECT | UPDATE | DELETE | INSERT] ON [当前账户的表名] TO [另外的账户名]

GRANT SELECT ON ObjectDef TO mplm_eai;

1.1.10 WITH GRANT OPTION 对象授权

GRANT SELECT ON ... TO ...WITH GRANT OPTION

• WITH GRANT OPTION： 权限赋予/取消是关联的，如将with grant option用于对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，如grant select on table with grant option to A,A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。

1.2 序列 SEQ

1.2.1 什么是序列？

--1.序列是一种用来生成唯一数字值的数据库对象。通常用来自动产生表的主键值。
--2.序列是独立的数据库对象，序列并不依附于表。
--3。通常情况下，一个序列为一个表提供主键值，但一个序列也可以为多个表提供主键值，由Oracle程序控制自增。

1.2.2 创建序列

CREATE SEQUENCE [SEQ_NAME] 
INCREMENT BY: 序列变化的步进，负数表示递减，默认1。
START WITH: 序列的初始值，默认1.
MAXVALUE: 序列可生成的最大值，默认不限制最大值，相当于NOMAXVALUE
MINVALUE: 序列可生成的最小值，默认不限制最小值，相当于NOMINVALUE
CYCLE: 定义当序列达到限制值后是否循环，NOCYCLE不循环
CACHE: 表示缓存序列的个数，数据库异常终止可能会导致序列中断出现不连续，默认值为20，NOCACHE表示不缓存

1.创建序列的例子
CREATE SEQUENCE SEQ_DEMO 
INCREMENT BY 1 
START WITH 1 
NOMAXvalue 
NOCYCLE 
NOCACHE;

2.mybatis插入数据并返回主键(Oracle)
 <insert id="addEmployee" databaseId="oracle">
        <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
            select EMPLOYEE_SEQ.nextval from dual
        </selectKey>
        <!-- 插入的主键是从序列中获取的 -->
          insert into employee(id,last_name,age,email)
          values(#{id},#{lastName},#{age},#{email})
 </insert>
 
3.获取序列的值
    SELECT MY_TEST.nextval FROM DUAL ：返回MY_TEST序列的下一个值
    SELECT MY_TEST.currval FROM DUAL ：返回MY_TEST序列的当前值
    INSERT INTO TABLE_NAME VALUES(MY_TEST.nextval,'TEST') ：使用序列值作为主键插入数据库

1.2.3 查询用户的序列

SELECT * FROM USER_SEQUENCES : 查询当前用户下的所有序列
SELECT * FROM user_tables : 查询当前用户下的所有表
SELECT * FROM all_tables WHERE OWNER='SYS' : 查询某个用户下的所有表，用户名必须大写

1.2.4 删除序列

DROP SEQUENCE [序列名]

1.3 索引

1.3.1 索引的概念

索引是一种允许直接访问数据表中某一数据行的树形结构，为了提高查询效率而引入。
索引是独立于表的对象，可以存放在与表不同的表空间中。
索引记录中，存有索引关键字和指向表中数据的指针。对索引进行的IO操作比对表进行的IO操作要少很多。
索引一旦被建立就将被Oracle系统自动维护。

1.3.2 创建索引

CREATE [UNIQUE] INDEX [索引名] ON 表名(字段名...)

CREATE INDEX MY_INDEX  ON USER(ID) 
CREATE INDEX MY_INDEX2 ON USER(ID,NAME)：复合索引
CREATE INDEX MY_INDEX3 ON USER(UPPER(NAME)): 基于函数的索引
        SELECT ID,NAME FROM USER WHERE UPPER(NAME) = 'DEMO' 该查询会使用到函数索引

1.3.3修改和删除索引

ALTER INDEX [索引名] REBUILD ：重建索引
DROP  INDEX [索引名]：删除索引

1.3.4 合理使用索引

1.为经常出现 WHERE 后面的字段创建索引
2.为经常出现在 ORDER BY,DISTINCT 后面的字段创建索引
3.为经常作为表的连接条件的字段创建索引
4.不要在经常做DML操作的表上建立索引
5.不要在小表上建立索引
6.限制表上索引的数目，索引不是越多越好
7.删除很少被使用的，不合理的索引

1.4 约束

[非空约束]    NOT NULL
[唯一性约束]  UNIQUE       
[主键约束]    PRIMARY KEY 
[外键约束]    FOREIGN KEY

--primary key和 unique 都是oracle中的约束类型。它们两个在创建表的时候，都会为字段自动添加唯一索引。
--primary key: 列值唯一，不为NULL
--unique:  列值唯一，可以为NULL

[检查约束]  CHECK : 检查约束条件用来强制在字段上的每个值都要满足 CHECK中定义的条件
如：ALTER TABLE emp ADD CONSTRAINT emp_ck CHECK(salary > 2000)  

--约束和索引， 前者是用来检查数据的正确性，后者用来实现数据查询的优化，目的不同。

--创建唯一约束与创建唯一索引有所不同：
    --创建唯一约束会在Oracle中创建一个Constraint，同时也会创建一个该约束对应的唯一索引。
    --创建唯一索引只会创建一个唯一索引，不会创建Constraint。
    --也就是说其实唯一约束是通过创建唯一索引来实现的。

--在删除时这两者也有一定的区别：
    --删除唯一约束时可以只删除约束而不删除对应的索引，所以对应的列还是必须唯一的，
    --而删除了唯一索引的话就可以插入不唯一的值。

1.5 同义词

--同义词:屏蔽对象的名字及其持有者，为用户简化sql

--1.私有同义词：用户在自己的模式下创建私有同义词，这个用户必须拥有CREATE SYNONYM权限，否则不能创建私有同义词。
GRANT CREATE SYNONYM TO userA;（SYS账号登录）
create or replace synonym tableA  for userA.tableA;  

--2.公有同义词：创建需要 CREATE PUBLIC SYNONYM 权限
但是公共的意思并不是所有的用户都可以访问它，必须被授权后才能进行
CREATE  PUBLIC  SYNONYM  pub_emp FOR  SCOTT.emp;

--3.如果需要在其它模式下创建同义词，则必须具有CREATE ANY SYNONYM的权限。

--如：用户DM想创建SCOTT模式下的私有同义词

CREATE SYNONYM SCOTT.EM FOR SOCTT.EMP;
ORA-01031: insufficient privileges

用sys账号给DM账号赋予CREATE ANY SYNONYM的权限 
SQL> GRANT CREATE ANY SYNONYM TO DM; 
Grant succeeded.

SQL> CREATE SYNONYM SCOTT.EM FOR SOCTT.EMP;
Synonym created

1.6 DBLink

用于访问远程数据库,为远程数据库建立网络服务名，创建DBLINK
select * from mtuser@mplm

1.8 对象类型TYPE

 Oracle对象类型包括 对象类型规范 和 对象类型体两部分。
       对象类型可以更好的体现Oracle的数据库中设计的面向对象的概念。
       

2.tnsname.ora数据库连接配置文件

#数据库访问地址别名（随便起）
ORCL =
 (DESCRIPTION =
    # 数据库的通讯协议 主机名 端口号
   (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
   (CONNECT_DATA =
     #此客户端所对应服务的server进程只为你这个客户端服务。
     (SERVER = DEDICATED)
     #数据库实例名称
     (SERVICE_NAME = orcl)
   )
 )  

3.Oracle的数据类型

3.01 NUMBER(P,S)

• P表示该数字允许的最大总位数
• S表示该数字允许的最大小数的位数

3.02 timestap(6)

• 时间戳类型，参数6指的是表示 秒 的 小数点右边 可以存储6位小数。
• 最多9位

4. Oracle的常用函数

4.01 INSTR

instr(源字符串，目标字符串，起始位置，发生次数)：返回要截取的字符串在源字符串中的位置

--起始位置: 参数可选，缺省默认为1，字符串索引从1开始。
如果此参数为正，从左到右开始检索，如果此参数为负，从右到左检索，返回要查找的字符串在源字符串中的开始索引。 

--出现次数：缺省默认为1，代表要查找第几次出现的 ‘目标字符串’

select instr('miaoying','i',2,2)  from dual;--返回6
select instr('miaoying','k',2,2)  from dual;--返回0:即如果查找不到，则返回0
select instr('miaoying','i')      from dual;--返回2
select instr('miaoying','yi')     from dual;--返回5：即"yi"的y的位置
select instr('miaoying','i',-1,2) from dual;--返回2：从右边第一个开始，往左边查找第二次出现的位置

4.02 SUBSTR

substr(字符串，截取开始位置，截取长度) ：返回截取的字符串

select substr('miaoying',0,1)  from dual;--m
select substr('miaoying',1,1)  from dual;--m,说明0和1都表示截取的位置为第一个字符
select substr('miaoying',-7,4) from dual;--iaoy,负数：-7表示从右边开始数第七位开
始，截取长度为4的字符串
select substr('miaoying',2)    from dual; --iaoying,从2开始截取到最后

4.03 DECODE

--exp满足condition1就返回value1 依次类推，最后一个为默认值
decode(exp,condition1,value1,condition2,value2.. ,默认值)

4.04 TO_DATE

INSERT INTO TB_TEST(ID,NAME,CREATE_TIME,UPDATE_TIME)
VALUES(1111111,'zs',TO_DATE ('2020-02-04 23:22:15','yyyy-mm-dd hh24:mi:ss'),SYSDATE)

4.05 TO_CHAR

SELECT TO_CHAR (SYSDATE, 'yyyy-mm-dd') FROM DUAL;
select to_char(sysdate,'day') from dual;--tuesday  
SELECT TO_CHAR(1234521313) FROM DUAL;
--把科学计数法 数字 转换为字符串形式方便查看
SELECT TO_CHAR(123E10) FROM DUAL;  --1230000000000

4.06 || 字符串拼接

|| 运算符，用于字符串的拼接   如  'a'||'b'||'c' 允许拼接多个字符

SELECT 'a'||'b'||'c' FROM DUAL；--返回 abc

4.07 NVL

NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。但此函数有一定局限，所以就有了NVL2函数。
SELECT NVL(null,'2222')  FROM DUAL; --2222
SELECT NVL('xxx','2222') FROM DUAL; --xxx

4.08 NVL2

NVL2(E1, E2, E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。
SELECT NVL2('ccc','true','false') FROM DUAL; --true
SELECT NVL2(null,'true','false')  FROM DUAL; --false

4.09 replace

--语法 replace(source,oldChar,newChar)

SELECT REPLACE('1aaa1','1','XXX') FROM DUAL;--返回XXXaaaXXX

4.10 NULLIF

NULLIF(param1,param2) 如果参数1等于参数2 ，那么就返回 null , 否则返回参数1的值
      
SELECT NULLIF(2,2) FROM DUAL; --返回NULL
SELECT NULLIF(1,2) FROM DUAL; --返回1

4.13 rank()over()

rank () OVER (PARTITION BY 列名 ORDER BY 列名 排序)

-----------------------
subject  score
数学      80
数学      90
数学      100
数学      60
数学      40
语文      70
语文      77
语文      88
语文      20
语文      60
-----------------------

---查询出每门科目的前3名的分数    
select *
  from (select rank() over(partition by a.subject order by a.score desc) rk,a.*
          from tb_cj a) t
 where t.rk <= 3;
 
 
-------------------- 
id  name age country 
1	刘备	45	蜀
2	关羽	42	蜀
3	张飞	40	蜀
5	孙权	21	吴
6	周瑜	24	吴
4	曹操	47	魏
--------------------

--按国家分组,按年龄升序
SELECT d.*,Rank()Over(partition by d.country order by d.age asc) age_rank  FROM demo d;
------------------------------
id  name age country  age_rank
3	张飞	40	 蜀	    1
2	关羽	42	 蜀	    2
1	刘备	45	 蜀	    3
4	曹操	47	 魏	    1
5	孙权	21	 吴	    1
6	周瑜	24	 吴	    2
------------------------------

--按国家分组，把各个国家年龄最小的猛将 查出来
SELECT *
  FROM (SELECT d.*,
               Rank() Over(partition by d.country order by d.age asc) age_rank
          FROM demo d) v
 where v.age_rank = 1;
------------------------------ 
id  name age country  age_rank
3	张飞	40	蜀	    1
4	曹操	47	魏	    1
5	孙权	21	吴	    1
------------------------------

4.14 sum(case … when … then … else … end)和sum(…)over(…)

id  dated       rst
-------------------
1	2021/1/23	胜
2	2021/1/23	胜
3	2021/1/23	负
4	2021/1/24	胜
5	2021/1/24	胜
6	2021/1/24	胜  
7	2021/1/25	负
8	2021/1/25	负
9	2021/1/25	负
-------------------

--按日期统计当天的胜负总数
=====================================================================

SELECT 
  m.dated AS 日期,
  sum(case m.rst when '胜' THEN 1 ELSE 0 END) AS  胜 ,-- 满足条件+1
  sum(case m.rst when '负' THEN 1 ELSE 0 END) AS  负  -- 满足条件+1
FROM
	match_rec m  
GROUP BY
	m.dated;
------------------
日期	      胜  负
2021/1/23	2	1
2021/1/24	3	0
2021/1/25	0	3
------------------
	
=====================================================================

SELECT DISTINCT
  m.dated AS 日期,
  sum(case m.rst when '胜' THEN 1 ELSE 0 END)over(partition by m.dated) AS  胜 ,
  sum(case m.rst when '负' THEN 1 ELSE 0 END)over(partition by m.dated) AS  负
FROM
	match_rec m;
------------------	
日期	       胜  负
2021/1/24	3	0
2021/1/25	0	3
2021/1/23	2	1
------------------
=====================================================================

SELECT DISTINCT
  m.dated AS 日期,
  sum(case when m.rst like '胜' THEN 1 ELSE 0 END)over(partition by m.dated) AS  胜 ,
  sum(case when m.rst like '负' THEN 1 ELSE 0 END)over(partition by m.dated) AS  负
FROM
	match_rec m;  
------------------	
日期	       胜  负
2021/1/24	3	0
2021/1/25	0	3
2021/1/23	2	1
------------------

--AVG()over(PARTITION BY...) 
--COUNT()over(PARTITION BY...) 
--MIN()over(PARTITION BY...)
--MAX()over(PARTITION BY...)
--SUM()over(PARTITION BY...)
--RANK()over(PARTITION BY ... ORDER BY ... [asc / desc])

SELECT E.ENAME,
       E.JOB,
       E.SAL,
       E.DEPTNO,
       SUM(E.SAL) OVER(PARTITION BY E.DEPTNO) SUM_SAL,     --统计某组中的总计值
       AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG_SAL,     --统计某组中的平均值
       COUNT(E.SAL) OVER(PARTITION BY E.DEPTNO) COUNT_SAL  --按某列分组，并统计该组中记录数量
  FROM EMP E;

4.15 Oracle 字段别名不要使用’ ',请使用 " "或者不带引号

--如果列名带空格，不适用任何符号包裹会报错 
     SELECT 
      m.dated AS 日 期,
      sum(case m.rst when '胜' THEN 1 ELSE 0 END) AS  胜 ,-- 满足条件+1
      sum(case m.rst when '负' THEN 1 ELSE 0 END) AS  负  -- 满足条件+1
    FROM
        match_rec m  
    GROUP BY
        m.dated;
	
--使用单引号包裹报错
     SELECT 
      m.dated AS '日 期',
      sum(case m.rst when '胜' THEN 1 ELSE 0 END) AS  胜 ,-- 满足条件+1
      sum(case m.rst when '负' THEN 1 ELSE 0 END) AS  负  -- 满足条件+1
    FROM
        match_rec m  
    GROUP BY
        m.dated;
	
--使用双引号包裹 OK	
     SELECT 
      m.dated AS "日 期",
      sum(case m.rst when '胜' THEN 1 ELSE 0 END) AS  胜 ,-- 满足条件+1
      sum(case m.rst when '负' THEN 1 ELSE 0 END) AS  负  -- 满足条件+1
    FROM
        match_rec m  
    GROUP BY
        m.dated;

4.16 wm_concat函数

wm_concat(列名)：分组之后，对组内所有该字段值进行拼接。
wm_concat(表达式)：分组之后，把组内的该字段的每个值都代入表达式得到的结果再拼接起来。

--wm_concat 可以实现MYSQL的GROUP_CONCAT函数功能 
     --运行后的返回结果根据oracle的版本不同而会字段类型不同
     --在oracle11g中返回clob型
     --在oracle10g中返回varchar型
     
dated       rst   mid
---------------------
2021/1/23	胜	 2
2021/1/23	胜	 8
2021/1/23	负	 1
2021/1/24	胜	 6
2021/1/24	胜	 10
2021/1/24	胜	 3
2021/1/25	负	 5
2021/1/25	负	 4
2021/1/25	负	 12

SELECT to_char(wm_concat(c.mid)) tw,c.dated 
  FROM match_rec c 
  group by c.dated;

tw      dated
-----------------
2,1,8	2021/1/23
6,3,10	2021/1/24
5,12,4	2021/1/25

SELECT to_char(wm_concat(case when c.mid >5 then 1 else 0 end)),c.dated 
  FROM match_rec c 
  group by c.dated;
  
tw      dated
-----------------  
0,0,1	2021/1/23
1,0,1	2021/1/24
0,1,0	2021/1/25

4.17 其它函数

-- TRIM 函数结构
--1. leading(开头字符) / trailing(结尾字符) / both(头和结尾字符)
--2. trim_character  去除的字符
--3. FROM
--4. source   源字符串

--去掉源字符串左边的'x' 返回===> UUUUx
SELECT TRIM(LEADING  'x' FROM 'xUUUUx')  FROM DUAL;

--去掉源字符串右边的'x' 返回===> xUUUU
SELECT TRIM(TRAILING 'x' FROM 'xUUUUx')  FROM DUAL;

--去掉源字符串的开头和结尾的'x' 返回===> UUxUU 
--中间的无法去除
SELECT TRIM(BOTH     'x' FROM 'xxUUxUUxx')  FROM DUAL;

--去除source的开头和结尾的空格
SELECT TRIM(' abc ') FROM DUAL;

--左去除完整字符串 ===> 返回 123123123 
SELECT LTRIM('mtuser:123123123','mtuser:') FROM DUAL;

--右去除完整字符串====>返回  mtuser
SELECT RTRIM('mtuser:123123123',':123123123') FROM DUAL;

--首字母大写 ====> 返回 English Good I Am
SELECT INITCAP('english good i am') FROM DUAL;

--左填充 ===> 返回 000123 
--中间数字参数代表填充后的总长度，右边参数代表填充物
SELECT LPAD('123',6,'0') FROM DUAL;

--右填充 ===> 返回 abc:::
SELECT RPAD('abc',6,':') FROM DUAL;

--TO_NUMBER 字符串转数字
SELECT TO_NUMBER('123244') FROM DUAL;

SELECT UPPER('aaaccc') FROM DUAL --AAACCC

SELECT LOWER('BAC') FROM DUAL --bac

SELECT LENGTH('1231') FROM DUAL --4

--concat函数只允许拼接2个字符 concat(x,y)
--如果要拼接多个字符串可以使用嵌套concat函数
SELECT CONCAT(CONCAT('A', 'B'),'C') --ABC

4.18 length 和 lengthb

----客户端 
SELECT Userenv('language') FROM dual; --AMERICAN_AMERICA.ZHS16GBK

--length 查字符数 || lengthb 查字节数
SELECT length('哈啊哈') FROM DUAL;--3 
SELECT lengthb('哈啊哈') FROM DUAL;--6 查字节数

5. Oracle的分页查询

--ROWNUM 只能从1计数，不能从结果集中直接截取，下面语句中将没有结果
    SELECT ROWNUM , x.* FROM tb_test x WHERE ROWNUM > 3;

--如果利用ROWNUM 截取结果集中的部分数据，需要用到内视图
   SELECT * FROM (SELECT ROWNUM rn , x.* FROM tb_test x) WHERE rn BETWEEN 3 AND 4; 

--分页计算
--startIndex = (pageNum-1)*pageSize+1 
--endIndex   = startIndex+pageSize-1

6. Oracle的特殊语法

--MERGE是Oracle9i 新增的语法，根据源表对目标表进行匹配查询，匹配成功时更新，不成功时插入。
--在写数据同步的脚本时，常常会遇到这样的需求：存在时更新，不存在时插入。
--把一个表的数据同步到另外一个表

--基本语法
    MERGE INTO [目标表] a
    USING [源表] b
    ON (a.字段1 = b.字段2 and a.字段n = b.字段n)
    WHEN MATCHED THEN
    UPDATE SET a.新字段 = b.字段 ...
    WHERE [限制条件]
    WHEN NOT MATCHED THEN
    INSERT (a.字段名1，a.字段名n) VALUES(b.字段值1, b.字段值n)
    WHERE [限制条件]
    
---示例===》把tb_test的数据同步到tb_2
--创建tb_2表
CREATE TABLE tb_2 AS SELECT * FROM tb_test WHERE  ID = 123;

--执行同步操作
MERGE INTO tb_2 a
USING tb_test b
ON (a.id = b.id)
WHEN MATCHED THEN
  UPDATE SET a.create_time = b.create_time,a.update_time = b.update_time WHERE a.id = b.id
WHEN NOT MATCHED THEN
  INSERT
    (a.name,a.create_time,a.update_time)
  VALUES
    (b.name,b.create_time,b.update_time)

--使用ANY 和ALL
> ANY 大于其中一个：大于最小
< ANY 小于其中一个：小于最大
> ALL 大于所有：大于最大
< ALL 小于所有：小于最小

--特别注意：(+) 操作符在左表的字段上表示右连接，在右表的字段上表示左连接。

--左连接
select a.i AF,b.i BF from a,b where a.i = b.i(+);

--右连接
select a.i AF,b.i BF from a,b where a.i(+) = b.i;

7. Oracle树查询

--创建表
create table TB_COMPANY
(
  company_id   INTEGER not null,
  company      VARCHAR2(256),
  up_companyid INTEGER
)

---插入数据
begin
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (0, '总公司', null);
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (1, '分公司1', 0);
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (2, '分公司2', 0);
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (3, '财务部', 1);
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (4, '生产部', 1);
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (5, '设计部', 2);
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (6, '销售部', 2);
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (8, '仓管部', 2);
insert into TB_COMPANY (COMPANY_ID, COMPANY, UP_COMPANYID)values (9, '伙食部', 2);
end;

------------------------------------------------
COMPANY_ID   COMPANY  UP_COMPANYID
0	         总公司	
1	         分公司1	   0
2	         分公司2	   0
3	         财务部	   1
4	         生产部	   1
5	         设计部	   2
6	         销售部	   2
8	         仓管部	   2
9	         伙食部	   2
-------------------------------------------------

--进行树查询
SELECT 
    COMPANY AS "名称", 
    CONNECT_BY_ROOT COMPANY AS "总部", 
    CONNECT_BY_ISLEAF AS "是否叶子节点", 
    LEVEL AS "树深度", 
    SYS_CONNECT_BY_PATH(COMPANY, '/') AS "上下文路径"
FROM TB_COMPANY
    START WITH UP_COMPANYID IS NULL
    CONNECT BY PRIOR COMPANY_ID = UP_COMPANYID;

名称    总部   是否叶子节点 树深度   上下文路径
---------------------------------------------------
总公司	  总公司	0	      1	    /总公司
分公司1  总公司	0	      2	    /总公司/分公司1
财务部	  总公司	1	      3	    /总公司/分公司1/财务部
生产部	  总公司	1	      3	    /总公司/分公司1/生产部
分公司2  总公司	0	      2	    /总公司/分公司2
设计部	  总公司	1	      3	    /总公司/分公司2/设计部
销售部	  总公司	1	      3	    /总公司/分公司2/销售部
仓管部	  总公司	1	      3	    /总公司/分公司2/仓管部
伙食部	  总公司	1	      3	    /总公司/分公司2/伙食部

CONNECT_BY_ROOT [字段] ：返回当前节点的根节点字段属性。
CONNECT_BY_ISLEAF ：判断当前节点是否为叶子节点。
LEVEL ：节点的树深度。
SYS_CONNECT_BY_PATH(列名, '/')：显示详细的上下文路径，并用“/”分隔。
START WITH：指定开始遍历的节点。
CONNECT BY PRIOR ID = 父ID ：从START WITH 指定的节点开始向叶子节点遍历。
CONNECT BY ID = PRIOR 父ID ：从START WITH 指定的节点开始向根节点遍历。

--prior跟子ID放在一边 ===> 就往叶子节点遍历。
--prior跟父ID放在一边 ===> 就往根节点遍历。
--START WITH ====> 查父节点就使用子节点，查子节点就使用父节点。

8.Oracle数据迁移

8.1 DBLink跨库迁移数据

 
--方式一：手动创建
create database link link_demo_01
connect to "用户名" identified by "密码"
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip地址)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =实例名)
))'

--方式二 ：PL/SQL Developer对象栏中右键新建database link


--使用dblink来访问远程数据库导数据，复制表结构和数据 @后面：dblink的名称
create table 表名 as select * from 表名@link_demo_01;

--使用dblin来访问远程数据库查询表或视图中的数据
SELECT * FROM 表名@link_demo_01;

--删除dblink
drop public database link dblink_name;

8.2 EXCEL导入导出

导入excel中数据到表中(选中CV)

9. Oracle执行计划

9.1 概念

当一条SQL语句通过语法检查和语义检查后，SERVER会比较所有的可能的方法所耗费的资源。
利用内部算法对SQL进行解析，计算出资源耗费最少的方法，然后生成解析树和执行计划，最后执行SQL并返回结果。
被物理性执行的方法被称作执行计划。

9.2 常见的表访问方式

1.TABLE ACCESS FULL 全表扫描：读取表中的所有行进行条件匹配
2.TABLE ACCESS BY ROWID 同过ROWID 存取 ROWID伪列：ORACLE自动加在表中每行最后的一列（伪列）
3.TABLE ACCESS BY INDEX SCAN 索引扫描
   在索引块中，既存储了每个索引的键值，也存储了具有该键值行的ROWID
   索引扫描分为两步：1.扫描索引得到对应的ROWID；2.通过ROWID定位到具体的行读取数据；
   
   索引扫描又分为以下五种：
        * INDEX UNIQUE SCAN 唯一扫描索引:针对唯一性索引的扫描，每次最多只返回一条记录
        * INDEX RANGE SCAN 范围扫描索引:在索引列上使用了范围操作符
        * INDEX FULL SCAN 全扫描索引:查询出的数据都必须从索引中可以直接得到
        * INDEX FAST FULL SCAN 快速全扫描索引: 相比索引全扫描不对查询出的数据进行排序
        * INDEX SKIP SCAN 跳跃扫描索引:
           Oracle 9i后提供，有时候复合索引的前导列没有在查询语句中出现，
           oralce也会使用该复合索引，这时候就使用的INDEX SKIP SCAN;
        

##### Oracle分析Sql执行是否使用索引等  PL/sql Developer选中sql ==>F5查看
 执行顺序：同一级的动作执行时遵循最上最右先执行的原则

10.SQL语言分类

DDL:数据定义语言。用于建立，修改，删除数据库对象。如CRATE TABLE ALTER  DROP
DML:数据操作语言。用于改变数据库数据。INSERT UPDATE DELETE.增删改三种
TCL:事务控制语言。用于保证数据原子性的语句。COMMIT ROLBACK.
DQL:数据查询语言。SELECT 
DCL:数据控制语言。用于执行权限的授予和回收，创建用户等。GRANT 授权。REVOKE 回收。

11.SELECT … FOR UPDATE NO WAIT

SELECT ... FOR UPDATE NO WAIT
只要发现查询结果集中有数据被加锁，就会立刻报错。这是为了防止无限期的等待被其它事务锁定的资源。如果没有发现数据被其它事务锁定，就会正常上锁。

12. WITH … AS

--相当于组装了2个虚表
with [虚表名1] as(select...语句块) , [虚表名2] as(select...语句块)

--示例
with
     e as (select * from emp),
     d as (select * from dept)
select * from e, d where e.deptno = d.deptno;

13. PL/SQL

PL/SQL : 是oracle对sql得扩展，在sql得基础上增加了流程控制，循环，定义变量等操作。
过程化结构查询语言。

13.1 PLSQL的变量类型

number / varchar2 / 绑定表中某个列的类型 type / 绑定一个表的数据行 rowtype

13.2 PLSQL 语法

/*
    --PLSQL 基本语法
      declare
        --变量声明区域;
      begin
        --DML/TCL/DQL (CRUD|事务) 操作区域;
      exception
        --异常处理区域;
      end;
*/
  
/*
    --自定义异常
    在declare节中定义例外    out_of   exception ;
    在begin节中可行语句中抛出例外  raise out_of ；
    在exception节处理例外 when out_of then …
*/

DECLARE
  i   number(3);
  
  str varchar2(10) := '哈哈哈';
  
  col tb_test.create_time%type;
  
  obj tb_test%rowtype;
  
  age number(3) :=55;
  
--1.定义无参显式游标
  cursor arow is SELECT t.id,t.update_time FROM tb_test t;
  
  a1 tb_test.id%type;
  b1 tb_test.update_time%type;
  
--2.定义有参显式游标
  cursor arow2(rid tb_test.id%type) is SELECT * FROM tb_test WHERE id = rid; 
  
--3.定义异常：
  out_of_index exception;
  
BEGIN
--4.变量赋值 :=
  i := 11 + 3;
  
--5.流程控制  
  if age <16 then 
    dbms_output.put_line('>>>>骚年');
  elsif age < 30 then
    dbms_output.put_line('>>>>青年');
  elsif age < 50 then
    dbms_output.put_line('>>>>中年');
  else 
    dbms_output.put_line('>>>>老年');
  end if;      
     
--6.SELECT ... INTO 把查询出来的赋值给相应的变量,可以是单个或多个字段也可以是数据行
--SELECT ..INTO  查询不到记录的时候 oracle会抛出 no_data_found异常，返回多行的时候会抛出其它异常
  SELECT t.create_time INTO col FROM tb_test t WHERE t.id = 123;
  
   dbms_output.put_line('====>' || i || '======>' ||
                       to_char(col, 'yyyy-mm-dd hh24:mi:ss'));
  
  SELECT * INTO obj FROM tb_test t WHERE t.id = 1231;
  
  dbms_output.put_line(str || '====>' || obj.name || '::' ||
                       to_char(obj.update_time, 'yyyy-mm-dd hh24:mi:ss'));
  --raise out_of_index;                   
                       
--7.WHILE循环
   WHILE i<30
     loop
       dbms_output.put_line('WHILE===>'||i);
       i := i +1;
     end loop;
     
--8.LOOP循环
    LOOP
       exit WHEN i >50;
       dbms_output.put_line('LOOP===>'||i);
       i := i + 5;
    END LOOP;
    
--9.FOR循环   
      --循环打印1到10 含头含尾
     for y IN 1 ..10  
       loop
         dbms_output.put_line('FOR====>'||y);
       end loop;   
     
--10.FOR 循环遍历表中数据行
     for oj IN(SELECT * FROM tb_test)
       loop
         dbms_output.put_line('FOR OBJ=====>ID '||oj.id ||' ,NAME ' ||oj.name ||' ,CreateTime '||oj.create_time );
       end loop;  
       
--11.游标，类似于JDBC 的ResultSet
     
       --打开游标 此时游标处于第一条记录之前
       open arow;
       loop
         fetch arow into a1,b1;
         exit when arow%notfound;
         dbms_output.put_line('CURSOR====>ID '|| a1 ||',UPDATE TIME '||b1);
       end loop;
       --关闭游标
       close arow;     
     
--12.带参游标
     open arow2(123);
       loop
         fetch arow2 into obj;
         exit when arow2%notfound;
         
         if obj.id = 123 then
           update tb_test t set t.update_time = sysdate; 
         end if;  
         
         dbms_output.put_line( 'PARAM CURSOR===>'||obj.id||' '||obj.name);
       end loop;
       
       commit;--提交事务
       close arow2; 
       
       
--13.异常处理      
/*       i := 1/0;
exception 
       when zero_divide then  --ORACLE 内置除零异常
         dbms_output.put_line('除数不能为0');   */    
    
   SELECT * INTO obj  FROM tb_test WHERE id = 11111111;   
exception
      when no_data_found then  --ORACLE 内置noDataFound异常
        dbms_output.put_line('查询不到数据.....');
        INSERT INTO tb_test values(11111111,1.1,sysdate,sysdate);
        commit;
      when others then  --others表示其它异常
        dbms_output.put_line('Error!!!!');   
END;

13.2 PLSQL 函数

--基本语法 
CREATE OR REPLACE FUNCTION 
   [函数名] ( [参数名] [IN | OUT | IN OUT] [参数类型]... ) 
    RETURN [返回的数据类型] [IS | AS]
BEGIN
    < 函数体执行体 >
END [函数名];

--IN 表示输入 
--OUT表示输出 可被获取 多用于存储过程
--IN OUT 可以作为一个参数传入，同时也可以被获取到

--使用示例
CREATE OR REPLACE function getMax(x IN number,y IN number) 
return number AS
      rst number;
begin
   if x > y then 
     rst := x;
   else 
     rst := y;
   end if;
   return rst;
end;        

--调用函数
SELECT getMax(0,2) FROM DUAL; --返回2

13.3 PLSQL 存储过程

Procedure ：存储过程，是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。

--1.基本语法
CREATE OR REPLACE PROCEDURE [存储过程名称]
    ([参数名] [IN | OUT | IN OUT] [参数类型]...) [IS | AS]    
BEGIN
    < 存储过程执行体 >
END [存储过程名称];

--IN 表示输入 
--OUT表示输出 可被获取 多用于存储过程
--IN OUT 可以作为一个参数传入，同时执行完存储过程后也可以被获取到

--2.使用示例
CREATE OR REPLACE PROCEDURE pro1
    (str1 IN varchar2 ,str2 IN varchar2 ,rst OUT number) AS
    x number;
    y number;
BEGIN
   x:= length(str1);
   y:= length(str2);
   rst := x-y;
   dbms_output.put_line('======>'||rst);
END pro1;
  
--3.测试存储过程
  在PL/SQL Developer 对象栏下procedures下的pro1 右键==>测试==>输入相关参数==>执行
  
--4.删除存储过程 
  drop procedure [存储过程名称];

--5.Mybatis传list参数调用oracle存储过程

--6.其它示例
CREATE OR REPLACE PROCEDURE add_job_history
  (  p_emp_id          job_history.employee_id%type
   , p_start_date      job_history.start_date%type
   , p_end_date        job_history.end_date%type
   , p_job_id          job_history.job_id%type
   , p_department_id   job_history.department_id%type
   )
IS
BEGIN
  INSERT INTO job_history (employee_id, start_date, end_date,
                           job_id, department_id)
    VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);
END add_job_history;

13.4 PLSQL 数组

--语法
TYPE [数组名] IS VARRAY(容量) OF [元素类型]

--声明示例
TYPE strArray IS VARRAY(5) OF VARCHAR2(10);
Type intArray IS VARRAY(5) OF INTEGER;
--注意
  [在Oracle的环境下，数组的起始索引从1开始]
  
--使用示例

DECLARE
    --定义字符串数组类型
    type strArray IS VARRAY(5) OF VARCHAR2(10);
    --定义整数数组类型
    type intArray IS VARRAY(5) OF INTEGER;
    --声明变量 引用数组类型
    names  strArray;
    grages intArray;
    total integer;
BEGIN
    --赋值给数组，不能超过容量
    names  := strArray('Tony', 'Sam', 'Ayan', 'Richil', 'Saber');
    grages := intArray(98, 97, 78, 87, 92);
    
    --[数组名].count 求数组的长度 
    total := names.count; 
    
    dbms_output.put_line('Total:'|| total);
    
    FOR i in 1 .. total 
    LOOP
        dbms_output.put_line('Name: ' || names(i) || ' ,Grage: ' || grages(i));
    END LOOP;
    
END;

--DBMS控制台输出
    Total:5
    Name: Tony ,Grage: 98
    Name: Sam ,Grage: 97
    Name: Ayan ,Grage: 78
    Name: Richil ,Grage: 87
    Name: Saber ,Grage: 92

13.5 PLSQL 触发器

----0.概念
当用户执行了 insert | update | delete 这些操作之后，可以触发一系列其它的动作、业务逻辑，使用触发器可以协助应用在数据库端确保数据的完整性、日志记录 、数据校验等操作。使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在Oracle触发器不仅支持行级触发，还支持语句级触发。


----1.基本语法
create or replace trigger [触发器名称] [ before | after]
  [insert | update | delete ] on [表名]
  [for each row] --行级触发器
  
declare
 --声明部分
begin
 --业务逻辑 
end;

----2.删除触发器
drop trigger [触发器名称]

----3.触发器类型和说明

触发器类型	NEW 和 OLD的使用
------------------------------------------------------------
INSERT型	   NEW 表示将要或者已经新增的数据
UPDATE型	   OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE型	   OLD 表示将要或者已经删除的数据
------------------------------------------------------------

----4.使用 INSERT型 触发器
    --使用:new 拿到插入表中的数据行

create or replace trigger tb_test_insert after insert on tb_test
for each row --行级触发器，不声明的化oracle会报错不能使用new 或者 old 关键字
begin
  dbms_output.put_line('又加了一条记录====>ID '|| :new.id || ',NAME '|| :new.name);
end;    

 --往tb_test插入一条记录，就会执行一次触发器tb_test_insert，输出语句...
 insert into tb_test values(66666,1.02,sysdate,sysdate)

----5.使用update型 触发器 
  --如：更新时 做数据校验
  --同时使用 old 和 new 关键字 拿到旧记录 和 即将要更新的数据
create or replace trigger tb_test_update before update on tb_test
for each row--行级触发器
declare

begin
  if :old.name > :new.name then
    raise_application_error(-20002,'更新的时候，新的NAME值必须大于旧的NAME值');
  end if;
end;

    --尝试更新 oracle报错，抛出ora--20002 '更新的时候，新的NAME值必须大于旧的NAME值'
      update tb_test set name = 0.02 where id = 66666;
      
    
----6.使用delete型触发器
   --使用 :old拿到被删除的数据行
create or replace trigger tb_test_delete after delete on tb_test  
for each row --行级别触发器
begin
    dbms_output.put_line('有一条记录被删除了====>'|| :old.id || '.....' ||:old.name);
end;

13.6 PLSQL 包

--1.package：就是用来封装 存储过程和函数用的。一个package可以存放多个存储过程和函数。

--2.调用方式
   包名.存储过程名
   包名.函数名   
   
--3.作用
   函数重载
   避免重名
   把相关的函数和过程放到一个包下，规范化管理和调用
   
--4.创建包头
create or replace package math_package
as
  --创建两个整数加法存储过程
  procedure add(a in int,b in int,c out int);
  --创建两个是整数的减法函数
  function sub(a in int,b in int ,c out int) return int;
  --创建乘法存储过程
  procedure mul(a in int,b in out int);
  --创建除法函数
  function div(a in int ,b in out number) return number;
end math_package;

--5.创建包体
create or replace package body math_package
as
  --创建两个整数加法存储过程
  procedure add(a in int,b in int,c out int)
  is
  begin
    c := a+b;
    dbms_output.put_line(a || ' + ' || b || ' = ' || c); 
  end add;
  
  --创建两个是整数的减法函数
  function sub(a in int,b in int ,c out int) return int 
  is
  begin
    if a>b then
       c:=a-b;
    else
       c:=b-a;
    end if;
    return c;
    exception 
       when VALUE_ERROR then dbms_output.put_line('The value is error!');     
  end sub;
  
  --创建乘法存储过程
  procedure mul(a in int,b in out int)
  is
  tmp int;
  begin
   tmp:=b;
   b:=a*b;
   dbms_output.put_line(a || ' * ' || tmp || ' = ' || b); 
  end mul;
  
  --创建除法函数
  function div(a in int ,b in out number) return number
  as
  begin
     if b!=0 then
        b:=a/b;
     else
        raise ZERO_DIVIDE;
     end if;
     return b;
     exception 
       when ZERO_DIVIDE then dbms_output.put_line('ZERO_DIVIDE');    
  end div;
  
end math_package;

13.7 PLSQL 隐式游标

当用户在PLSQL中使用DML时，oracle预先定义了一个名为SQL的隐式游标，通过检查隐式游标的属性可以获取与最近执行的SQL语句相关的信息。

1.SQL%isopen : 判断游标是否已经打开，在隐式游标永远为 false。

2.SQL%found : 
       true表示==> SELECT有结果 / UPDATE成功 / DELETE成功 / INSERT成功
       
3.SQL%notfound:
       true表示==> SELECT无结果 / UPDATE失败 / DELETE失败。

4.SQL%rowcount：默认值为0
        SELECT 结果集的行数
        INSERT 插入成功的行数(受影响的行数)
        UPDATE 修改成功的行数(受影响的行数)
        DELETE 删除成功的行数(受影响的行数)
        
--使用示例
--------1.DELETE成功的时候SQL%found返回true，DELETE失败的时候SQL%notfound返回true
--------update操作的时候和DELETE相同   
declare   
begin
  DELETE FROM tb_test WHERE id = 888888; 
  --update tb_test t set t.name = 10.255 where t.id =123;
    if SQL%found then
      dbms_output.put_line('=====>成功 '|| i);
    elsif SQL%notfound then
      dbms_output.put_line('=====>失败' || i); 
    end if;  
  commit;
end;  

--------2.SELECT INTO  查有结果 SQL%found返回true ，查不到抛出NO_DATA_FOUND异常
        --返回多行的时候抛出ora-01422异常
declare 
   i tb_test.id%type;
   obj tb_test%rowtype;
begin
    SELECT t.id INTO i  FROM tb_test t where t.id = 123;
    --SELECT * INTO obj FROM tb_test WHERE id = 123;
    if SQL%found then
      dbms_output.put_line('=====>成功 '|| sql%rowcount);
    elsif SQL%notfound then
      dbms_output.put_line('=====>失败' || sql%rowcount); 
    end if;  
exception
   WHEN NO_DATA_FOUND then
     dbms_output.put_line('====>抛出NO_DATA_FOUND异常了...');
   WHEN others then
     dbms_output.put_line('====>ERROR...');        
end; 

--3 Insert语句执行成功的时候SQL%found返回true，失败的时候抛出异常 进入others区域
declare 
begin
  Insert into tb_test values(666666777,1.2526,sysdate,sysdate);
    if SQL%found then
      dbms_output.put_line('=====>成功 '|| sql%rowcount);
    elsif SQL%notfound then
      dbms_output.put_line('=====>失败' || sql%rowcount); 
    end if;  
  commit;
exception
   WHEN NO_DATA_FOUND then
     dbms_output.put_line('====>抛出NO_DATA_FOUND异常了...');
   WHEN others then
     dbms_output.put_line('====>ERROR...');
end;

13.8 RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR 函数:
     该函数是将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或者前台开发语言)

RAISE_APPLICATION_ERROR( error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
    --1.error_number_in ：自定义的错误码，容许从 -20000 到 -20999 之间，
                         -- 这样就不会与 ORACLE 的任何错误代码发生冲突。
    --2.error_msg_in：长度不能超过 2k，否则截取 2k

13.9 使用触发器实现oracle主键自增

• 当插入数据的主键值为NULL的时候，调用序列的值作为主键值

14. 数据库表设计

14.01 带LINK表设计

 
create table PHASEDEF
(
  objectidentifier NUMBER(19) not null,
  oid              NUMBER(19) not null,
  createtime       TIMESTAMP(6) not null,
  edittime         TIMESTAMP(6) not null,
  internalname     VARCHAR2(255 CHAR) not null,
  name_zh_cn       VARCHAR2(255 CHAR) not null,
  name_en_us       VARCHAR2(255 CHAR) not null,
  name_ja_jp       VARCHAR2(255 CHAR) not null
)

create table LIFECYCLEDEF
(
  objectidentifier NUMBER(19) not null,
  oid              NUMBER(19) not null,
  createtime       TIMESTAMP(6) not null,
  edittime         TIMESTAMP(6) not null,
  internalname     VARCHAR2(255 CHAR) not null,
  name_zh_cn       VARCHAR2(255 CHAR) not null,
  name_en_us       VARCHAR2(255 CHAR) not null,
  name_ja_jp       VARCHAR2(255 CHAR) not null
)

create table LIFECYCLEDEFTOPHASEDEF
(
  objectidentifier NUMBER(19) not null,
  oid              NUMBER(19) not null,
  createtime       TIMESTAMP(6) not null,
  edittime         TIMESTAMP(6) not null,
  rolea            NUMBER(19) not null,
  roleaclass       VARCHAR2(255 CHAR) not null,
  roleb            NUMBER(19) not null,
  rolebclass       VARCHAR2(255 CHAR) not null,
  rowindex         NUMBER(19) not null,
  revisable        NUMBER(19) not null,
  editable         NUMBER(19) not null,
  deletable        NUMBER(19) not null
)

SELECT * FROM PhaseDef     --具体的生命周期阶段 (internalname, name_zh_cn, name_en_us, name_ja_jp)  正在工作 正在审阅 重新工作 已发布 已取消
SELECT * FROM LifeCycleDef --生命周期定义 (internalname,name_zh_cn, name_en_us, name_ja_jp) 
SELECT * FROM LifeCycleDefToPhaseDef 
--生命周期定义_阶段 LINK 把排序下标放置到LINK上 
--（rolea, roleaClass, roleb, rolebClass, rowIndex）

--加入Link的意义,可以使得生命周期的阶段 得到复用，把不同的排序又放在LiNK上 
SELECT b.*,l.rowindex
  FROM LifeCycleDef a, PhaseDef b, LifeCycleDefToPhaseDef l
 WHERE l.rolea = a.oid
   AND l.roleb = b.oid
   AND a.internalname = '零部件规格书生命周期'
   ORDER BY l.rowindex ASC

14.02 无LINK单字段直连 -全局枚举 (1对多)

SELECT * FROM Globalenumeration;--枚举定义表 为 “一”方
SELECT * FROM enumerationvalue;--枚举值表 为 “多”方，直接使用enumerationId 直接关联枚举定义表 
  
SELECT a.enumerationname BIG_KEY, --大KEY
         b.internalname    SMALL_KEY, --小KEY
         b.value_zh_cn,
         b.value_en_us,
         b.value_ja_jp
    FROM Globalenumeration a, enumerationvalue b
   WHERE a.oid = b.enumerationid
     AND a.enumerationname = 'ECO_changeCauseType'

create table GLOBALENUMERATION
(
  objectidentifier NUMBER(19) not null,
  oid              NUMBER(19) not null,
  createtime       TIMESTAMP(6) not null,
  edittime         TIMESTAMP(6) not null,
  enumerationname  VARCHAR2(255 CHAR) not null,
  name_zh_cn       VARCHAR2(255 CHAR) not null,
  name_en_us       VARCHAR2(255 CHAR) not null,
  name_ja_jp       VARCHAR2(255 CHAR) not null,
  description      VARCHAR2(4000 CHAR)
)

create table ENUMERATIONVALUE
(
  objectidentifier NUMBER(19) not null,
  oid              NUMBER(19) not null,
  createtime       TIMESTAMP(6) not null,
  edittime         TIMESTAMP(6) not null,
  enumerationid    NUMBER(19) not null,
  rowindex         NUMBER(19) not null,
  internalname     VARCHAR2(255 CHAR),
  value_zh_cn      VARCHAR2(255 CHAR),
  value_en_us      VARCHAR2(255 CHAR),
  value_ja_jp      VARCHAR2(255 CHAR),
  description      VARCHAR2(255 CHAR)
)

14.03

stringDefination 
stringValue 
将定义和值分开的意义是，定义可以复用，所以拆分

create table STRINGDEFINITION
(
  objectidentifier NUMBER(19) not null,
  oid              NUMBER(19) not null,
  createtime       TIMESTAMP(6) not null,
  edittime         TIMESTAMP(6) not null,
  internalname     VARCHAR2(255 CHAR) not null,
  unit             VARCHAR2(255 CHAR)
)


create table STRINGVALUE
(
  objectidentifier NUMBER(19) not null,
  oid              NUMBER(19) not null,
  createtime       TIMESTAMP(6) not null,
  edittime         TIMESTAMP(6) not null,
  objectclassname  VARCHAR2(255 CHAR) not null,
  objectclassid    NUMBER(19) not null,
  definitionid     NUMBER(19) not null,
  value            VARCHAR2(1000 CHAR)
)

14.04 浮点数 数据库列类型选择

• 如果是浮点数，数据库均采用字符串做列类型，到java层面进行类型转换

14.05 数据库保留字段以及对象属性字段维护问题

• 在软件的数据库表设计中，为了支持灵活性和未来变更，有一种做法是设计一种保留字段。

• 优点：
  1、扩展性，你可以想像到，如果你的数据表有超过1000W记录的时候，去修改数据库表结构会造成什么结果，而使用预留的字段将避免在一个非常大的数据表上增加字段造成的问题；
  2、某些视图，会因为增加了字段，造成视图无法使用，需要重新编译。

• 问题：
  1、预留字段的管理问题，既然使用了预留字段，就有其存在的意义，因此需要严格的文档去管理这些字段，一旦预留字段被使用，需要登记，防止混乱的管理导致数据库字段丢失、混乱。（使用专门的表去管理业务表的字段属性定义）。
  2、对于一般性的基础数据表，5个预留字段足矣，对于业务数据表，可考虑10~12个 。