| Why I cannot execute my program when I use a prompt? [message #39516] |
Mon, 22 July 2002 01:15  |
sohmoi
Messages: 2
Registered: July 2002
|
Junior Member |
|
|
Below is my statement, why it couldn't be executed?
ACCEPT p_cust_id PROMPT 'Enter the customer id: '
DECLARE
CURSOR cust_cur IS
SELECT cust_id, cust_name
FROM cust
WHERE cust_id = &p_cust_id;
CURSOR sales_cur IS
SELECT p.prod_id, p.prod_name, s.sales_amount, s.cust_id
FROM prod p, sale s
WHERE p.prod_id = s.prod_id;
TYPE cust_rec IS RECORD
(cust_id cust.cust_id%TYPE,
cust_name cust.cust_name%TYPE);
cust_list cust_rec;
TYPE sales_rec IS RECORD
(prod_id prod.prod_id%TYPE,
prod_name prod.prod_name%TYPE,
sales_amount sale.sales_amount%TYPE,
cust_id sale.cust_id%TYPE);
sales_list sales_rec;
v_total sale.sales_amount%TYPE := 0;
BEGIN
OPEN cust_cur;
LOOP
FETCH cust_cur INTO cust_list;
EXIT WHEN cust_cur%NOTFOUND;
END LOOP;
COMMIT;
CLOSE cust_cur;
OPEN sales_cur;
LOOP
FETCH sales_cur INTO sales_list;
EXIT WHEN sales_cur%NOTFOUND;
IF sales_list.cust_id = cust_list.cust_id THEN
v_total := v_total + sales_list.sales_amount;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Customer id: ' || cust_list.cust_id);
DBMS_OUTPUT.PUT_LINE ('Grand Total: ' || v_total);
COMMIT;
CLOSE sales_cur;
END;
/
|
|
|
|
| Re: Why I cannot execute my program when I use a prompt? [message #39533 is a reply to message #39516] |
Mon, 22 July 2002 12:03  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
SQL> set scan on
SQL> set serveroutput on
SQL> ACCEPT p_cust_id PROMPT 'Enter the customer id: '
Enter the customer id: scott
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE ('p_cust_id = ' || '&p_cust_id');
3 END;
4 /
old 2: DBMS_OUTPUT.PUT_LINE ('p_cust_id = ' || '&p_cust_id');
new 2: DBMS_OUTPUT.PUT_LINE ('p_cust_id = ' || 'scott');
p_cust_id = scott
PL/SQL procedure successfully completed.
SQL>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
