1z0-071 Oracle Database SQL – SUBSTITUTIONS – USING AMPERSANDS
- SUBSTITUTIONS – USING AMPERSANDS, DEFINING AND UNDEFINING THE VARIABLES
This is a simple sequel where we know the condition which is first name equals to James. But in real life, mostly in web applications, we would need to capture the user’s input and fetch rows accordingly. In other words, if they type Stephen, then we need to fetch rows where the first name is Stephen. Or if type Nina then we need to fetch rows related to Nina. This can be achieved by using substitutions. A single ampersand can be used for it. I use a single ampersand and call that variable as we name one. You can name it whatever you want. Now let me run it. It asks for the value. Let me input the value. It works. Now, we can also define the value price here. Now, if I run it, it will ask for the value because that parameter has been undefined. So it brings me results with the first name Stephen.
- SUBSTITUTIONS – DOUBLE AMPERSANDS
What if I want to use variables more than once? Let me use this SQL. Here I use the same variable name twice and let’s see how Oracle reacts. It is asking for the value vname one first. Now it’s asking for a value for this variable. Even though it’s of same name which is ampersand vname one, it is still asking for a value for that variable. Let me give a different one. I given yes T for the first one, yes M for the second one and let’s see how Oracle interprets. So what just happened is the first variable we name one has been replaced with S T. And the second variable, even though it has the same name, still got replaced with a different character string that we passed.
So the SQL got translated into this where first name like yes T or last name like SM. So if you look at the result, these three appear because the first name is like yes t and the final two rows appear because the last name starts with SM. Now what if I want to use the same variable names, but I don’t want Oracle to prompt me twice. For that I can use double amperesand like this. Okay, here I used double amp percent Vname one and single amp percent we name one. So let me pass the value yes T. Here what happened was the first double amp percent variable took the value st and it automatically passed it to the next variable which is single ampersand Vname one.
So using this method, I can avoid Oracle prompting for each and every variable. Here if you look at the where clause is first name like est or last name like est. So then you get the first three rows that match the first condition. Actually, the last row matches both the conditions. Now here, if you notice whenever a substitution happens, you would see the volt value here and then the new value here. We can turn it off by doing set verify off. Let me undefined minimal. Okay, I’m going to pass the value. It did the replacement, but however it did not show the old value and the new value, you can obviously turn it on by doing set verify on let me undefined rename one. Okay, now it shows the whole value and new value.