oracle

最新推荐文章于 2025-09-05 14:24:44 发布

转载最新推荐文章于 2025-09-05 14:24:44 发布 · 49 阅读

文章标签：

#数据库

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了一下，总结如上。