At school, I believe I work with Oracle SQL Developer when writing SQL. And in this I can type:
SELECT Book_Title, Auth_ID
FROM book
WHERE Auth_ID = '&Enter ID';
This will then display a little message box where the user can enter an ID number to see all the books written by an author with that ID number.
I want to know if there is a way to do this in MySQL. I have looked and the nearest thing I can find is setting a variable before hand, which is not quite what I'm looking for:
SET @EnterID := 2;
select Book_Title, Auth_ID
from book
where Auth_ID = @EnterID;
The above statement in MySQL will return all the books with author ID of 2, but only because I set it to that previously. I want the user to be able to enter the variable.
Thanks.
SET @EnterID := 2; select Book_Title, Auth_ID from book where Auth_ID = @EnterID; The above statement in MySQL will return all the books with author ID of 2, but only because I set it to that previously. I want the user to be able to enter the variable. Thanks.
SQL*Plus has several commands that can be used to prompt the user for input, accept input from the user and store it in a variable, and then use that variable in a query. The following example shows the prompt/accept sequence for a query.
User variables are written as @ var_name , where the variable name var_name consists of alphanumeric characters, . , _ , and $ . A user variable name can contain other characters if you quote it as a string or identifier (for example, @'my-var' , @"my-var" , or @`my-var` ).
The ACCEPT Command. The ACCEPT command is used to obtain input from the user. With it, you specify a user variable and text for a prompt. The ACCEPT command displays the prompt for the user, waits for the user to respond, and assigns the user's response to the variable.
Oracle has the concept of interactive queries, those that as you said you can run by adding the '&' before your variables names, that is a variable substitution, this concept doesn't exist in MySql, MySql is not interactive and requires the user to enter the values in the variables by using the keyword 'SET' and @ (instead of & like in Oracle).
So, no, you cannot do what you are looking for since this is not a client-side implementation either.
BTW, I just noticed this was asked so many years ago, amazing that this is still not added as a feature in mysql.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With