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的几点注意事项，希望对大家有所帮助。