区别（转：）
1、if else分⽀
mysql:⽤elseif Oracle:⽤elsif
可以⽤select直接查看获取的值或者⼀个变量。
1 create procedure pd_testif2(in num int,out str varchar) 2 begin
3 if num=1 then 4 set str='⼀';
5 elseif num=2 then 6 set str='⼆'; 7 end if; 8 end; 9 --调⽤
10 call pd_testif2(1,@str);11 select @str;
执⾏结果：⼀ 2、循环
mysql:while 条件 do Oracle:while 条件 loop 语句块 语句块 end while; end loop;
1 create procedure pd_testwhile22(in num int,out sum int) 2 begin
3 --定义变量
4 declare i int default 1; 5 declare sum int default 0; 6 --循环条件
7 while i 14 call pd_testwhile22(100,@str);15 select @str; 执⾏结果：5050 补充：Oracle和MySQL的⼀点区别： 函数、⽇期、limit关键字区别（转 不同点: 1. mysql: IFNULL(a,b)oracle: NULLIF(a,b) 2. mysql: 可以⽤Date类型的⽇期进⾏⽐较时间⽐较 oracle:必须⽤to_date()函数和to_char()函数配合转换成统⼀格式的⽇期字符串,然后进⾏⽐较 3. mysql: 可以⽤YEAR(), MONTH(), NOW()等函数获取年/⽉/当前⽇期等oracle:必须⽤to_char(sysdate, 'yyyy'),to_char(sysdate, 'MM')获取4. mysql: 字符串识别\"\oracle:字符串只识别'' 5. mysql: 可以⽤limit关键字截取所需的数据⾏ oracle:必须⽤rownum关键字通过⼦查询来截取所需数据⾏ 表、列、索引、空字符串---转（） 2.1 创建表（同）create table tableName(columnName1 int,columnName2 int) 2.2 删除表（异）MySQL: drop table if exists tableName Oracle: drop table tableName 注：Oracle没有if exists关键字，也没⽤类似if exists的SQL语法。 3 列 3.1 添加列（异）MySQL: A. alter table tableName add column columnName1 int; B. alter table tableName add column columnName1 int, add column columnName2 int;注：其中关键字column可有可⽆。 Oracle: A. alter table tableName add columnName1 int;B. alter table tableName add (columnName1 int); C. alter table tableName add (columnName1 int, columnName2 int); 注：对于A，只有添加单列的时候才可使⽤，对于添加多列时需要使⽤C，不能像MySQL那样重复使⽤add column关键字。3.2 删除列（异）MySQL: A. alter table tableName drop column columnName1 B. alter table tableName drop column columnName1, drop column columnName2注：其中关键字column可有可⽆。 Oracle: A. alter table tableName drop column columnName2B. alter table tableName drop (columnName1) C. alter table tableName drop (columnName1,columnName2) 注：对于A，只有删除单列的时候才可使⽤，对于删除多列时需要使⽤C，不能像MySQL那样重复使⽤drop column关键字。3.3 修改列名（异）MySQL: alter table tableName change column columnNameOld columnNameNew columnType; Oracle: alter table tableName rename column columnNameOld to columnNameNew;3.4 修改列类型（说明） Oracle中，在列有数据的时候，⽆法修改列类型；没有数据时可以。MySQL中，⽆论列是否有数据都可以修改列类型。 但是当有数据是，直接修改列类型都可能对数据造成丢失等，所以⼀般需要结合具体的业务来对列数据做处理后，再修改列类型类型。所以修改列的类型并⾮使⽤SQL语句进⾏⼀步到位的修改，⽽是通过以下流程：A. 添加临时列 B. 将需要更改的列的值经过类型转换的验证后，赋值给临时列C. 删除原有列 D. 将临时列的列名修改为原有列列名 4 索引 在整个数据库内，MySQL的索引可以同名，也就是说MySQL的索引是表级别的；但是Oracle索引不可以同名，也就是说Oracle的索引是数据库级别的。4.1 创建索引（同） create index indexName on tableName (columnName);4.2 删除索引（异）MySQL: alter table tableName drop index indexName Oracle: drop index indexName4.3 查询表的索引（异）MySQL: show index from tableName Oracle: select index_name, table_name, column_name from user_ind_columns where table_name=' tableName ' 5 空字符串问题 Oracle中空字符串''就是null（也就是说，只有null，没有空字符），⽽MySQL是区分null和''的。 对于使⽤语句：select * from table1 where user_name <> ''来查询列user_name不为空（不为null且不为空字符）时，Oracle会查不出任何结果，⽽MySQL可以正常运⾏。这⾥MySQL之所以可以得到正确结果，还因为⽐较符号<>会先将列为null的内容进⾏过滤，然后再⽐较内容 是否为空字符串。 这就要求⼀⽅⾯，以后在编写代码的时候，尽量保证不会往数据库插⼊空字符串''这样的值，要么保持有数据，要么保持为null。另外，对于MySQL中已经同时存在Null和''时，所有判断是否为null或者''的地⽅改为判断列的长度是否为0。 数据类型，基本语法，循环语句，存储过程&Function，⽤户权限，触发器 等 转（） 数据类型编号ORACLE MYSQL 注释 DECIMAL就是NUMBER(10,2)这样的结构INT就是是NUMBER(10)，表⽰整型； MYSQL有很多类int型，tinyint mediumint bigint等，不同的int宽度不⼀样 ⽇期字段的处理 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（），INTERVAL7 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 int / INTEGERMysql中INTEGER等价于intSQLEXCEPTION 详见<<2009001-eService-O2MG.doc>>中2.5 Mysql异常处理 从ORACLE迁移到MYSQL,所有CONSTANT常量只能定义成变 mysql中没有CONSTANT关键字 量光标 : mysql中有替代⽅案 详见<<2009001-eService-O2MG.doc>>中2.2 光标处理 1NUMBER2Varchar2（n） int / DECIMALvarchar(n) 3DateDATATIME 4INTEGER5EXCEPTION 6CONSTANT VARCHAR2(1)7 TYPE g_grp_cur IS REFCURSOR; 数组: mysql中借助临时表处理 TYPE unpacklist_type IS TABLE 或者直接写逻辑到相应的代码中， 8OF VARCHAR2(2000) INDEX BY详见<<2009001-eService-O2MG.doc>>中2.4 数组处理 直接对集合中每个值进⾏相应的处 BINARY_INTEGER; 理 MYSQL有⾃动增长的数据类型，插⼊记录时不⽤操作此字段，会⾃动获得数据值。ORACLE没有⾃动增长的数据类型，需要建⽴ 9⾃动增长的序列⾃动增长的数据类型 ⼀个⾃动增长的序列号，插⼊记录时要把序列号的下⼀个值赋于此字段。空字符的处理 MYSQL的⾮空字段也有空的内容，ORACLE⾥定义了⾮空字段就不容许有空的内容。按MYSQL的NOT NULL来定 10NULLNULL 义ORACLE表结构, 导数据的时候会产⽣错误。因此导数据时要对空字符进⾏判断，如果为NULL或空字符，需要把它改成⼀个空格的字符串。 基本语法 编号类别ORACLEMYSQL 1 变量的声明⽅ li_index NUMBER := 0 式不同 2 变量的赋值⽅ lv_inputstr := iv_inputstr 式不同 EXIT; 3 跳出（退出）while 条件 loop语句不同exit; end loop; 4 5 6 TYPE g_grp_cur IS REF 定义游标CURSOR; TYPE unpacklist_type IS 可以使⽤临时表代替oracle数 TABLE OF VARCHAR2(2000) 定义数组组, 也可以循环拆分字符来替 INDEX BY 代oracle数组. BINARY_INTEGER; \"-- message\" 或 \"/** …. \"-- message\" 或 \"/* …. 注释⽅式不同 */\" 或\"/* …. */\"*/\" 或 \"#\" 注释 1. mysql 使⽤DECLARE定义局部变量. 定义变量语法为: DECLARE var_name[,...] type DECLARE li_index INTEGER[DEFAULT value] 要给变量提供⼀个默认值，需要包DEFAULT 0含⼀个DEFAULT⼦句。值可以被指定为⼀个表达式， 不需要为⼀个常数。如果没有DEFAULT⼦句，初始值为NULL。 1. oracle变量赋值使⽤:= SET lv_inputstr = iv_inputstr mysql 使⽤赋值使⽤set关键字. 将⼀个值赋给⼀个变 量时使⽤\"=\". LEAVE procedure name;1. oracle: 如果exit语句在循环中就退出当前循环.如果 exit语句不再循环中,就退出当前过程或⽅法. label_name:while 条件 do Mysql: 如果leave语句后⾯跟的是存储过程名,则退出 leave label_name;当前存储过程. 如果leave语句后⾯跟的是lable名. 则退end while label_name;出当前lable. DECLARE cursor_nameoracle可以先定义游标,然后给游标赋值. CURSORmysql定义游标时就需要给游标赋值. Mysql定义游标出FOR SELECT_statement;⾃ Mysql 5.1 参考⼿册20.2.11.1.声明光标. ⽬前可以使⽤临时表来代替oracle数组. 详见<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.4 Mysql数组处理部分 7 8 mysql注释来⾃ MySQL 5.1参考⼿册 9.5. 注释语法, 建议同oracle⼀样, 单⾏⽤--, 多⾏/* */ 1. MYSQL⽇期字段分DATE和TIME两种. ORACLE⽇期字段只有DATE，包含年⽉⽇时分秒信 ⾃带⽇期时间Oracle时间格式：yyyy-MM-ddMysql时间格式：%Y-%m-%d息. 函数格式不同hh:mi:ss%H:%i:%s2. mysql中取当前系统时间为now()函数,精确到秒. oracle中取当前数据库的系统时间为SYSDATE, 精确到秒. ⽇期相加: date_add(now(),INTERVAL 180 DAY) 当前时间加N天: sysdate+N ⽇期加减⽇期相减: date_sub('1998-01- 当前时间减N天: sysdate-N 01 00:00:00', interval '1 1:1:1'day_second) 1. oracle使⽤||连接字符串,也可以使⽤concat函数. 但Oracle的concat函数只能连接两个字符串. Mysql使⽤concat⽅法连接字符串. MySQL的concat函数可以连接⼀个或者多个字符串,如 mysql> select concat('10'); 结果为: 10. mysql> select concat('11','22','33','aa'); 结果为:112233aa 2. \"||\"在Mysql是与运算 9 字符串连接符 result := v_int1||v_int2; 不同 set result =concat(v_int1,v_int2); 10 CURSOR l_bk_cur ISDECLARE l_bk_cur CURSOR SELECT FOR SELECTB.BK_HDR_INT_KEY,B.BK_HDR_INT_KEY,B.BK_NUMB.BK_NUM FROM ES_SR_DTL_VRB A, FROM ES_SR_DTL_VRB A,ES_BK_HDR BES_BK_HDR B WHERE WHERE详见<<2009002-OTMPPS-Difficult Questions-定义游标不同 A.BK_HDR_INT_KEY =A.BK_HDR_INT_KEY =0001.doc>>中2.2 Mysql游标处理部分B.BK_HDR_INT_KEYB.BK_HDR_INT_KEY AND b.BK_STATUS != AND b.BK_STATUS !=ES_BK_PKG.g_status_canES_BK_PKG.g_status_can AND ANDA.SR_HDR_INT_KEY =A.SR_HDR_INT_KEY =ii_sr_hdr_int_key;ii_sr_hdr_int_key;事务回滚GOTO语句 ROLLBACK;GOTO check_date; ROLLBACK;GOTO check_date; oracle和mysql中使⽤⽅法相同oracle和mysql中使⽤⽅法相同 1112 函数编 号类别ORACLE1 round(1.23456,4) 2abs(-1)3ceil(-1.001))数字函数4 floor(-1.001)5Max(expr)/Min(expr)6ascii(str)7CHAR(N,...)8REPLACE(str,from_str,to_str)9INSTR('sdsq','s',2)字符串函10数SUBSTR('abcd',2,2) 11instr(‘abcdefg’,’ab’)MYSQL round(1.23456,4) abs(-1) ceiling(-1.001) floor(-1.001) Max(expr)/Min(expr) ascii(str) CHAR(N,...) REPLACE(str,from_str,to_str) INSTR('sdsq','s') substring('abcd',2,2) locate(‘ab’,’abcdefg’) 注释⼀样： ORACLE：select round(1.23456,4) value fromdual MYSQL：select round(1.23456,4) value功能: 将当前数据取绝对值⽤法: oracle和mysql⽤法⼀样mysql: select abs(-1) value oracle: select abs(-1) value from dual功能: 返回不⼩于 X 的最⼩整数⽤法: mysqls: select ceiling(-1.001) value oracle: select ceil(-1.001) value from dual功能: 返回不⼤于 X 的最⼤整数值⽤法: mysql: select floor(-1.001) value oracle: select floor(-1.001) value from dual功能:返回 expr 的最⼩或最⼤ 值。MIN() 和 MAX() 可以接受⼀个字符串参数；在这 种情况下，它们将返回最⼩或最⼤的字符串传下。⽤法: ROACLE: select max(user_int_key) fromsd_usr; MYSQL: select max(user_int_key) from sd_usr;功能:返回字符串 str 最左边的那个字符的 ASCII 码值。如果 str 是⼀个空字符串， 那么返回值为 0。如果 str 是⼀个 NULL，返回值也是 NULL.⽤法: mysql:select ascii('a') value oracle:select ascii('a') value from dual 功能:CHAR() 以整数类型解释参数，返回这个整数所代表的 ASCII 码值给出的字符组成的字符串。NULL 值将被忽略.⽤法: mysql:select char(97) value oracle:select chr(97) value from dual 功能: 在字符串 str 中所有出现的字符 串 from_str 均被 to_str 替换，然后返回这个字符串.⽤法: mysql: SELECT REPLACE('abcdef', 'bcd','ijklmn') value oracle: SELECT Replace('abcdef', 'bcd', 'ijklmn')value from dual 参数个数不同 ORACLE: select INSTR('sdsq','s',2) value fromdual（要求从位置2开始） MYSQL: select INSTR('sdsq','s') value（从默认的位置1开始）函数名称不同： ORACLE: select substr('abcd',2,2) value fromdual MYSQL: select substring('abcd',2,2) value函数名称不同： instr -> locate（注意：locate的⼦串和总串的位置要互换） ORACLE: SELECT instr('abcdefg', 'ab') VALUEFROM DUAL MYSQL: SELECT locate('ab', 'abcdefg') VALUE函数名称不同： 12length（str）char_length() 13REPLACE('abcdef', 'bcd', 'ijklmn')REPLACE('abcdef', 'bcd', 'ijklmn') 14LPAD('abcd',14, '0')LPAD('abcd',14, '0') 15UPPER(iv_user_id)UPPER(iv_user_id) 16LOWER(iv_user_id)LOWER(iv_user_id) IFNULL(u.email_address, 10)17nvl(u.email_address, 10) 或 ISNULL(u.email_address) 控制流函数 18 DECODE(iv_sr_status,g_sr_status_com,⽆，请⽤IF或CASE语句代替.ld_sys_date, NULL)IF语句格式:(expr1,expr2,expr3) 19TO_CHAR(SQLCODE)date_format/ time_format 20to_date(str,format)STR_TO_DATE(str,format) ORACEL: SELECT length('AAAASDF') VALUE FROM DUAL MYSQL: SELECT char_length('AAAASDF')VALUE⼀样： ORACLE: SELECT REPLACE('abcdef', 'bcd','ijklmn') value from dual MYSQL: SELECT REPLACE('abcdef', 'bcd','ijklmn') value⼀样： ORACLE: select LPAD('abcd',14, '0') value fromdual MYSQL: select LPAD('abcd',14, '0') value fromdual⼀样： ORACLE: select UPPER(user_id) from sd_usr;MYSQL: select UPPER(user_id) from sd_usr;⼀样： ORACLE: select LOWER(user_id) from sd_usr;MYSQL: select LOWER(user_id) from sd_usr;函数名称不同（根据不同的作⽤进⾏选择）：ORACLE: select u.email_address, nvl(u.email_address, 10) value from sd_usr u(如果u.email_address=NULl,就在DB中⽤10替换其值) MYSQL: 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 1. 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 2. mysql If语法说明 功能: 如果 expr1 是TRUE (expr1 <> 0 and expr1<> NULL)，则IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境⽽定。⽤法: mysql: SELECT IF(1>2,2,3);函数名称不同 SQL> select to_char(sysdate,'yyyy-mm-dd') fromdual; SQL> select to_char(sysdate,'hh24-mi-ss') fromdual; mysql> select date_format(now(),'%Y-%m-%d');mysql> select time_format(now(),'%H-%i-%S');函数名称不同： ORACLE:SELECT to_date('2009-3-6','yyyy-mm-dd') VAULE FROM DUAL MYSQL: SELECT STR_TO_DATE('2004-03-01', '%Y-%m-%d') VAULE函数名称不同： TRUNC函数为指定元素⽽截去的⽇期值。 ORACLE： select trunc(-1.002) value from dualMYSQL：select cast(-1.002 as SIGNED) valueMYSQL： 字符集转换 : CONVERT(xxx USING gb2312)类型转换和SQL Server⼀样,就是类型参数有点点不同 : CAST(xxx AS 类型) , CONVERT(xxx,类型)，类型必须⽤下列的类型： 可⽤的类型 ⼆进制,同带binary前缀的效果 : BINARY 字符型,可带参数 : CHAR() ⽇期 : DATE 时间: TIME ⽇期时间型 : DATETIME 浮点数 : DECIMAL 整数 : SIGNED ⽆符号整数 : UNSIGNED 类型转换函数21 trunc(-1.002) cast(-1.002 as SIGNED) 22 23 24 ⽇期函数25 26 27 28 29 SQL函数 函数名称不同 ORACLE:SELECT TO_NUMBER('123') ASVALUE FROM DUAL; TO_NUMBER(str)CAST(\"123\" AS SIGNED INTEGER) MYSQL: SELECT CAST(\"123\" AS SIGNEDINTEGER) as value; SIGNED INTEGER:带符号的整形写法不同： ORACLE:select SYSDATE value from dual SYSDATEnow() / SYSDATE() MYSQL:select now() valueselect sysdate() value函数名称不同： ORACLE: SELECT Next_day(sysdate,7) valueFROM DUAL ⾃定义⼀个函MYSQL: SELECT Next_day(sysdate,7) 数:F_COMMON_NEXT_DAY(date,int)F_COMMON_NEXT_DAY(SYSDATE(), 3) value from DUAL; (3:指星期的索引值)返回的指定的紧接着下⼀个星期的⽇期函数名称不同: ORACLE: SELECT ADD_MONTHS(sysdate, 2) DATE_ADD(sysdate(), interval 2 ADD_MONTHS(sysdate, 2)as value from DUAL; month) MYSQL: SELECT DATE_ADD(sysdate(),interval 2 month) as value from DUAL;功能: 返回两个⽇期之间的天数。⽤法: mysql: SELECT DATEDIFF('2008-12-30','2008-2个⽇期相减(D1-D2)DATEDIFF(date1,date2) 12-29') AS DiffDate oracle: 直接⽤两个⽇期相减（⽐如d1-d2=12.3） Oracle内置函数SQLCODE和SQLERRM是特别 MYSQL中没有对应的函数，但JAVA⽤在OTHERS处理器中，分别⽤来返回Oracle的 SQLCODE中SQLException。getErrorCode()函错误代码和错误消息。 数可以获取错误号MYSQL: 可以从JAVA中得到错误代码，错误状 态和错误消息 Oracle内置函数SQLCODE和SQLERRM是特别 MYSQL中没有对应的函数，但JAVA⽤在OTHERS处理器中，分别⽤来返回Oracle的 SQLERRM中SQLException。getMessage()函数错误代码和错误消息。 可以获取错误消息MYSQL: 可以从JAVA中得到错误代码，错误状 态和错误消息 在MYSQL中是⾃动增长列. 如下⽅法获取最新ID: START TRANSACTION; INSERT INTO user(username,password) SEQ_BK_DTL_OPT_INT_KEY.NEXTVAL⾃动增长列 VALUES (username,MD5(password)); SELECT LAST_INSERT_ID() INTO id; COMMIT;⼀样： 30SUM(enable_flag)SUM(enable_flag) 31 在MYSQL中⽆相应的⽅法，其作⽤是 dbms_output.put_line每⾏只能显⽰255个字符， DBMS_OUTPUT.PUT_LINE(SQLCODE)在控制台中打印，⽤于测试，对迁移 超过了就会报错 ⽆影响。 ORCALE： SELECT SUM(enable_flag) FROMSD_USR; MYSQL： SELECT SUM(enable_flag) FROMSD_USR; 循环语句编号类别 ORACLE MYSQL 注释 1. mysql和oracle除了关键字有⼀个字差别外(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可以包括⼀个或多个语句。 1 IF iv_weekly_day = IF iv_weekly_day ='MON'THEN'MON'THEN set ii_weekly_day = ii_weekly_day := 'MON';'MON'; IF语句使⽤不 ELSIF iv_weekly_day =ELSEIF iv_weekly_day = 同 'TUE' THEN'TUE' THEN ii_weekly_day := 'TUE'; set ii_weekly_day =END IF;'TUE'; END IF; 2 3 loopLable:LOOP IF i > (ii_role_cnt-1) THEN LEAVE looplable; FOR li_cnt IN 0.. ELSE (ii_role_cnt-1) LOOP SELECT COUNT(*) INTO SELECT COUNT(*) INTO li_role_ik_cnt FROM li_role_ik_cnt FROM SD_ROLE SD_ROLE WHERE ROLE_CD = WHERE ROLE_CD =1. oracle使⽤For语句实现循环. 'ADMIN_SUPER'; FOR语句不同lo_aas_role_upl(li_cnt); Mysql使⽤Loop语句实现循环. /*lo_aas_role_upl(li_cnt);*/ IF li_role_ik_cnt = 0 2. oracle 使⽤For…loop关键字. IF li_role_ik_cnt = 0 THEN Mysql使⽤loopLable:LOOP实现循环. THEN RETURN 'N'; RETURN 'N'; END IF; END IF; li_role_ik_cnt := -3; SET li_role_ik_cnt = -3; END LOOP; SET i = i+1; END IF; END LOOP loopLable; WHILE lv_inputstr IS NOTWHILE lv_inputstr IS NOT1. oracle 中使⽤while语句关键字为: while 表达式 loop… while语句不NULL LOOPNULL DOend loop;同...... mysql 中使⽤while语句关键字为: while 表达式 do… end END LOOP;END WHILE;while;存储过程&Function 编 类别号 ORACLEMYSQL注释 1.在创建存储过程时如果存在同名的存储过程,会删除⽼的存储过程. oracle使⽤create or replace. mysql使⽤先删除⽼的存储过程,然后再创建新的存储过程. 2. oracle 存储过程可以定义在package中,也可以定义在Procedures中. 如果定义在包中,⼀个包中可以包含多个存储过程和⽅法.如果定义在Procedures中,存储过程中不可以定义多个存储过程. Mysql 存储过程中不可以定义多个存储过程. 3. oracle中字符串类型可以使⽤varchar2. Mysql 需要使⽤varchar 4. Oracle中参数varchar长度不是必须的, create or replace procedureDROP PROCEDURE IF EXISTS 创建存储过程语句不P_ADD_FAC(`SD_USER_P_ADD_USR`;1同 id_fac_cd INcreate procedure P_ADD_FAC( ES_FAC_UNIT.FAC_CD%TYPE) is id_fac_cd varchar(100)) 2创建函数语句不同 Mysql中参数varchar长度是必须的, ⽐如varchar(100) 1.在创建函数时如果存在同名的函数,会删除⽼的函数. oracle使⽤create or replace. mysql使⽤先删除⽼的函数,然后再创建新 CREATE ORDROP FUNCTION IF EXISTS的函数.REPLACEFUNCTION`SD_ROLE_F_ROLE_FACS_GRP`;2. oracle 函数可以定义在package中,也可F_ROLE_FACS_GRP(CREATE FUNCTION以定义在Functions中. 如果定义在包中,⼀ ii_role_int_key IN`SD_ROLE_F_ROLE_FACS_GRP`(个包中可以包含多个存储过程和函数.如果SD_ROLE.ROLE_INT_KEY%TYPE ii_role_int_key INTEGER(10)定义在Functions中,每个函数只能定义⼀个 ) RETURN VARCHAR2) RETURNS varchar(1000) 函数. Mysql Functions不可以定义多个函数. 3. oracle返回值⽤return. Mysql返回值⽤returns. procedure P_ADD_FAC( create procedure P_ADD_FAC( id_fac_cd IN (in) id_fac_cd varchar(100)) ES_FAC_UNIT.FAC_CD%TYPE) 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语句。 oracle可以创建包,包中可以包含多个存储过程和⽅法. mysql没有没有包这个概念,可以分别创建存储过程和⽅法. 每个存储过程或⽅法都需要放在⼀个⽂件中. 例1: ⽅法命名 oracle 中SD_FACILITY_PKG.F_SEARCH_FAC to mysql SD_FACILITY_F_SEARCH_FAC 例2: 过程命名oracle 中SD_FACILITY_PKG.P_ADD_FACto mysql SD_FACILITY_P_ADD_FAC oracle存储过程和⽅法都可以使⽤return退出当前过程和⽅法. Mysql存储过程中只能使⽤leave退出当前存储过程.不可以使⽤return. Mysql⽅法可以使⽤return退出当前⽅法. oracle : 内部异常不需要定义,在存储过程或函数末尾写上EXCEPTION后,后⾯的部分即为异常处理的部分. oracle可以定义⾃定义异常,⾃定义异常需要使⽤raise关键字抛出异常后,才可以在EXCEPTION中捕获.mysql: mysql内部异常也需要先定义,在定义的同时也需要实现异常的功能. ⽬前mysql不⽀持⾃定义异常. oracle中: 3传⼊参数写法不同 function func_name( gw_id in(out) varchar2 ) create function func_name( gw_id varchar（100)) 4包的声明⽅式 create or replace package/package 拆分成多个存储过程或函数 body package name 5 存储过程返回语句不 return; ⼀样LEAVE proc; (proc 代表最外层的begin end) 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; 声明变量在begin...end体 内，begin之后其他任何内容之前 EXCEPTION WHEN OTHERS THEN ROLLBACK ; 存储过程异常处理不6 ov_rtn_msg := c_sp_name||'('||⼀样 li_debug_pos ||'):'|| TO_CHAR(SQLCODE)||':'||SUBSTR(SQLERRM,1,100);过程和函数的声明变 声明变量在begin…end体之前 量的位置不同 EXCEPTION WHEN NO_DATA_FOUND 7 THEN oi_rtn_cd := 1; NO_DATA_FOUND ov_rtn_msg :=使⽤FOUND_ROWS()代替8异常处理SD_COMMON.P_GET_MSG('DP-NO_DATA_FOUND. 详见注释. CBM-01100a-016', li_sub_rtn_cd, lv_sub_rtn_msg );9 在存储过程中调⽤存 Procedure_Name(参数); 储过程⽅式的不同 Call Procedure_Name(参数); NO_DATA_FOUND是游标的⼀个属性. 当select没有查到数据就会出现 no datafound 的异常，程序不会向下执⾏.Mysql: 没有NO_DATA_FOUND这个属性.但可是使⽤FOUND_ROWS()⽅法得到select语句查询出来的数据.如果FOUND_ROWS()得到的值为0,就进⼊异常处理逻辑. MYSQL存储过程调⽤存储过程，需要使⽤Call pro_name(参数). Oracle调⽤存储过程直接写存储过程名就可以了. 详见<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.5 Mysql异常处理部分 10抛异常的⽅式不同 触发器编号类别 RAISE Exception_Name;见备注 注释 1. Oracle使⽤create or replace trigger语法创建触发器. create or replace trigger Mysql使⽤ create trigger创建触发器.TG_ES_FAC_UNITcreate trigger2. Oracle可以在⼀个触发器触发 创建触发器语 before insert or update`hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFOREinsert,delete,update事件. 1 句不同or delete onINSERT on `hs_esbs`.`es_fac_unit` Mysql每个触发器只⽀持⼀个事件. 也就 ES_FAC_UNITfor each row 是说,⽬前每个trigger需要拆分成3个mysql for each rowtrigger. 3. mysql trigger 不能在客户端显⽰或编辑.需要在服务器所在的机器上操作. 取得新数1. new和old记录⾏的引⽤： 触发器new和 据: :new.FAC_CD取得新数据: NEW.FAC_CD mysql是NEW.col1，OLD.col1来引⽤。 2old记录⾏的引 取得⽼数取得⽼数据: OLD.FAC_CD oracle是:NEW.col1, :OLD.col1来引⽤。 ⽤不同 据: :old.FAC_CD2. NEW 和OLD不区分⼤⼩写. ⽤户权限 ORACLEMYSQL 编号类别ORACLE 1创建⽤户 注释 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}; Create user user_name说明: CREATE USER user_name identified by user_password 每个⽤户都有⼀个默认表空间和⼀个临时表空间,如果没 IDENTIFIED BY default tablespace starSpace有指定,oracle就将system设置为默认表空间,将temp设为 user_password; temporary tablespace temp;临时表空间. 2.mysql创建⽤户 创建⽤户命令: mysql> CREATE USER yy IDENTIFIED BY '123'; yy表⽰你要建⽴的⽤户名，后⾯的123表⽰密码上⾯建⽴的⽤户可以在任何地⽅登陆。 如果要限制在固定地址登陆，⽐如localhost 登陆： mysql> CREATE USER yy@localhost IDENTIFIED BY'123';1. Oracle SQL>drop user ⽤户名; //⽤户没有建任何实体 SQL> drop user ⽤户名 CASCADE; // 将⽤户及其所建实体全部删除 注: 当前正连接的⽤户不得删除。 2. Mysql ⾃4.1.1以后，删除⼀个MYSQL帐户，可以使⽤drop MYSQL 2删除⽤户 Drop user user_namecascade; Drop user user_name; 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表中删除。1.mysql修改密码第⼀种⽅式： 1) 更改之前root没有密码的情况 c:\\mysql\\bin>mysqladmin -u root password\"your password\" 2) 更改之前root有密码的情况,假如为123456 c:\\mysql\\bin>mysqladmin -u root -p123456password \"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; 重新加载权限表 3修改密码 alter user user_namemysqladmin -u root -pidentified by new_password123456 password \"your password\"; 5 Grant connect to star -- star⾓⾊允许⽤户连接数据库, 并创建数据库对象1. 详见< 设置⽤户权Grant resource to star -- starGRANT ALL ON picture.* TO限设置 4限⾓⾊允许⽤户使⽤数据库中的test IDENTIFIED BY \"test\";2. 详见< 存储空间. 设置Grant dba to star -- DBA权限 1. Oracle Revoke语句的基本格式如下： REVOKE 权限类型 [（字段列表）] [, 权限类型 [（字段列表）]…]ON {数据库名称.表名称}FROM ⽤户名@域名或IP地址 例如，管理员撤销⽤户admin@localhost对数据库xsxk所拥有的创建、创建数据库及表的权限，并撤销该⽤户可以把⾃⼰所拥有的权限授予其他⽤户的权限，可使⽤以下命令。 mysql>revoke create,drop on xsxk.* fromadmin@localhost; mysql>revoke grant option on xsxk.* fromadmin@localhost; revoke语句中的“⽤户名@域名或IP地址”部分必须匹配原来grant语句中的“⽤户名@域名或IP地址”部分，⽽“权限类型”部分可以是所授权的⼀部分权限。⽽且，revoke只 REVOKE privilegesRevoke select, update on能撤销权限，不能删除⽤户账户，在授权表user中仍保(columns) ON what FROM留该⽤户的记录；⽤户仍可以连接到数据库服务器。如回收权限product from user02; user果要完全删除⽤户，则使⽤前⾯提到的delete语句从user 表中删除该⽤户记录。 2. Mysql 要取消⼀个⽤户的权限，使⽤REVOKE语句。REVOKE的语法⾮常类似于GRANT语句，除了TO⽤FROM取代并且没有INDETIFED BY和WITH GRANT OPTION⼦句： REVOKE privileges (columns) ON what FROM user user部分必须匹配原来GRANT语句的你想撤权的⽤户的user部分。privileges部分不需匹配，你可以⽤GRANT语句授权，然后⽤REVOKE语句只撤销部分权限。 REVOKE语句只删除权限，⽽不删除⽤户。即使你撤销了所有权限，在user表中的⽤户记录依然保留，这意味着⽤户仍然可以连接服务器。要完全删除⼀个⽤户，你必须⽤⼀条Delete语句明确从user表中删除⽤户记录 其它编 类别号 ORACLE MYSQL 注释 1 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 ⽅法⼀ 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 sql语句和mysql sql语句有⼀定的区别. 1. oracle左连接,右连接可以使⽤(+)来实现. Mysql只能使⽤left join ,right join等关键字. 2 最后⼀句执⾏的sql statement所SQL%ROWCOUNT取得或 影响的条数 执⾏select语句后⽤: FOUND_ROWS()执⾏update delete insert语句后⽤: ROW_COUNT(). oracle中: sql 表⽰最后⼀句执⾏的 SQL Statement, rowcount表⽰該 SQL 所取得或影响的条数.Mysql中: 执⾏select语句后查询所影响的条数⽤:FOUND_ROWS() 执⾏update delete insert语句后查询所影响的条数⽤: ROW_COUNT() 3查询分页 4java null值 ⽅法:使⽤循环变量替换oracle中ROWNUM SELECT t1.* set @mycnt = 0; FROM SELECT (@mycnt := @mycnt + 1) as (SELECT MSG_INT_KEY, ROW_NUM,t1.* MSG_TY, FROM MSG_CD, (SELECT MSG_INT_KEY, ROWNUM ROW_NUM MSG_TY, MSG_CD, ROWNUM FROM SD_SYS_MSG ROW_NUM WHERE (ii_msg_int_key IS NULL FROM SD_SYS_MSG OR msg_int_key = ii_msg_int_key) WHERE (ii_msg_int_key IS NULL OR ORDER BY MSG_CD msg_int_key = ii_msg_int_key ) ) t1 ORDER BY MSG_CD WHERE (in_page_no IS NULL) ) t1 OR (t1.ROW_NUM > WHERE (in_page_no IS NULL) ((in_page_no -1)*li_per_page_amt) OR (t1.ROW_NUM> AND t1.ROW_NUM < ((in_page_no - 1) * li_per_page_amt) (in_page_no*li_per_page_amt + 1) AND t1.ROW_NUM < ); (in_page_no * li_per_page_amt + 1) ); 现在java代码需要修改: \"\"作为参数传⼊后,在oracle中将识别 \"\"作为参数据传mysql还是\"\"inPara.add(MSG_TY.equals(\"\") ? null : 为null MSG_TY); 1. oracle可以将动态sql放在游标中执 set @a = iv_cd_field_name;⾏. set @b = iv_table_name; mysql游标声明有⼀定的局限性: set @c = IFNULL(iv_where_cause,' 1=1mysql游标必须在声明处理程序之前被 lv_sql := 'SELECT ' ||' distinct ' ||');声明，并且变量和条件必须在声明光标iv_cd_field_name || ' FIELD1 '||或处理程序之前被声明。Mysql采⽤ 5执⾏动态sql ' FROM ' || iv_table_name || SET @s = concat('SELECT distinct ', @aPrepared Statements实现动态sql. 例⼦ ' WHERE ' || NVL(iv_where_cause,', ' FIELD1 FROM ' , @b ,如下:1=1 '); ' WHERE ' , IFNULL(@c,' 1=1 '));INT Emp_id_var = 56 PREPARE SQLSA OPEN l_sys_cur FOR lv_sql; PREPARE stmt3 FROM @s;FROM \"DELETE FROM employee EXECUTE stmt3;WHERE emp_id=?\" ; EXECUTE SQLSA USING DEALLOCATE PREPARE stmt3;:Emp_id_var ; 6 使⽤数组步骤: 1. 将传⼊的字符串通 过P_UNPACK_LIST⽅法转换为数组.(lo_holiday_jan_upl即为数组) P_UNPACK_LIST(iv_jan__str, mysql中数⽤数组步骤: lv_delimiter, lo_holiday_jan_upl); 1. 将需要处理的字符串交给执⾏业务逻辑 2. 传数组到另⼀个存储过程. 的存储过程处理. P_MOD_MONTH(iv_year, 1, CALL lo_holiday_jan_upl, iv_user_cd); SD_HOLIDAY_P_MOD_MONTH(iv_year, 3. P_MOD_MONTH中使⽤数组: (将数 1, iv_jan__str, iv_user_cd); 组中的各个元素取出来插⼊ 2. SD_HOLIDAY_P_MOD_MONTH中处 到SD_HOLIDAY表) 理字符串. (将字符串按⾃定格式分隔出 存储过程相互调⽤时传递数组解决⽅FOR li_cnt IN 0 .. 9 LOOP 来,在对每个⼩字符串进⾏业务逻辑处理.) 法: IF iv_daystr(li_cnt) IS NOT NULL SET lv_inputstr = iv_inputstr; oracle中传⼊12个字符串到存储过程,THEN loopLable:LOOP 然后将这12个字符串转换为12个数组, INSERT INTO SD_HOLIDAY IF li_cnt > 9 THEN 再调⽤其他存储过程并将这12个数组分 (HOLIDAY_INT_KEY, LEAVE looplable; 别传给存储过程,便利每个数组进⾏业务 YEAR, ELSE 存储过程相互 MONTH,逻辑处理. SET li_pos = INSTR(lv_inputstr, 调⽤时传递数 DAY, mysql解决⽅法: 将存储过程中的数组 iv_delimiter); 组去掉,两个存储过程调⽤时直接传递字符 ENABLE_FLAG, IF li_pos = 0 THEN 串,然后再需要处理业务逻辑的地⽅将字 CREATE_BY, leave looplable; 符串分解,进⾏业务逻辑处理. CREATE_DATE, ELSE LAST_UPD_BY, set temp_str = SUBSTR(lv_inputstr, 可以参考<<2009002-OTMPPS- LAST_UPD_DATE) 1, li_pos - 1); Difficult Questions-0001.doc>> 中 VALUES /*插⼊temp_str到SD_HOLIDAY表*/ 2.4.2 逐层分解字符串 INSERT INTO SD_HOLIDAY(...) (SEQ_HOLIDAY_INT_KEY.NEXTVAL, SET lv_inputstr = iv_year, SUBSTRING(lv_inputstr, li_pos + iv_month, LENGTH(iv_delimiter)); iv_daystr(li_cnt), END IF; 1, SET li_cnt = li_cnt+1; iv_user_cd, END IF; ld_sys_date, END LOOP loopLable; iv_user_cd, ld_sys_date); END IF; END LOOP; select fac_unit_key FILED1在mysql中要 Java⽆法以 select fac_unit_key FILED1在oracle可改 String来接取CAST(intvalue A 以select CAST(fac_unit_key AS CHAR) int FILED1 7 因篇幅问题不能全部显示，请点此查看更多更全内容