ORACLE为什么不能建立function?急...

Question

我的下面的语句为什么不能建立function?真的搞不懂,请教各位大虾/
CREATE OR REPLACE FUNCTION GETWEEK (sdivd integer,sdyear integer) return integer
is
--declare 
--sdivd number;
--sdyear number;
v_week integer;
begin
--sdivd:=108069;
--sdyear:=107;
select (case when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))>0.849
              then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))<0.849
              then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))>0.701
             then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))<0.701
             then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           else
           0 end )  into v_week from testdta.f0008b where cddtpn='A' and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear; 
--dbms_output.put_line(v_week);
return v_week;
end GETWEEK;
但我这样执行又可以,真是搞不懂啊,declare 
sdivd number;
sdyear number;
v_week integer;
begin
sdivd:=108069;
sdyear:=107;
select (case when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))>0.849
              then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))<0.849
              then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))>0.701
             then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))<0.701
             then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           else
           0 end )  into v_week from testdta.f0008b where cddtpn='A' and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear; 
dbms_output.put_line(v_week);
--return v_week;
end GETWEEK;

Answer 1

是这样的可以
我的下面的语句为什么不能建立function?真的搞不懂,请教各位大虾/
CREATE OR REPLACE FUNCTION GETWEEK (sdivd integer,sdyear integer) return integer
is
--declare 
--sdivd number;
--sdyear number;
v_week integer;
begin
--sdivd:=108069;
--sdyear:=107;
select (case when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))>0.849
              then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))<0.849
              then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))>0.701
             then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))<0.701
             then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           else
           0 end )  into v_week from testdta.f0008b where cddtpn='A' and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear; 
--dbms_output.put_line(v_week);
return v_week;
end GETWEEK;
但我这样执行又可以,真是搞不懂啊,declare 
sdivd number;
sdyear number;
v_week integer;
begin
sdivd:=108069;
sdyear:=107;
select (case when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))>0.849
              then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))<0.849
              then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))>0.701
             then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))<0.701
             then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           else
           0 end )  into v_week from testdta.f0008b where cddtpn='A' and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear; 
dbms_output.put_line(v_week);
end ;

Answer 2

1: 这个单独的sql可以执行吗？
2: 这个sql会不会返回多个值？
3: 可能是数据精度的问题？能否把sql运行的结果贴上来？

Answer 3

执行创建的时候没有报错,但运行就提示无效的对象,
但我再编辑的时候好象提示是没有testdta.f0008b这个表,我这个表确实是有的
并且这样可以执行的.
declare 
sdivd number;
sdyear number;
v_week integer;
begin
sdivd:=108069;
sdyear:=107;
select (case when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))>0.849
              then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7),'.'),3))<0.849
              then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3)))/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))>0.701
             then ceil((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           when TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))<> TO_NUMBER(SUBSTR(TO_CHAR(sdivd),1,3))
                and  to_number(substr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),instr(to_char((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7),'.'),3))<0.701
             then floor((TO_NUMBER(SUBSTR(TO_CHAR(sdivd),4,3))-TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),4,3))+364)/7)
           else
           0 end )  into v_week from testdta.f0008b where cddtpn='A' and TO_NUMBER(SUBSTR(TO_CHAR(CDDFYJ),1,3))=sdyear; 
dbms_output.put_line(v_week);
end ;
如果是没有这个表,上面的语句就没法运行的.

Answer 4

Try it...
use sys to do:SQL> grant select on testdta.f0008b to yourUser;

Answer 5

以前很多象你这样类似的问题,
要显式地授权,亦即是执行grant ....语句，因为过程function里不会继承角色中的权限的．

Answer 6

请问grant 语句是给什么对象的权限,是表,还是function?

Answer 7

还有如果我建立成功了这个function的,其他的用户是否又要单独授权才可以使用?

Answer 8

把６楼的yourUser改为你的用户名，执行后再试试FUNCTION.
按道理是的．

Answer 9

你执行FUNCTION的SCHEMA和编译FUNCTION的SCHEMA是一样的吗？如果不一样，那么执行FUNCTION的SCHEMA需要有访问testdta.f0008b这个表的权限．

Answer 10

总结楼上几位的。
确定当前用户是否有访问testdta这个用户的表f0008b 权限

Answer 11

GRANT 给用户操作表权限的

Answer 12

--在使用此函数的用户下给另一用户中的表创建一个同义词:create synonym f0008b for testdta.f0008b;

Answer 13

其实大家回答的都不正确,自己解决了,加上table space就可以了