postgresql的一些常规的sql测试用例

概述这是以前工作中测试用例。准备离职了，顺便贴到这里来   几个存储过程和方法：   SELECT convert_from('aaaa','utf-8') CREATE OR REPLACE FUNCTION ABS.PRC_ENCODE_UTF8(PARAMS VARCHAR) RETURNS SETOF record AS $$     declare sql varchar;     r RE

这是以前工作中测试用例。准备离职了，顺便贴到这里来

几个存储过程和方法：

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

总结

以上是内存溢出为你收集整理的postgresql的一些常规的sql测试用例全部内容，希望文章能够帮你解决postgresql的一些常规的sql测试用例所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。