Replace variables and definition variables in SQL * Plus, and sqlplus

Replace variables and definition variables in SQL * Plus, and sqlplus

Replace variable

Replace variables in SQL * Plus are also called substitution variables. They are generally used to temporarily store relevant data and pass values between SQL statements. Generally, replace variables with the & or & prefix. You can use the replace variable to create a common script or exchange it with the user. As follows:

For example, you need to view the table information. If you do not use the replace variable, You need to modify the script for each query. It is inconvenient. If you use the replace variable, we can store the following script in the tab. SQL script. You only need to enter the value of the replace variable at each run.

COL LOGGING FOR A7;

COL OWNER FOR A12;

COL TABLE_NAME FOR A30

COL TABLESPACE_NAME FOR A30

SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS, COMPRESSION, 

       LOGGING, PARTITIONED, NUM_ROWS,

       TO_CHAR(LAST_ANALYZED,'YY-MM-DD HH24:MI:SS') LAST_ANALYZED

FROM DBA_TABLES 

    WHERE TABLE_NAME LIKE '&table_name_like%'

    ORDER BY TABLE_NAME;

 

epps> @tab.sql

Enter value for table_name_like: EMP

old   4:  WHERE  TABLE_NAME LIKE '&table_name_like%'

new   4:  WHERE  TABLE_NAME LIKE 'EMP%'

OWNER     TABLE_NAME       TABLESPACE_NAME      STATUS   COMPRESS LOGGING PAR   NUM_ROWS LAST_ANALYZED

------- --------------- ---------------------- -------- -------- ------- --- ---------- -----------------

SCOTT        EMP               USERS             VALID    DISABLED YES     NO          14 09-07-20 22:00:12

WIPOWNER     EMPLOYEE_MASTER   WIPOWNER_DATA     VALID    DISABLED YES     NO       12084 14-09-07 14:08:22

epps> @tab.sql

Enter value for table_name_like: DEPT

old   4:  WHERE  TABLE_NAME LIKE '&table_name_like%'

new   4:  WHERE  TABLE_NAME LIKE 'DEPT%'

OWNER     TABLE_NAME    TABLESPACE_NAME    STATUS   COMPRESS LOGGING PAR   NUM_ROWS LAST_ANALYZED

------- -------------- ------------------ -------- -------- ------- --- ---------- -----------------

SCOTT       DEPT                USERS       VALID    DISABLED YES     NO           4 09-07-20 22:00:12

epps> 

If you replace the variable with the numeric type, you can directly use the & + variable name, as shown below & DEPTNO,

SQL> SELECT deptno, dname, loc 

  2  FROM SCOTT.DEPT

  3  WHERE DEPTNO= &DEPTNO;

Enter value for deptno: 10

old   3: WHERE DEPTNO= &DEPTNO

new   3: WHERE DEPTNO= 10

    DEPTNO DNAME          LOC

---------- -------------- -------------

        10 ACCOUNTING     NEW YORK

If it is a string or date type, it is best to enclose it with single quotation marks. Otherwise, you must add single quotation marks when entering the variable value. Otherwise, an error is reported, as shown below.

SQL> SELECT deptno, dname, loc 

  2  FROM SCOTT.DEPT

  3  WHERE DNAME= &DNAME;

Enter value for dname: SALES

old   3: WHERE DNAME= &DNAME

new   3: WHERE DNAME= SALES

WHERE DNAME= SALES

             *

ERROR at line 3:

ORA-00904: "SALES": invalid identifier

SQL> SELECT deptno, dname, loc 

  2  FROM SCOTT.DEPT

  3  WHERE DNAME= &DNAME;

Enter value for dname: 'SALES'

old   3: WHERE DNAME= &DNAME

new   3: WHERE DNAME= 'SALES'

    DEPTNO DNAME          LOC

---------- -------------- -------------

        30 SALES          CHICAGO

The difference between replace & and & is that they have different scopes. & the referenced replace variable is only valid in the current SQL statement, the replace variable of & references is valid in the current session.. The following examples are often cited to illustrate

SQL> SELECT 2+&NUM FROM DUAL;

Enter value for num: 2

old   1: SELECT 2+&NUM FROM DUAL

new   1: SELECT 2+2 FROM DUAL

       2+2

----------

         4

SQL> SELECT 3+&NUM FROM DUAL;

Enter value for num: 3

old   1: SELECT 3+&NUM FROM DUAL

new   1: SELECT 3+3 FROM DUAL

       3+3

----------

         6

SQL> SELECT 2+&&NUM FROM DUAL;

Enter value for num: 2

old   1: SELECT 2+&&NUM FROM DUAL

new   1: SELECT 2+2 FROM DUAL

       2+2

----------

         4

SQL> SELECT 3+&&NUM FROM DUAL;

old   1: SELECT 3+&&NUM FROM DUAL

new   1: SELECT 3+2 FROM DUAL

       3+2

----------

         5

Knowledge 1:In a stored procedure or package, '&' is often used in strings. During script execution, these strings are often treated as replace variables and the input values are required, in fact, you only need to set the SQL * PLUS environment variable to avoid this situation. Usually SET DEFINE OFF

Knowledge 2:To display the script file after SQL * Plus is replaced by the replacement value, run the SET VERIFY ON/OFF command.

 

SQL> SET VERIFY OFF

SQL> SELECT 2+&NUM FROM DUAL;

       2+2

----------

         4

SQL> SET VERIFY ON

SQL> SELECT 2+&NUM FROM DUAL;

old   1: SELECT 2+&NUM FROM DUAL

new   1: SELECT 2+2 FROM DUAL

       2+2

----------

         4

User-Defined variables

Variables defined using DEFINE (maybe we can call it to DEFINE constants, which is very similar to DEFINE in C/C ++) can be used to reference declared variables. The scope or life cycle is usually the whole session. If you need to clear the VARIABLE after defining the VARIABLE, you can use UNDEFINE to clear the VARIABLE and use define variable to view the VARIABLE.

SQL> DEFINE NUM = 2; -- DEFINE variables

SQL> DEFINE NUM -- View Variables

DEFINE NUM             = "2" (CHAR)

SQL> SELECT 2 + & NUM FROM DUAL; -- reference variable

old   1: SELECT 2+&NUM FROM DUAL

new   1: SELECT 2+2 FROM DUAL

       2+2

----------

         4

SQL> UNDEFINE NUM; -- clear the variable

SQL> SELECT 2 + & NUM FROM DUAL; -- after the variable is cleared, & NUM becomes the replacement variable. Required value

Enter value for num: 

TIPS:View the variables defined in SQL * Plus in the current session

SQL> define

DEFINE _ DATE = "11-SEP-14" (CHAR)

DEFINE _ CONNECT_IDENTIFIER = "epps" (CHAR)

DEFINE _ USER = "SYS" (CHAR)

DEFINE _ PRIVILEGE = "as sysdba" (CHAR)

DEFINE _ SQLPLUS_RELEASE = "1002000400" (CHAR)

DEFINE _ EDITOR = "ed" (CHAR)

DEFINE _ O_VERSION = "Oracle Database 10g Release 10.2.0.4.0-Production" (CHAR)

DEFINE _ O_RELEASE = "1002000400" (CHAR)

Different from the define variable, the VARIABLE needs to specify the VARIABLE type, similar to the strong type and weak type variables in programming languages. The former is used to bind variables, and the latter is used to replace variables with & or &

SQL> VAR NUM NUMBER;

SQL> EXEC: NUM: = 10;

PL/SQL procedure successfully completed.

SQL> PRINT NUM;

NUM

----------

10

DECLARE variables are generally used in PL/SQL. Generally, we also use more. It is not described here.

References:

Http://www.doc88.com/p-783671200732.html

Http://blog.itpub.net/9933980/viewspace-627702/

Http://blog.csdn.net/haiross/article/details/15340489

Definition of variables in SQL

Your usage is incorrect. Variables cannot be used like this,
As is an alias in SQL. It can be an alias for a table name or a column name,
It is mainly used to increase readability.
Example: Two Methods of writing an alias for a column
1. Field name as alias. Note: as can be omitted.

Select id as number, name as name from table1

This statement can also be written as follows:

Select id number, name from table1

2. Use an equal sign to use an alias

Select number = id, name = name from table1

The effect is the same as that of the first two statements.

Example 2: how to use an alias for a table name

Select a. id number, name from table1 as a join table2 as B on a. id = B. id

The as can also be omitted.

You can define local variables or global variables in SQL.

Yes. It is often used in database programming. For example, in oracle, declarations in the package are visible to the stored procedures and functions in the entire package, that is, the global variables in the entire package. Variables defined in the function body or stored procedure are local variables.