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 六、实验内容和步骤1SQL*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 /如：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语句块，如果一行输不完可分多行输入，只要语句中每个单词不被分行即可。以下输入完全等价：SQLSelect * from dept;SQLSelect *2 from dept;(2)输入SQL*Plus命令SQL*Plus有自己的命令，可在数据库上执行特定的任务或格式化查询结果。SQL*Plus命令一般在一行中输完，按回车后立即执行，SQL*Plus命令不存储在缓冲区中。若想把一条SQL*Plus命令延续到下一行，必须在断行处加一连字符（-）。如：SQLTTITLE SKIP 3-CENTER “First line of title aligned center”(3)定制SQL*Plus环境l 关闭/打开屏幕反馈信息SET FEEDBACK OFFSET FEEDBACK ON注意观察设置前后查询结果的区别，见图2-6。图2-6l 设置SELECT检索结果集合中空值的替换值（为一字符串）如：SET NULL l 设置数字值的默认显示格式如：SET NUMF $9999l 设置数字型值的默认显示宽度如：SET NUMW 8设置后的查询结果见图2-7。图2-7(4)输出格式控制 列格式控制l COLUMN命令COLUMN ename HEADING Employee|Name FORMAT A15COLUMN sal JUSTIFY LEFT FORMAT $99,990.00COLUMN mgr FORMAT 999999999 NULL No manager设置后的查询结果见图2-8。图2-8 报表数据格式控制l BREAK命令l COMPUTE命令以下操作计算各个部门职员的总薪金，结果见图2-9。SQLBREAK ON deptno SKIP 1SQLCOMPUTE SUM LABEL Dept Total OF sal ON deptnoSQLselect deptno,ename,sal2 from employee3 order by deptno,ename;图2-9 报表格式控制l 设置页大小SQLSET NEWPAGE 1SQLSET PAGESIZE 24SQLSET LINESIZE 80l 设置页眉、页脚SQL TTITLE Salary|ReportSQL BTITLE Confidentiall 设置标题、尾注SQL REPHEADER Salary ReportSQL REPFOOTER Confidential对照图2-10的执行结果，检查自己是否读懂了以下SQL脚本。REM MyFirstReport.sqlREM TO PRACTICE REPORT FORMATTINGSET ECHO OFFSET FEEDBACK OFF DOCUMENT OFFSET 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 0999COLUMN emp_name HEADING Employee Name FORMAT A20COLUMN job_id HEADING PositionCOLUMN manager_id LIKE employee_id HEADING Mgr|IdCOLUMN salary FORMAT $9,999 HEADING SalaryTTITLE CENTER Employee Information SKIP 1- CENTER = SKIP 1 LEFT Dept 50- RIGHT PAGE: SQL.PNO SKIP 2BREAK ON job_id SKIP 2 NODUPLICATES-Two hyphens can also be used to specify a comment.-The querySELECT job_id,employee_id,first_name | | last_name emp_name,salary,manager_idFROM employeesWHERE department_id=50ORDER BY job_id,emp_name/REM Clear customizationsREMCLEAR COLUMNSCLEAR BREAKSTTITLE OFFSET PAGES 55 LINES 120 SET FEEDBACK ON ECHO ON DOCUMENT ON图2-10七、练习1Write 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 RESEARCH2Modify p2q1.sql to create a report containing the department name, employee name, hire date, salary, and each employees 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: ChicagoDEPARTMENT EMPLOYEE START ANNUALNAME NAME DATE SALARY SALARY- - - - -SALES BLAKE 01-MAY-81 $2,850.00 $34,200.00MARTIN 28-SEP-81 $1,250.00 $15,000.00ALLEN 20-FEB-81 $1,600.00 $19,200.00TURNER 08-SEP-81 $1,500.00 $18,000.00JAMES 03-DEC-81 $950.00 $11,400.00WARD 22-FEB-81 $1,250.00 $15,000.009