oracle-存储过程练习题

1.创建用户kaifa （密码亦为kaifa）
并分配connect,create table,resource 权限。

t
CREATE user KAIFA IDENTIFIED BY KAIFA DEFAULT TABLESPACE HOSDATA
TEMPOARY TABLESPACE TEMPDATA；
GRANT CONNECT . CREATE TABLE • RESOURCE TO KAIFA
2.在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。

其中币种代码如下：
--RMB 人民币
--CNY 本位币
—USD 外币折美元
如果币种为RMB,则取出人民币余额作为本期余额；为CNY,则取本位币余额；为USD 则取外币折美元余额。

请编写一个函数GetCurrBal （
qrp.rq IN VARCHAR2. ―报表日期
qrp_code IN VARCHAR2 —币种
）
CREATE OR REPLACE FUNCTION GetCurrBal（
Vqrp_rq Date •—报表日期
Vqrp.code VARCHAR2 一币种
）
RETURN NUMBER
IS
VAMOUNT NUMBER :
'DATE Date；
BEGIN
SELECT ACCOUNTING.DATE INTO VDATE FROM CCB_GYB
Where ACCOUNTING_DATE = Vqrp_rq；
IF Vqrp_code = 'RMB* THEN
SELECT RMB_YTD_BA1-ANCE INTO VAMOUNT FROM CCB_GYB WHERE Vqip_code二r RMB*
AND ACCOUNTING_DATE- VDATE；
ELSE
IF Vqrp.code = 'CNT THEN
SELECT CNY_YTD_BALANCII INTO VAMOUNT FROM CCB_GYB WHERE Vqip_code二'CNY'；
ELSE
SELECT USD_YTD_BALANCE INTO VAMOUNT FROM CCB_GYB WHERE Vqrp_code-
END IF :
END IF ;
COMMIT;
RETURN VAMOUNT；
END;
■一对多行处理.用游标
一-多单行处理，用SELECT
实现此功能，并能在sqlplus里调用。

其中建表语句如下：
create table CCB_GYB
(
ACCOUNTING_DATE DATE.
RMB_YTD_BALANCE NUMBER.
CNY_YTD_BALANCE NUMBER.
USD_YTD_BALANCE NUMBER
);
创建索引:create unique index CCB__GYB_IDX on CCB.GYB (ACCOUNT I NG.DATE)；
3.假设有学生成绩表(CJ)如下
[] ［学科］［成绩］
三语文80
三数学86
三英语75
四语文78
四数学85
四英语78
现有需求如下：
（1）要求统计分数段的人数。

显示结果为:
［成绩］［人数］
（X成绩<60 0
60〈成绩〈80 0
80〈成绩<100 5
CREATE OR REPLACE Procedure SCOUNT
Is
VC0UNT1 Varchar2(10)； VCOUNT2 Varchar2(10)； VCOUNT3 Varchar2(10)；
Begin
End;
（2）要求根据，把各科成绩显示在一条记录里。

显示结果如下：
语文 数学 英语 总
成绩
四
78
85
78
241
三
80
86
75
241
分
158
171
153
482
语・Sum(D ・SSOCRE+D ・ YSCORE+D. ESCORE) 总成绩 From
(Select A. SNAME SSNAMI: t A. SCORE SSOCRE.B. SCORE YSCORE.C ・ SCORE ESCORE From CJ A .CJ B .CJ C Where A. SNAME=B. SNAME
And C. SNAME-A. SNAME And A.XK 二'语文'And B. XK-r 数学'And C.XK 』英语’)D
Select Select Count(♦) Count(♦) Into Into Select
Count(♦)
Into
dbms_output ・ put_line dbms_output ・ put. 1ine dbms_output ・ put. 1ine dbms_output ・ put_line VCOUNT1 VC0UNT2 VC0UNT3 C 分數' From CJ Where SCORE Between 0 And
From CJ Where SCORE Between 61 From CJ Where SCORE Between 81 '4 I
C0〈成^＜60* | | C60＜成绩＜80‘ | | C8K 成绩COOT |
'人数'
M IVCOUNTl)； * I| VCOINT2)；
4
I VCOUNT3)；
60； And 80； And 100；
(Select D. SSNAME.D. SSOCRE
数 学 ，D. YSCORE 语 文 .D.ESCORE
Group By 1). SSNAME. D. SSOCRE.D. YSCORE.D. ESCORE )
Union All
(Select '总分1•Sum(FF.BB)数学,Sum(FF. CC)语文.Sum(FF. DD)英语.Sum(FF.EE)总成绩From
(Select D. SSNAME AA. D. SSOCRE BB.D. YSCORE CC. D. ESCORE DD. Sum(D. SSOCRE+D. YSCORE+D. ESCORIi) EE From
(Select A. SNAME SSNAMIi • A. SCORE SSOCRE. B. SCORE YSCORE.C. SCORE ESCORE From CJ A ・CJ B .CJ C Where A. SNA'IE二B.
SNAME
And C. SNAME=A. SNAME And A. XK=* 语文'And B.XK-'数学'And C. XK* 英语J D
Group By I). SSNAME, D. SSOCRE ・ D. YSCORE ・ D. ESCORK
)FF)
Select SNAME .SUM(DECODE(XK/ 语文SCORE.0))语文.Sum(DECODE(XK/ 数学..SCORE.0))数
学.Sum (DECODE (XK ■'英语\ SCORE. 0))英语・SUM (SCORE) AA From CJ
Group By SNAME
Union All
Select '总分'.Sum (DECODE (XK.1语文\ SCORE. 0))语文• Sum (DECODE (XK J 数学• .SCORE. 0))数学.S UB (DECODE (XK, * 英语*. SCORE. 0))英语・SUM (SCORE) From CJ
使用SQL语句或存储过程（显示结果时可用dbms output打印出来）实现这两个功能。

4.某一客户表包含如下信息:
IND1VIDUALID
BIRTHDATE
DBMS OUTPUT. PUT丄INE(" '语文’'数学''英语'
客户ID 出
生日期
VARCHAR2(20)(唯一键)
Date
GENDER 性别
VARCHAR2(10) SALARY 月收入
NUMBER (10, 2)
CERT-TYPE 证件类型
VARCHAR2(10) CERT-NO 证件
VARCHAR2(20) CREATED-TS
进入系统的时间 TIMESTAMP
现要把该表数据导出成文件， 导出的容格式如下：
属性列 列长度
备注
IND1VIDUALID 20
BIRTHDATE 8
格式为： yyyymmdd
GENDER 10
SALARY 13
CERT-TYPE 10
CERT-NO 20
CREATED-TS
17
格式为： yyy ymmddhh24m i s s ff3
要求每个字段列的容长度是固定的，不足部分由空格补齐，字符串左对齐（右补空格）, 数字右对齐。

如果列的容为null,需先进行处理，字符串默认为空格，数字默认为0, 日期默认为99991231,时间戳默认为99991231000000000c
请编写程序实现该导出功能。

）; 5.
某语音本表信息如下: Call book info MOB1LE_1D 移动
VARCHAR2(12) CALLING IME 呼入时间 Date CALLOUT_T1ME 呼出时间 Date STATUS 状态
CHAR(l)
在某次大批量操作后，数据记录达到100万，MOBILE.ID 估计有2万个重复・现要求删除重 复的（只保留一条），因为该表是业务表，删除时不能影响业务的正常使用。

编写存储过程 实现删除重复的功能。

要求如下：
（1） 为保证删除的数据以后可查，在删除时要先做备份，备份不成功则不能进行删除。

创建表脚本：
create table INDIVIDUAL1D tbl010( VARCHAR2(20).
BIRTHDATE GENDER SALARY CERT TYPE CERT NO CREATED TS
date.
VARCHAR2(10). NUMBER (10.2). VARCHAR2O0). VARCHAR2(20).
TIMESTAMP
（2）要有日志记录，比如删除所花时间，删除成功了多少条，失败多少条等操作信息。

（3）如果出现性能问題，要跟踪原因。

生成trace文件进行分析，改进程序。

6.阅读下列说明，回答问题1至问题5。

【说明】
某工厂的信息管理数据库的部分关系模式如下所示：
职工(职工号，，年龄，月工资，部门号，，办公室)
部门(部门号，部门名，负责人代码，任职时间)
关系模式的主要属性、含义及约束如表2-1所示，'‘职工”和“部门”的关系示例分别如表2-2和表2-3所不。

表2-1主要属性.含义及约束
【问题1】
根据上述说明，由SQL定义的“职工”和“部门”的关系模式，以及统计各部门的人数C、工资总数Totals.平均工资Averages的D_S视图如下所示，请在空缺处填入正确的容。

(6分)
Create Table 部门(部门号CHAR (1) (a),
部门名CHAR (16),
负责人代码CHAR (4),
任职时间DATE.
(b)(职工号))；
Create Table 职工(职工号CHAR (4),
CHAR (8),
年龄NUMDER (3), 月工资NUMDER (4), 部门号CHAR (1),
CHAR (8),
办公室CHAR (8),
(a)(职工号)，
(c)(部门号)，
CHECK ( (d)))；
Create View D S(D, C, Totals, Averages) As
(Select 部门号，(e) from职工
(f);
【问题2】
对于表2-2、表2-3所示的"职工”和“部门"关系，请指出下列各行是否可以插入, 为什么？(3分)
(1)1001 王新军28 1000 1 8001234 主楼201
(2)2003 力28 1000
(3)5802 晓啸36 1500 6 8001568 3 号楼503
【问题3】
在问题1定义的视图D_S上，下面哪个查询或更新是允许执行的，为什么？(3分)
(1)Update D S set D=3 where I>4；
(2)Delete from D S where 04；
(3)Select D, Averages from D_S
where C> (Select C from D S where D二：dept)；
(4)Select D, C from D_S
where Totals>10000；
(5)Select * from D_S；
【问题4】
查询每个部门中月工资最高的'‘职工号”的SQL查询语句如下：
Select职工号from职工E
Where月工资=(Select Max(月工资)
from 职工as M
where M.部门号二E.部门号)
(1)请用30字以文字简要说明该查询语句对查询效率的影响。

(3分)
（2）对该查询语句进行修改，使它既可以完成相同功能，又可以提髙查询效率。

（3分）【问题5】假定分别在"职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的Select查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同功能又可以提高查询效率的SQL语句（2分）Select ♦年龄，月工资from职工
where 年龄＞45 or 月工资＜1000；。