西宁解封最新通知/杭州关键词优化外包

壹：取汉语拼音首字母的函数

在做工程项目建模的时候,我们时常用字段的汉语拼音首字母来定义列名;如果手工取下来将是一件非常烦人的工作;此函数就用来取汉字的汉语拼音首字母,解决我们头疼的问题
代码如下：

CREATE OR REPLACE FUNCTION F_GET_FRIST_PINY(P_NAME IN VARCHAR2) RETURN VARCHAR2 AS
V_COMPARE VARCHAR2(100);
V_RETURN VARCHAR2(4000);
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..NVL(LENGTH(P_NAME), 0) 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';
ELSIF V_COMPARE = F_NLSSORT('1') THEN
V_RETURN := V_RETURN || 'Y';
ELSIF V_COMPARE = F_NLSSORT('2') THEN
V_RETURN := V_RETURN || 'R';
ELSIF V_COMPARE = F_NLSSORT('3') THEN
V_RETURN := V_RETURN || 'S';
ELSIF V_COMPARE = F_NLSSORT('4') THEN
V_RETURN := V_RETURN || 'S';
ELSIF V_COMPARE = F_NLSSORT('5') THEN
V_RETURN := V_RETURN || 'W';
ELSIF V_COMPARE = F_NLSSORT('6') THEN
V_RETURN := V_RETURN || 'L';
ELSIF V_COMPARE = F_NLSSORT('7') THEN
V_RETURN := V_RETURN || 'Q';
ELSIF V_COMPARE = F_NLSSORT('8') THEN
V_RETURN := V_RETURN || 'B';
ELSIF V_COMPARE = F_NLSSORT('9') THEN
V_RETURN := V_RETURN || 'J';
ELSIF V_COMPARE = F_NLSSORT('0') THEN
V_RETURN := V_RETURN || 'L';
END IF;
END LOOP;
RETURN V_RETURN;
END;

使用举例：

select f_get_frist_piny('我是1个中国人') from dual;
select f_get_frist_piny(ROWNAME) from aix_report_data;

贰:比较字符串是否完全相等的一个函数

在工程项目中，可能会对两个结果进行比对；则用下边一个函数，则可比较出两字符串是否相同。
 

CREATE OR REPLACE FUNCTION ISEQUAL(VAR1 IN VARCHAR2, VAR2 IN VARCHAR2)
RETURN varchar2
IS
BEGIN
IF ((VAR1 IS NULL AND VAR2 IS NULL) OR(VAR1 IS NOT NULL AND VAR2 IS NOT NULL) AND (VAR1 = VAR2)) THENRETURN '相等';
ELSERETURN '不等';
END IF;
EXCEPTION
WHEN OTHERS THENRETURN '出现错误';
END;
/

使用举例：

select ISEQUAL('我是1个中国人','我是一个中国人') from dual;
select a.ppl_amt - b.ppl_amt,a.base_rate - b.base_rate,a.act_rate - b.act_rate,a.cntr_amt - b.cntr_amt,ISEQUAL(a.TRCH_STS, b.TRCH_STS),ISEQUAL(a.PAYUP_DT, b.PAYUP_DT)
from sub.dm3_a_tranche1 a
inner join duan_tranche1_20100119 bon a.trch_no = b.trch_no;