关于Oracle过程,函数的经典例子及解析
2024-08-22 06:37:53
一,Oracle中的过程,函数
对于oracle中的过程和函数,个人觉得可以化为一类,因为它们在写法上并没有什么的不同。公式无非就是
create or replace Package_name(paramater1 in type,paramater2 in type,out_message out type) is locate_paramater1 type; locate_paramater2 type; begin begin select sth into locate_paramater1 from table_name where 。。。。 exception when no_data_found then locate_paramater1:=' '; out_message:='Some Information to user to look'; end begin select sth into locate_paramater2 from table_name where 。。。。 exception when no_data_found then locate_paramater2:=' '; out_message:='Some Information to user to look'; end end
总而言之,言而总之:包声明,私有数据神马的在紧跟is之后。接着就是一串的begin......exception....end;
函数
create or replace function_name(paramater1 in type,paramater2 in type) return return_type; is locate_paramater1 type; locate_paramater2 type; out_message return_type; begin begin select sth into locate_paramater1 from table_name where 。。。。 exception when no_data_found then locate_paramater1:=' '; out_message:='Some Information to user to look'; return out_message; when others then out_message:='Error'; return out_message; end begin select sth into locate_paramater2 from table_name where 。。。。 exception when no_data_found then locate_paramater2:=' '; out_message:='Some Information to user to look'; return out_message; end end
实战演练
create or replace procedure inOrUp_Sc_Number(ip_sno in number,ip_courseCount in number)
is
tempt_count number(4);
e_sc_number number(4);
begin begin
--tab 是一个系统表,存储当前用户下所拥有的表信息
select 1 into e_sc_number from tab where tname='SC_Number';
exception
when no_data_found then
--注意对私有数据的赋值方式!!!!
e_sc_number:=0;
when others then
dbms_output.put_line(sqlerrm);
return;
end;
--endOfBegin
if e_sc_number=0 then
--这个是动态sql,因为在执行这句话之前不知道是否要执行,所以要用动态的
--动态sql的语法:execute immediate 'sql statement'
execute immediate
'create table SC_Number(sno number(5) primary key,cnum number(3) )';
execute immediate
'insert into SC_Number select sno,count(*) from sc group by sno';
commit;
end if; begin
--这个也是动态sql
--需要注意的是给动态sql,动态的赋值,=:number useing paramater
execute immediate
'select 1 from SC_Number where sno=:1' into tempt_count using trim(ip_sno);
exception
when no_data_found then
tempt_count:=0;
when others then
dbms_output.put_line(sqlerrm);
return;
end;
--endOfBegin
if tempt_count=1 then
begin
execute immediate
--这个还是动态sql
--需要注意的是给动态sql,动态的赋值,=:number useing paramater
'update SC_Number set cnum=:1 where sno=:2'using ip_courseCount,trim(ip_sno);
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
return;
end;
end if;
--endOfIf
if tempt_count=0 then
begin
execute immediate
--为神马这么多动态sql
'insert into SC_Number values(:1,:2)'using ip_sno,ip_courseCount;
commit;
exception
when others then
dbms_output.put_line(sqlerrm);
return;
end;
end if;
--endOfIf
end;
create or replace function func_InsetOrUpdateSc(ip_sname in varchar2,ip_cname in varchar2,ip_grade in varchar2)
--这个是函数的返回类型
return varchar2
is
student_num student.sno%type;
course_num course.cno%type;
out_messsage varchar2(30);--注意私有数据声明时要标注大小
tempt_count number(4);
begin begin
select sno into student_num from student where student.snmae=ip_sname;
exception
when no_data_found then
student_num:='';
out_messsage:='No such a Student';
return out_messsage;
when others then
out_messsage:='Error';
return out_messsage;
end;
--endOfBegin
begin
select cno into course_num from course where course.cname=ip_cname;
exception
when no_data_found then
course_num:='';--私有数据的赋值方法和过程类似
out_messsage:='No such a Course';
return out_messsage;
when others then
out_messsage:='Error';
return out_messsage;
end;
--endOfBegin
begin
select 1 into tempt_count from sc where sc.sno=student_num and sc.cno=course_num;
exception
when no_data_found then
tempt_count:=0;
when others then
out_messsage:='Error';
return out_messsage;
end;
--endOfBegin
if tempt_count=1 then
begin
update sc set sc.grade=ip_grade where sc.sno=student_num and sc.cno=course_num;
--注意下面的commit语句,在update,insert时,不要忘记了哦
commit;
out_messsage:='Update Success';
return out_messsage;
exception
when others then
out_messsage:='Error';
return out_messsage;
end;
end if;
--endOfIf
if tempt_count=0 then
begin
insert into sc values(student_num,course_num,ip_grade);
commit;
out_messsage:='Insert Success!';
return out_messsage;
exception
when others then
out_messsage:='Error';
return out_messsage;
end;
end if;
--endOfIf
end;
最新文章
- codevs 2216 线段树 两种更新方式的冲突
- Codeforces Round #155 (Div. 2)
- Steam即将正式加入人民币支付(转)
- PHP中正则替换函数preg_replace用法笔记
- java对cookie的操作_01
- ASP.NET MVC4 + Highcharts生成报表
- mac + apache + php
- Derby的下载安装和使用,(和JAVA中使用Derby)
- Windows版Cacti全新安装与升级
- js中常见的问题
- ——————————JavaScript中,对String字符串的一些操作——————————
- 张高兴的 Xamarin.Android 学习笔记:(二)“Hello World”
- AtCoder Beginner Contest 069【A,水,B,水,C,数学,D,暴力】
- 【一天一道LeetCode】#53. Maximum Subarray
- netsh禁用启用本地连接
- IPhone微信H5用Video标签播放不了视频
- jQuery -- 光阴似箭(四):jQuery 遍历
- ng2-tree
- AI金融知识自学偏量化方向-前提1
- c# 之系统环境安装
热门文章
- ref 关键字修饰引用类型
- Nuget程序包 使用log4net
- 装饰器、生成器,迭代器、Json &; pickle 数据序列化
- PAT 解题报告 1010. Radix (25)
- PAT 解题报告 1009. Product of Polynomials (25)
- c# 访问ftp
- Leetcode: Create Maximum Number
- Dropbox能火,为何它的中国同行不能火?
- FAQ: C++中定义类的对象:用new和不用new有何区别?
- linux挂接U盘