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

Question

CREATE OR REPLACE PROCEDURE DEVICE.MYEXECUTE (aa in varchar2)
AS
  sql varchar2;
    BEGIN
    sql:='select count(*) from '+aa;       end;
怎么在存储过程中执行sql

Answer 1

8i 以上可以用 execute immediate sql;8i 以下用dbms_sql包来处理还有你的这句话sql:='select count(*) from '+aa;   改为
sql:='select count(*) from '||aa;

Answer 2

sql:='select count(*) from '||aa;
execute immediate sql;

Answer 3

用动态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语句的字符串。

Answer 4

这句不能编译:报错
   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,re,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,
                            re=Mre,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,re,userid,modifydate,append,font
                    from Device.Tstationhousemaptemp where name=Mname and czygh=vczygh;
        end if;
          end if;
    end;
  end loop;
 close cur1; commit;end;

Answer 5

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;

Answer 6

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

Answer 7

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,re,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,
                            re=v_cut.re,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,re,userid,modifydate,append,font
                    from Device.Tstationhousemaptemp where name=v_cut.name and czygh=vczygh;
end if;
end loop;
end;
/

Answer 8

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