您的PL / SQL语法不正确
create or replace procedure proc_grosssalary (out gross_salary number) AS begin select (Basic+HRA+DA+CA+Medical) into gross_salary from salary s; end;
但是..你想做什么?如果你想为每个员工计算工资,并且你的信息在同一行,你可以使用简单的SQL,如下所示:
Select id_emp, name_emp, surname_emp, (Basic+HRAs+DA+CA+Medical) as salary from salary;
它会比奇怪的函数和程序更快。如果你不喜欢它,创建一个这样的视图,你将有一个计算工资的列,更干净,更少模糊
您还应该考虑创建视图的选项。
Create or replace view v_grosssalary as select s.*,(Basic+HRA+DA+CA+Medical) Gross_Salary from salary s;
简单地做 select * from v_grosssalary 得到输出。
select * from v_grosssalary
通过程序,还有一个 PRINT 使用命令(在SQL * Plus和SQL开发人员中工作) CURSOR 绑定变量
PRINT
CURSOR
VARIABLE x REFCURSOR create or replace procedure proc_grosssalary AS begin OPEN cursor :x for select s.*,(Basic+HRA+DA+CA+Medical) Gross_Salary from salary s; end; / Print x -- This will display the required result.
任务完成:
create or replace procedure p1 IS cursor c1 is select s.*,(Basic+HRA+CA+DA+Medical) Gross_Salary from salary s; emp_rec c1%rowtype; begin open c1; dbms_output.put_line('Emp_No Basic HRA Gross_Salary'); dbms_output.put_line('---------------------------------------'); loop fetch c1 into emp_rec; exit when c1%notfound; dbms_output.put_line(emp_rec.employee_number||' '||emp_rec.Basic||' '||emp_rec.HRA||' '||' '||emp_rec.Gross_Salary); end loop; close c1; end;
- 使用PLSQL块执行过程 -
begin p1; end;
以下是最终结果:
---------------------------------------------- Employee_number Basic HRA Gross_salary ---------------------------------------------- 1 25000 10000 36750 2 7000 2800 11650 3 10000 4000 15950