关于学习oracle的PLSQL的完整笔记-mysql教程

PL/SQL 匿名块 declare--声明部分，可选的 begin--执行部分，必须的 exception--异常，可选的 end；--必须的 存储过程 create procedure 名(可以写入要接收的参数) is --声明部分 begin exception end; 通过 begin 名(参数) end;来使用存储过程 函数 create

PL/SQL
匿名块
declare --声明部分，可选的
begin --执行部分，必须的
exception --异常，可选的
end； --必须的

存储过程
create procedure 名(可以写入要接收的参数) is
--声明部分
begin
exception
end;

通过
begin
名(参数)
end;来使用存储过程

函数
create function 名(变量 in 类型) return 返回&＃20540;类型 is
定义变量
begin
select
from
return 一个要返回的&＃20540;
end；

变量和常量
如果是常量声明的时候必须在变量名后加上 constant
定义方式：变量名 常量标识符 变量的数据类型 [not null] :=(:=用于赋&＃20540;)......
另外常量的&＃20540;是不可修改的，类&＃20284;与java中的final

变量名必须少于30个字符 第一个为字母 其余可为字母数字下划线#￥符
变量前一般用v_标识 ，常量前一般用c_标识
变量名跟字段名不能相同，不能有相同的变量名

v_ename emp.ename%TYPE 表示定义的类型跟这里面的一样

存储过程和函数的参数列表里的类型不能够设置精度，例如number不能写成number(5,2)
另外，参数列表中的in out 和In out
这三种模式，In可以是直接通过
begin
名('','','')
end;使用
但是如果是out和in out模式的话必须将变量赋&＃20540;后放到括号里面做实参
declare
mmm number(5,3) :=100;
begin
名(mmm);
end; 否则会报错，因为在过程或者函数内部可能需要对这个100进行修改，所以
需要一个存储空间，所以需要变量。

在调用一个insert用途的存储过程时，有如果参数列表有三个a,b,c
而且a有一个默认的&＃20540;为123
那么这个时候我向这个函数传&＃20540;的话我就传后两个的时候要使用这种&＃26684;式
declere
mm number(5,2):=10;
nn number(5,2):=10;
begin
insert(b=>mm,c=>nn)
end；

这个时候我们没有传入a 但是他仍然默认为123&＃20540;，然后b 和 c则被赋&＃20540;为 mm和nn

drop function 函数名 ---删除函数

几道练习题

--1
create or replace procedure ADD_EMP_09(v_empNo emp1.EMPNO%type,
v_eName emp1.ENAME%type default 'UNKNOWN',
v_sal emp1.SAL%type default 2000,
v_hireDate emp1.HIREDATE%type) as
begin
insert into emp1
(empno, ENAME, SAL, HIREDATE)
values
(v_empNO, v_eName, v_sal, v_hireDate);
end;

begin
ADD_EMP_09(7988,'nnmmk',2332,sysdate);
end;

begin
ADD_EMP_09(v_empNo=>7548,v_hireDate=>sysdate);
end;

--2
create or replace function UPD_EMP_09(v_empNo in emp1.EMPNO%type,
v_sal in emp1.SAL%type)
return emp1.ENAME%type is
v_ename emp1.ENAME%type;
begin
update emp1
set sal = v_sal
where empno = v_empNo;
select ename
into v_ename
from emp1
where empno=v_empNo;
return v_ename;
end;

begin
dbms_output.put_line(UPD_EMP_09(7369,99999));
end;

--3
create or replace function GET_ENAME_09(v_empNo in emp1.EMPNO%type)
return emp1.ENAME%type is
v_ename emp1.ENAME%type;
begin
select ename
into v_ename
from emp1
where empno = v_empNo;
return v_ename;
end;
/
begin
dbms_output.put_line(GET_ENAME_09(7369));
end;

使用show erro查看错误提示。

这个练习包含了out模式的使用方法，，仔细看看

create or replace function DUTY(v_empno emp.empno%type,
v_ename out emp.ename%type)
return emp.sal%type is
v_duty emp.sal%type;
begin
select ename, sal * 0.05
into v_ename, v_duty
from emp
where empno = v_empno;
return v_duty;
end;

declare
empno emp.empno%type := 7499;
ename emp.ename%type;
begin
dbms_output.put_line(ename || ' ' || DUTY(empno,ename));
end;

异常
exception
when no_data_found then
v_empname :='no data'
when too_many_rows then
v_empname :='too many rows'

定义一种类型

type 类型名 is record(
ename varchar2(30),
job emp.job%type
);
这个可以定义在变量定义那一块 ，而且可以作为一种类型被新的变量所使用

例如: 名 类型名;就定义了这样一个变量

使用的话
.....
select ename,job
into 类型名
from emp
dbms_output.put_line(类型名.ename || 类型名.job);
.....

记住重要的一点，形参的数据类型不能有长度 例如不能写成 number(30) 只需要写number就ok了

使用%ROwTYPE

使用方法
变量声明的地方：dept_record dept%rowtype 跟 type定义的使用方法一致，
只是他包含表中的所有字段而已

SQL%ROWCOUNT 的使用方法

可以取得最近一条语句所影响的记录行数

if then
.....
elsif then
.....
else
.....
end if;

case
when v_aa = 'a' then .....;(有赋&＃20540;句子或者打印句子的时候用；结尾)
when v_bb = 'b' then .....;
else ......;
end case;

循环
loop
........
exit when
end loop;
这个跟java中的while类&＃20284;，是先执行一次

for i in 1..10 loop 递增的
......
end loop;

reverse递减的意思

for i in reverse 1..10 loop 递减的
......
end loop;

while v_num<11 loop
v_num := v_num&＃43;1;
dbms_output.put_line(v_num);
end loop;

退出循环的条件 exit when .....
或者 if...then exit

注意的两点：

整数之间的除法运算自动四舍五入。。。注意这一点。

还有几个dbms_output.put();东西连用之后一定要最后加上一句
dbms_output.put_line(' ');否则不会出结果的

使用集计函数的时候如果没有数据返回，也有可能返回一条为0的数据 ，例如使用count(*)

自定义游标

cursor 游标名 is
select
from

打开游标的方法 open 游标名，自定义的游标必须打开。
关闭游标的方法 close 游标名

fetch 游标名
into 字段，字段，字段

可以定义一个 表%rowtype

然后into 这个定义的rowtype也可以将数据放进去。

游标指针知道最后一条数据的时候就不再向下移动了，如果继续打印只会一直打印最后一条记录

游标名%isopen 判断游标是否打开。
游标名%notfound 如果没有数据的话返回true
游标名%found 如果有数据的话返回true
游标名%rowcount 返回结果集的行数

游标for循环

cursor 游标名 is
select
from
begin
for dept_c in 游标名 loop
.....
end loop;
end;
这里的dept_c如果是全表结构的话可以不定义，否则的话需要定义新的，
这个称为隐试游标，不需要自己写打开和关闭游标。

带参数的游标

cursor 游标名() is
......;
在打开游标的时候传递参数
如果是for循环的话就是在for循环中传递参数

锁定记录行：

在select的完整语句后面加上一句
for update 这样这些记录就被锁定了
只要你没有提交之前，其他的地方是不能更改的。

select
from
order by
for update
如果双方都锁定了这条记录就会抛出异常。

先查出数据，然后锁定 然后更新

update emp set sal=sal*11
where

异常处理

编译时异常
运行时异常：
--when后面可以接续两个异常，用or连接
--when others then 这个捕获其他所有异常
--一个程序块中只允许有这一个，放在最下面。
--no_data_found
--没有返回数据
--too_many_rows
--返回多行数据
--dup_val_on_index
--主键重复异常
--zero_pide
--试图除以零
--invalid_number
--将字符串转换成数字失败

自定义异常：

create or replace procedure DDDD(v_empno emp.empno%type) is
aaa emp.empno%type;
e_a exception; --定义一个异常
begin
select ename
into aaa
from emp
where empno=v_empno;

if aaa='sss' then
raise e_a; --raise是异常触发的语句
end if;
exception
when e_a then
.......;
end;

如果没有预定义的名字，只有错误代码可以使用下面的方法

pragma exception_init(自己之前定义的异常名称,错误代码);
--当发生这个错误代码的时候，转到下方when中捕获的你定义的异常名称。

sqlcode：用来取得错误代码
sqlerrm：用来取得错误信息

自定义异常的message：
--raise_app........

现在拿出begin和end的里再嵌套，如果内部的声明部分出现了异常，或者是内部异常中抛出的异常，
也就是说declare出现了异常，或者exception中通过raise抛出的异常，
他只会到外层的异常中寻求解决不会在内层中进行异常处理

包头：
create or replace package 包名 is
声明函数还是变量等等
procedure 过程名(...);
end;

在外面可以通过包名.过程名/函数名来调用

包头是公共的可以在外面使用，
包体不行。

包体：
create or repalce package body 包名 is
这里写入过程
函数
存储过程等。。
end 包名;
&＃20540;得注意的是这个包体里的声明的东西是私有的
包体中可以实现重载功能。
重载必须参数类型或者个数不同， 注意的是char和varchar是同一系列的不能当作不同的类型

drop package 包名 删除包头和包体
drop package body 包名 删除包体