在存储过程(PROCEDURE)和函数(FUNCTION)中没有区别；
在视图(VIEW)中只能用AS不能用IS；
在游标(CURSOR)中只能用IS不能用AS。
第一个简单的存储过程
1.无参存储过程
调用
2.带输入参数的存储过程
如下 使用标量变量作为输入参数，实现向部门表dept 增加信息
实现插入语句
检查 通过查看表 可以查看记录是否进去了。
2.2使用记录类型作为输入参数如下 使用标量变量作为输入参数，实现向部门表dept 增加信息
调用:
2.3 使用集合类型作为输入参数
第一步
基于部门dept表创建嵌套表类型
第二步
基于嵌套表类型创建存储过程
第三步
3.带输出参数的存储过程
3.1 使用标量变量作为输出参数
使用标量变量作为输出参数，根据输入的部门号，输出当前部门信息
调用存储过程get_dept ;
结果:
3.2 使用记录类型作为输出参数
调用存储过程get_dpet2
3.3 使用集合类型作为输出参数
调用
结果:
4.带输入/输出参数的存储过程
调用
5 补充学习
存储过程中的AS/IS相当于PLSQL中的declare地方
--为指定员工 涨100块钱的工资，并且打印涨钱和涨后的薪水
create or replace procedure p2 (eno IN emp.empno%TYPE )
IS
beforesal emp.sal%TYPE ;
aftersal emp.sal%TYPE ;
BEGIN
select sal into beforesal from emp where empno=eno ;
dbms_output.put_line('涨工资之前'||beforesal);
update emp set sal=sal+100 where empno =eno ;
select sal into aftersal from emp where empno=eno ;
dbms_output.put_line('涨工资之后'||aftersal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('没数据');
END ;--查询某个员工姓名和月薪
create or replace procedure queryinfo(pno emp.empno%TYPE, pname OUT emp.ename%TYPE,psal OUT emp.sal%TYPE)
is
begin
select ename,sal into pname ,psal from emp where empno=pno ;
dbms_output.put_line(pname||psal);
end ;6 包头 包体 光标
---思考 查询某个员工的所有信息 out太多？
---思考 查询某个部门下所有员工信息
在OUT参数中使用光标
申明包结构
包头
包体
包体需要实现包头中声明的所有方法
--声明包头
CREATE OR REPLACE PACKAGE MYPACKAGE
AS
type empcursor is ref cursor ;
procedure queryEmpList(dno IN NUMBER ,empList OUT empcursor );
--如果有其他存储过程可以继续添加
END MYPACKAGE ;
--声明包体
CREATE OR REPLACE
PACKAGE BODY MYPACKAGE AS
procedure queryEmpList(dno IN NUMBER ,empList OUT empcursor )AS
BEGIN
--TODO
--打开光标
open empcursor for select * from emp where empno =dno ;
END queryEmpList ;
END MYPACKAGE ;
7维护存储过程
7注意事项
oracle存储过程中is和as区别
PLSQL
什么是PL/SQL程序
PL/SQL （Procedure Language/SQL）
PLSQL是Oracle对sql语言的过程化扩展
PLSQL是面向过程的语言
1.基本数据类型
PLSql 包括标量类型、复合类型、参照类型、LOB类型灯4种类型
1.1 标量变量
标量变量是指只能存放单个数值的变量。当定义标量变量时，需要指定标量数据类型，常用的标量数据类型有VARCHAR2,CHAR、NUMBER、DATE、BOOLEAN、LONG和BINARY INTEGER等。
1.声明变量
v_sno VARCHAR2(20);
v_age NUMBER(2);
v_sex CHAR(1):='1' ; //定义变量并且赋值
c_count CONSTANT NUMBER(2):=10 ; //定义常量
2.定义变量
v_sno:='95001';
v_age:=23;
另一种方式是使用SELECT INTO 语句或者FETCH INTO语句给变量赋值。
案例如下
使用替换变量输入员工号，输出当前员工的相关信息
使用%TYPE
在定义变量时，如果其数据类型与已经定义的某个数据类型变量类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE,该属性会自动根据表列或其他变量的类型和长度定义新变量。
案例如下
输入员工号，输出当前员工的相关信息
记录类型
当使用PL/SQL记录时，既可以自定义记录类型和记录变量，也可以使用%ROWTYPE属性来定规记录变量。
案例1 输入员工号，输出当前员工的相关信息
自定义记录类型（切记切记）
%ROWTYPE记录类型
IF条件语法
CASE WHEN 用法
-- case when 用法
declare
grade char(10):=UPPER('&input');
begin
case grade
when 'A' then dbms_output.put_line('优秀');
when 'B' then dbms_output.put_line('一般');
end case ;
end ;-- case when 两种用法
declare
v_number number :=&input ;
begin
case v_number
when 1 then dbms_output.put_line('牛逼');
end case ;
end ;
declare
v_number number:=&input ;
begin
case
when v_number>1 then dbms_output.put_line('牛逼');
else dbms_output.put_line('太小啦');
end case ;
end ;--可转变的类型赋值
v_count :=TO_NUMBER('20');
v_pay:=TO_CHAR('3000.79');
--字符串转日期
v_date:=TO_DATE('2012.07.03','yyyy.mm.dd');
---日期转字符
v_now :=TO_CHAR(SYSDATE,'yyyy.mm.dd hh24:mi:ss');
循环用法
游标用法
光标（游标）
就是一个结果集(ResultSet)
光标的语法
CURSOR光标名 [(参数名数据类型[参数名数据类型])] IS SELECT语句 ;
(注意光标的属性有两种 %found 和 %notfound 返回值都是truefalse)
例子:
查询所有员工的名字和工资
例子 给员工涨工资
光标的另外两个属性
光标
%isopen 判断光标是否打开
光标%rowcount 被影响的条数
光标数的限制：默认情况下oracle数据库只允许在同一会话中，打开300个光标。
案例 带参数的光标
-- 查询某个部门下的名字和薪水
declare
cursor res(dno number) is select ename ,sal from emp where deptno =dno ;
myname emp.ename%TYPE;
mysal emp.sal%TYPE;
begin
open res(10);
loop
fetch res into myname,mysal ;
dbms_output.put_line(myname||'的薪水是'||mysal);
exit when res%notfound ;
end loop ;
close res ;
end ;
PLSQL中的例外
系统例外
no_data_found 没有找到数据
too_many_rows (select ...into 语句匹配多个行)
zero_divide (被0除)
value_error (算数或转换错误)
timeout_on_resource (在等待资源时发生超时)
自定义例外
案例如下
-- no_data_found异常
declare
dname emp.ename%TYPE;
begin
select ename into dname from emp where empno=&inputno ;
dbms_output.put_line('这个人是:'||dname);
exception
when no_data_found then
dbms_output.put_line('没有找到这个人');
when others then
dbms_output.put_line('其他异常');
end ;
--too_many_rows异常
declare
dname emp.ename%TYPE;
begin
select ename into dname from emp where deptno=20 ;
dbms_output.put_line(dname);
exception
when too_many_rows then
dbms_output.put_line('查询到多行数据');
when others then
dbms_output.put_line('其他异常');
end;
--zero_divide 除数为0
declare
num number ;
begin
num:=2/0 ;
exception
when zero_divide then
dbms_output.put_line('0不能做除数');
when others then dbms_output.put_line('其他异常');
end ;
---value_error 转换异常
declare
num number;
begin
num:='abc';
dbms_output.put_line(num);
exception
when value_error then
dbms_output.put_line('转换异常');
when others then
dbms_output.put_line('其他异常');
end ;
--需求:查询50号部门的员工名字
declare
dname emp.ename%TYPE ;
no_data exception ;
cursor res is select ename from emp where deptno=50;
begin
open res ;
loop
fetch res into dname ;
exit when res%notfound ;
end loop ;
if res%notfound then
raise no_data ;
end if ;
exception
when no_data then
dbms_output.put_line('无查询数据');
close res ;
end ;
案例
---统计每年入职的员工人数
declare
cursor res is select to_char(hiredate,'yyyy') ,count(*) from emp group by to_char(hiredate,'yyyy');
hiredateyear varchar2(20);
cishu number(20);
alls number(20):=0;
begin
open res ;
loop
fetch res into hiredateyear ,cishu ;
exit when res%notfound;
dbms_output.put_line('入职日期:'||hiredateyear||'次数'||cishu);
alls:=alls+cishu;
end loop ;
dbms_output.put_line('总次数='||alls);
close res ;
end ;
---为员工涨工资。从最低工资涨起每人涨10%，但工资总额不能超过5万元，请计算涨工资的人数和
---涨工资后的工资总额，并输出。
declare
cursor res is select empno, sal from emp order by sal;
allmoney number:=0 ;
currallmoney number ;
eno emp.empno%TYPE ;
esal emp.sal%TYPE ;
times number:= 0 ;
begin
open res ;
loop
fetch res into eno,esal ;
update emp set sal=sal+sal*0.1 where empno =eno ;
times:=times+1 ;
exit when currallmoney>50000 ;
exit when res%notfound;
select sum(sal) into currallmoney from emp ;
end loop ;
dbms_output.put_line('钱:'||currallmoney);
dbms_output.put_line('次数:'||times);
close res ;
end ;
---分别统计 <3000 3000-6000 >6000各个部门的人数 和总金额
declare
ccount_3000 number ;
ccount_6000 number ;
ccount_7000 number ;
total_10_sal emp.sal%TYPE;
pno number;
cursor res is select distinct(deptno) from emp ;
begin
open res ;
loop
fetch res into pno ;
exit when res%notfound ;
select nvl(sum(count(*)),0) into ccount_3000 from emp e inner join dept d on e.deptno=d.deptno where d.deptno =pno and e.sal<3000 group by d.deptno;
select nvl(sum(count(*)),0) into ccount_6000 from emp e inner join dept d on e.deptno=d.deptno where d.deptno =pno and e.sal>3000 and e.sal<6000 group by d.deptno;
select nvl(sum(count(*)),0) into ccount_7000 from emp e inner join dept d on e.deptno=d.deptno where d.deptno =pno and e.sal>6000 group by d.deptno ;
select sum(e.sal) into total_10_sal from emp e inner join dept d on e.deptno=d.deptno where d.deptno =pno group by d.deptno ;
insert into msg values (pno,ccount_3000,ccount_6000,ccount_7000,total_10_sal);
end loop ;
close res ;
end ;
---按系别分段统计 成绩 60 60--85 85以上 大学物理的各分段 学生成绩人数
declare
countnum60 number ;
countnum68 number ;
countnumhigh80 number ;
xiname dep.dname%TYPE;
lession course.cname%TYPE:='大学物理';
avgauto number ;
cursor res is select distinct(dname) from dep;
begin
open res ;
loop
fetch res into xiname ;
select nvl(sum(count(*)),0) into countnum60 from student s inner join dep d on s.dno=d.dno inner join sc on sc.sno=s.sno
inner join course c on c.cno=sc.cno where c.cname='大学物理' and sc.grade<=60 and d.dname=xiname group by d.dname ;
select nvl(sum(count(*)),0) into countnum68 from student s inner join dep d on s.dno=d.dno inner join sc on sc.sno=s.sno
inner join course c on c.cno=sc.cno where c.cname='大学物理' and sc.grade>60 and sc.grade<85 and d.dname=xiname group by d.dname ;
select nvl(sum(count(*)),0) into countnumhigh80 from student s inner join dep d on s.dno=d.dno inner join sc on sc.sno=s.sno
inner join course c on c.cno=sc.cno where c.cname='大学物理' and sc.grade>85 and d.dname=xiname group by d.dname ;
select avg(sc.grade) into avgauto from student s inner join dep d on s.dno=d.dno inner join sc on sc.sno=s.sno
inner join course c on c.cno=sc.cno where c.cname='大学物理' and d.dname=xiname ;
exit when res%notfound ;
insert into msg2 values(lession,xiname,countnum60,countnum68,countnumhigh80,avgauto);
end loop ;
close res ;
end ;
81
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
