Using replacement variables to improve Oracle Interaction

To improve Oracle interaction, you can define and use replacement variables. The following describes how to use replacement variables to improve Oracle interaction for your reference.

1. Define and use replacement variables.

Related tools provided by Oracle, such as the SQL plus management platform, allow database administrators to replace variables to improve the flexibility of SQL statements. The definition of the replace variable is similar to that of a common variable. You only need to add the & symbol before the variable name. As shown in the above example, "& name_filed" indicates a replacement variable. When you run an SQL statement with a replacement variable, the database system automatically reminds you of the variable values to be entered.

In the following statement, & name_filed indicates the field value that the user wants to display. If you want to display multiple fields in a table, you can define multiple replacement variables here. In the Condition Statement, '& name' indicates a restriction or a query parameter. The general idea of this statement is that the user enters the employee name and then asks the system to query the user's desired information, such as the user's ID card number.

SQL> select & name_filed from dtmfg. ad_user t where t. name like '& name ';

Careful readers may find that the replacement of variables in the query conditions should be enclosed in single quotes. Why? This is because when writing SQL statements, if the field is of the limit type, it must be enclosed in single quotes. Therefore, when defining replace variables, we usually write SQL statement types. If the data type is character or date type, you must use a single line to enclose it when defining the replace variable. Otherwise, brackets must be added each time you execute this statement and input variables. That is very troublesome. In comparison, it is better to add single quotation marks when defining this SQL statement. In this way, when you enter the corresponding value, you do not need to enclose it in single quotes. You can directly enter it.

In addition, replace variables can be used not only in Select statements, but also in Update statements.

2. Define the default value.

Sometimes, we may have default values for replacing variables. If the database administrator knows the specific value of the replace variable and wants to avoid repeated occurrences of the script file, you can define a default value for the replace variable. On the SQL Plus platform, the database administrator can use the Define command to Define the default value for replacing variables.

Pay attention to several issues when defining to replace the default value of a variable.

First, use single quotes properly. You must use single quotation marks to enclose them in single quotation marks. When you do not need to use them, do not use them. Otherwise, SQL statements may run incorrectly. So when should we use single quotes? This is mainly based on SQL statements. If you need to use single quotes when writing SQL statements, you must enclose them in single quotes when defining the default value. If you do not need it, you do not need it. If you define the default value of a field name, you do not need to enclose it in single quotes. The SQL statement execution is incorrect because the default value is defined. When a default value is defined, the system does not prompt the user to enter relevant parameters, but directly uses the default value. In the preceding example, if the default value of NAME is defined using the statement define NAME = 'superuser, the user will no longer be prompted to enter the NAME value. We can use the Define command to view the values of all replaceable variables in the current environment. You can also use Define NAME to view the default value of the variable NAME that can be replaced.

Third, if you use a script file, you can directly place the statement that defines the default value at the beginning of the script file. In this case, the definition of this default value will follow the script file. You do not need to define the default value every time you use a script file. This default value only applies to the current session. When the database administrator ends the current session, the default value of this variable becomes invalid. In order to continue to use this default value during the next session, it is also necessary to define the default value of the replaceable variable at the beginning of the script file.

Fourth, when the Define command is used to Define replaceable variables, there is a restriction. To replace the variable, the data must be of the stable type. If the replaceable variable you want to define is numeric, such as the employee's salary, you need to use the ACCEPT command. Otherwise, it is an invalid default value.

If we do not need this default value sometimes, we need to cancel it. What should we do? In this case, we can use the undefine command to cancel the default value of a variable to be replaced. Of course, we can also use the Define command to set a new value for a replacement variable. In actual work, you may still use the Define command to set a default value for it.

3. Save the test as a script file.

To enhance the reusability of this SQL statement, we often save the prepared SQL statement as a script file. In this case, you can directly execute this script statement when you need it next time. For better readability, I have some small suggestions.

First, it is best to comment out the specific usage of this statement at the beginning of the script file. As the saying goes, good memory is worse than bad writing. After a few months, it is hard to ensure that you still remember the purpose of this statement.

The second is to give some intuitive names to replaceable variables. Otherwise, the database administrator may not know the meaning of a replacement variable. In addition, it is best to distinguish the replacement variable of the column from the replacement variable in the query statement. For example, you can use the prefix to differentiate. In doing so, the readability of this statement will be enhanced.

Third, you can use the Prompt command to output some Prompt information. When writing replace variables, you need to make comments for each replaceable variable just like writing other programs. This is one of the essential skills for database administrators.

4. other solutions.

For Oracle Database interoperability, it also provides some other solutions.

For example, you can use the ACCEPT, PAUSE, and PROMPT commands to provide a simulated user input interface. The ACCEPT command allows the database administrator to specify the type of the variable to replace. When Define is defined to replace the default value of a variable, only the default value of the variable type data can be accepted. If it is the default value of other data types, it must be defined using ACCEPT. As the name suggests, PAUSE can temporarily run script statements to provide a more friendly user interface. For example, you can enter the Enter key to execute the remaining script statements. In this way, the user is given a chance to "breathe" and check the data.

For example, you can bind a variable to replace the variable. Variable binding only exists in the current session. The last definition of variable binding with the same name prevails. Variable binding is troublesome when assigning values. You need to use the Excute command to assign values to the variable binding. Therefore, it is not widely used in practical applications.

ORACLE system table and data dictionary View

In-depth analysis of Oracle ERP system module

Detailed explanation of four categories of Oracle index Scanning

Three methods for querying duplicate records in Oracle

Implementation of Oracle's fixed number of records