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;