Oracle_ single-line function _ceil floor ROUND TRUNC

Single-line function the first part 1th   numeric function--1  Create a user-specified tablespace--1.1  view the current tablespace state col tablespace_name for a15; col file_name for a50; select tablespace_name,file_id,file_name from dba_data_files;--1.2  Creating a directory mkdir -p  /u01/app/oracle/oradata/prod/disk6/--1.3  CREATE TABLE Space create tablespace dblion datafile  '/u01 /APP/ORACLE/ORADATA/PROD/DISK6/TB_DBLION01.DBF '  SIZE 100M;--1.4  create user and specify default table empty Create user  dblion IDENTIFIED BY dblion DEFAULT TABLESPACE DBLION;--1.5  Authorized Grant  RESOURCE,CONNECT TO dblion; the first part 1th Chapter   numerical function--1. function Classification   1      2      3      4           5       61 ceil  floor   round  trunc2 abs   sign   mod    remainder  bitand3 sin   cos    tan     asin       acos    atan 4 exp    LN     POWER  LOG         sqrt5 nanvl width_bucket99 tan2 cosh sinh tanh -- Subclass 99 does not speak--2. summary function Table Create table t_function (FID VARCHAR2 (8),         --function Idfname varchar2,      --function name Parm_list varchar2 (30),  --parameter list ffunc varchar2 (+),     --function function ret_type varchar2 (  )  --return value type);--rule: fid 12 denotes function Large Class 34 subclass 5 whether to explain 6 reserved 78 is the ordinal--4 in the subclass. Details--=========================================== ================================== function Id  :1 function name: ceil parameter list: ceil (n) function:  Returns the value type for which the rounding up is returned: number-------------------------------------------------------------------------------insert into t_function (Fid,fname,parm_list,ffunc,ret_type)  values (' 01011001 ', ' ceil ', ' Ceil (n) ', ' returns the value of rounding up ', ' number '); COMMIT; select * from t_function;---------------------------------------------------------------------- -----------Rounding up Select   ceil (9),     --9  ceil (9.1),   -- 10  ceil (9.49),  --10  ceil (8.49),  --9  ceil (8.51), --9   ceil (8.99)   --9from dual;--type conversion Select   ceil (' 9 '),     --9  ceil (' 9.1 '),   --10  ceil (' 9.49 '),  --10  ceil (' 8.49 '),  - -9  ceil (' 8.51 '),  --9  ceil (' 8.99 ')   --9from dual;--expression select    ceil (8 + 1.0),   --9  ceil (8 + 1.1),  --10   ceil (8 + 1.51),  --10  ceil (8 + 0.49),  --9  ceil (8 + 0.51),  --9  ceil (8 + 0.99)   -- 9from dual;--============================================================================= function id   :2 function Name: Floor parameter list: floor (n) function function:  Returns a value type for the value returned by rounding down: number-------------------------------------------------------------------------------INSERT  into t_function (Fid,fname,parm_list,ffunc,ret_type)  values (' 01011002 ', ' floor ', ' Floor (n) ', ' Returns the value of rounding down ', ' number '); COMMIT; select * from t_function;---------------------------------------------------------------------- -----------Rounding down Select   floor (9),     --9  floor (9.1),   --9  floor (9.49),  --9  floor (8.49),  --8  floor (8.51), --8   floor (8.99)   --8from dual;--type conversion select   floor (' 9 '),     --9  floor (' 9.1 '),   --9  floor (' 9.49 '), --9  floor (' 8.49 '),  --8  floor (' 8.51 '),  --8  floor (' 8.99 ')    --8from dual;--expression Select   floor (8 + 1.0),   --9  floor (8 + 1.1),   --9  floor (8 + 1.51),  --9  floor (8 +  0.49),  --8  floor (8 + 0.51),  --8  floor (8 + 0.99)   --8FROM DUAL; Memory:ceil  Ceiling So it's rounding up the floor   floor so it's rounding down  --======================= ====================================================== function Id  :3 function name: ROUND parameter list: ROUND (N[,integer]) function function:   Returns the value type after rounding: number-------------------------------------------------------------------------------INSERT  into t_function (Fid,fname,parm_list,ffunc,ret_type)  values (' 01011003 ', ' ROUND ', ' ROUND (n[, INTEGER]) ', ' Returns the rounded value ', ' number '); COMMIT; select * from t_function;-------------------------------------------------------------------------------select   round (0),         - -  0  round (' 0 '),       --  0  round (  10.1234),  --  10  round ( -10.1234),  -- -10  round (   9.9999),  --  10  round ( -9.9999)   -- -10FROM DUAL; Select   round (123.123456789)   COL01,   --  123   ROUND (123.123456789,0)  col11, --  123  round (123.123456789,1)  COL12, - -  123.1  round (123.123456789,2)  col13, --  123.12  round ( 123.123456789,3)  col14, --  123.123  round (123.123456789,4)  COL15, --   123.1235   round (123.123456789,-1)  col16, -- 120  round ( 123.123456789,-2)  col17,  -- 100  round (123.123456789,-3)  col18  -- 0 from dual;= Floor (N * power (10, integer)  + 0.5)  * power (10, -integer) SELECT    floor (123.123456789 * power (10, 3)  + 0.5)  col1,  floor ( 123.123456789 * power (10, 3)  + 0.5)  * power (10, -3)  COL2,   floor (123.123456789 * power (10, 4)  + 0.5)  col3 ,  floor ( 123.123456789 * power (10, 4)  + 0.5)  * power (10, -4)  COL4FROM  dual; --============================================================================= function id   :4 function Name: TRUNC parameter list: TRUNC (N[,integer]) function:  Returns the value type after truncation: number-------------------------------------------------------------------------------insert  Into t_function (Fid,fname,parm_list,ffunc,ret_type)  values (' 01011004 ', ' TRUNC ', 'TRUNC (N[,integer]) ', ' Returns the truncated value ', ' number '); COMMIT; select * from t_function;---------------------------------------------------------------------- ---------Select   trunc (0),         --  0   trunc (' 0 '),       --  0  trunc ( 10.1234),  --  10  trunc ( -10.1234),  -- -10  trunc (  9.9999),  --  9  trunc ( -9.9999)   -- -9FROM DUAL; Select   trunc (123.123456789)  col01,   --  123  trunc ( 123.123456789,0)  col11, --  123  trunc (123.123456789,1)  COL12, --   123.1  trunc (123.123456789,2)  col13, --  123.12  trunc ( 123.123456789,3)  col14, --  123.123  trunc (123.123456789,4)  COL15, --   123.124 --4 is truncated behind   trunc (123.123456789,-1)  col16, -- 120  trunc ( 123.123456789,-2)  col17, -- 100  trunc (123.123456789,-3)  COL18 FROM  DUAL;

This article is from the "Coperator" blog, make sure to keep this source http://coperator.blog.51cto.com/4441241/1593915

Oracle_ single-line function _ceil floor ROUND TRUNC