《oracle存储过程练习题》由会员分享，可在线阅读，更多相关《oracle存储过程练习题（8页珍藏版）》请在金锄头文库上搜索。
1、1 创建用户kaifa（密码亦为kaifa）， 并分配connect,create table,resource权限。CREATE user KAIFA IDENTIFIED BY KAIFA DEFAULT TABLESPACE HOSDATATEMPOARY TABLESPACE TEMPDATA;GRANT CONNECT , CREATE TABLE , RESOURCE TO KAIFA2 在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。概要表（CCB_GYB）信息如下：ACCOUNTING_DATEDATE报表日期（唯一索引）RMB_YTD_BALANCENUMBER
2、人民币余额CNY_YTD_BALANCENUMBER本位币余额USD_YTD_BALANCENUMBER外币折美元余额其中币种代码如下： -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 -币
3、种 )RETURN NUMBERIS VAMOUNT NUMBER ;VDATE Date;BEGINSELECT ACCOUNTING_DATE INTO VDATE FROM CCB_GYBWhere ACCOUNTING_DATE = Vqrp_rq;IF Vqrp_code = RMB THEN SELECT RMB_YTD_BALANCE INTO VAMOUNT FROM CCB_GYB WHERE Vqrp_code= RMBAND ACCOUNTING_DATE= VDATE; ELSE IF Vqrp_code = CNY THEN SELECT CNY_YTD_BALANC
4、E INTO VAMOUNT FROM CCB_GYB WHERE Vqrp_code= CNY;ELSE SELECT USD_YTD_BALANCE INTO VAMOUNT FROM CCB_GYB WHERE Vqrp_code= USD;END IF ;END IF ;COMMIT;RETURN VAMOUNT;END;-对多行处理，用游标-多单行处理，用SELECT 实现此功能，并能在sqlplus里调用。其中建表语句如下：create table CCB_GYB( ACCOUNTING_DATE DATE, RMB_YTD_BALANCE NUMBER, CNY_YTD_BALA
5、NCE 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 CREATE OR REPLACE Procedure SCOUNT Is VCOUNT1 Varchar2(10)
6、; VCOUNT2 Varchar2(10); VCOUNT3 Varchar2(10); Begin Select Count(*) Into VCOUNT1 From CJ Where SCORE Between 0 And 60; Select Count(*) Into VCOUNT2 From CJ Where SCORE Between 61 And 80; Select Count(*) Into VCOUNT3 From CJ Where SCORE Between 81 And 100;dbms_output.put_line (分数| | 人数); dbms_output.
7、put_line (0成绩60| |VCOUNT1); dbms_output.put_line (60成绩80| | VCOUNT2); dbms_output.put_line (81成绩100| | VCOUNT3); End;（2）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下：姓名 语文 数学 英语 总成绩- - - - -李四 78 85 78 241张三 80 86 75 241总分 158 171 153482 (Select D.SSNAME,D.SSOCRE 数学,D.YSCORE 语文,D.ESCORE 英语 ,Sum(D.SSOCRE+D.YSCORE+D.ES
8、CORE) 总成绩 From (Select A.SNAME SSNAME ,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=数学 And C.XK=英语)DGroup By D.SSNAME,D.SSOCRE,D.YSCORE,D.ESCORE)Union All(Select 总分 姓名,Sum(FF.BB) 数学 ,Sum(FF.CC) 语文,Sum(FF.DD) 英语,Sum(
9、FF.EE) 总成绩 From (Select D.SSNAME AA,D.SSOCRE BB,D.YSCORE CC,D.ESCORE DD,Sum(D.SSOCRE+D.YSCORE+D.ESCORE) EE From (Select A.SNAME SSNAME ,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=数学 And C.XK=英语) DGroup By D.SSNAME
10、,D.SSOCRE,D.YSCORE,D.ESCORE ) FF)行转列Select SNAME ,SUM(DECODE(XK,语文,SCORE,0) 语文,Sum(DECODE(XK,数学,SCORE,0) 数学 ,Sum(DECODE(XK,英语,SCORE,0) 英语 , SUM(SCORE) AA From CJGroup By SNAMEUnion AllSelect 总分 ,Sum(DECODE(XK,语文,SCORE,0) 语文,Sum(DECODE(XK,数学,SCORE,0) 数学 ,Sum(DECODE(XK,英语,SCORE,0) 英语 , SUM(SCORE) From
11、 CJ 使用SQL语句或存储过程（显示结果时可用dbms_output打印出来）实现这两个功能。DBMS_OUTPUT.PUT_LINE(姓名 语文 数学 英语 总成绩)4 某一客户表包含如下信息：INDIVIDUALID客户IDVARCHAR2(20)（唯一键）BIRTHDATE出生日期DateGENDER性别VARCHAR2(10)SALARY月收入NUMBER（10，2）CERT-TYPE证件类型VARCHAR2(10)CERT-NO证件号码VARCHAR2(20)CREATED-TS进入系统的时间TIMESTAMP现要把该表数据导出成文件，导出的内容格式如下:属性列列长度备注INDIV
12、IDUALID20BIRTHDATE8格式为：yyyymmddGENDER10SALARY13CERT-TYPE10CERT-NO20CREATED-TS17格式为：yyyymmddhh24missff3要求每个字段列的内容长度是固定的，不足部分由空格补齐，字符串左对齐（右补空格），数字右对齐。如果列的内容为null，需先进行处理，字符串默认为空格，数字默认为0，日期默认为99991231,时间戳默认为99991231000000000。请编写程序实现该导出功能。 创建表脚本：create table tb1010(INDIVIDUALID VARCHAR2(20),BIRTHDATE dat
13、e,GENDER VARCHAR2(10),SALARY NUMBER(10,2),CERT_TYPE VARCHAR2(10),CERT_NO VARCHAR2(20),CREATED_TS TIMESTAMP);5 某语音电话本表信息如下:Call_book_infoMOBILE_ID移动号码VARCHAR2(12) CALLIN_TIME呼入时间DateCALLOUT_TIME呼出时间DateSTATUS状态CHAR(1)在某次大批量操作后，数据记录达到100万，MOBILE_ID估计有2万个重复，现要求删除重复的号码（只保留一条），因为该表是业务表，删除时不能影响业务的正常使用。编写存储过程实现删除重复号码的功能。要求如下：（1） 为保证删除的数据以后可查，在删除时要先做备份，备份不成功则不能进行删除。（2） 要有日志记录，比如删除所花时间，删除成功了多少条，
