oracle 使用带参数的游标时出现参数数目或类型错误

uinbv5nw  于 2022-11-03  发布在  Oracle
关注(0)|答案(1)|浏览(185)

我尝试在游标中输入参数“var_city”,以便使用用户输入的城市时,出现此错误。

Error report -
ORA-06550: line 17, column 5:
PLS-00306: wrong number or types of arguments in call to 'CUSTOMERS'
ORA-06550: line 17, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"

* Cause:    Usually a PL/SQL compilation error.
* Action:

p_city被注解掉了,因为我试图将该输入参数用作所使用的值,但效果不佳。

SET SERVEROUTPUT ON
SET VER OFF

-- ACCEPT p_city PROMPT 'Enter city name: '

DECLARE
    v_custname      si.customer.custname%TYPE;
    v_total_spent   NUMBER;
    v_total_cars    NUMBER;
    CURSOR customers (var_city varchar2) IS
    SELECT
        custname
    FROM
        si.customer
    WHERE
        UPPER(custcity) = UPPER(var_city);

BEGIN
    dbms_output.put_line('Customer Name'
                         || 'Total Cars'
                         || 'Total Spent');
    OPEN customers;
    LOOP
        FETCH customers INTO v_custname;
        EXIT WHEN customers%NOTFOUND;
        cust_total_spending_and_cars(v_custname, v_total_cars, v_total_spent);
        dbms_output.put_line(v_custname
                             || v_total_cars
                             || v_total_spent);
    END LOOP;

    CLOSE customers;
END;
/
of1yzvn4

of1yzvn41#

必须将参数传递给OPEN语句
更改此行:

OPEN customers;

到这样的东西:

OPEN customers('city name here');

相关问题