Special usage of quotation marks in PL/SQL

Document directory

• Usage 1: (use two single quotes. Note: It is not double quotes)
• Usage 2:
• Usage 3: (three single quotes, plus |)

1. SQL statements are case-insensitive, except for characters in quotation marks, including single double quotation marks. in SQL statements, single quotation marks are equivalent to double quotation marks in other languages.

For example, if select * from EMP where ename = 'Scott 'has no result, select * from EMP where ename = 'Scott' has a result.

Double quotation marks are used as an identifier. For example, we create Table (ename varchar2 (5), age INT );

There will be errors, because table is a keyword. But we can use double quotation marks to cause the keyword. Create Table "table" (ename varchar2 (5), age INT );

Select * from "table ";

2. What should I do if quotation marks are used in quotation marks? (add a single quotation mark for escape characters) usage 1: (use two single quotation marks. Note: It is not double quotation marks)

Declare

V_name varchar2 (10 );

V_num number (4 );

Begin

Execute immediate 'select empno from EMP where ename = ''scott ''' into v_name;

Execute immediate 'insert into EMP (ename) values (''arwen '')';

End;

Usage 2:

Select 'I'm arwen' from dual;

The result is: I'm Arwen.

Usage 3: (three single quotes, plus |)

Declare

V_name varchar2 (10 );

V_num number (4 );

Begin

V_name: = 'Scott ';

Execute immediate 'select empno from EMP where ename = ''' | v_name | ''' into v_name;

-- Execute immediate 'create sequence '| v_name | 'start with 1 increment by 1'; -- directly reference the variable

End;

Of course, the quotation marks are too many to confuse, so you can use another method to implement the same function.

Declare

V_name varchar2 (10 );

V_num number (4 );

Begin

V_name: = 'Scott ';

Execute immediate 'select empno from EMP where ename =: inputname 'into v_name

Using v_name;

End;

(Supplement: DDL and DCL statements cannot be used directly in pl SQL blocks)

For example

Begin

Drop table EMP;

End;

But you can use execute immediate to use DDL, DCL.

For example

Begin

Execute immediate 'drop table emp ';

End;