plsql测试―参考答案_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
plsql测试―参考答案
阅读已结束，下载文档到电脑
想免费下载本文？
定制HR最喜欢的简历
你可能喜欢EMP表是Oracle测试账户SCOTT中的一张雇员表，首先，我们来看看emp表的数据
SQL& select * from
EMPNO ENAME
MGR HIREDATE
----- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH
7902 17-DEC-80
7499 ALLEN
7698 20-FEB-81
7698 22-FEB-81
7566 JONES
7839 02-APR-81
7654 MARTIN
7698 28-SEP-81
7698 BLAKE
7839 01-MAY-81
7782 CLARK
7839 09-JUN-81
7788 SCOTT
7566 19-APR-87
7844 TURNER
7698 08-SEP-81
7876 ADAMS
7788 23-MAY-87
7900 JAMES
7698 03-DEC-81
7566 03-DEC-81
7934 MILLER
7782 23-JAN-82
14 rows selected.
其中，empno是员工编号，同时也是该表的主键，ename是员工姓名，sal是员工工资，deptno是员工部门。
如何找出每个部门的最高工资的员工信息呢？
常用的方法是关联查询，SQL语句如下：
select emp.deptno,ename,sal
(select deptno,max(sal)maxsal from emp group by deptno) t
where emp.deptno=t.deptno and emp.sal=t.
结果如下：
DEPTNO ENAME
---------- ---------- ----------
下面我们来看看执行计划：
Execution Plan
----------------------------------------------------------
Plan hash value:
-----------------------------------------------------------------------------
| Operation
| Cost (%CPU) | Time
-----------------------------------------------------------------------------
0 | SELECT STATEMENT
(15)| 00:00:01 |
(15)| 00:00:01 |
(25)| 00:00:01 |
HASH GROUP BY
(25)| 00:00:01 |
TABLE ACCESS FULL| EMP
(0)| 00:00:01 |
TABLE ACCESS FULL
(0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="T"."DEPTNO" AND "EMP"."SAL"="T"."MAXSAL")
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
不难看出，该查询针对同一个表走了两次全盘扫描，成本为7，逻辑读为13。
如何对上述查询进行优化呢？在这里，用到分析函数LAST_VALUE，LAST_VALUE返回排序集中的最后一个值。
SELECT deptno,ename,sal,
LAST_VALUE(sal)
OVER(PARTITION BY deptno
ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)maxsal
输出结果如下：
DEPTNO ENAME
---------- ---------- ---------- ----------
14 rows selected.
不难看出，sal等于maxsal的行即为每个部门最高工资的员工,下面用嵌套子查询得到目标结果。
SELECT deptno,ename,sal FROM (
SELECT deptno,ename,sal,
LAST_VALUE(sal)
OVER(PARTITION BY deptno
ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)maxsal
FROM emp) WHERE sal=
输出结果如下：
DEPTNO ENAME
---------- ---------- ----------
下面我们来看看该语句的执行计划：
Execution Plan
----------------------------------------------------------
Plan hash value:
----------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------------
0 | SELECT STATEMENT
(25)| 00:00:01 |
(25)| 00:00:01 |
WINDOW SORT
(25)| 00:00:01 |
TABLE ACCESS FULL |
(0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SAL"="MAXSAL")
Statistics
----------------------------------------------------------
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
可见，引入了分析函数以后，成本和逻辑读都减少了一半。
通过查询的结果，我们可以看出，20号部门有两个人的工资最高，有时候，我们只想得到一个人的信息，如何实现呢？
在这里我们会用到分析函数LAG，具体SQL如下：
SELECT deptno,ename,sal,LAG(sal)OVER(ORDER BY deptno) presal FROM (
SELECT deptno,ename,sal,
LAST_VALUE(sal)
OVER(PARTITION BY deptno
ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)maxsal
FROM emp) WHERE sal=
输出结果如下：
DEPTNO ENAME
---------- ---------- ---------- ----------
剔除sal等于presal的行
SELECT deptno,ename,sal FROM (
SELECT deptno,ename,sal,LAG(sal)OVER(ORDER BY deptno) presal FROM (
SELECT deptno,ename,sal,
LAST_VALUE(sal)
OVER(PARTITION BY deptno
ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)maxsal
WHERE sal=maxsal) WHERE sal && presal or presal is null;
输出结果如下：
DEPTNO ENAME
---------- ---------- ----------
在实际生产环境中，此类应用还是蛮多的，譬如如何查询每个时段耗时最大的工单。当然，通过上述演示，我们也看出了group by函数的局限性。
关于LAST_VALUE和LAG函数的具体应用及说明，可参考Oracle官方文档：
阅读(...) 评论()博客分类：
条件分支语句用于依据特定情况选择要执行的操作，PL/SQL提供了三种条件分支语句：if-then,if-then-else,if-then-else –then-
A:简单条件判断：
简单条件判断用于执行单一条件判断，如果满足特定条件，则会执行相应操作：如果不满足条件则会退出下面我们用scott登陆用emp表举列子如下；
--简单的条件判断 if – then
--问题：编写一个过程，可以输入一个雇员名，
--如果该雇员的工资低于2000，就给该员工工资增加10%。
Create or replace procedure por_1(v_ename varchar2) is
V_sal emp.sal%
Select sal into v_sal form emp where ename=v_
If v_sal&2000
V_sal:=v_sal*0.1;
Update emp set sal=v_sal where ename=_
如上所示，我们在调用这个过程的时候会这样 exec pro_(‘king’);执行之后king的工资就会在原来的基础上乘以0.1；
B:二重条件分支
二重条件分支是指根据条件来选择两种可能性，
--问题：编写一个过程，可以输入一个雇员名，
--如果该雇员的补助不是0就在原来的基础上增加100；
--如果补助为0就把补助设为200；
create or replace procedure pro_2(v_ename varchar2) is
select nvl(comm,0) into v_comm from emp where ename=v_
dbms_output.put_line('comm:'||v_comm);
if v_comm&&0 then
v_comm:=v_comm+100;
v_comm:=200;
update emp set comm=v_comm where ename=v_
C:多重条件分支
多重条件分支用于执行最复杂的条件分支操作
--问题：编写一个过程，可以输入一个雇员编号，
--如果该雇员的职位是PRESIDENT就给他的工资增加1000，
--如果该雇员的职位是MANAGER就给他的工资增加500，
--其它职位的雇员工资增加200。
Create or replace procedure pro_3(v_no number) is
v_sal emp.sal%
v_job emp.job%
Select job,sal into v_job,v_sal from emp where empno=v_
v_job=’ PRESIDENT’
v_sal:=v_sal+1000;
Else if v_job=’MANAGER’ then
v_SAL=v_sal+500;
v_sal=v_sal+200;
update emp set sal=v_sal where empno=v_
D:case 条件控制
Case语句可以说是多重条件语句的超级版，用于判断多条件时，比IF 的多重条件语句要好用点
--问题：编写一个过程，可以输入一个雇员编号，
--如果该雇员的职位是PRESIDENT就给他的工资增加1000，
--如果该雇员的职位是MANAGER就给他的工资增加500，
300--ANALYST 增加200。--SALESMAN 加100
create or replace procedure pro_11(v_empno number) is
v_job emp.job%
v_sal emp.sal%
select job,sal into v_job,v_sal from emp where empno=v_
case v_job
when 'PRESIDENT' then
v_sal:=v_sal+1000;
when 'MANAGER' then
v_sal:=v_sal+500;
when 'CLERK' then
v_sal:=v_sal+300;
when 'ANALYST' then
v_sal:=v_sal+200;
when 'SALESMAN' then
v_sal:=v_sal+100;
--更新数据
update emp set sal=v_sal where empno=v_
为了在编写pl/sql快中重复执行一条语句或者一组语句，可以使用循环控制结构。循环结构有基本循环，while循环,fro循环，下面我们来简单的结束使用这三种循环语句的用法。
A:基本循环
在pl/sql中最简单格式的循环语句是基本循环语句格式如下：
…..执行代码
Exit when…..
I int :=1;
Insert into table_1 valuse(1);
Exit when I:=10;
B:while 循环：
对于whil循环来说只有当条件为true时，才会执行循环列子如下：
I int :=1;
While i&10 loop
Insert into table _1
C:FOR 循环
FRO循环可以在某一个范围内遍历循环，当使用FOR循环时，Oracle会隐含定义循环变量举例：
For I in reverse 1…..10 loop
Insert into table values(1);
如上所示,当执行pl/sql快时，会为teble表插入10条记录，因为指定了Reverse选项，所以插入数据的顺序为10,9.8….1,；
浏览: 7476 次
来自: 成都
轻松一下，哈哈
欺骗我的感情
电话多少，急求，好人一生平安！
(window.slotbydup=window.slotbydup || []).push({
id: '4773203',
container: s,
size: '200,200',
display: 'inlay-fix'