常用pl/sql

重命名表------------------------------------------------
rename test2 to test
增加列--------------------------------------------------
alter table test add c2 numeric
刪除列--------------------------------------------------
1）alter table test set unused column c3
2）alter table test drop unused columns
修改列--------------------------------------------------
alter table test modify(c3 numeric(5,3))
創建序列
create sequence BUFFER_FORMULA_seq nomaxvalue nocycle

常用函數用法--------------------------------------------
select least(1,2,5,6) from dual               //取最小者greatest(2,4,7)取最大者
select ascii('O') from dual
select chr(10) from dual
select concat('sdfds','UIOP') from dual        //連接兩個字符串
select initcap('sdfds')  from dual             //首字母大寫
select instr('sdfsds','s',2,2)  from dual      //返回在C1中從C3開始查找第C4個C2的位置
select length('sdfsds')  from dual
select lower('SDFSDS')  from dual              //小寫轉換
select upper('sdfsds')  from dual              //大寫轉換
select lpad('sdfsds',10,'@')  from dual        //在C1左邊用C3將C1補足C2位(返回@@@@sdfsds)
select ltrim('  sd  fsds ')  from dual         //去除C中左邊的空格
select replace('sdfsds','sd','SD')  from dual  //將C1中的每處C2都用C3替換
select substr('sdfsds',3,2)  from dual         //返回C1中從C2開始的C3個字符
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
select decode(sign(2-2),1,'>0',0,'=0','?') from dual   //正數返回'>0',0返回=0,else 返回 ？
select vsize('ab') from dual                   //返回C1的字節數
soundex
translate
 raise_application_error('aaaaaaaaaaa')  //拋錯

分析(匯總)函數
select c2,c3,sum(c3) from test group by rollup(c2,c3)
select c2,c3,sum(c3) from test group by cube(c2,c3) order by c2,c3

常用的時間操作--------------------------------------------
select to_date('14-11月-05','DD-Mon-YYYY')-to_date('5-10月-05','DD-Mon-YYYY') from dual  //日期間的天數差C1-C2=40
select add_months('14-11月-05',-5) from dual    //在C1上加上C2個月
select to_date('14-11月-05','DD-Mon-YYYY')+30 from dual  //在轉換成的日期上加上30天
select last_day('14-11月-05') from dual         //C1月份的最後一天
select next_day('14-11月-05','星期一') from dual  //C1的下周一
select months_between('14-11月-2005','14-1月-2005') from dual  //兩個日期之間的月份差C1-C2=10

常用的時間格式--------------------------------------------

DY-------DAY OF WEEK ABBREVIATED----------MON,TUE,FRI......
DAY------DAY OF WEEK SPELLED OUT----------MONDAY,TUESDAY,FRIDAY......
D--------DAY OF WEEK(1--7)----------------1,2,3,4,5,6,7
DD-------DAY OF MONTH(1-31)---------------1,2,3,.....31
DDD------DAY OF YEAR(1--366)--------------1,2,3,.....366
W--------WEEK OF THE MONTH----------------1,2,3,4,5
WW-------WEEK OF THE YEAR-----------------1,2,3,.....53
MM-------TWO-DIGIT MONTH------------------01,02,03,....12
MON------MONTH NAME ABBREVIATED-----------JAN,FEB,MAR....
MONTH----MONTH NAME SPELLED OUT-----------JANUARY,FEBRUARY.....
YYYY------------YEAR
SS--------------SECOND
HH,HH12,HH24---------HOUR
MI-------------------MINUTES

相关的几个JOB操作--------------------------------------------
添加job:dbms_job.submit(jobno,'test;',sysdate,'sysdate+1/1440');
删除job:dbms_job.remove(jobno);
修改要执行的操作:job:dbms_job.what(jobno,what);
修改下次执行时间：dbms_job.next_date(job,next_date);
修改间隔时间：dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);