| PL/SQL en Oracle9i
Hola a todos! Tengo problemas con la siguiente transacción, al ejecutarla las variables del prompt, sólo me pide valores para algunas en vez de para todas, y tambien me dice que "la variable w_street_2 no está declarada" cuando claramente está en el DECLARE arriba del todo, no sé si tendrá mas fallos porque después de dar ese mensaje se para. Aquí os dejo el código.
DECLARE
namecount NUMBER;
n NUMBER;
h_data history.h_data%TYPE;
h_date history.h_date%TYPE;
w_id warehouse.w_id%TYPE := 1;
w_street_1 warehouse.w_street_1%TYPE;
w_street_2 warehouse.w_street_2%TYPE;
w_city warehouse.w_city%TYPE;
w_zip warehouse.w_zip%TYPE;
W_name warehouse.w_name%TYPE;
d_street_1 district.d_street_1%TYPE;
d_street_2 district.d_street_2%TYPE;
d_city district.d_city%TYPE;
d_zip distric.d_zip%TYPE;
d_name district.d_name%TYPE;
d_state district.d_state%TYPE;
w_state warehouse.w_id%TYPE;
c_street_1 customer.c_street_1%TYPE;
c_street_2 customer.c_street_2%TYPE;
c_city customer.c_city%TYPE;
c_state customer.c_state%TYPE;
c_zip customer.c_zip%TYPE;
c_phone customer.c_phone%TYPE;
c_credit customer.c_credit%TYPE;
c_credit_lim customer.c_credit_lim%TYPE;
c_discount customer.c_discount%TYPE;
c_balance customer.c_balance%TYPE;
c_since customer.c_since%TYPE;
c_first customer.c_first%TYPE;
c_middle customer.c_middle%TYPE;
c_w_id customer.c_w_id%TYPE := 1;
SET VERIFY OFF;
ACCEPT did PROMPT 'Introduce the district ID:';
ACCEPT clast PROMPT 'Introduce the customer last name:';
ACCEPT cid PROMPT 'Introduce the customer ID:';
ACCEPT cdid PROMPT 'Introduce the district ID:';
ACCEPT hamount PROMPT 'Introduce the payment amount:';
ACCEPT cont PROMPT 'Introduce a value for cont:';
SET SERVEROUTPUT ON;
DECLARE
d_id district.d_id%TYPE := &did;
c_last customer.c_last%TYPE := &clast;
c_id customer.c_id%TYPE := &cid;
c_d_id customer.c_d_id%TYPE := &cdid;
h_amount history.h_amount%TYPE := &hamount;
vcont number := &cont;
BEGIN
SELECT sysdate FROM dual
INTO :h_date;
UPDATE warehouse SET w_ytd = w_ytd + :h_amount
WHERE w_id=:w_id;
SELECT w_street_1, w_street_2, w_city, w_state, w_zip, w_name
INTO :w_street_1, :w_street_2, :w_city, :w_state, :w_zip, :w_name
FROM warehouse
WHERE w_id=:w_id;
UPDATE district SET d_ytd = d_ytd + :h_amount
WHERE d_w_id=:w_id AND d_id=:d_id;
SELECT d_street_1, d_street_2, d_city, d_state, d_zip, d_name
INTO :d_street_1, :d_street_2, :d_city, :d_state, :d_zip, :d_name
FROM district
WHERE d_w_id=:w_id AND d_id=:d_id;
IF cont > 60 then
SELECT c_first, c_middle, c_last,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
INTO :c_first, :c_middle, :c_last,
:c_street_1, :c_street_2, :c_city, :c_state, :c_zip,
:c_phone, :c_credit, :c_credit_lim,
:c_discount, :c_balance, :c_since
FROM customer
WHERE c_w_id=:c_w_id AND c_d_id=:c_d_id AND c_id=:c_id;
ELSE /* by name */
SELECT count(c_id) INTO :namecnt
FROM customer
WHERE c_last=:c_last AND c_d_id=:c_d_id AND c_w_id=:c_w_id;
DECLARE c_byname CURSOR FOR
SELECT c_first, c_middle, c_id,
c_street_1, c_street_2, c_city, c_state, c_zip,
c_phone, c_credit, c_credit_lim,
c_discount, c_balance, c_since
FROM customer
WHERE c_w_id=:c_w_id AND c_d_id=:c_d_id AND c_last=:c_last
ORDER BY c_first;
OPEN c_byname;
IF mod(namecount,2) <> 0 then
SET namecount = :namecount + 1;
END IF;
FOR n=1 TO namecount/2 DO
BEGIN
FETCH c_byname
INTO :c_first, :c_middle, :c_id,
:c_street_1, :c_street_2, :c_city, :c_state, :c_zip,
:c_phone, :c_credit, :c_credit_lim,
:c_discount, :c_balance, :c_since;
END;
CLOSE c_byname;
END IF; /* else */
h_data := :w_name || ' ' || :d_name;
INSERT INTO history (h_c_d_id, h_c_w_id, h_c_id, h_d_id,
h_w_id, h_date, h_amount, h_data)
VALUES (:c_d_id, :c_w_id, :c_id, :d_id,
:w_id, :h_date, :h_amount, :h_data);
COMMIT;
dbms_output.put_line('Warehouse:'||to_char(w_id));
dbms_output.put_line('District:'||to_char(d_id));
dbms_output.put_line('Customer:'||to_char(c_id));
END;
/ |