oracle_存储过程练习题

1．创建用户kaifa（密码亦为kaifa），并分配connect,createtable,resource权限。CREATEuserKAIFAIDENTIFIEDBYKAIFADEFAULTTABLESPACEHOSDATATEMPOARYTABLESPACETEMPDATA;GRANTCONNECT,CREATETABLE,RESOURCETOKAIFA2．在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。概要表（CCB_GYB）信息如下：ACCOUNTING_DATEDATE报表日期（唯一索引）RMB_YTD_BALANCENUMBER人民币余额CNY_YTD_BALANCENUMBER本位币余额USD_YTD_BALANCENUMBER外币折美元余额其中币种代码如下：--RMB人民币--CNY本位币--USD外币折美元如果币种为RMB，则取出人民币余额作为本期余额；为CNY，则取本位币余额；为USD则取外币折美元余额。请编写一个函数GetCurrBal(qrp_rqINVARCHAR2,--报表日期qrp_codeINVARCHAR2--币种)CREATEORREPLACEFUNCTIONGetCurrBal(Vqrp_rqDate,--报表日期Vqrp_codeVARCHAR2--币种)RETURNNUMBERISVAMOUNTNUMBER;VDATEDate;BEGINSELECTACCOUNTING_DATEINTOVDATEFROMCCB_GYBWhereACCOUNTING_DATE=Vqrp_rq;IFVqrp_code='RMB'THENSELECTRMB_YTD_BALANCEINTOVAMOUNTFROMCCB_GYBWHEREVqrp_code='RMB'ANDACCOUNTING_DATE=VDATE;ELSEIFVqrp_code='CNY'THENSELECTCNY_YTD_BALANCEINTOVAMOUNTFROMCCB_GYBWHEREVqrp_code='CNY';ELSESELECTUSD_YTD_BALANCEINTOVAMOUNTFROMCCB_GYBWHEREVqrp_code='USD';ENDIF;ENDIF;COMMIT;RETURNVAMOUNT;END;---对多行处理，用游标---多单行处理，用SELECT实现此功能，并能在sqlplus里调用。其中建表语句如下：createtableCCB_GYB(ACCOUNTING_DATEDATE,RMB_YTD_BALANCENUMBER,CNY_YTD_BALANCENUMBER,USD_YTD_BALANCENUMBER)；创建索引：createuniqueindexCCB_GYB_IDXonCCB_GYB(ACCOUNTING_DATE)；3．假设有张学生成绩表(CJ)如下[姓名][学科][成绩]张三语文80张三数学86张三英语75李四语文78李四数学85李四英语78现有需求如下：（1）要求统计分数段的人数。显示结果为：[成绩][人数]0成绩60060成绩80080成绩1005CREATEORREPLACEProcedureSCOUNTIsVCOUNT1Varchar2(10);VCOUNT2Varchar2(10);VCOUNT3Varchar2(10);BeginSelectCount(*)IntoVCOUNT1FromCJWhereSCOREBetween0And60;SelectCount(*)IntoVCOUNT2FromCJWhereSCOREBetween61And80;SelectCount(*)IntoVCOUNT3FromCJWhereSCOREBetween81And100;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);End;（2）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下：姓名语文数学英语总成绩--------------------------------------------------李四788578241张三808675241总分158171153482(SelectD.SSNAME,D.SSOCRE数学,D.YSCORE语文,D.ESCORE英语,Sum(D.SSOCRE+D.YSCORE+D.ESCORE)总成绩From(SelectA.SNAMESSNAME,A.SCORESSOCRE,B.SCOREYSCORE,C.SCOREESCOREFromCJA,CJB,CJCWhereA.SNAME=B.SNAMEAndC.SNAME=A.SNAMEAndA.XK='语文'AndB.XK='数学'AndC.XK='英语')DGroupByD.SSNAME,D.SSOCRE,D.YSCORE,D.ESCORE)UnionAll(Select'总分'姓名,Sum(FF.BB)数学,Sum(FF.CC)语文,Sum(FF.DD)英语,Sum(FF.EE)总成绩From(SelectD.SSNAMEAA,D.SSOCREBB,D.YSCORECC,D.ESCOREDD,Sum(D.SSOCRE+D.YSCORE+D.ESCORE)EEFrom(SelectA.SNAMESSNAME,A.SCORESSOCRE,B.SCOREYSCORE,C.SCOREESCOREFromCJA,CJB,CJCWhereA.SNAME=B.SNAMEAndC.SNAME=A.SNAMEAndA.XK='语文'AndB.XK='数学'AndC.XK='英语')DGroupByD.SSNAME,D.SSOCRE,D.YSCORE,D.ESCORE)FF)行转列SelectSNAME,SUM(DECODE(XK,'语文',SCORE,0))语文,Sum(DECODE(XK,'数学',SCORE,0))数学,Sum(DECODE(XK,'英语',SCORE,0))英语,SUM(SCORE)AAFromCJGroupBySNAMEUnionAllSelect'总分',Sum(DECODE(XK,'语文',SCORE,0))语文,Sum(DECODE(XK,'数学',SCORE,0))数学,Sum(DECODE(XK,'英语',SCORE,0))英语,SUM(SCORE)FromCJ使用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现要把该表数据导出成文件，导出的内容格式如下:属性列列长度备注INDIVIDUALID20BIRTHDATE8格式为：yyyymmddGENDER10SALARY13CERT-TYPE10CERT-NO20CREATED-TS17格式为：yyyymmddhh24missff3要求每个字段列的内容长度是固定的，不足部分由空格补齐，字符串左对齐（右补空格），数字右对齐。如果列的内容为null，需先进行处理，字符串默认为空格，数字默认为0，日期默认为99991231,时间戳默认为99991231000000000。请编写程序实现该导出功能。创建表脚本：createtabletb1010(INDIVIDUALIDVARCHAR2(20),BIRTHDATEdate,GENDERVARCHAR2(10),SALARYNUMBER(10,2),CERT_TYPEVARCHAR2(10),CERT_NOVARCHAR2(20),CREATED_TSTIMESTAMP);5．某语音电话本表信息如下:Call_book_infoMOBILE_ID移动号码VARCHAR2(12)CALLIN_TIME呼入时间DateCALLOUT_TIME呼出时间DateSTATUS状态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分）CreateTable部门（部门号CHAR（1）（a），部门名CHAR（16），负责人代码CHAR（4），任职时间DATE，（b）(职工号)）；CreateTable职工（职工号CHAR（4），姓名CHAR（8），年龄NUMDER（3），月工资NUMDER（4），部门号CHAR（1），电话CHAR（8），办公室CHAR（8），（a）(职工号），(c)(部门号)，CHECK（(d)））；CreateViewD_S(D，C，Totals，Averages)As(Select部门号，(e)from职工(f)；【问题２】对于表2-2、表2-3所示的“职工”和“部门”关系，请指出下列各行是否可以插入，为什么？（3分）（1）1001王新军28100018001234主楼201（2）2003李力281000（3）5802赵晓啸361500680015683号楼503【问题3】在问题1定义的视图D_S上，下面哪个查询或更新是允许执行的，为什么？（3分）(1)UpdateD_SsetD=3whereD=4；(2)DeletefromD_SwhereC＞4；(3)SelectD，AveragesfromD_SwhereC＞(SelectCfromD_SwhereD=:dept)；(4)SelectD，CfromD_SwhereTotals＞10000；(5)Select*fromD_S；【问题４】查询每个部门中月工资最高的“职工号”的SQL查询语句如下：Select职工号from职工EWhere月工资=（SelectMax(月工资)from职工asMwhereM.部门号=E.部门号）（1）请用30字以内文字简要说明该查询语句对查询效率的影响。（3分）（2）对该查询语句进行修改，使它既可以完成相同功能，又可以提高查询效率。（3分）【问题5】假定分别在“职工”关系中的“年龄”和“月工资”字段上创建了索引，如下的Select查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既可以完成相同功能又可以提高查询效率的SQL语句（2分）Select姓