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

简介: 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:=#
   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:=#
   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:= #
   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

目录
相关文章
|
30天前
|
存储 SQL Oracle
Oracle系列十五:存储过程
Oracle系列十五:存储过程
|
1月前
|
Oracle 关系型数据库 数据库
|
1月前
|
SQL 关系型数据库 MySQL
【MySQL 数据库】2、MySQL 的数据控制语言、函数和约束
【MySQL 数据库】2、MySQL 的数据控制语言、函数和约束
28 0
|
1月前
|
关系型数据库 分布式数据库 数据库
PolarDB PostgreSQL版:Oracle兼容的高性能数据库
PolarDB PostgreSQL版是一款高性能的数据库,具有与Oracle兼容的特性。它采用了分布式架构,可以轻松处理大量的数据,同时还支持多种数据类型和函数,具有高可用性和可扩展性。它还提供了丰富的管理工具和性能优化功能,为企业提供了可靠的数据存储和处理解决方案。PolarDB PostgreSQL版在数据库领域具有很高的竞争力,可以满足各种企业的需求。
|
19天前
|
Oracle 关系型数据库 数据库
Oracle数据库基本概念理解(3)
Oracle数据库基本概念理解(3)
15 2
|
1月前
|
Oracle 关系型数据库 数据库
如何利用 Docker 安装 Oracle 数据库
【2月更文挑战第14天】
81 0
|
19天前
|
Oracle 关系型数据库 数据库
Oracle数据库基本概念理解(2)
Oracle数据库基本概念理解(2)
13 1
|
2天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
【Oracle】玩转Oracle数据库(七):RMAN恢复管理器
23 4
|
2天前
|
存储 SQL Oracle
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
【Oracle】玩转Oracle数据库(二):体系结构、存储结构与各类参数
30 7
|
2天前
|
SQL Oracle 关系型数据库
【Oracle】玩转Oracle数据库(一):装上去,飞起来!
【Oracle】玩转Oracle数据库(一):装上去,飞起来!
34 7

推荐镜像

更多