Недавно ваяю на PL/SQL. Прошу помощи.
Картина такая:
Сваял процедуру, интересует непостредственно переменная place_name.
Необходимо ее значение подставить в селекты под if и else, где AALST (AALST ) указан в дважды в одинарных кавычках.
Пробовал ставить значение ':1' вместо ''AALST (AALST )'' и using place_name; в конце EXECUTE IMMEDIATE, но процедура не выполняется.
Как это реализовать?
CREATE OR REPLACE
procedure bim_bom as
cnt number;
place_name VARCHAR2(30):= 'AALST (AALST )';
begin
select count(*) into cnt from
con_roads_benelux a
WHERE
a.order8_id in (select order8_id from con_admin_structure_eu
WHERE name in ( select name from con_admin_structure_eu a where name=place_name
and admin_type = '1119'));
if cnt = 0 then
EXECUTE IMMEDIATE 'create table CON_ROAD_TEMP
AS
SELECT a.admin_place_id, a.name, a.street_type, a.street_name,
a.base_name, a.iso_country_code, a.order1_id, a.order8_id,
a.builtup_id, a.road_name_id, a.link_id, a.road_link_id,
a.address_type, a.side, a.route_type, a.language_code, a.admin_type
FROM con_roads_benelux a
WHERE
builtup_id in (select builtup_id from con_admin_structure_eu
where name in ( select name from con_admin_structure_eu a where name=''AALST (AALST )'' and admin_type = ''3110''))
and language_code = ''DUT''
order by a.street_name
';
else
EXECUTE IMMEDIATE '
create table CON_ROAD_TEMP
AS
SELECT a.admin_place_id, a.name, a.street_type, a.street_name,
a.base_name, a.iso_country_code, a.order1_id, a.order8_id,
a.builtup_id, a.road_name_id, a.link_id, a.road_link_id,
a.address_type, a.side, a.route_type, a.language_code, a.admin_type
FROM con_roads_benelux a
WHERE
a.order8_id in (select order8_id from con_admin_structure_eu
where name in ( select name from con_admin_structure_eu a where name=''AALST (AALST )''
and admin_type = ''1119''))
and language_code = ''DUT''
order by a.street_name
';
end if;
end;
/