oracle 函数

1. 实现基于序列表sequence提供自增序列值。

sequence表结构如下：

CREATE TABLE "C##CWFX"."SEQUENCE" 
   (	"SEQ_NAME" NVARCHAR2(50) NOT NULL ENABLE, 
	"CURRENT_VALUE" NUMBER(20,0) NOT NULL ENABLE, 
	"INCREMENT" NUMBER(11,0) NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

SEQ_NAME	CURRENT_VALUE	INCREMENT
customer_code	2598	1

函数：

-- 这是第一个函数,根据传参，从序列表中拿到参数对应的当前序列值。
CREATE OR REPLACE FUNCTION "CUSTOMERCENTER_SEQ_CURRVAL" (SEQUENCE_NAME  VARCHAR)
RETURN NUMBER
IS
CVALUE NUMBER;
BEGIN
	-- routine body goes here, e.g.
	-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
	SELECT CURRENT_VALUE INTO CVALUE FROM SEQUENCE WHERE SEQ_NAME = SEQUENCE_NAME;
	RETURN CVALUE;
END;


-- 这是第二个函数 根据传参，更新序列表中对应的序列值。
CREATE OR REPLACE FUNCTION "CUSTOMERCENTER_SEQ_NEXTVAL" (SEQUENCE_NAME  VARCHAR)
RETURN NUMBER
IS
CVALUE NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
	-- routine body goes here, e.g.
	-- DBMS_OUTPUT.PUT_LINE('Navicat for Oracle');
	UPDATE SEQUENCE 
	SET SEQUENCE.CURRENT_VALUE = SEQUENCE.CURRENT_VALUE + SEQUENCE."INCREMENT"
	WHERE SEQUENCE.SEQ_NAME = SEQUENCE_NAME;
	COMMIT;
	CVALUE := CUSTOMERCENTER_SEQ_CURRVAL(SEQUENCE_NAME);
	RETURN CVALUE;
END;

-- 这是第三个函数，获取名称为customer_code的当前序列值，并用函数拼接为需要的样子。
CREATE OR REPLACE FUNCTION "GET_CUSTOMER_CODE" RETURN VARCHAR
IS
SEQ_VAL NUMBER;
CUSTOMER_CODE VARCHAR(500);
BEGIN
	SELECT CUSTOMERCENTER_SEQ_NEXTVAL('customer_code') INTO SEQ_VAL FROM DUAL;
	SELECT CONCAT('9',LPAD(SEQ_VAL,5,'0')) INTO CUSTOMER_CODE FROM DUAL;
	RETURN CUSTOMER_CODE;
END;

遇到的问题及注意事项：

1. 包含update、deleted的语句，需要在begin之前有“PRAGMA AUTONOMOUS_TRANSACTION;” 语句，作为事务标记，并在语句末尾用“commit;” 语句来表示提交。参照函数二。
2. 定义变量可以使用 IS关键字。但如果函数需要有返回结果，则定义的返回结果必须在定义变量时，写在最后个。参照函数一。
3. 所有的select语句 必须有from关键字。可以使用dual表做工具人。（dual是Oracle提供的一个system表，mysql也有。对dual的操作，一般对业务不会有任何影响。）
4. mysql好多函数的用法与Oracle不同，比如concat。
5. is后定义的VARCHAR变量如果不指定长度，可能会出异常

结果：

select get_customer_code() from dual;

扩展，如果需要多个序列值，可在sequence表中添加数据，然后将函数三中的逻辑按需修改即可。 如

SEQ_NAME	CURRENT_VALUE	INCREMENT
customer_code	2598	1
user_code	1	1
dept_code	1	1

CREATE OR REPLACE FUNCTION "GET_DEPT_CODE" RETURN VARCHAR
IS
SEQ_VAL VARCHAR(500);
BEGIN
	SELECT CUSTOMERCENTER_SEQ_NEXTVAL('dept_code') INTO SEQ_VAL FROM DUAL;
	RETURN SEQ_VAL;
END;