MySql和Oracle介绍：

首先他们二者都为关系型数据库(对比于非关系型数据库，如MongoDB，Redis等而言)，

其次就是二者的定位不同：

mysql（一般安装解压版）：免费（目前也开始收费），开源，中小型项目一般使用，安装（服务器）即可用

oracle（一般不安装）：收费，不开源，性能和稳定性都比mysql强，大型项目一般使用，下载客户端instantclient(在Oracle官网下载)并使用连接工具关联访问数据库方可使用

一：数据类型

Mysql的数据类型：

类型大小范围（有符号）范围（无符号）用途
TINYINT1 Bytes(-128，127)(0，255)小整数值
SMALLINT2 Bytes(-32 768，32 767)(0，65 535)大整数值
MEDIUMINT3 Bytes(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT或INTEGER4 Bytes(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
BIGINT8 Bytes(-9 223 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615)极大整数值
FLOAT4 Bytes(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度 浮点数值
DOUBLE8 Bytes(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度 浮点数值
DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值

Oracle的数据类型（常见的已加粗）

类型中文说明限制条件其他说明
CHAR固定长度字符串最大长度2000 bytes如果不指定长度，缺省为1个字节长（一个汉字为2字节）
VARCHAR2可变长度的字符串最大长度4000 bytes最常用的数据类型，可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。
NCHAR根据字符集而定的固定长度字符串最大长度2000 bytes
NVARCHAR2根据字符集而定的可变长度字符串最大长度4000 bytes
DATE日期（日-月-年）DD-MM-YY（HH-MI-SS）从公元前4712年1月1日到公元4712年12月31日的所有合法日期
LONG超长字符串最大长度2G（231-1）足够存储大部分著作
RAW固定长度的二进制数据最大长度2000 bytes可存放多媒体图象声音等
LONG RAW可变长度的二进制数据最大长度2G可存放保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。
BLOB二进制数据最大长度4G
CLOB字符数据最大长度4G
NCLOB根据字符集而定的字符数据最大长度4G
BFILE存放在数据库外的二进制数据最大长度4G
ROWID数据表中记录的唯一行号10 bytes********.****.****格式，*为0或1
NROWID二进制数据表中记录的唯一行号最大长度4000 bytes
NUMBER(n)整数类型 n表示数字长度
NUMBER(P,S)数字类型 P为总位数，S为小数位数
DECIMAL(P,S)数字类型 P为总位数，S为小数位数
INTEGER整数类型 小的整数
FLOAT浮点数类型双精度NUMBER(38)，常用作小数的数据类型
REAL实数类型精度更高NUMBER(63)，常用作实数的数据类型

关键区分点：

编号ORACLEMYSQL注释
1NUMBERint / DECIMALDECIMAL就是NUMBER(10,2)这样的结构INT就是是NUMBER(10)，表示整型； MYSQL有很多类int型，tinyint mediumint bigint等，不同的int宽度不一样
2DateDATATIME日期字段的处理 MYSQL日期字段分DATE和TIME两种，ORACLE日期字段只有DATE，包含年月日时分秒信息，用当前数据库的系统时间为 SYSDATE, 精确到秒，或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’)年-月-日 24小时:分钟:秒的格式YYYY-MM-DD HH24:MI:SS TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC.日期型字段转换成字符串函数TO_CHAR(‘2001-08-01’,’YYYY-MM-DD HH24:MI:SS’) 日期字段的数学运算公式有很大的不同。MYSQL找到离当前时间7天用DATE_FIELD_NAME ＞ SUBDATE（NOW（），INTERVAL 7 DAY）ORACLE找到离当前时间7天用 DATE_FIELD_NAME ＞SYSDATE - 7; MYSQL中插入当前时间的几个函数是：NOW()函数以`'YYYY-MM-DD HH:MM:SS'返回当前的日期时间，可以直接存到DATETIME字段中。CURDATE()以’YYYY-MM-DD’的格式返回今天的日期，可以直接存到DATE字段中。CURTIME()以’HH:MM:SS’的格式返回当前的时间，可以直接存到TIME字段中。例：insert into tablename (fieldname) values (now()) 而oracle中当前时间是sysdate
3INTEGERint / INTEGERMysql中INTEGER等价于int，而Oracle没有int
4CONSTANT VARCHAR2(1)mysql中没有CONSTANT关键字从ORACLE迁移到MYSQL,所有CONSTANT常量只能定义成变量
5自动增长的序列自动增长的数据类型MYSQL有自动增长的数据类型，插入记录时不用操作此字段，会自动获得数据值。ORACLE没有自动增长的数据类型，需要建立一个自动增长的序列号，插入记录时要把序列号的下一个值赋于此字段。
6NULLNULL空字符的处理 MYSQL的非空字段也有空的内容，ORACLE里定义了非空字段就不容许有空的内容。按MYSQL的NOT NULL来定义ORACLE表结构, 导数据的时候会产生错误。因此导数据时要对空字符进行判断，如果为NULL或空字符，需要把它改成一个空格的字符串。

二：库的管理

mysql可以创建数据库，而oracle没有这个操作，oracle只能创建实例

MySql创建数据库的操作：

create database 数据库名; 

create database 数据库名 character set 字符集;

Oracle创建用户的操作：

create user 自定义用户名
identified by 自定义密码
default tablespace 已存在的表空间名称（注意不是数据文件名称）

三：表的管理

建表：

建表的通用语法：

Create table 表名（
字段1 数据类型 [约束][default 默认值],
字段2 数据类型 [约束][default 默认值],
...
字段n 数据类型 [约束][default 默认值]
）;

MySql建表：

1、mysql没有number、varchar2()类型；

2、mysql可以声明自增长：auto_increment；

3、mysql有double类型；

create TABLE emp(
  eno INT PRIMARY KEY AUTO_INCREMENT,--eno 为主键且自动增长
  ename VARCHAR(20) NOT NULL UNIQUE,--ename 数据类型为长度20的varchar字符串且有非空约束和唯一约束
  job VARCHAR(10) DEFAULT '员工',--job 数据类型为长度10的varchar字符串且有默认值为员工
  mgr INT(10),
  hiredate  DATE,
  comm DOUBLE 
);

Oracle建表：

1、oracle没有double类型、有int类型但多数会用number来代替int；

2、oracle不可以声明自增长：即不能写auto_increment，主键自带自增长；

3、oracle小数只有float类型；

create table emp(
  empno number(10) primary key ,--主键
  ename varchar2(20) not null unique,--不能为空,唯一
  job varchar2(10) default '经理',--默认值，用单引号
  mgr number(10),
  hiredate date,--默认格式DD-MM-YY
  sal number(10,2),
  comm float,
  deptno number(10)
);

删表：

关键区分点：

Oracle没有if exists关键字，也没用类似if exists的SQL语法。

MySQL删表：

 DROP TABLE IF EXISTS 表名;
    或drop table if exists 表名；

Oracle删表：

 drop table 表名；

四：表的列操作

添加列：

MySQL:

方式1： alter table 表名 add column 字段 数据类型;
方式2： alter table 表名 add column 字段1 数据类型, add column 字段2 数据类型;

 注：其中关键字column可有可无。

Oracle：

方式1： alter table 表名 add 字段 数据类型;
方式2： alter table 表名 add (字段 数据类型);
方式3： alter table 表名 add (字段1 数据类型, 字段2 数据类型); 

 注：对于方式1，只有添加单列的时候才可使用，对于添加多列时需要使用方式3，
    好处在于不用再像MySQL那样重复使用add column关键字添加列了。

删除列：

MySQL:

方式1： alter table 表名 drop column 字段;
方式2： alter table 表名 drop column 字段, drop column 字段;

 注：其中关键字column可有可无。

Oracle：

方式1： alter table 表名 drop column 字段;;
方式2： alter table 表名 drop (字段);
方式3： alter table 表名 drop (字段1,字段2);

 注：对于方式1，只有添加单列的时候才可使用，对于添加多列时需要使用方式3，
    好处在于不用再像MySQL那样重复使用drop column关键字添加列了。

修改列字段名：

MySQL:

 alter table 表名 change column 原来字段 新的字段 字段类型(必须);

Oracle：

 alter table 表名 rename column 原来字段 to 新的字段;  注：不能有字段类型

修改列字段类型：

关键区分点：

**Oracle在列有数据的时候，无法修改列类型；没有数据时可以。 **

而在MySQL中，无论列是否有数据都可以修改列类型。

但是当有数据时，直接修改列类型都可能对数据造成丢失等，所以一般需要结合具体的业务来对列数据做处理后，再修改列类型类型。所以修改列的类型并非使用SQL语句进行一步到位的修改，而是通过以下流程：

A. 添加临时列

B. 将需要更改的列的值经过类型转换的验证后，赋值给临时列

C. 删除原有列

D. 将临时列的列名修改为原有列列名

MySQL:

alter table 表名 modify column 字段名 数据类型;

Oracle：

alter table 表名 modify(字段名 数据类型 约束条件);

五：索引

关键区分点：

在整个数据库内，MySQL的索引可以同名，也就是说MySQL的索引是表级别的；

但是Oracle索引不可以同名，也就是说Oracle的索引是数据库级别的。

创建索引：

MySQL和Oracle相同：

create index 索引名 on 表名 (表的某个字段名);

删除索引：

MySQL：

alter table 表名 drop index 索引名

Oracle：

drop index 索引名

查询表的索引

MySQL：

show index from 表名

Oracle：

oracle中表的索引信息存在 user_indexes 和 user_ind_columns 两张表里面，
其中:

user_indexes 系统视图存放是索引的名称以及该索引是否是唯一索引等信息，

user_ind_columns 系统视图存放的是索引名称，对应的表和列等

select * from user_ind_columns where table_name = upper('表名')   (这种写法不推荐，因为存在使用*号)
select  index_name, table_name, column_name from user_ind_columns where table_name = upper('表名') 
(推荐写法，将需要查找的字段信息列出来，代替*号)

六：空字符串问题

Oracle中空字符串''就是null（也就是说，只有null，没有空字符），而MySQL是区分null和''的。

对于使用语句：

select * from table1 where user_name <> ''

来查询列user_name不为空（不为null且不为空字符）时，Oracle会查不出任何结果，而MySQL可以正常运行。

这里MySQL之所以可以得到正确结果，是因为比较符号<>会先将列为null的内容进行过滤，然后再比较内容是否为空字符串。

这就要求一方面，以后在编写代码的时候，尽量保证不会往数据库插入空字符串''这样的值，要么保持有数据，要么保持为null。另外，对于MySQL中已经同时存在Null和''时，所有判断是否为null或者''的地方改为判断列的长度是否为0。

七：基本语法

1.变量声明的方式不同：

mysql 使用DECLARE定义局部变量. 
   定义变量语法为:  DECLARE var_name[,...] type [DEFAULT value] 要给变量提供一个默认值，需要包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

MySQL：

DECLARE li_index INTEGER DEFAULT 0

Oracle：

li_index NUMBER := 0

2.变量的赋值方式不同：

oracle变量赋值使用:=  
   mysql 使用赋值使用set关键字. 将一个值赋给一个变量时使用"="。

MySQL：

SET lv_inputstr = iv_inputstr

Oracle：

lv_inputstr := iv_inputstr

3.退出（跳出）的语句不同

oracle: 如果exit语句在循环中就退出当前循环.如果exit语句不再循环中,就退出当前过程或方法. 
   Mysql: 如果leave语句后面跟的是存储过程名,则退出当前存储过程. 如果leave语句后面跟的是lable名. 则退出当前lable.

MySQL：

label_name:
while 条件 do
    leave label_name;
end while label_name;

Oracle：

while 条件 loop
exit;
end loop;

4.定义游标的方式不同

oracle可以先定义游标,然后给游标赋值. 
    mysql定义游标时就需要给游标赋值

MySQL：

#定义游标  
declare fetchSeqCursor cursor for select seqname, value from sys_sequence;  
#使用游标  
open fetchSeqCursor；  
#fetch数据  
fetch cursor into _seqname, _value;  
#关闭游标  
close fetchSeqCursor;

Oracle：

declare 
	定义游标;
	定义变量;-- 用于存储游标中取出的一行或一列数据，需要匹配游标存储的数据类型
begin 
	open 游标名;
	loop
		fetch 游标名 into 变量;
		exit when 游标名%notfound;
		
		--遍历游标数据时的操作语句
        
	end loop;
	close 游标名; 
end;

5.注释方式不同

规范建议：单行用--, 多行/* */

MySQL：

-- 单行注解  
或 /* … */ 
或 #

Oracle：

-- 单行注解  
或 /** …  */ 
或/* … */

7.自带日期时间函数格式不同

1. MYSQL日期字段分DATE和TIME两种. 
   ORACLE日期字段只有DATE，包含年月日时分秒信息. 
   
2. mysql中取当前系统时间为now()函数,精确到秒. 
   oracle中取当前数据库的系统时间为SYSDATE, 精确到秒.

MySQL：

%Y-%m-%d %H:%i:%s

Oracle：

yyyy-MM-dd hh:mi:ss

8.日期加减的操作不同

1. MYSQL日期字段分DATE和TIME两种. 
   ORACLE日期字段只有DATE，包含年月日时分秒信息. 
   
2. mysql中取当前系统时间为now()函数,精确到秒. 
   oracle中取当前数据库的系统时间为SYSDATE, 精确到秒.

MySQL：

date_sub/add('某个日期时间', interval 时间数 时间种类名)
	日期相加: date_add(now(), INTERVAL 180 DAY)  -- 加180天
	日期相减: date_sub('1998-01-01 00:00:00', interval '1 1:1:1' day_second) -- 结果：1997-12-30 22:58:59

timediff(time1,time2);  -- 两个时间相减 time1减time2，返回差值。

Oracle：

当前时间加N天: 
	sysdate+N
当前时间减N天: 
	sysdate-N

9.字符串连接符不同

1. oracle使用||连接字符串,也可以使用concat函数. 但Oracle的concat函数只能连接两个字符串.
   Mysql使用concat方法连接字符串. MySQL的concat函数可以连接一个或者多个字符串,如
       mysql> select concat('10');   结果为: 10.
       mysql> select concat('11','22','33','aa'); 结果为: 112233aa
2. "||"在Mysql是与运算

MySQL：

set result =concat(str1,str2);

Oracle：

result  := str1||str2;

相同点：

事务回滚都是：

ROLLBACK;

GOTO语句都是：

GOTO check_date;

/*goto语句不符合结构化程序设计的原则，因为无条件转向使得程序的结构没有规律、可读性差。
对于初学者来说应尽量避免使用goto语句，但如果使用goto语句能够大大地提高程序的执行效率，也可以使用。*/

八：约束

总所周知，数据库约束常见的有五种，分别是：

主键约束：使用在主键字段上，值不能为空，并且值必须唯一，primary key

唯一约束：可用使用在任意字段上，如果值可以为null，但是只能有一个数据为null，值必须唯一,但是可以为null，unique

非空约束：可用使用在任意字段上，字段值不能为空，not null

外键约束：一般都是使用在关联字段上，数据库会保证当前表中的关联字段完整

（在对关联表进行删除和更新操作时，会来当前主表判断是否存在引用关联（当前字段值有效），如果存在就不允许关联表删除关联行数据或更新关联主键字段数据）

所以在实际开发我们一般，

​ 1.针对需要进行delete操作的表，去掉外键约束使用，使用外连接用形式外键进行关联，便于连表查询。

​ 2.或者采用常用手段，如果想要设置外键约束，来保证数据安全，那么建议在开发需求中进行逻辑删除操作（在关联表中定义字段描述删除状态，使用update更新数据行状态字段为删除状态，而不是使用delete删除数据）

检查约束：oracle和sqlserver特有的：用于限制字段的取值范围，mysql一般使用枚举类型模拟实现该功能

因此，检查约束也为区分Oracle和MySQL的重要特点，其使用语法如下：

create table myuser( 
	pid number(10) primary key, 
    username VARCHAR2(20) UNIQUE NOT NULL,
	sex VARCHAR2(2) CHECK(sex in ('男','女')),-- 值范围检查
    -- sex VARCHAR2(2) CHECK(sex = '男' or sex = '女') -- 值判断检查
	age NUMBER CHECK(age > 0 and age < 110),-- 数值范围
	password VARCHAR2(20) CHECK(length(password)=6) --长度检查
);

九：函数

说明：

  1. MySQl使用内置函数获取结果时，如果没有操作具体表字段，那么结尾可以没有from 表名，如果操作表字段那么必须带上表名
  2. oracle使用内置函数获取结果时，不管是否操作表字段，结尾都必须使用from 表名，如果不是操作具体的表字段，那么使用虚拟表名 dual

注：

dual是sys用户下的一张内部表，所有用户都可以使用DUAL名称访问，无论什么时候这个表总是存在，并且不论进行何种操作（不要删除记录），它都只有一条记录，所以在执行如下的一二三四这四种返回值只有唯一的一个的函数时，Oracle语句后面都固定加一个from dual

数字函数：

MySQL：

(一)
round(1.23456,4) -- 表示将1.23456四舍五入并保留小数点后四位 结果1.2345
rund(1.2345) -- 表示将1.2345四舍五入 结果1
说明：
round(x,d)/round(x) -- x指要处理的数，d是指保留几位小数
d可以是负数，这时是指小数点左边的d位整数位为0,同时小数位均为0；round(x)  ,其实就是round(x,0),也就是默认d为0；
用法：
select round(1.23456,4) value

(二)
abs(-1) -- 表示取-1的绝对值 结果1
用法：select abs(-1) value

(三)
ceiling(-1.001) -- 表示返回大于或等于-1.001的最小整数值 结果-1
说明：
函数返回大于或等于数字的最小整数值。
用法：
select ceiling(-1.001) value

(四)
floor(-1.001) -- 表示返回小于或等于-1.001的最大整数值 结果-2
说明：
函数返回小于或等于数字的最大整数值
用法：
select floor(-1.001) value

(五)
Max(expr)/Min(expr) -- 返回一组值中的最大值/最小值
用法：
select max(user_int_key) from sd_usr;

Oracle：

(一)
round(1.23456,4) -- 说明同上 
用法：
ORACLE：select round(1.23456,4) value from dual

(二)
abs(-1) -- 说明同上
用法：select abs(-1) value from dual

(三)
ceil(-1.001) -- 方法名与MySQL不同，但说明相同
用法：
select ceil(-1.001) value from dual

(四)
floor(-1.001)-- 说明同上
用法：
select floor(-1.001) value from dual

(五)
Max(expr)/Min(expr) --说明和用法同上

字符串函数：

MySQL：

(一)
ascii(str) -- 返回字符串 str 最左边的那个字符的 ASCII 码值

说明：如果 str 是一个空字符串，那么返回值为 0。如果 str 是一个 NULL，返回值也是 NULL.
用法：
select ascii('a') value

(二)
CHAR(N,...) -- 以整数类型解释参数，返回这个整数所代表的 ASCII 码值给出的字符组成的字符串。
说明：如果为NULL值将被忽略.
用法：
select char(97) value -- 结果a

(三)
REPLACE(str,from_str,to_str) -- 在字符串 str 中所有出现的字符串from_str 均被 to_str 替换，然后返回这个字符串.
用法：
SELECT REPLACE('abcdef', 'bcd', 'ijklmn') value -- 结果aijklmnef

(四)
INSTR('sdsq','s') -- 返回第一次出现的字符串在另一个字符串中的位置。此函数执行不区分大小写的搜索。
用法：
select INSTR('sdsq','s') value（从默认的位置1开始）-- 结果3

(五)
substring('abcd',2,2) -- 字符串中提取子字符串，从位置2开始，截取长度为2
用法：
select substring('abcd',2,2) value -- 结果cd

(六)
locate(‘ab’,’abcdefg’) -- 返回字符串abcdefg中第一次出现的子字符串ab的位置。
说明：
如果在原始字符串中找不到子字符串，则此函数返回0。此函数执行不区分大小写的搜索。
用法：
SELECT locate('ab', 'abcdefg') VALUE -- 结果1

(七)
char_length() -- 返回字符串的长度（以字符为单位）空格也算一个。
用法：
SELECT char_length('AAAASDF') VALUE -- 结果7

(八)
REPLACE('abcdef', 'bcd', 'ijklmn') -- 用新的子字符串替换字符串中所有出现的子字符串。
注意：此函数执行区分大小写的替换。
用法：
SELECT REPLACE('abcdef', 'bcd', 'ijklmn') value -- 结果aijklmnef

(九)
LPAD('abcd',14, '0') -- 用0左填充abcd字符串，直到长度为14
用法：
select LPAD('abcd',14, '0') value from dual 结果0000000000abcd

(十)
UPPER(iv_user_id) -- 将字符串转换为大写字母
用法：
select UPPER(user_id) from sd_usr; -- 查询sd_usr表中user_id字段的所有值，并将其每个结果字符串转换成大写

(十一)
LOWER(iv_user_id) -- 将字符串转换为小写
用法：
select LOWER(user_id) from sd_usr; -- 查询sd_usr表中user_id字段的所有值，并将其每个结果字符串转换成小写

Oracle：

(一)
ascii(str) -- 说明同上
用法：
select ascii('a') value from dual

(二)
CHAR(N,...) -- 说明同上
用法：
select chr(97) value from dual -- 结果a

(三)
REPLACE(str,from_str,to_str) -- 说明同上
用法：
SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual -- 结果aijklmnef

(四)
INSTR('sdsq','s',2) -- 说明同上,但参数个数不同，2代表默认位置从2开始
用法：
select INSTR('sdsq','s',2) value from dual（要求从位置2开始）-- 结果2

(五)
substr('abcd',2,2) -- 说明同上
用法：
select substr('abcd',2,2) value from dual -- 结果cd

(六)
instr(‘abcdefg’,’ab’) -- 说明同上，只不过字串和总串的位置换了
用法：
SELECT instr('abcdefg', 'ab') VALUE FROM DUAL -- 结果1

(七)
length（str） -- 说明同上，只不过函数名变了而已，使用效果相同
用法：
SELECT length('AAAASDF') VALUE FROM DUAL

(八)
REPLACE('abcdef', 'bcd', 'ijklmn') -- 说明同上
用法：
SELECT REPLACE('abcdef', 'bcd', 'ijklmn') value from dual

(九)
LPAD('abcd',14, '0') -- 说明同上
用法：
select LPAD('abcd',14, '0') value from dual

(十)
UPPER(iv_user_id) -- 说明同上
用法：
select UPPER(user_id) from sd_usr;

(十一)
LOWER(iv_user_id) -- 说明同上
用法：
select LOWER(user_id) from sd_usr;

控制流函数：

MySQL：

(一)判空函数
IFNULL(u.email_address, 10)
或
ISNULL(u.email_address) -- 判断表达式是否为NULL。如果expression为NULL，则此函数返回1.否则，返回0。

用法：
select u.email_address, IFNULL(u.email_address, 10) value from sd_usr u
(如果u.email_address=NULl,显示结果中是10，而不是在DB中用10替换其值)
或
select u.email_address, ISNULL(u.email_address) value from sd_usr u（如果u.email_address是NULL, 就显示1<true>,否则就显示0<false>）

(二)IF语句
SELECT IF(1>2,2,3); -- 如果判断1>2的结果为true，则返回2，false则返回3

(三)IF-THEN语句
IF expression THEN 
   statements;
END IF; 
说明：如果表达式求值为TRUE，则将执行语句，否则，控件将传递给后面的下一个语句END IF。

(四)IF-ELSE语句
IF expression THEN
   statements;
ELSE
   else-statements;
END IF; 
说明：如果表达式求值为TRUE，则将执行语句，否则，则执行另一个语句。

(五)IF-ELSEIF-ELSE语句
IF expression THEN
   statements;
ELSEIF elseif-expression THEN
   elseif-statements;
...
ELSE
   else-statements;
END IF; 
说明：如果表达式（expression）求值为TRUE，则IF分支中的语句（statements）将执行;如果表达式求值为FALSE，则如果elseif_expression的计算结果为TRUE，MySQL将执行elseif-expression，否则执行ELSE分支中的else-statements语句。

MySQL IF语句示例

以下示例说明了如何使用IF-ESLEIF-ELSE语句。GetCustomerLevel() 存储过程接受两个参数的客户数量和客户的水平。

  • 首先，它从customers表中获得信用额度。
  • 然后，根据信用额度，它决定了客户等级：PLATINUM，GOLD，和SILVER。

参数p_customerlevel存储客户的级别，并由调用程序使用。

DELIMITER $$
 
CREATE PROCEDURE GetCustomerLevel(
    in  p_customerNumber int(11), 
    out p_customerLevel  varchar(10))
BEGIN
    DECLARE creditlim double;
 
    SELECT creditlimit INTO creditlim
    FROM customers
    WHERE customerNumber = p_customerNumber;
 
    IF creditlim > 50000 THEN
        SET p_customerLevel = 'PLATINUM';
    ELSEIF (creditlim <= 50000 AND creditlim >= 10000) THEN
        SET p_customerLevel = 'GOLD';
    ELSEIF creditlim < 10000 THEN
        SET p_customerLevel = 'SILVER';
    END IF;
 
END $$

DELIMITER ;

Oracle：

(一)判空函数
nvl(u.email_address, 10)
用法：
select u.email_address, nvl(u.email_address, 10) value from sd_usr u 
(如果u.email_address=NULl,就在DB中用10替换其值)

(二)Decode替换函数
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF
用法：
查询员工表获取所有的员工名字，并将名字为ALLEN的员工名改成张三，名字为WARD的员工名改成李四，另外没有匹配到的员工姓名改成无名
SELECT ENAME from EMP;
SELECT DECODE(ename, 'ALLEN', '张三','WARD', '李四','无名') from EMP;

(三)case when语句
说明：
类似if....else if...esle
但要注意when 的执行顺序，当第一个when满足条件时，便结束查询不会继续判断其它的 when 条件
建议：当 case when 和 decode 等价，且判断语句不超过 10 行时，使用 decode（语法简洁）
z
CASE
WHEN comparison_expr1 THEN return_expr1
WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr
END 
用法：
查询员工表中所有员工的职位，如果员工的职位名称为CLERK则返回业务员，否则判断是否为MANAGER,如果是则返回经理...
如果所有条件都不符合，则返回无业
SELECT ENAME,
case 
	WHEN JOB='CLERK' THEN '业务员' 
	WHEN JOB='MANAGER' THEN '经理' 
-- 	.....
	ELSE '无业' 
end as 职位
from EMP

类型转换函数：

MySQL：

(一)
date_format/ time_format -- 按指定格式格式化日期/格式化时间。
用法：
select date_format(now(),'%Y-%m-%d');
/select time_format(now(),'%H-%i-%S');

(二)
STR_TO_DATE(str,format) -- 返回基于字符串和格式的日期。
用法：
SELECT STR_TO_DATE('2004-03-01', '%Y-%m-%d') VAULE -- 结果2004-03-01
SELECT STR_TO_DATE("2017,8,14 10,40,10", "%Y,%m,%d %h,%i,%s"); -- 结果2017-08-14 10:40:10

(三)
cast(value AS datatype) -- 将（任何类型的）值转换为指定的数据类型。
用法：
  -- 整数 : SIGNED    
  -- 无符号整数 : UNSIGNED  
select cast(-1.002 as SIGNED) value-- 将-1.002转化为有符号整数类型，一般默认定义的数据类型为signed（有符号类型）
SELECT CAST("2017-08-29" AS DATE);  -- 结果2017-08-29(数据类型变成了date)

(四)
CAST("123" AS SIGNED INTEGER) -- SIGNED INTEGER:带符号的整形

MySQL常见的绝大部分日期格式附表：

格式描述
％a工作日缩写名称（周日至周六）
％b缩写的月份名称（1月至12月）
％C数字月份名称（0到12）
％D作为数值的月中的某一天，后跟后缀（第1，第2，第3，......）
％d作为数值的月份日期（01到31）
％e每月的某一天作为数值（0到31）
％f微秒（000000至999999）
％H小时（00到23）
％h小时（00到12）
%I小时（00到12）
％i分钟（00至59）
％j一年中的某一天（001至366）
％k小时（0到23）
％l小时（1到12）
％M月份名称（1月至12月）
％m月份名称作为数值（00到12）
％p上午或下午
％r时间为12小时AM或PM格式（hh：mm：ss AM / PM）
％S秒（00到59）
％s秒（00到59）
％T24小时格式的时间（hh：mm：ss）
％U星期日是星期的第一天（00到53）的星期
％u星期一是一周的第一天（00到53）的星期
％V星期日是星期的第一天（01到53）的星期。与％X一起使用
％v星期一是星期的第一天（01到53）的星期。与％X一起使用
％W工作日名称全部（周日至周六）
％w星期日= 0和星期六= 6的星期几
％X星期日是一周的第一天的一周。与％V一起使用
％x星期一是一周的第一天的一周。与％V一起使用
％Y年份为数字，4位数值
％y年份为数字，2位数值

Oracle：

(一)
TO_CHAR(SQLCODE) -- 按指定格式格式化日期/格式化时间。
用法：
select to_char(sysdate,'yyyy-mm-dd') from dual;
 /select to_char(sysdate,'hh24-mi-ss') from dual;

(二)
to_date(str,format) -- 按指定格式格式化日期/格式化时间。 
注意：在SQL中不区分大小写，MM和mm会被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟。
用法：
SELECT to_date('2009-3-6','yyyy-mm-dd') VAULE FROM DUAL 

(三)
trunc() -- 函数截取日期/截取数值
用法：
select sysdate,trunc(sysdate,'mm') from dual; -- 截取本月第一天：
select trunc(sysdate,'q') from dual; -- 截取到季度（本季度的第一天）
select trunc(sysdate,'month') from dual; -- 截取到月（本月的第一天）
select trunc(sysdate) from dual; -- 截取到日（今天）

select trunc(-1.002) value from dual  -- 默认取整 结果-1
select trunc(122.555,-2) value from dual -- 负数表示从小数点左边开始截取2位 结果22
(四)
TO_NUMBER(str) -- ，是将一些处理过的并按一定格式编排过的字符串变回数值型的格式。
用法：
SELECT TO_NUMBER('123') AS VALUE FROM DUAL; -- 结果123
select to_number('f','xx') from dual; -- 用来实现进制转换；16进制转换为10进制 结果15

Oracle 常见的绝大部分日期格式附表：

格式描述
YYYY-MM-DD2015-06-15
YYYY-MON-DD2015-JUN-15
YYYY-MM-DD HH24:MI:SS FF32015-06-15 13:18:10 700
YYYY-MM-DD HH24:MI:SS FF3 TZR2015-06-15 13:18:10 700 +08:00
DS6/15/2015
DLMonday, June 15, 2015
TS1:18:10 PM
Y年的最后一位数字，如：5
YY年的最后两位数字，如：15
YYY年的最后三位数字，如：015
YYYY年，如：2015
Y,YYY年用逗号分割
SYYYY年
YEAR年拼写，如：TWENTY FIFTEEN
SYEAR年拼写，如：TWENTY FIFTEEN
IISO年的最后一位数字，如：5
IYISO年的最后两位数字，如：15
IYYISO年的最后三位数字，如：015
IYYYISO年，如：2015
RR两位数字年，如：15
RRRR四位数字年，如：2015
MMMonth (01-12)
MON月份简称，如：JUN
MONTH月份全称，如：JUNE
RM罗马数字月份
DDay of week (1-7)
DDDay of month (1-31)
DDDDay of year (1-366)
HHHour of day (1-12)
HH12Hour of day (1-12)
HH24Hour of day (0-23)
MIMinute (0-59)
SSSecond (0-59)
SSSSSSeconds past midnight
FF [1…9]毫秒
DS日期简称，如：6/12/2015
DL日期全称，如：Friday, June 12, 2015
TS时间简称，如：5:18:03 PM
CC世纪，如：21
SCC世纪，如：21
QQuarter of year (1, 2, 3, 4)
WWeek of month (1-5)
WWWeek of year (1-53)
FM去掉首尾空格
FX精确匹配

其他不太常见的我就没整理了，不然篇幅太长影响阅读，需要的话可以在Oracle官网查看

日期函数：

MySQL：

(一)
now() / SYSDATE() -- 返回当前日期和时间
用法：
select now() value
/select sysdate() value

(二)
DATE_ADD(date, INTERVAL value addunit) -- 将时间/日期间隔添加到日期，然后返回日期。
用法：
SELECT DATE_ADD(sysdate(), interval 2 month) as value from DUAL; -- 将当前日期加两个月并返回
SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY); -- 结果2017-06-25

(三)
DATEDIFF(date1,date2) -- 返回两个日期之间的天数。 
用法：
SELECT DATEDIFF("2017-06-25", "2017-06-15"); -- 结果10

Oracle：

(一)
SYSDATE -- 返回当前日期和时间

(二)
Next_day(sysdate,7) -- 返回当前日期的紧接着下一个星期的日期，若7为1，则为返回当前日期的紧接着一天的日期
用法：
SELECT Next_day(sysdate,7) value FROM DUAL -- 今天是2021-11-24则返回值为2021-12-1

(三)
ADD_MONTHS(sysdate, 2) -- 当前日期的两个月之后的时间
用法：
SELECT ADD_MONTHS(sysdate, 2) as value from DUAL;
 
(四)
D1-D2  -- 2个日期相减
用法：
直接用两个日期相减（比如d1-d2=12.3）

sql函数：

SQLCODE和SQLERRM：

Oracle内置函数SQLCODE和SQLERRM是特别用在OTHERS处理器中，分别用来返回Oracle的错误代码和错误消息。
MYSQL: 可以从JAVA中得到错误代码，错误状态和错误消息

DBMS_OUTPUT.PUT_LINE(SQLCODE)：

dbms_output.put_line每行只能显示255个字符，超过了就会报错，
而在MYSQL中并无这个方法，其作用是在控制台中打印，用于测试，对迁移无影响。

十：循环语句

IF语句使用不同：

关键点：

1. mysql和oracle除了关键字有一个字set的差别外(ELSEIF/ELSIF),if语句使用起来完全相同. 
2. mysql if语句语法: 摘自 MySQL 5.1 参考手册 20.2.12.1. IF语句 
IF search_condition 
THEN 
statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF
IF实现了一个基本的条件构造。如果search_condition求值为真，相应的SQL语句列表被执行。如果没有search_condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。

MySQL：

IF 
iv_weekly_day = 'MON' 
THEN
      set ii_weekly_day = 'MON';
ELSEIF 
iv_weekly_day = 'TUE'
THEN
      set ii_weekly_day = 'TUE';
END IF;

Oracle：

IF 
iv_weekly_day = 'MON' 
THEN
       ii_weekly_day := 'MON';
ELSIF 
iv_weekly_day = 'TUE' 
THEN
       ii_weekly_day := 'TUE';
END IF;

FOR语句不同：

关键点：

1. oracle使用For语句实现循环. 
   Mysql使用Loop语句实现循环. 
   
2. oracle 使用For…loop关键字. 
   Mysql使用loopLable:LOOP实现循环.

MySQL：

loopLable:LOOP
  IF i > (ii_role_cnt-1) 
  THEN
    LEAVE looplable;
  ELSE
    SELECT COUNT(*) INTO li_role_ik_cnt FROM SD_ROLE
    WHERE ROLE_CD = lo_aas_role_upl(li_cnt); 
    IF li_role_ik_cnt = 0  
    THEN
       RETURN 'N';
    END IF;
    SET li_role_ik_cnt = -3;
    SET i = i+1;
   END IF;
 END LOOP loopLable;

Oracle：

FOR li_cnt IN 0..(ii_role_cnt-1) LOOP
    SELECT COUNT(*) INTO li_role_ik_cnt FROM SD_ROLE
    WHERE ROLE_CD = lo_aas_role_upl(li_cnt);
    IF li_role_ik_cnt = 0  THEN
        RETURN 'N';
    END IF;
    li_role_ik_cnt := -3;
END LOOP;

语法1：类似java的while循环

判断条件为true表示执行循环体操作

范例:使用语法输出1到10的数字

declare 
	step number := 1; 
begin 
	while step <= 10 loop 
		dbms_output.put_line(step); 
		step := step + 1; 
	end loop; 

	dbms_output.put_line('程序结束');
end;

语法2：

判断条件为true表示结束循环操作

范例:使用语法输出1到5的数字

declare 
	step number := 1; 
begin 
	loop exit when step > 5; -- 注意条件后面的;
		dbms_output.put_line(step); 
		step := step + 1; 
	end loop;
    
    dbms_output.put_line('程序结束');
end;

语法3：

用于固定次数的循环

每次从开始值到结束值之间依次将值赋值给变量，赋值一次循环一次，当结束值赋值后就结束循环

FOR   i   IN   1 . . 3    LOOP
	语句序列 ;
END LOOP ;

范例:使用语法输出2到5的数字

declare 
	step number; 
begin 
	for step in 2 .. 5 loop 
		dbms_output.put_line(step); 
	end loop;
    
    dbms_output.put_line('程序结束');
end;

while语句不同：

关键点：

1. oracle 中使用while语句关键字为: while 表达式 loop… end loop;
    mysql 中使用while语句关键字为: while 表达式 do… end while;

MySQL：

WHILE lv_inputstr IS NOT NULL 
DO
...
END WHILE;

Oracle：

WHILE lv_inputstr IS NOT NULL 
LOOP
...
END LOOP;

十一：存储过程&存储函数

存储过程：

指在大型数据库系统中，一组为了完成特定功能的SQL 语句集合。

经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数值（如果该存储过程带有参数）来执行它。

存储函数：

指在数据库中封装执行特定sql语句集合的方法，该方法执行有返回值（等同于自定义内置函数）,使用方式和内置函数一致，

类似于Java有返回值的方法封装，等同于自定义内置函数（数据库自带的存储函数）

存储过程和存储函数的区别

语法区别：

​ 关键字不一样，存储函数比存储过程多了两个return。

本质区别：

  1. 存储函数有返回值，而存储过程没有返回值。
  2. 如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
  3. 即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值，而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。

（一）创建存储过程语句不同:

关键点：

1.在创建存储过程时如果存在同名的存储过程,会删除老的存储过程. 
  oracle使用create or replace.
  mysql使用先删除老的存储过程,然后再创建新的存储过程.

2.oracle 存储过程可以定义在package中,也可以定义在Procedures中. 
如果定义在包中,一个包中可以包含多个存储过程和方法.如果定义在Procedures中,存储过程中不可以定义多个存储过程. 
   Mysql  存储过程中不可以定义多个存储过程. 

3.oracle中字符串类型可以使用varchar2.  
   Mysql 需要使用varchar

4.Oracle中参数varchar长度不是必须的,
   Mysql中参数varchar长度是必须的, 比如varchar(100)

MySQL：

范例1：创建名称为 ShowStuScore 的存储过程，存储过程的作用是从学生成绩信息表中查询学生的成绩信息
DELIMITER // -- 将语句的结束符号从分号;临时改为两个//(自定义的结束符)
CREATE PROCEDURE GetScoreByStu
(IN name VARCHAR(30))
 BEGIN
 SELECT student_score FROM tb_students_score
 WHERE student_name=name;
 END //

范例2：创建名称为 GetScoreByStu 的存储过程，输入参数是学生姓名。
存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息
 DELIMITER //
 CREATE PROCEDURE GetScoreByStu
 (IN name VARCHAR(30))
 BEGIN
 SELECT student_score FROM tb_students_score
 WHERE student_name=name;
 END //

Oracle：

范例1：创建一个输出helloword的存储过程：
create or replace procedure helloworld is 
begin 
	dbms_output.put_line('hello world'); 
end helloworld;

范例2：给指定的员工涨指定工资，并打印出涨前和涨后的工资(带有参数的存储过程)
create or replace procedure addSalByNo(eno in number,addSal in number) as
  pemp myemp%rowtype;
begin
  select * into pemp from myemp where empno = eno;
  update myemp set sal = sal + addSal where empno = eno;
  dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' || (pemp.sal + addSal));
end addSalByNo;

（二）创建函数语句不同:

关键点：

1.在创建函数时如果存在同名的函数,会删除老的函数.  
  oracle使用create or replace.
  mysql使用先删除老的函数,然后再创建新的函数.
  
2.oracle 函数可以定义在package中,也可以定义在Functions中.
如果定义在包中,一个包中可以包含多个存储过程和函数.如果定义在Functions中,每个函数只能定义一个函数.
   Mysql  Functions不可以定义多个函数. 

3.oracle返回值用return. 
   Mysql返回值用returns.

MySQL：

范例：根据信用额度返回客户级别的功能
DELIMITER $$

CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
    DETERMINISTIC
BEGIN
    DECLARE lvl varchar(10);

    IF p_creditLimit > 50000 THEN
 SET lvl = 'PLATINUM';
    ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
        SET lvl = 'GOLD';
    ELSEIF p_creditLimit < 10000 THEN
        SET lvl = 'SILVER';
    END IF;

 RETURN (lvl);
END $$
DELIMITER ;

Oracle：

范例：使用存储函数来查询指定员工的薪资
create or replace function getSalByEmpNo(eno in NUMBER) return number is
	rs EMP%rowtype;	-- 结果变量，用于获取函数体中执行的语句赋值，用于返回结果中的某个值
					-- rs_sal number;
					-- rs_sal EMP.SAL%type;
begin
	-- 通过eno查询员工
	SELECT * INTO rs FROM EMP where EMPNO = eno;--查询返回一行数据并赋值给rs变量
	--SELECT sal INTO rs_sal FROM EMP where EMPNO = eno;--查询返回一列数据并赋值给rs变量

	-- 写在最后
	return(rs.sal);--返回在值必须和定义的数据类型一致
end;

（三）传入参数写法不同:

关键点：

1. oracle存储过程参数可以定义为表的字段类型.
   Mysql存储过程不支持这种定义方法.需要定义变量的实际类型和长度.
2. oracle 参数类型in/out/inout写在参数名后面. 
   Mysql  参数类型in/out/inout写在参数名前面.
3. oracle 参数类型in/out/inout 都必须写.
   Mysql  参数类型如果是in,则可以省略. 如果是out或inout则不能省略.
注意: mysql中指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数） RETURNS字句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

MySQL：

create procedure P_ADD_FAC(
     (in) id_fac_cd  varchar(100))
     
create function func_name(
       gw_id varchar（100))

Oracle：

procedure P_ADD_FAC(
   id_fac_cd  IN ES_FAC_UNIT.FAC_CD%TYPE)
   
function func_name(
           gw_id  in(out)  varchar2 )

（四）过程和函数的声明变量的位置不同：

MySQL：

声明变量在begin...end体内，begin之后其他任何内容之前

Oracle：

声明变量在begin…end体之前

（五）在存储过程中调用存储过程方式的不同：

关键点：

MYSQL存储过程调用存储过程，需要使用Call pro_name(参数).  
Oracle调用存储过程直接写存储过程名就可以了，或者用call以及begin和end包裹

MySQL：

Call Procedure_Name(参数);

范例：调用上面写的存储过程GetScoreByStu
call GetScoreByStu('狗黄晋')

Oracle：

Procedure_Name(参数);

范例：调用上面写的存储过程addSalByNo
begin 
	addSalByNo(7902,500); 
end;

--或
call addSalByNo(7902,500);

（六）存储过程返回语句不一样：

关键点：

oracle存储过程和方法都可以使用return退出当前过程和方法. 
Mysql存储过程中只能使用leave退出当前存储过程.不可以使用return. 
Mysql方法可以使用return退出当前方法.

MySQL：

LEAVE proc; (proc 代表最外层的begin end)

Oracle：

return;

（七）包的声明方式不同：

关键点：

oracle可以创建包,包中可以包含多个存储过程和方法. 
mysql没有没有包这个概念,可以分别创建存储过程和方法. 每个存储过程或方法都需要放在一个文件中. 
例1: 方法命名 
oracle 中SD_FACILITY_PKG.F_SEARCH_FAC 
to mysql SD_FACILITY_F_SEARCH_FAC 
例2: 过程命名
oracle 中SD_FACILITY_PKG.P_ADD_FAC
to mysql SD_FACILITY_P_ADD_FAC

MySQL：

拆分成多个存储过程或函数

Oracle：

create or replace package/package body package name

（八）存储过程异常处理不一样：

关键点：

oracle : 内部异常不需要定义,在存储过程或函数末尾写上EXCEPTION后,后面的部分即为异常处理的部分.  
oracle可以定义自定义异常,自定义异常需要使用raise关键字抛出异常后,才可以在EXCEPTION中捕获.

mysql: mysql内部异常也需要先定义,在定义的同时也需要实现异常的功能. 
          目前mysql不支持自定义异常.

MySQL：

DECLARE EXIT HANDLER FOR  SQLEXCEPTION 
 BEGIN
    ROLLBACK ;
    set ov_rtn_msg = concat(c_sp_name,'(', li_debug_pos ,'):',
        TO_CHAR(SQLCODE),': ',SUBSTR(SQLERRM,1,100));
 END;

Oracle：

EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK ;
    ov_rtn_msg := c_sp_name||'('|| li_debug_pos ||'):'||
        TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100);

（九）NO_DATA_FOUND异常处理：

关键点：

oracle中: 
NO_DATA_FOUND是游标的一个属性. 
当select没有查到数据就会出现 no data found 的异常，程序不会向下执行.

Mysql: 
没有NO_DATA_FOUND这个属性.但可是使用FOUND_ROWS()方法得到select语句查询出来的数据.如果FOUND_ROWS()得到的值为0,就进入异常处理逻辑.

MySQL：

使用FOUND_ROWS()代替NO_DATA_FOUND.

Oracle：

 EXCEPTION
        WHEN NO_DATA_FOUND THEN
            oi_rtn_cd := 1;
            ov_rtn_msg := SD_COMMON.P_GET_MSG('DP-CBM-01100a-016',li_sub_rtn_cd,lv_sub_rtn_msg);

十二：触发器

定义：

是当数据库中发生特定操作时运行的特殊存储过程

触发器可用于：

  1. 数据确认，保证数据是否存在

  2. 实施复杂的安全性检查，检查数据值是否符合要求

  3. 做审计，跟踪表上所做的数据操作等

  4. 数据的备份和同步（常用）

    说明：

    **触发器名：**触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。

    **触发时间：**指明触发器何时执行，该值可取：

    ​ before：表示在数据库动作之前触发器执行;

    ​ after：表示在数据库动作之后触发器执行。

    **触发事件：**指明哪些数据库动作会触发此触发器：

    ​ insert：数据库插入会触发此触发器;

    ​ update：数据库修改会触发此触发器;

    ​ delete：数据库删除会触发此触发器。

    ​ of

    列名：指定触发此触发器的数据库列名（表示只有在对指定列进行动作才能触发触发器），如果不指定表示 操作任意列都会触发

    表名：数据库触发器所在的表。

    for each row：对表的每一行操作，触发器执行一次。如果没有这一选项，则只对整个表执行一次。

    when(条件)：指定触发条件，没有条件表示所有行都触发，有条件表示只有满足条件的数据行触发

    创建触发器的语句不同：

    关键点：

    1. Oracle使用create or replace trigger语法创建触发器.
   Mysql使用 create trigger创建触发器.
2. Oracle可以在一个触发器触发insert,delete,update事件. 
   Mysql每个触发器只支持一个事件. 也就是说,目前每个trigger需要拆分成3个mysql trigger.
3. mysql trigger 不能在客户端显示或编辑.需要在服务器所在的机器上操作.

    MySQL：

    create trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` 
BEFORE INSERT on `hs_esbs`.`es_fac_unit` 
for each row

    Oracle：

    create or replace trigger TG_ES_FAC_UNIT
  before insert or update or delete on ES_FAC_UNIT
  for each row

    触发器new和old记录行的引用不同：

    关键点：

    1. new和old记录行的引用：
   mysql是NEW.col1，OLD.col1来引用。
   oracle是:NEW.col1, :OLD.col1来引用。 多了个:号
2. NEW 和OLD不区分大小写.

    MySQL：

    取得新数据: NEW.FAC_CD
取得老数据: OLD.FAC_CD

    Oracle：

    取得新数据: :new.FAC_CD
取得老数据: :old.FAC_CD

十三：自动增长

​ 在很多数据库中都存在一个自动增长的列,如果现在要想在oracle 中完成自动增长的功能, 则只能依靠序列完成,所有的自动增长操作,需要用户手动完成处理（不能如其他数据库那样靠数据库底层自动实现）。

Oracle完成自动增长步骤：

1）定义序列（特殊的伪表）

2）在新增语句中使用序列的内置方法nextval获取序列值（序列值在该方法使用后会自动默认加1）

创建序列语法：

CREATE SEQUENCE 自定义序列名
increment by n -- n代表每次增长多少
start with n -- n代表从几开始
minvalue n -- n代表最小值为多少
maxvalue n/NOMAXVALUE  -- n代表最小值为多少/没有最大值
cycle/no cycle -- 序列到达最大值之后怎么办，循环/不循环 一般取cycle
cache n/nocache -- 需要不需要使用缓存,n代表一次生成多少个序列的值

实际开发中：不会指定其他参数，全使用默认值就好

范例:创建一个名称为seqpersonid的序列,验证自动增长的操作

CREATE SEQUENCE seqpersonid;

序列创建完成之后,所有的自动增长应该由用户自己处理,所以在序列中提供了以下的两种操作：
序列名.nextval :取得序列的下一个内容（对当前序列加1）
序列名.currval :取得序列的当前内容 ，不递增1（第一次不能使用该语句获取）

select seqpersonid.nextval from dual; 
select seqpersonid.currval from dual;

Oracle中，使用查询语句时必须存在from语句，如果操作的不是某个存在表时，必须使用dual表示，dual数据库提供的虚拟表，用于查询测试

在插入数据时需要自增的主键中可以这样使用

insert into person values(seqpersonid.nextval,'李四',1,null,'北京育新')

​ 在实际项目中每一张表会配一个序列，但是表和序列是没有必然的联系的，一个序列被哪一张表使用都可以，但是我们一般都是一张表用一个序列。而序列的管理一般使用工具来管理。

十四：权限管理

创建用户：

关键点：

1.oracle创建用户
Oracle 的默认用户有三个: sys / system / scott. 其中sys和system 是系统用户,拥有dba权限, scott用户是Oracle数据库的一个示范账户, 在数据库安装时创建, 不具备dba权限. 
创建用户命令: 
Create user user_name identified by user_password 
[default tablespace tableSpace] 
[temporary tablespace tableSpace}; 
说明: 
每个用户都有一个默认表空间和一个临时表空间,如果没有指定,oracle就将system设置为默认表空间,将temp设为临时表空间. 

2.mysql创建用户
创建用户命令: 
mysql> CREATE USER yy IDENTIFIED BY '123'; 
yy表示你要建立的用户名，后面的123表示密码
上面建立的用户可以在任何地方登陆。
如果要限制在固定地址登陆，比如localhost 登陆：
mysql> CREATE USER yy@localhost IDENTIFIED BY '123';

MySQL：

CREATE USER user_name IDENTIFIED BY user_password;

Oracle：

Create user user_name identified by user_password 
default tablespace starSpace temporary tablespace temp;

删除用户：

关键点：

1. Oracle
SQL>drop user 用户名;  //用户没有建任何实体
SQL> drop user 用户名 CASCADE;  // 将用户及其所建实体全部删除
注: 当前正连接的用户不得删除。

2. Mysql
自4.1.1以后，删除一个MYSQL帐户，可以使用drop user 语句了。
不过在5.0.2之前的版本中，drop user语句只能删除没有任何权限的用户。
从5.0.2往后的版本中，drop user语句可以删除任何用户。(当然不能自己删自己)。示例：drop user "garfield"@"localhost"。别忘了加后面的@，不然会报错。

在4.1.1与5.0.2之间的版本中要删除一个MYSQL帐户，需要进行以下操作。
1)使用show grants语句查看要删除的MYSQL帐户都有哪些权限，使用方法如show grants for "garfield"@"localhost"。
2)使用revoke语句收回用户在show grants里拥有的权限。执行这个语句将删除除user表之外的其它所有权限表中的相关记录，并且收回在user表中该用户拥有的全局权限。
3)使用drop user 语句把用户从user表中删除。

MySQL：

Drop user user_name;

Oracle：

Drop user user_name cascade;

修改密码：

关键点：

1.mysql修改密码
第一种方式：
　　1) 更改之前root没有密码的情况
　　　　c:\mysql\bin>mysqladmin -u root password "your password"
2) 更改之前root有密码的情况,假如为123456
　　c:\mysql\bin>mysqladmin -u root -p123456 password "your password"
　　注意：更改的密码不能用单引号，可用双引号或不用引号
第二种方式：
　1) c:\mysql\bin>mysql -uroot -p密码 以root身份登录
　2) mysql>use mysql 选择数据库
　3) mysql>update user set password=password('你的密码') where User='root';
　4) mysqlflush privileges; 重新加载权限表

MySQL：

mysqladmin -u root -p 123456 password "your password";
Drop user user_name;

Oracle：

alter user user_name identified by new_password

设置用户权限以及回收权限：

关键点：

MySQL的用户形式:
用户名@主机,比如root@localhost和root@%是不一样的用户
主机地址可以是:本地(localhost),某个具体IP,某个IP网段，以及任何地址(用通配符%表示等，意味着这个账号可以从指定的IP来访问登陆。
例如: root@localhost只允许本地访问, root@192.168.0.1只允许192.168.0.1这个IP访问, 而root@%则允许所有IP访问。

Oracle 角色权限分类：
（1）CONNECT：拥有Connect权限的用户只可以登录Oracle，无法操作其他对象。
（2）RESOURCE：拥有Resource权限的用户可以在自己的模式下操作对象，不可以操作其他模式下的对象。
（3）DBA：拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
对于普通用户：授予 connect, resource 角色权限。
对于DBA管理用户：授予 connect，resource，dba 角色权限。

MySQL：

-- 创建epoint用户权限
create user epoint@'%' identified by 'Gepoint';

-- 赋予所有权限，也就是dba权限
grant all privileges on *.* to epoint@'%';

-- 仅赋予epoint数据库的权限
grant all privileges on epoint.* to epoint@'%';
-- 刷新到内存生效
flush privileges;


-- 回收账户权限
revoke all privileges on *.* from epoint@'%';
revoke all privileges on epoint.* from epoint@'%';

-- 删除epoint用户
drop user epoint@'%';

-- 查询用户及权限
-- 查询当前存在哪些账号
select user,host from mysql.user;
-- 查看具体账号的权限
show grants for epoint@'%';

Oracle：

-- 创建epoint用户
CREATE USER epoint identified BY Gepoint DEFAULT tablespace users;

-- 赋予权限;
-- 赋予dba权限
GRANT dba TO epoint;
-- 查看拥有dba权限的账户
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTED_ROLE ='DBA';
-- 赋予普通权限
GRANT CONNECT,RESOURCE TO EPOINT;

-- 回收权限
REVOKE dba FROM EPOINT;
REVOKE CONNECT,RESOURCE FROM EPOINT;

-- 删除epoint用户
-- 加上cascade则将用户连同创建的东西全部删除
DROP USER EPOINT CASCADE;

-- 查询用户拥有哪些角色权限
-- dba账号查看所有账号的权限
SELECT * FROM DBA_ROLE_PRIVS;
-- 普通账号查看自己的权限
SELECT * FROM USER_ROLE_PRIVS;

-- 查看所有用户的信息
SELECT * FROM DBA_USERS;

十五：视图(mysql和Oracle的使用语法相同)

定义：

视图就是用于封装一条复杂查询的语句。

作用：降低（Java程序中）语句使用的复杂度，隐藏细节（减少敏感字段），保护数据安全

说明：视图本身并不能提高性能，只可以提高程序开发的效率和安全性

要求：使用视图时一般建议将视图设置为只读(原因：默认修改视图中的数据会影响实际的数据库表中的数据)

语法：
create or replace view 视图名称 as 需要封装的复杂查询语句 with read only


范例：建立一个视图，此视图包括了部门编号20的全部员工信息** 

create or replace view empvd20 as 
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,D.DNAME FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = 20 with read only;

十六：其他sql语句细节

（1）内连接的更改：

关键点：

oracle sql语句和mysql sql语句有一定的区别.
1. oracle左连接,右连接可以使用(+)来实现. 
   Mysql只能使用left join ,right join等关键字.

MySQL：

左外连接：
方法一
select a.*, c.*, d.*
  from a
  left join(c, d) 
  on (a.id = c.id and a.id = d.id), b
 where a.id = b.id
   and a.name is not null
方法二
select a.*, c.*, d.*
  from a
  left join c on a.id = c.id
  left join d on a.id = d.id, b
 where a.id = b.id
   and a.name is not null

Oracle：

总结：(+)号在哪边，哪边是从表
1、
select a.*, b.*, c.*, d.*
  from a, b, c, d
 where a.id = b.id
   and a.name is not null
   and a.id = c.id(+)
   and a.id = d.id(+)
   
"(+)"所在位置的另一侧为连接的方向，
所以上面的例子1是左连接。
以下的例子2既是右连接。

2、
select a.*, b.*, c.*, d.*
  from a, b, c, d
 where a.id = b.id
   and a.name is not null
   and a.id(+) = c.id

最后一句执行的sql statement所取得或影响的条数：

关键点：

oracle中: 
sql 表示最后一句执行的 SQL Statement, rowcount表示该SQL所取得或影响的条数.
Mysql中: 
执行select语句后查询所影响的条数用: FOUND_ROWS()
执行update delete insert语句后查询所影响的条数用: ROW_COUNT()

MySQL：

执行select语句后用: FOUND_ROWS()
执行update delete insert语句后用: 
ROW_COUNT().

Oracle：

SQL%ROWCOUNT

查询分页：

关键点：

1.mysql修改密码
第一种方式：
　　1) 更改之前root没有密码的情况
　　　　c:\mysql\bin>mysqladmin -u root password "your password"
2) 更改之前root有密码的情况,假如为123456
　　c:\mysql\bin>mysqladmin -u root -p123456 password "your password"
　　注意：更改的密码不能用单引号，可用双引号或不用引号
第二种方式：
　1) c:\mysql\bin>mysql -uroot -p密码 以root身份登录
　2) mysql>use mysql 选择数据库
　3) mysql>update user set password=password('你的密码') where User='root';
　4) mysqlflush privileges; 重新加载权限表

MySQL：

方法:使用循环变量替换oracle中ROWNUM
set @mycnt = 0;
     SELECT (@mycnt := @mycnt + 1) as ROW_NUM,t1.*
     FROM
     (SELECT  MSG_INT_KEY,MSG_TY,MSG_CD,ROWNUM ROW_NUM
       FROM SD_SYS_MSG
    WHERE (ii_msg_int_key IS NULL OR msg_int_key  = ii_msg_int_key )
       ORDER BY MSG_CD
     ) t1
       WHERE (in_page_no IS NULL)
                   OR (t1.ROW_NUM> ((in_page_no - 1) * li_per_page_amt)
                   AND t1.ROW_NUM < (in_page_no * li_per_page_amt + 1)
                     );

Oracle：

SELECT t1.*
FROM 
(SELECT MSG_INT_KEY, 
     MSG_TY, 
     MSG_CD, 
     ROWNUM ROW_NUM
  FROM SD_SYS_MSG
  WHERE (ii_msg_int_key IS NULL 
        OR msg_int_key = ii_msg_int_key)
  ORDER BY MSG_CD
) t1
WHERE (in_page_no IS NULL)
 OR (t1.ROW_NUM >
      ((in_page_no -1)*li_per_page_amt) 
 AND t1.ROW_NUM <
    (in_page_no*li_per_page_amt + 1)
 );

执行动态sql：

关键点：

1. oracle可以将动态sql放在游标中执行. 
    mysql游标声明有一定的局限性:  mysql游标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。Mysql采用Prepared Statements实现动态sql. 例子如下:
    
INT Emp_id_var = 56
PREPARE SQLSA
FROM "DELETE FROM employee WHERE emp_id=?" ;
EXECUTE SQLSA USING :Emp_id_var ;

MySQL：

set @a = iv_cd_field_name;
 set @b = iv_table_name;
 set @c = IFNULL(iv_where_cause,' 1=1 ');

 SET @s = concat('SELECT distinct ', @a , ' FIELD1  FROM ' , @b ,
      ' WHERE ' , IFNULL(@c,' 1=1 '));

 PREPARE stmt3 FROM @s;
 EXECUTE stmt3;
 
 DEALLOCATE PREPARE stmt3;

Oracle：

lv_sql := 'SELECT ' ||' distinct ' || iv_cd_field_name || ' FIELD1 '||
                 ' FROM ' || iv_table_name ||
     ' WHERE ' || NVL(iv_where_cause,' 1=1 ');

  OPEN l_sys_cur FOR lv_sql;

存储过程相互调用时传递数组：

关键点：

存储过程相互调用时传递数组解决方法: 
  oracle中传入12个字符串到存储过程,然后将这12个字符串转换为12个数组,再调用其他存储过程并将这12个数组分别传给存储过程,便利每个数组进行业务逻辑处理. 
   mysql解决方法: 将存储过程中的数组去掉,两个存储过程调用时直接传递字符串,然后再需要处理业务逻辑的地方将字符串分解,进行业务逻辑处理.

MySQL：

mysql中数用数组步骤: 
1. 将需要处理的字符串交给执行业务逻辑
的存储过程处理. 
  CALL SD_HOLIDAY_P_MOD_MONTH(iv_year, 1, iv_jan__str, iv_user_cd);
2. SD_HOLIDAY_P_MOD_MONTH中处理字符串. (将字符串按自定格式分隔出来,在对每个小字符串进行业务逻辑处理.)
SET lv_inputstr = iv_inputstr;
    loopLable:LOOP
   IF li_cnt > 9 THEN
     LEAVE looplable;
   ELSE
     SET li_pos = INSTR(lv_inputstr, iv_delimiter);
     IF li_pos = 0 THEN
         leave looplable;
     ELSE
        set temp_str = SUBSTR(lv_inputstr, 1, li_pos - 1);
       /*插入temp_str到SD_HOLIDAY表*/
       INSERT INTO SD_HOLIDAY(...)
       SET lv_inputstr = SUBSTRING(lv_inputstr, li_pos + LENGTH(iv_delimiter)); 
     END IF;  
     SET li_cnt = li_cnt+1;
  END IF;
END LOOP loopLable;

Oracle：

oracle使用数组步骤: 
1. 将传入的字符串通过P_UNPACK_LIST方法转换为数组.(lo_holiday_jan_upl即为数组)
   P_UNPACK_LIST(iv_jan__str, lv_delimiter, lo_holiday_jan_upl);
2. 传数组到另一个存储过程.
   P_MOD_MONTH(iv_year, 1, lo_holiday_jan_upl, iv_user_cd);
3. P_MOD_MONTH中使用数组: (将数组中的各个元素取出来插入到SD_HOLIDAY表)
FOR li_cnt IN 0 .. 9 LOOP
    IF iv_daystr(li_cnt) IS NOT NULL THEN
      INSERT INTO SD_HOLIDAY
        (HOLIDAY_INT_KEY,
         YEAR,
         MONTH,
         DAY,
         ENABLE_FLAG,
         CREATE_BY,
         CREATE_DATE,
         LAST_UPD_BY,
         LAST_UPD_DATE)
      VALUES
        (SEQ_HOLIDAY_INT_KEY.NEXTVAL,
         iv_year,
         iv_month,
         iv_daystr(li_cnt),
         1,
         iv_user_cd,
         ld_sys_date,
         iv_user_cd,
         ld_sys_date);
  END IF;

END LOOP;

Java无法以String来接取int：

关键点：

CAST(intvalue AS CHAR)

MySQL：

select fac_unit_key -- FILED1在mysql中要改
select CAST(fac_unit_key AS CHAR) FILED1

Oracle：

select fac_unit_key  -- FILED1在oracle可以

总结：

上面整理了这么多，可是在实际开发中使用最多以及最关键还是基本的sql语句，连表查询，以及结果集封装等，另外Oracle数据库这么贵，上面的这些也会有专业的DBA来开发和管理，这里之所以整理了这么多，花了这么长时间，主要是想在以后的工作中，如果有需要，能够迅速理出思路并进行使用，所以最后就再主要整理一下MySQL和Oracle（二者的基本sql语句语法相同）的DQL（数据查询语言）,DML（数据操纵语言 增删改）,DDL（数据定义语言 创建数据库中的各种对象）语句和聚合函数的使用细节。

DQL:

<resultMap id="basicResultMap" type="com.leixiaoqiao.pojo.Book">
        <id property="bookId" column="book_id"/>
        <result property="bookName" column="book_name"/>
        <result property="bookDate" column="book_date"/>
</resultMap>

<resultMap id="bookListResultMap" type="com.leixiaoqiao.pojo.Book" extends="basicResultMap">
        <association property="type" javaType="com.leixiaoqiao.pojo.Type">
            <id property="typeId" column="type_id"/>
            <result property="typeName" column="type_name"/>
        </association>
</resultMap>

<sql id="columns">
        b.book_id,b.book_name,b.book_date,t.type_id,t.type_name
</sql>
    
<select id="selectOne" resultMap="bookListResultMap">
        select <include refid="columns"/>
        from book b LEFT OUTER JOIN `type` t
        on b.type_id=t.type_id
        where book_id=#{id}
</select>

DML:

1) 插入：INSERT

<insert id="insert" parameterType="com.leixiaoqiao.pojo.Book">
        insert into book (book_name,book_date,type_id)
        values (#{bookName},#{bookDate},#{type.typeId})
</insert>

2) 更新：UPDATE

<update id="update" parameterType="com.leixiaoqiao.pojo.Book">
        UPDATE book
           <set>
               <if test="bookName!=null">
                   book_name=#{bookName},
               </if>
               <if test="bookDate!=null">
                   book_date=#{bookDate},
               </if>
               <if test="type.typeId!=null">
                   type_id=#{type.typeId},
               </if>
           </set>
           <where>
               book_id=#{bookId}
           </where>
</update>

3) 删除：DELETE

<delete id="delete">
        delete from book where book_id=#{id}
</delete>

DDL:

创建一个表：
cteate table 表名（列1 类型，列2 类型）;

查看表结构:
desc表名

添加一个字段:
alter table 表名 add（列类型）;

修改字段类型: 
alter table 表名 modify(列类型);

删除一个字段:
alter table 表名 drop column列名;

修改表的名字:
rename student to stu

删除表:
drop table 表名

分组和聚合函数：

Mysql中语句如下：

select count(u.username)，r.rname,r.rid,r.pid
from room r,user1 u
where r.pid=u.id
group by r.rid,r.pid

注意：

这里r.rname并没有出现在group by子句、聚合函数中，但是MYSQL中仍然能够执行、列出数据。
但是，在ORACLE中，却不能
在Oracle中，在select后面会跟着分组统计结果显示普通字段列表和一个统计函数表达式
在group by后面指定分组条件(前面select后面跟着几个分组显示字段，这里就最少需要使用几个)，一般需要和分组显示字段列表保持一致（个数和列名），也就是前面写了几个显示字段名，这里就最少需要添加几个字段名

最后：

以上内容都是本人花了两天时间整理出来的，内容整理自博客园，Oracle官网，BEGTUT.com，菜鸟教程以及个人平时的练习和笔记，如果存在错误，欢迎大佬们评论指点。最后，整理不易，望喜欢。

L.X.Q.

温柔赠于四方，自由灵魂独享