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 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,HCT_USL_ID,HCT_CTM_ID,HCT_START_DT,HCT_END_DT,HCT_CTS_ID) VALUES
  (HCTID,USL_ID,CTM_ID,START_DT,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_MOBILE,CTM_CTY_ID
  INTO CTMNAME,CTMADDRESS,CTMZIP,CTMMOBILE,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,CTMADDRESS,
  CTMZIP,CTMMOBILE,CTMCTYID,NOW(),NOW(),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;
 LEPQTY INT;
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,0,0,NOW(),NOW(),LPKCREATIONUID,LPKCREATIONUID,0,0,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_MOBILE,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_NAME,CTM_ADDRESS,CTM_ZIP,CTM_TEL,CTM_MOBILE,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,years 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)),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+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_KPI
SELECT '合计' AS FILE_DESC,COALESCE(SUM(NEWDATA),0),'' AS EVT_DESC FROM devob.OB_REG_COUNT