oracle 未创建表或视图，但创建了其他表或视图？[已关闭]

yeotifhr  于 2023-03-22  发布在  Oracle
关注(0)|答案(1)|浏览(110)

**已关闭。**此问题为not reproducible or was caused by typos。当前不接受答案。

这个问题是由打字错误或无法再重现的问题引起的。虽然类似的问题在这里可能是on-topic，但这个问题的解决方式不太可能帮助未来的读者。
2天前关闭。
Improve this question
我需要创建4个表，以在数据为一所大学，事情是，而2的4个工作，其他人没有一个表插入数据，即使一切都写的一样。
这有什么意义呢？

SQL> spool c:\cprg250s\loadGHCOutput.txt
SQL>
SQL> -- Delete existing data, children followed by parent --
SQL>
SQL> delete from ghc_faculty;
delete from ghc_faculty
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> delete from ghc_department;

3 rows deleted.

SQL> delete from ghc_course;

8 rows deleted.

SQL> delete from ghc_expertise;
delete from ghc_expertise
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> -- insert parents followed by children --
SQL>
SQL> /*CREATE TABLE ghc_department (
SQL>     dept_no INTEGER,
SQL>     dept_name VARCHAR2(50),
SQL>     PRIMARY KEY (dept_no)
SQL> );*/
SQL>
SQL> insert into ghc_department (dept_no, dept_name) values (25, 'ICT');

1 row created.

SQL> insert into ghc_department (dept_no, dept_name) values (26, 'Business');

1 row created.

SQL> insert into ghc_department (dept_no, dept_name) values (100, 'Astrophysics');

1 row created.

SQL>
SQL>  /* CREATE TABLE ghc_faculty (
SQL>     faculty_id INTEGER PRIMARY KEY,
SQL>     surname VARCHAR(50) NOT NULL,
SQL>     firstname VARCHAR2(50) NOT NULL,
SQL>     date_hire DATE NOT NULL,
SQL>     date_fire DATE,
SQL>     is_active INTEGER NOT NULL
SQL>     dept_no INTEGER NOT NULL
SQL> ); */
SQL>
SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1294,  'Harris', 'Len', '15-Dec-19', null, 1, 25);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1294,  'Harris', 'Len', '15-Dec-19', null, 1, 25)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1244,  'Lock', 'Sara', '15-Dec-19', '1-May-20', 0, 25);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1244,  'Lock', 'Sara', '15-Dec-19', '1-May-20', 0, 25)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1948,  'Harris', 'Len', '15-Mar-21', null, 1, 26);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1948,  'Harris', 'Len', '15-Mar-21', null, 1, 26)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (7839,  'Harris', 'Len', '15-Aug-02', null, 1, 25);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (7839,  'Harris', 'Len', '15-Aug-02', null, 1, 25)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1001,  'Faulkner', 'Denny', '01-Jan-22', null, 1, 100);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1001,  'Faulkner', 'Denny', '01-Jan-22', null, 1, 100)
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> /* create table ghc_course {
SQL>     course_code varchar2(10) primary key,
SQL>     course_title varchar2(50),
SQL>     credits integer
SQL> } */
SQL>
SQL> insert into ghc_course (course_code, course_title, credits) values ('CMPS253', 'Interface Design', 3);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('CPRG250', 'Database Design and Programming', 5);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('CPRG251', 'Object Oriented Programming Essentials', 5);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('CPRG302', 'Web Essentials', 3);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('HREL250', 'Business Dynamics', 3);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('APH202', 'The Solar System', 5);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('APH203', 'Nebula', 5);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('APH204', 'Global Cluster', 5);

1 row created.

SQL>
SQL>
SQL> insert into ghc_expertise (faculty_id, course_code) values (1294, 'CMPS253');
insert into ghc_expertise (faculty_id, course_code) values (1294, 'CMPS253')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_expertise (faculty_id, course_code) values (1294, 'CPRG250');
insert into ghc_expertise (faculty_id, course_code) values (1294, 'CPRG250')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_expertise (faculty_id, course_code) values (1294, 'CPRG251');
insert into ghc_expertise (faculty_id, course_code) values (1294, 'CPRG251')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_expertise (faculty_id, course_code) values (1948, 'CMPS253');
insert into ghc_expertise (faculty_id, course_code) values (1948, 'CMPS253')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_expertise (faculty_id, course_code) values (1948, 'HREL250');
insert into ghc_expertise (faculty_id, course_code) values (1948, 'HREL250')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_expertise (faculty_id, course_code) values (7839, 'CPRG251');
insert into ghc_expertise (faculty_id, course_code) values (7839, 'CPRG251')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_expertise (faculty_id, course_code) values (7839, 'CPRG251');
insert into ghc_expertise (faculty_id, course_code) values (7839, 'CPRG251')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_expertise (faculty_id, course_code) values (1244, 'CMPS253');
insert into ghc_expertise (faculty_id, course_code) values (1244, 'CMPS253')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> insert into ghc_expertise (faculty_id, course_code )values (1244, 'CMPS253');
insert into ghc_expertise (faculty_id, course_code )values (1244, 'CMPS253')
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL>
SQL> select * from ghc_department;

   DEPT_NO DEPT_NAME
---------- --------------------
        25 ICT
        26 Business
       100 Astrophysics

SQL> select * from ghc_faculty;
select * from ghc_faculty
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from ghc_course;

COURSE_ COURSE_TITLE                                                    CREDITS
------- ------------------------------------------------------------ ----------
CMPS253 Interface Design                                                      3
CPRG250 Database Design and Programming                                       5
CPRG251 Object Oriented Programming Essentials                                5
CPRG302 Web Essentials                                                        3
HREL250 Business Dynamics                                                     3
APH202  The Solar System                                                      5
APH203  Nebula                                                                5
APH204  Global Cluster                                                        5

8 rows selected.

SQL> select * from ghc_expertise;
select * from ghc_expertise
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> commit;

Commit complete.

SQL> spool off

它应该已经打印了所有需要的数据。

set echo on

spool c:\cprg250s\loadGHCOutput.txt

-- Delete existing data, children followed by parent --

delete from ghc_faculty;
delete from ghc_department;
delete from ghc_course;
delete from ghc_expertise;

-- insert parents followed by children --

/*CREATE TABLE ghc_department (
    dept_no INTEGER,
    dept_name VARCHAR2(50),
    PRIMARY KEY (dept_no)
);*/

insert into ghc_department (dept_no, dept_name) values (25, 'ICT');
insert into ghc_department (dept_no, dept_name) values (26, 'Business');
insert into ghc_department (dept_no, dept_name) values (100, 'Astrophysics');

 /* CREATE TABLE ghc_faculty (
    faculty_id INTEGER PRIMARY KEY,
    surname VARCHAR(50) NOT NULL,
    firstname VARCHAR2(50) NOT NULL,
    date_hire DATE NOT NULL,
    date_fire DATE,
    is_active INTEGER NOT NULL
    dept_no INTEGER NOT NULL
); */

insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1294,  'Harris', 'Len', '15-Dec-19', null, 1, 25);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1244,  'Lock', 'Sara', '15-Dec-19', '1-May-20', 0, 25);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1948,  'Harris', 'Len', '15-Mar-21', null, 1, 26);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (7839,  'Harris', 'Len', '15-Aug-02', null, 1, 25);
insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1001,  'Faulkner', 'Denny', '01-Jan-22', null, 1, 100);

/* create table ghc_course {
    course_code varchar2(10) primary key,
    course_title varchar2(50),
    credits integer
} */

insert into ghc_course (course_code, course_title, credits) values ('CMPS253', 'Interface Design', 3);
insert into ghc_course (course_code, course_title, credits) values ('CPRG250', 'Database Design and Programming', 5);
insert into ghc_course (course_code, course_title, credits) values ('CPRG251', 'Object Oriented Programming Essentials', 5);
insert into ghc_course (course_code, course_title, credits) values ('CPRG302', 'Web Essentials', 3);
insert into ghc_course (course_code, course_title, credits) values ('HREL250', 'Business Dynamics', 3);
insert into ghc_course (course_code, course_title, credits) values ('APH202', 'The Solar System', 5);
insert into ghc_course (course_code, course_title, credits) values ('APH203', 'Nebula', 5);
insert into ghc_course (course_code, course_title, credits) values ('APH204', 'Global Cluster', 5);

insert into ghc_expertise (faculty_id, course_code) values (1294, 'CMPS253');
insert into ghc_expertise (faculty_id, course_code) values (1294, 'CPRG250');
insert into ghc_expertise (faculty_id, course_code) values (1294, 'CPRG251');
insert into ghc_expertise (faculty_id, course_code) values (1948, 'CMPS253');
insert into ghc_expertise (faculty_id, course_code) values (1948, 'HREL250');
insert into ghc_expertise (faculty_id, course_code) values (7839, 'CPRG251');
insert into ghc_expertise (faculty_id, course_code) values (7839, 'CPRG251');
insert into ghc_expertise (faculty_id, course_code) values (1244, 'CMPS253');
insert into ghc_expertise (faculty_id, course_code )values (1244, 'CMPS253');

select * from ghc_department;
select * from ghc_faculty;
select * from ghc_course;
select * from ghc_expertise;

commit;
spool off
oracle

1条答案

按热度按时间
zsohkypk

zsohkypk1#

相当多的问题。阅读代码中的注解。

  • 我没有你的表，所以我取消了所有CREATE TABLE语句的注解，并添加了一个缺失的语句
  • 缺逗号
  • 错括号
  • 缺分号
  • 使用DATE数据类型时的方法错误;不要传递字符串，但日期文字或使用to_date函数与适当的格式模型。或者，我改变了会话，使您的字符串将被插入到date数据类型列。没有alter，所有这些插入在我的数据库中失败（和任何其他不匹配您的设置）
SQL> -- Delete existing data, children followed by parent --
SQL> drop table ghc_faculty;

Table dropped.

SQL> drop table ghc_department;

Table dropped.

SQL> drop table ghc_course;

Table dropped.

SQL> drop table ghc_expertise;

Table dropped.

SQL>
SQL> delete from ghc_faculty;
delete from ghc_faculty
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> delete from ghc_department;
delete from ghc_department
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> delete from ghc_course;
delete from ghc_course
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> delete from ghc_expertise;
delete from ghc_expertise
            *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
SQL> -- insert parents followed by children --
SQL>
SQL> CREATE TABLE ghc_department (
  2      dept_no INTEGER,
  3      dept_name VARCHAR2(50),
  4      PRIMARY KEY (dept_no)
  5  );

Table created.

SQL>
SQL> insert into ghc_department (dept_no, dept_name) values (25, 'ICT');

1 row created.

SQL> insert into ghc_department (dept_no, dept_name) values (26, 'Business');

1 row created.

SQL> insert into ghc_department (dept_no, dept_name) values (100, 'Astrophysics');

1 row created.

SQL>
SQL>  CREATE TABLE ghc_faculty (
  2      faculty_id INTEGER PRIMARY KEY,
  3      surname VARCHAR(50) NOT NULL,
  4      firstname VARCHAR2(50) NOT NULL,
  5      date_hire DATE NOT NULL,
  6      date_fire DATE,
  7      is_active INTEGER NOT NULL,   --> missing comma
  8      dept_no INTEGER NOT NULL
  9  );

Table created.

SQL> -- alter session - otherwise, strings you're passing as dates might not be inserted
SQL> alter session set nls_date_format = 'dd-mon-yy';

Session altered.

SQL> alter session set nls_date_language = 'english';

Session altered.

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1294,  'Harris', 'Len', '15-Dec-19', null, 1, 25);

1 row created.

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1244,  'Lock', 'Sara', '15-Dec-19', '1-May-20', 0, 25);

1 row created.

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1948,  'Harris', 'Len', '15-Mar-21', null, 1, 26);

1 row created.

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (7839,  'Harris', 'Len', '15-Aug-02', null, 1, 25);

1 row created.

SQL> insert into ghc_faculty (faculty_id, surname, firstname, date_hire, date_fire, is_active, dept_no) values (1001,  'Faulkner', 'Denny', '01-Jan-22', null, 1, 100);

1 row created.

SQL>
SQL>  create table ghc_course ( -- {    --> wrong parenthesis
  2      course_code varchar2(10) primary key,
  3      course_title varchar2(50),
  4      credits integer
  5      --};          --> wrong parenthesis, missing semi-colon
  6  );

Table created.

SQL>
SQL> insert into ghc_course (course_code, course_title, credits) values ('CMPS253', 'Interface Design', 3);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('CPRG250', 'Database Design and Programming', 5);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('CPRG251', 'Object Oriented Programming Essentials', 5);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('CPRG302', 'Web Essentials', 3);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('HREL250', 'Business Dynamics', 3);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('APH202', 'The Solar System', 5);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('APH203', 'Nebula', 5);

1 row created.

SQL> insert into ghc_course (course_code, course_title, credits) values ('APH204', 'Global Cluster', 5);

1 row created.

SQL>
SQL> -- missing CREATE TABLE
SQL> create table ghc_expertise (faculty_id number, course_code varchar2(20));

Table created.

SQL>
SQL> insert into ghc_expertise (faculty_id, course_code) values (1294, 'CMPS253');

1 row created.

SQL> insert into ghc_expertise (faculty_id, course_code) values (1294, 'CPRG250');

1 row created.

SQL> insert into ghc_expertise (faculty_id, course_code) values (1294, 'CPRG251');

1 row created.

SQL> insert into ghc_expertise (faculty_id, course_code) values (1948, 'CMPS253');

1 row created.

SQL> insert into ghc_expertise (faculty_id, course_code) values (1948, 'HREL250');

1 row created.

SQL> insert into ghc_expertise (faculty_id, course_code) values (7839, 'CPRG251');

1 row created.

SQL> insert into ghc_expertise (faculty_id, course_code) values (7839, 'CPRG251');

1 row created.

SQL> insert into ghc_expertise (faculty_id, course_code) values (1244, 'CMPS253');

1 row created.

SQL> insert into ghc_expertise (faculty_id, course_code )values (1244, 'CMPS253');

1 row created.

SQL>
SQL>
SQL> select * from ghc_department;

   DEPT_NO DEPT_NAME
---------- --------------------------------------------------
        25 ICT
        26 Business
       100 Astrophysics

SQL> select * from ghc_faculty;

FACULTY_ID SURNAME         FIRSTNAME       DATE_HIRE DATE_FIRE  IS_ACTIVE    DEPT_NO
---------- --------------- --------------- --------- --------- ---------- ----------
      1294 Harris          Len             15-dec-19                    1         25
      1244 Lock            Sara            15-dec-19 01-may-20          0         25
      1948 Harris          Len             15-mar-21                    1         26
      7839 Harris          Len             15-aug-02                    1         25
      1001 Faulkner        Denny           01-jan-22                    1        100

SQL> select * from ghc_course;

COURSE_COD COURSE_TITLE                                          CREDITS
---------- -------------------------------------------------- ----------
CMPS253    Interface Design                                            3
CPRG250    Database Design and Programming                             5
CPRG251    Object Oriented Programming Essentials                      5
CPRG302    Web Essentials                                              3
HREL250    Business Dynamics                                           3
APH202     The Solar System                                            5
APH203     Nebula                                                      5
APH204     Global Cluster                                              5

8 rows selected.

SQL> select * from ghc_expertise;

FACULTY_ID COURSE_CODE
---------- --------------------
      1294 CMPS253
      1294 CPRG250
      1294 CPRG251
      1948 CMPS253
      1948 HREL250
      7839 CPRG251
      7839 CPRG251
      1244 CMPS253
      1244 CMPS253

9 rows selected.

SQL>
SQL>
赞(0）分享  回复(0）举报  2023-03-22
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com