1.尽可能了解oracle的功能，因为很多业务逻辑oracle已经为我们做了，比如oracle已经预定义了大量的异常代码，我们不必要写自己的异常而增加代码的复杂度。
例如oracle定义了当找不到符合要求的数据时定义的异常：no_data_found，而很多开发人员经常使用自己的逻辑去判断是否找到数据。
1 declare 2 ln_deptno number; 3 ln_tempno number:=1; 4 begin 5 select deptno into ln_deptno 6 from scott.dept 7 where deptno=ln_tempno; 8 dbms_output.put_line(ln_deptno); 9 exception10 when no_data_found then11 dbms_output.put_line('No deptno for: '||ln_tempno);12 end;13 /14 15 16 SQL> @a.sql;17 No deptno for: 118 19 PL/SQL procedure successfully completed. 2.管线表函数,可以把她的返回值作为像数据库表 一样在查询语句中使用。
1 create or replace type color_hex_code as object 2 ( 3 color_name varchar2(10), 4 color_code varchar2(10) 5 ); 6 / 7 create or replace type color_type as table of color_hex_code; 8 / 9 create or replace function f_pipeline_test return color_type pipelined as10 begin11 pipe row(color_hex_code('black','#000000'));12 pipe row(color_hex_code('red','#ff0000'));13 pipe row(color_hex_code('green','#00ff00'));14 pipe row(color_hex_code('blue','#0000ff'));15 pipe row(color_hex_code('white','#ffffff'));16 return;17 end;18 /19 20 21 SQL> select * from table(f_pipeline_test);22 23 COLOR_NAME COLOR_CODE24 ---------- ----------25 black #00000026 red #ff000027 green #00ff0028 blue #0000ff29 white #ffffff 3.传址函数的几种类型
1 create or replace function adding 2 (a in number,b in out number) 3 return number is 4 begin 5 b :=b+1; 6 return a+b; 7 end; 8 / 9 variable one number;10 /11 variable two number;12 /13 begin14 :one :=1;15 :two :=0;16 end;17 /18 variable output number;19 /20 call adding(:one,:two) into :output;21 /22 call adding(:one,:two) into :output;23 /24 25 26 27 select :one, :two from dual;28 29 :ONE :TWO30 ---------- ----------31 1 4
4.使用rollup对分组数据进行汇总
1 select deptno,sum(sal) from emp where deptno is not null2 group by rollup(deptno);3 4 DEPTNO SUM(SAL)5 ---------- ----------6 10 87507 20 108758 30 94009 29025
5.使用cube对分组内每一组的数据和总数据同时汇总，同时包含对不同分组依据进行汇总
1 select deptno,job,sum(sal) from emp where deptno is not null 2 group by cube(deptno,job) 3 order by deptno,job； 4 5 6 DEPTNO JOB SUM(SAL) 7 ---------- --------- ---------- 8 10 CLERK 1300 9 10 MANAGER 245010 10 PRESIDENT 500011 10 875012 20 ANALYST 600013 20 CLERK 190014 20 MANAGER 297515 20 1087516 30 CLERK 95017 30 MANAGER 285018 30 SALESMAN 560019 30 940020 ANALYST 600021 CLERK 415022 MANAGER 827523 PRESIDENT 500024 SALESMAN 560025 2902526 27 18 rows selected.
6.使用grouping sets 只显示汇总，不列出原始分组数据
1 SELECT deptno,job,SUM(sal) 2 FROM emp 3 WHERE deptno IS NOT NULL 4 GROUP BY GROUPING SETS(deptno,job) 5 ORDER BY deptno,job; 6 7 DEPTNO JOB SUM(SAL) 8 ---------- --------- ---------- 9 10 875010 20 1087511 30 940012 ANALYST 600013 CLERK 415014 MANAGER 827515 PRESIDENT 500016 SALESMAN 5600
7.grouping_id函数可以用来标记汇总数据的行，比如第一个维度的汇总用1表示，第二个维度的汇总用2表示，两个维度的所以汇总用3表示。因此可以过滤未汇总的行。
1 ELECT deptno,job, 2 GROUPING_ID(deptno,job),SUM(sal) 3 FROM emp 4 WHERE deptno IS NOT NULL 5 GROUP BY CUBE(deptno,job) 6 HAVING GROUPING_ID(deptno,job) > 0 7 ORDER BY deptno,job; 8 9 10 DEPTNO JOB GROUPING_ID(DEPTNO,JOB) SUM(SAL)11 ---------- --------- ----------------------- ----------12 10 1 875013 20 1 1087514 30 1 940015 ANALYST 2 600016 CLERK 2 415017 MANAGER 2 827518 PRESIDENT 2 500019 SALESMAN 2 560020 3 29025
8.利用子查询克隆表结构
1 CREATE TABLE emp2 2 AS 3 SELECT * 4 FROM scott.emp 5 WHERE 1=2; 6 7 8 9 SQL> SELECT * FROM emp2;10 11 no rows selected 12
9.用户查看自身表的使用情况(user_extents)
1 SELECT segment_name,segment_type, 2 tablespace_name, 3 bytes/1024/1024 "size", 4 blocks from user_extents; 5 6 7 SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME size BLOCKS 8 -------------------- ------------------ ------------------------------ ---------- ---------- 9 ORDERS TABLE USERS .0625 810 EMP TABLE USERS .0625 811 EMP2 TABLE USERS .0625 8 12
10.用户查看自身表的使用情况(user_segments)
1 SELECT segment_name,segment_type,tablespace_name, 2 bytes/1024/1024 byt,buffer_pool 3 FROM user_segments; 4 5 SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYT BUFFER_ 6 ---------------------------------------- ------------------ ------------------- ---------- ------- 7 EMP2 TABLE USERS .0625 DEFAULT 8 EMP TABLE USERS .0625 DEFAULT 9 ORDERS TABLE USERS .0625 DEFAULT10 BIN$iZfhhmba19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT11 BIN$iZfhhmbZ19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT12 BIN$iZfhhmbY19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT13