Oracle与MySQL的SQL语句区别
发布时间：2021-03-18 08:31:19 所属栏目：站长百科 来源：网络整理
导读：1数据库 /* ? mysql可以创建数据库，而oracle没有这个操作，oracle只能创建实例； ? sql数据库操作：database ? 格式: ? ? * create database 数据库名; ? ? * create database 数据库名 character set 字符集; */ CREATE DATABASE j0815_1;? CREATE DATABAS
|
2、 select a.*,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.*,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 查询分页 SELECT t1.* FROM? (SELECT MSG_INT_KEY,? ???? MSG_TY,? ???? MSG_CD,? ???? ROWNUM ROW_NUM ? FROM SD_SYS_MSG ? WHERE (ii_msg_int_key IS NULL? ??????? OR msg_int_key = ii_msg_int_key) ? ORDER BY MSG_CD ) t1 WHERE (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中ROWNUM set @mycnt = 0; ???? SELECT (@mycnt := @mycnt + 1) as ROW_NUM,t1.* ???? FROM ???? (SELECT? MSG_INT_KEY,???? MSG_TY,???? MSG_CD,??????? ROWNUM ROW_NUM ?????? FROM SD_SYS_MSG ??? WHERE (ii_msg_int_key IS NULL OR msg_int_key? = ii_msg_int_key ) ?????? ORDER BY MSG_CD ???? ) t1 ?????? WHERE (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) ???????????????????? ); ? 4 java null值 ""作为参数传入后,在oracle中将识别为null ""作为参数据传mysql还是"" 现在java代码需要修改: inPara.add(MSG_TY.equals("") ? null : MSG_TY); 5 执行动态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 = 56 PREPARE SQLSA FROM "DELETE FROM employee WHERE emp_id=?" ; EXECUTE SQLSA USING :Emp_id_var ; 6 存储过程相互调用时传递数组 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
