Oracle中的方法

方法

方法中必须有一个返回值，暂时还不会编写聚合方法

select fn_simpleTest('0507') from dual;
CREATE OR REPLACE FUNCTION 
fn_simpleTest(riskcode IN VARCHAR2 --险种) RETURN NUMBER IS
  returnNum NUMBER; --returnNum
BEGIN
  IF riskcode = '0507' THEN
    returnNum := 10507;
  ELSIF riskcode = '0522' THEN
    returnNum := 10522;
  ELSE
    returnNum := 1;
  END IF;
  RETURN returnNum;
END fn_simpleTest;
--字符串拼接函数
CREATE OR REPLACE FUNCTION fn_simpleTest2(varStrTable in varchar2)
  return varchar2 is
  testStr varchar2(4000);
begin
  for tempCur in (select distinct(riskcode) from varStrTable) loop
    testStr := testStr || tempCur.riskcode;
  end loop;
  return testStr;
end;
--计算位数
select fn_simpleTest4('1111111') from dual;
CREATE OR REPLACE FUNCTION fn_simpleTest4 (
  strIn varchar2
) RETURN varchar2
IS
    v_regstr      VARCHAR2 (2000);
    v_str  CONSTANT VARCHAR2 (20)  := '五位数';
    v_str2  CONSTANT VARCHAR2 (20)  := '六位数';
begin
    v_regstr :='不是五和六位数';
      CASE LENGTHB (strIn)
       WHEN 5
       THEN
          return v_str;
       WHEN 6
       THEN
          return v_str2;
       ELSE
          RETURN 0;
    END CASE;
  END fn_simpleTest4;
--在方法中声明游标使用
select fn_simpleTest5('%') from dual
CREATE OR REPLACE FUNCTION fn_simpleTest5(inColumn  in varchar2)
  RETURN VARCHAR2
   IS
  str varchar2(2000);
  CURSOR cursortest(testColumn VARCHAR2) IS
    SELECT DISTINCT (t.riskcode) riskcode FROM prptaddress t  where  t.riskcode like testColumn;
BEGIN
  FOR cursortest_row IN cursortest(inColumn) LOOP
    str := str || cursortest_row.riskcode;
  END LOOP;
  RETURN str;
END fn_simpleTest5;