oracle_存储过程练习题

1 1． 创建用户 kaifa（密码亦为 kaifa），并分配 connect,create table,resource权限。 2． 在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。 概要表（CCB_GYB）信息如下： ACCOUNTING_DATE RMB_YTD_BALANCE CNY_YTD_BALANCE USD_YTD_BALANCE DATE NUMBER NUMBER NUMBER 报表日期（唯一索引） 人民币余额 本位币余额 外币折美元余额 其中币种代码如下： --RMB人民币 --CNY本位币 --USD外币折美元 如果币种为 RMB， 则取出人民币余额股作为本期余额； 为CNY， 则取本位币余额； 为USD 则取外币折美元余额。 请编写一个函数 GetCurrBal( qrp_rqININ VARCHAR2VARCHAR2,--报表日期 qrp_code ININ VARCHAR2VARCHAR2--币种 ) 实现此功能，并能在 sqlplus 里调用。 其中建表语句如下： createcreate tabletable CCB_GYB ( ACCOUNTING_DATE DATEDATE, RMB_YTD_BALANCE NUMBERNUMBER, CNY_YTD_BALANCE NUMBERNUMBER, USD_YTD_BALANCE NUMBERNUMBER )； 创 建 索 引 ： createcreateuniqueuniqueindexindexCCB_GYB_IDXononCCB_GYB (ACCOUNTING_DATE)； 3． 假设有张学生成绩表(CJ)如下 [姓名] [学科] [成绩] 张三 语文 80 张三 数学 86 张三 英语 75 李四 语文 78 李四 数学 85 李四 英语 78 现有需求如下： （1）要求统计分数段的人数。显示结果为： [成绩] [人数] 0成绩60 0 2 60成绩80 0 80成绩1005 （2）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下： 姓名语文数学英语总成绩 ---------- ---------- ---------- ---------- ---------- 李四788578241 张三808675241 总分158171153 482 使用 SQL 语句或存储过程（显示结果时可用dbms_output 打印出来）实现这两个功能。 4． 某一客户表包含如下信息： INDIVIDUALID BIRTHDATE GENDER SALARY CERT-TYPE CERT-NO CREATED-TS 属性列 INDIVIDUALID BIRTHDATE GENDER SALARY CERT-TYPE CERT-NO CREATED-TS 客户 ID 出生日期 性别 月收入 证件类型 证件号码 进入系统的时间 列长度 20 8 10 13 10 20 17 备注 格式为：yyyymmdd 格式为：yyyymmddhh24missff3 VARCHAR2(20)（唯一键） Date VARCHAR2(10) NUMBER（10，2） VARCHAR2(10) VARCHAR2(20) TIMESTAMP 现要把该表数据导出成文件，导出的内容格式如下: 要求每个字段列的内容长度是固定的， 不足部分由空格补齐， 字符串左对齐 （右补空格） ， 数字右对齐。如果列的内容为null，需先进行处理，字符串默认为空格，数字默认为0， 日期默认为 99991231,时间戳默认为 99991231000000000。 请编写程序实现该导出功能。 创建表脚本： createcreate tabletable tb1010( INDIVIDUALID VARCHAR2VARCHAR2(20), BIRTHDATEdatedate, GENDERVARCHAR2VARCHAR2(10), SALARYNUMBERNUMBER(10,2), CERT_TYPEVARCHAR2VARCHAR2(10), 3 CERT_NOVARCHAR2VARCHAR2(20), CREATED_TSTIMESTAMPTIMESTAMP ); 5． 某语音电话本表信息如下: Call_book_info MOBILE_ID CALLIN_TIME CALLOUT_TIME STATUS 移动号码 呼入时间 呼出时间 状态 VARCHAR2(12) Date Date CHAR(1) 在某次大批量操作后，数据记录达到100 万，MOBILE_ID 估计有 2 万个重复，现要求删除 重复的号码（只保留一条），因为该表是业务表，删除时不能影响业务的正常使用。编写存 储过程实现删除重复号码的功能。 要求如下： （1）为保证删除的数据以后可查，在删除时要先做备份，备份不成功则不能进行删除。 （2）要有日志记录，比如删除所花时间，删除成功了多少条，失败多少条等操作信息。 （3）如果出现性能问题，要跟踪原因。生成trace 文件进行分析，改进程序。 6．阅读下列说明，回答问题1 至问题 5。 【说明】 某工厂的信息管理数据库的部分关系模式如下所示： 职工（职工号，姓名，年龄，月工资，部门号，电话，办公室） 部门（部门号，部门名，负责人代码，任职时间） 关系模式的主要属性、含义及约束如表 2-1 所示，“职工”和“部门”的关系示例分别如表 2-2 和表 2-3 所示。 表 2-1 主要属性、含义及约束 表 2-2 “职工”关系 4 表2-3“部门”关系 【问题 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-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