--查看数据库的schema
select * from sysibm.sysschemata
--查看所有的数据库的procedure
SELECT * FROM syscat.procedures
--创建存储过程
CREATE PROCEDURE dept_median(IN deptNumber SMALLINT, OUT medianSalary double)
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INTEGER DEFAULT 1;
DECLARE v_counter INTEGER DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS double)
FROM STAFF
WHERE DEPT = deptNumber
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT count(*) INTO v_numRecords
FROM STAFF
WHERE DEPT = deptNumber;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1 ) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter +1;
END WHILE;
CLOSE c1;
END
--是正确的语句,执行报错,改成命令行,能够正确执行
CALL dept_median(20,?)
DB2-小案例
最新推荐文章于 2022-10-29 12:52:11 发布