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
deletefrom 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;
···