If I want to filter by a date column, and I want to use a variable in the place of a date literal, I could do something like this: SELECT * Or I can simply enclose the variable inside the quotes in the query, to avoid having to write the quotes when entering the value, which I think is easier. I would need to enter the value I want to use inside single quotes, like ‘HR_REP’ or similar. Now, if I want to use a substitution variable for a string (varchar), I have to make sure the value is enclosed inside single quotes, as usual, so, for example, if I have this query to filter employees by job_id: SELECT * Using Substitution Variables for Strings and Dates
Now, if I execute it again, it will ask for the value I want to use as well, so I can now enter a different department id, and get my results using the same query without modifications.īut please keep in mind that this substitution is done by the client tool (SQL Developer in this case) before sending the statement to the database, so, the database doesn’t even know that I used a substitution variable, because it is sent by SQL Developer as if I had used the department id as a literal. Here I just have to enter the department_id I want to use, and the query will be executed as if I had written it using a literal, like in my original query. Now, look what happens when I run the query: So, my query would look like this SELECT * I just have to write an ampersand (&), and then the name of the variable I want to use, so, in this case, I’m going to call it “department”. Parameterizing a Query by Adding Substitution Variables Instead of writing the department id in my query directly, I could use a variable, which could take a different value each time I run the query, instead of having to modify the query every time, or have many different variants of the query. I could need to do it for department 4 a little later, so, I would need to make another copy and modify it again, but there is an easier way to handle this situation: SELECT *īut now I want to list employees from department 2, so, I could modify my query directly, or I could make a copy and modify the copy, in case I want the old version again. Let’s say I have this query to get the list of employees who work in the department with ID 1. The concept is very simple, and I’m going to show you with an example. Let me start by saying that substitution variables are not a feature of the SQL language itself, but a feature of the tools we use to interact with the database, such as SQL Developer or SQL Plus, but I have included it here because it is very useful, and also because, for some strange reason, the SQL certification exam includes this topic, and I know many people use this course to prepare for the exam.
0 kommentar(er)
