資料庫postgresql函數(預存程序)的三個例子

 例子1:

-- Function: getopngrd(numeric)</p><p>-- DROP FUNCTION getopngrd(numeric);</p><p>CREATE OR REPLACE FUNCTION getopngrd(opnsq numeric)<br /> RETURNS character varying AS<br />$BODY$<br />DECLARE<br /> rec RECORD;<br /> OpnGrd "varchar" := '';<br /> sql "varchar";<br />BEGIN<br /> sql := 'select opn_grd from t_opn_grd_asg where opn_sq = ' || opnSq || ' order by opn_grd asc';<br /> FOR rec IN EXECUTE sql LOOP<br />OpnGrd := OpnGrd || rec.opn_grd || ',';<br /> END LOOP;<br /> IF OpnGrd != '' THEN<br />OpnGrd := substring(OpnGrd, 0, character_length(OpnGrd));<br /> END IF;<br /> RETURN OpnGrd;<br />END;<br />$BODY$<br /> LANGUAGE 'plpgsql' VOLATILE<br /> COST 100;<br />ALTER FUNCTION getopngrd(numeric) OWNER TO postgres;

 

例子2:

-- Function: getfacdpt(numeric, character varying, character varying)</p><p>-- DROP FUNCTION getfacdpt(numeric, character varying, character varying);</p><p>CREATE OR REPLACE FUNCTION getfacdpt(opnsq numeric, categorycd character varying, schoolid character varying)<br /> RETURNS character varying AS<br />$BODY$<br />DECLARE<br /> rec RECORD;<br /> FacDpt "varchar" := '';<br /> sql "varchar";<br />BEGIN</p><p> sql := ' select t1.org_unit_cd,t2.orgunit_nm,t2.orgunit_snm '<br />|| ' from t_opn_orgunit_ast t1 '<br />|| ' inner join t_orgunit t2 on t1.org_unit_cd=t2.org_unit_cd and t1.school_id=t2.school_id '<br />|| ' where t1.opn_sq=' || opnSq<br />|| ' and t1.opn_category_cd=/'' || categoryCd || '/''<br />|| ' and t1.school_id=/'' || schoolId || '/''<br />|| ' order by t1.org_unit_cd asc ';</p><p> FOR rec IN EXECUTE sql LOOP<br />FacDpt := FacDpt || rec.orgunit_nm || ',';<br /> END LOOP;<br /> IF FacDpt != '' THEN<br />FacDpt := substring(FacDpt, 0, character_length(FacDpt));<br /> END IF;<br /> RETURN FacDpt;<br />END;<br />$BODY$<br /> LANGUAGE 'plpgsql' VOLATILE<br /> COST 100;<br />ALTER FUNCTION getfacdpt(numeric, character varying, character varying) OWNER TO postgres;

 

例子3:

-- Function: getopntmasg(numeric)</p><p>-- DROP FUNCTION getopntmasg(numeric);</p><p>CREATE OR REPLACE FUNCTION getopntmasg(opnsq numeric)<br /> RETURNS character varying AS<br />$BODY$<br />DECLARE<br /> rec RECORD;<br /> OpnTmAsg "varchar" := '';<br /> sql "varchar";<br />BEGIN<br /> sql := ' select distinct t1.opn_sq, t1.opn_day_of_wk_dv as opn_day_of_wk_dv, '<br />|| ' t2.day_of_wk_nm as day_of_wk_nm, t1.opn_stt_prd_crd_cd as opn_stt_prd_crd_cd, '<br />|| ' t1.opn_prd_crd_lg as opn_prd_crd_lg, t1.mn_opn_tm_flg as mn_opn_tm_flg, '<br />|| ' t1.prd_crd_ord as prd_crd_ord, t1.prd_ord as prd_ord '<br />|| ' from t_opn_tm_asg t1, t_day_of_wk t2 where t1.opn_sq = ' || opnSq<br />|| ' and t1.opn_day_of_wk_dv = t2.day_of_wk_dv order by t1.opn_day_of_wk_dv asc ';<br /> FOR rec IN EXECUTE sql LOOP<br />IF rec.prd_ord + ceiling(rec.opn_prd_crd_lg / 2) - 1 = rec.prd_ord THEN<br /> OpnTmAsg := OpnTmAsg || substring(rec.day_of_wk_nm, 0, 2) || rec.prd_ord || ',';<br />ELSE<br /> OpnTmAsg := OpnTmAsg || substring(rec.day_of_wk_nm, 0, 2) || rec.prd_ord || '-' || rec.prd_ord + ceiling(rec.opn_prd_crd_lg / 2) - 1 || ',';<br />END IF;</p><p> END LOOP;<br /> IF OpnTmAsg != '' THEN<br />OpnTmAsg := substring(OpnTmAsg, 0, character_length(OpnTmAsg));<br /> END IF;<br /> RETURN OpnTmAsg;<br />END;<br />$BODY$<br /> LANGUAGE 'plpgsql' VOLATILE<br /> COST 100;<br />ALTER FUNCTION getopntmasg(numeric) OWNER TO postgres;