SQL笔记-存储过程+循环

秒速五厘米 2023-10-12 11:47 44阅读 0赞

### 存储过程循环使用方法 ###

#### 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