PL/SQL存储过程优化：实现客户与车辆信息的非重复输出

刚接触PL/SQL，编写了customer_info存储过程，关联customer和car表查询指定customerid的客户与车辆信息。当前输出会重复打印客户姓名、地址、电话，希望仅打印一次客户基础信息，后续仅展示对应车辆的详细信息。

现有代码 #

create or replace procedure customer_info ( p_customerid customer.customerid%type)
as
cursor executive is
select  customername, address, phoneno, regno, carmodel, color, plateno
from customer c, car car
where c.customerid = p_customerid
and  c.customerid = car.customerid;

begin

for v_loop in executive loop

DBMS_OUTPUT.PUT_LINE ('Customer name:  ' || v_loop.customername);
dbms_output.put_line ('Customer Address:  ' || v_loop.address);
dbms_output.put_line ('Customer Phone Number:  ' || v_loop.phoneno);
dbms_output.put_line ('Car registiration number:  ' || v_loop.regno);
dbms_output.put_line ('Car model:  ' || v_loop.carmodel);
dbms_output.put_line ('Car color:  ' || v_loop.color);
dbms_output.put_line ('Car plate number:  ' || v_loop.plateno);
    end loop;
END customer_info;
/

当前输出 #

Customer name:  ahmed
Customer Address:  jeddah
Customer Phone Number:  538447169
Car registiration number:  car101
Car model:  mg5
Car color:  gray
Car plate number:  KSA 5808
Customer name:  ahmed
Customer Address:  jeddah
Customer Phone Number:  538447169
Car registiration number:  car113
Car model:  rio
Car color:  black
Car plate number:  ksa 5909

期望输出 #

Customer name:  ahmed
Customer Address:  jeddah
Customer Phone Number:  538447169
Car registiration number:  car101
Car model:  mg5
Car color:  gray
Car plate number:  KSA 5808
Car registiration number:  car113
Car model:  rio
Car color:  black
Car plate number:  ksa 5909

解决方案 #

方法一：基于原代码修改，添加标志变量控制打印 #

通过布尔变量标记是否已打印客户信息，仅在第一次循环时输出客户基础数据，后续循环只打印车辆信息：

create or replace procedure customer_info ( p_customerid customer.customerid%type)
as
cursor executive is
select  customername, address, phoneno, regno, carmodel, color, plateno
from customer c, car car
where c.customerid = p_customerid
and  c.customerid = car.customerid;
-- 定义标志变量，标记是否已打印客户信息
v_printed_cust boolean := false;
begin

for v_loop in executive loop
    -- 第一次循环时打印客户信息
    if not v_printed_cust then
        DBMS_OUTPUT.PUT_LINE ('Customer name:  ' || v_loop.customername);
        dbms_output.put_line ('Customer Address:  ' || v_loop.address);
        dbms_output.put_line ('Customer Phone Number:  ' || v_loop.phoneno);
        v_printed_cust := true;
    end if;
    -- 每次循环都打印车辆信息
    dbms_output.put_line ('Car registiration number:  ' || v_loop.regno);
    dbms_output.put_line ('Car model:  ' || v_loop.carmodel);
    dbms_output.put_line ('Car color:  ' || v_loop.color);
    dbms_output.put_line ('Car plate number:  ' || v_loop.plateno);
end loop;

-- 处理客户存在但无车辆的情况
if not v_printed_cust then
    declare
        v_cust_name customer.customername%type;
        v_cust_addr customer.address%type;
        v_cust_phone customer.phoneno%type;
    begin
        select customername, address, phoneno 
        into v_cust_name, v_cust_addr, v_cust_phone
        from customer where customerid = p_customerid;
        
        DBMS_OUTPUT.PUT_LINE ('Customer name:  ' || v_cust_name);
        dbms_output.put_line ('Customer Address:  ' || v_cust_addr);
        dbms_output.put_line ('Customer Phone Number:  ' || v_cust_phone);
        dbms_output.put_line ('No cars found for this customer.');
    exception
        when no_data_found then
            dbms_output.put_line ('Customer not found.');
    end;
end if;

END customer_info;
/

方法二：拆分查询，逻辑更清晰 #

分开查询客户信息和车辆信息，先打印客户数据，再循环打印该客户的所有车辆信息，避免关联查询的重复数据问题：

create or replace procedure customer_info ( p_customerid customer.customerid%type)
as
-- 客户信息游标
cursor c_customer is
select customername, address, phoneno
from customer where customerid = p_customerid;
-- 车辆信息游标，接收客户ID参数
cursor c_cars(p_custid customer.customerid%type) is
select regno, carmodel, color, plateno
from car where customerid = p_custid;
v_cust c_customer%rowtype;
begin
    -- 获取并打印客户信息
    open c_customer;
    fetch c_customer into v_cust;
    if c_customer%found then
        DBMS_OUTPUT.PUT_LINE ('Customer name:  ' || v_cust.customername);
        dbms_output.put_line ('Customer Address:  ' || v_cust.address);
        dbms_output.put_line ('Customer Phone Number:  ' || v_cust.phoneno);
        
        -- 遍历打印车辆信息
        for v_car in c_cars(p_customerid) loop
            dbms_output.put_line ('Car registiration number:  ' || v_car.regno);
            dbms_output.put_line ('Car model:  ' || v_car.carmodel);
            dbms_output.put_line ('Car color:  ' || v_car.color);
            dbms_output.put_line ('Car plate number:  ' || v_car.plateno);
        end loop;
        
        -- 无车辆时提示
        if c_cars%notfound then
            dbms_output.put_line ('No cars found for this customer.');
        end if;
    else
        dbms_output.put_line ('Customer not found.');
    end if;
    close c_customer;
END customer_info;
/

内容的提问来源于stack exchange，提问作者Ahmed AL-ghamdi