自定义函数如下
create or replace function Get_ZDNR( ZHENGZ VARCHAR2 )
return varchar2 as
returnvalue varchar2(100);
Str varchar2(100);
ZDZID varchar2(5);
begin Str :=ZHENGZ ;
Create GLOBAL TEMPORARY Tarea (ZDZID VARCHAR2(5))
ON COMMIT PRESERVE ROWS; while LENGTH(Str)>0
begin
if(Instr(Str,',')>0)
begin
ZDZID:=SUBSTR(Str,1,Instr(Str,',')-1)
str:=SUBSTR(Str,Instr(Str,',')+1,LENGTH(Str))
Insert into Tarea(ZDZID) end
else
begin
Insert into Tarea(Str)
end end select returnvalue=returnvalue+a.ZDNR+','
from (
select ZDZ,ZDNR from JC_ZDWH_HZ
left outer join JC_ZDWH_MX on JC_ZDWH_HZ.ZDBH=JC_ZDWH_MX.ZDBH
where ZDm='ZZDM' order by ZDZ ) a inner join Tarea on a.ZDZ=T_area.ZDZID
IF RIGHT(@returnvaluesc,1)=','
returnvalue := LEFT(returnvalue,LENGTH(returnvalue)-1)drop Table TareaRETURN returnvalueend调试下 创建按临时表 Create GLOBAL TEMPORARY Tarea (ZDZID VARCHAR2(5))
ON COMMIT PRESERVE ROWS; 这行语句报FUNCTION ZQCY_JKDABS.GET_ZDNR 编译错误错误:PLS-00103: 出现符号 "CREATE"在需要下列之一时:
begin case declare end
exception exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
行:9
文本:Create GLOBAL TEMPORARY Tarea (ZDZID VARCHAR2(5))
错 请问是什么原因 应咋改 小弟初始使用oracle 很多语法 不明白 望大侠能指正下
create or replace function Get_ZDNR( ZHENGZ VARCHAR2 )
return varchar2 as
returnvalue varchar2(100);
Str varchar2(100);
ZDZID varchar2(5);
begin Str :=ZHENGZ ;
Create GLOBAL TEMPORARY Tarea (ZDZID VARCHAR2(5))
ON COMMIT PRESERVE ROWS; while LENGTH(Str)>0
begin
if(Instr(Str,',')>0)
begin
ZDZID:=SUBSTR(Str,1,Instr(Str,',')-1)
str:=SUBSTR(Str,Instr(Str,',')+1,LENGTH(Str))
Insert into Tarea(ZDZID) end
else
begin
Insert into Tarea(Str)
end end select returnvalue=returnvalue+a.ZDNR+','
from (
select ZDZ,ZDNR from JC_ZDWH_HZ
left outer join JC_ZDWH_MX on JC_ZDWH_HZ.ZDBH=JC_ZDWH_MX.ZDBH
where ZDm='ZZDM' order by ZDZ ) a inner join Tarea on a.ZDZ=T_area.ZDZID
IF RIGHT(@returnvaluesc,1)=','
returnvalue := LEFT(returnvalue,LENGTH(returnvalue)-1)drop Table TareaRETURN returnvalueend调试下 创建按临时表 Create GLOBAL TEMPORARY Tarea (ZDZID VARCHAR2(5))
ON COMMIT PRESERVE ROWS; 这行语句报FUNCTION ZQCY_JKDABS.GET_ZDNR 编译错误错误:PLS-00103: 出现符号 "CREATE"在需要下列之一时:
begin case declare end
exception exit for goto if loop mod null pragma raise return
select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
行:9
文本:Create GLOBAL TEMPORARY Tarea (ZDZID VARCHAR2(5))
错 请问是什么原因 应咋改 小弟初始使用oracle 很多语法 不明白 望大侠能指正下
ON COMMIT PRESERVE ROWS;改成下面的:
execute immediate 'Create GLOBAL TEMPORARY Tarea (ZDZID VARCHAR2(5))
ON COMMIT PRESERVE ROWS';