英文:
dynamic creation of insert statements | SQL |
问题
我一直在努力学习如何创建动态的INSERT语句，用于具有CLOB数据的表格。
表格 - employee
firstname CLOB,
lastname CLOB,
age CLOB,
creation CLOB,
description CLOB
上述所有列的数据类型都是CLOB，并包含数据。
我还有一个名为main_employee的表格：
firstname varchar2(255),
lastname varchar2(255),
age number,
creation date,
description CLOB
我需要创建一个动态的插入语句。我通过PLSQL获取它并生成以下语句：
Insert into main_employee values(firstname,lastname,age,creation,description) as
select firstname,lastname,age,creation,description from employee
但问题是，我的employee表格包含CLOB数据，将其推送到main_employee时会引发错误。
错误：
clob不能插入到number，clob不能插入到varchar
我应该如何创建能够处理CLOB数据的动态插入语句。我听说有一种叫做cast的东西。
下面是表格中的示例数据，仅用逗号分隔进行解释：
'ABC','XYZ',24,20230930,'Testing in progess'
英文:
I have been working hard how to create dynamic INSERT statement the table which as CLOB data
Table - employee
firstname CLOB,
lastname CLOB,
age CLOB,
creation CLOB,
description CLOB
The above all the column data type is CLOB and as data in it
I have one more table called main_employe
firstname varchar2(255),
lastname varchar2(255),
age number,
creation date,
description CLOB
I need to create a dynamic insert statement. This I getting it through PLSQL and its generating
Insert into main_employee values(firstname,lastname,age,creation,description) as
select firstname,lastname,age,creation,description from employee
But the problem is my employee table as data which is clob and it throws error when pushing to main_employee
Error :
clob cannot be inserted to number , clob cannot be inserted to varchar
How do I need to create dynamic insert statement which can handle clob data. I heard their is something called cast
Below is the Sample data which is in table
And just for explaining I mentioned in comma seperated
'ABC',XYZ',24,20230930,'Testing in progess'
答案1
得分: 0
这是一个选项。
源表：
SQL> create table employee
2 (firstname clob,
3 lastname clob,
4 age clob,
5 creation clob
6 );
Table created.
SQL> insert into employee values ('Little', 'Foot', '7', '25.03.2023');
1 row created.
目标表：
SQL> create table main_employee
2 (firstname varchar2(255),
3 lastname varchar2(255),
4 age number,
5 creation date
6 );
Table created.
插入：
SQL> insert into main_employee (firstname, lastname, age, creation)
2 select to_char(firstname),
3 to_char(lastname),
4 to_number(age),
5 to_date(creation, 'dd.mm.yyyy')
6 from employee;
1 row created.
结果：
SQL> select * from main_employee;
FIRSTNAME LASTNAME AGE CREATION
-------------------- -------------------- ---------- ----------
Little Foot 7 2023-03-25
SQL>;
英文:
Here's one option.
Source table:
SQL> create table employee
2 (firstname clob,
3 lastname clob,
4 age clob,
5 creation clob
6 );
Table created.
SQL> insert into employee values ('Little', 'Foot', '7', '25.03.2023');
1 row created.
Target table:
SQL> create table main_employee
2 (firstname varchar2(255),
3 lastname varchar2(255),
4 age number,
5 creation date
6 );
Table created.
Insert:
SQL> insert into main_employee (firstname, lastname, age, creation)
2 select to_char(firstname),
3 to_char(lastname),
4 to_number(age),
5 to_date(creation, 'dd.mm.yyyy')
6 from employee;
1 row created.
Result:
SQL> select * from main_employee;
FIRSTNAME LASTNAME AGE CREATION
-------------------- -------------------- ---------- ----------
Little Foot 7 2023-03-25
SQL>
I'm not sure what you meant to say by creating a "dynamic" insert statement; there's nothing "dynamic" in what you're doing.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论