1、PLSQL编程
1.1概念和目的
PL/SQL(Procedure Language/SQL)
PLSQL是Oracle对sql语言的过程化扩展
指在SQL命令语言中增加了过程处理语句(如分支、循环),使SQL语言具有过程处理能力
1.2程序结构
通过PLsqlDeveloper工具的Test Windows创建程序模版或者通过语句在SQLWindows编写
提示:PLSQL语言的大小是不区分的
PL/SQL可以分为三个部分:声明部分、可执行部分、异常处理部分
[declare] --声明变量,游标(无变量声明可以省略)
begin
--执行部分(方法)处理业务异常
end;
set serveroutput on --sqlplus 命令窗口
begin
DBMS_output.put_line(“Hello world”);
end;
/ ---需要在程序最后添加一个/标识程序的结束
1.3变量
声明变量的方式
变量名 变量类型(变量长度) --v_name varchar2(20)
1.3.1普通变量(char varchar2 date number boolean long)
(1)直接赋值语句:= --v_name=’zhangsan’
(2)语句赋值,使用select…into…赋值(语法select值into变量)
【示例】打印人员个人信息,包括:姓名、薪水、地址
declare v_name varchar2(20):=’zhangsan’; v_sal number; v_addr varchar2(200); begin v_sal:=1580; select ‘ad’ into v_addr from dual; DBMS_output.put_line(‘姓名:’|| v_name|| ’薪水:’ || v_sal ||‘地址:’||v_addr); end;
【示例】查询 emp表中7839号员工的个人信息,打印姓名和薪水
declare v_name vachar2(20); v_sal number; begin select ename,sal into v_name,v_sal from emp where empno=7839; DBMS_output.put_line(‘姓名:’|| v_name || ‘,薪水:’|| v_sal); end;
1.3.2特殊变量类型(引用型变量、记录型变量)
(1)引用型变量
【示例】查询 emp表中7839号员工的个人信息,打印姓名和薪水
declare v_name emp.ename%TYPE; v_sal emp.sal%TYPE; begin select ename,sal into v_name,v_sal from emp where empno=7839; DBMS_output.put_line(‘姓名:’|| v_name || ‘,薪水:’|| v_sal); end;
使用普通变量定义方式,需要知道表中列的类型,而是用引用类型,不需要考虑列的类型,使用%TYPE是非常好的编程风格,因为它使得PL/SQL更加灵活,更加适应于对数据库定义的更新。
(2)记录型变量
接受表中的一行记录,相当于Java中的一个对象
语法:变量名称 表名%ROWTYPE
【示例】查询 emp表中7839号员工的个人信息,打印姓名和薪水…..
declare v_emp emp%ROWTYPE; begin select * into v_emp from emp where empno=7839; DBMS_output.put_line(‘姓名:’|| v_emp.ename || ‘,薪水:’|| v_emp.sal); end;
如果有一个表,有100个字段,如果要使用这100字段名,如果使用引用型变量一个个声明,会特别麻烦,记录型变量可以方便的解决这个问题。
错误的使用:
1.记录型变量只能存储一个完整的行数据。
2.返回的行太多了,记录型变量也接受不了。
1.4流程控制
1.4.1条件分支
begin if 条件1 then 执行1 elsif 条件2 then 执行2 else 执行3 end if; end;
【示例】判断emp表中记录是否超过20条,10-20之间,或者10条以下
declare --声明变量接受emp中的数量 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;
1.4.2循环
在oracle中有三种循环方式,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;
2、游标
2.1什么是游标
用于临时存储一个查询返回的多行数据(结构集,类似于Java的jdbc连接返回的ResultSet集合),通过遍历游标,可以逐行访问处理该结果集的数据。
游标的使用方式:声明--à打开à读取à关闭
2.2语法
游标声明:
cursor 游标名[(参数列表)] is 查询语句;
游标的打开:
open 游标名;
游标的取值:
fetch 游标名 into 变量列表;
游标的关闭:
close 游标名;
2.3游标的属性
游标的属性 |
返回值类型 |
说明 |
%ROWCOUNT |
整型 |
获得fetch语句返回的数据行数 |
%FOUND |
布尔型 |
最近的fetch语句返回一行数据则为真,否则为假 |
%NOTFOUNT |
布尔型 |
与%FOUND属性返回值相反 |
%ISOPEN |
布尔型 |
游标已经打开时值为真,否则为假 |
其中%NOTFOUND是在游标中找不到元素的时候返回TRUE,通常用来判断退出循环
2.4创建和使用
无参游标
【示例】使用游标查询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_name||’,薪水:’||v_sal); end loop; --关闭游标 close c_emp; end;
带参游标
【示例】使用游标查询并打印某部门的员工的姓名和薪资,部门编号为运行时手动输入。
declare --声明游标 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 --打开游标 open c_emp; 遍历游标 loop --获取游标中的数据 fetch c_emp into v_ename,v_sal; --退出循坏条件 exit when c_emp%notfound; dbms_output.put_line(v_name || v_sal); end loop; --关闭游标 close c_emp; end;
3、存储过程
3.1概念作用
之前我们编写的PLSQL语句程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复调用。可以理解之前的代码全部编写在了main方法中,是匿名程序。Java可以通过封装对象和方法来解决复用问题,PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程。
存储过程作用:
3.2语法
create or replace procedure 过程名称[(参数列表)] is begin end [过程名称];
根据参数的类型,我们将其分为3类讲解:
不带参数的
带输入参数的
带输入输出参数(返回值)的
3.3无参存储
3.3.1创建存储
3.3.2调用存储
create or replace procedure p_hello as begin dbms_output.put_line(‘hello word’); end p_hello; begin p_hello; end;
exec p_hello; -----sqlplus
注:is和as是可以会用的,用哪个都没有关系;过程中没有declare关键字,declare用在语句块中
3.4带输入参数的存储过程
【示例】查询并打印某个员工(如7839号员工)的姓名和薪水—存储过程:要求:调用的时候传入员工编号,自动控制台打印。
create or replace procedure p_querynameandsal(v_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=v_empno; dbms_output.put_line(v_name || v_sal); end;
declare i integer; begin p_querynameandsal(7839); end exec p_querynameandsal(7839);
3.5带输出参数的存储过程
【示例】输入员工号查询某个员工(7839号员工)信息,要求,将薪水作为返回值输出,给调用的程序使用。
create or replace procedure p_querysal_out(v_empno in emp.empno%type,o_sal out emp.sal%type) as begin select sal into o_sal from emp where empno=v_empno; end;
declare v_sal emp.sal%type; begin p_querysal_out(7839,v_sal); dbms_output.put_line(v_sal); end
3.6JAVA程序调用存储过程
需求:如果一条语句无法实现结构集,比如需要多表查询,或者需要复杂逻辑查询,我们可以选择调用存储出你的结果。
结论:通过 Connection对象的prepareCall方法传递一个转义SQL语句可以实现调用存储过程。输入参数直接调用set方法传递,输出参数需要注册后,执行存储过程,通过get方法获取,参数列表的下标是从1开始的。
public interface CallableStatement
用于执行SQL存储过程的界面。 JDBC API提供了存储过程SQL转义语法,允许以标准方式为所有RDBMS调用存储过程。 此转义语法包含一个结果参数和不包含结果参数的表单。 如果使用,结果参数必须注册为OUT参数。 其他参数可用于输入,输出或两者。 参数按顺序依次引用,第一个参数为1。
{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
{call <procedure-name>[(<arg1>,<arg2>, ...)]}
IN参数值使用从PreparedStatement继承的set方法设置。 所有OUT参数的类型必须在执行存储过程之前进行注册; 它们的值通过这里提供的get方法在执行get 。
import oracle.jdbc.OracleTypes; import java.sql.CallableStatement; import java.sql.DriverManager; import java.sql.Connection; public class JdbcTest{ public static void main(String[] args){ //1.加载驱动 Class.forName(“oracle.jdbc.driver.PracleDriver”); //2.获取连接 String url=”jdbc:oracle:thin:@localhost:1521:xe”; String name=”scott”; String password=”tigger”; Connection conn=DriverManager.getConnection(url,name,password); //3.获取语句对象 String sql=”{call p_querysal_out(?,?)}”; CallableStatement call=conn.prepareCall(sql); //4.设置输出参数 call.setInt(1,7839); //5.注册输出参数 call.registerOutParameter(2,OracleTypes.DOUBLE); //6.执行存储过程 call.execute(); //7.获取输出参数 double sal=call.getDouble(2); System.out.println(“薪水:”+sal); //8.释放资源 call.close(); conn.close(); } }
视频整理:https://www.bilibili.com/video/av46777605
原文:https://www.cnblogs.com/chloe-0328/p/10575238.html