oracle 调用mysql存储过程_基于oracle数据库存储过程的创建及调用

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变量)

【示例】打印人员个人信息,包括:姓名、薪水、地址

declarev_namevarchar2(20):=’zhangsan’;

v_salnumber;

v_addrvarchar2(200);beginv_sal:=1580;select ‘ad’ into v_addr fromdual;

DBMS_output.put_line(‘姓名:’|| v_name|| ’薪水:’ || v_sal ||‘地址:’||v_addr);end;

【示例】查询 emp表中7839号员工的个人信息,打印姓名和薪水

declarev_name vachar2(20);

v_salnumber;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号员工的个人信息,打印姓名和薪水

declarev_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号员工的个人信息,打印姓名和薪水…..

declarev_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 条件2then执行2else执行3end if;end;

【示例】判断emp表中记录是否超过20条,10-20之间,或者10条以下

declare

--声明变量接受emp中的数量

v_count number;begin

select count(1) into v_count fromemp;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循环

语法:

beginloopexit when退出循环条件endloop;end;

【示例】打印数字1-10

declarev_numnumber:=1;beginloopexit when v_num>10;

dbms_output.put_line(v_num);

v_num+=v_num+1;endloop;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 fromemp;--声明变量用来接受游标中的元素

v_ename emp.ename%type;

v_sal emp.sal%type;begin

--打开游标

openc_emp;

遍历游标

loop--获取游标中的数据

fetch c_emp intov_ename,v_sal;--退出循坏条件

exit when c_emp%notfound;

dbms_output.put_line(‘姓名:’||v_name||’,薪水:’||v_sal);endloop;--关闭游标

closec_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

--打开游标

openc_emp;

遍历游标

loop--获取游标中的数据

fetch c_emp intov_ename,v_sal;--退出循坏条件

exit when c_emp%notfound;

dbms_output.put_line(v_name||v_sal);endloop;--关闭游标

closec_emp;end;

3、存储过程

3.1概念作用

之前我们编写的PLSQL语句程序可以进行表的操作,判断,循环逻辑处理的工作,但无法重复调用。可以理解之前的代码全部编写在了main方法中,是匿名程序。Java可以通过封装对象和方法来解决复用问题,PLSQL是将一个个PLSQL的业务处理过程存储起来进行复用,这些被存储起来的PLSQL程序称之为存储过程。

存储过程作用:

在开发过程中,为了一个特定的业务功能,会向数据库进行多次连接关闭(连接和关闭是很耗费资源),需要对数据库进行多次I/O读写,性能比较低。如果把这些业务放到PLSQL中,在应用程序中只需要调用PPLSQL就可以做到连接关闭一次数据库就可以实现我们的业务,可以大大提高效率。

ORACLE官方给的建议:能够让数据库操作的不要放在程序中。在数据库中实现基本上不会出现错误,在程序中操作可能会存在错误。(如果在数据库中操作数据,可以有一定的日志恢复等功能)

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

begindbms_output.put_line(‘hello word’);endp_hello;beginp_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;

declareiinteger;beginp_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;

declarev_sal emp.sal%type;beginp_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 [(,, ...)]}

{call [(,, ...)]}

IN参数值使用从PreparedStatement继承的set方法设置。 所有OUT参数的类型必须在执行存储过程之前进行注册; 它们的值通过这里提供的get方法在执行get 。

importoracle.jdbc.OracleTypes;importjava.sql.CallableStatement;importjava.sql.DriverManager;importjava.sql.Connection;public classJdbcTest{public static voidmain(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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值