plsql - PL/SQL 在不应该的时候要求输入绑定

问题描述

我的 PL/SQL 代码中可能存在一些小错误。我正在编写一个需要在虚构图书馆系统中租用虚构书籍的程序。当我执行该过程时，它要求我输入绑定，并且应该没有绑定。如果我按 OK，这是我得到的错误：

PLS-00049: bad bind variable 'DBMS_OUTPUT.PUT_LINE'

似乎无法弄清楚错误在哪里。

这是我的代码：

CREATE OR REPLACE PROCEDURE dr_rent_book(
    f_member_id dr_library_rental.member_id%TYPE,
    f_isbn dr_library_rental.isbn%TYPE
) IS 
    v_is_member NUMBER := dr_is_active_member(f_member_id);
    v_num_already_rented NUMBER := dr_num_rented_books(f_member_id);
    v_book_min_copy dr_library_books_copy.copy_id%TYPE;
BEGIN 
    IF v_is_member = 0 THEN -- Not a valid membership
        dbms_output.put_line('Your membership has expired!');
    ELSIF v_is_member = 1 THEN 
        IF v_num_already_rented >= 3 THEN -- Can't rent another book
            dbms_output.put_line('You already have 3 rented books, return one of those first.');
        ELSE -- Valid member and can rent book 
            SELECT MIN(copy_id) INTO v_book_min_copy 
            FROM dr_library_books_copy 
            WHERE isbn = f_isbn 
            AND status = 'AVAILABLE';
            IF v_book_min_copy IS NOT NULL THEN -- Exists
                UPDATE dr_library_books_copy 
                SET status = 'RENTED' 
                WHERE isbn = f_isbn 
                AND copy_id = v_book_min_copy;
                INSERT INTO dr_library_rental(copy_id, member_id, isbn, issued_date, exp_return_date, return_date)
                    VALUES(v_book_min_copy, f_member_id, f_isbn, SYSDATE, SYSDATE + 15, NULL);
                COMMIT;    
                dbms_output.put_line('The book ' || f_isbn || ' is now rented by ' || f_member_id);
            ELSE:
                dbms_output.put_line('No copies of that book currently available, we will reserve it for you!');
                dr_reserve_book(f_member_id, f_isbn);
            END IF;
        END IF;
    END IF;    
EXCEPTION 
    WHEN OTHERS THEN 
     dr_exception_handler(SQLCODE, 'rent_book');    
END dr_rent_book;

dr_is_active_member 在哪里：

CREATE OR REPLACE FUNCTION dr_is_active_member(
    f_member_id dr_library_members.member_id%TYPE
) RETURN NUMBER IS 
    v_end_date dr_library_members.membership_end_date%TYPE;
    v_is_active NUMBER(1);
BEGIN 
    SELECT membership_end_date 
    INTO v_end_date
    FROM dr_library_members 
    WHERE member_id = f_member_id;
    IF SYSDATE <= v_end_date THEN 
         v_is_active := 1;
    ELSE 
         v_is_active := 0;
    END IF;
    RETURN v_is_active;
END dr_is_active_member;

dr_num_rented_books 是：

CREATE OR REPLACE FUNCTION dr_num_rented_books(
    f_member_id dr_library_rental.member_id%TYPE
) RETURN NUMBER IS 
    v_num_rented NUMBER(1);
BEGIN 
    SELECT COUNT(copy_id) 
    INTO v_num_rented 
    FROM dr_library_rental 
    WHERE member_id = f_member_id;
    RETURN v_num_rented;
END dr_num_rented_books;

谁能帮我调试一下？

标签： plsql

plsql - PL/SQL 在不应该的时候要求输入绑定

问题描述

解决方案

推荐阅读