Swatch SQL * Plus uses a lot of techniques in SQL * Plus. If you master these techniques, it is beneficial to rapidly develop and effective maintenance databases under Oracle databases. Next, I will accept one or two, 1. Dynamically generating bulk scripts using SQL * Plus uses the spool to use the select command to generate a script, and the script contains a statement that can perform a task in batches. Example 1: Generate a script, delete all tables under the Scott user: a. Create a gen_drop_table.sql file, contain the following statement: spool c: /drop_table.sql select 'drop table' || Table_name || ';' from user_tables Spool off b. Log in to the database SQLPlus> @ ... ../ gen_dorp_table.sqlc. The file DROP_TABLE.SQL file will be generated in the C drive, which contains 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 Parent; Drop Table Stat_vender_temp; Drop Table Table_forum; 5 Rows SELECTED. SQL> Spool Off D.Sql generated Drop_Table.sql The file is edited to remove unnecessary parts, leaving only the drop table ... .. Statement e. Run the Dorp_Table.sql file under Scott users, delete all the 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 running SQL statements, headings, or returned rows, you need to edit the script and then run again. Bringing a lot of inconvenience to the actual operation. Lazy is human nature, which prompted us to implement the above tasks with a simpler way.
. A file created gen_drop_table.sql, contains the following statement: set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading off set timing offset verify offset numwidth 38SPOOL c: /drop_table.sql SELECT ' Drop table '|| Table_name ||'; 'from user_tables; spool off b. Log in to the database SQLPlus> @ ... ../ gen_dorp_table.sqlc in the C-drive root directory, contain deletion All statements are shown below: Drop Table Dept; Drop Table Emp; Drop Table Parent; Drop Table Parent; Drop Table Table_forum; D. Run the DORP_TABLE.SQL file under Scott users, delete all tables under the Scott user. SQLPlus> @ c: /dorp_table.sql2. A data table deriving generates a text file, to between columns and column "," separated set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading off set timing offset verify OFFSET NUMWIDTH 38SPOOL C: /DROP_TABLE.SQL SELECT Deptno || ',' || DNAME from Dept; SpoOL OFF After saving the above content as a text file, log in with Scott, execute the file after the file is displayed: 10, Accounting 20 , RESEARCH 30, SALES 40, OPERATIONS by the above two examples, we can: set echo offset feedback offset newpage noneset pagesize 5000set linesize 500set verify offset pagesize 0set term offset trims onset linesize 600set heading off set timing offset verify offset numwidth 38SPOOL c : / Concrete file name You want to run the SQL statement spool off as a template, as long as the necessary statements are like this template.
In Oracle's newer versions, 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, BB3. Dynamically generates the file name required by the spool command in our example, the file names required by the spool command are 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 selection * from dept; spool off; 4. How to get the value of the Windows environment variable from the script file: in Windos: Spool c: /temp/%Oracle_sid%.txt selection * from dept; ... spool off in the above example, reference through% ORACLE_SID% The value of environment variables 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 selection * from dept; ... spool off on it In the 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 is named: orcl.txt5. How to specify the default editing script in SQL * Plus, you can save the SAVE command to a file with the SAVE command, 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.
6. How to remove the same line in the table: 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 columns 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 de PT B WHERE A.DEPTNO = B.deptno) Delete the same line: Delete from Dept Awhere Rowid <> (SELECT MAX (Rowid from DePT B Where A.DEPTNO = B.DEPTNO and A.DNAME = B. DNAME - MAKE SURE ALL Column is compared and a.loc = b.loc); Note: The row of column values is NULL. 7. How to insert two single quotes ('') insert inot de PEPT VALUES (35, 'AA '' '' BB ',' A'' '); When inserted, use two' to represent a '.8. How to set the SQL * Plus search path, so when you use the @ command, you don't have to enter a file. A full path. Set the sqlpath environment variable. Such as: sqlpath = c: / orant / dbs; c: / apps / scripts; c: /myscripts9.@ @@'s difference? @ 等于 s,, used to run a SQL Script file. @ Command calls the current directory, or specifies the full path, or can pass SQLPath Environment variables searched for script files. @@ 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. 10. & &&&'s difference & To create a temporary variable, you will prompt you to enter a value whenever you encounter this temporary variable. && 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, the following three-line statement is a script file, run the script file, will be prompted 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; save the following three-line statement as a script file, run this script file, just tips, 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; 11. The purpose of the introduction of COPY is especially useful when copying data between two databases, especially if the command can pass data of the LONG field between the two databases. Disadvantages: When data is transmitted between two databases, it is possible to lose precision. 12. Ask what is my script becomes very slow when modifying a lot of rows? 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. Transferee: http://www.cnoug.org/viewthread.php? TID = 31451
