如何写mysql中实现nvl功能的sql呢？

MySQL的确实有一个ISNULL （ ）函数。然而，它的作品有点不同，微软的ISNULL （ ）函数。 MySQL中我们可以使用IFNULL （ ）函数，就像这样： SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))

FROM Products

DECLARE

t_name VARCHAR2(10)

t_course1 NUMBER

t_course2 NUMBER

t_course3 NUMBER

TYPE c_type IS REF CURSOR

cur c_type

BEGIN

OPEN cur FOR

'SELECT t_name,

SUM(CASE t_course WHEN '''||'语文'||''' THEN t_score ELSE 0 END),

SUM(CASE t_course WHEN '''||'数学'||''' THEN t_score ELSE 0 END),

SUM(CASE t_course WHEN '''||'物理'||''' THEN t_score ELSE 0 END)

FROM tb GROUP BY t_name'

LOOP

FETCH cur INTO t_name,t_course1,t_course2,t_course3

EXIT WHEN cur%NOTFOUND

dbms_output.put_line(t_name||'语文'||t_course1||'数学'||t_course2||'物理'||t_course3)

END LOOP

CLOSE cur

讲解Oracle数据库移植到MySQL

出处：IT专家网 日期：2010-06-24

一、前言

公司原来的项目是基于Oracle数据库的，Oracle功能强大，但是部署和管理较复杂，更重要的是，购买Oracle的费用不是每个客户都愿意承担的。因此，迫切需要把公司项目所用数据库移植到一个简单好用的数据库上。当然，如您所料，我们选择了广受欢迎的MySQL。

作为一个开源数据库，MySQL用无数案例证明了她的可用性，因此让我们把重点放在如何将Oracle移植到MySQL上。已经有很多的文章和专题介绍了Oracle移植到MySQL的方法和步骤，也有相当多的工具可以辅助这种移植过程。但是，由于数据库实现的差异，完美的移植工具是不存在的，移植过程中不断碰到的问题证明了这一点，特别是您使用了Oracle的一些高级特性时。

从Oracle移植到MySQL主要有六个方面的内容需要移植，一是表Table，包括表结构和数据，二是触发器Trigger，三是存储过程Procedure，函数function和包Package，四是任务Job，五是用户等其他方面的移植，六是具体应用程序通过SQL语句访问时的细节差异克服。

笔者用来移植测试的数据库是：Oracle 9i ，MySQL 6.0，Windows 2000环境。

二、表的移植

这个部分的移植是最容易用工具实现的部分，因为很多MySQL的图形管理工具都自带这样的移植工具，比如SQLYog，MySQL Administrator等。但是，这些工具的移植能力各有不同，对字段类型转换、字符集等问题都有自己的处理方式，使用时请注意。

笔者使用“SQLYog Migration Toolkit”工具按提示步骤移植后，表的主要结构和数据将成功移植，主要包括表的字段类型(经过映射转换，比如number会转换为double，date转换为timestamp等，请小心处理日期字段的默认值等)，表的主键，表的索引(Oracle的位图索引会被转成BTree索引，另外表和字段的注释会丢失)等信息。需要特别注意的是，Oracle的自增字段的处理。

大家知道，Oracle通常使用序列sequence配合触发器实现自增字段，但是MySQL和SQL Server等一样，不提供序列，而直接提供字段自增属性。所以，请把Oracle里面的自增字段实现直接改为MySQL的字段属性，而且，这个字段必须是主键(key)并且不能有默认值。

还有一个问题，如果您的应用要直接使用Oracle的某个序列，那么您只能在MySQL里面模拟实现一个，具体方法就是利用MySQL的自增字段实现的。

三、触发器的移植

首先，MySQL在6.0以后才支持触发器!

触发器的移植没有现成工具，因为两者之间的语法差异较大，您只能通过手工对照着原来的逻辑一个一个添加。

这里要说明一下，MySQL的SQL过程语法和Oracle PL/SQL大致相同，但还是有些细微差别：

1. 变量声明Declare部分，在Oracle中Declare语句位于Begin之前，在MySQl中，Declare位于Begin之后

2. 注释不同，在Oracle中，可用 “—“ 注释一行或“/* */”注释一段，在MySQL中，需用 “/* */”或“#”来注释

3. 对触发前后变量值的引用方法不同在Oracle中，用 :new.eid, :old.eid表示新旧值，

在MySQL中，用 New.eid，old.eid表示新旧值

4. 移植中发现的问题

1) Oracle的自治事务autonomous_transaction ，MySQL不支持，您必须用其他方式实现，MySQL不允许在触发器过程中执行对触发器所在表的 *** 作(包括读写)

2) MySQL函数和trigger中不能执行动态SQL语句，也就是说，您不能在触发器里面组合出来一个SQL字符串，然后用exec来执行

3) Oracle的表级触发器，MySQL还不支持，所以必须改成使用行级触发器，注意这会导致有时SQL语句的执行效率很低

四、存储过程，函数和程序包的移植

程序包是Oracle用来组织逻辑功能的一个Object，MySQL不支持，因此需要将包里的存储过程、函数等全部放到该数据库公有过程和函数里面。

MySQL的过程和函数语法与Oracle类似，但还是有细微差别，除了数据类型需要转换，还有：

1. 格式不同，例如：

Oracle为：

CREATE OR REPLACE procedure procedure1(TableName in varchar2) is

MySQL应该为：

CREATE procedure procedure1( in TableName varchar(200))

2. 赋值语句不同：

Oracle赋值语句为：

strSQL := ‘update table set field1=1’

MySQL应该为：

Set StrSQL = ‘update table set field1=1’(用:=也行)

3. 一些要用到游标的过程请注意

MySQL过程不支持嵌套游标，不支持带参游标，不支持记录类型%ROWTYPE，不支持数组等，原Oracle用到这些的必须改写

五、Job的移植

Job是Oracle的定时任务实现的方法，MySQL6中用Event实现，具体语法请参考MySQL手册。

在MySQL中使用event请注意，默认它是不运行的，您可以

1) 保证MySQL定时任务event scheduler运行,需要MySql 5.1.6以上，并且在启动后执行SET GLOBAL event_scheduler = ON(也可以在初始配置文件比如my.ini中加入event_scheduler = ON的参数)

2) 启用event功能后，每次执行会往MySQL的错误日志文件写一些信息(data目录下的“主机名.err”文件)，导致这个文件越来越大(除非经常做flush log *** 作)。所以，如果您的event执行很频繁，可在my.ini中加参数console=TRUE，这样执行event的信息就不会写进来了

六、用户的移植

Oracle的用户管理和MySQL下有较大区别，请分别建立用户，并赋予合适的权限。

七、应用程序的移植

由于语法细节上的差异，导致很多SQL语句需要改写。笔者记下了所有移植过程中碰到的SQL语句细节差异，这些也是一般项目可能会用到的地方，虽然肯定不全，但也列出来以供参考：

1)Oracle的to_char函数不能再使用，换用如CONCAT(14.3)的形式，为了提高应用程序兼容性，建议手工写一个

2)Oracle的to_date函数不能再使用，建议手工写一个添加到MySQL数据库

3)Oracle的decode函数不能再使用，换用SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END 的形式

4)nvl这样的一些专用函数，MySQL是没有的，可以把

select nvl(to_char(num),'nothing') from t_equipment转换成

select case num when num then num else 'nothing' end from t_equipment

5)instr之类的函数，函数名相同，但参数个数不同

6)Oracle的sysdate要写成sysdate()的形式

7)包的形式已经取消，所以原来以包的方式调用的过程如xx_pack.xxx要写成xxx()

8)带进制字符转数字

Oracle风格：TO_NUMBER(strTmp,'XX') TO_NUMBER(’9’)

MySQL风格：CONV(strTmp,16,10) CONV(’9’,10,10) 如果字符串前后有加减 *** 作，会隐含转换成数字

9) 不能再有直接调用序列的形式，如果一定需要，可以模拟实现一个

10)日期直接加减的含义不同了，比如Oracle中sysdate + 1 变成了sysdate() + interval 1 day(注意如果写成sysdate() + 1 语法还是正确的，但含义是错误的)

查询select sysdate() + 1 from dual 在MySQL得到比如 20080223153234(= 20080223153233 + 1)的数

而在Oracle中会得到第二天当前时刻。

11) MySQL单纯的date类型只是日期不带时间，DATETIME或TIMESTAMP带有时间，用DATE_FORMAT函数可以控制显示形式

12)select 'abc' || 'd' from dual 两个数据执行的结果不同(语法都能通过)，MySQL要写成select concat('abc' , 'd')的形式

13) Oracle高级功能，如带有暗示索引的select语句，MySQL是不支持的(语法可以通过)

14)有些MySQL的保留字不能直接用在SQL语句里,要加表名或别名限制，如select RIGHT FROM XX要改成select a.RIGHT FROM XX a

15) Oracle的子查询可以不起别名，但MySQL是必须的，比如下面的别名aa：

select field1 from (select sysdate() as field1 from dual) as aa

16)很多系统表名都是不同的,比如，列出某个表的信息：

select * from tab where TName='T_TEST'改成

select table_name,table_type from information_schema.tables where table_schema = 'user' and table_name=' T_TEST '

17)MySQL下update时不能有本身的子查询

update T_TEST set Flag = 0 where field1 in

(select distinct b.field1 from T_TEST b where b.flag=1)

18)Oracle下’’和null等价，而MySQL则不然

select 1 from dual where '' is null在Oracle下可以取到记录，在MySQL下不能

dual表的使用，substr、trim等函数的主要使用方式和Oracle类似

八、小结和建议

看起来，Oracle移植到MySQL似乎挺麻烦，有没有一键完成的简单办法?呵呵，我没有找到，除非您只使用基本表，只使用基本SQL语句访问它。当然，建议大家初始设计的时侯，就考虑到多数据库的支持，权衡一下使用一些高级功能带来的好处和对可移植性方面带来的损害，这会大大减少后期移植时面对的问题另外，在应用架构设计时，也建议使用较好的框架去屏蔽这些差异，比如J2EE的Hibernate框架等。

感谢伟大的Oracle，给我们提供了很多的高级功能，有很多是MySQL没有的，因此，在移植时你不得不放弃一些非必须的功能，比如,全表cache、物化视图、函数索引等如果该功能是必须的，您可能要使用别的方式来实现，或者转到应用程序层面来考虑。当然，这些功能MySQL今天没有，不代表明天也没有，我们可以拭目以待。

由于开源软件的原因，MySQL的bug或者缺陷有时还会干扰你，请仔细测试和优化您的应用程序，调整MySQL的配置参数，确保它可以运行得和Oracle下一样好。

（责任编辑：王倩）

insert all

into dept(deptno,dname) values(50,'测试一部')

into dept(deptno,dname) values(51,'测试二部')

SELECT 1 FROM DUAL

insert all into dept(deptno,dname)

into dept(deptno,dname) values(60,'测试一部')

into dept(deptno,dname) values(61,'测试二部')

SELECT 62,'测试三' FROM DUAL

begin

insert into dept(deptno,dname) values(70,'测试一部')

insert into dept(deptno,dname) values(71,'测试二部')

end

oracle的序列 sequences(mysql自增长)

sequences的使用分两步

1、新建一个序列(工具建)

2、如何使用

3、函数对比

https://blog.csdn.net/qq_39137554/article/details/76034335

3.1）数学函数

oracle: ceil(-1.001) mysql: ceiling(-1.001)

3.2)字符串函数

1）ascii(str)一样：返回一个字符对应的ascii码值

2）chr||char （不同）

ORACLE:chr(97) MYSQL:char(97)返回这个整数所代表的 ASCII 码值

oracle示例：

3）INSTR||locate（mysql新增 mysql可以用instr locate）

INSTR(s1,s2,[,n1],[n2]) 返回s1中，子串s2从n1开始，第n2次出现的位置。n1,n2默认值为1

oracle例子：

-- 查询s字符串在sdsq中第一次出现的位置，从头开始查找,索引从1开始计算 返回1

select INSTR('sdsq','s') value from dual

-- 查询s字符串在sdsq中第一次出现的位置，从索引从2开始查找 返回3

select INSTR('sdsq','s',2) value from dual

-- 查询s字符串在sdsq中第一次出现的位置，从索引从2开始查找，如果没有查找到，返回0

select INSTR('sdsq','s',2,2) value from dual

mysql:

instr||locate

4）length||char_length(不同)

SELECT length('AAAASDF') VALUE FROM DUAL

CONCAT：连接

LPAD：在左边加点内容（左追加）

RPAD：在右边加点内容（右追加）

把sal变成10位，不足10位的，右侧 补*

select RPAD(sal,10,'*') as v from emp

REPLACE 替换一样

LOWER 转小写一样

UPPER 转大写 一样

initcap首字母大写

SELECT REPLACE('18604001139', '0400', '****') value from dual

select LOWER(ename) username from emp 转小写

select UPPER(ename) username from emp

select initcap(ename) username from emp

3.2.4.1TRIM:去掉首尾的指定字符，不能去除中间的

oracle:trim(子串 from 父串)

select TRIM('S' FROM 'SSMISSTH') value from dual//MISSTH

select TRIM(' ' FROM ' ab c ') value from dual//ab c

3.2.4.2SUBSTR(oracle)||substring(mysql)

SUBSTR(原字符串，开始处位置，截取长度)

select SUBSTR('String',2,5) value from dual //tring

ORACLE:

练习1：

1.写一个查询,用首字母大写，其它字母小写显示雇员的 ename，显

示名字的长度，并给每列一个适当的标签，条件是满足所有雇员名字

的开始字母是J、A 或 M 的雇员，并对查询结果按雇员的ename升序

排序。（提示：使用initcap、length、substr）

练习2 都用函数解决

• 1.查询员工姓名中中包含大写或小写字母A的员工姓名。

select ename

from emp

where instr(ename,'A')>0 OR instr(ename,'a')>0

• 2.查询部门编号为10或20，入职日期在81年5月1日之后，并且姓名中

包含大写字母A的员工姓名，员工姓名长度（提示，要求使用INSTR函

数，不能使用like进行判断)

select ename,length(ename) len

from emp

where deptno in(10,20)

and hiredate>'1-5月-1981'

and instr(ename,'A')>0

• 3.查询每个职工的编号,姓名，工资

– 要求将查询到的数据按照一定的格式合并成一个字符串.

– 前10位：编号,不足部分用 填充,左对齐(右填充)

– 中间10位：姓名，不足部分用 填充,左对齐

– 后10位：工资，不足部分用*填充,右对齐

select rpad(empno,10,' ')||rpad(ename,10,' ')||lpad(sal,10,'*') as value

from emp

select concat(concat(rpad(empno,10,' ') , rpad(ename,10,' ')),lpad(sal,10,'*')) as value

from emp

https://www.cnblogs.com/aipan/p/7941917.html

3.4.1 to_char vs date_format/ time_format

select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') VALUE FROM emp

select to_char(-125.8, '999D99S') from dual

3.4.2 to_date vs STR_TO_DATE(str,format)

select to_date('2021-04-23 15:12:20','YYYY-MM-DD HH24:MI:SS') VALUE FROM DUAL

3.4.3 to_number vs CAST("123" AS SIGNED INTEGER)

select to_number('12,454.8-', '99G999D9S') value from dual

select to_number('-12,454.8', 'S99G999D9') value from dual

1.显示服务器系统当前时间，格式为2007-10-1217:11:11(提示：使用to_char函数)

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')

from dual

3.查询员工姓名，工资，格式化的工资（￥999,999.99）(提示：使用to_char函数)

select to_char(sal,'L999G999D99')

from emp

4.把字符串2015-3月-18 13:13:13 转换成日期格式

select to_date('2015-3月-18 13:13:13','YYYY-MON-DD HH24:MI:SS')

from dual

select cast(11 as unsigned int) / 整型 /

select cast(11 as decimal(10,2)) / 浮点型 /

3.5.1 nvl/COALESCE vs ifnull

**NVL函数等价于mysql的ifnull

– 语法:NVL (expr1,expr2)

– 如果expr1不是null,返回expr1,否则返回expr2

select comm,nvl(comm,0) from emp

select comm,coalesce(comm,sal,0) from emp

3.5.2 nvl2 vs if

NVL2函数

– 语法:NVL2(expr1,expr2,expr3)

– 如果expr1不是null,返回expr2,否则返回expr3

select nvl2(comm,'无奖金','有奖金') from emp

3.5.3 NULLIF vs 无(可以考虑用if 建议了解，不用掌握)

NULLIF函数

– 语法: NULLIF(expr1,expr2)

– 比较两个表达式,如果相等，返回null,否则，返回第一个表达式

3.5.4 DECODE（解密的意思）/case vs if/case 多分支情况

DECODE(字段| 表达式, 条件1,结果1[,条件2,结果2…，][,缺省值] )

SELECT ename, deptno,

decode(deptno,

10,' 销售部',

20,' 技术部',

30 , ' 管理部',

' 无') deptname

FROM emp

CASE expr

WHEN comparison_expr1 THEN return_expr1

[WHEN comparison_expr2 THEN return_expr2

WHEN comparison_exprn THEN return_exprn

ELSE else_expr]

END

SELECT ename, deptno,

(CASE deptno

WHEN 10 THEN ' 销售部'

WHEN 20 THEN ' 技术部'

WHEN 30 THEN ' 管理部'

ELSE ' 无' END) deptname FROM emp

EXTRACT ([YEAR] [MONTH][DAY] FROM [日期表达式])

SELECT ename, sal, MONTHS_BETWEEN(SYSDATE,hiredate) months

FROM emp

ORDER BY months

SELECT ename, sal, hiredate, ADD_MONTHS(hiredate,3) new_date

FROM emp

WHERE hiredate>'01-1月-82'

SELECT NEXT_DAY(sysdate,'星期二') NEXT_DAY

FROM DUAL

SELECT Last_DAY('1-2月-2019') last_DAY

FROM DUAL

select EXTRACT (YEAR FROM HIREDATE) AS 年 from emp

select EXTRACT (YEAR FROM HIREDATE) AS 年 from emp

select EXTRACT (MONTH FROM HIREDATE) AS 年 from emp

select EXTRACT (DAY FROM HIREDATE) AS 年 from emp

4、外连接的加号定法。oralce支持，mysql不支持

查询所有雇员姓名，部门编号，部门名称， 包括没有员工

的部门也要显示出来

select e.ename,d.deptno,d.dname

from emp e,dept d

where e.deptno(+)=d.deptno

4、分页的处理

比较日期相等

https://www.cnblogs.com/bailing80/p/11440927.html

---