--通过表找存储过程名称 select * from dba_source a where upper(a.text) like upper('%sp_kr_bimv_sms_busi_day%'); 过程查看表 dgdm_kr.sh_mon_def CMD_NAME 查询表和分区信息 user_tab_partitions TABLE_NAME PARTITION_NAME 查询数据库名称 select sys_context('userenv','db_name') from dual; 左连接 select a.*,b.* from tb_tzh_test1 a left outer join tb_tzh_test2 b on a.name = b.name; 右连接 right outer join 外连接 full outer join 改变时间格式 alter session set nls_date_format = 'yyyy-mm-dd'; create table dgdm_mk.tb_mk_yjq_20101025_01_tmp5_2 tablespace tbs_mk_fact_data_2 nologging as 取最接近的一个整数(大) select ceil(12.1) from dual; 13 regexp_like的使用 select * from smsorgprs_dic where regexp_like(priv_name,'.[^[:digit:]]5元.*'); trunc(months_between(sysdate,a.ACTIVE_TIME)) select count(distinct a||b) from c; 左连接 a=b(+) b(+)=c select deptno,ename,sal, sum(sal) over (order by ename) 累计, --按姓名排序，并将薪水逐个累加 分析函数 Select EMPLOYEENAME,SALARY, RANK() OVER (Order By SALARY Desc Nulls Last) "RANK", --7 7 9 DENSE_RANK() OVER (Order By SALARY Desc Nulls Last) "DENSE_RANK", --7 7 8 ROW_NUMBER() OVER(Order By SALARY Desc Nulls Last) "ROW_NUMBER" --7 8 9 From EMPLOYEEINFO 增加一列 ALTER TABLE TABLE_AAA ADD STOCKMAN NUMBER(18) 修改一列 ALTER TABLE TABLE_AAA modify STOCKMAN NUMBER(20) 删除一列 ALTER TABLE TABLE_AAA drop column STOCKMAN 增加多列： 增加多列 alter table emp4 add (test varchar2(10),test2 number); 修改多列：alter table emp4 modify (test varchar2(20),test2 varchar2(20)); 删除多列： alter table emp4 drop (test,test2); select distinct a,b from c 只有当a 和 b 同时相等时才会被排除掉
