Taking user input, using it in where clause.
661668Jun 23 2010 — edited Jun 24 2010Hi,
What I want to achieve is the following (needs to be written in PL/SQL as a stored procedure and ran in SQLPLUS using simple execute statement):
1. User is prompted for input with the exact message I want to prompt them with i.e. "Please enter pay band you want to change:"
2. User enters number.
3. Number is assigned to PAY_BAND.
4. First update statement is made:
UPDATE EMPS_TABLE
SET SALARY = '30000'
WHERE EMPS_PAY_BAND = PAY_BAND;
5. Output how many rows were updated as a result of this statement.
6. Require input of user which will determine whether to carry on to next update statement or abort.
7. Providing the user is happy with the row count, proceed to next update statement which will use the variable similar to the previous update statement.
8. Again, row count is output and user is required to confirm the continuation of the script.
9. A procedure that has already been written is executed from this procedure and passing the parameters in 'PAY_BAND' to be used by this second procedure.
I'm having real problems with the lot of this. I have tried utilising the ampersand '&' symbol in SQLPLUS but have had problems. Also, step 9 where I call upon another procedure - how do I adapt that already written procedure? In other words, at the start of this second procedure it declares variables such as PAY_BAND = '5.' How do I go about converting this line to PAY_BAND = [user_input_passed_from_original_pl_code].
Thanks a LOT!!! If I get the answer to this I'd be extremely grateful, I've struggled quite a bit but it's something that needs doing rather urgently.
Regards,
Dan