What does 'colon' mean in SQL under WHERE clause?

Question

I have SQL function, it is not written by me.

I am having hard time understanding, what does following condition mean?
specifically :key and ||cLF||'.

WHERE  ' WHERE 1=1 '
       ||cLF||' AND   f.key = :key '
       ||cLF||' AND   i.flag = 0'
       ||cLF||' AND   r.flag = 0'

@JoãoSilva Thanks for link. and jimp Fixed thanks, I tried best to not have any silly errors while asking question. — Mowgli, Sep 17 '12 at 16:33
possible duplicate of [What does the colon sign ":" do in a SQL query?](http://stackoverflow.com/questions/2177978/what-does-the-colon-sign-do-in-a-sql-query) — Jeffrey Kemp, Sep 18 '12 at 04:32

score 3 · Accepted Answer · edited May 23 '17 at 12:09

3

First, the || operator is a string concatenation operator. So it looks like the code is building a WHERE clause using conditions specified by cLF. Though I'm not entirely sure why they're tacking on cLF three times there.

The :key syntax refers to a parameter in a parameterized query. Its value will be passed in when the SQL statement you're building is actually run.

edited May 23 '17 at 12:09

Community

1
1

answered Sep 17 '12 at 16:34

Mike Christensen

88,082
50
208
326

`CLF` is set as Constant `CONSTANT varchar2(1) := chr(10);` – Mowgli Sep 17 '12 at 16:37
mike thanks for explaining and helpful link, so in that link, can you please explain what `'Mr '` selects? – Mowgli Sep 17 '12 at 16:42
also in my case `CLF` is it changing the type? – Mowgli Sep 17 '12 at 16:42
Not sure what you mean about *what 'Mr ' selects*. Can you elaborate? `cLF` is just adding linefeeds to your statement, they really have no purpose except for readability. Perhaps the statement is being logged somewhere. – Mike Christensen Sep 17 '12 at 16:47

score 1 · Answer 2 · answered Sep 17 '12 at 16:35

The query you have pasted is a part of a dynamically constructed SQL statement. Semicolon here points to a bind-place holder, meaning that the actual value for ":key" is passed through an argument and not hard coded.

Read examples on EXECUTE IMMEDIATE.

What does 'colon' mean in SQL under WHERE clause?

2 Answers2