表名不确定,需要动态拼接。表名格式为”car_state_123“,后面的123需要动态获取。获取语句如下:
select car_model_id from car_info where no='XXXX'最后需要的sql语句为:select * from car_state_123,请问该语句如何写???
select car_model_id from car_info where no='XXXX'最后需要的sql语句为:select * from car_state_123,请问该语句如何写???
--在存储过程里面完成呗
--例:
create or replace procedure yyp_cwdh(table_name varchar)
is
str_sql varchar2(400):='';
begin
str_sql:='select * from '||table_name||' where 1=1';
execute immediate str_sql;
end yyp_cwdh;
一个拼接sql语句写存储过程不好吧,将来不好维护,存储过程越来越多。。
str_l_sql varchar2(1000);
begin
for c in (select car_model_id from car_info where no='XXXX') loop str_l_sql := 'select * from car_state_'||c.car_model_id;
execute immediate str_l_sql into ......
end loop;
end;
declare
tabName varchar(1000);
id integer;
tabSql varchar(1000);
begin
tabName:=('select * from car_state_'||'');
id:=select car_model_id from car_info where car_lic_plate='XXXXX';
tabSql:=tabName||id
EXECUTE IMMEDIATE tabSql USING tabName, id;
commit;
end;
该怎么改呢???
tabName varchar(1000);
id number;
tabSql varchar(1000);
begin
select car_model_id into id from car_info where car_lic_plate='XXXXX';
--id:=select car_model_id from car_info where car_lic_plate='XXXXX';
--tabSql:=tabName||id
tabSql := 'select * from :a';
EXECUTE IMMEDIATE tabSql USING 'car_state_'||to_char(id);
commit;
end;
Select JOB_NUM,
JOB_TYPE,
JOB_SERVICE_NAME,
JOB_NAME,
JOB_STATUS,
ERR_CODE,
ERR_MESSAGE,
DATELASTMAINT,
JOB_VALIDATE
From RP_TASKLIST
WHERE JOB_VALIDATE = 1
AND JOB_TYPE = 'GL'
And JOB_STATUS < IN_JOB_STATUS
ORDER BY JOB_NUM;
For R_TASKLIST IN TASKLIST LOOP
JOB_SERVICE_NAME_ := R_TASKLIST.JOB_SERVICE_NAME;
JOB_NUM_ := R_TASKLIST.JOB_NUM; execute immediate 'BEGIN ' || R_TASKLIST.Job_Service_Name ||
'(:1,:2,:3);END;' --执行子任务
using in in_yyyymmdd, out out_ErrorNbr, out out_ErrorMsg;