Shulou(Shulou.com)06/01 Report--

1. Type comparison

Oraclemysql

Int

Int

Number (6Pol 2)

Decimal (6Pol 2)

Date (sysdate)

Timestamp (now ())

Varchar2 (20)

Varchar (20)

two。 Common function

/ * DATE_FORMAT (date,format) formats the date value * / select date_format according to the format string ('2013-09-21 14 format 01select date_format (' 2013-09-21 14 format 01format) from dual 2 when 1 then 1 when 2 else 0 end from dual;select IFNULL (null,0) from dual

3. Pagination

Start with the 10th record and retrieve 20 records.

Select * from sys_option limit 10, 10, 20, select, from table [query conditions] order by id limit?

The following statement is sorted before paging (good)

SELECT * FROM test_yutong ORDER BY ID DESC LIMIT 0Pol 1

This is the only way to implement order (not as convenient as mysql)

SELECT * FROM (SELECT * FROM TABLE_A ORDER BY ID DESC) B WHERE ROWNUM=1

4. Custom sequence function (source http://www.oschina.net/code/snippet_110375_12231)

/ * CREATE TABLE Sequence * / DROP TABLE IF EXISTS Sequence;CREATE TABLE Sequence (name VARCHAR (32), maxIndex INTEGER, CONSTRAINT cst_Sequence_pk PRIMARY KEY (name, maxIndex)); / * FUNCTION nextval * / SET GLOBAL log_bin_trust_function_creators = 1 FUNCTION IF EXISTS nextval;DELIMITER / / CREATE FUNCTION nextval (sequenceName VARCHAR (32)) RETURNS INTEGER NOT DETERMINISTIC CONTAINS SQLBEGIN DECLARE iMaxIndex INTEGER; UPDATE Sequence SET maxIndex = maxIndex + 1 WHERE name = sequenceName; SELECT maxIndex INTO iMaxIndex FROM Sequence WHERE name = sequenceName; RETURN iMaxIndex;END//DELIMITER / * Test example * / INSERT INTO Sequence (NAME,maxIndex) VALUES ('CK10_GHDJ',1); SELECT nextval (' CK10_GHDJ') FROM DUAL;SELECT * FROM Sequence

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.