Oracle数据库开发笔记 lesson4

最新推荐文章于 2021-04-03 01:33:46 发布

写代码的judy

最新推荐文章于 2021-04-03 01:33:46 发布

阅读量293

点赞数

分类专栏： Oracle数据库学习笔记

本文链接：https://blog.csdn.net/xiedaimadejudy/article/details/52434182

版权

Oracle数据库学习笔记专栏收录该内容

6 篇文章 0 订阅

订阅专栏

存储过程：有名字的plsql

plsql没办法复用，可以重复调用执行

用在业务相对复杂（sql+逻辑）

提示数据库性能，一次编译，反复调用

相当于java里定义方法

- 存储过程1

create or replace procedure addemp_proc(v_eno number,v_ename varchar2,v_job varchar2,v_salary number,v_age number)

as

-- 声明过程的中间变量

begin

insert into emp(empno,ename,job,salary,age)

values(v_eno,v_ename,v_job,v_salary,v_age);

commit;

end addemp_proc;

-- 通过plsql调用存储过程

declare

begin

addemp_proc(1004,'王二麻子','程序员',12345,23);

end;

--存储过程2

create or replace procedure deleteemp_proc(v_eno number)

as

-- 声明变量

begin

delete from emp where empno=v_eno;

commit;

end deleteemp_proc;

-- plsql 调用过程

declare

begin

deleteemp_proc(1004);

end;

-- 存储过程3

create or replace procedure updateemp_proc(v_eno number)

as

-- 声明变量

begin

update emp set salary=salary+1000,bonus=bonus+500 where empno=v_eno;

commit;

end updateemp_proc;

select * from emp;

--plsql 调用过程

declare

begin

updateemp_proc(1002);

end;

-- 存储过程4

create or replace procedure selectone_proc(v_eno in number)

as

v_name emp.ename%type;

v_job emp.job%type; 相当于返回值

begin

select ename,job into v_name,v_job from emp where empno=v_eno;

dbms_output.put_line(v_name|| v_job);

end selectone_proc;

-- plsql调用过程

declare

begin

selectone_proc(1001);

end;

--过程中的三种参数的模式
-- a. 输入参数 in 默认的
-- b. 输出参数 out 往外返回值的
-- c. 输入输出参数 in out

-- 带有输出参数模式的过程

create or replace procedure countemp_proc(v_dno number,v_count out number)

as



begin

select count(*) into v_count from emp where dno=v_dno;

end countemp_proc;

-- plsql 调用过程

declare

v_c number;

begin

countemp_proc(50,v_c);

dbms_output.put_line(v_c);

end;

-- 传递俩个参数交换对应的值

create or replace procedure swap_proc(v_a in out number,v_b in out number)

as

v_t number;

begin

v_t:=v_a;

v_a:=v_b;

v_b:=v_t;

end swap_proc;

-- 结合游标

create or replace procedure selectAll_proc(v_dno number)

as

Cursor emp_cur6 is

select * from emp where dno=v_dno;

v_emp emp_cur6%rowtype;

begin

open emp_cur6;

loop

fetch emp_cur6 into v_emp;

exit when emp_cur6%notfound;

dbms_output.put_line(v_emp.ename||v_emp.salary);

end loop;

close emp_cur6;

end selectAll_proc;

-- plsql 调用过程

declare

begin

selectAll_proc(10);

end;

-- oracle中有自带游标

create or replace procedure selectAll_proc2(v_dno in number,sys_cur out SYS_REFCURSOR)

as

begin

open sys_cur for select * from emp where dno=v_dno;

end selectAll_proc2;

--调用执行过程
declare
emp_cur7 sys_refcursor;
v_emp emp%rowtype;

begin
selectAll_proc2(10,emp_cur7);

loop
fetch emp_cur7 into v_emp;
exit when emp_cur7%notfound;
dbms_output.put_line(v_emp.ename||v_emp.job);
end loop;
end;

JDBC调用存储过程

Connection conn = ConnectionUtils.getConnection

String sql ="{call add_proc(?,?,?)}" 调用过程

CallableStatement cs = conn.perpareCall(sql);

cs.setInt(1,10);

cs.setString(2, "");

cs.setString(3,"");

boolean flag=cs.execute(); false 表示该过程为增删改， true为查询

ConnectionUtils.CloseConnection();

Connection conn =ConnectionUtils.getConnection();

String sql="{call countemp_proc(?,?)}";

CallableStatement cs =conn.prepareCall(sql);

输入模式的参数设置值

cs.setInt(1,10);

对于输出模式的参数，不要设置值，只需要将oracle数据库类型转换成java类型

cs.registerOutParameter(2,Types.INTEGER);

cs.execute();

获取过程的输出参数值

int count =cs.getInt(2);

ConnectionUtils.CloseConnection();

Connection conn = ConnectionUtils.getConnection();

String sql = "{call select_proc(?,?)}";

CallableStatement cs = conn.prepareCall(sql);

cs.set(1,30);

输出模式为游标类型

cs.registerOutParameter(2,OracleTypes.CURSOR);

cs.execute();

游标==结果集

(ResultSet) rs =cs.getObject(2);

while(rs.next){

System.out.println(rs.getInt(1));

}

ConnectionUtils.CloseConnection();

-- oracle中的自定义函数(了解)
create or replace function get_avgsal_func(v_dno number)
return number
as
-- 声明函数中间变量
v_avg number;
begin
select avg(salary) into v_avg from emp where dno=v_dno;
return v_avg;
end;

程序包:

程序包是对相关存储过程、函数、变量、游标和异常等对象的封装

程序包由规范和主体两部分组成

为什么使用程序包？

使程序设计模块化
提高程序的执行效率

-- 创建程序包规范

create or replace package emp_package

is

minsal emp.salary%type;

maxsal emp.salary%type;

-- 1.添加雇员信息

procedure add_employee(v_empno number,v_ename varchar2,v_salary number,v_dno number);

-- 2.通过雇员编号修改薪水

procedure upd_sal(v_empno number,v_salary number);

-- 3.通过雇员名称修改薪水

procedure upd_sal(v_ename varchar2,v_salary number);

-- 4.通过雇员编号查询薪水

function get_sal(v_empno number) return number;

end emp_package;

-- 创建程序包主体

create or replace package body emp_package

as

-- 实现规范中的函数和过程

--实现过程1

procedure add_employee(v_empno number,v_ename varchar2,v_salary number,v_dno number)

IS

begin

if v_salary between minsal and maxsal then

insert into emp (empno,ename,salary,dno)values (v_empno,v_ename,v_salary,v_dno);

commit;

end if;

end;

-- 实现过程2

procedure upd_sal(v_empno number,v_salary number)

IS

begin

if v_salary between minsal and maxsal then

update emp set salary=v_salary where empno=v_empno;

end if;

end;

-- 实现过程3

procedure upd_sal(v_ename varchar2,v_salary number)

IS

begin

if v_salary between minsal and maxsal then

update emp set salary=v_salary where UPPER(ename)=UPPER(v_ename);

end if;

end;

function get_sal(v_empno number) return number

is

v_sal emp.salary%type;

begin

select salary into v_sal from emp where empno=v_empno;

return v_sal;

end;

begin

select max(salary),min(salary) into maxsal,minsal from emp;

end emp_package;

-- plsql调用程序包的函数和过程

declare

v_sal number;

begin

v_sal:=emp_package.get_sal(1002);

emp_package.add_employee(1005,'李莫愁',32345,50);

dbms_output.put_line(v_sal);

end;