Oracle数据库A实验指导书(二).doc

“Oracle数据库A”实验指导书（二）

一、实验课程编码：111009
二、实验课程名称：Oracle数据库A
三、实验项目名称：实验二 SQL*PLUS的使用
四、实验目的
1.熟悉SQL*PLUS操作环境
2.了解SQL*PLUS命令与SQL语句的区别
3.掌握常用的SQL Plus命令
4.学会定制SQL PLUS报表
五、主要设备Oracle Universal Installer
计算机硬件：Pentium（R）4 CPU 2.80GHz，RAM 1.00GB
计算机软件：Windows 2000/XP，Oracle 9i
六、实验内容和步骤
1．SQL*Plus的启动与关闭
SQL*Plus是Oracle数据库管理员和普通用户最常用的实用程序之一,它提供一个交互式SQL语句、PL/SQL语句块和SQL*Plus命令的编辑、编译和执行环境。
(1)基于DOS方式的启动与关闭
①单击“开始”—>“运行”，在对话框中键入cmd，进入DOS控制台后在提示符下键入sqlplus /nolog（nolog 表示只启动SQL*Plus程序，而不建立与具体Oracle数据库的连接。）,出现如图2-1画面。

图2-1
②下面命令使用scott帐户（口令为tiger）连接到本地数据库，结果如图2-2所示。
SQL> connect scott/tiger
以上两个步骤也可合并为: sqlplus scott/tiger

图2-2

③在SQL>提示符下即可输入SQL PLUS命令或SQL语句，如：Select * from emp;
注意：;是语句的结束标志
④执行完毕后，输入quit或exit命令返回DOS控制台。如图2-3所示。

图2-3

(2)基于Windows方式的启动与关闭
①单击“开始”—>“程序”—>“Oracle-OraHome92”—>“Application Development”—>“SQL Plus”，出现如图2-4对话框。
主机字符串或称连接字符串就是数据库的别名。若省略，SQL*Plus会试图连接到在变量ORACLE_SID中定义的本地数据库上。

图2-4

②成功连接数据库后出现图2-5界面，在菜单栏里有一些常见的菜单命令可帮助执行并保存SQL查询语句。

图2-5

③输入查询语句select * from emp;
④可使用disconnect命令中断与数据库的会话连接;如再次连接，可使用connect命令。
CONNECT <username>/<password>
如：conn scott/tiger
⑤可用show user命令显示当前帐户，用password命令更改当前用户口令。
⑥可用quit或exit返回到Windows状态

2. SQL*Plus的使用
在SQL*Plus提示符（SQL>）下，用户即可输入和编辑SQL语句、PL/SQL语句块或SQL*Plus命令。
(1)输入SQL语句
对于SQL语句和PL/SQL语句块，如果一行输不完可分多行输入，只要语句中每个单词不被分行即可。以下输入完全等价：
SQL>Select * from dept;
SQL>Select *
2 from dept;
(2)输入SQL*Plus命令
SQL*Plus有自己的命令，可在数据库上执行特定的任务或格式化查询结果。SQL*Plus命令一般在一行中输完，按回车后立即执行，SQL*Plus命令不存储在缓冲区中。若想把一条SQL*Plus命令延续到下一行，必须在断行处加一连字符（-）。如：
SQL>TTITLE SKIP 3-
>CENTER “First line of title aligned center”
(3)定制SQL*Plus环境
l 关闭/打开屏幕反馈信息
SET FEEDBACK OFF
SET FEEDBACK ON
注意观察设置前后查询结果的区别，见图2-6。

图2-6

l 设置SELECT检索结果集合中空值的替换值（为一字符串）
如：SET NULL ‘<未知>’
l 设置数字值的默认显示格式
如：SET NUMF $9999
l 设置数字型值的默认显示宽度
如：SET NUMW 8
设置后的查询结果见图2-7。

图2-7
(4)输出格式控制
① 列格式控制
l COLUMN命令
COLUMN ename HEADING 'Employee|Name' FORMAT A15
COLUMN sal JUSTIFY LEFT FORMAT $99,990.00
COLUMN mgr FORMAT 999999999 NULL 'No manager'
设置后的查询结果见图2-8。

图2-8

② 报表数据格式控制
l BREAK命令
l COMPUTE命令
以下操作计算各个部门职员的总薪金，结果见图2-9。
SQL>BREAK ON deptno SKIP 1
SQL>COMPUTE SUM LABEL "Dept Total" OF sal ON deptno
SQL>select deptno,ename,sal
2 from employee
3 order by deptno,ename;

图2-9

③ 报表格式控制
l 设置页大小
SQL>SET NEWPAGE 1
SQL>SET PAGESIZE 24
SQL>SET LINESIZE 80
l 设置页眉、页脚
SQL> TTITLE 'Salary|Report'
SQL> BTITLE 'Confidential'
l 设置标题、尾注
SQL> REPHEADER 'Salary Report'
SQL> REPFOOTER 'Confidential'
对照图2-10的执行结果，检查自己是否读懂了以下SQL脚本。
REM MyFirstReport.sql
REM TO PRACTICE REPORT FORMATTING
SET ECHO OFF
SET FEEDBACK OFF DOCUMENT OFF
SET PAGES 55 LINES 54
REM SET UNDERLINE =
/*
This is an example of multiple-line comments.
Following lines are column-formatting commands.
*/
COLUMN employee_id HEADING "Emp|Id" FORMAT 0999
COLUMN emp_name HEADING "Employee Name" FORMAT A20
COLUMN job_id HEADING "Position"
COLUMN manager_id LIKE employee_id HEADING "Mgr|Id"
COLUMN salary FORMAT "$9,999" HEADING "Salary"
TTITLE CENTER "Employee Information" SKIP 1-
CENTER ==================== SKIP 1 LEFT "Dept 50"-
RIGHT 'PAGE: ' SQL.PNO SKIP 2

BREAK ON job_id SKIP 2 NODUPLICATES
--
--Two hyphens can also be used to specify a comment.
--The query
SELECT job_id,employee_id,first_name || ' ' ||
last_name emp_name,salary,manager_id
FROM employees
WHERE department_id=50
ORDER BY job_id,emp_name
/
REM Clear customizations
REM
CLEAR COLUMNS
CLEAR BREAKS
TTITLE OFF
SET PAGES 55 LINES 120
SET FEEDBACK ON ECHO ON DOCUMENT ON

图2-10

七、练习
1．Write a script to display the employee name, job, and department name for a given location. The search condition should allow for case-insensitive searches of the department location. Save the script file as p2q1.sql.
Please enter the location name: Dallas
EMPLOYEE NAME JOB DEPARTMENT NAME
------------- ------------ --------------
JONES MANAGER RESEARCH
FORD ANALYST RESEARCH
SMITH CLERK RESEARCH
SCOTT ANALYST RESEARCH
ADAMS CLERK RESEARCH

2．Modify p2q1.sql to create a report containing the department name, employee name, hire date, salary, and each employee’s annual salary for all employees in a given location. Prompt the user for the location. Label the columns DEPARTMENT NAME, EMPLOYEE NAME, START DATE, SALARY, and ANNUAL SALARY, placing the labels on multiple lines. Resave the script as p2q2.sql.

Please enter the location name: Chicago
DEPARTMENT EMPLOYEE START ANNUAL
NAME NAME DATE SALARY SALARY
---------- ----------- --------- --------- ----------
SALES BLAKE 01-MAY-81 $2,850.00 $34,200.00
MARTIN 28-SEP-81 $1,250.00 $15,000.00
ALLEN 20-FEB-81 $1,600.00 $19,200.00
TURNER 08-SEP-81 $1,500.00 $18,000.00
JAMES 03-DEC-81 $950.00 $11,400.00
WARD 22-FEB-81 $1,250.00 $15,000.00

9