Oracle的存储

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle的存储，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

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语句等等）

原文链接：https://www.f2er.com/oracle/211644.html

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问