SQL * plus tips

 

SQL * plus contains a lot of skills. If you master these skills, it is helpful for quick development and effective database maintenance in oracle databases.

1. Use SQL * PLUS to dynamically generate Batch scripts
When combined with the select command, spool can generate a script containing statements that can execute a task in batches.

Example 1:
Generate a script to delete all tables under the SCOTT User:
　　A.Create the gen_drop_table. SQL file, which contains the following statements:

 

 

SPOOL c:/drop_table. SQL
SELECT 'drop table' | table_name | ';' FROM user_tables;
SPOOL OFF

　　B. Log on to the database as a SCOTT user.
SQLPLUS> @..... /Gen_dorp_table. SQL

　　C. The drop_table. SQL file will be generated under the c root directory, containing the statement for deleting all tables, as shown below:
SQL>

SELECT 'drop table' | table_name | ';' FROM user_tables;

'Droptable' | TABLE_NAME | ';'

Drop table dept;
Drop table emp;
Drop table parent;
Drop table STAT_VENDER_TEMP;
Drop table TABLE_FORUM;

5 rows selected.

SQL> SPOOL OFF

　　D. Edit the generated drop_table. SQL file to remove unnecessary parts, leaving only drop table ..... Statement

　　ERun the dorp_table. SQL file under the scott user to delete all tables under the scott user.

SQLPLUS> @ c:/dorp_table. SQL

In the above operation, there are extra characters in the generated script file, such as the running SQL statement, title, or number of returned rows. We need to edit the script and run it again, it brings a lot of inconvenience to the actual operation. Laziness is a human nature, which prompts us to implement the above tasks in a simpler way.

 

A. Create the gen_drop_table. SQL file, which contains the following statements:

Set echo off
Set feedback off
Set newpage none
Set pagesize 5000
Set linesize 500
Set verify off
Set pagesize 0
Set term off
Set trims on
Set linesize 600
Set heading off
Set timing off
Set verify off
Set numwidth 38
SPOOL c:/drop_table. SQL
SELECT 'drop table' | table_name | ';' FROM user_tables;
SPOOL OFF

　　B. Log on to the database as a SCOTT user.

SQLPLUS> @..... /Gen_dorp_table. SQL

　　C. The drop_table. SQL file will be generated under the c root directory, containing the statement for deleting all tables, as shown below:

Drop table dept;
Drop table emp;
Drop table parent;
Drop table STAT_VENDER_TEMP;
Drop table TABLE_FORUM;

　　DRun the dorp_table. SQL file under the scott user to delete all tables under the scott user.

SQLPLUS> @ c:/dorp_table. SQL

2. export data from a table to generate a text file separated by commas (,).

Set echo off
Set feedback off
Set newpage none
Set pagesize 5000
Set linesize 500
Set verify off
Set pagesize 0
Set term off
Set trims on
Set linesize 600
Set heading off
Set timing off
Set verify off
Set numwidth 38
SPOOL c:/drop_table. SQL
Select DEPTNO | ',' | dname from dept;
SPOOL OFF

After saving the preceding content as a text file, log on to scott and execute the file. The result is displayed:

10, ACCOUNTING
20, RESEARCH
30, SALES
40, OPERATIONS

Through the two examples above, we can:

Set echo off
Set feedback off
Set newpage none
Set pagesize 5000
Set linesize 500
Set verify off
Set pagesize 0
Set term off
Set trims on
Set linesize 600
Set heading off
Set timing off
Set verify off
Set numwidth 38
SPOOL c:/specific file name

The SQL statement you want to run

SPOOL OFF

As a template, you only need to take the necessary statements as the template.

 

7. How to insert two single quotes ('') into the database ('')

Insert inot dept values (35, 'aa' '''bb ', 'A' B ');

During the insert operation, two ''are used to represent one '.

8. How to set the search path for SQL * plus, so that the full path of the file does not need to be entered when the @ command is used.
Set the SQLPATH environment variable.
For example:

SQLPATH = C:/ORANT/DBS; C:/APPS/SCRIPTS; C:/MYSCRIPTS

9. What is the difference between @ and?
@ Equals to the start command, used to run an SQL script file.
@ Command to call the script file in the current directory, or specify the full path, or you can use the SQLPATH environment variable to search for the script file.
@ Is used in the script file to specify that the file executed with @ is in the same directory as the file where @ is located, instead of specifying the full path, this command does not search for files from the path specified by the SQLPATH environment variable. It is generally used in nested script files.

10. & Differences
& Used to create a temporary variable. Whenever this temporary variable is encountered, you will be prompted to enter a value.
& Used to create a persistent variable, just like a persistent variable created using the define command or the column command with the new_vlaue words. When you use the & command to reference this variable, you will not be prompted to enter a value every time you encounter this variable, but will be prompted only once when you first encounter this variable.

For example, if you save the following three statements as a script file and run the script file, you will be prompted three times to enter the deptnoval value:

Select count (*) from emp where deptno = & deptnoval;
Select count (*) from emp where deptno = & deptnoval;
Select count (*) from emp where deptno = & deptnoval;

Save the following three statements as a script file. If you run the script file, you will only be prompted once to enter the value of deptnoval:

Select count (*) from emp where deptno = & deptnoval;
Select count (*) from emp where deptno = & deptnoval;
Select count (*) from emp where deptno = & deptnoval;

11. The purpose of introducing copy
The Copy command is particularly useful when copying data between two databases. In particular, this command can transmit data of long fields between two databases.
　　Disadvantages:
When data is transmitted between two databases, the precision (lose precision) may be lost ).

12. Why is my script slow when a large number of rows are modified?
When you modify many rows in a table through PL/SQL blocks, you will create a cursor in the table, but the ROLLBACK SEGMENT will be released only when you disable cursor, when the cursor is enabled, the modification process slows down because the database has to search for a large number of rollback segments to maintain read consistency. To avoid this problem, add a flag field to the table to describe whether the row has been modified, close the cursor, and then open the cursor. You can modify 5000 rows each time.