存过及如何给变量赋值
发布时间:2020-12-12 16:01:38 所属栏目:百科 来源:网络整理
导读:####无参存过 create or replace procedure init_Income_EveMonth isbegin -- 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
####无参存过 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; 上面是一个无参存过;
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;
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |