时间戳

首页 > 解决方案 > 创建函数“COPY EMPLOYEES_WITH_REC”，使用记录类型将数据从 EMPLOYEES 表复制到 JRF _EMPLOYEES 表

sql - 创建函数“COPY EMPLOYEES_WITH_REC”，使用记录类型将数据从 EMPLOYEES 表复制到 JRF _EMPLOYEES 表

问题描述

我不知道什么是坏的，任何人都可以帮忙吗？ 

CREATE OR REPLACE FUNCTION COPY_EMPLOYEES_WITH_REC (O_error_message OUT VARCHAR2)
    RETURN BOOLEAN IS
     --
     -- Local variable of R_emp_table type
     L_emp_table R_emp_table;
     --
     -- Associative Array of EMPLOYEES
     TYPE A_COPY_EMPLOYEES_TYPE
     IS TABLE OF L_emp_table%TYPE;

 --
 -- Local variable of A_COPY_EMPLOYEES_TYPE type
 L_copy_employees A_COPY_EMPLOYEES_TYPE;
 --
BEGIN
  --
  -- POPULATE ASSOCIATIVE ARRAY WITH EMPLOYEES TABLE DATA
  SELECT EMPLOYEE_ID,
         FIRST_NAME,
         LAST_NAME,
         EMAIL,
         PHONE_NUMBER,
         HIRE_DATE,
         JOB_ID,
         SALARY,
         COMMISSION_PCT,
         MANAGER_ID,
         DEPARTMENT_ID
  BULK COLLECT INTO L_copy_employees
  FROM EMPLOYEES
  ORDER BY EMPLOYEE_ID, MANAGER_ID;
  --
  -- Populate  JRF_EMPLOYEES with Associative Array data
  FORALL i IN L_copy_employees.FIRST..L_copy_employees.LAST
   INSERT INTO JRF_EMPLOYEES
   VALUES (L_copy_employees(i).EMPLOYEE_ID,
           L_copy_employees(i).FIRST_NAME,
           L_copy_employees(i).LAST_NAME,
           L_copy_employees(i).EMAIL,
           L_copy_employees(i).PHONE_NUMBER,
           L_copy_employees(i).HIRE_DATE,
           L_copy_employees(i).JOB_ID,
           L_copy_employees(i).SALARY,
           L_copy_employees(i).COMMISSION_PCT,
           L_copy_employees(i).MANAGER_ID,
           L_copy_employees(i).DEPARTMENT_ID);
  --
  COMMIT;
  --
  RETURN TRUE;
  --
EXCEPTION
  --
  WHEN OTHERS THEN
    O_error_message := SQLERRM;
    RETURN FALSE;
  --
END COPY_EMPLOYEES_WITH_REC;


 

标签： sqloracleplsql

解决方案

你为什么要创建这种A_COPY_EMPLOYEES_TYPE类型？它基于另一种可能已经存在的表类型。

我有两张表，一张包含三列，第二张只有两列。

create table employees(id, name, sal) as (
  select 1, 'Pete', 1000 from dual union all
  select 2, 'Matt',  800 from dual );

create table jrf_employees as select id, name from employees where 1=0;

您可以使用 single 将数据从第一个表复制到第二个insert，但是 homework(?) 需要使用函数和用户定义类型，因此：

create or replace function copy_employees (o_error_message out varchar2) return boolean is
    type tp_tb_emp is table of employees%rowtype;
    v_copy tp_tb_emp;
begin
    select * bulk collect into v_copy from employees;
    forall i in v_copy.first..v_copy.last
        insert into jrf_employees(id, name) values (
            v_copy(i).id,
            v_copy(i).name);
    return true;
exception when others then
    o_error_message := sqlerrm;
    return false;
end copy_employees;

我们可以测试功能：

declare 
    v_err varchar2(1000);
    v_ok boolean;
begin 
    v_ok := copy_employees(v_err);
    if not v_ok then 
        dbms_output.put_line(v_err);
    else 
        dbms_output.put_line('data copied');
    end if;
end;

如果要使用记录类型（而不是rowtype），则：

create or replace function copy_employees_2 (o_error_message out varchar2) return boolean is
    type tp_tr_emp is record (id employees.id%type, name employees.name%type);
    type tp_tb_emp is table of tp_tr_emp;
    v_copy tp_tb_emp;
  begin
      select id, name bulk collect into v_copy from employees;
      forall i in v_copy.first..v_copy.last
          insert into jrf_employees(id, name) values (
              v_copy(i).id,
              v_copy(i).name);
      return true;
  exception when others then
      o_error_message := sqlerrm;
      return false;
  end copy_employees_2;

这两个功能都对我有用。您可以在第二个表中添加对唯一 id 的约束并运行两次函数，它会引发有关违反唯一性的消息错误。

推荐阅读