oracle 存储过程 练习笔记

目标：

·熟悉Oracle存储过程的各种写法


先写下面几个存储过程(无，有，返回普通值，返回结果集)

在Oracle中编写：

1、创建班级表和学生表。

2、添加学生(学号，姓名，班级编号)，不返回任何值。

3、添加学生(姓名，班级编号)，由程序生成学号并返回(。

4、添加学生(姓名，班级编号)，并返回该班的所有学生。

解答：
1、创建 班级表过程 classinfo_create
CREATE OR REPLACE PROCEDURE classinfo_create
Authid Current_User
IS
v_classinfo_sql varchar2(1000);
v_flag integer;
BEGIN
v_classinfo_sql:='create table classinfo(
classinfo_id varchar2(20) primary key,
classinfo_name varchar2(20) not null
) ';
EXECUTE IMMEDIATE v_classinfo_sql;
EXCEPTION
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'drop table classinfo';
EXECUTE IMMEDIATE v_classinfo_sql;
END classinfo_create;
创建 学生表过程 student_class_create
CREATE OR REPLACE PROCEDURE student_class_create
Authid Current_User
IS
v_student_sql varchar2(1000);
v_classinfo_sql varchar2(1000);
v_flag integer;
BEGIN
v_flag:=1;
v_student_sql:='create table student(
student_id varchar2(20) primary key,
student_name varchar2(20) not null,
classinfo_id varchar2(20) not null
)' ;
v_classinfo_sql:='create table classinfo(
classinfo_id varchar2(20) primary key,
classinfo_name varchar2(20) not null
) ';
EXECUTE IMMEDIATE v_student_sql;
v_flag:=2;
EXECUTE IMMEDIATE v_classinfo_sql;

EXCEPTION
WHEN OTHERS THEN
if v_flag=1 then
EXECUTE IMMEDIATE 'drop table student';
EXECUTE IMMEDIATE v_student_sql;
classinfo_create;
end if;
if v_flag=2 then
EXECUTE IMMEDIATE 'drop table classinfo';
EXECUTE IMMEDIATE v_classinfo_sql;
end if;
END student_class_create;
2、添加学生(学号，姓名，班级编号)，不返回任何值 insert_student
CREATE OR REPLACE PROCEDURE insert_student(sid in varchar2,
sname in varchar2,
sclass in varchar2)
IS
BEGIN
insert into student(student_id,student_name,classinfo_id)-- values (sid,sname,sclass);
select sid as student_id,sname as student_name,sclass as classinfo_id from dual;
commit;
exception
when others then dbms_output.put_line(sqlcode||sqlerrm);
END insert_student;
测试方法：
exec insert_student('1','zhangsan','1');
3、添加学生(姓名，班级编号)，由程序生成学号并返回 insert_student2
CREATE OR REPLACE PROCEDURE insert_student2(sid out varchar2,
sname in varchar2,
sclass in varchar2)
IS
BEGIN
select max(student_id) into sid from student;
sid:=nvl(sid,0)+1;
insert into student(student_id,student_name,classinfo_id)-- values (sid,sname,sclass);
select sid as student_id,sname as student_name,sclass as classinfo_id from dual;
commit;
exception
when others then dbms_output.put_line(sqlcode||sqlerrm);
END insert_student2;
测试方法：
declare
v_out varchar2(100);
begin
insert_student2(v_out,'221','1');
dbms_output.put_line(v_out);
end;
4、添加学生(姓名，班级编号)，并返回该班的所有学生 insert_student3
CREATE OR REPLACE PROCEDURE insert_student3(sysrefcursor out sys_refcursor,sname in varchar2,sclass in varchar2) IS
refcursor sys_refcursor;
sid varchar2(20);
BEGIN
select max(student_id) into sid from student;
sid:=nvl(sid,0)+1;
insert into student(student_id,student_name,classinfo_id)
select sid as student_id,sname as student_name,sclass as classinfo_id from dual;
commit;
open refcursor for select * from student where classinfo_id=sclass order by student_id;
sysrefcursor := refcursor;
exception when others then dbms_output.put_line(sqlcode||sqlerrm);
END insert_student3;
测试方法：
declare
v_out sys_refcursor;
sid varchar2(20);
sname varchar2(20);
sclass varchar2(20);
begin
insert_student3(v_out,'222','2');
loop
fetch v_out into sid,sname,sclass; --fetch ..into ..exit when遍历游标的方式之一
exit when v_out%notfound;
dbms_output.put_line(sid||','||sname||','||sclass);
end loop;
close v_out;
end;