Oracle_ single-line function _ceil floor ROUND TRUNC

Source: Internet
Author: User
Tags truncated

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.