|
这是以前工作中测试用例。准备离职了，顺便贴到这里来
几个存储过程和方法：
SELECT convert_from('aaaa','utf-8')
CREATE OR REPLACE FUNCTION ABS.PRC_ENCODE_UTF8(PARAMS VARCHAR) RETURNS SETOF record
AS $$
declare sql varchar;
r RECORD;
BEGIN
sql:='select * from abs.bcustomer';
FOR r IN (EXEC sql)
LOOP
RETURN NEXT r;
END LOOP;
END
$$
LANGUAGE plpgsql;
---SELECT NOW();
SELECT ABS.PROC_ENCODE_UTF8('测试效果看看如何');
CREATE OR REPLACE FUNCTION ABS.PRC_ADD_HCONTACT(USL_ID INT,CTM_ID INT,START_DT timestamp without time zone,
END_DT timestamp without time zone,CTS_ID INT) RETURNS INT
AS $$
DECLARE HCTID INT;
BEGIN
SELECT NEXTVAL('ABS.HCONTACT_HCT_ID_SEQ') INTO HCTID;
IF END_DT IS NULL THEN
INSERT INTO ABS.HCONTACT(HCT_ID,HCT_USL_ID,HCT_CTM_ID,HCT_START_DT,HCT_END_DT,HCT_CTS_ID) VALUES
(HCTID,USL_ID,CTM_ID,START_DT,NOW(),CTS_ID);
ELSE
INSERT INTO ABS.HCONTACT(HCT_ID,END_DT,CTS_ID);
END IF;
RETURN HCTID;
END
$$
LANGUAGE plpgsql;
SELECT ABS.PRC_ADD_HCONTACT(1,2,'2009-01-01',NULL,1)
CREATE OR REPLACE FUNCTION abs.prc_getorderaddress_byctmid(ctmid integer,flag boolean,username varchar)
RETURNS INT AS
$BODY$
DECLARE
ORAID INT;
CTMNAME VARCHAR;
CTMADDRESS VARCHAR;
CTMZIP VARCHAR;
CTMMOBILE VARCHAR;
CTMCTYID INT;
BEGIN
ORAID:=0;
CTMCTYID:=0;
IF FLAG=FALSE THEN
SELECT CTM_NAME,CTM_COMPANYADDRESS,CTM_COMPANYZIP,CTM_MOBILE,CTM_COM_CTY_ID
INTO CTMNAME,CTMADDRESS,CTMZIP,CTMMOBILE,CTMCTYID FROM ABS.BCUSTOMER WHERE CTM_ID=CTMID LIMIT 1;
ELSE
SELECT CTM_NAME,CTM_ADDRESS,CTM_ZIP,CTM_CTY_ID
INTO CTMNAME,CTMCTYID
FROM ABS.BCUSTOMER WHERE CTM_ID=CTMID LIMIT 1;
END IF;
IF FOUND THEN
SELECT NEXTVAL('ABS.BORDERADDRESS_ORA_ID_SEQ') INTO ORAID;
INSERT INTO ABS.BORDERADDRESS(ORA_ID,ORA_CTM_ID,ORA_NAME,ORA_ADDRESS,ORA_ZIP,ORA_PHONE,ORA_CTY_ID,ORA_CREATION_DT,
ORA_UPDATE_DT,ORA_CREATIONUID,ORA_UPDATEUID) VALUES(ORAID,CTMID,CTMNAME,
CTMZIP,CTMCTYID,username,username);
RETURN ORAID;
END IF;
RETURN 0;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
CREATE OR REPLACE FUNCTION ABS.PRC_LPRDPUR_FREE_ADD(
CTMID INT,
LPKPUR_ID int,
LPKCREATIONUID varchar(50))
RETURNS INT AS
$BODY$
DECLARE
PRDID INT;
LEPQTYINT;
BEGIN
SELECT LEP_PRD_ID,SUM(LEP_QTY) AS LEP_NUM INTO PRDID,LEPQTY FROM ABS.LEVPPRD WHERE EXISTS (SELECT EVP_ID FROM
ABS.BEVENTPRODUCT LEFT JOIN ABS.BCUSTOMER ON EVP_EVT_ID=CTM_EVT_ID WHERE CTM_ID=CTMID AND LEP_EVP_ID=EVP_ID) GROUP BY LEP_PRD_ID;
IF(PRDID>0 AND LEPQTY>0) THEN
INSERT INTO ABS.LPRDPUR(
LPK_PUR_ID,LPK_PRD_ID,LPK_QTY,LPK_AMOUNT,LPK_REFAMOUNT,LPK_CREATION_DT,LPK_UPDATE_DT,LPK_CREATIONUID,
LPK_UPDATEUID,LPK_INVSTATUS,LPK_PURSTATUS,LPK_STATUS
)VALUES(LPKPUR_ID,PRDID,LEPQTY,LPKCREATIONUID,0);
RETURN 1;
ELSE
RETURN 0;
END IF;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
SELECT * FROM ABS.LPRDPUR ORDER BY LPK_UPDATE_DT DESC
select ABS.PROC_LPRDPUR_ADD(1,1,'xto')
--SELECT NEXTVAL('')
--SELECT CURRVAL('')
--------------------------
CREATE OR REPLACE FUNCTION ABS.PRC_GET_ORDERCODE(
SYSTEMCODE VARCHAR,
PTYID INT,
USERID INT)
RETURNS VARCHAR AS
$BODY$
DECLARE
SEQ VARCHAR;
USERCODE VARCHAR;
BEGIN
SELECT USR_CODE INTO USERCODE FROM ABS.BUSER WHERE USR_ID=USERID LIMIT 1;
IF PTYID=9 THEN
SELECT (to_char(now(),'YYMMDD'))||trim(to_char(NEXTVAL('abs.ordercode_pointorder_seq'),'00000')) INTO SEQ;
RETURN CAST(PTYID AS VARCHAR)||USERCODE||SEQ||SYSTEMCODE;
ELSIF PTYID=8 THEN
SELECT (to_char(now(),'YYMMDD'))||trim(to_char(NEXTVAL('abs.ordercode_weborder_seq'),'00000')) INTO SEQ;
RETURN CAST(PTYID AS VARCHAR)||'000'||SEQ||SYSTEMCODE;
ELSIF PTYID=7 THEN
SELECT (to_char(now(),'YYMMDD'))||trim(to_char(NEXTVAL('abs.ordercode_obsaleorder_seq'),'00000')) INTO SEQ;
RETURN CAST(PTYID AS VARCHAR)||USERCODE||SEQ||SYSTEMCODE;
ELSIF PTYID=6 THEN
SELECT (to_char(now(),'YYMMDD'))||trim(to_char(NEXTVAL('abs.ordercode_obfreeorder_seq'),'00000')) INTO SEQ;
RETURN CAST(PTYID AS VARCHAR)||USERCODE||SEQ||SYSTEMCODE;
ELSIF PTYID=5 THEN
SELECT (to_char(now(),'YYMMDD'))||trim(to_char(NEXTVAL('abs.ordercode_iborder_seq'),'00000')) INTO SEQ;
RETURN CAST(PTYID AS VARCHAR)||USERCODE||SEQ||SYSTEMCODE;
ELSIF PTYID=4 THEN
SELECT (to_char(now(),'YYMMDD'))||trim(to_char(NEXTVAL('abs.ordercode_grouporder_seq'),'00000')) INTO SEQ;
RETURN CAST(PTYID AS VARCHAR)||USERCODE||SEQ||SYSTEMCODE;
ELSIF PTYID=3 THEN
SELECT (to_char(now(),'YYMMDD'))||trim(to_char(NEXTVAL('abs.ordercode_counterorder_seq'),'00000')) INTO SEQ;
RETURN CAST(PTYID AS VARCHAR)||USERCODE||SEQ||SYSTEMCODE;
ELSIF PTYID=2 THEN
SELECT (to_char(now(),'YYMMDD'))||trim(to_char(NEXTVAL('abs.ordercode_insideorder_seq'),'00000')) INTO SEQ;
RETURN CAST(PTYID AS VARCHAR)||USERCODE||SEQ||SYSTEMCODE;
ELSE
RETURN NULL;
END IF;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
SELECT CAST(3 AS VARCHAR)||CAST(3 AS VARCHAR)
CREATE OR REPLACE FUNCTION devob.proc_getorderaddress_byctmid(ctm_id integer,flag boolean)
RETURNS SETOF record AS
BODY
DECLARE
result RECORD;
BEGIN
IF FLAG=FALSE THEN
for result in SELECT CTM_ID,CTM_NAME,CTM_COMPANYADDRESS as CTM_ADDRESS,CTM_COMPANYZIP as CTM_ZIP,CTM_TEL,CTM_COM_CTY_ID as CTM_CTY_ID FROM devob.BCUSTOMER
LOOP
RETURN NEXT result;
END LOOP;
ELSE
for result in SELECT CTM_ID,CTM_CTY_ID FROM devob.BCUSTOMER
LOOP
RETURN NEXT result;
END LOOP;
END IF;
END
BODY
LANGUAGE 'plpgsql' VOLATILE
COST 100
ROWS 1000;
ALTER FUNCTION devob.proc_getorderaddress_byctmid(integer,boolean) OWNER TO postgres;
DROP FUNCTION devob.PRC_GETREGCOUNT(avg int,years int,months int);
CREATE OR REPLACE FUNCTION devob.PRC_GETREGCOUNT(avg int,months int)
RETURNS SETOF record AS
$BODY$
DECLARE
SUMNEWDATA INT;
TRSCOUNT INT; --
SUM_NEWDATA INT;
MAX_SID INT;
result RECORD;
begin
SUMNEWDATA=0; --当月打电话次数
TRSCOUNT=0; --查询月在最后一天职人数
SUM_NEWDATA=0;
SELECT TSR_NUM INTO TRSCOUNT FROM devob.V_REG_TSR_NUM WHERE EXTRACT(YEAR FROM DT)=years AND
EXTRACT(MONTH FROM DT)=months ORDER BY DT DESC LIMIT 1;
SELECT SUM(NEWDATA) INTO SUMNEWDATA FROM devob.OB_REG_KPI WHERE EXTRACT(YEAR FROM CAST(DT AS timestamp))=years AND
EXTRACT(MONTH FROM CAST(DT AS timestamp))=months;
SELECT SUM(NEWDATA) INTO SUM_NEWDATA FROM devob.OB_REG_COUNT WHERE EXTRACT(YEAR FROM DT)=years AND
EXTRACT(MONTH FROM DT)=months;
SELECT MAX(SID) INTO MAX_SID FROM devob.OB_REG_COUNT WHERE EXTRACT(YEAR FROM DT)=years AND
EXTRACT(MONTH FROM DT)=months;
IF TRSCOUNT IS NULL OR TRSCOUNT=NULL THEN
TRSCOUNT=0;
END IF;
IF SUMNEWDATA IS NULL OR SUMNEWDATA=NULL THEN
SUMNEWDATA=0;
END IF;
IF SUM_NEWDATA IS NULL OR SUM_NEWDATA=NULL THEN
SUM_NEWDATA=0;
END IF;
IF MAX_SID IS NULL OR MAX_SID=NULL THEN
MAX_SID=0;
END IF;
for result in SELECT sid,file_desc,ct,evt_desc FROM (SELECT sid,FILE_DESC,CAST(NEWDATA AS NUMERIC(18,2)) as ct,EVT_DESC FROM devob.OB_REG_COUNT WHERE EXTRACT(YEAR FROM DT)=years AND
EXTRACT(MONTH FROM DT)=months UNION
SELECT MAX_SID+1,'合计',CAST(COALESCE(SUM(NEWDATA),0) AS NUMERIC(18,2)),'' FROM devob.OB_REG_COUNT WHERE EXTRACT(YEAR FROM DT)=years AND
EXTRACT(MONTH FROM DT)=months UNION
SELECT MAX_SID+2,'尚余',CAST(COALESCE(SUM(NEWDATA)-(COALESCE(SUMNEWDATA,0)),'' FROM devob.OB_REG_COUNT WHERE EXTRACT(YEAR FROM DT)=years AND
EXTRACT(MONTH FROM DT)=months UNION
SELECT MAX_SID+3,'可用天数',CASE WHEN TRSCOUNT=0 THEN
CAST(CAST((SUM_NEWDATA-SUMNEWDATA) AS NUMERIC(18,2))/AVG AS NUMERIC(18,2))
ELSE
CAST(CAST((SUM_NEWDATA-SUMNEWDATA) AS NUMERIC(18,2))/(TRSCOUNT*AVG) AS NUMERIC(18,2))
END,'' FROM devob.OB_REG_COUNT WHERE EXTRACT(YEAR FROM DT)=years AND
EXTRACT(MONTH FROM DT)=months) A
LOOP
RETURN NEXT result;
END LOOP;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE
SELECT a.sid,a.file_desc,a.ct,a.evt_desc from devob.PRC_GETREGCOUNT(250,2009,9) AS a(sid int,file_desc VARCHAR,ct NUMERIC,evt_desc VARCHAR)
select * FROM devob.OB_REG_COUNT
SELECT * FROM devob.OB_REG_COUNT WHERE EXTRACT(YEAR FROM DT)=EXTRACT(YEAR FROM TIMESTAMP '2009-09-03 00:00:00') AND
EXTRACT(MONTH FROM DT)=EXTRACT(MONTH FROM TIMESTAMP '2009-09-03 00:00:00');
SELECT CAST(CAST(19 AS NUMERIC(18,2))/3 AS NUMERIC(18,2))
select * from devob.OB_REG_COUNT
SELECT * FROM devob.OB_REG_KPISELECT '合计' AS FILE_DESC,COALESCE(SUM(NEWDATA),0),'' AS EVT_DESC FROM devob.OB_REG_COUNT （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
