如何在PL/SQL中以编程方式设置表名?

问题描述：

我创建了以下简单的PL/SQL存储过程示例，以询问特定问题.此过程将员工姓名和ID号插入到名为employees_???的表中.这 ???解释如下.

I created the following simple PL/SQL stored procedure example to ask a specific question. This procedure inserts an employee name and id number into a table called employees_???. The ??? is explained below.

PROCEDURE hire_employee (emp_id IN INTEGER, name IN VARCHAR2, country IN VARCHAR2) 
AS
BEGIN
    INSERT INTO employees_??? VALUES (emp_id, name, 1000);
END hire_employee;

我需要基于IN变量country设置表名称.例如，

What I need is to set the table name based on the IN variable country. For example,

如果country ='usa'，我希望INSERT行显示为:

If country = 'usa', I want the INSERT line to read:

INSERT INTO employees_usa VALUES (emp_id, name, 1000);

如果country ='germany'，我希望INSERT行显示为:

If country = 'germany', I want the INSERT line to read:

INSERT INTO employees_germany VALUES (emp_id, name, 1000);

如果country ='france'，我希望INSERT行显示为:

If country = 'france', I want the INSERT line to read:

INSERT INTO employees_france VALUES (emp_id, name, 1000);

等...

在PL/SQL中是否可以通过代替employee_???的方式来做到这一点，因此仅使用一行用于INSERT的代码?还是使用case或if/then/else语句是最好的方法?

Is there a way to do this in PL/SQL by substituting something in place of employee_??? so only one line of code for INSERT is used? Or is using a case or if/then/else statement the best way?

答

要回答您的问题，您必须使用

To answer your question, you have to use execute immediate and create your statement dynamically.

create or replace procedure hire_employee (
        emp_id IN INTEGER
      , name IN VARCHAR2
      , country IN VARCHAR2 ) is

   -- maximum length of an object name in Oracle is 30
   l_table_name varchar2(30) := 'employees_' || country;

begin
    execute immediate 'insert into ' || l_table
                       || ' values (:1, :2, 1000)'
      using emp_id, name;
end hire_employee;

但是，这是一种非常复杂的数据存储方式.如果要选择所有数据，则必须合并大量表.

However, this is a massively over-complicated way of storing the data. If you want to select all data you have to union large numbers of tables.

最好正确地规范化数据库并将国家/地区添加到employees表中.

It would be far better to normalise the database properly and add country to an employees table.

类似以下内容:

create table employees (
    emp_id number(16)
  , country varchar2(3) -- ISO codes
  , name varchar2(4000) -- maximum who knows what name people might have
  , < other_columns >
  , constraint pk_employees primary key ( emp_id )
    );

您的过程将变成一个非常简单的插入语句:

Your procedure then becomes a very simple insert statement:

create or replace procedure hire_employee (
       emp_id in integer
     , name in varchar2
     , country in varchar2 ) is

    insert into employees
    values ( emp_id, country, name, 1000 );

end hire_employee;

如何在PL/SQL中以编程方式设置表名?

相关推荐