Single quotation mark escape in oralce

In oracle, the single quotes in oralce are escaped. strings are referenced by single quotes, and the escape is also completed by single quotes. This is a headache for many beginners, especially those who are learning dynamic SQL. In simple single quotes, it is easy to understand that it always finds a pair with the nearest single quotes. That is, the proximity principle. However, when a single quotation mark acts as an escape role, the situation is somewhat confusing. (For convenience, red single quotes are used as escape characters.) 1. The second single quotes are considered as escape characters. If the second single quotes are followed by single quotes (even if there is only one ). SQL> select ''' from dual; ''' -- www.2cto.com: select ''' from dual;), we do the following two tests: SQL> select ''' from dual; ERROR: ORA-01756: the character strings in the quotation marks are not properly ended. I believe everyone is familiar with such errors. SQL> select 'sdlf from dual; ERROR: ORA-01756: The character strings in the quotation marks are not properly ended. That is to say, when the second single quotation mark acts as an escape role, the third single quotation mark is escaped. Then select ''' from dual; naturally, there is a lack of single quotes that match the first single quotes. The two experiments under the isolated single quotes support the above conclusions. SQL> select ''' from dual; ''' ------ 'www.2cto.com SQL> select ''' from dual; select ''' from dual * ERROR: ORA-00923: The expected FROM keyword is not found for the first: select ''' from dual; for the second: Escape does not exist. The above two experiments are actually a single quotation mark escape and a non-escape nature: the escape is dense, that is, if the single quotation mark appears at the escape position, the single quotation mark follows closely (it is defined as follows: there is no distance between two single quotation marks). In this case, the single quotation mark does not act as an escape character, but is paired with it. 2. the connector '|' leads to a new round of escape: Connection Symbol '|' there is no relationship between the left and right single quotes, unless '|' is a part of a string (which is common in dynamic SQL ). SQL> select 'exit '| ''' from dual; 'exit' | ''' -------- exit 'www.2cto.com SQL> select 'exit ''' from dual; 'Exit ''' ------------ EXIT ''' for the first pair, the first two single quotes are paired, and the last four single quotes are allocated according to the first principle above, that is: select 'exit '| ''' from dual; for the second, because there is a single quotation mark after the second single quotation mark, it is not paired with the first one, but acts as an escape role. BOTH: select 'exit ''' from dual; with the above two principles, we can calmly face complex dynamic SQL statements.