公司企业模板_广州网站开发创意设计公司_网络黄页推广软件_网站友情链接怎么添加

drop FUNCTION if exists query_sal_more_than_sb;
create or replace FUNCTION query_sal_more_than_sb(emp_name text 
)
returns table (e_ename text, e_sal integer)
LANGUAGE plpgsql
AS
$$BEGINreturn query select ename, sal from emp where sal > (select sal from emp where ename = emp_name);
END
$$;

select * from query_sal_more_than_sb('ALLEN')

setof

drop FUNCTION if exists query_sal_more_than_sb;
create or replace FUNCTION query_sal_more_than_sb(emp_name text 
)
returns setof emp
LANGUAGE plpgsql
AS
$$BEGINreturn query select * from emp where sal > (select sal from emp where ename = emp_name);
END
$$;

员工数量

drop FUNCTION if exists query_emp_count;
create or replace FUNCTION query_emp_count()
returns integer
LANGUAGE plpgsql
AS
$$DECLARE
emp_count integer;BEGINselect count(1) into emp_count from emp;return emp_count;
END
$$;

drop FUNCTION if exists get_dept_emp_info;
create or replace FUNCTION get_dept_emp_info(
dno integer
)
returns table(
eno integer,
e_name text,
e_sal integer,
d_name text
)
LANGUAGE plpgsql
AS
$$DECLARE
info record;BEGINfor info in (select empno, ename, sal, dname from empjoin dept on dept.deptno = emp.deptnowhere emp.deptno = dno) LOOPeno := info.empno;e_name := info.ename;e_sal := info.sal;d_name := info.dname;return next;
END LOOP;END
$$;

select * from get_dept_emp_info(10);