使用PL/SQL匿名块插入
使用PL/SQL匿名块插入
提问于 2021-02-27 02:05:31
我的任务是创建一些表，并使用PL/SQL匿名块向其中插入数据。我可以创建表，但我终生无法弄清楚如何插入数据。我尝试过的每一种方法都会以某种方式引发错误。目前，我有以下代码：
DECLARE
create_student VARCHAR2(500) := 'CREATE TABLE student(stu_id CHAR(5),Lname VARCHAR2(10) NOT NULL,Fname VARCHAR2(10) NOT NULL,Mi VARCHAR(2),Sex CHAR(1),Major VARCHAR(10),Home_State VARCHAR(4),CONSTRAINT stu_id_pk PRIMARY KEY (stu_id))';
create_course VARCHAR(500) := 'CREATE TABLE course(course_id VARCHAR(8),section CHAR(3)NOT NULL,c_name VARCHAR(30)NOT NULL,c_description VARCHAR(50),CONSTRAINT course_id_pk PRIMARY KEY (course_id))';
create_student_course VARCHAR(300) := 'CREATE TABLE student_course(stu_id CHAR(5),course_id VARCHAR(8),section CHAR(3),CONSTRAINT ck_stuid_courseid PRIMARY KEY (stu_id, course_id))';
insert_student VARCHAR2(1200) := 'INSERT ALL
INTO student VALUES(‘10011’, ‘Smith’, ‘Peter’, ‘M’, ‘M’, ‘Math’, ‘TX’)
INTO student VALUES(‘10012’, ‘Jones’, ‘Sam’, ‘A’, ‘M’, ‘English’, ‘TX’)
INTO student VALUES(‘10013’, ‘Peters’, ‘Amy’, ‘A’, ‘F’, ‘English’, ‘ME’)
INTO student VALUES(‘10014’, ‘Johnson’, ‘John’, ‘J’, ‘M’, ‘CompSci’, ‘CA’)
INTO student VALUES(‘10015’, ‘Penders’, ‘Alton’, ‘P’, ‘F’, ‘Math’, ‘GA’)
INTO student VALUES(‘10016’, ‘Allen’, ‘Diane’, ‘J’, ‘F’, ‘Geography’, ‘Minn’)
INTO student VALUES(‘10017’, ‘Gill’, ‘Jennifer’, ‘‘, ‘F’, ‘CompSci’, ‘TX’)
INTO student VALUES(‘10018’, ‘Johns’, ‘Roberta’, ‘‘, ‘F’, ‘CompSci’, ‘TX’)
INTO student VALUES(‘10019’, ‘Wier’, ‘Paul’, ‘‘, ‘M’, ‘English’, ‘TX’)
SELECT * FROM dual';
BEGIN
EXECUTE IMMEDIATE create_student;
EXECUTE IMMEDIATE create_course;
EXECUTE IMMEDIATE create_student_course;
EXECUTE IMMEDIATE insert_student;
END;
/SQL开发人员给我这份错误报告：
Error report -
ORA-00955: name is already used by an existing object
ORA-06512: at line 17
00955. 00000 - "name is already used by an existing object"
*Cause:
*Action:任何帮助都将不胜感激。我在这方面已经有一段时间了，即使是在甲骨文这本书上，我似乎也不能理解它。
回答 3
Stack Overflow用户
发布于 2021-02-27 02:12:17
1-这意味着您已经有了这些表
2-为什么要在这里使用动态sql？只需使用常规dml即可
3-您可以使用以下命令包装每个执行
begin
execute immediate ...;
exception when others then
null;
end;4-也许您应该在外部创建表，然后运行
truncate table xxxxx;不是创建表，而是在插入...
Stack Overflow用户
发布于 2021-02-27 02:57:13
我想通了。是注释掉表create executes和使用q'‘表示字符串文字的组合。
Stack Overflow用户
发布于 2021-02-27 03:03:55
您需要在insert语句中使用单引号(‘)，而不是撇号。用"select * from dual“代替"select * from dual”，用“select 1FROM dual”和last for多行sql语句(insert语句)代替'‘你可以使用(q'[]')。请尝试下面的查询。起作用了。
DECLARE
create_student VARCHAR2(500) := 'CREATE TABLE student(stu_id CHAR(5),Lname VARCHAR2(10) NOT NULL,Fname VARCHAR2(10) NOT NULL,Mi VARCHAR(2),Sex CHAR(1),Major VARCHAR(10),Home_State VARCHAR(4),CONSTRAINT stu_id_pk PRIMARY KEY (stu_id))';
create_course VARCHAR(500) := 'CREATE TABLE course(course_id VARCHAR(8),section CHAR(3)NOT NULL,c_name VARCHAR(30)NOT NULL,c_description VARCHAR(50),CONSTRAINT course_id_pk PRIMARY KEY (course_id))';
create_student_course VARCHAR(300) := 'CREATE TABLE student_course(stu_id CHAR(5),course_id VARCHAR(8),section CHAR(3),CONSTRAINT ck_stuid_courseid PRIMARY KEY (stu_id, course_id))';
insert_student VARCHAR2(1200) := q'[INSERT ALL
INTO student VALUES('10011', 'Smith', 'Peter', 'M', 'M', 'Math', 'TX')
INTO student VALUES('10012', 'Jones', 'Sam', 'A', 'M', 'English', 'TX')
INTO student VALUES('10013', 'Peters', 'Amy', 'A', 'F', 'English', 'ME')
INTO student VALUES('10014', 'Johnson', 'John', 'J', 'M', 'CompSci', 'CA')
INTO student VALUES('10015', 'Penders', 'Alton', 'P', 'F', 'Math', 'GA')
INTO student VALUES('10016', 'Allen', 'Diane', 'J', 'F', 'Geography', 'Minn')
INTO student VALUES('10017', 'Gill', 'Jennifer', '', 'F', 'CompSci', 'TX')
INTO student VALUES('10018', 'Johns', 'Roberta', '', 'F', 'CompSci', 'TX')
INTO student VALUES('10019', 'Wier', 'Paul', '', 'M', 'English', 'TX')
SELECT 1 FROM dual]';
begin
EXECUTE IMMEDIATE create_student;
EXECUTE IMMEDIATE create_student_course;
EXECUTE IMMEDIATE insert_student;
end;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/66390761
复制相关文章
相似问题
腾讯云开发者
