正在学习oracle阶段,创建了一个简单的函数,登录的用户是system,但创建在isql*plus运行后却提示 警告: 创建的函数带有编译错误。到底是哪里有问题呢?create or replace function get_salary(
dept_no number,
emp_count number)
return number is
v_sum number;
begin
select sum(salary),count(*) into v_sum, emp_count
from employees where employees_id > dept_no;
return v_sum;
exception
when no_data_found then
dbms_output.put_line('the date you need is not exist');
when too_many_rows then
dbms_output.put_line('program error,please use cousor');
when others then
dbms_output.put_line('other errors');
end get_salary;declare
v_num number;
v_sum number;
begin
v_sum:=get_salary(123455,v_num);
dbms_output.put_line('the sum: '||v_sum||' , people: '|| v_num);
end;谢谢
dept_no number,
emp_count number)
return number is
v_sum number;
begin
select sum(salary),count(*) into v_sum, emp_count
from employees where employees_id > dept_no;
return v_sum;
exception
when no_data_found then
dbms_output.put_line('the date you need is not exist');
when too_many_rows then
dbms_output.put_line('program error,please use cousor');
when others then
dbms_output.put_line('other errors');
end get_salary;declare
v_num number;
v_sum number;
begin
v_sum:=get_salary(123455,v_num);
dbms_output.put_line('the sum: '||v_sum||' , people: '|| v_num);
end;谢谢
不过粗粗一看你的function,传进来的参数emp_count不能作为into的赋值对象。
dept_no number,
emp_count number)
return number is
v_sum number;
-- 函数默认的参数是 in 类型,in 类型的参数是不能在函数内部赋值的!
所以你的 select sum(salary),count(*) into v_sum, emp_count
from employees where employees_id > dept_no;是不对的!
dept_no number,
emp_count number
改成
dept_no in number,
emp_count out number
运行还是提示这个错误
create or replace function get_salary(
dept_no number)
return number is
v_sum number;
emp_count NUMBER;
begin
select sum(salary),count(*) into v_sum, emp_count
from employees where employees_id > dept_no;
return v_sum;
exception
when no_data_found then
dbms_output.put_line('the date you need is not exist');
when too_many_rows then
dbms_output.put_line('program error,please use cousor');
when others then
dbms_output.put_line('other errors');
end get_salary;
这个在isql*plus运行还是提示有编译错误,但在 PL/SQL developer却没有错误,不知道怎么回事