Oracle

MySQL

字符串拼接

SELECT concat('a','b') from dual 或'a'||'b'

SELECT concat('a','b')

生成唯一序列

create sequence seq_newsId increment by 1 start with 1 maxvalue 999999999

SELECT uuid() from dual

返回最小整数

SELECT ceil(-1.001) from dual

SELECT ceiling(-1.001)

字符串替换

SELECT REPLACE('abcdef', 'bcd', 'ijklmn') from dual

SELECT REPLACE('abcdef', 'bcd', 'ijklmn')

字符串截取

SELECT substr('abcd',2,2)

SELECT substring('abcd',2,2)

子字符串在父字符串中是否存在

SELECT instr('abcdefg', 'ab')

SELECT locate('ab', 'abcdefg')

字符串长度

SELECT length('AAAASDF')

SELECT char_length('abcdef')

替换NULL

SELECT nvl(t.id, 10) from t

SELECT IFNULL(t.id, 10) from t

日期转字符

SELECT to_char(sysdate,'yyyy-mm-dd') from dual

SELECT to_char(sysdate,'hh24-mi-ss') from dual

SELECT date_format(now(),'%Y-%m-%d')

SELECT time_format(now(),'%H-%i-%S')

字符转日期

SELECT to_date('2020-6-20','yyyy-mm-dd')

SELECT STR_TO_DATE('2020-6-20', '%Y-%m-%d')

字符转整数

SELECT TO_NUMBER('123') AS VALUE FROM DUAL

SELECT CAST("123" AS SIGNED INTEGER) as value

获取当前时间

select SYSDATE value from dual

select now() value 或 select sysdate() value

日期加月份

SELECT ADD_MONTHS(sysdate, 2) as value from DUAL

SELECT DATE_ADD(sysdate(), interval 2 month) as value from DUAL

两个日期之间的相差天数

直接用两个日期相减

SELECT DATEDIFF('2020-12-30','2020-12-29') AS DiffDate

Oracle

MySQL

创建存储过程

create or replace procedure p_add_fac (id_fac_cd in) as

DROP PROCEDURE IF EXISTS `P_ADD_FAC`;create procedure P_ADD_FAC(id_fac_cd varchar(100))

创建函数

CREATE OR REPLACE FUNCTION F_ROLE_FACS_GRP(

ii_role_int_key IN SD_ROLE.ROLE_INT_KEY%TYPE

) RETURN VARCHAR2

DROP FUNCTION IF EXISTS `SD_ROLE_F_ROLE_FACS_GRP`;CREATE FUNCTION `SD_ROLE_F_ROLE_FACS_GRP`( ii_role_int_key INTEGER(10) ) RETURNS varchar(1000)

异常处理

EXCEPTION

WHEN OTHERS THEN

ROLLBACK ;

ov_rtn_msg := c_sp_name||'('|| li_debug_pos ||'):'||

TO_CHAR(SQLCODE)||': '||SUBSTR(SQLERRM,1,100);

DECLARE EXIT HANDLER FOR SQLEXCEPTION

BEGIN ROLLBACK ;

set ov_rtn_msg = concat(c_sp_name,'(', li_debug_pos ,'):',

TO_CHAR(SQLCODE),': ',SUBSTR(SQLERRM,1,100));

END;

声明变量位置

声明变量在begin…end体之前

声明变量在begin...end体内，begin之后其他任何内容之前

调用

Procedure_Name(参数)

Call Procedure_Name(参数);

Oracle

MySQL

CREATE

CREATE TABLE s (s_no VARCHAR(5) NOT NULL,

s_name VARCHAR(30) UNIQUE,

s_status VARCHAR(20),

PRIMARY KEY(s_no));

CREATE UNIQUE INDEX test_index ON s (s_no DESC) ;

CREATE TABLE s (s_no VARCHAR(5) NOT NULL,

s_name VARCHAR(30) UNIQUE,

s_status VARCHAR(20),

PRIMARY KEY(s_no));

CREATE UNIQUE INDEX test_index ON s (s_no DESC) ;

ALTER

ALTER TABLE s ADD s_city VARCHAR2(20);

ALTER TABLE s RENAME TO s_table; --修改表名

ALTER TABLE s_table RENAME COLUMN s_city TO s_country; --修改表列名

ALTER TABLE s_table MODIFY s_country NUMBER(20);

ALTER TABLE s_table DROP COLUMN s_country; --删除表列

ALTER TABLE s ADD s_city VARCHAR(20);

ALTER TABLE s RENAME TO s_table; --修改表名

ALTER TABLE s_table change s_city s_country VARCHAR(20); --修改表列名

ALTER TABLE s_table MODIFY s_country decimal(10,1);

ALTER TABLE s_table DROP COLUMN s_country; --删除表列

ALTER TABLE s_table ADD (test1 VARCHAR(10),test2 INTEGER);

DROP

drop table s

drop table s

TRUNCATE

truncate table s

truncate table s

创建视图

CREATE OR REPLACE VIEW S_VIEW AS

SELECT S_NO,S_NAME,S_STATUS

FROM S ;

CREATE OR REPLACE VIEW S_VIEW AS

SELECT S_NO,S_NAME,S_STATUS

FROM S ;