pl/sql编程-01
总体介绍
–pl/sql编程语言
- pl/sql编程语言是对sql语言的扩展,使得sql语言具有过程化变编程的特性。
- pl/sql编程语言比一般的过程化编程语言,更加灵活高效。
- pl/sql编程语言主要用来编写存储过程和存储函数等。
–声明方法
–赋值操作可以使用=: 也可以使用into查询语句赋值。
示例:
declare
i number(2) := 10 ;
s varchar2(10) := '哈dd哈' ;
ena XXGX_TJ_RBBTJ.SYS_TIME%TYPE ; -- 引用型变量
rowtest XXGX_TJ_RBBTJ%ROWTYPE; -- 记录型变量
begin
dbms_output.put_line(i);
dbms_output.put_line(s);
select SYS_TIME into ena from XXGX_TJ_RBBTJ where ID = 1000060;
dbms_output.put_line(ena);
select * into rowtest from XXGX_TJ_RBBTJ where ID = 1000060;
dbms_output.put_line('系统时间:'||rowtest.sys_time || ' ID: ' || rowtest.id);
end;
注意:
- plsql编程的习惯,先把主题部分完整写出来,以防后期遗漏。(如declare begin end; 有 loop 后边必加end loop)
- 引用型变量 表明.字段名%TYPE, 记录型变量 表明%ROWTYPE;
- oracle中的连接字符串用 “||” (双竖线)。
1. pl/sql中的if判断
— 输入小于18的数字,输出未成年
— 输入大于18小于40的数字,输出中年人
— 输入大于40的数字,输出老年人
declare
i number(3) := ⅈ
begin
if i<18 then
dbms_output.put_line('未成年');
elsif i<40 then
dbms_output.put_line('中年人');
else
dbms_output.put_line('老年人');
end if;
end;
注:
- 接收键盘输入值 用 " &ii "。
- 其中if后跟的是 elsif . 不要多加个e 写成了 elseif 。
2. pl/sql 中的loop循环
三种方式输出1到10
1) . while循环
declare
i number(2) := 1;
begin
while i<11
loop
dbms_output.put_line(i);
i:= i+1;
end loop;
end;
注意: plsql 里边没有像java中的 i++,要用i:=i+1 来代替 。
2) . exit when循环(常用)
declare
i number(2) := 1;
begin
loop
exit when i>10;
dbms_output.put_line(i);
i:=i+1;
end loop;
end;
3) . for 循环
declare
begin
for i in 1..10
loop
dbms_output.put_line(i);
end loop;
end;
注: 1…10 表示1到10之间
3. plsql 中的游标
其实这个名字挺起了还挺唬人了,其实也就是个取值遍历(迭代)而已。
–游标: 可以存放多个对象,多行记录
–输出emp表中所有员工的姓名
declare
cursor c1 is select * from emp;
emprow emp%rowtype;
begin
open c1;
loop
fetch c1 into emprow;
exit when c1%notfound;
dbms_output.put_line(emprow.ename);
end loop;
close c1;
end;
注: 这里需要记住的就是plsqo中编程的时候,一定要把结构写完整。比如 declare begin end; 写了open c1,后边就跟上close c1; 写了loop 后边就跟 end loop;
就像java里边的中括号{} 一样。 plsql中没有中括号。 loop 和 end loop 就类似于中括号一样。
还有一个就是注意一下游标的定义格式。
– 给指定部门员工涨工资
declare
cursor c2(eno emp.deptno%type)
is select empno from emp where deptno = eno;
en emp.empno%type;
begin
open c2(10);
loop
fetch c2 into en;
exit when c2%notfound;
update emp set sal=sal+100 where empno=en;
commit; --这里注意要提交。
end loop;
close c2;
end;
注: 这里要注意的就是在 增删改之后要记得加上 commit; 提交以下。
4. plsql中的存储过程
- 存储过程: 存储过程就是提前已经编译好的一段pl/sql语言,放置在数据库端
- 为了完成特定功能的sql语句集,是数据库的一个重要对象
- 可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
create or replace procedure p1(eno emp.empno%type)
is
begin
update emp set sal = sal + 100 where empno = eno;
commit;
end;
注意: 参数类型后边是不能带长度的。比如 number(10),这样写会报错。上述示例是直接引用了。
测试存储过程 p1
select * from emp where empno = 7788; // 比对前后结果
调用存储过程
declare
begin
p1(7);
end;
5.存储函数
格式:
create or replace function 函数名(Name in type,Name in type,...) return 数据类型 is
结果变量 数据类型;
begin
return(结果变量);
end 函数名;
注意:
- 存储过程和存储函数的函数的 参数 都不能带长度
- 存储函数的返回值类型不能带长度
示例: 通过存储函数实现计算指定员工的年薪
create or replace function f_yearsal(eno emp.empno%type) return number
is
s number(10);
begin
select sal*12+nvl(comm,0) into s from emp where empno = eno;
return s;
end;
测试f_yearsal
declare
s number(10);
begin
s := f_yearsal(7788);
dbms_output.put_line(s);
end;
注意: 存储函数在调用的时候,返回值需要接收,否则会报错。
6. 使用out类型参数
create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
is
s number(10);
c emp.comm%type;
begin
select sal*12,nvl(comm,0) into s ,c from emp where empno = eno;
yearsal := s+c;
end;
测试p_yearsal
declare
yearsal number(10);
begin
p_yearsal(7788,yearsal);
dbms_output.put_line(yearsal);
end;
- in 和out 类型的参数的区别是什么?
凡是涉及到into 查询语句赋值或者 := 赋值操作的参数,都必须使用out来修饰。
7. 存储过程和存储函数的区别
一般来讲,过程和函数的区别在于函数可以有一个返回值,而过程没有返回值。
但过程和函数都可以通过out指定一个或者多个输出参数。我们可以利用out参数,在过程和函数中实现返回多个值。但是在调用的是会增加繁琐度
- 语法区别: 关键字不一样 procedure function
–存储函数比存储过程多了两个return。 - 本质区别: 存储函数有返回值,而存储过程没有返回值。
--------------如果存储过程想实现有返回的业务,我们就必须借助out 参数来实现
--------------即使是存储过程使用了out类型的参数,本质上也不是真的有了返回值。
--------------而是在存储过程内部给out类型参数赋值,在执行完毕后,直接拿到输出类型参数的值。
--------------可以使用存储函数又返回值的特性,来自定义函数。而存储过程不能用来自定义函数。
8. 用存储函数来自定义函数
查询出员工姓名,员工所在的部门名称的名称。
–准备: 将scott用户下的dept表复制到当前用户下。
create table dept as select * from scott.dept;
- 使用传统方式来实现需求
select e.ename, d.dname from emp e, dept d where e.deptno=d.deptno;
- 使用存储函数来实现提供一个部门编号,输出一个部门名称。
create or replace function fdna(dno dept.deptno%type) return dept.dname
is
dna dept.dname%type;
begin
select dname into dna from dept where deptno = dno;
return dna;
end;
使用fdna存储函数来实现需求: 查询出员工姓名,员工所在部门名称。
select e.ename, fdna(e.deptno) from emp e;
over!