一个简单的PL/SQL小程序-CSDN博客

昨天一个学妹问我一个关于ORACLE这门课Assignment的问题，关于PL/SQL的，正好我在复习SQL方面的东西，所以就顺便的帮她看了一下，写了一小段代码。

题目如下：

Write a stored procedure named MAXCUST that accepts (input parameter) the registration number of a car and returns (output parameter) the customer NAME of the customer who has rented the car the greatest number of times. If more than one customer is tied for the greatest number of rentals, return the customer who rented the car most recently.

Now, write an anonymous block that calls the stored procedure and displays the customer number, customer name and customer address.

代码如下：

SET SERVEROUTPUT ON; --打开Oracle的输出服务

CREATE OR REPLACE PROCEDURE MAXCUST (REGISTRATION_NUMBER IN VARCHAR2, I_CUST_NAME OUT VARCHAR2)

CURSOR CUR_BOOKING(REG I_BOOKING.REGISTRATION%TYPE) IS --定义一个游标，将汽车Registration传入，选择出来用户和他们的租赁次数

SELECT CUST_NO, COUNT(*) "TIME"

FROM I_BOOKING

WHERE I_BOOKING.REGISTRATION = REG

GROUP BY CUST_NO;

REC_BOOKING CUR_BOOKING%ROWTYPE;

V_CUST_NO I_BOOKING.CUST_NO%TYPE;

V_MAX_TIME NUMBER(3);

V_MAX_DATE DATE;

TEMP_DATE DATE;

BEGIN

V_MAX_TIME := -1;

V_CUST_NO := 0;

OPEN CUR_BOOKING(REGISTRATION_NUMBER); -- 打开游标

LOOP

FETCH CUR_BOOKING INTO REC_BOOKING;

EXIT WHEN CUR_BOOKING%NOTFOUND;

IF (V_MAX_TIME < REC_BOOKING.TIME) THEN

V_MAX_TIME := REC_BOOKING.TIME;

V_CUST_NO := REC_BOOKING.CUST_NO;

SELECT MAX(DATE_RENT_START) INTO TEMP_DATE

FROM I_BOOKING

WHERE CUST_NO = REC_BOOKING.CUST_NO AND REGISTRATION = REGISTRATION_NUMBER;

ELSIF (V_MAX_TIME = REC_BOOKING.TIME) THEN

SELECT MAX(DATE_RENT_START) INTO TEMP_DATE

FROM I_BOOKING

WHERE CUST_NO = REC_BOOKING.CUST_NO;

IF (V_MAX_DATE < TEMP_DATE) THEN

V_MAX_DATE := TEMP_DATE;

V_CUST_NO := REC_BOOKING.CUST_NO;

END IF;

END LOOP; /*NOW GET THE MAX TIMES*/

SELECT CUST_NAME INTO I_CUST_NAME

FROM I_CUSTOMER

WHERE CUST_NO = V_CUST_NO;

CLOSE CUR_BOOKING; -- 关闭游标

EXCEPTION

WHEN NO_DATA_FOUND THEN -- 抛出异常

DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');

END;

--THE ANONYMOUS BLOCK ------------------------------------------------------------------

DECLARE

V_REG_NO I_BOOKING.REGISTRATION%TYPE;

V_CUST_NAME I_CUSTOMER.CUST_NAME%TYPE;

V_CUST_NO I_CUSTOMER.CUST_NO%TYPE;

V_ADDRESS I_CUSTOMER.ADDRESS%TYPE;

BEGIN

V_REG_NO := '&reg';

MAXCUST(V_REG_NO,V_CUST_NAME);

SELECT CUST_NO,ADDRESS INTO V_CUST_NO, V_ADDRESS

FROM I_CUSTOMER

WHERE CUST_NAME = V_CUST_NAME;

DBMS_OUTPUT.PUT_LINE(CHR(10)||'Customer who has rented the car the greatest number of times');

DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------' );

DBMS_OUTPUT.PUT_LINE('Customer Name: ' || V_CUST_NAME ));

DBMS_OUTPUT.PUT_LINE('Customer Number: ' || V_CUST_NO );

DBMS_OUTPUT.PUT_LINE('Customer Address: ' || V_ADDRESS );

END;

表结构：

I_BOOKING

Name	Null?	Type
BOOKING_NO	NOT NULL	NUMBER(5)
CUST_NO		NUMBER(5)
DATE_RESERVED		DATE
RESERVED_BY		VARCHAR2(12)
DATE_RENT_START		DATE
RENTAL_PERIOD		NUMBER(3)
REGISTRATION		VARCHAR2(7)
MODEL_NAME		VARCHAR2(8)
MILES_OUT		NUMBER(6)
MILES_IN		NUMBER(6)
AMOUNT_DUE		NUMBER(6,2)
PAID		CHAR(1)

I_CUSTOMER

Name	Null?	Type
CUST_NO	NOT NULL	NUMBER(5)
CUST_NAME	NOT NULL	VARCHAR2(20)
ADDRESS		VARCHAR2(20)
TOWN		VARCHAR2(20)
COUNTY		VARCHAR2(20)
POST_CODE		VARCHAR2(10)
CONTACT		VARCHAR2(20)
PAY_METHOD		CHAR(1)