PLSQL语法及存储过程
pl/sql 的程序结构
declare
说明部分（变量说明，光标申明 ， 例外说明 ）
begin
语句序列 （DML语句）
exception
例外处理语句
end;
/
说明部分
1,定义基本变量
类型包括： char，varchar2，date ，number ， boolean ， long
* 举例：
var1char(15);
定义了一个字符，长度是15
marriedboolean : = true ;
布尔值定义 需要 ：=赋值
psalnumber(7,2);
定义了一个数字，且有两位小数
2，引用型变量关于plsql工具的使用：--打印一些简单变量并对日期进行简单加减
declare
-- Local variables here
pnumber number(7,2);
--字符串变量
pname varchar2(20);
--日期
pdate date;
begin
-- Test statements here
pnumber :=1;
pname:= 'Tom';
pdate := sysdate;
dbms_output.put_line(pname);
dbms_output.put_line(pnumber);
dbms_output.put_line(pdate+2);
end;
https://www.cnblogs.com/luzhanshi/p/11017780.html例子1： 循环打印出表中的数据项（id和name）
--认识游标（光标/结果集）
-- cursor光标名[( 参数名 ， 参数类型[,参数名，参数类型])]isselect（查询语句）
/**
光标的属性：
1，%found取到值为true%notfound取到值为fale
**/
declare
--定义一个光标
cursor cstaffis select fname,fid from t_human_staff;
--为光标定义一个变量来取值用这里引用表中的类型，通过%来引用
sta_name t_human_staff.fname%type;
sta_id t_human_staff.fid%type;
begin
--打开光标
open cstaff;
--循环loop
LOOP
--取一条记录放到定义好的变量中
fetch cstaff into sta_name,sta_id;
--思考 1，循环退出条件 2， fetch 不一定取出数据when循环是在条件值到达某个值时跳出，下面的例子是大于200时跳出
exit when sta_id > 200;
dbms_output.put_line(sta_name||'的id是 ：'||sta_id);
--结束循环
end LOOP;
--关闭光标
close cstaff;
end;
例子2 : 布尔值打印
-- Created on 2020/9/11 by ADMINISTRATOR
declare
f boolean :=true;
sh number :=1000;
begin
sh := sys.diutil.bool_to_int(f);
dbms_output.put_line(sh);
end;
例子3：结合例子1，打印出游标的布尔属性
declare
--定义一个光标
cursor cstaffis select fname,fid from t_human_staff;
--为光标定义一个变量来取值用这里引用表中的类型，通过%来引用
sta_name t_human_staff.fname%type;
sta_id t_human_staff.fid%type;
flag boolean;
i number:=111;
begin
--打开光标
open cstaff;
--循环loop
LOOP
--取一条记录放到定义好的变量中
fetch cstaff into sta_name,sta_id;
flag := cstaff%found;
exit when sta_id > 200;
--对布尔值打印进行处理后
i := sys.diutil.bool_to_int(flag);
dbms_output.put_line('的flag是 ：'||i);
--结束循环
end LOOP;
--关闭光标
close cstaff;
end;
对于查询数据的时候有可能会造成overflow的异常
文章图片
image.png 因为dbms_output 的默认值是20000
处理方式1：PLSQL 更新符合某个条件的数据
文章图片
image.png
处理方式2：
文章图片
image.png
追加一条命令：
DBMS_OUTPUT.ENABLE (buffer_size=>null);
表示不限制数据大小
/***/
declare
--定义一个光标
cursor cstaffis select fname,fid from t_human_staff;
--为光标定义一个变量来取值用这里引用表中的类型，通过%来引用
sta_name t_human_staff.fname%type;
sta_id t_human_staff.fid%type;
flag boolean;
i number:=111;
begin
--打开光标
open cstaff;
--循环loop
LOOP
--取一条记录放到定义好的变量中
fetch cstaff into sta_name,sta_id;
flag := cstaff%found;
exit when sta_id > 200;
if sta_name = '111' then
dbms_output.put_line('修改数据行是 ：'||sta_id);
update t_human_staff set fname = '赵爱民' where fid = sta_id;
else dbms_output.put_line('正常数据：'||sta_name);
end if;
--DBMS_OUTPUT.ENABLE (buffer_size=>null);
--结束循环
end LOOP;
--关闭光标
close cstaff;
commit;
end;
针对游标的属性笔记
--光标属性笔记
declare
--定义一个游标接收select查询语句查出的结果集，并定义两个变量来在plsql中逻辑处理
cursor ccis select fname,fid from t_human_staff;
sta_name t_human_staff.fname%type;
sta_id t_human_staff.fid%type;
begin
--打开光标
open cc;
if cc%isopen then dbms_output.put_line('游标是开启的');
elsif not cc%isopen then dbms_output.put_line('游标是关闭的');
end if;
--关闭游标
close cc;
if cc%isopen then dbms_output.put_line('游标是开启1的');
elsif not cc%isopen then dbms_output.put_line('游标是关闭1的');
end if;
end;
%ROWCOUNT
这个属性记录了游标取过记录的行数，也可以理解为当前游标所在的行数。这个属性在循环的时候十分有效.
无论是显式游标还是隐式游标都会有四个属性分别是：%ISOPEN、%FOUND、%NOTFOUND、%ROWCOUNT 下
【PLSQL语法及存储过程】默认情况下，oracle数据库只允许在一个会话中，打开300个游标
文章图片
image.png 异常处理
--常见异常处理
declare
--定义一个带参数的光标
cursor cc(cno number)is select fname ,fid from t_human_staff where fid=cno;
sta_name t_human_staff.fname%type;
sta_id t_human_staff.fid%type;
num number;
--自定义异常
no_data exception;
begin
--数据库中没有的数据查询并绑定到定义的属性中
--select fname into sta_name from t_human_staff where fe
--多数据匹配异常
--select fname into sta_name from t_human_staff t where t.fdepartmentid = 234;
--被0除异常
--num := 2/0;
--系统例外
--num := 'abc';
--自定义异常
--打开游标
open cc(123123);
fetch cc into sta_name,sta_id;
if cc%notfound then
--抛出异常
raise no_data;
end if;
--关闭游标
close cc;
exception
--未找到结果异常no_data_found
when no_data_found thendbms_output.put_line('没找到数据');
--多数据匹配异常too_many_rows
when too_many_rows thendbms_output.put_line(' 多数据匹配异常');
--被0除异常zero_divide
when zero_divide thendbms_output.put_line('被0除异常');
--系统异常：数据转换
when value_error thendbms_output.put_line('数据类型转换异常');
--自定义异常
when no_data thendbms_output.put_line('自定义异常：无结果');
when others then dbms_output.put_line('其他异常');
--系统遇到异常后跳到exception语句后而close 游标的指令却没有执行，这个时候oracle会自动启动pemon（process monitor）进程关闭游标，清理垃圾
end;
针对循环语句 wheil和when 小例子：
declare
n number:=0;
nn number:=0;
begin
while n<=5 loop
n:=n+1;
dbms_output.put_line('n='||n);
end loop;
dbms_output.put_line('---------');
loop
exit when nn>5;
nn :=nn+1;
dbms_output.put_line('nn='||nn);
end loop;
end;
/**
loop循环： while条件为false跳出循环true继续loop循环
when条件为true跳出循环false继续循环
*/
存储过程
-- 用createprocedure创建存储过程和存储函数、
/*
创建或者替换一个存储过程
create [or replace]procedure过程名（参数列表）asPLSQL子程序体；
as相当于plsql中的 declare*/create or replace proceduresayhello
--说明部分
as
begin
dbms_output.put_line('hello plsqls');
end;
/*
执行后就创建成功了，可以再plsql工具中的Procedures文件夹下找到
*//*
调用存储过程两种方式：
1,execute(缩写为： exec)sayhello();
进行调用，格式为：exec函数名 ；--注意分号结束
2,在另外的plsql中调用
begin
--调用存储过程
sayhello();
sayhello();
sayhello();
end;
*/
存储函数
--查询某个员工的年收入
create or replace function cxempsals(eno in number)
return number
as
--月薪和奖金
psalemp.sal%type;
pcommemp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
--返回年收入
return psal*12+nvl(pcomm,0);
end;
/*
存储过程和函数中输入和输出参数in 和 out
一般来讲，存储过程：无返回值
存储函数：有返回值
二者都可以通过out 指定返回一个或者多个值
存储过程本身不可以有返回值，但有了out之后就可以有返回值
*/
java调用 存储过程
package com.ysu.drgs.proxy.stafftest;
import java.sql.*;
import org.junit.Test;
import oracle.jdbc.driver.OracleTypes;
import oracle.jdbc.oracore.OracleType;
public class OracleTestProcedure {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
private static String username ="";
private static String pwd ="";
/**
* 获取数据库驱动
*/
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}/**
* 获取数据库连接
* @return
*/
public static Connection getConn (){try {
return DriverManager.getConnection(url, username, pwd);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}/**
* 释放资源
*/
public static void closeAll(Connection conn , Statement st ,ResultSet rs ){
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs=null;
}
}
if(st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
st=null;
}
}
if(conn!= null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
conn=null;
}
}
}
/**
* java调用存储过程
* @param args
*/@Test
publicvoid test() {
//Connection conn = getConn();
//System.out.println(conn == null);
Connection conn= null;
CallableStatement call = null;
//访问存储过程
try {
//? 占位符{call 存储过程的名字(?,?,?,?)}
String sql = "{call cxempinfo(?,?,?,?)}";
conn = getConn();
call= conn.prepareCall(sql);
//对in赋值
call.setInt(1, 7839);
//out输出参数需要声明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
//调用存储过程
call.execute();
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name);
System.out.println(sal);
System.out.println(job);
} catch (Exception e) {
e.printStackTrace();
}finally{
closeAll(conn, call, null);
}
}}推荐阅读
- JS中的各种宽高度定义及其应用
- 参保人员因患病来不及到指定的医疗机构就医，能否报销医疗费用（）
- MybatisPlus|MybatisPlus LambdaQueryWrapper使用int默认值的坑及解决
- 标签、语法规范、内联框架、超链接、CSS的编写位置、CSS语法、开发工具、块和内联、常用选择器、后代元素选择器、伪类、伪元素。
- 六步搭建ES6语法环境
- 【Hadoop踩雷】Mac下安装Hadoop3以及Java版本问题
- 经历了人生，才知道人生的艰难！及精彩！
- 罗塞塔石碑的意义（古埃及文字的起源，圣书体文字是如何被破解的）
- 以太坊中的计量单位及相互转换
- Spark|Spark 数据倾斜及其解决方案