Oracle Auto-increment function, general function

1.UNIX time and normal time conversion to each other

1.ORACLE first create a function method, then directly use, MySQL direct use method unix_timestamp,from_unixtime

Oracle_to_unix (Create_date)

Create or Replace functionOracle_to_unix (in_dateinchDatereturn  Number  isbegin  return((in_date-To_date ('19700101','YYYYMMDD'))* 86400 -To_number (substr (Tz_offset (sessiontimezone),1,3))* 3600);EndOracle_to_unix;

Unix_to_oracle (Create_date)

Create or Replace functionUnix_to_oracle (In_number Number)returnDate isbegin  return(To_date ('19700101','YYYYMMDD')+In_number/86400  +To_number (substr (Tz_offset (Sessiontimezone),1,3))/ -);EndUnix_to_oracle;

Exp:

SELECT *   from(SELECTMobile,/*Unix_timestamp (create_date) as Create_date,*/               /*To_timestamp (create_date, ' Yyyy-mm-dd hh24:mi:ss ') as Create_date,*/               /*To_timestamp (create_date) as Create_date,*/Oracle_to_unix (create_date) ascreate_date, code, code_status, UUID/*Top (1)*/           fromR_check_codeWHERECode_status= '0'            andMobile= '138******'        /*and rownum<=1*/         Order  byCreate_datedesc) swhereRowNum<= 1

2. Display Data quantity limit

Mysql:

SELECT * from TBL limit 100;

ORACLE:

SELECT * from TBL where rownum<=100;

SQL SERVER:

Select Top * from TBL

3. Fractional rounding

ORACLE: Rounding function trunc ():

Trunc (12.354), return value 12

Trunc (12.354,1), return value 12.3

Trunc (12.354,-1), return value 10

MYSQL: Numeric processing function floor and round

Floor: The function returns only the integer part, and the fractional part is discarded.

Round: Function rounding, part rounding greater than 0.5, discarding if not. different from floor.

4. Get the current time

Oracle:sysdate parentheses are prone to errors: missing commas

Mysql:sysdate ()

Oracle Auto-increment function, general function