1. No '' after variable conversion

1.1. Create a stored procedure, which contains a cursor to query the table in a certain mode. It also contains a print of the for loop. The print is added to make the effect of dynamic statements substituted with parameters more intuitive.

CREATE OR REPLACE PROCEDURE T_1208(COL VARCHAR(100))
AS

CURSOR T_CS FOR SELECT OWNER,OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER='OA_01_TEST';
BEGIN

FOR T IN T_CS LOOP
PRINT  'SELECT '||COL||' FROM '||T.OWNER||'.'||T.OBJECT_NAME||' WHERE '||COL||'=15' ;
END LOOP;

END;

1.2 implementation effect

CALL T_1208('ID');
Successful execution, Execution took 1 ms. Execution number:2048
SELECT ID FROM OA_01_TEST.TEST_PRO WHERE ID=15

Note: if '...' is used to wrap an ordinary string as a whole, then this' SELECT '| COL' | FROM '| T.OWNER' '|| T.OBJECT_ The statement name | WHERE '| COL | = 15' can be understood as ordinary character | variable | ordinary string | variable | ordinary string |..., WHERE | is the connector.

Replace variable with ''

2.1. Create stored procedure

CREATE OR REPLACE PROCEDURE T_1208(COL VARCHAR(100))
AS
CURSOR T_CS FOR SELECT OWNER,OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER='OA_01_TEST';
BEGIN

FOR T IN T_CS LOOP
PRINT  'SELECT '''||COL||''' FROM '''||T.OWNER||'''.'''||T.OBJECT_NAME||''' WHERE '''||COL||'''=15' ;
END LOOP;

END;

2.2 test execution results

CALL T_1208('ID');

2.2. Result message

[Execute statement 1]:
CALL T_1208('ID')
Successful execution, Execution took 2 milliseconds. Execution number:2025
SELECT 'ID' FROM 'OA_01_TEST'.'TEST_PRO' WHERE 'ID'=15

0 records affected

Note: the result brought in is SELECT 'ID' FROM 'OA'_ 01_ TEST’.‘ TEST_PRO 'WHERE' ID '= 15, there is a syntax problem with this statement. But let's put aside this problem and see how the 'is formed. The "description" in the previous section should be written like this (PRINT 'SELECT' | '|' | 'COL' | '|' FROM '|' T.OWNER |. 't.object |' name | 'WHERE' | 'COL' = 15 ';), but because' is a special symbol, it is an ordinary character that needs to be escaped. Therefore, here 'SELECT' is regarded as a whole, and the second 'is used to escape the third' character.

2.3 why bother to say "variable plus"
If the input parameter of the variable is a number, but the field definition is char or varchar, escape is required. If there is' 'outside the variable, escape is no longer required, so as to improve the performance of a certain SQL statement.

3. Variable replaced with ""

3.1. According to the description in Section 1, we can quickly get the following stored procedures

CREATE OR REPLACE PROCEDURE T_1208(COL VARCHAR(100))
AS

CURSOR T_CS FOR SELECT OWNER,OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OWNER='OA_01_TEST';
BEGIN

FOR T IN T_CS LOOP
PRINT  'SELECT '||'"'||COL||'"'||' FROM '||T.OWNER||'.'||T.OBJECT_NAME||' WHERE '||COL'=15' ;

END LOOP;

END;

3.2 test execution results

CALL T_1208('ID');
Successful execution, Execution took 2 milliseconds. Execution number:2122
SELECT "ID" FROM OA_01_TEST.TEST_PRO WHERE =15

3.3 what are the benefits of adding "" to variables

 CREATE USER 123 IDENTIFIED BY 1234567890;

The above statement will report the following error

[Execute statement 1]:
create user 123 identified by 1234567890;
Execution failed(Statement 1)
Line 1, Column 22[123]Error near[-2007]: 
Parsing error

The following statement will not report an error

CREATE USER "123" IDENTIFIED BY 1234567890;

If there is a student's table and I want to create an account of the campus network according to the student's student number (if it is all numbers), then our "" will come in handy.

Link: Dameng technology community https://eco.dameng.com/.