postgresql procedure 存储过程 > 语雀

已迁移语雀 https://www.yuque.com/puredream/am1ddn/wbv9d5

 

QUOTE_IDENT 函数以双引号字符串的形式返回指定字符串,以便此字符串可用作 SQL 语句中的标识符。

在 sql 语句中，大写，全部会变成小写，如果想保留大写，需要加 双引号

基础表

create table student
(
    id   integer not null primary key ,
    name varchar(255),
    sex  varchar
);

 

 

存储过程（存过）

 

select * from student;

create table teacher as select * from student;

---------------11111111111111111
CREATE extension "uuid-ossp";
SELECT    uuid_generate_v4 ( );

--立即执行存储过程
DO $$
DECLARE
    v_idx INTEGER := 1;
BEGIN
        while
        v_idx < 300000
        loop
        v_idx = v_idx + 1;
    INSERT INTO "public"."student" ( "id", "name" )
    VALUES
        ( uuid_generate_v4 ( ), 'bobo' );

END loop;
END $$;

---------------22222222222222222


create or replace function count_student1()
returns integer as $$

declare
 pgSqlScript text;
 counts integer;

begin
pgSqlScript:='select count("name") from "student"';
execute pgSqlScript into counts;
return counts;
end;

$$ language plpgsql;


select count_student1();



---------------333333333333333
create or replace function count_student2(tableName text, columnName text)
returns text as $$

declare
 pgSqlScript text;
 counts integer;
begin
pgSqlScript:='select count('    || quote_ident(columnName)    || ') from '    || quote_ident(tableName);

return pgSqlScript;

end;

$$ language plpgsql;

select count_student2('student','name');




---------------4444444444444444
drop function count_student3;
create or replace function count_student3(tableName text, columnName text)
returns integer as $$

declare
 pgSqlScript text;
 counts integer;
begin
pgSqlScript:='select count('    || quote_ident(columnName)    || ') from '    || quote_ident(tableName);
execute pgSqlScript into counts;

if counts > 100 then
    return counts;
else
    return 0;
end if;

end;

$$ language plpgsql;

select count_student3('student','name');





---------------555555555555555
create or replace function count_student5()
returns integer as $$

declare
 pgSqlScript text;
 myid integer;
 myname varchar;

begin
pgSqlScript:='select id,name from student order by "id" asc ';
execute pgSqlScript into myid,myname;--　可以同时赋多个值，但只会塞入最后一行数据

raise notice 'result is %' , myname; --打印语句
return myid;
end;

$$ language plpgsql;


select count_student5();


select id,name from student order by id;

delete from teacher;
select * from teacher;
select * from student;
insert into teacher select * from student;

update teacher T1 set name = T2.name from student T2 where T1.id = T2.id;

 

 

 

 游标使用

CREATE OR REPLACE FUNCTION cursor_demo() 
  RETURNS refcursor AS  --返回一个游标
$BODY$ 
declare  --定义变量及游标
    unbound_refcursor refcursor;  --游标
    t_accid varchar;    --变量
        t_accid2 int;    --变量
 
begin  --函数开始
    open unbound_refcursor for execute 'select name from cities_bak';  --打开游标 并注入要搜索的字段的记录
    loop  --开始循环
        fetch unbound_refcursor into t_accid;  --将游标指定的值赋值给变量
           
        if found then  --任意的逻辑
            raise notice '%-',t_accid; 
        else 
            exit; 
        end if; 
    end loop;  --结束循环
    close unbound_refcursor;  --关闭游标
    raise notice 'the end of msg...';  --打印消息
    return unbound_refcursor; --为函数返回一个游标
exception when others then  --抛出异常
    raise exception 'error-----(%)',sqlerrm;--字符“%”是后面要显示的数据的占位符
end;  --结束
$BODY$ 
 
  LANGUAGE plpgsql;  --规定语言

select cursor_demo()； --调用

 

 

 

 

总结一下：

定义变量是在begin前

变量赋值时使用 ：=

select 中赋值使用into

 

1、存储过程（FUNCITON）变量可以直接用  || 拼接。上面没有列出，下面给个栗子：

　　 

create or replace function f_getNewID(myTableName text,myFeildName text) returns integer as $$
declare
    mysql text;
        myID integer;
begin
    mysql:='select max('|| $2 || ' ) from '||$1;
        execute mysql into myID using myFeildName,myTableName; 
    if myID is null or myID=0 then return 1;
    else return myID+1;
      end if;
end;
$$ language plpgsql;

2、存储过程的对象不可以直接用变量，要用 quote_ident(objVar)

3、$1  $2是 FUNCTION 参数的顺序，如1中的 $1 $2交换，USING 后面的不换 结果 ：select max(myTableName) from myFeildname

4、注意：SQL语句中的大写全部会变成小写，要想大写存大，必须要用双引号。

 

 

我的真实样例

 

-- 创建函数 开始
create or replace function add_one_tbl_activity(prize_arg text)
returns text as $$

declare
 dateNoSecondStr text;
 i int;
 key_periodid text;
 prize_name text;
 year1 text;  month1 text; day1 text; hour1 text; minute1 text; second1 text;
 nowtime timestamp;
 intervalMinute varchar;
BEGIN
	SELECT now() INTO nowtime;
	SELECT  to_char(now(),'YYYY-MM-DD hh24:mi:ss') INTO key_periodid;
	SELECT  prize_arg || '-' ||  key_periodid INTO prize_name;
	
 SELECT EXTRACT (YEAR FROM now()) INTO year1;
 SELECT EXTRACT (MONTH  FROM now()) INTO month1;
 SELECT EXTRACT (DAY FROM now()) INTO day1;
 SELECT EXTRACT (HOUR FROM now()) INTO hour1;
 SELECT EXTRACT (MINUTE FROM now()) INTO minute1;
 SELECT EXTRACT (SECOND  FROM now()) INTO second1;


 for i in 0..9 loop 
--	   raise notice 'i match %',(minute1 + i) % 10;

--     RAISE NOTICE  'a is % % % % % %',year1,month1,day1,hour1,minute1,second1;
	  if ( ((cast(minute1 as integer) + i) % 10) = 0 ) then
	   raise notice 'i match %',i;
	  end if;
	   select year1 || '-' || month1 || '-' || day1 || ' ' || hour1 || ':' || minute1 || ':' || '00'   into dateNoSecondStr;
	   raise notice 'dateNoSecondStr is %' , dateNoSecondStr; 
	    select ( cast(i as  varchar) || ' min') into intervalMinute;
	   raise notice 'intervalMinute %',intervalMinute;
	 	select to_timestamp(dateNoSecondStr , 'YYYY-MM-DD hh24:mi:ss')  + i * interval '1 min'   into nowtime;
--		raise notice 'nowtime is %' , to_char(nowtime , 'yyyy-MM-DD hh24:mi:ss');
--	 select to_date(string_agg(year1 || '-' || month1 || '-' || day1 || ' ' ||  hour1 || ':' || minute1 || ':' || '00' ||, 'yyyy-MM-dd HH:mm:ss') into nowtime;
	  exit when   ((cast(minute1 as integer) + i) % 10) = 0 ;
--  	
  END loop;

--RAISE NOTICE  'a is % % % % % %',year1,month1,day1,hour1,minute1,second1;
--
    INSERT INTO tbl_activity  SELECT key_periodid, createdate, createuserid, owneruserid, schemaname, tenantid, updatedate, updateuserid, activity_end_time, activity_flag, activity_start_time, appoint_start_time, custom_flag, exchaneg_time, lottery_time, proportion, threshold, "version", product_image, prize, winners, market_price, nonzs_winners, zs_winners, activity_rule, expire_time, detail_image1, detail_image2, detail_image3, detail_image4, detail_image5 FROM public.cshop_weekly_loot_activity_batch WHERE periodid='1';
    
   		raise notice 'nowtime is %' , to_char(nowtime , 'yyyy-MM-DD hh24:mi:ss');

	UPDATE tbl_activity SET 
	createdate  = now(),
	updatedate  = now(),
	appoint_start_time  = nowtime , -- 预约
	activity_start_time  = nowtime + '10 min',-- 开始
	activity_end_time  = nowtime + '20 min', -- 结束
	lottery_time  = nowtime + '30 min', -- 抽匀
	expire_time  = nowtime + '40 min', --过期
	prize  = prize_name,
	activity_rule = prize_name,
	activity_flag = 0
	WHERE periodid  = key_periodid ;
	
--return prize_name;
return prize_name;
end;

$$ language plpgsql;

 

-- 创建函数 结束
SELECT tbl_activity('哈哈V2'); -- 执行函数

14:30 预约开始
14:40 活动开始
14:50 活动结束
15:00 开奖时间
15:10 过期时间

 

 

 

 

参考

Postgresql 存储过程(plpgsql)两层for循环的操作==>https://www.jb51.net/article/204229.htm

PostgreSQL的存储过程简单入门==>https://www.cnblogs.com/ios9/p/16055797.html