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

DELIMITER //

CREATE PROCEDURE p1()
IS
BEGIN
NULL;
END //

DELIMITER ;

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 ;

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();

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 ;

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);

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);