Oracle中比较适用的命令和操作技巧
日期：2014-05-16 浏览次数：20432 次
Oracle中比较实用的命令和操作技巧
oRACLE
1、查询系统的相关信息
SELECT * FROM USER_TABLES
SELECT * FROM USER_INDEXES;
2、修改表结构信息
*ALTER TABLE SCHEMA_NAME.TABLE_NAME MOVE TABLESPACE TTPRDDATA STORAGE(INITIAL 64K)
3、修改索引的结构信息
*ALTER INDEX SCHEMA_NAME.INDEX_NAME REBUILD STORAGE(INITIAL 64K)
4、查询/设置当前的SCHEMA
*SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')FROM DUAL;
*ALTER SESSION SET CURRENT_SESSION='QUIXD'
5、DB LINK
*SELECT * FROM DBA_DB_LINKS;
6、显示表的结构信息
*在command上输入：desc table tableName;
*select * from col where tname=upper('wbts_tmp');
*select * from user_tab_columns where table_name=upper('表名');
7、oracle错误信息提示及关键字
*DUP_VAL_ON_INDEX （oracle内部异常dup_val_on_index问题）
*NO_DATA_FOUND（没有找到信息）
*OTHERS (其他情况)
*通过SQLCODE/SQLERRM 来取得错误信息,想得到具体信息 通过 SQLERRM (SQLCODE)
*DUP_VAL_ON_INDEX 如果主键重复、则抛出此异常
*******************************************
*EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20103,'In adding Non Standard template name'||SQLERRM);
8、Oracle函数
*SUBSTR
*SIGN eg:select sign(900-800) from dual;值为1
*DECODE（可以代替if-then-elsif-then-else或switch语句）
*-1、0、1分别代表小于、等于、大于
*NVL 判断不能为空，如果为空的：nvl(字段名,0)，就是当你选出来的时候，这个字段虽然为空，但是显示的是0，当然这个0也可以换成其他东西，如： 1，2，3……
9、动态执行SQL语句
*V_SQL :='SELECT * FROM ' ||V_TBNAME;
EXECUTE IMMEDIATE V_SQL;
10、 Oracle转义
*select * from dba_tables where table_name like 'U/_%'ESCAPE'/' 单引号转义v_sql := 'select * from tb where t_co='||''''||value||'''';
11、ORacle系统经常用到的一些表
*dba_segments、DBA_INDEXES、DBA_TABLES....;
12、 FUNCTIONAL-BASED-INDEX
*SELECT * FROM DBA_INDEXES WHERE OWNER='TTPRD' AND INDEX_TYPE='FUNCTION-BASED NORMAL'
13、导出对象的方法
* select dbms_metadata.get_ddl('INDEX','QUIX_LEGACY_ID_XREF_IDX02') from dual;
14、创建历史表&向历史表中插入数据
*执行语句:CREATE TABLE 新表名 AS SELECT * FROM 旧表名;
*插入数据：INSERT INTO WBTS_T2 SELECT * FROM WBTS_T1;
15、定义一个参数的类型为游标类型
*TYPE quixnotescurtype IS REF CURSOR;
*TYPE trenddates IS RECORD (closedate DATE, submitdate DATE);
*TYPE trendtable IS TABLE OF trenddates INDEX BY BINARY_INTEGER;
16、重置用户密码
*alter user system identified by NewPasswordHere;
*备注：记得username and password values should add ""
17、INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)
*例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置
默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。
所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的显示结果是
Instring
14
18、Loop Methods(If you want to exit the loop,you can by the key word "exit"):
* LOOP
EXIT WHEN p_cursordata%NOTFOUND;
FETCH p_cursordata INTO v_trendtable (v_i);
v_i := v_i + 1;
END LOOP;
* FOR i IN 1 .. v_trendtable.LAST
LOOP
IF (i = 1)
THEN
v_mindate := v_trendtable (i).submitdate;
ELSIF (v_mindate > v_trendtable (i).submitdate)
THEN
v_mindate := v_trendtable (i).submitdate;
END IF;
END LOOP;
19、Trunc(for number)
*grammar:TRUNC（number[,decimals]）
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分
下面是该函数的使用情况：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以
oRACLE
1、查询系统的相关信息
SELECT * FROM USER_TABLES
SELECT * FROM USER_INDEXES;
2、修改表结构信息
*ALTER TABLE SCHEMA_NAME.TABLE_NAME MOVE TABLESPACE TTPRDDATA STORAGE(INITIAL 64K)
3、修改索引的结构信息
*ALTER INDEX SCHEMA_NAME.INDEX_NAME REBUILD STORAGE(INITIAL 64K)
4、查询/设置当前的SCHEMA
*SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')FROM DUAL;
*ALTER SESSION SET CURRENT_SESSION='QUIXD'
5、DB LINK
*SELECT * FROM DBA_DB_LINKS;
6、显示表的结构信息
*在command上输入：desc table tableName;
*select * from col where tname=upper('wbts_tmp');
*select * from user_tab_columns where table_name=upper('表名');
7、oracle错误信息提示及关键字
*DUP_VAL_ON_INDEX （oracle内部异常dup_val_on_index问题）
*NO_DATA_FOUND（没有找到信息）
*OTHERS (其他情况)
*通过SQLCODE/SQLERRM 来取得错误信息,想得到具体信息 通过 SQLERRM (SQLCODE)
*DUP_VAL_ON_INDEX 如果主键重复、则抛出此异常
*******************************************
*EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20103,'In adding Non Standard template name'||SQLERRM);
8、Oracle函数
*SUBSTR
*SIGN eg:select sign(900-800) from dual;值为1
*DECODE（可以代替if-then-elsif-then-else或switch语句）
*-1、0、1分别代表小于、等于、大于
*NVL 判断不能为空，如果为空的：nvl(字段名,0)，就是当你选出来的时候，这个字段虽然为空，但是显示的是0，当然这个0也可以换成其他东西，如： 1，2，3……
9、动态执行SQL语句
*V_SQL :='SELECT * FROM ' ||V_TBNAME;
EXECUTE IMMEDIATE V_SQL;
10、 Oracle转义
*select * from dba_tables where table_name like 'U/_%'ESCAPE'/' 单引号转义v_sql := 'select * from tb where t_co='||''''||value||'''';
11、ORacle系统经常用到的一些表
*dba_segments、DBA_INDEXES、DBA_TABLES....;
12、 FUNCTIONAL-BASED-INDEX
*SELECT * FROM DBA_INDEXES WHERE OWNER='TTPRD' AND INDEX_TYPE='FUNCTION-BASED NORMAL'
13、导出对象的方法
* select dbms_metadata.get_ddl('INDEX','QUIX_LEGACY_ID_XREF_IDX02') from dual;
14、创建历史表&向历史表中插入数据
*执行语句:CREATE TABLE 新表名 AS SELECT * FROM 旧表名;
*插入数据：INSERT INTO WBTS_T2 SELECT * FROM WBTS_T1;
15、定义一个参数的类型为游标类型
*TYPE quixnotescurtype IS REF CURSOR;
*TYPE trenddates IS RECORD (closedate DATE, submitdate DATE);
*TYPE trendtable IS TABLE OF trenddates INDEX BY BINARY_INTEGER;
16、重置用户密码
*alter user system identified by NewPasswordHere;
*备注：记得username and password values should add ""
17、INSTR(源字符串, 目标字符串, 起始位置, 匹配序号)
*例如：INSTR('CORPORATE FLOOR','OR', 3, 2)中，源字符串为'CORPORATE FLOOR', 目标字符串为'OR'，起始位置为3，取第2个匹配项的位置
默认查找顺序为从左到右。当起始位置为负数的时候，从右边开始查找。
所以SELECT INSTR('CORPORATE FLOOR', 'OR', -1, 1) "Instring" FROM DUAL的显示结果是
Instring
14
18、Loop Methods(If you want to exit the loop,you can by the key word "exit"):
* LOOP
EXIT WHEN p_cursordata%NOTFOUND;
FETCH p_cursordata INTO v_trendtable (v_i);
v_i := v_i + 1;
END LOOP;
* FOR i IN 1 .. v_trendtable.LAST
LOOP
IF (i = 1)
THEN
v_mindate := v_trendtable (i).submitdate;
ELSIF (v_mindate > v_trendtable (i).submitdate)
THEN
v_mindate := v_trendtable (i).submitdate;
END IF;
END LOOP;
19、Trunc(for number)
*grammar:TRUNC（number[,decimals]）
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分
下面是该函数的使用情况：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
