What does the colon sign ":" do in a SQL query?

Question

What does : stand for in a query?

INSERT INTO MyTable (ID) VALUES (:myId)

How does it fetch the desired value?

Edit: Also what is that sign called? I wanted to search on google, but what's the name for :?

Sweat

It's called a "colon", a ";" is a semi-colon. – Camshaft

Answer 1

That's called a bind variable in Oracle.

what's the name for ":"?

Colon.

Trevor

Answer 2

What does ":" stand for in a query?

A bind variable. Bind variables allow a single SQL statement (whether a query or DML) to be re-used many times, which helps security (by disallowing SQL injection attacks) and performance (by reducing the amount of parsing required).

How does it fetch the desired value?

Before a query (or DML) is executed by Oracle, your program will create a cursor. Your program issues the SQL to be parsed for that cursor, then it must bind values for each bind variable referred to by the SQL. How this is done depends on the language.

What is that sign called?

A colon.

Rouen

Answer 3

Colon : is used in HQL Hibernate Query Language to signify that there is a parameter involved.

So what that means is: SQL SYNTAX:

SELECT * FROM EMPLOYEE WHERE EMP_ID = empID

is same as HQL SYNTAX:

SELECT * FROM EMPLOYEE WHERE EMP_ID = :empID

empID being local variable for parameters...

Hope this helps.

Camphene

Answer 4

This is a tag for a named query parameter, and is not part of the query's actual syntax. The tag is replaced with some value specified in the code that makes the query before it is actually run.

Holloman

Answer 5

Consider the following statements

select name from T_emp where id=1;
select name from T_emp where id=2;
select name from T_emp where id=3;

Each time a statement is executed, Oracle checks for previous occurrences of the same query. If it finds the same query, it makes use of the same execution plan. If not, It has to find the various execution paths, come up with the optimal execution plan and execute it.

Unlike human's it not intelligent enough to realize that only the id has changed(as per above example). Hence it go through all the struggles and executes it.

But there's a way to tell Oracle that its a similar statement and that it can use the same execution plan - BIND VARIABLE. Please find the example below:

declare
  v_id number;
  v_name varchar2(30);
  type c is ref cursor;
  c1 c;
begin
  for i in 1..100
   loop
    open c1 for 'select * from T_emp where id = :x' using i;
    fetch c1 into v_name;
    dbms_output.put_line('name is ' || v_name);
   end loop;
END;

Using Bind variables helps to improve the performance tenfold. PL/SQL does use the bind variables on its own(you need not explicitly tell it)

Triviality

What does ":x" mean in "open c1 for 'select * from T_emp where id = :x' using i;"? Is :x just a placeholder for 'i'? – Gumption

Answer 6

It is a named parameter.

In C#, you prefix the parameter with @ (see here).

Monagan

That's not a function of C#. It's ADO.NET, and it can be changed. – Stimulant

Answer 7

that's also the parameter syntax for a Delphi query

Turco

Answer 8

Found the first couple minutes of this video to be very useful: https://www.youtube.com/watch?v=K6VfcRALxW4

To extract: it's called a bind variable, this is a placeholder for the user input it's waiting to receive

Untinged