学步园

 例子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;