将结果集插入 Oracle 中的表中

Question

嘿嘿，

我必须编写一个过程，将 select 语句中的结果集插入到表中。 我的一位同事之前做过类似的事情，将值从一个表复制到另一个表。他的声明如下：

CREATE OR REPLACE PROCEDURE Co-Worker(
    pId IN INT
)
AS
BEGIN
    INSERT INTO Table1_PROCESSED
    SELECT * FROM Table1
    WHERE ID = pId;

    DELETE FROM Table1
    WHERE ID = pId;

END Co-Worker;
/

这里提到的两个表具有相同的结构（实际上 table1_processed 只是表 1 的副本）。 所以我想“嘿！我也从我的选择语句中得到了一个结果集！那为什么我不稍微调整一下它呢！” 所以我创建了这样的表：

MyTable:
TIMEID (number) | NAME (varchar2 - 128)
-----------------------------------
VALUE       | VALUE
VALUE       | VALUE
VALUE       | VALUE

我的过程如下：

CREATE OR REPLACE procedure MyProcedure(
pdate in date,
pJobtype in number  default 3,
pTasktype in number default 4,
pJobstatus in number default 1,
pTaskstatus in number default 4
)
AS
    pformateddate date;
BEGIN
    Select to_date(to_char(to_date(pdate, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY'), 'DD.MM.YYYY') 
    into pformateddate 
    from dual;
Insert into MyTable (TIMEID, NAME)
Select Function_GETTIMEID(to_date(st, 'DD.MM.YYYY HH24')) TIMEID
       ,to_char(ext) NAME 
from(
    Select to_char(arch_job.exec_start, 'DD.MM.YYYY HH24') st
           ,file.name ext
           , count(file.id) cnt
    from 
         arch_task_file 
             left join file on arch_task_file.File_ID = file.ID
             left join arch_task on arch_task_file.Task_ID = arch_task.ID
             left join arch_job on arch_task.Job_ID = arch_job.ID
    where 
        arch_job.exec_start > pformateddate 
        and arch_job.exec_end <pformateddate + 1 
        and arch_job.jobtype_id = pJobtype 
        and arch_job.jobstatus_id = pJobstatus 
        and arch_task.Tasktype_ID = pTasktype 
        and arch_task.Taskstatus_ID = pTaskstatus
     group by 
         file.name,
           to_char(arch_job.exec_start, 'DD.MM.YYYY HH24'
       )
    );
End MyProcedure;
/

单独的大型 Select 语句的结果如下所示：

TIMEID      | NAME
-----------------------------------
VALUE       | VALUE
VALUE       | VALUE
VALUE       | VALUE

但是如果我执行此过程并给它一个虚拟日期（sysdate - 12 或类似“16.07.2010”的日期10:32:50') 我的蟾蜍给我一条消息“程序已完成”，我的桌子仍然是空的......！ 但正如之前所说，大型 Select 语句会给出结果，因此不应尝试插入空结果集......！谁能告诉我为什么我的程序不起作用？

感谢每一个有用的答案。 =）

再见！

附： 需要

Select to_date(to_char(to_date(pdate, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY'), 'DD.MM.YYYY') 
into pformateddate 
from dual;

缩短 pDate 值！我测试了它，所以它也有效，你可以在整个逻辑中忽略它。这里只是为了让您全面了解情况！

Answer 1

这是 SQL 论坛中非常常见的模式。模式是OP说的

“我在 TOAD 工作表中运行此 SQL
（或其他什么）并且它有效。但是当
我把它放在不同的上下文中 -
例如存储过程 - 它
不起作用。给出了什么？”

给出的是两个语句不相同。某处存在转录错误。也许省略了连接或添加了额外的连接。最可能的错误来源是 显然，我无法告诉您差异在

哪里，我所能做的就是敦促您仔细检查这两个 SQL 语句，并找出差异（

如果您确实找不到任何差异） 。差异那么你需要调试你的代码。最快的方法是在插入语句后添加这一行：

dbms_output.put_line('Rows inserted = '||to_char(sql%rowcount));

你需要在 TOAD 中启用 DBMS_OUTPUT；这至少会有一个选项卡。告诉您查询是否确实返回零行，或者您的过程正在插入行，但由于某种原因您没有看到它们，这是两个不同的问题。

Answer 2

您需要在运行此过程的 Toad 会话中提交 COMMIT，然后才能在任何其他会话（例如表浏览器）中看到表中的数据。你记得这样做吗？

Answer 3

与您的问题并不真正相关，但这

Select to_date(to_char(to_date(pdate, 'DD.MM.YYYY HH24:MI:SS'), 'DD.MM.YYYY'), 'DD.MM.YYYY') 
into pformateddate 
from dual;

只是从传递的参数中删除时间元素的一种冗长的方法。这做了同样的事情：

Select trunc(pdate) 
into pformateddate 
from dual; 

或者确实正如托尼指出的那样，这是一个简单的任务：

pformateddate := trunc(pdate);