oracle-存储过程练习题

1 创建用户 kaifa 密码亦为 kaifa 并分配 connect create table resource 权限 CREATE user KAIFA IDENTIFIED BY KAIFA DEFAULT TABLESPACE HOSDATA TEMPOARY TABLESPACE TEMPDATA GRANT CONNECT CREATE TABLE RESOURCE TO KAIFA 2 在做报表统计时 需要根据报表日期和币种从概要表中查询本期余额 概要表 CCB GYB 信息如下 ACCOUNTING DATEDATE 报表日期 唯一索引 RMB YTD BALANCENUMBER 人民币余额 CNY YTD BALANCENUMBER 本位币余额 USD YTD BALANCENUMBER 外币折美元余额 其中币种代码如下 RMB 人民币 CNY 本位币 USD 外币折美元 如果币种为 RMB 则取出人民币余额作为本期余额 为CNY 则取本位币余额 为 USD 则取外币折美元余额 请编写一个函数 GetCurrBal qrp rq IN VARCHAR2 报表日期 qrp code IN VARCHAR2 币种 CREATECREATE OROR REPLACEREPLACE FUNCTIONFUNCTION GetCurrBal Vqrp rq DateDate 报表日期 Vqrp code VARCHAR2VARCHAR2 币种 RETURNRETURN NUMBERNUMBER ISIS VAMOUNT NUMBERNUMBER VDATE DateDate BEGINBEGIN SELECTSELECT ACCOUNTING DATE INTOINTO VDATE FROMFROM CCB GYB WhereWhere ACCOUNTING DATE Vqrp rq IFIF Vqrp code RMB THENTHEN SELECTSELECT RMB YTD BALANCE INTOINTO VAMOUNT FROMFROM CCB GYB WHEREWHERE Vqrp code RMB ANDAND ACCOUNTING DATE VDATE ELSEELSE IFIF Vqrp code CNY THENTHEN SELECTSELECT CNY YTD BALANCE INTOINTO VAMOUNT FROMFROM CCB GYB WHEREWHERE Vqrp code CNY ELSEELSE SELECTSELECT USD YTD BALANCE INTOINTO VAMOUNT FROMFROM CCB GYB WHEREWHERE Vqrp code USD ENDEND IFIF ENDEND IFIF COMMITCOMMIT RETURNRETURN VAMOUNT ENDEND 对多行处理 用游标 多单行处理 用 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 ACCOUNTING DATE 3 假设有张学生成绩表 CJ 如下 姓名 学科 成绩 张三 语文 80 张三 数学 86 张三 英语 75 李四 语文 78 李四 数学 85 李四 英语 78 现有需求如下 1 要求统计分数段的人数 显示结果为 成绩 人数 0 成绩 60 0 60 成绩 80 0 80 成绩 100 5 CREATECREATE OROR REPLACEREPLACE ProcedureProcedure SCOUNT IsIs VCOUNT1 Varchar2Varchar2 10 VCOUNT2 Varchar2Varchar2 10 VCOUNT3 Varchar2Varchar2 10 BeginBegin SelectSelect CountCount IntoInto VCOUNT1 FromFrom CJ WhereWhere SCORE BetweenBetween 0 AndAnd 60 SelectSelect CountCount IntoInto VCOUNT2 FromFrom CJ WhereWhere SCORE BetweenBetween 61 AndAnd 80 SelectSelect CountCount IntoInto VCOUNT3 FromFrom CJ WhereWhere SCORE BetweenBetween 81 AndAnd 100 dbms output put line 分数 人数 dbms output put line 0 成绩 60 VCOUNT1 dbms output put line 60 成绩 80 VCOUNT2 dbms output put line 81 成绩 100 VCOUNT3 EndEnd 2 要求根据姓名 把各科成绩显示在一条记录里 显示结果如下 姓名 语文 数学 英语 总成绩 李四 78 85 78 241 张三 80 86 75 241 总分 158 171 153 482 SelectSelect D SSNAME D SSOCRE 数学 D YSCORE 语文 D ESCORE 英语 SumSum D SSOCRE D YSCORE D ESCORE 总成绩 FromFrom SelectSelect A SNAME SSNAME A SCORE SSOCRE B SCORE YSCORE C SCORE ESCORE FromFrom CJ A CJ B CJ C WhereWhere A SNAME B SNAME AndAnd C SNAME A SNAME AndAnd A XK 语文 AndAnd B XK 数学 AndAnd C XK 英语 D GroupGroup ByBy D SSNAME D SSOCRE D YSCORE D ESCORE UnionUnion AllAll SelectSelect 总分 姓名 SumSum FF BB 数学 SumSum FF CC 语文 SumSum FF DD 英语 SumSum FF EE 总成绩 FromFrom SelectSelect D SSNAME AA D SSOCRE BB D YSCORE CC D ESCORE DD SumSum D SSOCRE D YSCORE D ESCORE EE FromFrom SelectSelect A SNAME SSNAME A SCORE SSOCRE B SCORE YSCORE C SCORE ESCORE FromFrom CJ A CJ B CJ C WhereWhere A SNAME B SNAME AndAnd C SNAME A SNAME AndAnd A XK 语文 AndAnd B XK 数学 AndAnd C XK 英语 D GroupGroup ByBy D SSNAME D SSOCRE D YSCORE D ESCORE FF 行转列 SelectSelect SNAME SUMSUM DECODE XK 语文 SCORE 0 语文 SumSum DECODE XK 数学 SCORE 0 数学 SumSum DECODE XK 英语 SCORE 0 英语 SUMSUM SCORE AA FromFrom CJ GroupGroup ByBy SNAME UnionUnion AllAll SelectSelect 总分 SumSum DECODE XK 语文 SCORE 0 语文 SumSum DECODE XK 数学 SCORE 0 数学 SumSum DECODE XK 英语 SCORE 0 英语 SUMSUM SCORE FromFrom CJ 使用 SQL 语句或存储过程 显示结果时可用 dbms output 打印出来 实现这两个功能 DBMS OUTPUT PUT LINE 姓名 语文 数学 英语 总成绩 4 某一客户表包含如下信息 INDIVIDUALID 客户 IDVARCHAR2 20 唯一键 BIRTHDATE 出生日期 Date GENDER 性别 VARCHAR2 10 SALARY 月收入NUMBER 10 2 CERT TYPE 证件类型 VARCHAR2 10 CERT NO 证件号码 VARCHAR2 20 CREATED TS 进入系统的时间 TIMESTAMP 现要把该表数据导出成文件 导出的内容格式如下 属性列列长度备注 INDIVIDUALID20 BIRTHDATE8 格式为 yyyymmdd GENDER10 SALARY13 CERT TYPE10 CERT NO20 CREATED TS17 格式为 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 任职时间 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 2 Delete from D S where C 4 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 问题 查询每个部门中月工资最高的 职工号 的 SQL 查询语句如下 Select 职工号 from 职工 E Wh