orcle题目Word下载.docx
《orcle题目Word下载.docx》由会员分享，可在线阅读，更多相关《orcle题目Word下载.docx（15页珍藏版）》请在冰豆网上搜索。
3、查看系统拥有哪些用户
select*fromall_users;
4、新建用户并授权
createuseraidentifiedbya;
（默认建在SYSTEM表空间下）
grantconnect,resourcetoa;
5、连接到新用户
conna/a
6、查询当前用户下所有对象
select*fromtab;
7、建立第一个表
createtablea（anumber）;
8、查询表结构
desca
9、插入新记录
insertintoavalues
（1）;
10、查询记录
select*froma;
11、更改记录
updateaseta=2;
12、删除记录
deletefroma;
Oracle上机练习
（二）
一、进入自己创建的用户下进行以下操作。
1、创建表STUDENT、COURSE、GRADE，各个表的结构如下：
（1）STUDENT
学号NUMBER（6）,
姓名VARCHAR2（12）
入学时间DATE,
专业VARCHAR2（20）,
性别CHAR
（2）,
年龄INTEGER
（2）COURSE
课程号NUMBER（6）,
课程名称VARCHAR2（20）,
学时INTEGER,
学分INTEGER
（3）GRADE
学号NUMBER（6）,
课程号NUMBER（6）,
成绩NUMBER
（2）
2、向上面的三个表中分别插入5条纪录。
示例：
（1）INSERTINTOSTUDENT（学号，姓名，入学时间，专业，性别，年龄）
VALUES（100001,’HUANGWEI’,TO_DATE（‘1999-09-10’,’YYYY-MM-DD’）,’COMPUTER’,’男’,23）;
（2）INSERTINTOCOURSE（课程号,课程名称,学时,学分）
VALUES（000001,’多媒体’,32,4）;
（3）INSERTINTOGRADE（学号，课程号,成绩）
VALUES（100001,000001,90）
二、练习使用SQL*PLUS的一些编辑和执行命令
1、RUN简写R或者/
用来列出并执行当前存储在缓冲区的SQL命令
示例：
select*fromstudent;
SQL>
RUN
1*select*fromstudent;
2、LIST简写L
列出当前缓冲区的内容，主要用在当我们写错SQL语句的时候，可以将错误的SQL语句列出来，以便修改
LISTn列出SQL语句的第几行。
LISTm,n列出SQL语句的第几行到第几行。
3、CHANGE简写C
编辑LIST命令列出的行。
用法：
CHANGE旧文本/新文本
4、DEL
删除LISTn命令列出的某一行
5、APPEND简写A
在一行的尾部添加文本
6、CLEARBUFFER简写CLBUFF
删除全部的行
oracle上机练习（三）
本练习主要熟悉在oracelsql*plus中常用的汇总函数：
汇总函数主要有：
COUNT：
返回满足WHERE条件子句中记录的个数。
SUM：
它返回某一列的所有数值的和。
AVG：
可以返回某一列的平均值。
MAX：
如果你想知道某一列中的最大值请使用MAX。
MIN：
MIN与MAX类似它返回一列中的最小数值。
练习：
建立TEAMSTATS表如下：
NAMEPOSABHITSWALKSSINGLESDOUBLESTRIPLESHRSO
JONES1B14545343181510
DONKNOW3B175652350101415
WORLEYLF1574915583316
DAVIDOF187702448401742
HAMHOCKER3B5012101020013
CASEYDH10000001
在TEAMSTATS表上做如下操作：
（1）查看WALKS小于20的球员的个数。
SELECTCOUNT（*）FROMTEAMSTATSWHEREWALKS<
20;
（2）查看表中所有记录的个数。
（自己写）
（3）查看队员总的HITS数。
SELECTSUM（HITS）TOTAL_HITSFROMTEAMSTATS;
（4）查看3B位置上的队员总的DOUBLES及总的SO。
（5）查看漏球（SO）的平均数。
SELECTAVG（SO）AVE_STRIKE_OUTSFROMTEAMSTATS;
（6）查看TRIPLES为0的队员的HITS的平均数。
（7）查看所有队员的最高SIGGLES是多少。
SELECTMAX（SIGGLES）FROMTEAMSTATS;
oracle上机练习（四）
题目：
已知公司的员工表EMP（EID,ENAME,BDATE,SEX,CITY），
部门表DEPT（DID,DNAME,DCITY），
工作表WORK（EID,DID,STARTDATE,SALARY）。
各个字段说明如下：
EID——员工编号，最多6个字符。
例如A00001（主键）
ENAME——员工姓名，最多10个字符。
例如SMITH
BDATE——出生日期，日期型
SEX——员工性别，单个字符。
F或者M
CITY——员工居住的城市，最多20个字符。
例如：
上海
DID——部门编号，最多3个字符。
例如A01（主键）
DNAME——部门名称，最多20个字符。
研发部门
DCITY——部门所在的城市，最多20个字符。
STARTDATE——员工到部门上班的日期，日期型
SALARY——员工的工资。
整型。
请使用ORACLE的sql*plus完成下列的操作
1、创建表EMP，DEPT，WORK，并定义表的主键和外键。
2、向每个表中插入适当的数据。
插入三条部门的数据，分别为每个部门插入两条员工数据
3、查询“研发”部门的所有员工的基本信息
4、查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。
5、显示部门人数大于5的每个部门的编号，名称，人数
6、显示部门人数大于5的每个部门的最高工资，最低工资
7、列出员工编号以字母P至S开头的所有员工的基本信息
8、删除年龄超过60岁的员工
9、为工龄超过10年的职工增加10%的工资
说明：
1、环境：
sql*plus
2、将练习（四）所有的SQL语句整理成文本文件保存下来，以姓名和学号命名。
下课之前作为附件发到邮箱：
sli@
oracle上机练习（五）
一.PL/SQL快速学习：
1.PL/SQL是一项ORACLE的技术,是ORACLE的过程型语言，它由标准的SQL语句和一系列可以让你在不同的情况下对SQL语句的执行进行控制的命令组成。
2.PL/SQL块的结构:
PL/SQL是一种块结构语言,也就是说PL/SQL的程序可以分成逻辑块来写。
块是PL/SQL代码的逻辑单元，包括至少一个BEGIN部分和可以选择的DECLARE以及EXCEPTION部分。
PL/SQL块的基本结构：
SYNTAX:
BEGIN--optional,denotesbeginningofblock
DECLARE--optional,variabledefinitions
BEGIN--mandatory,denotesbeginningofproceduresection
EXCEPTION--optional,denotesbeginningofexceptionsection
END--mandatory,denotesendingofproceduresection
END--optional,denotesendingofblock
（1）在PL/SQL的DECLARE部分包括了变量、常量、指针和特殊数据类型的定义。
（2）BEGIN部分是PL/SQL语句块中的必须部分，在这一部分将会使用变量和用户指针来操作数据库中的数据。
（3）EXCEPTION部分在PL/SQL语句块中是可以选择的，如果在这一部分被省略而遇到异常的时候该块就会终止了。
3.由基本的PL/SQL块所组成的PL/SQL程序，可组成不同的程序形式，它们的用途和适用性各不相同。
程序形式大致有以下几种：
（1）无名块：
也就是没有命名的PL/SQL块，它可以是嵌入某一个应用之中的一个PL/SQL块。
无名块在所有PL/SQL环境中都适用。
（2）存储过程/函数（Procedure/Function）：
命名的PL/SQL块，它可以接受参数，并且可以重复的被调用。
（3）包（Package）：
命名的PL/SQL块，由一组相关的过程、函数和标识符组成。
（4）数据库触发器（Triggers）：
是与一个具体数据库表相关联的PL/SQL存储程序。
每当一个SQL操作影响到该数据库表时，系统就自动执行相应的数据库触发器。
4.PL/SQL中的控制语句:
（1）条件语句：
IF...THEN
语法：
IFcondition1THEN
statement1；
ELSIFcondition2THEN（可选）
statement2；
ELSE（可选）
statement3；
ENDIF
（2）循环语句：
i.简单循环：
LOOP
ENDLOOP
ii.FOR循环：
iii.WHILE循环：
二.实例：
1.使用的表为：
PAY_TABLE，结构：
NameNull?
Type
NAMENOTNULLVARCHAR2（20）
PAY_TYPENOTNULLVARCHAR2（8）
PAY_RATENOTNULLNUMBER（8,2）
EFF_DATENOTNULLDATE
PREV_PAYNUMBER（8,2）
数据：
NAMEPAY_TYPEPAY_RATEEFF_DATEPREV_PAY
SANDRASAMUELSHOURLY12.5001-JAN-04
ROBERTBOBAYHOURLY11.5015-MAY-03
KEITHJONESHOURLY10.0031-OCT-04
SUSANWILLIAMSHOURLY9.7501-MAY-04
CHRISSYZOESSALARY50000.0001-JAN-04
CLODEEVANSSALARY42150.0001-MAR-04
JOHNSMITHSALARY35000.0015-JUN-03
KEVINTROLLBERGSALARY27500.0015-JUN-03
2.操作要求：
要给为你工作的时间超过了六个月的个人增加薪金。
符合条件的钟点工的薪金增加4%而符合条件的雇员的薪金需要增加5%。
PL/SQL的脚本：
setserveroutputon
DECLARE
UnknownPayTypeexception;
cursorpay_cursoris
selectname,pay_type,pay_rate,eff_date,
sysdate,rowid
frompay_table;
IndRecpay_cursor%ROWTYPE;
cOldDatedate;
fNewPaynumber（8,2）;
openpay_cursor;
loop
fetchpay_cursorintoIndRec;
exitwhenpay_cursor%NOTFOUND;
cOldDate:
=sysdate-180;
if（IndRec.pay_type='
SALARY'
）then
fNewPay:
=IndRec.pay_rate*1.05;
elsif（IndRec.pay_type='
HOURLY'
=IndRec.pay_rate*1.04;
else
raiseUnknownPayType;
endif;
if（IndRec.eff_date<
cOldDate）then
updatepay_table
setpay_rate=fNewPay,
prev_pay=IndRec.pay_rate,
eff_date=IndRec.sysdate
whererowid=IndRec.rowid;
commit;
endloop;
closepay_cursor;
EXCEPTION
whenUnknownPayTypethen
dbms_output.put_line（'
======================='
）;
ERROR:
Abortingprogram.'
UnknownPayTypeforName'
whenothersthen
ERRORDuringProcessing.SeetheDBA.'
/
三.练习：
1.对PAY_TABLE表写一个查询所有记录的过程。
2.对PAY_TABLE表写一个插入一条记录的过程，要求输入参数为一条记录。
3.写一个过程实现如下要求：
对工作时间超过8个月的职员，如果PAY_TYPE
是HOURLY,则改为SALARY,并将PAY_RATE改为按每天7小时工作的年薪.
oracle上机练习（六）
一．案例
某数据库有两张表：
emp表和dept表，两张表的结构如下：
emp（emp_idnumber（5）,emp_namevarchar2（20）,emp_salarynumber（4））；
dept（dept_idnumber（3）,dept_namevarchar2（20）,emp_idnumber（5））;
要求如下：
1、按照上表结构建立相应的表，为每张表写入5组合法数据。
2、操纵相关表，使得“技术部”的员工的薪水上涨20%。
3、建立日志，追踪薪水变动情况。
4、建立测试包。
二．案例的分析与实现
要求1考察点为基本SQL语句；
要求2主要考察复合查询；
要求3是考察触发器的应用；
要求4不仅考察了包的创建，而且也考察了在PL/SQL中的测试方法。
要求1：
首先根据前面表的结构可以创建两张表：
——创建员工表
createtableemp（emp_idnumber（5）,emp_namevarchar2（20）,emp_salarynumber（4））;
——部门表
createtabledept（dept_idnumber（3）,dept_namevarchar2（20）,emp_idnumber（5））;
建立了表之后就可以往表里面写数据了，这里把添加表记录的代码写入到相应的存储过程。
/*给emp表添加记录的存储过程*/
createorreplaceprocedureins_table_emp（p_emp_idnumber,p_emp_namevarchar2,p_emp_salarynumber）as
v_emp_idnumber:
=p_emp_id;
v_emp_namevarchar2（20）:
=p_emp_name;
v_emp_salarynumber:
=p_emp_salary;
begin
insertintoempvalues（v_emp_id,v_emp_name,v_emp_salary）;
endins_table_emp;
/*给dept表添加记录的存储过程*/
createorreplaceprocedureins_table_dept（p_dept_idnumber,p_dept_namevarchar2,p_emp_idnumber）as
v_dept_idnumber:
=p_dept_id;
v_dept_namevarchar2（20）:
=p_dept_name;
v_emp_idnumber:
insertintodeptvalues（v_dept_id,v_dept_name,v_emp_id）;
/*调用相应的存储过程实现记录添加*/
ins_table_emp（10000,'
'
4000）;
ins_table_emp（10001,'
?
è
y'
2300）;
ins_table_emp（10002,'
3?
t'
3500）;
ins_table_emp（10003,'
à
ins_table_emp（10004,'
á
ò
ins_table_dept（111,'
DD?
t2?
10000）;
10001）;
10002）;
ins_table_dept（112,'
ê
2?
10003）;
ins_table_dept（113,'
D3?
10004）;
end;
要求2：
给指定部门的员工加薪，这实际上是一个复合查询，首先需要把所有该部门的员工选出来，然后对这些员工的薪水进行相应的改动。
代码如下：
（需要注意的是：
将要加薪的部门作为参数，这样的存储过程更有灵活性。
createorreplaceprocedureadd_salary（p_dept_namevarchar2）as
v_dept_namevarchar2（20）:
updateempsetemp.EMP_SALARY=emp.EMP_SALARY*1.2whereemp.EMP_IDin（selectemp.EMP_IDfromemp,deptwhereemp.EMP_ID=dept.EMP_IDanddept.DEPT_ID='
endadd_salary;
要求3：
建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来。
如果对emp表的salary字段创建一个触发器，来监视对salary的更改，把每次更改进行记录，这样就达到了要求3的目的了。
createorreplacetriggerprint_salary_change
beforedeleteorinsertorupdateonemp--触发事件
foreachrow--每修改一行都需要调用此过程
declare--只有触发器的声明需要declare，过程和函数都不需要
salary_balancenumber;
--:
new与:
old分别代表该行在修改前和修改后的记录
salary_balance=:
new.salary=:
old.salary;
升级会员 