Oracle存储：
PL/SQL：
PL/SQL块结构语言是sql语言的一种扩展，结合了oracle过程语言进行使用。
PL/SQL块：
声明部分、执行部分、异常部分
/*
[declare]
--定义变量，如果没有变量可以不写
begin
--执行逻辑代码
end;
*/
set serveroutput on; --设置控制支持dbms_output的打印
begin
dbms_output.put_line('Hello World');
end;
--需求：将helloworld设置到变量里面
declare
--声明变量的格式
--字段名 数据类型(大小)[:=初始值]; --赋值符号使用:=
sayHello varchar2(15):='Hello World!';
begin
SYS.dbms_output.put_line(sayHello);
end;
变量与常量：
除了使用数据类型之外：varchar、number、int等
还可以使用属性类型：%type与%rowtype
%type：可以用来定义数据变量的类型与已定义的数据变量一致
%rowtype：与某一数据库表的结构一致；访问方式为rowtype的变量名.字段名。（一般用于声明对表的类型）
--需求：指定员工的编号，查询员工的姓名;
declare
--设置一个常量写员工的编号
--常量的定义格式
--常量名 constant 数据类型(大小):=初始值;
v_empno constant number(10):=7788;
v_ename varchar2(50);
begin
--如何查询的结果赋予声明变量,使用into关键字
select ename into v_ename from emp where empno=v_empno;
dbms_output.put_line(v_ename);
end;
--需求：指定员工的编号，查询员工的姓名，工资，奖金;
declare
--设置一个常量写员工的编号
--常量的定义格式
--常量名 constant 数据类型(大小):=初始值;
v_empno constant number(10):=7788;
v_ename varchar2(50);
v_sal number(8,2);
v_comm number(8,2);
begin
--如何查询的结果赋予声明变量,使用into关键字
select ename,sal,comm into v_ename,v_sal,v_comm from emp where empno=v_empno;
dbms_output.put_line(v_ename||'--'||v_sal||'--'||v_comm);
end;
--oracle设置变量或常量时，借用表字段已经定义的类型
--格式为：
-- 变量名 表名.字段名%type[:=初始值];
--%type类型
--需求：指定员工的编号，查询员工的姓名，工资，奖金;
declare
--设置一个常量写员工的编号
--常量的定义格式
--常量名 constant 数据类型(大小):=初始值;
v_empno constant emp.empno%type:=7788;
v_ename emp.ename%type;
v_sal emp.sal%type;
v_comm emp.comm%type;
begin
--如何查询的结果赋予声明变量,v_comm from emp where empno=v_empno;
dbms_output.put_line(v_ename||'--'||v_sal||'--'||v_comm);
end;
--需求：指定员工的编号，查询员工的姓名，工资，奖金,使用行类型；
--如果返回的是一行记录，可以设置一个行类型接收所有的字段，类似于java里的javabean
--格式为:
-- 字段名 表名%rowtype;
declare
--设置一个常量写员工的编号
--常量的定义格式
--常量名 constant 数据类型(大小):=初始值;
v_empno constant emp.empno%type:=7788;
v_emp emp%rowtype;
begin
--如何查询的结果赋予声明变量,使用into关键字
--select ename,comm into v_emp.ename,v_emp.sal,v_emp.comm from emp where empno=v_empno;
select * into v_emp from emp where empno=v_empno;
dbms_output.put_line(v_emp.ename||'--'||v_emp.sal||'--'||v_emp.comm);
end;
--控制语句
/*
根据员工的工资判断其工资等级（工资大于等于5000为A级，工资大于等于4000为B级，工资大于等于3000为C级，工资大于等于2000为D级，其它为E级）
if -else
*/
/*
语法：
if <条件 > then
--处理
[elsif <条件> then]
[else]
end if;
*/
declare
--指定一个员工的编号
v_empno emp.empno%type:='7789';
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=v_empno;
--控制语句
if v_sal >= 5000 then
dbms_output.put_line('A级');
elsif v_sal >=4000 then
dbms_output.put_line('B级');
elsif v_sal >=3000 then
dbms_output.put_line('C级');
elsif v_sal >=2000 then
dbms_output.put_line('D级');
else
dbms_output.put_line('E级');
end if;
end;
------需求：计算1-10的总和，for循环
--范围运算符（..） 1..4，表示1至4这个范围.只能从小到大，如果需要反转使用reverse
/*
for 变量 in 范围或游标 1..10
loop
--循环体
end loop;
类似于java的加强的for循环
for(变量 : 集合|数组){
}
*/
declare
v_sum number:=0;
begin
for i in 1..10
loop
v_sum:=v_sum+i;
end loop;
SYS.dbms_output.put_line(v_sum);
end;
begin
for i in reverse 1..4
loop
dbms_output.put_line(i);
end loop;
end;
----需求：计算1-10的总和，while循环
/*
语法：
while <条件>
loop
--循环体
end loop;
等同于java里面
while(条件){
}
*/
declare
v_sum number:=0;
v_num number:=1;
begin
while v_num<=10
loop
v_sum:=v_sum+v_num;
v_num:=v_num+1;
end loop;
SYS.dbms_output.put_line(v_sum);
end;
--循环
/*
语法：
loop
exit when <条件> --退出条件，如果条件为真就退出
end loop;
类似于java的do{}while(<条件>)。不过条件如何都先进到循环体
于do-while()的区别：
1.条件退出条件，如果条件为真就退出
2.条件可以放在循环体里面的任何地方。
*/
--需求：计算1-10的总和，loop循环
declare
v_sum number:=0; --声明变量时，如果是数字类型，默认可以不指定大小，字符类型必须要指定大小
v_num number:=1;
begin
loop
v_sum :=v_sum+v_num;
exit when v_num=10;
v_num:=v_num+1;
end loop;
SYS.dbms_output.put_line(v_sum);
end;
---异常处理
/*
declare
begin
[exception]
when <异常类型> then
--处理
when others when
--处理
end;
*/
--需求，当除法为0
declare
v_total number:=0;
begin
v_total:=10/0;
SYS.dbms_output.put_line(v_total);
exception
--when ZERO_DIVIDE then
when others then
--异常处理输出，有两个输出的内置变量
--sqlcode，内置异常的错误码
--sqlerrm,内置异常的错误信息
SYS.dbms_output.put_line('除数不能为0，请注意输入！'||sqlcode||'--错误信息'||sqlerrm);
end;
--自定义异常
/*
判断emp中相应empno对应用户的奖金是否低于500，如果低于则抛出并处理自定义异常
*/
set serveroutput on;
declare
v_empno constant emp.empno%type:=7788; --scott
v_comm emp.comm%type;
--自定义异常
ex_comm exception;
begin
select nvl(comm,0) into v_comm from emp where empno=v_empno;
if v_comm<500 then
SYS.dbms_output.put_line('---奖金为---'+v_comm);
--抛出异常
raise ex_comm;
end if;
exception
--捕捉异常
when ex_comm then
--异常输出的存储过程，第一参数代表错误码，第二参数代表错误信息
RAISE_APPLICATION_ERROR(-20001,'奖金低于500，太少了！');
SYS.dbms_output.put_line('奖金不能小于500，太扣了');
when others then
SYS.dbms_output.put_line('其它异常');
end;
--TCL，事务控制语言（AMT）
--如果一个处理有多个操作（增，删，改），要不全部成功，要不全部失败。这个时候就需要使用事务
--commit 提交。oracle数据库如果没有提交，数据是没有保证到表里面的
--rollback 回滚
--savepoint 保存点
savepoint a;
insert into emp(empno,ename) values(9,'张三');
savepoint b;
insert into emp(empno,ename) values(10,'李四');
--rollback to b; --回滚到b点
commit;
rollback to a; --提交后不能回滚
--需求，编写一段plsql，插入两个员工，如果出错，回滚
begin
insert into emp(empno,ename) values(11,'张三');
insert into emp(empno,ename) values(12,'李四999999999999999');
commit;
exception
when others then
SYS.dbms_output.put_line('出错，回滚');
rollback;
end;
游标：
显式游标：
游标是映射在结果集中一行数据上的位置实体，使用游标，便可以访问结果集中的任意一行数据了，将游标放置到某行后，即可对该行数据进行操作；从上向下依次迭代结果集。
隐式游标：
当执行一个SQL语句时，Oracle会自动创建一个隐式游标，隐式游标主要处理DML语句，该游标的名称是sql。隐试游标不能进行"OPEN","CLOSE","FETCH"这些操作。
属性:
%NOTFOUND --如果DML语句没有影响到任何一行时，则该属性为"TRUE"，否则为"FALSE";
%FOUND --如果DML语句影响到一行或一行以上时，则该属性为"TRUE"，否则为"FALSE";
%ROWCOUNT --返回游标当最后一行的行数;
|
---如果需要读一个表数据，如何读取呢？
--需求：读取emp表里面的所有员工编号，姓名，工资信息
--如果返回的数据是一个集合的数据，这种情况下，需要使用游标
--游标的声明格式
-- cursor 游标名 is select 语句
-- 操作数据库的流程
-- 打开
-- 获得数据
-- 关闭
---游标的状态
-- notfound ，找不到数据返回true,否则返回false
-- found,有数据，返回true.否则false
-- isopen,游标是不是打开，如果关闭为false
-- rowcount,返回当前的行数
--使用的格式 游标名%状态名
set serveroutput on;
declare
v_emp emp%rowtype;
--声明一个游标
cursor v_cur_emp is select * from emp;
begin
--打开游标
open v_cur_emp;
---获取所以数据，循环
loop
--获取游标数据
fetch v_cur_emp into v_emp;
--如果游标读不到数据，就退出
exit when v_cur_emp%notfound;
SYS.dbms_output.put_line(v_emp.ename||'----');
end loop ;
close v_cur_emp;
end;
--需求：读取emp表里面的部门编号为30所有员工编号，姓名，工资信息
--使用for循环
--区别与其它两个循环
--不需要打开，不需要关闭，不需要显示的读取游标
declare
cursor v_cur_emp(p_deptno emp.deptno%type) is select * from emp where deptno=p_deptno;
--如果游标声明后面包括了一个select语句，这个游标有一个返回类型，返回类型就是查询语法的表的行类型。
--游标返回类型等同于查询的表的行类型
v_emp v_cur_emp%rowtype;
begin
for v_emp in v_cur_emp(30)
loop
SYS.dbms_output.put_line(v_emp.ename);
end loop;
end;
--需求：读取emp表里面的部门编号为20所有员工编号，姓名，工资信息
--使用while循环
set serveroutput on;
declare
cursor v_cur_emp(p_deptno emp.deptno%type) is select * from emp where deptno=p_deptno;
v_emp emp%rowtype;
begin
--1.打开
open v_cur_emp(20);
--2.读取,--游标状态必须要读过游标之后才有数据的
fetch v_cur_emp into v_emp;
while v_cur_emp%found --有数据才循环
loop
fetch v_cur_emp into v_emp;
dbms_output.put_line(v_emp.ename||'--'||v_emp.sal);
end loop;
--3.关闭
close v_cur_emp;
end;
--游标引用
--需求：读取emp表里面的所有员工编号，姓名，工资信息。使用游标引用
--游标引用的声明
-- 游标名 sys_refcursor;
--或
-- 游标名 refcursor;
declare
--声明了个游标引用,声明游标一个没有select语句的游标，就是游标引用。
v_cur_emp sys_refcursor;
v_emp emp%rowtype;
begin
--使用游标引用
open v_cur_emp for select * from emp;
--获取数据
loop
fetch v_cur_emp into v_emp;
exit when v_cur_emp%notfound; --如果没有数据，退出
SYS.dbms_output.put_line(v_emp.ename||'--'||v_emp.sal);
end loop;
close v_cur_emp;
end;
--隐式游标
--需求：指定一个员工的编号，增加这个员工的奖金，如果员工不存在，提示不存在
begin
update emp set comm=nvl(comm,0)+300 ;
if sql%notfound then --离它最近的sql
SYS.dbms_output.put_line('员工不存在');
else
SYS.dbms_output.put_line('有'||sql%rowcount||'增加了300元');
end if;
commit; --如果加上提交，数据是没有更新到表的
end;
--需求：读取emp表里面的部门编号为10所有员工编号，姓名，工资信息
--使用有参数的游标
--有参数的游标的声明格式
-- cursor 游标名(变量名 数据类型[,变量名 数据类型...]) is select 语句;
--参数列表：的参数可以被select直接使用
declare
cursor v_cur_emp(p_deptno emp.deptno%type ) is select * from emp where deptno =p_deptno;
v_emp emp%rowtype;
begin
--1.打开游标
open v_cur_emp(10);
--2.获得数据
loop
fetch v_cur_emp into v_emp;
exit when v_cur_emp%notfound; --没有数据才退出
SYS.dbms_output.put_line(v_emp.ename||'--'||v_emp.sal);
--打印当前的记录数
SYS.dbms_output.put_line(v_cur_emp%rowcount);
end loop;
--3.关闭游标
close v_cur_emp;
end;
存储过程和存储函数：
存储过程是命名的pl/sql程序块，封装数据业务操作，具有模块化、可重用、可维护、更安全特点；并且可以被程序调用。一般有4类型的存储过程，分别为不带参数、带输入参数、带输出参数、带输入输出参数。
存储函数与过程不同的是，存储函数有return语句；一般情况下如果在需要一个返回值时可使用存储函数。
--为什么需要存储过程
/*
提高sql执行的效率
提高数据库代码的重用性
*/
--存储过程的创建
/*
语法：
create [or replace] procedure <过程名>
as
--定义声明
begin
--逻辑处理主体
end;
*/
--无参数的存储过程
create or replace procedure pro_say_hello
as
v_hello constant varchar2(15):='HelloWorld';
begin
SYS.dbms_output.put_line(v_hello);
end;
/ --执行
---调用
--方式1，使用exec关键字调用
exec pro_say_hello;
--方式2，使用plsql调用
begin
pro_say_hello;
end;
--查看存储过程
select * from SYS.user_procedures;
--删除过程
drop procedure pro_say_hello;
--返回游标的存储过程
--需求：传入编号的部门编号，返回该部门的所有员工信息
create or replace procedure find_emp_by_deptno
(
v_deptno in emp.deptno%type,--定义了一个游标的引用
v_cur_emp out sys_refcursor
)
as
begin
--将游标引用指定条查询语句
open v_cur_emp for select * from emp where deptno=v_deptno;
end;
--调用
declare
v_emp emp%rowtype;
v_cur_emp sys_refcursor;
begin
--因为在调用存储过程的时候，存储过程已经打开了游标，所以不用打开了
find_emp_by_deptno(10,v_cur_emp);
loop
fetch v_cur_emp into v_emp;
exit when v_cur_emp%notfound;
SYS.dbms_output.put_line(v_emp.ename);
end loop;
close v_cur_emp;
end;
--有参数的存储过程
--需求：使用存储过程，传入员工的编号，返回员工的姓名
/*
语法：
create or replace procedure <过程名>
(变量名 in 数据类型,变量名 out 数据类型 )
as
begin
end;
参数列表：
1.输入的参数格式为：变量名 in 数据类型 ;
或 变量名 数据类型; 如果不写in默认为输入参数
2.输出的参数格式为：变量名 out 数据类型;
3.输入参数和输出参数可以有1到N个。
*/
create or replace procedure pro_find_name_by_empno
(p_empno in emp.empno%type,p_ename out emp.ename%type )
as
begin
select ename into p_ename from emp where empno=p_empno;
end;
--查看
select * from SYS.user_procedures;
--调用
declare
v_empno emp.empno%type:=7788; --scott
v_ename emp.ename%type;
begin
pro_find_name_by_empno( v_empno,v_ename);
SYS.dbms_output.put_line(v_ename);
end;
---存储函数
/*
create or replace function <函数名> return 返回类型
as
begin
end;
*/
---HelloWorld
create or replace function fn_say_hello return varchar2
as
begin
return 'HelloWorld';
end;
--有参数的存储函数
--需求：使用存储函数，输入员工的部门编号，输出员工的信息，返回部门编号
--函数类似与存储过程
--区别
/*
1.存储函数是必须需要返回值，存储过程是没有返回值
2.调用不一样。存储函数使用select关键字调用，存储过程使用exec调用
应用场景的不同
一般情况下，存储函数用于给一些复杂sql语句使用的。存储过储用于被第三的程序调用
*/
create or replace function fn_find_by_deptno
(
p_deptno in emp.deptno%type,p_cur_emp out sys_refcursor
)
return number
as
--定义一个内部变量
begin
open p_cur_emp for select emp.* from emp where deptno =p_deptno;
--返回部门的编号
return p_deptno;
end;
--调用
declare
v_cur_emp sys_refcursor;
v_depno emp.deptno%type;
v_emp emp%rowtype;
begin
v_depno:=fn_find_by_deptno(10,v_cur_emp);
loop
fetch v_cur_emp into v_emp;
exit when v_cur_emp%notfound;
SYS.dbms_output.put_line(v_emp.ename);
end loop;
SYS.dbms_output.put_line(v_depno);
close v_cur_emp;
end;
--为什么需要存储过程
/*
提高sql执行的效率
提高数据库代码的重用性
*/
--存储过程的创建
/*
语法：
create [or replace] procedure <过程名>
as
--定义声明
begin
--逻辑处理主体
end;
*/
--无参数的存储过程
create or replace procedure pro_say_hello
as
v_hello constant varchar2(15):='HelloWorld';
begin
SYS.dbms_output.put_line(v_hello);
end;
/ --执行
---调用
--方式1，使用exec关键字调用
exec pro_say_hello;
--方式2，使用plsql调用
begin
pro_say_hello;
end;
--查看存储过程
select * from SYS.user_procedures;
--删除过程
drop procedure pro_say_hello;
--返回游标的存储过程
--需求：传入编号的部门编号，返回该部门的所有员工信息
create or replace procedure find_emp_by_deptno
(
v_deptno in emp.deptno%type,v_cur_emp);
loop
fetch v_cur_emp into v_emp;
exit when v_cur_emp%notfound;
SYS.dbms_output.put_line(v_emp.ename);
end loop;
SYS.dbms_output.put_line(v_depno);
close v_cur_emp;
end;
同时存储过程和函数也是可以通过java来获取数据
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class TestProcedure {
public static void main(String[] args) {
Connection conn = null;
CallableStatement call = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
conn = DriverManager.getConnection(url,"itcast","itcast");
call = conn.prepareCall("{call pro_query_enameAndSal_by_empno(?,?,?)}");
//设置输入型参数
call.setInt(1,7369);
//注册输出型参数
call.registerOutParameter(2,OracleTypes.VARCHAR);
call.registerOutParameter(3,OracleTypes.NUMBER);
//调用存储过程
call.execute();
//获取返回值
String ename = call.getString(2);//员工名称
double sal = call.getDouble(3);//员工工资
System.out.println("员工号为7369的员工名称为：" + ename + "，工资为：" + sal);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(call != null){
call.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
public class TestFunction {
public static void main(String[] args) {
Connection conn = null;
CallableStatement call = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
conn = DriverManager.getConnection(url,"itcast");
call = conn.prepareCall("{? = call fun_get_annualSal_by_empno2(?,?)}");
//注册存储函数返回值
call.registerOutParameter(1,OracleTypes.DOUBLE);
//设置输入参数，员工号
call.setInt(2,7499);
//注册输出参数，员工姓名
call.registerOutParameter(3,OracleTypes.VARCHAR);
//注册输出参数，奖金
call.registerOutParameter(4,OracleTypes.DOUBLE);
call.execute();
System.out.println("员工姓名为：" + call.getString(3) + "，奖金为：" + call.getDouble(4)
+ "，年薪为：" + call.getDouble(1));
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(call != null){
call.close();
}
if(conn != null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
存储过程与存储函数的区别
1、返回值的区别,函数一定要有1个返回值或有多个通过输出参数的返回值,而存储过程是通过输出参数返回的,可以有多个或者没有；
2、调用的区别,函数可以在sql语句中直接调用,而存储过程必须单独调用；
3、函数一般情况下是用来计算并返回一个计算结果，而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
