存过及如何给变量赋值

前端之家收集整理的这篇文章主要介绍了存过及如何给变量赋值前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

####无参存过

create or replace procedure init_Income_EveMonth is
begin
  -- Created on 2016/9/6 by YANGQC 
  --每月初始化薪资表
  declare
    cursor salesset is
      select s.user_id,to_char(sysdate,'yyyy-mm') month
        from sys_salesman s
        left join sys_user u on s.user_id = u.user_id
       where u.status = 0
         and not exists (select 1
                from sys_income_main m
               where m.month = to_char(sysdate,'yyyy-mm')
                 and m.user_id = s.user_id);
    salary Integer;
  begin
    for salesman in salesset loop
    
      select nvl((select b.salary
                 
                   from sys_income_basicsalay b
                  where b.user_id = salesman.user_id
                    and b.flag = 0),0)
        into salary
        from dual;
      insert into sys_income_main m
        (m.id,USER_ID,month,BASIC_SALARY,ALLRESULT)
      values
        ((select max(m1.id) + 1 from sys_income_main m1),salesman.user_id,salesman.month,salary,salary);
    end loop;
    commit;
  end;
end init_Income_EveMonth;

上面是一个无参存过;

  • 使用into给参数salary赋值,这个只适用于select语句查询出结果的情况;
  • 存过格式如下:
create or replace procedure xxx
begin 
end xxx;

####有参存过

create or replace procedure test_parameter(emp_name in varchar2,dept_no out number ) is
begin
  dept_no:=2;
end test_parameter;

这个存过定义了输入和输出参数 测试该存过

-- Created on 2016/9/6 by YANGQC 
declare 
  -- Local variables here
  i integer:=20;
  x varchar2(20);
begin
  test_parameter(x,i);
   dbms_output.put_line('After swap: x = ' || x || ',i= ' ||i);
end;
  • 输出结果:After swap: x =,i= 2 有参存过的输入和输出参数都不可缺少
原文链接:https://www.f2er.com/oracle/212759.html

猜你在找的Oracle相关文章