SQL*PLUS脚本运行问题。

Rewiah 2001-12-06 03:48:56
ORACLE 8I for windows NT,刚安装,看书《ORACLE 8I for windows NT实用指南》,有编好的脚本,用SQL*PLUS运行,出现
Press RETURN to continue or CTRL-C to abort.
后不在反应,按回车没用,再执行任何语句都出
SQL*PLUS已存在的对话框。



脚本如下:

SET ECHO OFF;

CLEAR SCREEN;

DEFINE script = CHAP_04.SQL
DEFINE title = "Basic Database Access with SQL"
DEFINE chapter = 4
DEFINE schema = PRACTICE04
DEFINE default_tablespace = "USERS"
DEFINE temp_tablespace = "TEMP"
------------------------------------------------------------------------
-- COPYRIGHT
-- This script is the property of Animated Learning, Inc. (c) 1999

PROMPT |
PROMPT |
PROMPT |
PROMPT |
PROMPT |
PROMPT |
PROMPT |
PROMPT |
PROMPT | Oracle8i for Windows NT Starter Kit
PROMPT |
PROMPT | by Steve Bobrowski
PROMPT | Oracle Press 1999
PROMPT |
PROMPT |
PROMPT |
PROMPT |
PROMPT |
PROMPT |
PAUSE | Press RETURN to continue or CTRL-C to abort.

CLEAR SCREEN;

PROMPT WHAT DOES THIS SCRIPT DO?
PROMPT
PROMPT This script builds the schema objects necessary to complete the
PROMPT practice excercises in the following chapter of the book
PROMPT "Oracle8i Windows NT Starter Kit":
PROMPT Chapter &chapter : &title
PROMPT
PAUSE Press RETURN to continue or CTRL-C to abort.

CLEAR SCREEN;

PROMPT PREREQUISITES
PROMPT
PROMPT You must use the SYSTEM account to run this script, or modify the
PROMPT script to use another administration account capable of creating
PROMPT a user and granting the CONNECT and RESOURCE roles to the user.
PROMPT
PROMPT Press RETURN if you know the password for the SYSTEM account
PAUSE and would like to continue, or CTRL-C to abort.
ACCEPT system_password PROMPT 'Please enter the password for the SYSTEM account: '

CLEAR SCREEN;

PROMPT WARNINGS
PROMPT
PROMPT This script recreates the user account &schema and all of the
PROMPT schema objects owned by the account.
PROMPT
PAUSE Press RETURN to continue or CTRL-C to abort.

CONNECT system/&system_password;

CLEAR SCREEN;

PROMPT Now building objects ...

DROP USER &schema CASCADE;

WHENEVER SQLERROR EXIT;

CREATE USER &schema
IDENTIFIED BY password
DEFAULT TABLESPACE &&default_tablespace
TEMPORARY TABLESPACE &&temp_tablespace
QUOTA 200K ON &&default_tablespace;

GRANT CONNECT, RESOURCE TO &schema;

CONNECT &schema/password;

CREATE TABLE ITEMS
(O_ID NUMBER(38)
,ID NUMBER(38)
,P_ID NUMBER(38) NOT NULL
,QUANTITY NUMBER(38) DEFAULT 1 NOT NULL
);

CREATE TABLE PARTS
(ID NUMBER(38)
,DESCRIPTION VARCHAR2(250) NOT NULL
,UNITPRICE NUMBER NOT NULL
,ONHAND NUMBER(38) NOT NULL
,REORDER VARCHAR2(40) NOT NULL
);

CREATE TABLE CUSTOMERS
(ID NUMBER(38)
,LASTNAME VARCHAR2(100) CONSTRAINT LASTNAME NOT NULL
,FIRSTNAME VARCHAR2(50) CONSTRAINT FIRSTNAME NOT NULL
,COMPANYNAME VARCHAR2(100)
,STREET VARCHAR2(100)
,CITY VARCHAR2(100)
,STATE VARCHAR2(50)
,ZIPCODE VARCHAR2(50)
,PHONE VARCHAR2(30)
,FAX VARCHAR2(30)
,EMAIL VARCHAR2(100)
,S_ID NUMBER(38) CONSTRAINT SALESREP NOT NULL
);

CREATE TABLE SALESREPS
(ID NUMBER(38)
,LASTNAME VARCHAR2(100) NOT NULL
,FIRSTNAME VARCHAR2(50) NOT NULL
,COMMISSION NUMBER(38) NOT NULL
);

CREATE TABLE ORDERS
(ID NUMBER(38)
,C_ID NUMBER(38) NOT NULL
,ORDERDATE DATE DEFAULT SYSDATE NOT NULL
,SHIPDATE DATE
,PAIDDATE DATE
,STATUS CHAR(1) DEFAULT 'F'
);

ALTER TABLE ITEMS
ADD CONSTRAINT O_I_ID PRIMARY KEY
(O_ID, ID) ;

ALTER TABLE PARTS
ADD CONSTRAINT P_ID PRIMARY KEY
(ID) ;

ALTER TABLE CUSTOMERS
ADD CONSTRAINT C_ID PRIMARY KEY
(ID) ;

ALTER TABLE SALESREPS
ADD CONSTRAINT S_ID PRIMARY KEY
(ID) ;

ALTER TABLE ORDERS
ADD CONSTRAINT O_ID PRIMARY KEY
(ID) ;

ALTER TABLE PARTS
ADD CONSTRAINT PAR_DESCRIPTION UNIQUE
(DESCRIPTION) ;

ALTER TABLE CUSTOMERS
ADD CONSTRAINT CUS_NAME UNIQUE
(LASTNAME
,FIRSTNAME) ;

ALTER TABLE ITEMS
ADD CONSTRAINT PARTS_FK
FOREIGN KEY
(P_ID)
REFERENCES PARTS
(ID)
;

ALTER TABLE ITEMS
ADD CONSTRAINT ORDERS_FK
FOREIGN KEY
(O_ID)
REFERENCES ORDERS
(ID)
;

ALTER TABLE CUSTOMERS
ADD CONSTRAINT SALESREPS_FK
FOREIGN KEY
(S_ID)
REFERENCES SALESREPS
(ID)
;

ALTER TABLE ORDERS
ADD CONSTRAINT CUSTOMERS_FK
FOREIGN KEY
(C_ID)
REFERENCES CUSTOMERS
(ID)
;

-- PARTS

INSERT INTO parts
VALUES (1,'Fax Machine',299,277,50);
INSERT INTO parts
VALUES (2,'Copy Machine',4895,143,25);
INSERT INTO parts
VALUES (3,'Laptop PC',2100,7631,1000);
INSERT INTO parts
VALUES (4,'Desktop PC',1200,5903,1000);
INSERT INTO parts
VALUES (5,'Scanner',99,490,200);
COMMIT;

-- SALESREPS
INSERT INTO salesreps
VALUES (1,'Pratt','Nick',5);
INSERT INTO salesreps
VALUES (2,'Jonah','Suzanne',5);
INSERT INTO salesreps
VALUES (3,'Greenberg','Bara',5);
COMMIT;

-- CUSTOMERS
INSERT INTO customers
VALUES (1,'Joy','Harold','McDonald Co.','4458 Stafford St.','Baltimore','MD','21209','410-983-5789',NULL,'harold_joy@mcdonald.com',3);
INSERT INTO customers
VALUES (2,'Musial','Bill','Car Audio Center','12 Donna Lane','Reno','NV','89501','775-859-2121','775-859-2121','musial@car-audio.net',1);
INSERT INTO customers
VALUES (3,'Sams','Danielle','Wise Trucking','489 Main St., #11','Hartford','CT','06103','203-955-4263','203-955-9532','danielle@wise.com',1);
INSERT INTO customers
VALUES (4,'Elias','Juan','Rose Garden Inn','55 Condor Dr.','Dallas','TX','75252','214-907-3344','214-907-3188','jelias@rosegardeninnn.com',2);
INSERT INTO customers
VALUES (5,'Foss','Betty','Foss Photography','446 Lincoln Ave.','Philadelphia','PA','19144','215-367-7746','215-543-9800','bfoss15@yahoo.com',3);
INSERT INTO customers
VALUES (6,'Schaub','Greg','Pampered Pets','716 Heritage Ave., #4A','Phoenix','AZ','85023','602-617-7321','602-617-7321','pamperedpets@msn.com',3);
INSERT INTO customers
VALUES (7,'Wiersbicki','Joseph','Key Locksmith','122 83rd Ave.','Brooklyn','NY','11220','718-445-8452','718-445-8799','joe@keylocksmith.com',1);
INSERT INTO customers
VALUES (8,'Ayers','Jack','Park View Insurance','2 Curtis Lane','Topeka','KS','66604','785-707-4120',NULL,'jayers@parkview.com',3);
INSERT INTO customers
VALUES (9,'Clay','Dorothy','Kenser Corp.','57623 A St.','Sacramento','CA','95821','916-672-8700','916-672-8753','dorothy.clay@kenser.com',1);
INSERT INTO customers
VALUES (10,'Haagensen','Dave','Dave''s Tree Service','874 Lafayette Rd.','Cleveland','OH','44124','216-578-2347',NULL,'chopchop@excite.com',1);
COMMIT;

-- ORDERS and ITEMS
INSERT INTO orders
VALUES (1,1,'18-JUN-99','18-JUN-99','30-JUN-99','F');
INSERT INTO items
VALUES (1,1,3,1);
INSERT INTO items
VALUES (1,2,2,1);
INSERT INTO items
VALUES (1,3,5,1);
COMMIT;

INSERT INTO orders
VALUES (2,2,'18-JUN-99',NULL,NULL,'B');
INSERT INTO items
VALUES (2,1,1,2);
INSERT INTO items
VALUES (2,2,4,2);
INSERT INTO items
VALUES (2,3,5,2);
COMMIT;

INSERT INTO orders
VALUES (3,3,'18-JUN-99','18-JUN-99','21-JUN-99','F');
INSERT INTO items
VALUES (3,1,3,1);
COMMIT;

INSERT INTO orders
VALUES (4,4,'19-JUN-99','21-JUN-99','21-JUN-99','F');
INSERT INTO items
VALUES (4,1,5,1);
COMMIT;

INSERT INTO orders
VALUES (5,5,'19-JUN-99','19-JUN-99','28-JUN-99','F');
INSERT INTO items
VALUES (5,1,2,1);
INSERT INTO items
VALUES (5,2,3,1);
COMMIT;

INSERT INTO orders
VALUES (6,6,'19-JUN-99','19-JUN-99',NULL,'F');
INSERT INTO items
VALUES (6,1,4,1);
COMMIT;

INSERT INTO orders
VALUES (7,7,'19-JUN-99',NULL,NULL,'B');
INSERT INTO items
VALUES (7,1,1,1);
COMMIT;

INSERT INTO orders
VALUES (8,8,'20-JUN-99','20-JUN-99','20-JUN-99','F');
INSERT INTO items
VALUES (8,1,5,10);
COMMIT;

INSERT INTO orders
VALUES (9,9,'21-JUN-99',NULL,NULL,'B');
INSERT INTO items
VALUES (9,1,1,2);
INSERT INTO items
VALUES (9,2,4,2);
INSERT INTO items
VALUES (9,3,5,2);
COMMIT;

INSERT INTO orders
VALUES (10,2,'21-JUN-99','22-JUN-99','22-JUN-99','F');
INSERT INTO items
VALUES (10,1,2,1);
INSERT INTO items
VALUES (10,2,3,1);
COMMIT;

INSERT INTO orders
VALUES (11,4,'22-JUN-99','22-JUN-99',NULL,'F');
INSERT INTO items
VALUES (11,1,3,2);
INSERT INTO items
VALUES (11,2,2,2);
INSERT INTO items
VALUES (11,3,5,2);
COMMIT;

INSERT INTO orders
VALUES (12,7,'22-JUN-99','23-JUN-99','30-JUN-99','F');
INSERT INTO items
VALUES (12,1,4,1);
COMMIT;

INSERT INTO orders
VALUES (13,4,'22-JUN-99',NULL,NULL,'B');
INSERT INTO items
VALUES (13,1,5,1);
COMMIT;

INSERT INTO orders
VALUES (14,1,'23-JUN-99','25-JUN-99',NULL,'F');
INSERT INTO items
VALUES (14,1,2,1);
COMMIT;

WHENEVER SQLERROR CONTINUE;

-- column formatting here
CLEAR COLUMNS;
COLUMN name FORMAT a20;
COLUMN status FORMAT a6;
COLUMN companyname FORMAT a30;
COLUMN lastname FORMAT a30;
COLUMN status FORMAT a15;
COLUMN zipcode FORMAT a7;
COLUMN description FORMAT a15;

CLEAR SCREEN

PROMPT Script successfully completed.
PROMPT
PAUSE Press RETURN to continue.

CLEAR SCREEN

PROMPT BEGIN PRACTICING NOW!
PROMPT
PROMPT You are now ready to begin practicing the excercises in
PROMPT Chapter &chapter : &title
PROMPT
PAUSE Press RETURN to continue.

CLEAR SCREEN;
...全文
181 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Rewiah 2001-12-06
  • 打赏
  • 举报
回复
谢谢各位,原来已经安装了sqlplus,以后有问题再请教!
Rewiah 2001-12-06
  • 打赏
  • 举报
回复
我用典型安装,没有SQLPLUS,我还以为SQLPLUS WORKSHEET就是SQLPLUS呢,问了才知道不一样!
rwq_ 2001-12-06
  • 打赏
  • 举报
回复
我也执行过这些程序,没有反应我没有遇过!你可以用SQLPLUS来试试先(不是SQLPLUS WORKSHEET)
Rewiah 2001-12-06
  • 打赏
  • 举报
回复
to: rwq_(风云浪子) 
谢谢你的回答!
你的意思是日期格式的问题?
但我主要是没有反应,这一章的例子我给分开搞了几十分钟给倒进去了,也有日期问题,我把日期都替换成'23-9月-99'的格式.
但我这本书还有好多章呀!

rwq_ 2001-12-06
  • 打赏
  • 举报
回复
我都不知道这种时间格式怎么设置,你安装的应该是中文版的吧!我已前不得不把所有的时间对应的字符加上TO_DATE('23-JUN-99','DD-MON-YY')
Rewiah 2001-12-06
  • 打赏
  • 举报
回复
TO: xinpingf(白开心) 
本来就是一个文件,贴到这里是由于我不知道怎么回事,不知道和脚本的内容有没有关系!
我在SQL*PLUS WORKSHEET的菜单"运行本地脚本"选这个文件运行,然后出好多提示,最后一行是
Press RETURN to continue or CTRL-C to abort.
但我怎么按回车都没反应.

还有,我的SQL*PLUS WORKSHEET没有
SQL>
提示符,也没有时候会变提示符.

帮我看看怎么回事,我用WIN2000中文版.
xinpingf 2001-12-06
  • 打赏
  • 举报
回复
你是怎么执行的????????
把这些东西写到一个文件里面,扩展名用.sql
然后打开sql*plus,随便用一个用户连上,然后用以下的方法执行
(假设你存的文件为c:\test.sql)

SQL> @C:\TEST.SQL
这样试一试,
最后问一下,你按完回车后,提示符是不是变了?

34,567

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧