Postgres SQL

创建带游标的存储过程

drop procedure if exists phoneDeal;
delimiter //
create procedure phoneDeal()
begin
    declare  zjid varchar(64);   -- 主键id
    declare  path varchar(200);   -- 路径
    declare  createTime varchar(64);   -- 创建时间
    declare  fileName varchar(100);   -- 文件名
    declare  extention varchar(64);   -- 扩展名
    
    declare  temp varchar(64);   -- 临时变量
    -- 遍历数据结束标志
    declare done int default false;
    -- 游标
    declare cur_account cursor for select id,LICENSE_PATH,CREATE_TIME from debtee;
	-- 导出上传法人证件路径
	-- declare cur_account cursor for select id,COM_FRZJ_PATH,CREATE_TIME from debtee;
    -- 将结束标志绑定到游标
    declare continue handler for not found set done = true;
    
    -- 打开游标
    open  cur_account;     
    -- 遍历
    read_loop: loop
            -- 取值 取多个字段 赋值
            fetch  next from cur_account into zjid,path,createTime;
            if done then
                leave read_loop;
             end if;
 
        -- 做你想做的操作
    end loop;
 
    close cur_account;
    select * from tempDataTable;
end
//
 
call phoneDeal;

使用存储过程拆分字符串

# 函数：func_split_TotalLength
delimiter $$
drop function if exists `func_split_TotalLength` $$
create definer=`root`@`%` function `func_split_TotalLength`
(f_string varchar(1000),f_delimiter varchar(1000)) returns int(11)
begin
# 计算传入字符串的总length
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
end$$
 
 
# 函数：func_split
delimiter $$
drop function if exists `func_split` $$
create definer=`root`@`%` function `func_split`
(f_string varchar(1000),f_delimiter varchar(1000),f_order int) returns varchar(1000) charset utf8
begin
# 拆分传入的字符串，返回拆分后的新字符串
declare result varchar(1000) default '';
set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
return result;
end$$
 
# 存储过程：splitString
delimiter $$
drop procedure if exists `splitString` $$
create procedure `splitString`
(in f_string varchar(1000),in f_delimiter varchar(1000))
begin
# 拆分结果
declare cnt int default 0;
declare i int default 0;
set cnt = func_split_TotalLength(f_string,f_delimiter);
drop table if exists `tmp_split`;
create temporary table `tmp_split` (`status` varchar(1000) not null) default charset=utf8;
while i < cnt
do
set i = i + 1;
insert into tmp_split(`status`) values (func_split(f_string,f_delimiter,i));
end while;
select * from tmp_split;
end$$

统一修改数据库表名小写

SELECT concat('alter table ', TABLE_NAME, ' rename to ', LOWER( TABLE_NAME ), ' ; ') AS '修改脚本'
FROM information_schema.TABLES WHERE TABLE_SCHEMA = '数据库名'

分割字符为列

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3',',',help_topic_id+1),',',-1) AS num 
FROM 
    mysql.help_topic 
WHERE 
    help_topic_id < LENGTH('1,2,3')-LENGTH(REPLACE('1,2,3',',',''))+1

查询子父级

根据父级查询所有子级

delimiter //
CREATE FUNCTION `getChildList`(root_id VARCHAR(200)) 
  RETURNS VARCHAR(1000) 
  BEGIN 
   DECLARE str VARCHAR(1000) ; 
   DECLARE cid VARCHAR(1000) ; 
   DECLARE k INT DEFAULT 0;
   SET str = root_id; 
   SET cid = CAST(root_id AS CHAR);
	 WHILE cid IS NOT NULL DO 
        IF k > 0 THEN
         SET str = CONCAT(str,',',cid);
        END IF;
				
        SELECT GROUP_CONCAT(user_name) INTO cid FROM sys_user WHERE FIND_IN_SET(parent_id,cid)>0;
        SET k = k + 1;
   END WHILE; 
   RETURN str; 
END // 
delimiter ;
 
select getChildList('15200142299')

根据子级查询所有父级

delimiter // 
CREATE 
FUNCTION `getParentList`(root_id BIGINT) 
RETURNS VARCHAR(1000) 
BEGIN
	DECLARE 
	k INT DEFAULT 0;
	DECLARE fid INT DEFAULT 1;
	DECLARE str VARCHAR(1000) DEFAULT '$';
   
　　WHILE rootId > 0 DO
       SET fid=(SELECT pid FROM table_name WHERE root_id=id); 
       IF fid > 0 THEN
         SET str = concat(str,',',fid);  
         SET root_id = fid; 
       ELSE
         SET root_id=fid; 
       END IF; 
   END WHILE;　　
RETURN str; 
END 
//
 
delimiter ;