1.mysql的存储过程是:
create PROCEDURE proc_getPayDetail(IN dtm VARCHAR(20))
BEGIN
SELECT emp.empId,emp.ename,emp.basepay,
CASE
WHEN (SELECT sum(hours * 20) FROM tb_overtime WHERE emptId=emp.Id AND DATE_FORMAT(overtimeDtm,'%Y-%m')=dtm) is NULL THEN 0
ELSE (SELECT hours*20 FROM tb_overtime WHERE emptId=emp.Id AND DATE_FORMAT(overtimeDtm,'%Y-%m')=dtm)
END overtimepay,
CASE
WHEN (SELECT sum(days*(CASE WHEN PersonalLeaveType=0 THEN 100 ELSE 20 END)) FROM tb_personalleave WHERE emptId=emp.Id AND DATE_FORMAT(PersonalLeaveDtm,'%Y-%m')=dtm) is NULL THEN 0
ELSE (SELECT sum(days*(CASE WHEN PersonalLeaveType=0 THEN 100 ELSE 20 END)) FROM tb_personalleave WHERE emptId=emp.Id AND DATE_FORMAT(PersonalLeaveDtm,'%Y-%m')=dtm)
END personalleavepay,
CASE
WHEN (SELECT sum(days*150) FROM tb_absence WHERE emptId=emp.Id AND DATE_FORMAT(absenceDtm,'%Y-%m')=dtm) is NULL THEN 0
ELSE (SELECT sum(days*150) FROM tb_absence WHERE emptId=emp.Id AND DATE_FORMAT(absenceDtm,'%Y-%m')=dtm)
END absencepay
FROM tb_emp emp;
END
CALL proc_getPayDetail('2016-05');
2.sqlserver的
create procedure proc_getPayDetail
(
@dtm varchar(20)
)
as
begin
select
@dtm,
emp.id,
emp.empid,
emp.ename,
emp.basepay,
case
when(select sum(days*150 ) from tb_Absence
where emptId=emp.id and CONVERT(varchar(100),
tb_Absence.absenceDtm, 102)=@dtm) is null then 0
else(select sum(days*150 ) from tb_Absence
where emptId=emp.id and CONVERT(varchar(100),tb_Absence.absenceDtm, 102)=@dtm)
end absencepay
,
case
when(select sum(hours*20) from tb_overtime where emptId=emp.id
and CONVERT(varchar(100), tb_overtime.overtimeDtm, 102)=@dtm)
is null then 0
else(select sum(hours*20) from tb_overtime where emptId=emp.id
and CONVERT(varchar(100), tb_overtime.overtimeDtm, 102)=@dtm)
end overtimepay
,
case
when(select sum(days*case when PersonLeaveType=0
then 100 else 20 end) from tb_PersonalLeave
where emptId=emp.id
and CONVERT(varchar(100),tb_PersonalLeave.PersonLeaveDtm, 102)=@dtm)
is null then 0
else(select sum(days*case when PersonLeaveType=0
then 100 else 20 end) from tb_PersonalLeave where emptId=emp.id
and CONVERT(varchar(100),tb_PersonalLeave.PersonLeaveDtm, 102)=@dtm)
end personalleavepay
from tb_emp emp
end
drop procedure proc_getPayDetail
Exec proc_getPayDetail '2016.09.01'
3.
oracle:
CREATE OR REPLACE PROCEDURE存储过程名
(
--定义参数
is_ym IN CHAR(6) ,
the_count OUT NUMBER,
)
AS
--定义变量
vs_msg VARCHAR2(4000); --错误信息变量
vs_ym_beg CHAR(6); --起始月份
vs_ym_end CHAR(6); --终止月份
vs_ym_sn_beg CHAR(6); --同期起始月份
vs_ym_sn_end CHAR(6); --同期终止月份
--定义游标(简单的说就是一个可以遍历的结果集)
CURSOR cur_1 IS
SELECT 。。。
FROM 。。。
WHERE 。。。
GROUP BY 。。。;
BEGIN
遍历游标:
Cursor cursor is select name from student; name varchar(20);
begin
for name in cursor LOOP
begin
dbms_output.putline(name);
end;
end LOOP;
end test;
oracle使用create or replace.
mysql使用先删除老的存储过程,然后再创建新的存储过程.
5.
- CREATE
PROCEDURE demo_in_parameter(IN p_in int) - ->
BEGIN -
- ->
SET p_in=2; -
- ->
END;
执行结果:
-
SET @p_in=1; - CALL
demo_in_parameter(@p_in);