存储过程文档
创建了四个不同的存储过程,用于对一个名为department的表进行 CRUD 操作。每一个存储过程都有独立的作用,具体如下:
1. addone 存储过程:用于向 department 表中插入一条新的记录,参数为 v_deptno、v_dname、v_loc。
create or replace procedure addone(v_deptno department.dno%type,
v_dname department.dname%type,
v_loc department.stucount%type) is
begin
insert into department values (v_deptno, v_dname, v_loc);
commit;
end;
2. updateone 存储过程:用于更新 department 表中的一条记录,参数为 v_deptno、v_dname、v_loc。
create or replace procedure updateone(v_deptno department.dno%type,
v_dname department.dname%type,
v_loc department.stucount%type) is
begin
update department
set dname = v_dname, stucount = v_loc
where dno = v_deptno;
commit;
end;
3. deleteone 存储过程:用于从 department 表中删除一条记录,参数为 v_deptno。
create or replace procedure deleteone(v_deptno department.dno%type) is
begin
delete from department where dno = v_deptno;
commit;
end;
4. findall 存储过程:用于查询 department 表中的全部记录。
create or replace procedure findall is
cursor v_depts is
select * from department;
begin
for c in v_depts loop
dbms_output.put_line(c.dno || ',' || c.dname || ',' || c.stucount);
end loop;
end;
5. findbyid 存储过程:用于根据给定的 v_deptno,查询 department 表中对应的一条记录。
create or replace procedure findbyid(v_deptno department.dno%type) is
v_dept department%rowtype;
begin
select * into v_dept from department where dno = v_deptno;
dbms_output.put_line(v_dept.dno || ',' || v_dept.dname || ',' ||
v_dept.stucount);
end;
使用实例:
1. 查询全部记录:
declare
begin
findall;
end;
2. 查询指定记录:
declare
begin
findbyid(12);
end;
3. 新增一条记录:
declare
begin
addone(12, '语文', 200);
end;
4. 修改一条记录:
declare
begin
updateone(12, '数学', 300);
end;
5. 删除一条记录:
declare
begin
deleteone('12');
end;