用pgSQL改写这段代码,使用游标获取指定 empno 的员工的每条工资记录中的工资数额。使 用 LOOP 语句编写循环。DELIMITER // CREATE PROCEDURE calc_avg_and_var_salary_for_emp_no(IN emp_no_in INT, OUT AVG REAL, OUT var REAL) BEGIN DECLARE done INT DEFAULT 1; DECLARE sal REAL; DECLARE num INT; DECLARE cur1 CURSOR FOR SELECT salary FROM salaries WHERE emp_no = emp_no_in; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0; SET var = 0, AVG = 0, num = 0; OPEN cur1; FETCH cur1 INTO sal; WHILE done = 1 DO SET AVG = AVG + sal; SET var = var + sal*sal; SET num = num + 1; FETCH cur1 INTO sal; END WHILE; CLOSE cur1; SET AVG = AVG/num; SET var = var/num - AVG*AVG; END// DELIMITER ;
时间: 2023-06-20 18:10:01 浏览: 102
当时自己初学SQL时,对游标很难理解,现在编写了一个实例游标循环,到时写SQL循环时方便些
```postgresql
CREATE OR REPLACE FUNCTION calc_avg_and_var_salary_for_emp_no(IN emp_no_in INT, OUT AVG REAL, OUT var REAL)
AS $$
DECLARE
done INT DEFAULT 1;
sal REAL;
num INT;
cur1 CURSOR FOR SELECT salary FROM salaries WHERE emp_no = emp_no_in;
BEGIN
SET var = 0;
SET AVG = 0;
SET num = 0;
OPEN cur1;
FETCH cur1 INTO sal;
WHILE done = 1 LOOP
SET AVG = AVG + sal;
SET var = var + sal*sal;
SET num = num + 1;
FETCH cur1 INTO sal;
IF NOT FOUND THEN
SET done = 0;
END IF;
END LOOP;
CLOSE cur1;
SET AVG = AVG/num;
SET var = var/num - AVG*AVG;
END
$$ LANGUAGE plpgsql;
```
使用游标获取指定 empno 的员工的每条工资记录中的工资数额,使用了 `CURSOR`;
使用 `LOOP` 语句编写循环;
增加了 `IF NOT FOUND THEN` 来判断是否到达游标末尾。
阅读全文