oracle - 将生成的 SQL 查询表复制到另一个表中
问题描述
我在蟾蜍工作。在这里,我想创建一个过程,其中将根据执行的选择查询创建一个新表。但我收到此错误:
[错误] PLS-00103 (15: 21):PLS-00103:在预期以下情况之一时遇到符号“CREATE”:
( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue
以下是我到目前为止的程序代码,有人可以指导我错误在哪里。
create or replace procedure fire
is
v_full_name VARCHAR2(500);
sal varchar(200);
jobid varchar(100);
cpct varchar(50);
mgid varchar(25);
did varchar(20);
cid varchar(20);
rid varchar(20);
lid varchar(20);
Begin
execute immediate create table resquery as
(
select HR.EMPLOYEES.FIRST_NAME||' '||HR.EMPLOYEES.LAST_NAME AS
Full_Name,HR.EMPLOYEES.SALARY as sal,HR.EMPLOYEES.JOB_ID as ji,
HR.EMPLOYEES.COMMISSION_PCT as cmpct,HR.EMPLOYEES.MANAGER_ID as
mgid,HR.EMPLOYEES.DEPARTMENT_ID as dep,HR.COUNTRIES_EXTERNAL.COUNTRY_ID as
country,HR.DW_REGION.R_ID as region,
HR.LOCATIONS.LOCATION_ID as loc
into v_full_name,sal,jobid,cpct,mgid,did,cid,rid,lid
from HR.EMPLOYEES
join HR.DEPARTMENTS ON
HR.EMPLOYEES.DEPARTMENT_ID=HR.DEPARTMENTS.DEPARTMENT_ID
join HR.LOCATIONS ON HR.DEPARTMENTS.LOCATION_ID=HR.LOCATIONS.LOCATION_ID
join HR.COUNTRIES_EXTERNAL ON HR.LOCATIONS.COUNTRY_ID=HR.COUNTRIES_EXTERNAL.COUNTRY_ID
join HR.DW_REGION ON HR.COUNTRIES_EXTERNAL.REGION_ID=HR.DW_REGION.R_ID
where HR.COUNTRIES_EXTERNAL.COUNTRY_ID='US'
AND trunc(HR.EMPLOYEES.HIRE_DATE) BETWEEN TO_DATE('16/08/2002','DD/MM/YYYY') AND
TO_DATE('07/12/2007','DD/MM/YYYY'))
end
/
解决方案
立即执行需要一个字符串。将您的 CREATE 语句用引号括起来。此外,execute immediate 是一个命令,后面必须跟一个分号。
create or replace procedure fire
is
v_full_name VARCHAR2(500);
sal varchar(200);
jobid varchar(100);
cpct varchar(50);
mgid varchar(25);
did varchar(20);
cid varchar(20);
rid varchar(20);
lid varchar(20);
Begin
execute immediate 'create table resquery as
(
select HR.EMPLOYEES.FIRST_NAME||'' ''||HR.EMPLOYEES.LAST_NAME AS
Full_Name,HR.EMPLOYEES.SALARY as sal,HR.EMPLOYEES.JOB_ID as ji,
HR.EMPLOYEES.COMMISSION_PCT as cmpct,HR.EMPLOYEES.MANAGER_ID as
mgid,HR.EMPLOYEES.DEPARTMENT_ID as dep,HR.COUNTRIES_EXTERNAL.COUNTRY_ID as
country,HR.DW_REGION.R_ID as region,
HR.LOCATIONS.LOCATION_ID as loc
into v_full_name,sal,jobid,cpct,mgid,did,cid,rid,lid
from HR.EMPLOYEES
join HR.DEPARTMENTS ON
HR.EMPLOYEES.DEPARTMENT_ID=HR.DEPARTMENTS.DEPARTMENT_ID
join HR.LOCATIONS ON HR.DEPARTMENTS.LOCATION_ID=HR.LOCATIONS.LOCATION_ID
join HR.COUNTRIES_EXTERNAL ON HR.LOCATIONS.COUNTRY_ID=HR.COUNTRIES_EXTERNAL.COUNTRY_ID
join HR.DW_REGION ON HR.COUNTRIES_EXTERNAL.REGION_ID=HR.DW_REGION.R_ID
where HR.COUNTRIES_EXTERNAL.COUNTRY_ID=''US''
AND trunc(HR.EMPLOYEES.HIRE_DATE) BETWEEN TO_DATE(''16/08/2002'',''DD/MM/YYYY'') AND
TO_DATE(''07/12/2007'',''DD/MM/YYYY''))';
end;
/
推荐阅读
- amazon-web-services - 具有特定私有 IP 的 Terraform AWS 实例
- php - 未定义操作方法,此错误是对使用 codeigniter 4 的 curl 请求的响应
- android - 为什么会有一条线?我没有添加那行
- javascript - 在没有 event.stopPropagation() 的情况下单击下拉 div 时防止引导选择的下拉菜单关闭
- facebook - 如何将我当前的 URL 发送到 Facebook 聊天?
- swift - 迅速将 [T?] 转换为 [T]?
- android - 检查 GET_SIGNATURES 返回的一份证书的有效性
- javascript - 如何修复代码中的计算器错误?
- java - Legacy Eureka Server、当代 Spring Boot 和 Eureka 依赖不匹配
- powershell - 从表中输出字符串