能编译,但执行的时候提示 ORA-01027:在数据定义操作中不允许有绑定变量create or replace procedure createTable_sp_vehicle_68 as v_sql_siteOfSmartphone varchar2(500);
siteTypeOfSp varchar2(10); --smartphone 站点的siteTypebegin
v_sql_siteOfSmartphone := 'select sitesysid from site_management where type=:site_type';
siteTypeOfSp := '01'; --需要修改 --create table sp_vehicle(vehicle)
execute immediate 'create table sp_vehicle as select * from vehicle where v.site_sys_id in (' || v_sql_siteOfSmartphone || ')' --这一段报错
using siteTypeOfSp; DBMS_OUTPUT.put_line('create table sp_vehicle success');
end createTable_sp_vehicle_68;
siteTypeOfSp varchar2(10); --smartphone 站点的siteTypebegin
v_sql_siteOfSmartphone := 'select sitesysid from site_management where type=:site_type';
siteTypeOfSp := '01'; --需要修改 --create table sp_vehicle(vehicle)
execute immediate 'create table sp_vehicle as select * from vehicle where v.site_sys_id in (' || v_sql_siteOfSmartphone || ')' --这一段报错
using siteTypeOfSp; DBMS_OUTPUT.put_line('create table sp_vehicle success');
end createTable_sp_vehicle_68;
v.site_sys_id ---这个v是从哪里来的???
是呀,最后生成的那个报错的sql语句里就是有 :site_type吧知道是啥东西了
create or replace procedure createTable_sp_vehicle_68 as
siteTypeOfSp varchar2(10);
begin
siteTypeOfSp := '01';
execute immediate 'create table sp_vehicle as select * from vehicle where v.site_sys_id in (select sitesysid from site_management where type=:site_type)'
using siteTypeOfSp;
end createTable_sp_vehicle_68;
这样试一下
create or replace procedure createTable_sp_vehicle_68 as v_sql_siteOfSmartphone varchar2(500);
siteTypeOfSp varchar2(10); --smartphone 站点的siteTypebegin
v_sql_siteOfSmartphone := 'select sitesysid from site_management where type='''||'01'||'''';
--siteTypeOfSp := '01'; --需要修改 --create table sp_vehicle(vehicle)
execute immediate 'create table sp_vehicle as select * from vehicle v where v.site_sys_id in (' || v_sql_siteOfSmartphone || ')'; DBMS_OUTPUT.put_line('create table sp_vehicle success');
end createTable_sp_vehicle_68;
try this.
create table 放在存储过程之外,
用insert into select 试试,这样效率高,还能启动事务