快捷搜索: 王者荣耀 脱发

PGSQL、Kingbasees存储过程写法,游标使用等

PGSQL

create or replace function PRIMARY_KEY_MANY(t character varying, columnnames character varying, types character varying)
    RETURNS void AS
$BODY$
DECLARE
  count int4 :=0; --判断是否需要添加主键
  num int4 := 0;
  rec_column varchar;
  cur_column CURSOR(columnNames varchar) FOR SELECT regexp_split_to_table(columnNames,,);
  rec_type varchar;
  cur_type CURSOR(types varchar) FOR SELECT regexp_split_to_table(types,,);
BEGIN
    select count(*) into num from pg_tables where schemaname=ly_ysj and tablename=lower(T);
    IF num > 0 THEN
        OPEN cur_column(columnNames);
        OPEN cur_type(types);
        LOOP
            FETCH cur_column INTO rec_column;-- 获取字段放入rec_column
            FETCH cur_type INTO rec_type;-- 获取字段类型放入rec_type
            EXIT WHEN NOT FOUND;
            select count(*)
            into num
            from information_schema.columns
            where table_schema = ly_ysj
              and table_name = lower(T)
              and column_name = lower(trim(rec_column));
            IF num = 0 THEN
                EXECUTE ALTER TABLE IF EXISTS  || upper(T) || 
                    ADD COLUMN IF NOT EXISTS  || upper(trim(rec_column)) ||   || upper(rec_type) || ;;
                count := count + 1;
            END IF;
        END LOOP;
        CLOSE cur_column;
        CLOSE cur_type;
        IF COUNT > 0 THEN
             EXECUTE ALTER TABLE || T || ADD CONSTRAINT PK_|| upper(trim(T)) || PRIMARY KEY (|| upper(columnNames) ||);;
        END IF;
    END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

具体格式:
create or replace function PRIMARY_KEY_MANY(t character varying, columnnames character varying, types character varying)
    RETURNS void AS
$BODY$
DECLARE 定义属性
Begin
流程
end;
$BODY$
  LANGUAGE plpgsql VOLATILE;

KingBaseSE

create or replace function PRIMARY_KEY_MANY(t varchar(50) , columnnames varchar(100), types varchar(100))
returns void as

DECLARE
  n int :=0;
  count int :=0; --判断是否需要添加主键
  num int := 0;
  rec_column varchar(30);
  rec_type varchar(30);
  cursor cur is select a.c as c,b.t as t from (select regexp_split_to_table(columnnames,,) as c) a,(select regexp_split_to_array(types,,) as t) b;
BEGIN
    select count(*) into num from sys_tables where schemaname=ly_ysj and tablename=trim(T);
    IF num > 0 THEN
        FOR copy in cur loop
            n = n +1;
            rec_column := copy.c;
            rec_type := copy.t[n];
            select count(*)
            into num
            from all_tab_columns
            where table_name = trim(T)
              and column_name = trim(rec_column);
            IF num = 0 THEN
                count := count + 1;
                EXECUTE ALTER TABLE IF EXISTS || upper(T) ||
                    ADD COLUMN IF NOT EXISTS || lower(rec_column) ||  || upper(rec_type) ||;;
            END if;
        END LOOP;
        IF COUNT > 0 THEN
             EXECUTE ALTER TABLE || T || ADD CONSTRAINT PK_|| upper(trim(T)) || PRIMARY KEY (|| upper(columnNames) ||);;
        END IF;
    END IF;
END;
  language plsql;

具体格式:
create or replace function PRIMARY_KEY_MANY(t varchar(50) , columnnames varchar(100), types varchar(100))
returns void as

DECLARE 定义属性
Begin
流程
end;
 language plsql;

游标具体用法:https://www.cnblogs.com/xiaoliu66007/p/7495753.html 和 https://www.cnblogs.com/xiongzaiqiren/p/sql-cursor.html pgsql存储过程具体学习:https://www.cnblogs.com/sunalways/p/13474440.html

经验分享 程序员 微信小程序 职场和发展