oracle_存储过程练习题

1．创建用户kaifa（密码亦为kaifa），并分配connect,create table,resource权限。

CREATE user KAIFA IDENTIFIED BY KAIFA DEFAULT TABLESPACE HOSDATA TEMPOARY TABLESPACE TEMPDA TA;

GRANT CONNECT , CREATE TABLE , RESOURCE TO KAIFA

2．在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。

概要表（CCB_GYB）信息如下：

--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--币种

)

RETURN NUMBER

IS

VAMOUNT NUMBER ;

VDATE Date;

BEGIN

SELECT ACCOUNTING_DATE INTO VDATE FROM CCB_GYB

Where 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 = '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

实现此功能，并能在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

CREATE OR REPLACE Procedure SCOUNT

Is

VCOUNT1 Varchar2(10);

VCOUNT2 Varchar2(10);

VCOUNT3 Varchar2(10);

Begin

Select Count(*) Into VCOUNT1 From CJ Where SCORE Between0And60;

Select Count(*) Into VCOUNT2 From CJ Where SCORE Between61And80;

Select Count(*) Into VCOUNT3 From CJ Where SCORE Between81And100;

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）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下：

姓名语文数学英语总成绩

---------- ---------- ---------- ---------- ----------

李四78 85 78 241

张三80 86 75 241

总分158 171 153 482

(Select D.SSNAME,D.SSOCRE 数学,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 And A.XK='语文'And B.XK='数学'And C.XK='英语')D

Group By D.SSNAME,D.SSOCRE,D.YSCORE,D.ESCORE

)

Union All

(Select'总分'姓名,Sum(FF.BB) 数学 ,Sum() 语文,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