JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数（一）-阿里云开发者社区

day03_Oracle

一、课程目标

PLSQL语法（掌握）
存储函数（了解）
存储过程（掌握）
JAVA来调用oracle函数或过程(掌握)

二、PL/SQL基础语法

2.1 什么是PL/SQL

PL/SQL（Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。

2.2 基本语法

[declare 
  --声明变量
]
begin
  --代码逻辑 
[exception
    --异常处理
 ]
end;

2.3 变量

声明变量和赋值

变量名 类型（长度）;  
-- 1.基本类型
变量名:=变量值

--  1+1=2的案例
declare 
   a int:=1;
   b int:=1;
   c number;
begin  
  c:=a+b;
   dbms_output.put_line(c);   
end;

Select into 方式赋值

select 列名 into 变量名 from 表名 where 条件

注意：结果必须是一条记录(不是一个值) ，有多条记录和没有记录都会报错

declare 
   num_sal number;
   var_name varchar2(20);
   begin
            select ename,sal into var_name,num_sal from emp where empno=7369;
            dbms_output.put_line(var_name||'工资是：'||num_sal);
   end;

引用变量
优点：1.用户不必查看表中各列的数据类型，即可查询到所需数据；
2.对表中已经有的数据类型进行修改，不必考虑已定义的数据类型，和表中的一致；
2.%Type类型
– 变量表名.列名%type
使用%Type关键字可以声明一个与指定名称相同的数据类型，它通常跟在指定列名的后面。

var_job emp.job%type – 表示var_job和emp表下的job是同一个类型；

参考案例(同上做比较）：
```plsql
select * from emp
declare
   var_job emp.job%type;
   var_name emp.ename%type;
begin
  select ename,job into var_name,var_job from emp where empno=7369;
  dbms_output.put_line(var_name||'职务：'||var_job);
end;

3.%rowtype类型

记录类型，用来存储从数据表查询到的一行的数据

语法：表变量名表名%rowtype;

declare 
   varEmp  emp%rowtype;
   begin
     select * into varEmp from emp where empno= 7839;
     dbms_output.put_line('员工'||varEmp.empno||'姓名'||varEmp.ename);
 end;

从运行结果可以看到，变量varEmp和emp表的结构完全相同。

2.4 异常

在运行程序时出现的错误叫做异常发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分

异常有两种类型：

预定义异常

当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

用户定义异常

用户可以在 PL/SQL 块的声明部分定义异常，自定义的

异常通过 RAISE 语句显式引发

2.4.1 预定义异常

Oracle 预定义异常 21 个

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e1H8eidn-1666020355057)(assets/image-20210326085850685.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h24a1ePX-1666020355059)(assets/image-20210326085917379.png)]

2.4.2 语法结构

exception
  when 异常类型 then
       异常处理逻辑

案例

declare
       primary_iter exception;  -- 定义一个自定义异常
       pragma exception_init(primary_iter,-00001);
       begin
         insert into emp(empno,ename)values(7369,'sss');
       exception
         when primary_iter then
              dbms_output.put_line('主键不能重复');
       end;

2.5 条件判断

2.5.2 单分支

if 条件 then
  业务逻辑
end if;

declare
   vnum number:=&num;
   begin
     if vnum=1 then 
        dbms_output.put_line('成功登录');
     end if;
   end;

2.5.2 if else

if 条件 then
 业务逻辑
else
 业务逻辑
end if;

declare
   vnum number:=&num;
   begin
        if vnum=1 then
           dbms_output.put_line('登录成功');
        else
          dbms_output.put_line('登录失败');
        end if;
     end;

2.5.3 多分支

if 条件 then
  业务逻辑
elsif 条件 then  -- 注意：这个长相：是els+if
  业务逻辑
else
  业务逻辑 
end if;

declare
   age number:= &num;
   begin
     if age<18 then 
       dbms_output.put_line('未成年人');
     elsif age>=18 and age<60 then 
       dbms_output.put_line('年轻人');
     elsif age>=60 and age<80 then
       dbms_output.put_line('中年人');
     elsif age>=80 then
       dbms_output.put_line('老人');
     end if;       
   end;

2.5.4 Case语句

Oracle 9i之后增加的语句，笔记二未体现。

declare
   age integer:=&num;
   vresult varchar2(100); -- 别起result关键字；
   begin
     case 
       when age<18 then
         vresult:='未成年人';
       when age>=18 and age<60 then
         vresult:='年轻人';
       when age>=60 and age<80 then
         vresult:='中年人';
       when age>=80 then
         vresult:='老年人';
     end case;
     dbms_output.put_line(vresult);
   end;

2.6 循环

2.6.1 loop when循环

语法

loop   
 --循环语句   类似于之前的do while循环
   exit when 退出条件;
end loop;

演示

输出从1开始的100个数

declare
  i number:=1;
begin 
 loop
   dbms_output.put_line(i);
     i:=i+1;
     exit when i>100;
 end loop; 
end ;

2.6.2 when loop循环

语法

while 循环条件  
loop
  循环体
end loop;

演示

输出从1开始的100个数

declare
  i number:=1;
begin
 while i<=100 loop
     dbms_output.put_line(i);
     i:=i+1; 
 end loop; 
end ;

2.6.3 for … loop循环

语法

-- for(int i=1;i<结束值;i++){}
for 变量 in 起始值..终止值 loop
    -- 循环语句
end loop;

演示

输出从1开始的100个数

begin
 for i in 1..100
 loop
  dbms_output.put_line(i); 
 end loop;
end;

向表中插入数据；

insert into student(id,name)values(seq_test.nextval,'李四')
-- 向student表中插入1000条数据；使用while loop循环？？？？
declare
   i number :=1;
begin
  while i<=1000 loop -- 当大于2755的时候，控制台会报错；插入没问题；
      -- dbms_output.put_line(i); 在sql控制台输出
      insert into student(id,name)values(seq_test.nextval,'李四'||i);
      commit;
      i:=i+1;
  end loop;    
end;

三、存储函数

3.1 什么是存储函数

存储函数又称为自定义函数,一种存储在数据库中的命令程序块。可以接收零或多个参数，必须有返回值。

3.2 存储函数语法

创建或修改存储过程的语法如下:

CREATE [ OR REPLACE ] FUNCTION 函数名称(参数名称 参数类型, 参数名称 参数类型,...n)
  RETURN 结果变量数据类型
IS
   变量声明部分;
BEGIN
 -- 逻辑部分;
    RETURN  结果变量;
[EXCEPTION 异常处理部分]
END;

示例

需求：根两个数相加函数

create or replace function f_sum(a number,b number) return number
is fresult number;  -- 这个是变量，fresult这个是需要return
begin
       fresult:=a+b;
       return fresult;
  end;

调用存储函数

select f_sum(3,2) from dual

实用函数：

Create Or Replace Function Money2Chinese(Money In Number) Return Varchar2 Is
  strYuan       Varchar2(150);
  strYuanFen    Varchar2(152);
  numLenYuan    Number;
  numLenYuanFen Number;
  strRstYuan    Varchar2(600);
  strRstFen     Varchar2(200);
  strRst        Varchar2(800);
  Type typeTabMapping Is Table Of Varchar2(8) Index By Binary_Integer;
  tabNumMapping  typeTabMapping;
  tabUnitMapping typeTabMapping;
  numUnitIndex   Number;
  i              Number;
  j              Number;
  charCurrentNum Char(1);
Begin
  If Money Is Null Then
    Return Null;
  End If;
  strYuan := TO_CHAR(FLOOR(Money));
  If strYuan = '0' Then
    numLenYuan := 0;
    strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0');
  Else
    numLenYuan := length(strYuan);
    strYuanFen := TO_CHAR(FLOOR(Money * 100));
  End If;
  If strYuanFen = '0' Then
    numLenYuanFen := 0;
  Else
    numLenYuanFen := length(strYuanFen);
  End If;
  If numLenYuan = 0 Or numLenYuanFen = 0 Then
    strRst := '零圆整';
    Return strRst;
  End If;
  tabNumMapping(0) := '零';
  tabNumMapping(1) := '壹';
  tabNumMapping(2) := '贰';
  tabNumMapping(3) := '叁';
  tabNumMapping(4) := '肆';
  tabNumMapping(5) := '伍';
  tabNumMapping(6) := '陆';
  tabNumMapping(7) := '柒';
  tabNumMapping(8) := '捌';
  tabNumMapping(9) := '玖';
  tabUnitMapping(-2) := '分';
  tabUnitMapping(-1) := '角';
  tabUnitMapping(1) := '';
  tabUnitMapping(2) := '拾';
  tabUnitMapping(3) := '佰';
  tabUnitMapping(4) := '仟';
  tabUnitMapping(5) := '万';
  tabUnitMapping(6) := '拾';
  tabUnitMapping(7) := '佰';
  tabUnitMapping(8) := '仟';
  tabUnitMapping(9) := '亿';
  For i In 1 .. numLenYuan Loop
    j            := numLenYuan - i + 1;
    numUnitIndex := Mod(i, 8);
    If numUnitIndex = 0 Then
      numUnitIndex := 8;
    End If;
    If numUnitIndex = 1 And i > 1 Then
      strRstYuan := tabUnitMapping(9) || strRstYuan;
    End If;
    charCurrentNum := substr(strYuan, j, 1);
    If charCurrentNum <> 0 Then
      strRstYuan := tabNumMapping(charCurrentNum) ||
                    tabUnitMapping(numUnitIndex) || strRstYuan;
    Else
      If (i = 1 Or i = 5) Then
        If substr(strYuan, j - 3, 4) <> '0000' Then
          strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
        End If;
      Else
        If substr(strYuan, j + 1, 1) <> '0' Then
          strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;
        End If;
      End If;
    End If;
  End Loop;
  For i In -2 .. -1 Loop
    j              := numLenYuan - i;
    charCurrentNum := substr(strYuanFen, j, 1);
    If charCurrentNum <> '0' Then
      strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||
                   strRstFen;
    End If;
  End Loop;
  If strRstYuan Is Not Null Then
    strRstYuan := strRstYuan || '圆';
  End If;
  If strRstFen Is Null Then
    strRstYuan := strRstYuan || '整';
  Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '角' Then
    strRstFen := strRstFen || '整';
  End If;
  strRst := strRstYuan || strRstFen;
  --strRst := Replace(strRst, '亿零', '亿');
  --strRst := Replace(strRst, '万零', '万');
  Return strRst;
End Money2Chinese;
-- 调用--
Select Money2Chinese(786.213) From dual;

JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数（二）https://developer.aliyun.com/article/1432878

JAVAEE框架数据库技术之13_oracle 之PLSQL技术及存储过程和函数（一）

day03_Oracle

一、课程目标

二、PL/SQL基础语法

2.1 什么是PL/SQL

2.2 基本语法

2.3 变量

2.4 异常

2.4.1 预定义异常

2.4.2 语法结构

2.5 条件判断

2.5.2 单分支

2.5.2 if else

2.5.3 多分支

2.5.4 Case语句

2.6 循环

2.6.1 loop when循环

2.6.2 when loop循环

2.6.3 for … loop循环

三、存储函数

3.1 什么是存储函数

3.2 存储函数语法

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像