oracle-存儲過程練習題

1、精選優(yōu)質文檔-傾情為你奉上1 創(chuàng)建用戶kaifa（密碼亦為kaifa）， 并分配connect,create table,resource權限。CREATE user KAIFA IDENTIFIED BY KAIFA DEFAULT TABLESPACE HOSDATATEMPOARY TABLESPACE TEMPDATA;GRANT CONNECT , CREATE TABLE , RESOURCE TO KAIFA2 在做報表統(tǒng)計時，需要根據(jù)報表日期和幣種從概要表中查詢本期余額。概要表（CCB_GYB）信息如下：ACCOUNTING_DATEDATE報表日期（唯一索引）RMB_YTD_

2、BALANCENUMBER人民幣余額CNY_YTD_BALANCENUMBER本位幣余額USD_YTD_BALANCENUMBER外幣折美元余額其中幣種代碼如下： -RMB 人民幣 -CNY 本位幣 -USD 外幣折美元如果幣種為RMB，則取出人民幣余額作為本期余額；為CNY，則取本位幣余額；為USD則取外幣折美元余額。請編寫一個函數(shù)GetCurrBal( qrp_rq IN VARCHAR2, -報表日期 qrp_code IN VARCHAR2 -幣種 )CREATE OR REPLACE FUNCTION GetCurrBal( Vqrp_rq Date , -報表日期 Vqrp_cod

3、e VARCHAR2 -幣種 )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= 'RMB'AND ACCOUNTING_DATE= VDATE; ELSE IF Vqrp_code

4、 = 'CNY' THEN SELECT CNY_YTD_BALANCE 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 實現(xiàn)此功能，并能在sqlplus里調用。其中建表語句如下：create table CCB_GY

5、B( ACCOUNTING_DATE DATE, RMB_YTD_BALANCE NUMBER, CNY_YTD_BALANCE NUMBER, USD_YTD_BALANCE NUMBER)；創(chuàng)建索引：create unique index CCB_GYB_IDX on CCB_GYB (ACCOUNTING_DATE)；3 假設有張學生成績表(CJ)如下 姓名 學科 成績 張三 語文 80 張三 數(shù)學 86 張三 英語 75 李四 語文 78 李四 數(shù)學 85 李四 英語 78 現(xiàn)有需求如下：（1）要求統(tǒng)計分數(shù)段的人數(shù)。顯示結果為：成績      人

6、數(shù)0<成績<60      0 60<成績<80     0 80<成績<100    5 CREATE OR REPLACE Procedure SCOUNT Is VCOUNT1 Varchar2(10); VCOUNT2 Varchar2(10); VCOUNT3 Varchar2(10); Begin Select Count(*) Into VCOUNT1 From CJ Where SCORE Between 0 And 60; Select Co

7、unt(*) 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 ('分數(shù)'| | 人數(shù)); dbms_output.put_line ('0<成績<60'| |VCOUNT1); dbms_output.put_line ('60<成績<80'| | VCOUNT2); dbms_output.

8、put_line ('81<成績<100'| | VCOUNT3); End;（2）要求根據(jù)姓名，把各科成績顯示在一條記錄里。顯示結果如下：姓名             語文       數(shù)學       英語     總成績- - - - -李四   

9、0;           78         85         78        241張三              

10、80         86         75        241總分              158        171   &#

11、160;    153       482 (Select D.SSNAME,D.SSOCRE 數(shù)學,D.YSCORE 語文,D.ESCORE 英語 ,Sum(D.SSOCRE+D.YSCORE+D.ESCORE) 總成績 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

12、And A.XK='語文' And B.XK='數(shù)學' And C.XK='英語')DGroup By D.SSNAME,D.SSOCRE,D.YSCORE,D.ESCORE)Union All(Select '總分' 姓名,Sum(FF.BB) 數(shù)學 ,Sum(FF.CC) 語文,Sum(FF.DD) 英語,Sum(FF.EE) 總成績 From (Select D.SSNAME AA,D.SSOCRE BB,D.YSCORE CC,D.ESCORE DD,Sum(D.SSOCRE+D.YSCORE+D.ESCORE) EE F

13、rom (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='數(shù)學' And C.XK='英語') DGroup By D.SSNAME,D.SSOCRE,D.YSCORE,D.ESCORE ) FF)行轉列Select SNAME ,SUM(DECODE(XK,'語文',S

14、CORE,0) 語文,Sum(DECODE(XK,'數(shù)學',SCORE,0) 數(shù)學 ,Sum(DECODE(XK,'英語',SCORE,0) 英語 , SUM(SCORE) AA From CJGroup By SNAMEUnion AllSelect '總分' ,Sum(DECODE(XK,'語文',SCORE,0) 語文,Sum(DECODE(XK,'數(shù)學',SCORE,0) 數(shù)學 ,Sum(DECODE(XK,'英語',SCORE,0) 英語 , SUM(SCORE) From CJ 使用SQ

15、L語句或存儲過程（顯示結果時可用dbms_output打印出來）實現(xiàn)這兩個功能。DBMS_OUTPUT.PUT_LINE(姓名 語文 數(shù)學 英語 總成績)4 某一客戶表包含如下信息：INDIVIDUALID客戶IDVARCHAR2(20)（唯一鍵）BIRTHDATE出生日期DateGENDER性別VARCHAR2(10)SALARY月收入NUMBER（10，2）CERT-TYPE證件類型VARCHAR2(10)CERT-NO證件號碼VARCHAR2(20)CREATED-TS進入系統(tǒng)的時間TIMESTAMP現(xiàn)要把該表數(shù)據(jù)導出成文件，導出的內容格式如下:屬性列列長度備注INDIVIDUALID2

16、0BIRTHDATE8格式為：yyyymmddGENDER10SALARY13CERT-TYPE10CERT-NO20CREATED-TS17格式為：yyyymmddhh24missff3要求每個字段列的內容長度是固定的，不足部分由空格補齊，字符串左對齊（右補空格），數(shù)字右對齊。如果列的內容為null，需先進行處理，字符串默認為空格，數(shù)字默認為0，日期默認為,時間戳默認為。請編寫程序實現(xiàn)該導出功能。 創(chuàng)建表腳本：create table tb1010(INDIVIDUALID VARCHAR2(20),BIRTHDATE date,GENDER VARCHAR2(10),SALARY NUMB

17、ER(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狀態(tài)CHAR(1)在某次大批量操作后，數(shù)據(jù)記錄達到100萬，MOBILE_ID估計有2萬個重復，現(xiàn)要求刪除重復的號碼（只保留一條），因為該表是業(yè)務表，刪除時不能影響業(yè)務的正常使用。編寫存儲過程實現(xiàn)刪除重復號碼的功能。要求如下：（1） 為保證刪除的數(shù)據(jù)以

18、后可查，在刪除時要先做備份，備份不成功則不能進行刪除。（2） 要有日志記錄，比如刪除所花時間，刪除成功了多少條，失敗多少條等操作信息。（3） 如果出現(xiàn)性能問題，要跟蹤原因。生成trace文件進行分析，改進程序。6閱讀下列說明，回答問題1至問題5?！菊f明】某工廠的信息管理數(shù)據(jù)庫的部分關系模式如下所示：職工（職工號，姓名，年齡，月工資，部門號，電話，辦公室）部門（部門號，部門名，負責人代碼，任職時間）關系模式的主要屬性、含義及約束如表2-1所示，“職工”和“部門”的關系示例分別如表2-2和表2-3所示。表2-1主要屬性、含義及約束 表2-2 “職工”關系表2-3“部門”關系【問題1】根據(jù)上述說明，

19、由SQL定義的“職工”和“部門”的關系模式，以及統(tǒng)計各部門的人數(shù)C、工資總數(shù)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 10