Oracle学习 第29天 存储过程生成报表

折腾了一下午，终于把一个报表存储过程的其中一个移植到Oracle了。。。Oracle和PL/SQL太不人性化了，有些SQL Server很简单的东西，就是不支持，拐弯抹角又能实现，那干嘛不直接支持呢。

由于报表很多公司内部业务逻辑，就不放上来了，总结几个坑：

一、报错很不清晰，存储过程里面干脆就不报错（不检查语法），复制出来执行报错也不能一目了然定位到错误位置。

二、存储过程里面有拼一个很长的字符串，原先SQL Server2012是直持超长的，SQL 2008的话要每4000就用 ' + ' 这样隔一下也是可以，到Oracle，最大只能设置VARCHAR2(4000)，超过就不行了。要改成CLOB类型才可以（类似TEXT）。

三、语法差异倒还好，每一句都要分号，老是忘，PL/SQL的提示又不明确，不能一眼看出来是分号忘了加（也可能是不习惯）

1、IF ELSE要注意THEN和结束的分号

IF ... THEN

...

ELSIF ... THEN

...

ELSE

...

END IF;

2、变量不再加@，这样有时都不好区分变量和列名，除非名称要规范区分一下。

原先SQL SERVER赋值有两种，一种是SET @XX = 'abc'，或者 SELECT @XX = xx FROM XXX

Oracle赋值改为 XX := 'abc'; 或者 SELECT xx INTO XX FROM XXX;

3、字符串相加的 + ，改成了 || ，ISNULL变成了NVL，利用CONVERT转换2位小数的，要用ROUND，PRINT改成了dbms_output.put_line

4、日期不能自动转化和兼容，SQL Server 可以把日期格式赋一个 '2017-07-16' 这样的字符串，Oracle要用to_date函数转换

在拼字符串时，就不得不转两次，先把日期转成字符串去拼接，再转成日期格式

ToDate := to_date(''' ||  to_char(dNewToDate,'YYYY-MM-DD HH24:MI:SS') || ''', ''YYYY-MM-DD HH24:MI:SS'') ;

固定日期也要这样转换一下

WHERE DealDate >= to_date(''2009-01-01 00:00:00'', ''yyyy-mm-dd hh24:mi:ss'')

要加减年份没有专门函数，只能用add_months来加减12的倍数。。。

一般没转换都会报错，即使拼在字符串里，刚才检查代码居然有一处没报错，运行了也正常（虽然没有进到相应IF判断里）

WHERE DealDate >= ''2012-01-01 00:00:00''

5、存储过程里面不能CREATE、TRUNCATE、SELECT * FROM 。。。

这个是最坑爹的了，可以用字符串拼CREATE和TRUNCATE，再EXECUTE IMMEDIATE sSQL，但经常各种问题。

6、DELETE FROM不能直接JOIN，而是要EXISTS

SQL Server可以UPDATE XX FROM XX JOIN，也可以DELETE XX FROM XX JOIN

Oracle不支持这样写了，只能

DELETE FROM XXX xxx
WHERE EXISTS (
　　SELECT 1 FROM XX xx
　　WHERE xx.xx = xxx.xx
);

7、字符串里的链接服务器

原先SQL Server如果把不存在的链接服务器直接放在脚本里，如果没有相应链接服务器是会报错，一般都是拼在字符串里就可以。

Oracle的链接服务器是【DBName.TableName@ServerName】这种格式，即使拼在脚本里也会报错，只能再拼一次，即在字符串里再拼字符串。。。

四、存储过程里面不能直接输出SELECT * FROM XX。。。只能用游标OUT出来

临时表也不能设自增主键（所有表都这样），只能用序列，好在有个默认的ROWID功能差不多。

OPEN sCUR FOR SELECT * FROM tCanSell ORDER BY ROWID;

五、利用存储过程上右键 -> 测试是可以得到游标并查看结果，直接用语句EXEC还不知道怎么看。。。执行完啥事也没发生，没地方看结果。

六、语法是Oracle定了的，但有些调试和输出可能是PL/SQL的交互特性，下次换其它编辑器试试看，比如续一秒的TOAD

来自东方神秘力量的加持