SQL * Plus has a lot of techniques. If you master these techniques, it is beneficial for rapid development and effective maintenance databases under the Oracle database.
I will accept one or two below.
[b] 1. Dynamically generate bulk scripts using SQL * PLUS [/ B]
Combine the spool with the select command to generate a script, and the script contains a statement that can perform a certain task in batches.
example 1:
Generate a script, delete all the tables under the Scott user:
a. Create a gen_drop_table.sql file containing the following statement:
Spool c: /drop_table.sql
SELECT 'DROP TABLE' || Table_name || ';' from user_tables;
Spool off
b. Log in to the database in Scott User
SQLPlus> @ ... ../ gen_dorp_table.sql
c. Generate a file DROP_TABLE.SQL file in the C-drive directory, contain the statement to delete 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 the Drop Table ... .. statement
e. Run the DORP_TABLE.SQL file under Scott users to remove all tables under the Scott user.
SQLPlus> @ c: /dorp_table.sql
In the above operation, there will be extra characters in the generated script file, such as the running SQL statement, title, or returning number of rows, we need to edit the script and run, bring many inconvenience to the actual operation. Lazy is human nature, which prompted us to implement the above tasks with a simpler way.
a. Create a gen_drop_table.sql file containing the following statement:
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 in to the database in Scott User
SQLPlus> @ ... ../ gen_dorp_table.sql
c. Generate a file DROP_TABLE.SQL file in the C-drive directory, contain the statement to delete all tables, as shown below:
DROP TABLE DEPT;
DROP TABLE EMP; DROP TABLE PARENT;
DROP TABLE STAT_VENDER_TEMP;
Drop Table Table_forum;
d. Run the DORP_TABLE.SQL file under Scott users, delete all the tables under the Scott user.
SQLPlus> @ c: /dorp_table.sql
[b] 2. Bring a text file in a table into a text file, the column and columns are "," "separated [/ B]
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 above content as a text file, log in with Scott, and execute the file after the file is executed:
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: / concrete file name
SQL statement you want to run
Spool off
As a template, as long as the necessary statement is like this template.
In the newer version of Oracle, you can also use the set colsep command to implement the above features:
SQL> SET colsep,
SQL> SELECT * from dept;
10, Accounting, New York
20, Research, Dallas
30, Sales, Chicago
40, Operations, Boston
35, AA, BB
[b] 3. Dynamically generate the file name required for the spool command [/ b]
In our example, the file name required for the spool command is fixed. Sometimes we need spool every day, and each spool file name is different, such as the file name contains the date of the day, how to implement it?
Column Dat1 New_Value FileName;
Select to_char (sysdate, 'YYYYMMDDHH24MI') DAT1 from Dual;
Spool c: / && filename..txt
SELECT * from dept;
Spool OFF;
[b] 4. How to get the value of the Windows environment variable from the script file: [/ b]
In Windos:
Spool c: /temp/%Oracle_sid%.txt
SELECT * from dept;
...
Spool off
In the above example, reference to the value of the environment variable Oracle_sid by% ORACLE_SID%, if the value of Oracle_sid is ORCL, the generated spool file name is: orcl.txt in UNIX:
SPOOL C: /TEMP/$Oracle_sid.txt
SELECT * from dept;
...
Spool off
In the above example, the value of the environment variable oracle_sid is referenced by the $ ORACLE_SID. If the value of Oracle_sid is ORCL, the generated spool file name is: orcl.txt
[b] 5. How to specify the directory of the default editing script [/ b]
In SQL * Plus, you can use the save command to save the SQL statement of the previously executed to a file, but how to set the default directory of the file?
With the SQL> Set EditFile C: /TEMP / FILE.SQL command, you can set its default directory to C: / TMPE, the default file is named file.sql.
[b] 6. How to remove the same line in the table [/ b]
Find the same line:
SELECT * from dept a
WHERE ROWID <> (SELECT MAX (RowID)
From dept b
Where a.deptno = b.deptno
And A.dname = B. DNAME - Make Sure All Column Are Compared
And a.loc = b.loc);
Note:
If you only find the same line as the deptno column, the above query can be changed to:
SELECT * from dept a
WHERE ROWID <> (SELECT MAX (RowID)
From dept b
WHERE A.DEPTNO = B.DEPTNO)
Delete the same line:
Delete from Dept A
WHERE ROWID <> (SELECT MAX (RowID)
From dept b
Where a.deptno = b.deptno
And A.dname = B. DNAME - Make Sure All Column Are Compared
And a.loc = b.loc);
Note: The row of column values is NULL is not deleted.
[b] 7. How to insert two single quotes ('') in the database [/ b]
INSERT INOT Dept Values (35, 'AA' '' 'BB', 'A''B');
When inserted, use two 'to represent one'.
[b] 8. How to set the SQL * PLUS search path, so when you use the @ command, you don't have to enter the full path of the file. [/ b]
Set the SQLPath environment variable.
Such as:
SQLPATH = C: / ORANT / DBS; C: / Apps / Scripts; C: / Myscripts
[b] 9. What is the difference between @ @@? [/ b]
@ Is equal to the start command to run a SQL script file.
The @ command calls, or specifies the full path, or can search the script file through the SQLPath environment variable.
@@ is used in a script file, used to specify the file with @@ executive in the same directory, without specifying a full path, nor is it a file from the path specified by the SQLPath environment variable, which is generally used In the nested script file.
[b] 10. & &&&'s difference [/ b] & to create a temporary variable, whenever this temporary variable is encountered, you will prompt you to enter a value.
&& is used to create a persistent variable, just like a persistent variable created with a define command or a column command with a new_vlaue. When you use && command to reference this variable, you don't prompt the user to enter the value each time you encounter this variable, but just the first time you encounter once.
For example, put the following three-line statement as a script file, run the script file, will prompt three times, let the value of the deptnoval:
SELECT Count (*) from Emp where deptno = & deptnoval;
SELECT Count (*) from Emp where deptno = & deptnoval;
SELECT Count (*) from Emp where deptno = & deptnoval;
The following three-line statement is a script file, run the script file, will only be prompted once, let 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;
[b] 11. The purpose of introducing COPY [/ B]
The copy command is particularly useful when copying the data between the two databases, especially if the command can pass the data of the LONG field between the two databases.
Disadvantages:
When data is transmitted between two databases, it is possible to lose precision.
[b] 12. Ask what is my script becomes very slow when modifying a lot of rows? [/ b]
When you modify a list in a table via the PL / SQL block, you will create a CURSOR on the table, but only Rollback Segment will release Rollback Segment when you shut down Cursor, so that when Cursor is still open, the modification process will Slow, this is because the database has to search for a large number of Rollback Segment to maintain read consistency. In order to avoid this, try to add a logo field in the table to describe whether the line has been modified, then turn off the Cursor, and then open the Cursor. You can modify 5000 rows each time.
