sql中的存储过程

pgsql中的存储过程

 
 
-- 促销主题演示
CREATE OR REPLACE FUNCTION func_prm_prom (
	pbillidorg integer default 0,
	pbillidseq integer default 0
) RETURNS integer 
as 
$body$
 
-- Declare param
DECLARE
	return_status integer;
BEGIN
 
 
insert into prm_prom
(ipromid,spromname,sbegintime,sendtime,icrtuserid,scrtuser,scrttime,stopuserid,stopuser,stoptime,istatus,spromcode,schgtime,sattime,satuser)
select pbillidseq as ipromid,spromname,sbegintime,sendtime,icrtuserid,scrtuser,scrttime,stopuserid,stopuser,stoptime,istatus,spromcode,schgtime,sattime,satuser
from prm_prom
where ipromid = pbillidorg ;
 
 
/*	
_seq := 1;
For _demo_rec In 
	select d.iuserid,d.susercode,d.susername,d.istatus from demo_test d where d.istatus <> '10' 
Loop
 
	RAISE NOTICE '_demo_rec.loop --- %' , '11111111111111111111111111111111111111' ;
	RAISE NOTICE '_demo_rec.begin --- %' , to_char(now(),'yyyy-mm-dd HH24:mi:ss') ;
	RAISE NOTICE '_demo_rec.seq --- %' , _seq ;
	RAISE NOTICE '_demo_rec.iuserid --- %' , _demo_rec.iuserid ;
	RAISE NOTICE '_demo_rec.end --- %' , to_char(now(),'yyyy-mm-dd HH24:mi:ss') ;
	RAISE NOTICE '_demo_rec.loop --- %' , '222222222222222222222222222222222222222' ;
	
	_seq := (_seq + 1);
End Loop;
*/
 
-- return_status
Select 1 as return_status into return_status ;
 
return return_status; -- -1检验失败 0运行失败 1运行成功
 
 
 
 
END;
 
$body$ 
LANGUAGE 'plpgsql' 
VOLATILE 
CALLED ON NULL INPUT 
SECURITY INVOKER 
COST 100;
 
 
 
 
 
-- 演示
drop table if exists demo_test;
create table demo_test (
	id serial 			primary key,
	addtime				timestamp not null default now(),
	iuserid				integer not null,
	susercode			varchar(100) not null,
	susername			varchar(500) not null,
	istatus				integer not null default 0
);
create unique index uk_demo_test_iuserid on demo_test(iuserid);
create unique index uk_demo_test_susercode on demo_test(susercode);
create index idx_demo_test_susername on demo_test(susername);
 
 
comment on table demo_test is '演示';
comment on column demo_test.id is '系统使用:自增id';
comment on column demo_test.addtime is '系统使用:数据添加时间';
comment on column demo_test.iuserid is '会员id';
comment on column demo_test.susercode is '会员卡号';
comment on column demo_test.susername is '会员姓名';
comment on column demo_test.istatus is '状态 0:无效 1:有效';
 
 
 
 
 
 
insert into demo_test (iuserid,susercode,susername,istatus) values('111','u111','张11','1') ;
insert into demo_test (iuserid,susercode,susername,istatus) values('122','u122','张22','0') ;
 
insert into demo_test (id,addtime,iuserid,susercode,susername,istatus) values('123458','now()','333111','u133111','张33','0') ;
 
 
insert into demo_test (iuserid,susercode,susername,istatus)
select 111111,susercode||'v',susername,0 as istatus from demo_test 
where iuserid = 111
;
 
 
select * from demo_test_id_seq ;
select setval('demo_test_id_seq',123456) ;