MariaDB 设置 sql_mode=Oracle 和 Oracle 对比验证

功能		Oracle语法	MariaDB语法	Oracle执行结果	MariaDB执行结果
创建存储过程	未使用参数和变量	CREATE PROCEDURE p1 AS BEGIN NULL; END p1; /	DELIMITER // CREATE PROCEDURE p1() IS BEGIN NULL; END // DELIMITER ;
	带有参数和变量	CREATE PROCEDURE p1(p_input IN NUMBER, p_output OUT NUMBER) AS v_variable VARCHAR2(100); BEGIN v_variable := 'Hello, World!'; p_output := p_input * 2; END; /	DELIMITER // CREATE PROCEDURE p1(INOUT p_input INT) BEGIN DECLARE v_variable VARCHAR(100); SET v_variable = 'Hello, World!'; SET p_input = p_input * 2; END // DELIMITER ;
创建存储函数		CREATE FUNCTION f1(a VARCHAR2) RETURN VARCHAR2 IS v_result VARCHAR2(100); BEGIN v_result := a || ' appended text'; RETURN v_result; END; /	DELIMITER // CREATE FUNCTION f1(a VARCHAR(100)) RETURN VARCHAR(100) IS v_result VARCHAR(100); BEGIN SET v_result = CONCAT(a, ' appended text'); RETURN v_result; END // DELIMITER ;
游标	带有FOR 循环显式游标	CREATE TABLE t1 ( a NUMBER, b NUMBER ); INSERT INTO t1 (a, b) VALUES (1, 2); INSERT INTO t1 (a, b) VALUES (3, 4); INSERT INTO t1 (a, b) VALUES (5, 6); CREATE OR REPLACE PROCEDURE p1 AS a_val t1.a%TYPE; b_val t1.b%TYPE; CURSOR cur IS SELECT a, b FROM t1; BEGIN FOR rec IN cur LOOP a_val := rec.a; b_val := rec.b; DBMS_OUTPUT.PUT_LINE('a: ' || a_val || ', b: ' || b_val); END LOOP; END; / CREATE OR REPLACE PROCEDURE main_procedure AS BEGIN p1; END; /	-- 创建表 t1 CREATE TABLE t1 ( a INT, b INT ); -- 向表 t1 插入数据 INSERT INTO t1 (a, b) VALUES (1, 2); INSERT INTO t1 (a, b) VALUES (3, 4); INSERT INTO t1 (a, b) VALUES (5, 6); -- 创建存储过程 p1 DELIMITER // CREATE PROCEDURE p1() BEGIN DECLARE a_val INT; DECLARE b_val INT; DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT a, b FROM t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO a_val, b_val; IF done THEN LEAVE read_loop; END IF; SELECT CONCAT('a: ', a_val, ', b: ', b_val) AS result; END LOOP; CLOSE cur; END // DELIMITER ; -- 调用存储过程 p1 CALL p1();
	带有 FOR 循环的隐式游标	CREATE OR REPLACE PROCEDURE p1 IS BEGIN FOR rec IN (SELECT a, b FROM t1) LOOP DBMS_OUTPUT.PUT_LINE('A = ' || rec.a || ', B = ' || rec.b); END LOOP; END p1; /	DELIMITER // CREATE PROCEDURE p1() BEGIN DECLARE a_value INT; DECLARE b_value INT; DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT a, b FROM t1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur; read_loop: LOOP FETCH cur INTO a_value, b_value; IF done THEN LEAVE read_loop; END IF; SELECT CONCAT('A = ', a_value, ', B = ', b_value) AS output; END LOOP; CLOSE cur; END // DELIMITER ;
	带有参数和 FOR 循环的游标	CREATE OR REPLACE PROCEDURE process_cursor(prm_a IN INT, prm_b IN INT) IS CURSOR c IS SELECT a, b FROM t1 WHERE a = prm_a AND b = prm_b; BEGIN FOR rec IN c LOOP DBMS_OUTPUT.PUT_LINE('A: ' || rec.a || ', B: ' || rec.b); END LOOP; END process_cursor; / set serveroutput on BEGIN process_cursor(1, 2); END; /	DELIMITER // CREATE PROCEDURE process_cursor(IN prm_a INT, IN prm_b INT) BEGIN DECLARE a_val INT; DECLARE b_val INT; DECLARE cur CURSOR FOR SELECT a, b FROM t1 WHERE a = prm_a AND b = prm_b; DECLARE CONTINUE HANDLER FOR NOT FOUND SET @finished = TRUE; OPEN cur; loop_label: LOOP FETCH cur INTO a_val, b_val; IF @finished THEN LEAVE loop_label; END IF; SELECT CONCAT('A: ', a_val, ', B: ', b_val); END LOOP; CLOSE cur; END // DELIMITER ; CALL process_cursor(1, 2);
	s %ISOPEN， %ROWCOUNT， %FOUND， %NOTFOUND方式显式游标属性	SET SERVEROUTPUT ON; BEGIN DECLARE CURSOR c IS SELECT a, b FROM t1; v_a t1.a%TYPE; v_b t1.b%TYPE; BEGIN OPEN c; IF c%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Cursor is open.'); ELSE DBMS_OUTPUT.PUT_LINE('Cursor is not open.'); END IF; LOOP FETCH c INTO v_a, v_b; EXIT WHEN c%NOTFOUND; DBMS_OUTPUT.PUT_LINE('A: ' || v_a || ', B: ' || v_b); END LOOP; IF c%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('No rows found.'); END IF; IF c%ROWCOUNT > 0 THEN DBMS_OUTPUT.PUT_LINE('Total rows: ' || c%ROWCOUNT); END IF; CLOSE c; IF c%ISOPEN THEN DBMS_OUTPUT.PUT_LINE('Cursor is open.'); ELSE DBMS_OUTPUT.PUT_LINE('Cursor is not open.'); END IF; END; END; /	DELIMITER // CREATE PROCEDURE process_cursor(prm_a INT, prm_b INT) BEGIN DECLARE v_a INT; DECLARE v_b INT; DECLARE done INT DEFAULT 0; DECLARE c CURSOR FOR SELECT a, b FROM t1 WHERE a = prm_a AND b = prm_b; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN c; IF (FOUND_ROWS() > 0) THEN SET @output = CONCAT('Total rows: ', FOUND_ROWS()); SELECT @output; ELSE SET @output = 'No rows found.'; SELECT @output; END IF; loop_label: LOOP FETCH c INTO v_a, v_b; IF done = 1 THEN LEAVE loop_label; END IF; SET @output = CONCAT('A: ', v_a, ', B: ', v_b); SELECT @output; END LOOP; CLOSE c; END // DELIMITER ; CALL process_cursor(1, 2);