存储过程&Function
编号 | 类别 | ORACLE | MYSQL | 注释 |
1 | 创建存储过程语句不同 | create or replace procedure P_ADD_FAC( id_fac_cd IN ES_FAC_UNIT.FAC_CD%TYPE) is |
DROP PROCEDURE IF EXISTS `SD_USER_P_ADD_USR`; create procedure P_ADD_FAC( id_fac_cd varchar(100)) |
1.在创建存储过程时如果存在同名的存储过程,会删除老的存储过程. oracle使用create or replace. mysql使用先删除老的存储过程,然后再创建新的存储过程. 2. oracle 存储过程可以定义在package中,也可以定义在Procedures中. 如果定义在包中,一个包中可以包含多个存储过程和方法.如果定义在Procedures中,存储过程中不可以定义多个存储过程. Mysql 存储过程中不可以定义多个存储过程. 3. oracle中字符串类型可以使用varchar2. Mysql 需要使用varchar 4. Oracle中参数varchar长度不是必须的, Mysql中参数varchar长度是必须的, 比如varchar(100) |
2 | 创建函数语句不同 | CREATE OR REPLACEFUNCTION F_ROLE_FACS_GRP( ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE ) RETURN VARCHAR2 |
DROP FUNCTION IF EXISTS `SD_ROLE_F_ROLE_FACS_GRP`; CREATE FUNCTION `SD_ROLE_F_ROLE_FACS_GRP`( ii_role_int_key INTEGER(10) ) RETURNS varchar(1000) |
1.在创建函数时如果存在同名的函数,会删除老的函数. oracle使用create or replace. mysql使用先删除老的函数,然后再创建新的函数. 2. oracle 函数可以定义在package中,也可以定义在Functions中. 如果定义在包中,一个包中可以包含多个存储过程和函数.如果定义在Functions中,每个函数只能定义一个函数. Mysql Functions不可以定义多个函数. 3. oracle返回值用return. Mysql返回值用returns. |
3 | 传入参数写法不同 | procedure P_ADD_FAC( id_fac_cd IN ES_FAC_UNIT.FAC_CD%TYPE) |
create procedure P_ADD_FAC( (in) id_fac_cd varchar(100)) |
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语句。 |
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 | 拆分成多个存储过程或函数 | oracle可以创建包,包中可以包含多个存储过程和方法. mysql没有没有包这个概念,可以分别创建存储过程和方法. 每个存储过程或方法都需要放在一个文件中. 例1: 方法命名 oracle 中SD_FACILITY_PKG.F_SEARCH_FAC to mysql SD_FACILITY_F_SEARCH_FAC 例2: 过程命名 oracle 中SD_FACILITY_PKG.P_ADD_FAC to mysql SD_FACILITY_P_ADD_FAC |
5 | 存储过程返回语句不一样 | return; | LEAVE proc; (proc 代表最外层的begin end) | oracle存储过程和方法都可以使用return退出当前过程和方法. Mysql存储过程中只能使用leave退出当前存储过程.不可以使用return. Mysql方法可以使用return退出当前方法. |
6 | 存储过程异常处理不一样 | EXCEPTION WHEN OTHERS THEN ROLLBACK ; ov_rtn_msg := c_sp_name||'('|| li_debug_pos ||'):'|| TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100); |
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; |
oracle : 内部异常不需要定义,在存储过程或函数末尾写上EXCEPTION后,后面的部分即为异常处理的部分. oracle可以定义自定义异常,自定义异常需要使用raise关键字抛出异常后,才可以在EXCEPTION中捕获. mysql: mysql内部异常也需要先定义,在定义的同时也需要实现异常的功能. 目前mysql不支持自定义异常. |
7 | 过程和函数的声明变量的位置不同 | 声明变量在begin…end体之前 | 声明变量在begin...end体内,begin之后其他任何内容之前 | |
8 | NO_DATA_FOUND异常处理 | EXCEPTION WHEN NO_DATA_FOUND THEN oi_rtn_cd := 1; ov_rtn_msg := SD_COMMON.P_GET_MSG('DP-CBM-01100a-016', li_sub_rtn_cd, lv_sub_rtn_msg ); |
使用FOUND_ROWS()代替NO_DATA_FOUND. 详见注释. | oracle中: NO_DATA_FOUND是游标的一个属性. 当select没有查到数据就会出现 no data found 的异常,程序不会向下执行. Mysql: 没有NO_DATA_FOUND这个属性.但可是使用FOUND_ROWS()方法得到select语句查询出来的数据.如果FOUND_ROWS()得到的值为0,就进入异常处理逻辑. |
9 | 在存储过程中调用存储过程方式的不同 | Procedure_Name(参数); | Call Procedure_Name(参数); | MYSQL存储过程调用存储过程,需要使用Call pro_name(参数). Oracle调用存储过程直接写存储过程名就可以了. |
10 | 抛异常的方式不同 | RAISE Exception_Name; | 见备注 | 详见<<2009002-OTMPPS-Difficult Questions-0001.doc>>中2.5 Mysql异常处理部分 |
触发器
编号 | 类别 | ORACLE | MYSQL | 注释 |
1 | 创建触发器语句不同 | create or replace trigger TG_ES_FAC_UNIT before insert or update or delete on ES_FAC_UNIT for each row |
create trigger `hs_esbs`.`TG_INSERT_ES_FAC_UNIT` BEFORE INSERT on `hs_esbs`.`es_fac_unit` for each row |
1. Oracle使用create or replace trigger语法创建触发器. Mysql使用 create trigger创建触发器. 2. Oracle可以在一个触发器触发insert,delete,update事件. Mysql每个触发器只支持一个事件. 也就是说,目前每个trigger需要拆分成3个mysql trigger. 3. mysql trigger 不能在客户端显示或编辑.需要在服务器所在的机器上操作. |
2 | 触发器new和old记录行的引用不同 | 取得新数据: :new.FAC_CD 取得老数据: :old.FAC_CD |
取得新数据: NEW.FAC_CD 取得老数据: OLD.FAC_CD |
1. new和old记录行的引用: mysql是NEW.col1,OLD.col1来引用。 oracle是:NEW.col1, :OLD.col1来引用。 2. NEW 和OLD不区分大小写. |
用户权限
编号 | 类别 | ORACLE | MYSQL | 注释 |
1 | 创建用户 | Create user user_name identified by user_password default tablespace starSpace temporary tablespace temp; |
CREATE USER user_name IDENTIFIED BY user_password; | 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}; 说明: 每个用户都有一个默认表空间和一个临时表空间,如果没有指定,oracle就将system设置为默认表空间,将temp设为临时表空间. 2.mysql创建用户 创建用户命令: mysql> CREATE USER yy IDENTIFIED BY '123'; yy表示你要建立的用户名,后面的123表示密码 上面建立的用户可以在任何地方登陆。 如果要限制在固定地址登陆,比如localhost 登陆: mysql> CREATE USER yy@localhost IDENTIFIED BY '123'; |
2 | 删除用户 | Drop user user_name cascade; | Drop user user_name; | 1. Oracle SQL>drop user 用户名; //用户没有建任何实体 SQL> drop user 用户名 CASCADE; // 将用户及其所建实体全部删除 注: 当前正连接的用户不得删除。 2. Mysql 自4.1.1以后,删除一个MYSQL帐户,可以使用drop 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表中删除。 |
3 | 修改密码 | alter user user_name identified by new_password | mysqladmin -u root -p 123456 password "your password"; |
1.mysql修改密码 第一种方式: 1) 更改之前root没有密码的情况 c:\mysql\bin>mysqladmin -u root password "your password" 2) 更改之前root有密码的情况,假如为123456 c:\mysql\bin>mysqladmin -u root -p123456 password "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; 重新加载权限表 |
4 | 设置用户权限 | Grant connect to star -- star角色允许用户连接数据库, 并创建数据库对象 Grant resource to star -- star角色允许用户使用数据库中的存储空间. Grant dba to star -- DBA权限 |
GRANT ALL ON picture.* TO test IDENTIFIED BY "test"; | 1. 详见<<oracle vs mysql 用户权限.doc>> 2.1 Oracle 权限设置 2. 详见<<oracle vs mysql 用户权限.doc>> 1.4 用户权限设置 |
5 | 回收权限 | Revoke select, update on product from user02; | REVOKE privileges (columns) ON what FROM user | 1. Oracle Revoke语句的基本格式如下: REVOKE 权限类型 [(字段列表)] [, 权限类型 [(字段列表)]…]ON {数据库名称.表名称}FROM 用户名@域名或IP地址 例如,管理员撤销用户admin@localhost对数据库xsxk所拥有的创建、创建数据库及表的权限,并撤销该用户可以把自己所拥有的权限授予其他用户的权限,可使用以下命令。 mysql>revoke create,drop on xsxk.* from admin@localhost; mysql>revoke grant option on xsxk.* from admin@localhost; revoke语句中的“用户名@域名或IP地址”部分必须匹配原来grant语句中的“用户名@域名或IP地址”部分,而“权限类型”部分可以是所授权的一部分权限。而且,revoke只能撤销权限,不能删除用户账户,在授权表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、 2、 |
方法一 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 | 查询分页 | 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 | 存储过程相互调用时传递数组 |
Oracle使用数组步骤: END LOOP; |
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:LOOP IF li_cnt > 9 THEN LEAVE looplable; ELSE SET li_pos = INSTR(lv_inputstr, iv_delimiter); IF li_pos = 0 THEN leave looplable; ELSE set 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 逐层分解字符串 |
7 | 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) |
- 欢迎访问木庄网络博客
- 可复制:代码框内的文字。
- 方法:Ctrl+C。