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