1.创建上的不同
1)mysql是将参数设置在文件外面,而oracle是在文件里面设置
2)mysql和oracle一些类型的区别
mysql: uuid(),now(),varchar
oracle:sys_guid(),sysdate,varchar2
3)查询表的列名
mysql查询的表:information_schema.COLUMNS
oracle查询的表:USER_TAB_COLUMNS
4)申明变量与赋值的语法
mysql:
DECLARE testCount int DEFAULT 0;
set testCount = 3;
oracle:
v_sql varchar2(1000);
v_sql :=‘测试’;
5)循环语法:
mysql(这里采用游标来处理循环):
DECLARE cols CURSOR FOR ( select t.name,t.code,t.index_code,t.unit
from TMP_CONF t
inner join (select a.COLUMN_NAME from information_schema.COLUMNS a
where a.TABLE_NAME=upper(source_table_name))b on t.index_code = b.COLUMN_NAME );
-- 用在while循环前的,如果游标到了最后就会将之前定义的s设置为1
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;
-- 打开游标
OPEN cols;
FETCH cols into name2,code2,index_code2,unit2;
WHILE s <> 1 DO SET v_sql =CONCAT('sql语句');
set @sql = v_sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
-- 释放掉预处理段
DEALLOCATE PREPARE stmt1 ;
FETCH cols into name2,code2,index_code2,unit2;
END WHILE;
-- 关闭游标
CLOSE cols;
oracle:
for col in (
select t.name,t.code,t.index_code,t.unit from TMP_CONF t
where t.index_code in(
select T.COLUMN_NAME from USER_TAB_COLUMNS T
where T.TABLE_NAME=upper(source_table_name)
)
order by t.index_code asc
) loop
v_sql := 'sql语句';
execute immediate v_sql;
end loop;
6)mysql没有decode,可以用case when来替换
mysql:
(
CASE WHEN t.PROC_ERROR = null
THEN '数据值为空'
ELSE CONCAT(t.PROC_ERROR,';','数据值为空')
END)
oracle:
decode(t.PROC_ERROR, null, '数据值为空', t.PROC_ERROR || ';' || '数据值为空')