【Oracle】简单的存储函数和存储过程以及触发器

-- 简单定义一个存储函数
create or replace function test_funtion(v_params1 varchar2)
return varchar2
is
begin
       return v_params1;
end;

-- 定义一个有out（记录）存储函数
create or replace function out_funtion(v_code varchar2,v_count out number) -- 输入名字输出总出单保费以及单数
return number
is
-- 申明变量
   cursor sum_cursor is select * from vx_main w where w.usercode =  v_code;
   v_sum number(10) := 0;
begin
     v_count := 0;
     for c in sum_cursor loop
        if (c.POLICYNO is not null or c.BZPOLICYNO is not null) and substr(c.UPDATETIME,1,10) >= '2019-08-01' and c.SUMPREMIUM is not null then 
          v_sum := to_number(c.SUMPREMIUM) + v_sum;
          dbms_output.put_line('费：'||c.sumpremium||'日期：'||c.UPDATETIME||'单号:'||c.BZPOLICYNO||'单号:'||c.POLICYNO);
          v_count := v_count + 1;
        end if;
     end loop;
     return v_sum/10000;
end;

declare 
     v_count number(10) := 0;
begin
   dbms_output.put_line(out_funtion('身份证号',v_count));
   dbms_output.put_line('单'||v_count);
end;
------------------------------------------------------------------------------
create or replace function exc_premium_function(v_count out number) -- 输出所有错误的单子
return number
is
       cursor allpremium_cursor is select * from vx_main;
       --v_count number(10);
begin
       v_count := 0;
       for c in allpremium_cursor loop
           if (c.POLICYNO is not null or c.BZPOLICYNO is not null) and substr(c.UPDATETIME,1,10) >= '2019-08-01' and c.SUMPREMIUM  is  null then 
              dbms_output.put_line('名字: '||c.username||' 交单号: '||c.BZPOLICYNO||'单号: '||c.POLICYNO);
              v_count := v_count + 1;
           end if;
       end loop;
       return v_count;
end;
------------------------------------------------------------------------------
declare
       v_count number(10) := 0;
begin
     dbms_output.put_line(exc_premium_function(v_count));
     --dbms_output.put_line(v_count);
end;
-- 存储过程--------------------------------------------------------------------------
create or replace procedure exc_premium_function2(v_count out number) -- 输出所有错误的单子
is
       cursor allpremium_cursor is select * from vx_main;
       --v_count number(10);
begin
       v_count := 0;
       for c in allpremium_cursor loop
           if (c.POLICYNO is not null or c.BZPOLICYNO is not null) and substr(c.UPDATETIME,1,10) >= '2019-08-01' and c.SUMPREMIUM  is  null then 
              dbms_output.put_line('名字: '||c.username||' 单号: '||c.BZPOLICYNO||'单号: '||c.POLICYNO);
              v_count := v_count + 1;
           end if;
       end loop;
      dbms_output.put_line('单数: '||v_count);
end;

-- 触发器--------------------------------------
create or replace trigger update_ws_carmain
after -- 后
   update on ws_carmain -- 当更新ws_carmain时触发
   for each row -- 每一行
begin
   dbms_output.put_line('old : '|| :old.username || ' new : '|| :new.username);
end;

---更新-----------------------------
update ws_carmain
set username = '山'
where usercode = '身份证'

-- 查询
select username from ws_carmain where usercode = '身份证'