Sql and Plsql 测试
作者:   
创建日期:   
最近更新:   
控制号:   
版本:    1
审批:
    拷贝号    _____
文档控制
更新记录
日期
作者
版本
变更参考
审阅
姓名
职位
分发
拷贝号
姓名
地点
1
1
1
1
(9-Dec-96)
目录
文档控制    ii
概述    4
测试用例    4
测试题    5
(9-Dec-96)

概述
本测试文档主要目的用于测试培训人员对sql 与 plsql的掌握情况，主要侧重于基本语法。
测试用例
用例
直接将sqlplus的结果粘贴进来，如果是函数或过程，请将代码粘贴进来。
问题
1
从EMP表查询部门编号为10的所有员工，显示员工编号，员工名称
SQL> select empno, ename
  2  from emp
  3  where deptno=10;
    EMPNO ENAME
---------- --------------------
      7782 CLARK
      7839 KING
      7934 MILLER
2
将部门编号为10的所有员工转移到部门40
SQL> update emp
  2  set deptno=40
  3  where deptno=10;
已更新3行。
(4-Dec-96)
测试题
plsql developer怎么执行语句
问题
以system/manager登录到sql plus
1
创建用户USERXXX，同时指定该用户的表空间到CUX_DATA,临时表空间到TEMP。
create user USERJOAN
identified by huangqq
default tablespace CUX_DATA
temporay tablespace TEMP;
2
授予USERXXX用户连接和使用资源的权限。
grant connect,resource to USERJOAN;
切换到用户USERXXX
3
创建同义词 EMP 到 SCOTT.EMP，DEPT到SCOTT.DEPT
create synonym EMP for SCOTT.EMP;
create synonym DEPT for SCOTT.DEPT;
4
显示EMP的表结构
desc EMP;
5
按照EMP的表结构创建表 EMPXXX，使用Create table … (column1 datetype[,column…] );
Create table EMPJOAN(
ame%type ename,
pno%type empno,
emp.sal%type sal,
empm%type comm;
emp.job%type job,
emp.manager%type manager,
emp. hiredate%type hiredate);
6
对表EMPXXX的EMPNO创建主键 EMPXXX_PK, 需要指定索引表空间到CUX_INDEX
alert  table EMPJOAN
add primary key EMPNO EMPJOAN_PK;
create index JOAN_INDEX
on EMPJOAN(empno) 
tablespace CUX_INDEX;
7
对表EMPXXX的ENAME创建唯一索引EMPXXX_U1, 需要指定索引表空间到CUX_INDEX
create unique index EMPJOAN_U1
on EMPJOAN(ename)
tablespace CUX_INDEX;
8
将表EMP的数据增加到表EMPXXX中，使用insert … select …语句
insert into
EMPJOAN  as
select *
from emp;
9
对表EMPXXX的JOB创建约束EMPXXX_C1,将JOB的值限制在'CLERK','SALESMAN','MANAGER','ANALYST','PRESIDENT'
alter EMPJOAN
add constraints EMPJOAN_C1(
job='CLERK’ or
job=’'SALESMAN' or
job='MANAGER' or
job='ANALYST' or
job='PRESIDENT');
10
使用SQL: CREATE TABLE ... AS SELECT ... FROM ... 来创建表DEPTXXX
Create table DETPJOAN
as SELECT * FROM dept;
11
为表DEPTXXX的DEPTNO创建主键 DEPTXXX_PK, 需要指定索引表空间到CUX_INDEX
alert  table DEPTJOAN
add primary key EMPNO DEPTJOAN_PK;
create index DEPT_INDEX
on DEPTJOAN(empno) 
tablespace CUX_INDEX;
12
将表EMPXXX, DEPTXXX的SELECT, INSERT, UPDATE, DELETE权限赋予PUBLIC
Grant select,insert,update,delete  on EMPJOAN,DEPTJOAN to PUBLIC.
13
查询出EMPXXX中各员工的总工资（SAL + COMM），和年薪，显示显示员工名、总工资、年薪
Select
ename,
sal+nvl(comm.,0) total_sal,
12*(sal+nvl(comm.,0)) year_sal
from EMPJOAN;
14
查询出EMPXXX员工分布在哪些部门，只显示部门编号（唯一性）
Select
distinct depno
from EMPJOAN;
15
从EMPXXX获取员工姓名中含有M字母并且姓名的长度>4的员工，显示员工号、员工名
Select
depno,ename
from EMPJOAN
where ename like ‘%M%’
and length(ename)>4;
16
从EMPXXX,DEPTXXX查询出各员工及其所在的部门名称，显示员工名、部门编号、部门名称
Select
e.enema,e.depno,d.dname
from EMPJOAN e,DEPTJOAN d;
17
从EMPXXX查询出员工及其经理名称，无经理的也要显示出来，显示出员工号、员工名、经理的员工号、经理名称
Select depno,ename,manager,manger_name
from EMPJOAN
where manger_name in (
Select  ename from EMPJOAN whre depno=manager);
18
从EMPXXX查询出所有部门为20的员工工龄，显示员工名、雇用日期、工龄，排序：按工龄降序
Select
ename,hiredate, trunc(months_between(sysdate,hiredate)/12) work_year
from EMPJOAN
where depno=20
order by
work_year;
19
从EMPXXX查询出1981年入职并且工资在1000 - 2000内的员工,显示员工名、雇用日期、工资
Select
ename,hiredate,sal
from EMPJOAN
where to_date(hiredate,’yyyy’)=to_date(‘1986’,’yyyy’)
and sal between 1000 and 2000;
20
按工资写出员工的工资等级：>= 3000 为 A, >= 2000 为 B, >= 1000 为C, <1000 为D, 使用CASE语句，显示员工名，工资等级
Create procedure test_case is
Begin
Select sal  from EMPJOAN;
End;
select
ename,
case
when sal>=3000 then ‘A’
when sal>=2000 and sal<3000 then ‘B’
when sal>=1000 and sal<2000 then ‘C’
ELSE  ‘D’
End
grance
from EMPJOAN;
21
年底调薪，需要将JOB为CLERK的增加15%，MANAGER增加20%，其他增加10%，请从EMPXXX写出SQL显示员工名、工资、调整后工资，按员工名排序，要求使用DECODE函数
22
从EMPXXX查询各个部门的最先入职和最后入职年月，要求入职年月显示格式为YYYY/MM，显示最先入职年月\最后入职年月
23
从EMPXXX查询出平均工资在1500以上的部门，显示部门编号、部门名称
24
从EMPXXX查出与CLARK同职但工资比他高的员工，显示员工名、工资
25
从EMPXXX查出与CLARK同经理同职位的其他员工，显示员工名、职位、经理的员工编号
26
以树状结构显示编号7698员工的所有下属，显示员工号、员工名称，经理的员工编号，层数
27
创建序列EMPXXX_S，起始值从8000开始，步长1
28
增加员工到EMPXXX表中，EMPNO = EMPXXX_S.NEXTVAL, ENAME = JOHN, JOB = CLERK, MGR = 7900，HIREDATE = 1982-01-20，SAL = 1200，DEPTNO = 50
29
对表EMPXXX增加一个字段NEW_SAL，类型NUMBER，允许为空
30
在表EMPXXX中，对低于其所在部门平均工资的人员，更新字段NEW_SQL = SAL * 1.10
31
在表EMPXXX中，将分配到无效部门（DEPTXXX）的员工删除掉
32
在表EMPXXX中，将员工MILLER提升到与CLARK同职位且同上级（经理）
33
在表EMPXXX中，查询出工资排行榜前3位的员工，显示用工号，姓名、员工名称、工资，按工资降序排列
34
创建视图EMPXXX_V，使用到基表EMPXXX, DEPTXXX，视图包含字段，ROW_ID、EMPXXX中的全部字段、DEPTXXX中的DNAME和LOC
以下测试，请在PLSQL Developer 中的test window中完成，所有的输出使用DBMS_OUTPUT
35
在test window写一段程序，实现以下逻辑：
1． 检索出所有的部门DEPTXXX，按部门编号排序；
2． 循环各个部门，到员工表EMPXXX查询，判断是否有员工分配到该部门；
3． 如果有员工分配到该部门，则出该部门担任MANAGER的员工并输出，输出内容：部门名称、员工号、员工名称；如果不到MANAGER，则输出内容：部门名称+‘尚未分配部门经理’
4． 如果没有员工分配到该部门，则输出内容：部门名称 + ‘里没有员工’
创建程序包
36
要求：
创建程序包EMPXXX_PKG程序包
在程序包中创建函数 RANDOM_CHAR来产生指定长度的随机字符串
函数定义如下：
  FUNCITON random_char( p_length    in number default 10,
                                                  P_include_char in boolean default false ) return varchar2;
P_LENGTH                    产生随机字符的长度
P_INCLUDE_CHAR    产生的随机字符是否包含字母，否则只能为数字
程序逻辑：
以下是产生随机字符的一种方式，仅供参考：
如果不含字母 使用DBMS_RANDOM.VALUE(48,57)得到ASCII码，再使用CHR()转化成字符；
如果含有字母，使用DBMS_RANDOM.VALUE(48,90)获得ASCII码，如果ASCII码在 58~64则重新取，再使用CHR()转化成字符；
37
功能：开发一个过程来为员工产生PIN码。
前提：创建表PINXXX来存放员工PIN码，字段：EMPNO  NUMBER(4), PIN_NUMBER VARCHAR2(30), UPDATED_DATE DATE
对字段EMPNO创建唯一索引；
各自段含义：
EMPNO                        员工号
PIN_NUMBER            PIN码
UPDATED_DATE      最后更新时间
在程序包EMPXXX_PKG增加一个过程
定义如下：
PROCEDURE generate_emp_pin( p_empno  IN NUMBER,
                                                            x_pin_number  OUT NOCOPY VARCHAR2,
                                                            x_return_status OUT NOCOPY VARCHAR2,
                                                            x_return_message  OUT VARCHAR2 NOCOPY)
参数说明：
P_EMPNO                                要产生PIN码的员工号
X_PIN_NUMBER                    返回成功产生PIN码
X_RETURN_STATUS            返回状态：S 成功，E 错误
X_RETURN_MESSAGE          返回信息：
成功，返回‘旧PIN码：XXXXXXXX => 新PIN码：XXXXXXXX’
失败，返回具体错误信息
过程模板：
PROCEDURE generate_emp_pin( p_empno  IN NUMBER,
                                                            x_pin_number  OUT NOCOPY VARCHAR2,
                                                            x_return_status OUT NOCOPY VARCHAR2,
                                                            x_return_message  OUT VARCHAR2 NOCOPY)
IS
  --申明部分
BEGIN
  -- 初始化变量，设置保存点
X_return_status := ‘S’;
    Savepoint sp_generate_pin;
    ….
    ….
    -- 提交保存
Commit work;
EXCEPTION
    Rollback to savepoint sp_generate_pin;
X_return_status := ‘E’;
    X_return_message := sqlerrm;
END;
程序逻辑：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。