PL SQL practice (2)

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

  1 as far as possible to understand the function of Oracle, because a lot of the business logic in the Oracle has done for us, for example, Oracle has a predefined exception code a lot of complexity, we don't need to write your own exceptions and increase code.

For example, Oracle defines abnormal definition when unable to meet the requirements of the data: no_data_found, and many developers often use their own logic to determine whether the 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 exception 10 when no_data_found then 11 dbms_output.put_line('No deptno for: '||ln_tempno); 12 end; 13 / 14 15 16 SQL> @a.sql; 17 No deptno for: 1 18 19 PL/SQL procedure successfully completed.

  The 2 line table function, can put her return value as the database table is used in the query statement.

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 as 10 begin 11 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_CODE 24 ---------- ---------- 25 black #000000 26 red #ff0000 27 green #00ff00 28 blue #0000ff 29 white #ffffff

  Several types of 3 address transfer function

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 begin 14 :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 :TWO 30 ---------- ---------- 31 1 4

  4 using rollup for packet data summary

1 select deptno,sum(sal) from emp where deptno is not null 2 group by rollup(deptno); 3 4 DEPTNO SUM(SAL) 5 ---------- ---------- 6 10 8750 7 20 10875 8 30 9400 9 29025

  At the same time, summary of the 5 on the use of cube packets within each group of data and general data, also includes different packet basis summary

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 2450 10 10 PRESIDENT 5000 11 10 8750 12 20 ANALYST 6000 13 20 CLERK 1900 14 20 MANAGER 2975 15 20 10875 16 30 CLERK 950 17 30 MANAGER 2850 18 30 SALESMAN 5600 19 30 9400 20 ANALYST 6000 21 CLERK 4150 22 MANAGER 8275 23 PRESIDENT 5000 24 SALESMAN 5600 25 29025 26 27 18 rows selected.

  6 using grouping sets will only display the summary, not listed in the original packet data

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 8750 10 20 10875 11 30 9400 12 ANALYST 6000 13 CLERK 4150 14 MANAGER 8275 15 PRESIDENT 5000 16 SALESMAN 5600

  The 7.grouping_id function can be used to mark the summary data, such as the first dimension of the aggregate with 1, second dimensions of the aggregate with 2, two dimensions so summary 3. It can filter not summary rows.

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 8750 13 20 1 10875 14 30 1 9400 15 ANALYST 2 6000 16 CLERK 2 4150 17 MANAGER 2 8275 18 PRESIDENT 2 5000 19 SALESMAN 2 5600 20 3 29025

  8 use the subquery clone table structure

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 view usage its table(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 8 10 EMP TABLE USERS .0625 8 11 EMP2 TABLE USERS .0625 8 12

  10 user view usage its table(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 DEFAULT 10 BIN$iZfhhmba19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT 11 BIN$iZfhhmbZ19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT 12 BIN$iZfhhmbY19rgQKjAZQETvA==$0 TABLE USERS .0625 DEFAULT 13
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Sebastian at November 30, 2013 - 3:44 AM