SQL笔记-存储过程+循环

存储过程循环使用方法

Oracle

Oracle中存储过程的循环使用方法如下：

DECLAREi NUMBER;
BEGINi := 1;WHILE i <= 10 LOOPDBMS_OUTPUT.PUT_LINE('i=' || i);i := i + 1;END LOOP;
END;

其中，DECLARE用于声明变量，BEGIN和END用于标识存储过程的开始和结束，LOOP和END LOOP用于标识循环体的开始和结束。

MySQL

MySQL中存储过程的循环使用方法如下：

BEGINDECLARE i INT DEFAULT 1;WHILE i <= 10 DOSELECT CONCAT('i=', i);SET i = i + 1;END WHILE;
END;

其中，DECLARE用于声明变量，BEGIN和END用于标识存储过程的开始和结束，DO和END WHILE用于标识循环体的开始和结束。

SQL Server

SQL Server中存储过程的循环使用方法如下：

DECLARE @i INT = 1;
WHILE @i <= 10
BEGINPRINT 'i=' + CONVERT(VARCHAR(10), @i);SET @i = @i + 1;
END;

其中，DECLARE用于声明变量，PRINT用于输出信息，WHILE和BEGIN以及END用于标识循环体的开始和结束。

PostgreSQL

PostgreSQL中存储过程的循环使用方法如下：

DECLARE i INT := 1;
BEGINWHILE i <= 10 LOOPRAISE NOTICE 'i=%', i;i := i + 1;END LOOP;
END;

其中，DECLARE用于声明变量，BEGIN和END用于标识存储过程的开始和结束，LOOP和END LOOP用于标识循环体的开始和结束，RAISE NOTICE用于输出信息。

多表关联分组排序查询的例子

假设有两个表，一个是学生表（students），另一个是成绩表（scores）。学生表中存储了学生的基本信息，包括学生ID（id），姓名（name），性别（gender）等；成绩表中存储了学生的各科成绩，包括学生ID（student_id），科目（subject），成绩（score）等。

建表语句和数据插入语句

-- 学生表
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(20),gender VARCHAR(2)
);INSERT INTO students VALUES (1, '张三', '男');
INSERT INTO students VALUES (2, '李四', '女');
INSERT INTO students VALUES (3, '王五', '男');-- 成绩表
CREATE TABLE scores (id INT PRIMARY KEY,student_id INT,subject VARCHAR(20),score INT
);INSERT INTO scores VALUES (1, 1, '语文', 80);
INSERT INTO scores VALUES (2, 1, '数学', 90);
INSERT INTO scores VALUES (3, 1, '英语', 85);
INSERT INTO scores VALUES (4, 2, '语文', 75);
INSERT INTO scores VALUES (5, 2, '数学', 95);
INSERT INTO scores VALUES (6, 2, '英语', 80);
INSERT INTO scores VALUES (7, 3, '语文', 85);
INSERT INTO scores VALUES (8, 3, '数学', 70);
INSERT INTO scores VALUES (9, 3, '英语', 90);

Oracle存储过程

CREATE OR REPLACE PROCEDURE get_student_scores
ISCURSOR c_students IS SELECT id, name FROM students;v_student_id students.id%TYPE;v_student_name students.name%TYPE;v_subject scores.subject%TYPE;v_score scores.score%TYPE;
BEGINFOR r_student IN c_students LOOPv_student_id := r_student.id;v_student_name := r_student.name;DBMS_OUTPUT.PUT_LINE(v_student_name || '的成绩：');FOR r_score IN (SELECT subject, score FROM scores WHERE student_id = v_student_id) LOOPv_subject := r_score.subject;v_score := r_score.score;DBMS_OUTPUT.PUT_LINE(v_subject || '：' || v_score);END LOOP;END LOOP;
END;

上述存储过程实现了一个嵌套循环，外层循环遍历学生表中的每一条记录，内层循环根据学生ID查询成绩表中的相关成绩，并输出结果。

MySQL存储过程

DELIMITER //
CREATE PROCEDURE get_student_scores()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE v_student_id INT;DECLARE v_student_name VARCHAR(20);DECLARE v_subject VARCHAR(20);DECLARE v_score INT;DECLARE cur_students CURSOR FOR SELECT id, name FROM students;DECLARE cur_scores CURSOR FOR SELECT subject, score FROM scores WHERE student_id = v_student_id;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur_students;read_students: LOOPFETCH cur_students INTO v_student_id, v_student_name;IF done THENLEAVE read_students;END IF;SELECT CONCAT(v_student_name,'的成绩：');SET done = FALSE;OPEN cur_scores;read_scores: LOOPFETCH cur_scores INTO v_subject, v_score;IF done THENLEAVE read_scores;END IF;SELECT CONCAT(v_subject,'：',v_score);END LOOP;CLOSE cur_scores;END LOOP;CLOSE cur_students;
END //
DELIMITER ;

上述存储过程与Oracle的实现方法类似，只是语法有所不同。

SQL Server存储过程

CREATE PROCEDURE get_student_scores
AS
BEGINDECLARE @student_id INT;DECLARE @student_name VARCHAR(20);DECLARE @subject VARCHAR(20);DECLARE @score INT;DECLARE cur_students CURSOR FOR SELECT id, name FROM students;DECLARE cur_scores CURSOR FOR SELECT subject, score FROM scores WHERE student_id = @student_id;OPEN cur_students;FETCH NEXT FROM cur_students INTO @student_id, @student_name;WHILE @@FETCH_STATUS = 0BEGINPRINT @student_name + '的成绩：';OPEN cur_scores;FETCH NEXT FROM cur_scores INTO @subject, @score;WHILE @@FETCH_STATUS = 0BEGINPRINT @subject + '：' + CONVERT(VARCHAR(10), @score);FETCH NEXT FROM cur_scores INTO @subject, @score;END;CLOSE cur_scores;FETCH NEXT FROM cur_students INTO @student_id, @student_name;END;CLOSE cur_students;
END;

上述存储过程与Oracle、MySQL的实现方法类似，只是语法有所不同。

PostgreSQL存储过程

CREATE OR REPLACE FUNCTION get_student_scores()
RETURNS VOID AS $$
DECLAREv_student_id INT;v_student_name VARCHAR(20);v_subject VARCHAR(20);v_score INT;
BEGINFOR r_student IN SELECT id, name FROM students LOOPv_student_id := r_student.id;v_student_name := r_student.name;RAISE NOTICE '%的成绩：', v_student_name;FOR r_score IN SELECT subject, score FROM scores WHERE student_id = v_student_id LOOPv_subject := r_score.subject;v_score := r_score.score;RAISE NOTICE '%：%s', v_subject, v_score;END LOOP;END LOOP;
END;
$$ LANGUAGE plpgsql;

上述存储过程与Oracle、MySQL、SQL Server的实现方法类似，只是语法有所不同。

结果

无论使用哪种数据库，上述存储过程的执行结果都应该是类似下面这样的：

张三的成绩：
语文：80
数学：90
英语：85
李四的成绩：
语文：75
数学：95
英语：80
王五的成绩：
语文：85
数学：70
英语：90