使用对象类型+在存储过程使用+在函数中使用

使用对象类型

在存储过程使用
增删改查

--查询
create or replace procedure select_stu_class(cid in stu_class.id%TYPE)
as 
stu_c class4;
begin
select value(sc) into stu_c from stu_class sc where id=cid;
dbms_output.put_line(stu_c.id||' '||stu_c.name);
end select_stu_class;
/


--插入
create or replace procedure insert_stu_class(cid in stu_class.id%TYPE,cname in stu_class.name%TYPE)
as 
v_stu_c class4:=class4(cid,cname);
begin
insert into  stu_class values(v_stu_c);
exception
when others then rollback;
end insert_stu_class;
/

--更新
create or replace procedure update_stu_class(v_class4 in class4)
as 
begin
update stu_class sc set  sc=v_class4  where  id=v_class4.id;
exception
when others then rollback;
end update_stu_class;
/
--删除
create or replace procedure delete_stu_class(cid in stu_class.id%TYPE)
as 
begin
delete  stu_class  where  id=cid;
exception
when others then rollback;
end delete_stu_class;
/


 call select_stu_class(1101);
 call insert_stu_class(1103,'英语');
 select * from stu_class;
 call update_stu_class(class4(1103,'商务英语'));
  select * from stu_class;
 call delete_stu_class(1103);
  select * from stu_class;

在函数中使用
在函数中使用1

--函数
create or replace function select_stu_class_function(cid in stu_class.id%TYPE) return class4 
as
stu_c class4;
begin
select value(sc) into stu_c from stu_class sc where id=cid;
return stu_c;
end select_stu_class_function;

select select_stu_class_function(1101) from dual;
/

在函数中使用2

 create or replace function select_stu_class_function2(cid in stu_class.id%TYPE) return  ref class4 
as
stu_c  ref  class4;
begin
select ref(sc) into stu_c from stu_class sc where id=cid;
return stu_c;
end select_stu_class_function2;
/


 select deref(select_stu_class_function2(1101)) from dual;