更新日期:2024年09月11日
查询操作需要游标FETCH到一个记录变量里,而记录变量必须使用%ROWTYPE,且
投影列存在虚拟列(即表达式),则该该列必须有别名。且别名必须加AS, 否则报错。在
ORACLE中可以加AS,也可以不加。
在程序中引用定义的虚拟列
CREATE OR REPLACE PROCEDURE P_6_10 AS
CURSOR c1 IS SELECT employee_id,(salary * .05) as raise FROM employees
WHERE job_id LIKE '%_PRES' ORDER BY employee_id;
emp_rec c1%ROWTYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO emp_rec;
EXIT WHEN c1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('Raise for employee #' || emp_rec.employee_id ||' is $' || emp_rec.raise);
END LOOP;
CLOSE c1;
END;
--Result:
--Raise for employee #114 is $550
--Raise for employee #120 is $400
--Raise for employee #121 is $410
--Raise for employee #122 is $395
--Raise for employee #123 is $325
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 102 -
--Raise for employee #124 is $368.445
--Raise for employee #145 is $700
--Raise for employee #146 is $675
--Raise for employee #147 is $600
--Raise for employee #148 is $550
--Raise for employee #149 is $525
--Raise for employee #201 is $650