oracle存储过程的实现及调用实例

oracle存储过程的实现及调用实例

create table t_project                                  --创建表
(id number(4) primary key not null,
name varchar2(60) not null,
star_time varchar2(30),
end_time varchar2(30),
now_stage varchar2(20),
profit  number(7),
manager varchar2(10),
tem_sum number(5)
);

-------------------------------------------------------------------------------------------------------------------
create or replace procedure add_project(                                --1.创建增加过程
                                       v_id in number,
                                       v_name in varchar2,
                                       v_star_time in  varchar2,
                                       v_end_time in varchar2,
                                       v_now_stage in  varchar2,
                                       v_profit in number,
                                       v_manager in  varchar2,
                                       v_tem_sum in number                
                                      
                                      ) is
                                            
begin
  --插入数据
   insert into t_project values (v_id,v_name,v_star_time,v_end_time,v_now_stage,v_profit,v_manager,v_tem_sum);
      
exception
    when no_data_found then
        dbms_output.put_line('你需要的数据不存在!');
    when others then
        dbms_output.put_line(sqlcode || '---' || sqlerrm);
end add_project;
------------------------------------------------------------------------------------------------------------------

create or replace procedure del_project(                                 --2.创建删除过程
                                       v_id in number
                                                     
                                      ) is
                                            
begin
 
  delete from t_project t where t.id=v_id;
       
exception
    when no_data_found then
        dbms_output.put_line('你需要的数据不存在!');
    when others then
        dbms_output.put_line(sqlcode || '---' || sqlerrm);
end del_project;

-----------------------------------------------------------------------------------------------------------------

create or replace procedure upda_project(                                 --3.创建修改过程
                                       v_id in number,
                                       v_name in varchar2,
                                       v_star_time in  varchar2,
                                       v_end_time in varchar2,
                                       v_now_stage in  varchar2,
                                       v_profit in number,
                                       v_manager in  varchar2,
                                       v_tem_sum in number   
                                                     
                                      ) is
                                            
begin
 
   update t_project t set t.name=v_name ,
                          t.star_time=v_star_time ,
                          t.end_time=v_end_time ,
                          t.now_stage=v_now_stage ,
                          t.profit=v_profit,
                           t.manager= v_manager,
                            t.tem_sum=v_tem_sum  where t.id=v_id;
       
exception
    when no_data_found then
        dbms_output.put_line('你需要的数据不存在!');
    when others then
        dbms_output.put_line(sqlcode || '---' || sqlerrm);
end upda_project;


----------------------------------------------------------------------------------------------------------------


create or replace procedure select_project(                     --4.创建查询过程
                                             v_id in number,
                                             c_mycur out sys_refcursor                       
                                      
                                      ) is
begin 
  open c_mycur for select * from t_project t where t.id=v_id ;

exception
    when no_data_found then
        dbms_output.put_line('你需要的数据不存在!');
    when others then
        dbms_output.put_line(sqlcode || '---' || sqlerrm);
end select_project;

------------------------------------------------------------------------------------------------------------------
                                                             

   
begin
                             --调用插入过程
  add_project(1,'股票管理系统','2018.05.05','2020.02.01','开始编码',10000,'张三',40);
  add_project(2,'国债投资管理系统','2017.09.05','2018.03.05','已验收',20000,'张三',50);
  add_project(3,'基金投资管理系统','2018.09.05','2019.10.14','系统测试',30000,'张三',60);
  add_project(4,'期货管理系统','2016.09.05','2017.02.01','系统测试',10000,'张三',73);
  add_project(5,'彩票系统','2019.06.05.06','2020.06.05','需求分析',20000,'张三',46);
  
  end;
  
  
  
  
 begin   
                          --调用删除过程
  del_project(1);
  
  end;
  
  
  
  
                                --调用修改过程
begin
  upda_project(2,'投资管理系统','2018.09.05','2019.04.14','系统测试',4500,'张三',60);
    end;
 



   
declare                      --调用查询过程
          id  number;
          name  varchar2(60);
          star_time   varchar2(60);
          end_time  varchar2(60);
          now_stage   varchar2(60);
          profit  number;
          manager   varchar2(60);
          tem_sum  number; 
          type_cur sys_refcursor; 
          
begin                                                                             
         select_project(3,type_cur);
 

 loop
    fetch type_cur into id,name,star_time,end_time,now_stage,profit,manager,tem_sum;
    exit when type_cur %notfound;
    dbms_output.put_line(id||' '||name||' '||star_time||' '||end_time||' '||now_stage||' '||profit||' '||manager||' '||tem_sum);
  end loop;
  close type_cur;
end;



---------------------------------------------------------------------------------------------------------------------------------------

select id,name 项目名,star_time 开始时间,end_time 结束时间,now_stage 执行阶段,profit 总价,manager 项目经理,tem_sum 团队人数 from t_project;