oracle

SQL*Plus 数据库基本操作

电脑与网络

  以前并没有很在意Oracle的SQL*Plus操作方面的东西，上次的项目里用得比较多一点，老是一种套路，弄起来很麻烦，别人一看就明白：新警察吧？

  这回哥哥我学了两手：

•   有用的dual表
  SQL> select user from dual;
  SQL> select sysdate from dual;
  SQL> select power(4,3) from dual;
  will generates the following output:
  USER
  ------------------------------
  STORACLE
  SYSDATE
  ---------
  22-JAN-99
  POWER(4,3)
  ----------
   64
  
• Use "Describe" To Get Table Definition
  The SQLPlus command describe returns the definitions of tables and views. For example, information about your tables are stored in the table TABS. However, since you do know the columns names of that table, you can not query that table. The command
  SQL> describe tabs;
  他会列出表空间的属性，其中有一个TABLE_NAME NOT NULL VARCHAR2(30)
  SQL> select table_name from tabs order by table_name;
  他会列出当前用户下的所有表的表名，使用describe命令还可以列出其他系统views的信息
  View     Name Description
  ———————————————
  DICT table names and table description
  DICT_COLUMN column names of table names and column description
  CAT names of all user's tables, views, synonyms, and sequences
  OBJ information on all objects in your account
  TABS table information on all user's tables
  COLS column information on all user's columns
  USER_VIEWS view information on all user's views
  SYN synonyms information on all user's synonyms
  SEQ sequence information on all user's sequences
  USER_CONSTRAINTS constraint information on user's constraints
  USER_CONS_COLUMNS column information on user's constraints
  IND index information on all user's indices
  USER_IND_COLUMNS column information on user's indices
  
• Use "Column" To Set Column Output Length
  The output format for columns values are determined by the column data definition. 
  SQL>column full_name format a24;
  SQL>select * from test_table;
  可以定义SQL*Plus输出结果中某一列的输出格式，格式"a24"代表24个字母，还可以使用SET命令来设置Environment:
  SQL>set numwidth 2; -- 设置数字的位数
  SQL>set linesize 20; -- 设置每一行输出的字母个数，也可以通过SQL*Plus的菜单打开配置项
  
• 设置命令行之间的分隔符
  SQL> SET CMDSEP + -- 命令行之间可以使用'+'号来连接
  SQL> TTITLE LEFT 'SALARIES' + COLUMN SAL FORMAT $9,999
  
• 设置输出的表格的各个列之间使用某一符号隔开
  To set the column separator to "|" enter
  SQL> SET COLSEP '|'
  SQL> SELECT ENAME, JOB, DEPTNO
  2 FROM EMP
  3 WHERE DEPTNO = 20;
  
• 同时按下Shift+Del清空屏幕
• CHANGE 命令可以改变当前行中的值
  Change text on the current line.
  SQL>C /oldval/newval
• 调试时打开输出
  SQL> SET SERVEROUTPUT ON FORMAT WORD_WRAPPED
  SQL> SET LINESIZE 20
  SQL> BEGIN
  2 DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
  3 DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
  4 end;
  5 /
  If there is nothing
  left to do
  shall we continue
  with plan B?
  
  To set the output to TRUNCATED, enter

SQL> SET SERVEROUTPUT ON FORMAT TRUNCATED
SQL> SET LINESIZE 20
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('If there is nothing left to do');
3 DBMS_OUTPUT.PUT_LINE('shall we continue with plan B?');
4 END;
5 /
If there is nothing
shall we continue with plan B?

  一些小Tips，顺手拈来，不需要过多的学习，参考http://www.zh-aptech.com.cn/student/study-text.asp?id=126可以得到一个比较详细的列表。

  上次在南山图书城里看到一本《关系数据库与SQL》的国外教材，一系列红色皮子的那种，第6章还是哪一章讲解的很好，可惜当时没有带DC咔嚓下来，买下来又不划算，事后回忆了一下，google了一下，总结如上。