Sqlplus Study Notes

1. What is sqlplus? Sqlplus is an interactive tool for batch query. Sqlplus is installed for each oracle database. Sqlplus works in the command line mode. SQL * Plus has its own command set and environment. Sqlplus provides users with a way to access the database. Sqlplus can execute SQL, PL/SQL, SQL * Plus, and operating system commands to provide the following functions: -- format, execute operational commands, store, and output query results -- check the definition of tables and objects in the database -- develop and Run batch processing scripts -- execute Database Management -- query results of the database output to a text file, screen, or HTML file: sqlplus is a common oracle Database client. 2. environment variables related to sqlplus specify the path of some commands used by sqlplus, or affect the running behavior of sqlplus. ORACLE_HOME: Specify the directory where oracle software is installed ORACLE_SID: Specifies the database instance, SQL * Plus optional PATH: Specifies the executable program search PATH, windows environment is also used to specify the DLLs PATH. Generally, it contains ORACLE_HOME/bin TNS_ADMIN: when connecting to the remote oracle, specify the location of tnsnames. ora LD_LIBRARY_PATH: Specify the search path of the dynamic library in Unix and Linux systems. Windows is not required. SQLPATH: Specify the SQL script search path to be executed. 3. How to run SQL * Plus? Sqlplus is generally installed in the $ ORACLE_HOME/bin PATH. After setting the PATH, you can directly execute sqlplus on the command line: Symbol conventions of the following commands: "$" indicates the system command prompt "[]" indicates the option "|" indicates that only one option can appear in the "{}" option on both sides of the system, cannot coexist $ sqlplus [[[options] [logon] [start]. options has the following syntax:-H [ELP] |-V [ERSION] |... | [-S [ILENT] displays help, version, and other information. logon has the following syntax: {username [/password] [@ connect_indentifier] |/} [AS {SYSOPER | SYSDBA | SYSASM}] |/nolog specifies the username, password, and system privileges for logging on to the database, or (/nolog) Start sqlplus only if the database is not connected, or use operating system authentication only. Tip: it is best not to keep up with the password after sqlplus, because in the task manager or with ps-ef | grep oracle, the start syntax is as follows: @ {url | file_name [. ext]} [arg...] specify some scripts that can be executed immediately after entering sqlplus. 4. There are three types of commands that can be executed by sqlplus: SQL command: PL/SQL block working through the database: run the SQL * Plus command to work with the database: format query results, set options, edit and store SQL commands, PL/SQL block 5, sqlplus SQL Buffer to store the last executed SQL command or PL/SQL block. Yes. Run the LIST command to view the commands in the buffer. Or execute the RUN or/command to execute the commands in the buffer. Run the SAVE command to SAVE the content in the file. Note: Do not save the SQL * Plus command in the buffer zone! 6. Install the command line Help Command Line Help is usually installed during oracle database installation. If no installation is available, DBA can execute some scripts for installation. Step 1: log on to SQL * Plus as a SYSTEM user Step 2: Execute hlpbld. SQL and helpus. sqlSQL> @ $ ORACLE_HOME/sqlplus/admin/help/hlpbld. SQL uninstallation Command Line help: SQL> @ $ ORACLE_HOME/sqlplus/admin/help/helpdrop. SQL 7. Run the exit SQL * Plus command and enter EXIT or QUIT, Ctrl + D in Unix, or Ctrl + Z in windows. 8. view the structure of a table SQL> describe table _ or _ view name SQL> desc table _ or _ view name SQL> describe PL/SQL function _ or _ process name 9, how to end an SQL command? There are three ways to end the SQL command: Use ";". Run "/" and start with another line. Execute a blank line. Run the host command SQL> host ls-l/tmp in sqlplus or not "! "(Linux) SQL>! Ls-l/tmp can also use "$" (windows) SQL> $ dir c: or directly execute "! ", Restart shell SQL>! $ Ls $ exitSQL> 11. The pause query result is displayed. The syntax is as follows: set pau [SE] {ON | OFF | text}. If set pause on is SET, you can set PAESIZE to determine how many rows of data are paused at a time. set pause text specifies the interval between each screen. SQL> set pause '^ ^ 'SQL> show pause 12. Automatic submission of sqlplus (autocommit) DML commands (insert, update, delete) need to execute the COMMIT command, the DCL (GRANT or REVOK) command, or the DDL command (create table) before being recorded in the database. Set AUTOCOMMIT to enable automatic submission of sqlplus. SQL> set autocommit onSQL> set autocommit immediateSQL> set autocommit 10 -- execute 10 DML statements to automatically submit SQL> set autocommit offSQL> show autocommit 13. Edit the script in sqlplus and use the START command., @, or @ can execute the script. You can also edit the script in sqlplus. In SQL> edit myscripts, "edit" can be specified: SQL> define _ editor = vi. myscript is saved in the current directory, and The. SQL suffix 14 is automatically added. How can I add comments to the script? There are three ways to add comments to the script: Use the: REMARK command: remark here is the remarks. It can also be abbreviated as REM usage:/* here is the remarks */usage: -- here is the remarks 15. Using SPOOL to save the query results can not only make the query results output on the screen, the results are also saved in the specified file. Syntax: SPO [OL] [file_name [. ext] [gr [ATE] | REP [tablespace] \ | APP [END] | OFF | OUT] 16. query the table space SQL that the user has.> select object_name, object_type from user_objects; 17. Replace SQL with variables> select * from m_table where id = & myid; Enter value for myid: 18. The following parameter sets echo on/off shows whether the command set feedback on/off to be executed in the script displays the number of rows returned after the select result. The prompt set linesize n sets a row. maximum number of characters displayed: set termout on/off whether the result set heading on/off is displayed on the screen when the script is executed. set pagesize n: set trimspool on/off whether to remove the blank character set trimout on/off at the end of the row in the output result when spool to the file. whether to remove the blank character set space on/off at the end of the output result row on the screen if you need sqlplus to automatically adjust these formats at next startup, you can save the preceding settings to $ ORACLE_HOME/sqlplus/admin/glogin. SQL file 19, column format col column_name format a50 set column_name to 50 characters wide col column_name display column format col column_name noprint Hidden Column CLEAR COLUMNS set display attribute of all COLUMNS to default BREAK display clear breaks settings for BREAK deletion settings