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;