执行存储过程的问题???在线等待

gczhong1980 2003-09-12 03:43:29

CREATE OR REPLACE PROCEDURE DEVICE.MYEXECUTE (aa in varchar2)
AS
sql varchar2;
BEGIN
sql:='select count(*) from '+aa;

end;
怎么在存储过程中执行sql

...全文

51 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

nicholaz 2003-09-16

打赏
举报

execute immediate .....

onejune4450 2003-09-15

打赏
举报

楼上的不是那句改写了吗？

gczhong1980 2003-09-15

打赏
举报

我执行的时候在
execute 'select count(name) into mm from '||tablename||' where name=MNAME';
出错,提示:missing keyword

beckhambobo 2003-09-12

打赏
举报

CREATE OR REPLACE PROCEDURE DEVICE.PUpdateMachineHouseMAP
(vczygh in varchar2,tablename in varchar2)
as
cursor cur1 is
select name,stationhouseid,caption,parentname,controltype,facttype,queueid,rackid,
top,left,width,height,hint,showhint,color,flag,remark,userid,modifydate,append,font
from Device.Tstationhousemaptemp where czygh=vczygh;

mm number(8);
begin
for v_cut in cur1 loop
execute immediate 'select count(name) from '||tablename||' where name='||v_cut.NAME into mm;
if mm=1 then
update Device.Tstationhousemap11 set caption=v_cut.CAPTION,parentname=v_cut.PARENTNAME,
queueid=v_cut.QUEUEID,rackid=v_cut.RACKID,top=v_cut.TOP,left=v_cut.LEFT,width=v_cut.width, height=v_cut.height,hint=v_cut.hint,showhint=v_cut.showhint,color=v_cut.color,flag=v_cut.flag,
remark=v_cut.remark,userid=v_cut.userid,modifydate=v_cut.modifydate,font=v_cut.font
where name=v_cut.name;
elsif mm=0 then
insert into Device.Tstationhousemap11 select name,stationhouseid,caption,parentname,controltype,facttype,
queueid,rackid,top,left,width,height,hint,showhint,null,color,flag,remark,userid,modifydate,append,font
from Device.Tstationhousemaptemp where name=v_cut.name and czygh=vczygh;
end if;
end loop;
end;
/

goldenmean 2003-09-12

打赏
举报

比如：v_sql是拼装好的SQL语句，在Package中这样让他执行：
EXECUTE IMMEDIATE v_sql;

l2g32003 2003-09-12

打赏
举报

execute 'select count(name) into mm from '||tablename||' where name=MNAME';
改成
execute 'select count(name) from '||tablename||' where name=mname_t' using MNAMW into mm;

gczhong1980 2003-09-12

打赏
举报

这句不能编译:报错
Encountered the symbol "select count(name) into mm from " when expecting one of the following : := .( 0 immediate the symbol ":=" was subtituted for "select count(name) into mm from" to continue

CREATE OR REPLACE PROCEDURE DEVICE.PUpdateMachineHouseMAP
(vczygh in char,tablename in char)
as
cursor cur1 is
select name,stationhouseid,caption,parentname,controltype,facttype,queueid,rackid,
top,left,width,height,hint,showhint,color,flag,remark,userid,modifydate,append,font
from Device.Tstationhousemaptemp where czygh=vczygh;

MNAME CHAR(40);
MSTATIONHOUSEID CHAR(12);
MCAPTION CHAR(100);
MPARENTNAME CHAR(40);
MCONTROLTYPE CHAR(1);
MFACTTYPE CHAR(1);
MQUEUEID NUMBER(8);
MRACKID NUMBER(8);
MTOP NUMBER(4);
MLEFT NUMBER(4);
MWIDTH NUMBER(4);
MHEIGHT NUMBER(4);
MHINT VARCHAR2(60);
MSHOWHINT NUMBER(1);
MCOLOR CHAR(11);
MFLAG NUMBER(1);
MREMARK VARCHAR2(100);
MUSERID CHAR(8);
MMODIFYDATE DATE;
MAPPEND VARCHAR2(10);
MFONT CHAR(2);

mm number(8);
begin
OPEN cur1;
loop
begin
fetch cur1 into MNAME,MSTATIONHOUSEID,MCAPTION,MPARENTNAME,MCONTROLTYPE,MFACTTYPE,MQUEUEID,MRACKID,MTOP,MLEFT,MWIDTH,
MHEIGHT,MHINT,MSHOWHINT,MCOLOR,MFLAG,MREMARK,MUSERID,MMODIFYDATE,MAPPEND,MFONT;

Exit when cur1%notfound ;

execute 'select count(name) into mm from '||tablename||' where name=MNAME';
if mm=1 then
update Device.Tstationhousemap11 set caption=MCAPTION,parentname=MPARENTNAME,
queueid=MQUEUEID,rackid=MRACKID,top=MTOP,left=MLEFT,width=Mwidth,
height=Mheight,hint=Mhint,showhint=Mshowhint,color=Mcolor,flag=Mflag,
remark=Mremark,userid=Muserid,modifydate=Mmodifydate,font=Mfont
where name=Mname;
else
if mm=0 then
insert into Device.Tstationhousemap11 select name,stationhouseid,caption,parentname,controltype,facttype,
queueid,rackid,top,left,width,height,hint,showhint,null,color,flag,remark,userid,modifydate,append,font
from Device.Tstationhousemaptemp where name=Mname and czygh=vczygh;
end if;
end if;
end;
end loop;
close cur1;

commit;

end;

minkoming 2003-09-12

打赏
举报

用动态SQL 语句来执行
使用PREPARE命令准备SQL语句
该命令用于命名和分析SQL语句。在分析了SQL语句后，可以使用EXECUTE语句多次执行该语句。PREPARE命令的语法如下：
EXEC SQL PREPARE statement_name FROM{:host_string|string_literal};
参数说明如下：
·statement_name：语句名称，它是一个标识符而不是宿主变量。
·host_string：包含了SQL语句的宿主变量。
·string_literal：包含了SQL语句的字符串。