1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
|
SQL> spool 1
SQL> set linesize 100
SQL> set pagesize 30
SQL> select ename,sal
2 from emp;
ENAME SAL
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
BLAKE 2850
CLARK 2450
KING 5000
TURNER 1500
JAMES 950
FORD 3000
MILLER 1300
已選取 12 個資料列.
SQL> save 1
已建立 file 1.sql
SQL> edit 1
SQL> @1
ENAME SAL
KING 5000
FORD 3000
JONES 2975
BLAKE 2850
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
MARTIN 1250
WARD 1250
JAMES 950
SMITH 800
已選取 12 個資料列.
SQL> define school='Takming'
SQL> accept course prompt 'Enter your course:'
Enter your course:Oracle database
SQL> define
DEFINE _DATE = "26-9月 -23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1201000200" (CHAR)
DEFINE _RC = "0" (CHAR)
DEFINE SCHOOL = "Takming" (CHAR)
DEFINE COURSE = "Oracle database" (CHAR)
SQL> undefine school
SQL> define
DEFINE _DATE = "26-9月 -23" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER = "SCOTT" (CHAR)
DEFINE _PRIVILEGE = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1201000200" (CHAR)
DEFINE _EDITOR = "Notepad" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE = "1201000200" (CHAR)
DEFINE _RC = "0" (CHAR)
DEFINE COURSE = "Oracle database" (CHAR)
SQL> col job heading 'Pos'
SQL> col col format a3
SQL> set wrap off
SQL> desc emp
名稱 空值? 類型
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> col ename heading '姓名'
SQL> col sal heading '薪水' format '$999,999'
SQL> break on job skip 2
SQL> compute avg of sal on job
SQL> select job,ename,sal
2 from emp
3 order by job;
Pos 姓名 薪水
ANALYST FORD $3,000
*********
avg $3,000
CLERK MILLER $1,300
JAMES $950
SMITH $800
*********
avg $1,017
MANAGER JONES $2,975
BLAKE $2,850
CLARK $2,450
*********
avg $2,758
PRESIDENT KING $5,000
*********
avg $5,000
SALESMAN TURNER $1,500
WARD $1,250
ALLEN $1,600
Pos 姓名 薪水
SALESMAN MARTIN $1,250
*********
avg $1,400
已選取 12 個資料列.
SQL> col job format a3
SQL> /
Pos 姓名 薪水
ANA FORD $3,000
***
avg $3,000
CLE MILLER $1,300
JAMES $950
SMITH $800
***
avg $1,017
MAN JONES $2,975
BLAKE $2,850
CLARK $2,450
***
avg $2,758
PRE KING $5,000
***
avg $5,000
SAL TURNER $1,500
WARD $1,250
ALLEN $1,600
Pos 姓名 薪水
SAL MARTIN $1,250
***
avg $1,400
已選取 12 個資料列.
SQL> spool off
|
