oracle 常用函數及啟動問題

 oracle常用函數：

<b>oracle </b><b>串函數</b>

|| 連接配接符

SQL&gt; select id||'  '||xingming from t11;

<b>TO_CHAR </b><b>是把日期或數字轉換為字元串</b>

SQL&gt; select to_char(123,'9999.00') from dual;

SQL&gt; select to_char(123,'$99,999.99') from dual;

<b>檢視時間</b>

SQL&gt; select sysdate from dual;

SQL&gt; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

SQL&gt; select gongzi from t9 where name='zs' and substr(to_char (d,'yyyy-mm-dd'),1,4)='2012';

<b>substr </b><b>截取函數</b>

SQL&gt; select substr('abcdef',2,3) from dual;

(截取從第二個字元開始的3個字元)

SQL&gt; select substr('abcdef',-5) from dual;

(截取後5位)

<b>聚集函數</b>

 avg(平均值)

SQL&gt; select avg(gongzi) from t9;

 sum(求和)

SQL&gt; select sum(gongzi) from t9;

 min(最小值)

SQL&gt; select min(gongzi) from t9;

 max(最大值)

SQL&gt; select max(gongzi) from t9;

 count(行計數)

SQL&gt; select count(*) from t9; 

<b>vm_concat</b><b>合并列</b>

SQL&gt; create table ww (id int,name varchar2(10),gz int);

Table created.

SQL&gt; insert into ww values (1,'zs',10000);

SQL&gt; select id,wm_concat(name) from ww group by id;

顯示每部門人名和工資

SQL&gt; select id,wm_concat(name||'('||gz||')') from ww group by id;

sqrt 求平方根

SQL&gt; select sqrt(64) from dual;

<b>Round </b><b>函數 (</b><b>四舍五入)</b>

SQL&gt; select round(123.123) from dual;

SQL&gt; select round(123.8) from dual； 

trunc 截掉小數點後值

SQL&gt; select trunc(123.8) from dual;

sign取數字n的符号,大于0傳回1,小于0傳回-1,等于0傳回0

SQL&gt; select sign(100),sign(-100),sign(0) from dual;

删除重複行

SQL&gt; select distinct(xingming) from t11;

<b>abs ceil </b><b>傳回絕對值</b>

SQL&gt; select abs(-5) from dual; (傳回值為5)

SQL&gt; select abs(5.5)  from dual;(傳回值為5.5)

SQL&gt; select ceil(5.4) from dual;(傳回值為6)

如果pfile和spfile全部丢失，如何啟動oracle？

[oracle@oracle ~]$ cd /oracle/app/admin/TEST/bdump/

[oracle@oracle bdump]$ ls

alert_TEST.log       test_lgwr_22103.trc  test_lgwr_8829.trc

test_lgwr_18215.trc  test_lgwr_4598.trc   test_lgwr_8928.trc

[oracle@oracle bdump]$ tail -f -n 200 alert_TEST.log 

  processes                = 150

  __shared_pool_size       = 79691776

  __large_pool_size        = 4194304

  __java_pool_size         = 4194304

  __streams_pool_size      = 0

  sga_target               = 285212672

  control_files            = /oracle/app/oradata/TEST/control01.ctl, /oracle/app/oradata/TEST/control02.ctl, /oracle/app/oradata/TEST/control03.ctl

  db_block_size            = 8192

  __db_cache_size          = 192937984

  compatible               = 10.2.0.1.0

  db_file_multiblock_read_count= 16

  db_recovery_file_dest    = /oracle/app/flash_recovery_area

  db_recovery_file_dest_size= 2147483648

  undo_management          = AUTO

  undo_tablespace          = UNDOTBS1

  remote_login_passwordfile= EXCLUSIVE

  db_domain                = 

  dispatchers              = (PROTOCOL=TCP) (SERVICE=TESTXDB)

  job_queue_processes      = 10

  background_dump_dest     = /oracle/app/admin/TEST/bdump

  user_dump_dest           = /oracle/app/admin/TEST/udump

  core_dump_dest           = /oracle/app/admin/TEST/cdump

  audit_file_dest          = /oracle/app/admin/TEST/adump

  db_name                  = TEST

  open_cursors             = 300

  pga_aggregate_target     = 94371840

将其粘到initTEST.ora

再重新啟動，如果發現有問題，就将initTEST.ora中的db_domain                = 

dispatchers              = (PROTOCOL=TCP) (SERVICE=TESTXDB) 兩行删除再重新開機即可。