Oracle用sql存储过程将姓名转成首字母并存在表字段中

例如 “李明” 转成“lm”  “易烊千玺”转成“yyqx”   “DD22”转成“dd22”

转成的字母为小写字母，大写自己改一下就阔以

此处的for loop循环是循环名字的每一个字，判断“易”转成“y”，判断大写字母转小写，判断数字直接拼接

函数入参是要转换的名字，返回转换好的值

--定义函数
create FUNCTION GET_PYJM (P_NAME IN VARCHAR2)
      RETURN VARCHAR2
AS
  V_COMPARE   VARCHAR2 (100);
  V_RETURN    VARCHAR2 (4000);
BEGIN
  DECLARE
	  FUNCTION F_NLSSORT (P_WORD IN VARCHAR2)
		  RETURN VARCHAR2
	  AS
	  BEGIN
		  RETURN NLSSORT (P_WORD, 'NLS_SORT=SCHINESE_PINYIN_M');
	  END;
  BEGIN
	  FOR I IN 1 .. LENGTH (P_NAME)
	  LOOP
		  V_COMPARE := F_NLSSORT (SUBSTR (P_NAME, I, 1));

		  IF     V_COMPARE >= F_NLSSORT ('吖')
			 AND V_COMPARE <= F_NLSSORT ('驁')
		  THEN
			  V_RETURN := V_RETURN || 'a';
		  ELSIF     V_COMPARE >= F_NLSSORT ('八')
				AND V_COMPARE <= F_NLSSORT ('簿')
		  THEN
			  V_RETURN := V_RETURN || 'b';
		  ELSIF     V_COMPARE >= F_NLSSORT ('嚓')
				AND V_COMPARE <= F_NLSSORT ('錯')
		  THEN
			  V_RETURN := V_RETURN || 'c';
		  ELSIF     V_COMPARE >= F_NLSSORT ('咑')
				AND V_COMPARE <= F_NLSSORT ('鵽')
		  THEN
			  V_RETURN := V_RETURN || 'd';
		  ELSIF     V_COMPARE >= F_NLSSORT ('妸')
				AND V_COMPARE <= F_NLSSORT ('樲')
		  THEN
			  V_RETURN := V_RETURN || 'e';
		  ELSIF     V_COMPARE >= F_NLSSORT ('发')
				AND V_COMPARE <= F_NLSSORT ('猤')
		  THEN
			  V_RETURN := V_RETURN || 'f';
		  ELSIF     V_COMPARE >= F_NLSSORT ('旮')
				AND V_COMPARE <= F_NLSSORT ('腂')
		  THEN
			  V_RETURN := V_RETURN || 'g';
		  ELSIF     V_COMPARE >= F_NLSSORT ('妎')
				AND V_COMPARE <= F_NLSSORT ('夻')
		  THEN
			  V_RETURN := V_RETURN || 'h';
		  ELSIF     V_COMPARE >= F_NLSSORT ('丌')
				AND V_COMPARE <= F_NLSSORT ('攈')
		  THEN
			  V_RETURN := V_RETURN || 'j';
		  ELSIF     V_COMPARE >= F_NLSSORT ('咔')
				AND V_COMPARE <= F_NLSSORT ('穒')
		  THEN
			  V_RETURN := V_RETURN || 'k';
		  ELSIF     V_COMPARE >= F_NLSSORT ('垃')
				AND V_COMPARE <= F_NLSSORT ('擽')
		  THEN
			  V_RETURN := V_RETURN || 'l';
		  ELSIF     V_COMPARE >= F_NLSSORT ('嘸')
				AND V_COMPARE <= F_NLSSORT ('椧')
		  THEN
			  V_RETURN := V_RETURN || 'm';
		  ELSIF     V_COMPARE >= F_NLSSORT ('拏')
				AND V_COMPARE <= F_NLSSORT ('瘧')
		  THEN
			  V_RETURN := V_RETURN || 'n';
		  ELSIF     V_COMPARE >= F_NLSSORT ('筽')
				AND V_COMPARE <= F_NLSSORT ('漚')
		  THEN
			  V_RETURN := V_RETURN || 'o';
		  ELSIF     V_COMPARE >= F_NLSSORT ('妑')
				AND V_COMPARE <= F_NLSSORT ('曝')
		  THEN
			  V_RETURN := V_RETURN || 'p';
		  ELSIF     V_COMPARE >= F_NLSSORT ('七')
				AND V_COMPARE <= F_NLSSORT ('裠')
		  THEN
			  V_RETURN := V_RETURN || 'q';
		  ELSIF     V_COMPARE >= F_NLSSORT ('亽')
				AND V_COMPARE <= F_NLSSORT ('鶸')
		  THEN
			  V_RETURN := V_RETURN || 'r';
		  ELSIF     V_COMPARE >= F_NLSSORT ('仨')
				AND V_COMPARE <= F_NLSSORT ('蜶')
		  THEN
			  V_RETURN := V_RETURN || 's';
		  ELSIF     V_COMPARE >= F_NLSSORT ('侤')
				AND V_COMPARE <= F_NLSSORT ('籜')
		  THEN
			  V_RETURN := V_RETURN || 't';
		  ELSIF     V_COMPARE >= F_NLSSORT ('屲')
				AND V_COMPARE <= F_NLSSORT ('鶩')
		  THEN
			  V_RETURN := V_RETURN || 'w';
		  ELSIF     V_COMPARE >= F_NLSSORT ('夕')
				AND V_COMPARE <= F_NLSSORT ('鑂')
		  THEN
			  V_RETURN := V_RETURN || 'x';
		  ELSIF     V_COMPARE >= F_NLSSORT ('丫')
				AND V_COMPARE <= F_NLSSORT ('韻')
		  THEN
			  V_RETURN := V_RETURN || 'y';
		  ELSIF     V_COMPARE >= F_NLSSORT ('帀')
				AND V_COMPARE <= F_NLSSORT ('咗')
		  THEN
			  V_RETURN := V_RETURN || 'z';
      else
        V_RETURN := V_RETURN || lower(SUBSTR (P_NAME, I, 1));
		  END IF;
	  END LOOP;

	  RETURN V_RETURN;
  END;
END;

定义存储 过程，for循环是循环查询表的每一行，调用上面定义的函数

--定义存储过程
create procedure changeChineseCharacters2PinYin
as
v_record receiver_info%rowtype;
name_1 receiver_info.ename%type;
begin
  for v_record in (select * from 表名 t) loop   
    --dbms_output.put_line(v_record.receiver_name); --输出并换行 
    select GET_PYJM(v_record.receiver_name) into name_1 from dual;
    update 表名 set 转换后字段名 = name_1 where id = v_record.id;
    commit;   
  end loop;
end changeChineseCharacters2PinYin;

最后进行存储过程调用和删除

--调用存储过程
begin
  changeChineseCharacters2PinYin;
end;

--删除函数
drop FUNCTION GET_PYJM;
--删除存储过程
drop procedure changeChineseCharacters2PinYin;