.net 中写 psql 匿名函数、过程语言

 

DO            --关键字
$serch$       --$中间随便写， 不能用特殊符号和数字好像$
 DECLARE times integer; --定义变量
 rec history_depart%ROWTYPE; -- 定义行变量
 BEGIN
    times := 0;
    FOR rec in SELECT * FROM history_depart WHERE person_id = 5655 LOOP --几种循环的一种 循环查到的结果 另几种查看 https://www.cnblogs.com/stephen-liu74/archive/2012/06/06/2312759.html
    times:=times+1;
    RAISE NOTICE 'LOOP % %',times,rec.history_depart_id;
  END LOOP;
END
$serch$ LANGUAGE PLPGSQL

　

DO
$serch$
DECLARE rec history_depart%ROWTYPE;
strSQL varchar(1000);
BEGIN
  strSQL := '';
  FOR rec in SELECT * FROM history_depart WHERE person_id = 5655 LOOP
    strSQL :=strSQL || ' union ';
    RAISE NOTICE 'LOOP % %',strSQL,rec.history_depart_id;
    strSQL := strSQL || 'SELECT * FROM attend_record_all AS a LEFT JOIN leave_type AS b ON a.leave_type_id = b.leave_type_id WHERE attend_day >= ''' || to_char(rec.transfer_in,'YYYY-MM-DD HH24:MI:SS') || ''' AND attend_day < ''' || to_char(COALESCE(rec.transfer_out,'9999/12/31 23:59:59'),'YYYY-MM-DD HH24:MI:SS') || ''' AND person_id = 5655 AND a.leave_type_id < 50';
    RAISE NOTICE 'LOOP % %',strSQL,rec.history_depart_id;
  END LOOP;
  strSQL := substring(strSQL from 7); 
  
  RAISE NOTICE '%',strSQL;
  EXECUTE strSQL;
END
$serch$