如何搜索当前用户下所有表里含某个值的字段,生成流水号
分类:高并发

复制代码 代码如下:
CREATE OR REPLACE FUNCTION fn_no_make(v_prefix     VARCHAR2,
                                      v_table_name VARCHAR2,
                                      v_number_col VARCHAR2)
 /*
  * v_prefix:     编码前缀两位
  * v_table_name:编码所在表名
  * v_number_col:编码所在列名
  */ 
  RETURN VARCHAR2 IS
  v_old_no   VARCHAR2(50); --原编码
  v_new_no   VARCHAR2(50); --新编码
  v_old_num  NUMBER;       --原编码后三位编号
  v_new_num  VARCHAR2(10); --新编码后三位编号
  v_date_no  VARCHAR2(10); --当前日期编号
  v_sql      VARCHAR2(4000);
BEGIN
  v_sql := 'SELECT MAX(' || v_number_col || ') FROM ' || v_table_name;
  EXECUTE IMMEDIATE v_sql INTO v_old_no;

使用postgre的存储过程生成自定义订单号
CREATE OR REPLACE FUNCTION get_order_no(tname VARCHAR,cname VARCHAR,cprefix VARCHAR)

create or replace procedure MY_Pro_SearchKeyWord is
  v_sql VARCHAR2(4000);
  v_tb_column VARCHAR2(4000);
  v_cnt NUMBER(18,0);
  cursor cur is SELECT 'SELECT '''||'"'||t1.table_name||'"."'||t1.Column_Name||'"'||''''||' as col_name, NVL(COUNT(t."'||t1.Column_Name||'"),0) as cnt FROM "'||
         t1.table_name||'" t WHERE t."'||t1.column_name||'" like ''%关键字%''' AS str
    FROM cols t1 left join user_col_comments t2
      on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
    left join user_tab_comments t3
      on t1.Table_name=t3.Table_name
   WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
               WHERE t4.Object_Type='TABLE'
                 AND t4.Temporary='Y'
                 AND t4.Object_Name=t1.Table_Name )
     AND (t1.Data_Type='CHAR' or t1.Data_Type='VARCHAR2' or t1.Data_Type='VARCHAR')
   ORDER BY t1.Table_Name, t1.Column_ID;

  v_sql := 'SELECT TO_CHAR(SYSDATE,''YYYYMMDD'') FROM DUAL';
  EXECUTE IMMEDIATE v_sql INTO v_date_no;

RETURNS VARCHAR
AS
$$
DECLARE
--当前的订单号
now_order_no VARCHAR;
  --存储当前日期
now_day VARCHAR;
--新的订单号
new_order_no VARCHAR;
--旧的时间
old_date VARCHAR;
--旧的订单编号
old_order_no BIGINT;
--新的订单编号
order_no VARCHAR;
BEGIN
  EXECUTE 'SELECT MAX ('||cname||') FROM '||tname||' WHERE booking_time > CURRENT_DATE' INTO now_order_no;
   --通过截取 获得订单号中的日期
  old_date = substr(now_order_no,1,8);
  --通过截取获得旧的订单编号
  old_order_no = substr(now_order_no,10,5);
   --当前日期
  now_day = to_char(CURRENT_DATE,'yyyyMMdd');
  --如果没有获得大于当前日期的订单 则从新插入
  IF now_order_no is NULL THEN
  new_order_no = cprefix || now_day || '00001';
  RETURN new_order_no;
   --如果 查询的时间 等于当前时间
  ELSEIF old_date != now_day THEN
  new_order_no = cprefix || now_day || '00001';
RETURN new_order_no;
  ELSE
  order_no= cast(old_order_no+1 as varchar);
  order_no = LPAD(order_no,5,'0');
  new_order_no = cprefix || now_day || order_no;
RETURN new_order_no;
  END IF;

BEGIN
  FOR i IN cur LOOP
    v_sql := i.str; -- 获取将要执行的SQL语句;
    EXECUTE IMMEDIATE v_sql INTO v_tb_COLUMN, v_cnt;
    IF v_cnt > 0 THEN
      dbms_output.put_line('表:'||substr(v_tb_column,1,instr(v_tb_column,'.',1,1)-1)||' 列:'||substr(v_tb_column,instr(v_tb_column,'.',1,1)+1)||
                           '有 '||to_char(v_cnt)|| '条记录含有字串"关键字" ');
    END IF;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
BEGIN
  dbms_output.put_line(v_sql);
  dbms_output.put_line(v_tb_column);
END;
end MY_Pro_SearchKeyWord;

  v_old_num := to_number(substr(v_old_no, 12, 3));
  v_new_num := lpad(to_char(v_old_num+1), 3, '0');

END;
$$
LANGUAGE 'plpgsql' VOLATILE;

 

  IF v_old_no IS NULL OR substr(v_old_no, 3, 8) <> v_date_no THEN
    v_new_no := v_prefix || v_date_no || '-' || '001';
  ELSE
    v_new_no := v_prefix || v_date_no || '-' || v_new_num;
  END IF;

  RETURN v_new_no;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END fn_no_make;

本文由10bet手机官网发布于高并发,转载请注明出处:如何搜索当前用户下所有表里含某个值的字段,生成流水号

上一篇:oracle中decode函数的使用方法10bet体育中文官网 下一篇:没有了
猜你喜欢
热门排行
精彩图文