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;