/**
1.plsql语句是匿名块,并且不能在数据库中永久的保存,因为我们要对匿名块进行封装
1.1匿名块的缺点:相应的语句不能永久的保存到服务器中,复用性太差
(不可能保存到客户端,都是保存到服务器端)
1.2为了解决这个问题:引入了函数和存储过程,都是对匿名块的封装
**/
----1.无参函数,比较两个数的大小
create or replace function maxNum -----注意:无论是函数还是存储过程,返回值和输入输出参数都不能指定大小
return number
is
v_num1 number(3):=100;
v_num2 number(3):=101;
v_max number;---接收返回值
begin
if v_num1>v_num2 then
v_max:=v_num1;
else
v_max:=v_num2;
end if ;
return v_max;
end;
----由于没有输出参数,可以使用select语句调用,也可以使用匿名块调用上面的函数
select maxNum from dual
------------------------------
--2.带参函数(输出参数in,就是形参)
create or replace function maxNum(v_num1 in number,v_num2 number)---in是输入参数,可以不写。out是输出参数
return number
is
v_max number;---接收返回值
begin
if v_num1>v_num2 then
v_max:=v_num1;
else
v_max:=v_num2;
end if ;
return v_max;
end;
----由于没有输出参数,可以使用select语句调用,也可以使用匿名块调用上面的函数
select maxNum(12,500) from dual
---------------------------
--3.带参函数(out是输出参数),如果有输出函数,就不能用select语句测试了,就得用匿名块测试了
create or replace function maxNum(v_num1 in number,v_num2 in number,v_r out number)---in是输入参数,可以不写。out是输出参数
return number
is
v_max number;---用来接收返回值
begin
if v_num1>v_num2 then
v_max:=v_num1;
v_r:=1;
elsif v_num1<v_num2 then
v_max:=v_num2;
v_r:=-1;
else
v_r:=0;
end if ;
return v_max;
end;
--select maxNum(12,500,?) from dual--第三个参数写写什么?不知道,因为输出参数不是变量(select中只能出现变量),因此不能出现在select语句中。因此用select语句无法调用上面的函数了
-------
---如果有输出参数,就要匿名块调用(因为要定义变量接收这个输出参数),只能使用匿名块调用上面的函数
declare
v_res number;---定义变量,充当输出参数
v_rtu number;-----定义变量接收返回值
begin
v_rtu:=maxNum(1,2,v_res);
dbms_output.put_line(v_res||' '||v_rtu);
end;
---------------------------------------------
-------练习:写一个函数,统计参加考试的人和未参加考试的人
-------分析:这里需要输出两个值,因此一个是返回值,另一个是输出参数。因为有输出参数,因此在调用此函数的时候需要使用匿名块调用
create or replace function countNum(v_join out number) return number ---v_join表示参加考试的学生
is
v_num number;----定义变量接收返回值
begin
---得到参加考试的人,并把值赋值给v_join
select count(distinct sno) into v_join from score;
---得到没参加考试的人,结果复制给返回值
select count(*) into v_num from student where sno not in (select sno from score);
return v_num;
end;
declare
v_result number;---定义变量,充当输出参数
v_return number;---接收返回值
begin
v_return:=countNum(v_result);
dbms_output.put_line('参加考试的人'||v_result||' 未参加考试的人'||v_return);
end;
-------------------------------------------
--第二部分:存储过程:不能被select调用,只能用匿名块调用
--自我理解:(因为存储过程没有返回值,因此用输出参数充当返回值,既然使用了输出函数,就要用匿名块调用了)
--表,存储过程,函数,视图等都是数据库对象
------------------------------------------------
--1.无参存储过程
create or replace procedure
proc_max
is
v_num1 number(3):=100;
v_num2 number(3):=200;
v_max number;
begin
if v_num1> v_num2 then
v_max:=v_num1;
else
v_max:=v_num2;
end if;
dbms_output.put_line('v_max='||v_max);
end;
------调用存储过程(只能使用匿名块)
begin----由于没有输出参数,因此不用定义变量,因此不用写declare,可以写,写了也没用
proc_max;
end;
-----------------------------------------
---2.带参存储过程
create or replace procedure
proc_max1(v_num1 number,v_num2 number,
v_res out number)
is
begin
if v_num1>v_num2 then
v_res:=v_num1;
elsif v_num1<v_num2 then
v_res:=v_num2;
else
v_res:=0;
end if;
end;
-------必须用匿名块调用存储过程
declare
v_result number;
begin
proc_max1(34,65,v_result);
dbms_output.put_line(v_result);
end;
-------------------------------------------
---练习:使用存储过程求出两个数中的较大值
create or replace procedure proc_max(v_num1 number,v_num2 number,v_max out number)
is
----需要变量就写,如果什么也不需要就什么不写
begin
if v_num1>v_num2 then
v_max:=v_num1;
dbms_output.put_line(v_max);
elsif v_num1<v_num2 then
v_max:=v_num2;
dbms_output.put_line(v_max);
else
v_max:=0;
dbms_output.put_line(v_max);
end if;
end;
----只能使用匿名块调用存储过程
declare
-- v_a number(2):=1;
--v_b number(2):=2;
v_c number(2);
begin
proc_max(1,2,v_c);
end;
------------
--函数和存储过程的区别:
---相同点:
---1.封装匿名块,提高代码复用性。
---2.都可以有输入输出参数
-----不同:
----1.函数必须有返回值,存储过程没有返回值
----1.函数可以通过select语句或者匿名块调用。存储过程只能用匿名块调用
------------------------------------------------------------------------
----练习:实现学生表student中 ,写一个函数将电话号码的中间四为用*表示(意思是在查询的时候电话号码的显示中间四位的时候是*)
----思考:首先考虑是使用函数做还是使用存储过程做,由于最后要写一个查询语句显示电话号码和学生表的其他信息,因此只能用函数写(只有函数能用select查询语句)
select * from student
create or replace function substel(v_tel in varchar2) return varchar2
is
v_res varchar2(11);----用于接收返回值(return)
v_si varchar2(4);---前四位
v_san varchar2(3);---后三位
begin
v_si:=substr(v_tel,1,4);
v_san:=substr(v_tel,9,3);
v_res:=v_si || '****' || v_san ;
return v_res;
end;
select sno,sname,substel(stel) from student;----这里这个函数就相当于max
--------------------
---第三部分.视图:是一张包含特定数据的虚拟表(不存在的数据)(内容和table表一样,但是效果不一样)
---例题1:查询补考过学生的信息
select student.* from student
where sno in(select sno from score group by cid,sno having count(*)>1)
---现在想找到那个学号的那个学生的那个科目补考过(这也不能永久的保存,这时候就用虚拟表保存)
---视图名字as select语句。as是作为,是把复杂是select语句起个名字保存下来
---视图一般关联多张表。就是给复杂的select起个别名永久的保存,以后就直接用别名调用了。
---因此现在把上面的select语句创建一个试图
create or replace view view_exam_two---保存参加过补考的学生
as
select student.* from
student
where sno in(select sno from score
group by sno,cid
having count(*)>1)
select * from view_exam_two---查询试图
--------------------------------------------
---例题2:查询参加过考试的学生的学号,姓名,科目
create or replace view view_join_exam
as
select student.sno,sname,cname from
student inner join score
on student.sno=score.sno
inner join course
on score.cid=course.cid
-----查询试图
select * from view_join_exam
---视图的好处
--1.简化对复杂select的处理
--2.隐藏表结构,增加数据的安全性
---视图的分类:单表视图,多表视图,视图的视图
----单表视图:就是为了给列起个别名(隐藏表和表结构,这样外人就不知道表的名字和这表的数据代表什么意思了)。单表视图可以update
----多表跟单表一样
---视图的视图:在视图上在创建视图。一级视图,二级视图。。。不能看到物理表
-----------------------------------------------------------
--第四部分:序列:数据库来维护,一组能够自动增长的值:要不要循环(例如:一次产生5个数,用完在给)。要不要排序
---有两个属相,nextval:下一个值 currval:当前值
---用处:用序列的值充当主键 例如:insert into student(seq_sno.nextval,,,,)
create sequence SEQ_SNO-----在左侧找到sequences,右键新建,添加值,这就创建了一个完整的序列
minvalue 100000
maxvalue 999999
start with 111200
increment by 1
cache 5
order;
select seq_sno.currval from dual