oracle_存储过程练习题

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

2．在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。

其中币种代码如下：

--RMB 人民币

--CNY 本位币

--USD 外币折美元

如果币种为RMB，则取出人民币余额股作为本期余额；为CNY，则取本位币余额；为USD 则取外币折美元余额。

请编写一个函数GetCurrBal(

qrp_rq IN VARCHAR2, --报表日期

qrp_code IN VARCHAR2--币种

)

实现此功能，并能在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 (ACCOUNTING_DATE)；

3．假设有张学生成绩表(CJ)如下

[姓名] [学科] [成绩]

张三语文80

张三数学86

张三英语75

李四语文78

李四数学85

李四英语78

现有需求如下：

（1）要求统计分数段的人数。显示结果为：

[成绩] [人数]

0<成绩<60 0

60<成绩<80 0

80<成绩<100 5

（2）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下：

姓名语文数学英语总成绩

---------- ---------- ---------- ---------- ----------

李四78 85 78 241

张三80 86 75 241

总分158 171 153 482

使用SQL语句或存储过程（显示结果时可用dbms_output打印出来）实现这两个功能。4．某一客户表包含如下信息：

INDIVIDUALID 客户ID VARCHAR2(20)（唯一键）BIRTHDATE 出生日期Date

GENDER 性别VARCHAR2(10)

SALARY 月收入NUMBER（10，2）

CERT-TYPE 证件类型VARCHAR2(10)

CERT-NO 证件号码VARCHAR2(20)

CREATED-TS 进入系统的时间TIMESTAMP

现要把该表数据导出成文件，导出的内容格式如下:

属性列列长度备注

INDIVIDUALID 20

BIRTHDATE 8 格式为：yyyymmdd

GENDER 10

SALARY 13

CERT-TYPE 10

CERT-NO 20

CREATED-TS 17 格式为：yyyymmddhh24missff3 要求每个字段列的内容长度是固定的，不足部分由空格补齐，字符串左对齐（右补空格），数字右对齐。如果列的内容为null，需先进行处理，字符串默认为空格，数字默认为0，日期默认为99991231,时间戳默认为99991231000000000。

请编写程序实现该导出功能。

创建表脚本：

create table tb1010(

INDIVIDUALID VARCHAR2(20),

BIRTHDATE date,

GENDER VARCHAR2(10),

SALARY NUMBER(10,2),

CERT_TYPE VARCHAR2(10),

CERT_NO VARCHAR2(20),

CREATED_TS TIMESTAMP

);

5．某语音电话本表信息如下:

Call_book_info

MOBILE_ID 移动号码VARCHAR2(12)

CALLIN_TIME 呼入时间Date

CALLOUT_TIME 呼出时间Date

STATUS 状态CHAR(1)

在某次大批量操作后，数据记录达到100万，MOBILE_ID估计有2万个重复，现要求删除重复的号码（只保留一条），因为该表是业务表，删除时不能影响业务的正常使用。编写存储过程实现删除重复号码的功能。

要求如下：

（1）为保证删除的数据以后可查，在删除时要先做备份，备份不成功则不能进行删除。（2）要有日志记录，比如删除所花时间，删除成功了多少条，失败多少条等操作信息。（3）如果出现性能问题，要跟踪原因。生成trace文件进行分析，改进程序。

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

【说明】

某工厂的信息管理数据库的部分关系模式如下所示：

职工（职工号，姓名，年龄，月工资，部门号，电话，办公室）

部门（部门号，部门名，负责人代码，任职时间）

关系模式的主要属性、含义及约束如表2-1所示，“职工”和“部门”的关系示例分别如表2-2和表2-3所示。

表2-1主要属性、含义及约束

表2-2 “职工”关系

表2-3“部门”关系

【问题1】

根据上述说明，由SQL定义的“职工”和“部门”的关系模式，以及统计各部门的人数C、工资总数Totals、平均工资Averages的D_S视图如下所示，请在空缺处填入正确的内容。（6分）

Create Table 部门（部门号CHAR（1）（a），

部门名CHAR（16），

负责人代码CHAR（4），

任职时间DA TE，

（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-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 D=4；