Oracle小道笔记

Oracle小道学习经验

存储过程

给自定的列名添加注解

传入参数：字段名称和注解

create or replace procedure csp_col_comment (
							p_column_name 	varchar,  --字段名
                            p_Title varchar,          --字段注释
                            p_RetVal Out integer)
  as
  v_sql varchar(1000);
  v_Title varchar(1000);
  v_username varchar(1000);
begin
    v_Title := ''''||p_Title||''''; 
    --获取当前用户名
    select user into v_username from dual;
    for tb in(select  *  from  DBA_COL_COMMENTS a where a.comments is null 
                      and upper(a.owner) = upper(v_username) and a.column_name = upper(P_column_name)
                      and exists(select 1 from user_tab_columns b where a.table_name = b.table_name 
                                        and a.column_name = b.column_name)
             ) loop
      v_sql := 'comment on column  '||tb.table_name||'.'||tb.column_name ||' is '||v_Title;
      dbms_output.put_line (v_sql);
      execute immediate v_sql;
    end loop;
    p_RetVal := 0;
    commit;
end;

身份证号校验

--正则表达式写法
CREATE OR REPLACE FUNCTION cfn_checkidcard(p_idcard IN VARCHAR2) RETURN INT IS
	/*
       身份证校验，正则表达式写法
  */

	v_regstr VARCHAR2(2000);
	v_sum NUMBER;
	v_mod NUMBER;
	v_checkcode CHAR(11) := '10X98765432';
	v_checkbit CHAR(1);
	v_areacode VARCHAR2(2000) := '11,12,13,14,15,21,22,23,31,32,33,34,35,36,37,41,42,43,44,45,46,50,51,52,53,54,61,62,63,64,65,71,81,82,91,';
BEGIN
	CASE LENGTHB(p_idcard)
		WHEN 15 THEN
			-- 15位
			IF INSTRB(v_areacode, SUBSTR(p_idcard, 1, 2) || ',') = 0 THEN
				RETURN 0;
			END IF;

			IF MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 2)) + 1900, 400) = 0 OR
				 (MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 2)) + 1900, 100) <> 0 AND
					MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 2)) + 1900, 4) = 0) THEN
				-- 闰年
				v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}$';
			ELSE
				v_regstr := '^[1-9][0-9]{5}[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}$';
			END IF;

			IF REGEXP_LIKE(p_idcard, v_regstr) THEN
				RETURN 1;
			ELSE
				RETURN 0;
			END IF;
		WHEN 18 THEN
			-- 18位
			IF INSTRB(v_areacode, SUBSTRB(p_idcard, 1, 2) || ',') = 0 THEN
				RETURN 0;
			END IF;

			IF MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 4)), 400) = 0 OR
				 (MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 4)), 100) <> 0 AND
					MOD(TO_NUMBER(SUBSTRB(p_idcard, 7, 4)), 4) = 0) THEN
				-- 闰年
				v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9Xx]$';
			ELSE
				v_regstr := '^[1-9][0-9]{5}(19|20)[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9Xx]$';
			END IF;

			IF REGEXP_LIKE(p_idcard, v_regstr) THEN
				v_sum := (TO_NUMBER(SUBSTRB(p_idcard, 1, 1)) +
								 TO_NUMBER(SUBSTRB(p_idcard, 11, 1))) * 7 +
								 (TO_NUMBER(SUBSTRB(p_idcard, 2, 1)) +
								 TO_NUMBER(SUBSTRB(p_idcard, 12, 1))) * 9 +
								 (TO_NUMBER(SUBSTRB(p_idcard, 3, 1)) +
								 TO_NUMBER(SUBSTRB(p_idcard, 13, 1))) * 10 +
								 (TO_NUMBER(SUBSTRB(p_idcard, 4, 1)) +
								 TO_NUMBER(SUBSTRB(p_idcard, 14, 1))) * 5 +
								 (TO_NUMBER(SUBSTRB(p_idcard, 5, 1)) +
								 TO_NUMBER(SUBSTRB(p_idcard, 15, 1))) * 8 +
								 (TO_NUMBER(SUBSTRB(p_idcard, 6, 1)) +
								 TO_NUMBER(SUBSTRB(p_idcard, 16, 1))) * 4 +
								 (TO_NUMBER(SUBSTRB(p_idcard, 7, 1)) +
								 TO_NUMBER(SUBSTRB(p_idcard, 17, 1))) * 2 +
								 TO_NUMBER(SUBSTRB(p_idcard, 8, 1)) *