MYsql和oracle的存储过程的创建

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

遍历游标：

create or replace procedure test() as
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;

4. 1.在创建存储过程时如果存在同名的存储过程,会删除老的存储过程.  
  oracle使用create or replace.
  mysql使用先删除老的存储过程,然后再创建新的存储过程.

5.

1. CREATE PROCEDURE demo_in_parameter(IN p_in int)  
2. -> BEGIN   
3. 
   
4. -> SET p_in=2;   
5. 
   
6. -> END;   

执行结果:

1.  SET @p_in=1;  
2. CALL demo_in_parameter(@p_in);