Oracle到mysql转换的问题总结(4)

begin

set @sqltext=CONCAT( ' insert into MOCHA_FE_DOC_BODY_TEMP ',v_sql); prepare stmt from @sqltext; execute stmt; end ; /

Oracle下函数：

CREATE OR REPLACE FUNCTION GET_PER_NAME_LEVEL( v_UserId VARCHAR2) RETURN VARCHAR2 IS v_UserName VARCHAR2(30); v_PrimaryPosition VARCHAR2(1); v_PersonLevel VARCHAR2(2); v_LevelName VARCHAR2(4000); v_Result VARCHAR2(4000); v_RecCount NUMBER; CURSOR c_PersonPosition IS

SELECT PERSON.USER_NAME, POSITION.PERSON_LEVEL, POSITION.PRIMARY_POSITION, PERSON_LEVEL.LEVEL_NAME

FROM MOCHA_IM_PERSON PERSON, MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON_LEVEL PERSON_LEVEL

WHERE PERSON.USER_ID = POSITION.USER_ID AND POSITION.PERSON_LEVEL = PERSON_LEVEL.PERSON_LEVEL AND POSITION.USER_ID = v_UserId

ORDER BY POSITION.PRIMARY_POSITION DESC; BEGIN

SELECT COUNT(PERSON_LEVEL) INTO v_RecCount FROM MOCHA_IM_PERSON_POSITION POSITION WHERE USER_ID = v_UserId;

IF v_RecCount=0 THEN

SELECT USER_NAME INTO v_Result FROM MOCHA_IM_PERSON PERSON WHERE USER_ID = v_UserId;

ELSE

OPEN c_PersonPosition; LOOP

FETCH c_PersonPosition INTO v_UserName, v_PersonLevel, v_PrimaryPosition, v_LevelName;

EXIT WHEN c_PersonPosition%NOTFOUND;

v_Result := NVL(v_Result, CONCAT(CONCAT(v_Result, v_UserName), '['));

IF (v_PrimaryPosition = '1' AND (v_RecCount >1 OR SUBSTR(v_PersonLevel,2) < '3')) THEN

v_Result := CONCAT(v_Result, v_LevelName);

ELSIF (v_RecCount >0 AND v_PrimaryPosition = '0') THEN v_Result := CONCAT(CONCAT(v_Result, '兼'), v_LevelName); END IF; END LOOP;

CLOSE c_PersonPosition; IF LENGTH(v_Result)>0 THEN v_Result := CONCAT(v_Result, ']'); END IF;

IF SUBSTR(v_Result, LENGTH(v_Result)-1) = '[]' THEN v_Result := SUBSTR(v_Result,1,LENGTH(v_Result)-2);

END IF; END IF;

RETURN v_Result; END;

Mysql下函数：

CREATE FUNCTION GET_PER_NAME_LEVEL(v_UserId VARCHAR(30)) RETURNS VARCHAR(30) BEGIN

DECLARE v_UserName VARCHAR(30); DECLARE v_PrimaryPosition VARCHAR(1); DECLARE v_PersonLevel VARCHAR(2); DECLARE v_LevelName VARCHAR(4000); DECLARE v_Result VARCHAR(4000); DECLARE v_RecCount DECIMAL;

DECLARE c_PersonPosition CURSOR FOR

SELECT PERSON.USER_NAME, POSITION.PERSON_LEVEL, POSITION.PRIMARY_POSITION, PERSON_LEVEL.LEVEL_NAME

FROM MOCHA_IM_PERSON PERSON, MOCHA_IM_PERSON_POSITION POSITION, MOCHA_IM_PERSON_LEVEL PERSON_LEVEL

WHERE PERSON.USER_ID = POSITION.USER_ID AND POSITION.PERSON_LEVEL = PERSON_LEVEL.PERSON_LEVEL AND POSITION.USER_ID = v_UserId

ORDER BY POSITION.PRIMARY_POSITION DESC;

SELECT COUNT(PERSON_LEVEL) INTO v_RecCount FROM MOCHA_IM_PERSON_POSITION POSITION WHERE USER_ID = v_UserId;

IF v_RecCount=0 THEN

SELECT USER_NAME INTO v_Result FROM MOCHA_IM_PERSON PERSON WHERE USER_ID = v_UserId;

ELSE

OPEN c_PersonPosition;

REPEAT

FETCH c_PersonPosition INTO v_UserName, v_PersonLevel, v_PrimaryPosition, v_LevelName;

IF NOT done THEN

set v_Result = IFNULL(v_Result, CONCAT(CONCAT(v_Result, v_UserName), '['));

IF (v_PrimaryPosition = '1' AND (v_RecCount >1 OR SUBSTR(v_PersonLevel,2) < '3')) THEN

set v_Result = CONCAT(v_Result, v_LevelName);

ELSEIF (v_RecCount >0 AND v_PrimaryPosition = '0') THEN set v_Result = CONCAT(CONCAT(v_Result, '兼'), v_LevelName); END IF; END IF;

UNTIL done END REPEAT; CLOSE c_PersonPosition; IF LENGTH(v_Result)>0 THEN set v_Result = CONCAT(v_Result, ']'); END IF;

IF SUBSTR(v_Result, LENGTH(v_Result)-1) = '[]' THEN set v_Result = SUBSTR(v_Result,1,LENGTH(v_Result)-2); END IF;

END IF;

RETURN v_Result; END； / 索引

普通索引，唯一索引，全文索引都支持，但是不支持bitmap索引。 其他：

Oracle用||连接字符串，mysql不支持||，但可以用CONCAT来连接，nvl在mysql中是ifnull，instr函数oracle与mysql有些不同，oracle支持的参数比mysql多，功能更强大，但是mysql有substring_index配合substring，length可以做绝大多数的字符的操作

为了支持中文，字符集选择utf8，为了支持事务和行级锁选择存储引擎为InnoDB 这些可以在mysql的配置文件里去改，也可以在建表的时候写进sql中

最后mysql与oracle的语法（临时表，表的comment，定义变量啊等等）函数上的区别还有很多。

MySQL转换Oracle的七大注意事项

有很多应用项目, 刚起步的时候用MySQL数据库基本上能实现各种功能需求，随着应用用户的增多，数据量的增加，MySQL渐渐地出现不堪重负的情况：连接很慢甚至宕机，于是就有MySQL转换Oracle的需求，应用程序也要相应做一些修改。下面总结出MySQL转换Oracle的几点注意事项，希望对大家有所帮助。

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说教育文库Oracle到mysql转换的问题总结(4)在线全文阅读。