以下为个人学习笔记
plsql语言不区分大小写。
1.程序结构:
Plsql分为三个部分:声明部分、可执行部分、异常处理部分 。语法:
declare
--声明变量,游标
i integer;
begin
--执行语句
--异常处理
end;
示例:打印hello world(在测试窗口编写)
begin
-- 打印hello world
DBMS_OUTPUT.PUT_LINE('hello world');
end;
(在命令窗口编写)“set serveroutput on“开启输出命令
Sqlplus中执行plsql程序需要在程序最后添加/,表示程序的结束。
SQL> set serveroutput on
SQL> begin
-- 打印hello world
DBMS_OUTPUT.PUT_LINE('hello world');
end;
/
2.变量
(1)普通数据类型(char,varchar2,date,number,boolean,long)
(2)特殊变量类型(引用型变量、记录型变量)
声明变量方式:
变量名 变量类型(变量长度) 例如:v_name varchar2(20);
变量赋值:
(1)直接赋值(变量 :=’值’)
V_name :=’zhangsan’
(2)语句赋值 (select 值 into 变量)
普通变量:
--打印人员个人信息,包括:姓名、薪水、地址
declare
-- 姓名
v_name varchar2(20) := '张三';--声明变量时直接赋值
--薪水
v_sal number;
--地址
v_addr varchar2(200);
begin
-- 在程序中直接赋值
v_sal := 10000;
-- 语句赋值
select '北京' into v_addr from dual;
-- 打印变量
dbms_output.put_line('姓名' || v_name || ',薪水’ || v_sal || ',地址' || v_addr);
end;
引用型变量:
变量的类型和长度取决于表中字段的类型和长度
语法:变量名 表名.列名%TYPE;
--查询emp表中1089号员工个人信息,包括:姓名、薪水
declare
-- 姓名
v_name emp.ename%TYPE;
--薪水
v_sal emp.sal%TYPE;
begin
-- 查询表中信息并赋值,保持字段顺序一致
select ename,sal into v_name,v_sal from emp where id=1089;
-- 打印变量
dbms_output.put_line('姓名' || v_name || ',薪水’ || v_sal);
end;
记录型变量:
接受表中的一整行记录,相当于java中的一个对象
语法:变量名 表名%ROWTYPE
--查询emp表中1089号员工个人信息,包括:姓名、薪水
declare
-- 记录型变量
v_emp emp%ROWTYPE;
--薪水
v_sal emp.sal%TYPE;
begin
-- 查询表中一行记录并赋值
select * into v_mep from emp where id=1089;
-- 打印变量
dbms_output.put_line('姓名' || v_emp.ename || ',薪水’ || v_emp.sal);
end;
3.流程控制
(1)条件分支
语法:
begin
if 条件1 then 执行1
-- 注意关键字elsif
elsif 条件2 then 执行2
else 执行3
end if;
end;
示例:
--判断emp表中记录数
declare
-- 记录型变量
v_count number;
begin
select count(1) into v_count from emp;
if v_count > 20 then
dbms_output.put_line('emp表中记录数大于20条,为:' || v_count);
elsif v_count >= 10 then
dbms_output.put_line('emp表中记录数在10到20条之间,为:' || v_count);
else
dbms_output.put_line('emp表中记录数小于10条,为:' || v_count);
end if;
end;
(2)loop循环
语法:
begin
loop
exit when 退出循环条件
end loop;
end;
示例:
--打印数字1-10
declare
-- 声明循环变量
v_num number := 1;
begin
loop
exit when v_num > 10;
dbms_output.put_line(v_num);
-- 循环变量自增
v_num := v_num + 1;
end loop;
end;
4.游标
(1)概念:用于临时存储一个查询返回的多行数据(结果集,类似于Java的jdbc连接返回的ResultSet集合),通过遍历游标,可以逐行访问处理该结果集的数据。
游标的使用方式:声明——打开——读取——关闭
(2)游标声明语法
-- 游标声明
cursor 游标名[(参数列表)] is 查询语句;
--游标打开:
open 游标名;
-- 游标的取值
fetch 游标名 into 变量列表;
-- 游标的关闭
close 游标名;
(3)游标的属性
游标的属性 | 返回值类型 | 说明 |
---|---|---|
%ROWCOUNT | 整型 | 获取fetch语句返回的数据行数 |
%FOUND | 布尔型 | 最近的fetch语句返回一行数据则为真,否则为假 |
%NOTFOUND | 布尔型 | 与%FOUND属性返回值相反,找不到元素时返回true,通常用来判断推出循环 |
%ISOPEN | 布尔型 | 游标已经打开时值为真,否则为假 |
不带参数游标应用:
--使用游标查询emp表中所有员工的姓名和工资,并将其以此打印出来
declare
-- 游标声明
cursor c_emp is select ename,sal from emp;
-- 声明变量接收游标中的数据
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
--游标打开:
open c_emp;
loop
-- 获取游标中的数据
fetch c_emp into v_ename,v_sal;
-- 退出循环条件
exit when c_emp%NOTFOUND;
dbms_output.put_line(v_ename || '-' || v_sal);
close loop;
-- 游标的关闭
close c_emp;
end;
带参数游标应用:
--使用游标查询并打印某部门的员工的姓名和工资,部门编号为运行时手动输入
declare
-- 游标声明(v_deptno是传递的部门参数)
cursor c_emp(v_deptno emp.deptno%TYPE) is select ename,sal from emp where deptno = v_deptno;
-- 声明变量接收游标中的数据
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
--游标打开(传递参数v_deptno的值)
open c_emp(10);
loop
-- 获取游标中的数据
fetch c_emp into v_ename,v_sal;
-- 退出循环条件(注意:要先fetch获取数据,然后在做判断)
exit when c_emp%NOTFOUND;
dbms_output.put_line(v_ename || '-' || v_sal);
close loop;
-- 游标的关闭
close c_emp;
end;
5.存储过程
(1)概念:之前我们编写的plsql程序可以进行表的操作、判断、循环、逻辑处理的工作,但无法重复调用。可以理解为之前的代码都编写在了main方法,是匿名程序,Java可通过封装对象和方法来解决服用问题。Plsql是将一个个plsql的业务处理过程存储起来进行复用,这些被存储起的plsql程序称之为存储过程。
(2)作用:在开发程序中,为了一个特定的业务功能,会向数据库进行多次来连接关闭(非常消耗资源),需要对数据库进行多次I/O读写,性能较低。如果把这些业务放到plsql中,在应用程序中只需要调用plsql就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率。
(3)语法(is可以换成as)
create or replace procedure 过程名称[(参数列表)] is
-- 声明变量(没有declare关键字,declare用在语句块中)
begin
end[过程名称];
无参示例:
create or replace procedure p_hello is
begin
-- 打印hello world
dbms_output.put_line('hello world');
end p_hello;
在测试窗口中调用:
begin
-- plsql调用存储过程
pl_hello;
end;
在命令窗口调用:
SQL> set serveroutput on
SQL> exec p_hello;
带输入参数例子:
-- 查询并打印某个员工(如2189号员工)的姓名和薪水
-- 存储过程:要求调用的时候传入员工编号,自动控制台打印
-- 存储过程名(入参名 in 入参类型)
create or replace procedure p_querynameAndSal(i_empno in emp.empno%TYPE) is
-- 声明变量
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
-- 查询emp表中某员工的姓名和薪水并赋值给变量
select ename,sal into v_name,v_sal from emp where empno = i_empno;
dbms_output.put_line(v_name || '-' || v_sal);
end;
-- 在测试窗口中调用
begin
-- plsql调用存储过程
pl_querynameAndSal(2189);
end;
带输出参数(返回值)的存储过程:
-- 查询并返回某个员工(如2189号员工)的薪水
-- 存储过程名(入参名 in 入参类型,输出参数名 out 输出参数类型)
create or replace procedure p_querySal_out(i_empno in emp.empno%TYPE,o_sal out emp.sal%TYPE) is
begin
select sal into o_sal from emp where empno = i_empno;
end;
-- 测试窗口调用存储过程
declare
-- 声明变量接收存储过程中的输出参数
v_sal emp.sal%TYPE;
begin
-- plsql调用存储过程
p_querySal_out(2189,v_sal);
dbms_output.put_line(v_sal);
end;
Java程序调用存储过程:
需求:如果一条语句无法实现结果集,比如需要多表查询,或者需要复杂逻辑查询,我们可以选择调用存储过程查询需要的结果。
public class ProcedureTest {
public static void main(String[] args) {
// 1.加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
// 2.创建连接
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
// 3.获得语句对象
String sql = "{call p_querySal_out(?,?)}";
CallableStatement call = conn.prepareCall(sql);
// 4.设置输入参数
call.setInt(1,2189);
// 5.注册输出参数
call.registerOutParameter(2, OracleTypes.DOUBLE);
// 6.执行存储过程
call.execute();
// 7.获取输出参数
double sal = call.getDouble(2);
System.out.println(sal);
// 8.释放资源
call.close();
conn.close();
}
}
Hibernate调用存储过程:
/**
* 直接调用存储过程
*/
public void callProcedure(String procString,List<Object> params) throws Exception {
CallableStatement stmt = null;
try {
stmt = this.getSession().connection().prepareCall(procString);
if (params != null){
int idx = 1;
for (Object obj : params) {
if (obj != null) {
stmt.setObject(idx, obj);
} else {
stmt.setNull(idx, Types.NULL);
}
idx++;
}
}
stmt.execute();
} catch (SQLException e) {
e.printStackTrace();
throw new Exception("调用存储过程的时候发生错误[sql = " + procString + "]", e);
}
或:
tx = session.beginTransaction();
Connection con=session.connection();
String procedure = "{call batchUpdateStudent(?) }";
CallableStatement cstmt = con.prepareCall(procedure);
cstmt.setInt(1,0); //把年龄参数设为0
cstmt.executeUpdate();
tx.commit();
上文提到的测试窗口和命令窗口: