orcle题目Word下载.docx

1、3、查看系统拥有哪些用户 select * from all_users;4、新建用户并授权 create user a identified by a;（默认建在SYSTEM表空间下） grant connect,resource to a;5、连接到新用户 conn a/a6、查询当前用户下所有对象 select * from tab;7、建立第一个表 create table a（a number）;8、查询表结构 desc a9、插入新记录 insert into a values（1）;10、查询记录 select * from a;11、更改记录 update a set a=2;

2、12、删除记录 delete from a;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、向上面的三

3、个表中分别插入5条纪录。 示例：（1） INSERT INTO STUDENT （学号，姓名，入学时间，专业，性别，年龄）VALUES （100001,HUANGWEI,TO_DATE（1999-09-10,YYYY-MM-DD）,COMPUTER,男,23）;（2） INSERT INTO COURSE （课程号,课程名称,学时,学分） VALUES （000001,多媒体,32,4）;（3） INSERT INTO GRADE （学号，课程号,成绩）VALUES （100001,000001,90） 二、练习使用SQL*PLUS的一些编辑和执行命令 1、RUN 简写R 或者 /用来列出并执

4、行当前存储在缓冲区的SQL命令示例： select * from student; SQL RUN 1* select * from student; 2、LIST 简写 L列出当前缓冲区的内容，主要用在当我们写错SQL语句的时候，可以将错误的SQL语句列出来，以便修改LIST n 列出SQL语句的第几行。LIST m,n 列出SQL语句的第几行到第几行。3、CHANGE 简写 C 编辑LIST命令列出的行。 用法： CHANGE 旧文本/新文本4、DEL 删除LIST n命令列出的某一行5、APPEND 简写 A 在一行的尾部添加文本6、CLEAR BUFFER 简写 CL BUFF 删除全

5、部的行oracle 上机练习（三）本练习主要熟悉在oracel sql*plus中常用的汇总函数：汇总函数主要有：COUNT：返回满足WHERE 条件子句中记录的个数。SUM：它返回某一列的所有数值的和。AVG：可以返回某一列的平均值。MAX：如果你想知道某一列中的最大值请使用MAX。MIN：MIN 与MAX 类似它返回一列中的最小数值。练习：建立TEAMSTATS表如下：NAME POS AB HITS WALKS SINGLES DOUBLES TRIPLES HR SOJONES 1B 145 45 34 31 8 1 5 10DONKNOW 3B 175 65 23 50 10 1 4

6、 15WORLEY LF 157 49 15 5 8 3 3 16 DAVID OF 187 70 24 48 4 0 17 42HAMHOCKER 3B 50 12 10 10 2 0 0 13CASEY DH 1 0 0 0 0 0 0 1在TEAMSTATS表上做如下操作：（1）查看WALKS小于20的球员的个数。 SELECT COUNT（*） FROM TEAMSTATS WHERE WALKS20;（2）查看表中所有记录的个数。（自己写）（3）查看队员总的HITS数。SELECT SUM（HITS） TOTAL_HITS FROM TEAMSTATS;（4）查看3B位置上的队员总的

7、DOUBLES及总的SO。（5）查看漏球（SO）的平均数。SELECT AVG（SO） AVE_STRIKE_OUTS FROM TEAMSTATS;（6）查看TRIPLES为0的队员的HITS的平均数。（7）查看所有队员的最高SIGGLES是多少。SELECT MAX（SIGGLES） FROM TEAMSTATS;oracle 上机练习（四）题目：已知公司的员工表EMP（EID, ENAME, BDATE, SEX, CITY），部门表DEPT（DID, DNAME, DCITY），工作表WORK（EID,DID,STARTDATE,SALARY）。各个字段说明如下：EID员工编号，最多6

8、个字符。例如A00001（主键）ENAME员工姓名，最多10个字符。例如SMITHBDATE出生日期，日期型SEX员工性别，单个字符。F或者MCITY员工居住的城市，最多20个字符。例如：上海DID部门编号，最多3个字符。例如 A01 （主键）DNAME部门名称，最多20个字符。研发部门DCITY部门所在的城市，最多20个字符。STARTDATE员工到部门上班的日期，日期型SALARY员工的工资。整型。请使用ORACLE的sql*plus 完成下列的操作1、 创建表EMP，DEPT，WORK，并定义表的主键和外键。2、 向每个表中插入适当的数据。插入三条部门的数据，分别为每个部门插入两条员工数

9、据3、 查询“研发”部门的所有员工的基本信息4、 查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。5、 显示部门人数大于5的每个部门的编号，名称，人数6、 显示部门人数大于5的每个部门的最高工资，最低工资7、 列出员工编号以字母P至S开头的所有员工的基本信息8、 删除年龄超过60岁的员工9、 为工龄超过10年的职工增加10%的工资说明：1、环境：sql*plus 2、将练习（四）所有的SQL语句整理成文本文件保存下来，以姓名和学号命名。下课之前作为附件发到邮箱：sli oracle上机练习（五）一.PL/SQL快

10、速学习：1.PL/SQL 是一项ORACLE 的技术,是ORACLE 的过程型语言，它由标准的SQL 语句和一系列可以让你在不同的情况下对SQL 语句的执行进行控制的命令组成。2.PL/SQL 块的结构: PL/SQL 是一种块结构语言,也就是说PL/SQL 的程序可以分成逻辑块来写。块是PL/SQL 代码的逻辑单元，包括至少一个BEGIN 部分和可以选择的DECLARE 以及EXCEPTION 部分。 PL/SQL 块的基本结构：SYNTAX:BEGIN - optional, denotes beginning of block DECLARE - optional, variable d

11、efinitions BEGIN - mandatory, denotes beginning of procedure section EXCEPTION - optional, denotes beginning of exception section END - mandatory, denotes ending of procedure sectionEND - optional, denotes ending of block （1）在PL/SQL 的DECLARE 部分包括了变量、常量、指针和特殊数据类型的定义。 （2） BEGIN部分是PL/SQL 语句块中的必须部分，在这一部

12、分将会使用变量和用户指针来操作数据库中的数据。 （3）EXCEPTION 部分在PL/SQL 语句块中是可以选择的，如果在这一部分被省略而遇到异常的时候该块就会终止了。3.由基本的PL/SQL块所组成的PL/SQL程序，可组成不同的程序形式，它们的用途和适用性各不相同。程序形式大致有以下几种： （1）无名块：也就是没有命名的PL/SQL块，它可以是嵌入某一个应用之中的一个PL/SQL块。无名块在所有PL/SQL环境中都适用。 （2）存储过程/函数（Procedure/Function）：命名的PL/SQL块，它可以接受参数，并且可 以重复的被调用。 （3）包（Package）：命名的PL/SQ

13、L块，由一组相关的过程、函数和标识符组成。 （4）数据库触发器（Triggers）：是与一个具体数据库表相关联的PL/SQL存储程序。每当一个SQL操作影响到该数据库表时，系统就自动执行相应的数据库触发器。4.PL/SQL中的控制语句: （1）条件语句：IF.THEN 语法： IF condition1 THEN statement1； ELSIF condition2 THEN（可选） statement2； ELSE（可选） statement3； END IF （2）循环语句： i.简单循环： LOOP END LOOP ii.FOR循环： iii.WHILE循环：二.实例： 1.使用的

14、表为：PAY_TABLE， 结构： Name Null? Type NAME NOT NULL VARCHAR2（20） PAY_TYPE NOT NULL VARCHAR2（8） PAY_RATE NOT NULL NUMBER（8,2） EFF_DATE NOT NULL DATE PREV_PAY NUMBER（8,2） 数据： NAME PAY_TYPE PAY_RATE EFF_DATE PREV_PAY SANDRA SAMUELS HOURLY 12.50 01-JAN-04 ROBERT BOBAY HOURLY 11.50 15-MAY-03 KEITH JONES HOUR

15、LY 10.00 31-OCT-04 SUSAN WILLIAMS HOURLY 9.75 01-MAY-04 CHRISSY ZOES SALARY 50000.00 01-JAN-04 CLODE EVANS SALARY 42150.00 01-MAR-04 JOHN SMITH SALARY 35000.00 15-JUN-03 KEVIN TROLLBERG SALARY 27500.00 15-JUN-03 2.操作要求：要给为你工作的时间超过了六个月的个人增加薪金。符合条件的钟点工的薪金增加4% 而符合条件的雇员的薪金需要增加5% 。PL/SQL 的脚本： set servero

16、utput on DECLARE UnknownPayType exception; cursor pay_cursor is select name, pay_type, pay_rate, eff_date, sysdate, rowid from pay_table; IndRec pay_cursor%ROWTYPE; cOldDate date; fNewPay number（8,2）; open pay_cursor; loop fetch pay_cursor into IndRec; exit when pay_cursor%NOTFOUND; cOldDate := sysd

17、ate - 180; if （IndRec.pay_type = SALARY） then fNewPay := IndRec.pay_rate * 1.05; elsif （IndRec.pay_type = HOURLY= IndRec.pay_rate * 1.04; else raise UnknownPayType; end if; if （IndRec.eff_date cOldDate） then update pay_table set pay_rate = fNewPay, prev_pay = IndRec.pay_rate, eff_date = IndRec.sysda

18、te where rowid = IndRec.rowid; commit; end loop; close pay_cursor; EXCEPTION when UnknownPayType then dbms_output.put_line（=）;ERROR: Aborting program.Unknown Pay Type for Name when others thenERROR During Processing. See the DBA./三.练习： 1.对PAY_TABLE表写一个查询所有记录的过程。 2.对PAY_TABLE表写一个插入一条记录的过程，要求输入参数为一条记录

19、。 3.写一个过程实现如下要求：对工作时间超过8个月的职员，如果PAY_TYPE 是HOURLY,则改为SALARY,并将PAY_RATE改为按每天7小时工作的年薪. oracle上机练习（六）一 案例某数据库有两张表：emp表和dept表，两张表的结构如下：emp （emp_id number（5）, emp_name varchar2（20）, emp_salary number（4）；dept （dept_id number（3）, dept_name varchar2（20）, emp_id number（5）;要求如下：1、按照上表结构建立相应的表，为每张表写入5组合法数据。2、操纵

20、相关表，使得“技术部”的员工的薪水上涨20%。3、建立日志，追踪薪水变动情况。4、建立测试包。二 案例的分析与实现要求1考察点为基本SQL语句；要求2主要考察复合查询；要求3是考察触发器的应用；要求4不仅考察了包的创建，而且也考察了在PL/SQL中的测试方法。要求1：首先根据前面表的结构可以创建两张表：创建员工表create table emp （emp_id number（5）, emp_name varchar2（20）, emp_salary number（4）;部门表create table dept （dept_id number（3）, dept_name varchar2（20）

21、, emp_id number（5）;建立了表之后就可以往表里面写数据了，这里把添加表记录的代码写入到相应的存储过程。/*给emp表添加记录的存储过程*/create or replace procedure ins_table_emp（p_emp_id number,p_emp_name varchar2,p_emp_salary number） asv_emp_id number:=p_emp_id;v_emp_name varchar2（20）:=p_emp_name;v_emp_salary number:=p_emp_salary;begininsert into emp value

22、s （v_emp_id,v_emp_name,v_emp_salary）;end ins_table_emp;/*给dept表添加记录的存储过程*/create or replace procedure ins_table_dept（p_dept_id number,p_dept_name varchar2,p_emp_id number） asv_dept_id number:=p_dept_id;v_dept_name varchar2（20）:=p_dept_name;v_emp_id number:insert into dept values （v_dept_id,v_dept_na

23、me,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：给指定部门的员工加薪，这实际上是一个复合查询，首先需要

24、把所有该部门的员工选出来，然后对这些员工的薪水进行相应的改动。代码如下：（需要注意的是：将要加薪的部门作为参数，这样的存储过程更有灵活性。create or replace procedure add_salary（p_dept_name varchar2） asv_dept_name varchar2（20）:update emp set emp.EMP_SALARY=emp.EMP_SALARY*1.2 where emp.EMP_ID in （select emp.EMP_ID from emp,dept where emp.EMP_ID=dept.EMP_ID and dept.DEP

25、T_ID=end add_salary;要求3：建立日志对薪水的变动情况形成一个追踪，也就是说，如果对某个职员的薪水进行变更就应该将其相应的变更记录全部记下来。如果对emp表的salary字段创建一个触发器，来监视对salary的更改，把每次更改进行记录，这样就达到了要求3的目的了。create or replace trigger print_salary_changebefore delete or insert or update on emp-触发事件for each row- 每修改一行都需要调用此过程declare -只有触发器的声明需要declare，过程和函数都不需要salary_balance number;-:new 与:old分别代表该行在修改前和修改后的记录salary_balance=:new.salary=:old.salary;