其它
| 编号 | 类别 | 注释 | |
| 内连接的更改 |
1、select a.*, b.*, c.*, d.*from a, b, c, dwhere a.id = b.iDand a.name is not nulland a.id = c.id(+)and a.id = d.id(+)"(+)"所在位置的另一侧为连接的方向，所以上面的例子1是左连接。以下的例子2既是右连接。 2、select a.*, b.*, c.*, d.*from a, b, c, dwhere a.id = b.idand a.name is not nulland a.id(+) = c.id |
方法一select a.*, c.*, d.*from aleft join(c, d)on (a.id = c.id and a.id = d.id), bwhere a.id = b.idand a.name is not null方法二select a.*, c.*, d.*from aleft join c on a.id = c.idleft join d on a.id = d.id, bwhere a.id = b.idand a.name is not null | oracle sql语句和mysql sql语句有一定的区别.1. oracle左连接,右连接可以使用(+)来实现.Mysql只能使用left join ,right join等关键字. |
| 最后一句执行的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() |
| 查询分页 | SELECT t1.*FROM(SELECT MSG_INT_KEY,MSG_TY,MSG_CD,ROWNUM ROW_NUMFROM SD_SYS_MSGWHERE (ii_msg_int_key IS NULLOR msg_int_key = ii_msg_int_key)ORDER BY MSG_CD) t1WHERE (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中ROWNUMset @mycnt = 0;SELECT (@mycnt := @mycnt + 1) as ROW_NUM,t1.*FROM(SELECT MSG_INT_KEY, MSG_TY, MSG_CD, ROWNUM ROW_NUMFROM SD_SYS_MSGWHERE (ii_msg_int_key IS NULL OR msg_int_key = ii_msg_int_key )ORDER BY MSG_CD) t1WHERE (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)); | |
| java null值 | ""作为参数传入后,在oracle中将识别为null | ""作为参数据传mysql还是"" | 现在java代码需要修改:inPara.add(MSG_TY.equals("") ? null : MSG_TY); |
| 执行动态sql | 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; | 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; | 1. oracle可以将动态sql放在游标中执行.mysql游标声明有一定的局限性: mysql游标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。Mysql采用Prepared Statements实现动态sql. 例子如下:INT Emp_id_var = 56PREPARE SQLSAFROM "DELETE FROM employee WHERE emp_id=?" ;EXECUTE SQLSA USING :Emp_id_var ; |
| 存储过程相互调用时传递数组 |
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 LOOPIF iv_daystr(li_cnt) IS NOT NULL THENINSERT 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; |
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:LOOPIF li_cnt > 9 THENLEAVE looplable;ELSESET li_pos = INSTR(lv_inputstr, iv_delimiter);IF li_pos = 0 THENleave looplable;ELSEset 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中传入12个字符串到存储过程,然后将这12个字符串转换为12个数组,再调用其他存储过程并将这12个数组分别传给存储过程,便利每个数组进行业务逻辑处理.mysql解决方法: 将存储过程中的数组去掉,两个存储过程调用时直接传递字符串,然后再需要处理业务逻辑的地方将字符串分解,进行业务逻辑处理.可以参考<<2009002-OTMPPS-Difficult Questions-0001.doc>> 中 2.4.2 逐层分解字符串 |
| Java无法以String来接取int | select fac_unit_key FILED1在oracle可以 | select fac_unit_key FILED1在mysql中要改select CAST(fac_unit_key AS CHAR) FILED1 | CAST(intvalue AS CHAR) |
发表评论