本人写了个简单的函数程序。如下:create or replace FUNCTION test(
field_type IN varchar
) RETURN NUMBER
AUTHID CURRENT_USER AS
avg_plus NUMBER;
avg_negative NUMBER;
return_avg_date NUMBER;
field varchar2(30);
table_name varchar2(50);
BEGIN
field := 'aa';
table_name := 'qqq';
SELECT avg(field) INTO avg_plus FROM table_name where field > 0;
SELECT avg(case when field < 0 then abs(field) end) INTO avg_negative FROM table_name; if avg_plus > avg_negative then
return_avg_date := avg_plus;
else
return_avg_date := -1*avg_negative;
end if;
RETURN return_avg_date;
END test;
/
本函数编译时会报
15/2 PL/SQL: SQL Statement ignored
15/39 PL/SQL: ORA-00942: table or view does not exist 没有该表存在,但实际该表是存在的。然后把sql语句中的table_name 替换成表名qqq 编辑就过去了,但是运行(select test('1') from dual;)时又会报错。当把sql语句中的字段名用"aa"表示时才能正常工作。
这是为什么?不能用变量去替代字段or表名么? 请教高手如何能让变量替代表名,要不那么多表,就疯了~
field_type IN varchar
) RETURN NUMBER
AUTHID CURRENT_USER AS
avg_plus NUMBER;
avg_negative NUMBER;
return_avg_date NUMBER;
field varchar2(30);
table_name varchar2(50);
BEGIN
field := 'aa';
table_name := 'qqq';
SELECT avg(field) INTO avg_plus FROM table_name where field > 0;
SELECT avg(case when field < 0 then abs(field) end) INTO avg_negative FROM table_name; if avg_plus > avg_negative then
return_avg_date := avg_plus;
else
return_avg_date := -1*avg_negative;
end if;
RETURN return_avg_date;
END test;
/
本函数编译时会报
15/2 PL/SQL: SQL Statement ignored
15/39 PL/SQL: ORA-00942: table or view does not exist 没有该表存在,但实际该表是存在的。然后把sql语句中的table_name 替换成表名qqq 编辑就过去了,但是运行(select test('1') from dual;)时又会报错。当把sql语句中的字段名用"aa"表示时才能正常工作。
这是为什么?不能用变量去替代字段or表名么? 请教高手如何能让变量替代表名,要不那么多表,就疯了~
解决方案 »
- 请给我SQL语句
- 判断一个number 类型字段是不是 null
- 数据类型转换问题 下午就要交了 谢谢 高手速赐教
- 各位高手,在Oracle里边如何存储java里边已经序列了的对象?
- 请大家帮我看个关于oracle的题目!
- Can't connect to X11 window server using '127.0.0.1:0.0' as the value of the DISPLAY variable.
- 又是游标问题,MD
- 送分题:请说出五个oracle后台进程
- 求教各位大神怎么给查询出来的字段值命名
- 请教:关于rac scan ip和dns中域名配置问题
- 请教:存储过程返回结果集
- 9i的isqlplusdba权限问题
--------当字段名和表作为动态的时候,必须使用动态的sql执行
create or replace FUNCTION test(field_type IN varchar) RETURN NUMBER AUTHID CURRENT_USER AS
avg_plus NUMBER;
avg_negative NUMBER;
return_avg_date NUMBER;
field varchar2(30);
table_name varchar2(50);
BEGIN
field := 'aa';
table_name := 'qqq';
execute immediate 'SELECT avg(field) FROM :table_name where :field > 0'
into avg_plus
using table_name, field;
execute immediate 'SELECT avg(:field) FROM :table_name where :field > 0'
into avg_plus
using field, tablename, field;
SELECT avg(case
when field < 0 then
abs(field)
end)
INTO avg_negative
FROM table_name; if avg_plus > avg_negative then
return_avg_date := avg_plus;
else
return_avg_date := -1 * avg_negative;
end if;
RETURN return_avg_date;
END test;
/
field_type IN varchar2
) RETURN NUMBER
AUTHID CURRENT_USER AS
avg_plus NUMBER;
avg_negative NUMBER;
return_avg_date NUMBER;
field varchar2(30);
table_name varchar2(50);
BEGIN
field := 'aa';
table_name := 'qqq';
execute immediate 'SELECT avg('||field||') FROM '||table_name||' where '||field||' > 0' INTO avg_plus;
execute immediate 'SELECT avg(case when '||field||' < 0 then abs('||field||') end) FROM '||table_name INTO avg_negative; if avg_plus > avg_negative then
return_avg_date := avg_plus;
else
return_avg_date := -1*avg_negative;
end if;
RETURN return_avg_date;
END test;