记录数据库中常用的存储过程公用方法
最近一直在做项目的数据库（postgresql）加压，在写存储过程的时候经常需要用到一些公用方法，在此做一下记录：
1、产生指定长度的中文字符
1 CREATE OR REPLACE FUNCTION getChnChar(startLength int, endLength int) RETURNS VARCHAR (3000) AS $BODY$ 2 DECLARE 3 chars_str VARCHAR(3000) DEFAULT '赵钱孙李周吴郑王冯陈楮卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董梁杜阮蓝闽席季麻强贾路娄危江童颜郭梅盛林***锺徐丘骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经房裘缪干解应宗丁宣贲邓郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄麹家封芮羿储靳汲邴糜松井段富巫乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘斜厉戎祖武符刘景詹束龙叶幸司韶郜黎蓟薄印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴郁胥能苍双闻莘党翟谭贡劳逄姬申扶堵冉宰郦雍郤璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容向古易慎戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东欧殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚那简饶空曾毋沙乜养鞠须丰巢关蒯相查后荆红游竺权逑盖益桓公万俟司马上官欧阳夏侯诸葛闻人东方赫连皇甫尉迟公羊澹台公冶宗政濮阳淳于单于太叔申屠公孙仲孙轩辕令狐锺离宇文长孙慕容鲜于闾丘司徒司空丌官司寇仉督子车颛孙端木巫马公西漆雕乐正壤驷公良拓拔夹谷宰父谷梁晋楚阎法汝鄢涂钦段干百里东郭南门呼延归海羊舌微生岳帅缑亢况后有琴梁丘左丘东门西门商牟佘佴伯赏南宫墨哈谯笪年爱阳佟第五言福霠霡霢霣霤霥霦霧霨霩霪霫霬霭霮霯一丁丂七丄丅丆万丈三上下丌不与丏丐丑丒专且丕世丗丘丙业丛东丝丞丟丠両丢丣两严並丧乙丁厂卜人入几儿了力乃刀又于干亏士工土才寸下大丈与万上小口巾山千乞川亿个勺久凡及夕丸么广亡门义之尸弓己已子卫也女飞刃习叉马乡丰王井开夫天无元专云扎艺木支厅不太犬区历尤友匹车巨牙屯比互切瓦止少日中冈贝内水见午牛手毛气升长仁什片仆化仇币仍仅斤爪反介父从今凶分乏公仓月氏勿欠风丹匀乌凤勾文方火为斗忆订计户认心尺引丑巴孔队办以允予劝双书幻玉刊示末未击打巧正扑扒功扔去甘世古节本术可丙左厉右石布龙平灭轧东卡北占业旧帅归且旦目叶甲申叮电号田由史只央兄叼叫另叨叹生失禾丘付仗代仙们仪白仔他斥瓜乎丛令用甩印乐句匆册犯外处冬鸟务包饥主市立闪兰半汁汇头汉宁穴它讨写让礼训必议讯记永司尼民出辽奶奴加召皮边发孕圣对台矛纠母幼丝式刑动扛寺吉扣考托老执巩圾扩扫地扬场耳共芒亚芝朽朴机权过臣再协西压厌在有百存而页匠夸夺灰达列死成夹轨邪划迈毕至此贞师尘尖劣光当早吐吓虫曲团同吊吃因吸吗屿帆岁回岂刚则肉网年朱先丢舌竹迁乔伟传乒乓休伍伏优伐延件任伤价份华仰仿伙伪自血向似后行舟全会杀合兆企众爷伞创肌朵杂危旬旨负各名多争色壮冲冰庄庆亦刘齐交次衣产决充妄闭问闯羊并关米'; 4 return_str VARCHAR(3000) DEFAULT ''; 5 i int; 6 end1 int; 7 BEGIN 8 end1 :=cast((random()*(endLength - startLength)) as int)+startLength; 9 FOR i in 1 .. end1 loop 10 --CONCAT(char1,char2,char3,…)顺序联结多个字符串成为一个字符串 11 return_str = concat(return_str,substring(chars_str , cast((random()*(length(chars_str) -1) +1) as INT),1)); 12 end loop; 13 RETURN return_str; 14 END; 15 $BODY$ 16 LANGUAGE 'plpgsql' ;
2、产生指定长度的英文字符
CREATE OR REPLACE function getEnChar (startLength int, endLength int) RETURNS VARCHAR (3000) AS $BODY$ DECLARE chars_str VARCHAR(3000) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'; return_str VARCHAR(3000) DEFAULT ''; i int; end1 int; BEGIN end1 :=cast((random()*(endLength - startLength)) as int)+startLength; FOR i in 1 .. end1 loop return_str = concat(return_str,substring(chars_str , cast((random()*(length(chars_str) -1) +1) as INT),1)); end loop; RETURN return_str; END; $BODY$ LANGUAGE 'plpgsql' ;
3、产生指定长度的混合字符
CREATE OR REPLACE function getMixChar (startLength int, endLength int) RETURNS VARCHAR (3000) AS $BODY$ DECLARE chars_str VARCHAR(3000) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'; return_str VARCHAR(3000) DEFAULT ''; i int; end1 int; BEGIN end1 :=cast((random()*(endLength - startLength)) as int)+startLength; FOR i in 1 .. end1 loop return_str = concat(return_str,substring(chars_str , cast((random()*(length(chars_str) -1) +1) as INT),1)); end loop; RETURN return_str; END; $BODY$ LANGUAGE 'plpgsql' ;
4、随意生成固定区间的整数
CREATE OR REPLACE FUNCTION get_rand_int (m INT ,n INT) RETURNS INT AS $BODY$ BEGIN RETURN CAST((random() *(n - m) + m) as int); END ; $BODY$ LANGUAGE plpgsql;
5、产生身份证号码
CREATE or REPLACE FUNCTION get_IdCard(startLength int,endLength int) RETURNs VARCHAR(300) as $BODY$ DECLARE chars_str VARCHAR(100) DEFAULT '0123456789'; return_str VARCHAR(300) DEFAULT substring('123456789' , cast((1 + random()*8 ) as INT),1); i int ; end1 int; BEGIN end1 :=cast((random()*(endLength - startLength)) as int)+startLength; FOR i in 1 .. end1-1 loop return_str = concat(return_str,substring(chars_str , cast((1 + random()*9 ) as INT),1)); end loop; RETURN return_str; END; $BODY$ LANGUAGE 'plpgsql' ;
6、产生11位电话号码
CREATE or REPLACE FUNCTION get_Tel(startLength int,endLength int) RETURNs VARCHAR(300) as $BODY$ DECLARE first_no VARCHAR(100) DEFAULT '1'; chars_str VARCHAR(100) DEFAULT '0123456789'; return_str VARCHAR(300) DEFAULT substring('3578' , cast((1 + random()*3 ) as INT),1); i int ; end1 int; BEGIN end1 :=cast((random()*(endLength - startLength)) as int)+startLength; FOR i in 1 .. end1-2 loop return_str = concat(return_str,substring(chars_str , cast((1 + random()*9 ) as INT),1)); end loop; RETURN concat(first_no,return_str); END; $BODY$ LANGUAGE 'plpgsql' ;
7、生成指定日期内的随机日期
CREATE OR REPLACE FUNCTION get_rand_date (start_date DATE,end_date DATE) RETURNS DATE AS $BODY$ DECLARE interval_days INTEGER ; random_days INTEGER ; random_date DATE ; BEGIN interval_days := end_date - start_date ; random_days := get_rand_int (0, interval_days) ; random_date := start_date + random_days ; RETURN random_date; END ; $BODY$ LANGUAGE plpgsql;
8、生成指定时间内的随机时间
CREATE OR REPLACE FUNCTION get_rand_datetime(start_date date, end_date date) RETURNS TIMESTAMP AS $BODY$ DECLARE interval_days integer; random_seconds integer; random_dates integer; random_date date; random_time time; BEGIN interval_days := end_date - start_date; random_dates:= trunc(random()*interval_days); random_date := start_date + random_dates; random_seconds:= trunc(random()*3600*24); random_time:=' 00:00:00'::time+(random_seconds || ' second')::INTERVAL; RETURN random_date +random_time; END; $BODY$ LANGUAGE plpgsql;
9、分割字符串，返回其中的随机字符串（用逗号连接）
CREATE OR REPLACE FUNCTION get_split_str (str VARCHAR) RETURNS VARCHAR AS $BODY$ DECLARE array_str VARCHAR[]; i int; rand_index int; BEGIN str :=trim(str); array_str :=regexp_split_to_array(str,'[，,]');--正在表达式匹配 -- array_length(array_str,1)1代表维度，一维、二维 rand_index :=cast((random()*(array_length(array_str,1) -1) +1 )as int); RETURN array_str[rand_index] ; END ; $BODY$ LANGUAGE plpgsql;
10、返回指定长度的UUID
CREATE or REPLACE FUNCTION get_UUID(startLength int,endLength int ,isContain_ BOOLEAN ) RETURNS varchar AS $BODY$ DECLARE v_seed_value varchar(250); end1 int; BEGIN end1 :=cast((random()*(endLength - startLength)) as int)+startLength; v_seed_value:=md5( inet_client_addr()::varchar || timeofday() || inet_server_addr()::varchar || to_hex(inet_client_port())); v_seed_value:= (substr(v_seed_value,1,8) || '-' || substr(v_seed_value,9,4) || '-' || substr(v_seed_value,13,4) || '-' || substr(v_seed_value,17,4) || '-' || substr(v_seed_value,21,12)); if(not isContain_) then v_seed_value:=replace(v_seed_value,'-', ''); end if; RETURN substr(v_seed_value, 1 ,end1); END; $BODY$ LANGUAGE plpgsql ;
