关于学习oracle的PLSQL的完整笔记

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 返回值类型 is
  定义变量
begin
  select
  from
  return 一个要返回的值
end；

 

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

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

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

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

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

这个时候我们没有传入a 但是他仍然默认为123值，然后b 和 c则被赋值为 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 .....;(有赋值句子或者打印句子的时候用；结尾)
  when v_bb = 'b' then .....;
  else ......;
end case;

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

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

reverse递减的意思

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

while v_num     v_num := v_num+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_divide        
    --试图除以零
 --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 包名;
  值得注意的是这个包体里的声明的东西是私有的
  包体中可以实现重载功能。
  重载必须参数类型或者个数不同， 注意的是char和varchar是同一系列的不能当作不同的类型
  
  drop package 包名  删除包头和包体
  drop package body 包名 删除包体