一个简单的PL/SQL小程序
昨天一个学妹问我一个关于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)
IS
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 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 := '®';
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) |
