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
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。
20、Round(for number)
*grammar:ROUND( number, decimal_places )
select round(123.456, 0) from dual; 回传 123
select round(123.456, 3) from dual; 回传 123.456
21、动态执行SQL 语句
*    EXECUTE IMMEDIATE 'select ts_name_new from yan.ts_users_reform t where ts_id=:vts_id'
            INTO   sts_name
            USING nts_id;（变量赋值）
22、Union vs Union All ====Intersect vs Minus
*UNION在进行表链接后会筛选掉重复的记录
*在查询中会遇到 UNION ALL,它的用法和union一样,只不过union含有distinct的功能,它会把两张表了重复的记录去掉,而union all不会,所以从效率上,union all 会高一点,但在实际中用到的并不是很多
*************************************************************************8
*ntersect和Minus的操作和Union基本一致，这里一起总结一下：
*Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
*Union All，对两个结果集进行并集操作，包括重复行，不进行排序；
*Intersect，对两个结果集进行交集操作，不包括重复行，同时进行默认规则的排序；
*Minus，对两个结果集进行差操作，不包括重复行，同时进行默认规则的排序。

可以在最后一个结果集中指定Order by子句改变排序方式。
23、Exists

24、ALL_USERS vs DBA_USERS vs USER_USERS
*Related Views
*DBA_USERS describes all users of the database, and contains more columns than ALL_USERS.
*USER_USERS describes the current user, and contains more columns than ALL_USERS.
*ALL_USERS lists all users of the database visible to the current user. This view does not describe the users (see the related views).
25、INITCAP 将第一个字母转换成大写
26、select * from tb for update 、select  * from tb, td for update of tb.field1(只锁定tb 这个表)
*关于NOWAIT(如果一定要用FOR UPDATE，我更建议加上NOWAIT)
*如果加了for update后 该语句用来锁定特定的行（如果有where子句，就是满足where条件的那些行）。当这些行被锁定后，其他会话可以选择这些行，但不 能更改或删除这些行，直到该语句的事务被commit语句或rollback语句结束为止。
因为FOR   UPDATE子句获得了锁，所以COMMIT将释放这些锁。当锁释放了，该游标就无效了。
27、Sotre procedure/Function diff
*function 与 procedure 的比较
--都可以通过使用out参数返回一个以上的值
--当返回多个值时,用procedure较好
--当返回一个值时,用function较好
--function可以在sql语句中调用,而procedure则不可
28、跳转loop----->>>>continue               终止loop------>>return
29、Oracle中，单独申明一个Varchar2类型的字段field1，它定义的长度应为 <=32767
        如果在表(或者是视图)中定义一个类型为Varchar2的列column1，它的最大长度不能超过4000.

30、函数LENGTHB这个方法不能对Clob的字段进行操作，如果想获取Clob字段的长度应用函数DBMS_LOB.getlenght(CLOB)
32、In oracle. & is a key word. if using it.you should use ascii as  chr(39)
33、Set table’s column datatype ：usage>>ALTER TABLE
wbts_tmp MODIFY
(
STATUS varchar2(1) not null
)
34、Pls get the first five places  via sql ：YOU NEED USE RANK  FUNCTION
*SELECT * FROM
( SELECT RANK() OVER(PARTION  BY GENDER  BY GRADE)  RK,
ST.* FROM TS_STUDENT ST)  
   WHERE RK <=5;

*row_number() 按序分配序列,如: 1, 2 ,3 ,4 ,5
*rank() 并列排名会占取下一个名次，如： 1, 2, 2, 4, 5
*dense_rank() 并列名次不会占取下一名次,如：1, 2, 2, 3, 4, 5
35、Unlock a user account
*Command ： ALTER USER username ACCOUNT UNLOCK;
36、 Make sure the output words can display in command window, you should use below command:
*SET SERVEROUTPUT ON
37、If you wann to get millisecond you should  use SYSTIMESTAMP not the SYSDATE
*MILLISECOND FORMAT PATTERN: .FF  e.g: YYYY/MM/DD HH24:MI:SS.FF
38、Startup the listener
*lsnrctl startup listener
39、According the condition to insert records to difference table
*eg：insert all when
  (account_name = 'A') then  into quixpct.wbts_tmp
  (account_name,STATUS)
values
  (account_name || '>>A','N')
else into quixpct.wbts_tmp(account_name,STATUS) values(account_name || '>>AAA','Y') select account_name from quixpct.wbts_tmp;