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