oracle自定义函数

1.自动生成以p_website开头的18位序列

   			create or replace function fun_get_glide_num(p_table   varchar2,
																									 p_website varchar2)
			/**
				*  p_table varchar2 表名
				*  p_website varchar2 自定义开头字符
				*/
			 return varchar2 as
				v_seq       varchar2(50);
				v_seq_value number;
				v_sql       varchar2(200);
				v_num       varchar2(20);
			begin
				v_seq := 'seq_' || p_table;
				v_sql := 'select ' || v_seq || '.nextval from dual';
				execute immediate v_sql
					into v_seq_value;
				select lpad(v_seq_value, 16, '0') into v_num from dual;
				v_num := p_website || v_num;
				return v_num;
			end;

  

2. 更新表序列值设置为其最大值到最大值

  	create or replace procedure pro_modify_seq_table(p_table_name varchar2,
																									 key_id       varchar2) is
		v_id  number;
		v_max number;
		v_seq number;
		v_sql varchar2(1000);
		/**
		* p_table_name表的名称 >
		*
		*  key_id 表的主键ID名称
		**/
	begin
		v_id  := 0;
		v_max := 0;
		v_seq := 0;
		begin
			v_sql := 'select max(' || key_id || ') from ' || p_table_name;
			execute immediate v_sql
				into v_id;
			v_max := to_number(substr(v_id, -5, 5));
			loop
				v_sql := 'select seq_' || p_table_name || '.nextval from dual';
				execute immediate v_sql
					into v_seq;
				exit when v_seq > v_max or v_seq = v_max;
			end loop;
	 end;
    end;

3.生成MD5加密码字符方法

	create or replace function FUN_MD5(input_string VARCHAR2)  return varchar2 IS
		raw_input     RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
		decrypted_raw RAW(2048);
		--MD5加密
		error_in_input_buffer_length EXCEPTION;
	BEGIN
		sys.dbms_obfuscation_toolkit.MD5(input    =>    raw_input,
																		 checksum => decrypted_raw);
		return lower(rawtohex(decrypted_raw));
	END;