相关推荐recommended
SQL笔记-存储过程+循环
作者:mmseoamin日期:2023-12-21

存储过程循环使用方法

Oracle

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

DECLARE
  i NUMBER;
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    DBMS_OUTPUT.PUT_LINE('i=' || i);
    i := i + 1;
  END LOOP;
END;

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

MySQL

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

BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= 10 DO
    SELECT 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
BEGIN
  PRINT 'i=' + CONVERT(VARCHAR(10), @i);
  SET @i = @i + 1;
END;

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

PostgreSQL

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

DECLARE i INT := 1;
BEGIN
  WHILE i <= 10 LOOP
    RAISE 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
IS
  CURSOR 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;
BEGIN
  FOR r_student IN c_students LOOP
    v_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) LOOP
      v_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()
BEGIN
  DECLARE 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: LOOP
    FETCH cur_students INTO v_student_id, v_student_name;
    IF done THEN
      LEAVE read_students;
    END IF;
    SELECT CONCAT(v_student_name,'的成绩:');
    SET done = FALSE;
    OPEN cur_scores;
    read_scores: LOOP
      FETCH cur_scores INTO v_subject, v_score;
      IF done THEN
        LEAVE 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
BEGIN
  DECLARE @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 = 0
  BEGIN
    PRINT @student_name + '的成绩:';
    OPEN cur_scores;
    FETCH NEXT FROM cur_scores INTO @subject, @score;
    WHILE @@FETCH_STATUS = 0
    BEGIN
      PRINT @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 $$
DECLARE
  v_student_id INT;
  v_student_name VARCHAR(20);
  v_subject VARCHAR(20);
  v_score INT;
BEGIN
  FOR r_student IN SELECT id, name FROM students LOOP
    v_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 LOOP
      v_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