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