ORACLE_ one-line function _ CEIL FLOOR ROUND TRUNC

One-line functions part 1 Chapter 1 numeric functions-- 1 create the tablespace that the user specifies to use-- 1. 1 View the current tablespace status COL tablespace_name FOR a15 ten Col file_name FOR A50 select tablespace_name,file_id,file_name FROM dba_data_files -- 1.2 create directories mkdir-p / u01/app/oracle/oradata/PROD/disk6/--1.3 create tablespaces CREATE TABLESPACE DBLION DATAFILE'/ u01qapax oradata CREATE TABLESPACE DBLION DATAFILE'/ u01qapax oradata CREATE TABLESPACE DBLION DATAFILE'/ u01qqqqoradataplash disk6Uniplictbaked dblion01.dbf' SIZE 100Mrip1.4 create the user and specify the default table empty CREATE USER dblion IDENTIFIED BY dblion DEFAULT TABLESPACE DBLION;--1.5 authorization oracle part 1 chapter 1 numeric 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 say-- 2. Summarize the function table CREATE TABLE T_FUNCTION (FID VARCHAR2 (8),-- function IDFNAME VARCHAR2 (20),-- function name PARM_LIST VARCHAR2 (30),-- Parameter list FFUNC VARCHAR2 (100),-- function function RET_TYPE VARCHAR2 (20)-- return value type;-- Rule: FID 12 represents the function class 34 subclass 5 whether to explain 6 retain 78 as the subclass serial number-4. Details-- = function ID: 1 function name: CEIL parameter list: CEIL (n) function: return the value rounded up return value type: NUMBER -INSERT INTO T_FUNCTION (FID FNAME,PARM_LIST,FFUNC,RET_TYPE) VALUES ('01011001', 'NUMBER'', 'return rounded up', 'CEIL (n)') COMMIT;SELECT * FROM T_FUNCTION Round 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 -- expressions 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: return the value rounded down return value type: NUMBER----INSERT INTO T_FUNCTION (FID) FNAME,PARM_LIST,FFUNC,RET_TYPE) VALUES ('01011002' rounded 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 -- expressions 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 FLOOR floor is rounded up, so it is rounded down-- = function ID: 3 function name: ROUND parameter list: ROUND (n [ INTEGER]) function: returns the rounded value return type: NUMBER----INSERT INTO T_FUNCTION (FID,FNAME,PARM_LIST,FFUNC,RET_TYPE) VALUES ('01011003') 'ROUND','ROUND (n [, INTEGER])', 'return 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) COL11,-- 123 ROUND (123.123456789) COL12,-- 123.1 ROUND (123.123456789) COL13,-- 123.12 ROUND (123.123456789) COL14,-- 123.123 ROUND (123.123456789) COL15,-- 123.1235 ROUND (123.123456789) COL16,-120 ROUND (123.123456789) COL17,-123.123456789 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: return truncated value return value type: NUMBER----INSERT INTO T_FUNCTION (FID,FNAME,PARM_LIST,FFUNC,RET_TYPE) VALUES ('01011004 truncated truncation' 'TRUNC (n [, INTEGER])', 'return 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) COL11,-- 123 TRUNC (123.123456789) COL12,-- 123.1 TRUNC (123.123456789) COL13,-- 123.12 TRUNC (123.123456789) COL14,-- 123.123 TRUNC (123.123456789) COL15,-- 123.124-- 4 truncated TRUNC (123.123456789) COL16,-120 TRUNC (123.123456789) COL17 -- 100 TRUNC (123.12345678) COL18 FROM DUAL