基于oracle数据库的PLSQL编程以及存储过程的创建和使用

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/zyp112/article/details/82755468

前言

本文简单记录下oracle数据库中,如何用plsql编程、以及存储过程的创建和使用。
相关的概念我就不列举了,大家不清楚的可以自行搜索,本篇主要列举实际的sql语法。
那plsql编程,大家可以使用windows的命令行,也可以使用工具进行编写调试,我这边主要使用Oracle Sql Developer来举例。

1 plsql编程

1.1 程序结构

首先说说plsql的程序,可以分为3个部分:声明部分、可执行部分、异常处理部分。其中declare部分用来声明变量或者游标(结果集类型变量),如果程序中没有变量声明则可以省略。

DECLARE
  --声明变量、游标
  -- 相当于java中的 public class A{}
BEGIN
  --执行部分,相当于java中的main方法
  --可以进行异常处理
  dbms_output.put_line('hello world');
END;

这段程序中,dbms_output是oracle的内置程序包,相当于java中的System.out,而put_line()是调用的方法,相当于println()方法。执行结果为:
在这里插入图片描述
那如果是在命令行执行这段程序,则需要在执行程序之前,打开控制台的输出,并在结尾加“/”标识程序已结束,然后回车即可看到打印的结果。
在这里插入图片描述

1.2 变量

在plsql中,常见的变量分为两大类:
1、普通数据类型(char,varchar2,date,number,boolean,long);
2、特殊变量类型(引用型变量、记录型变量);
变量的声明方式为:变量名 变量类型(变量长度) 例如:v_name varchar2(20)

1.2.1 普通变量

普通变量的赋值方式有两种:
1)使用“:=”直接赋值,比如:v_name :=‘zhangsan’
2)语句赋值,语法为:select 值 into 变量名

-- 声明一个人的信息,姓名、薪水、地址
DECLARE
  -- 姓名
  v_name VARCHAR2(50) := 'zhangsan';
  -- 薪水
  v_sal NUMBER;
  -- 地址
  v_addr VARCHAR2(200);
begin
  -- 直接赋值
  v_sal := 10000;
  -- 语句赋值
  select '中国' into v_addr from dual;
  -- 打印变量
  DBMS_OUTPUT.PUT_LINE('姓名:' || v_name || '薪水:' || v_sal || '地址:' || v_addr);
end;

执行结果为:
在这里插入图片描述

1.2.2 引用型变量

变量的类型和长度取决于表中字段的类型和长度;
通过表名.列名%type指定变量的类型和长度,例如:v_name emp.ename%type;

引用型变量的好处:
使用普通变量定义方式,需要知道表中列的类型,而使用引用类型,不需要考虑列的类型,使用%type是非常好的编程风格,因为它使得plsql更加灵活,更加适应于对数据库定义的更新。
建表语句:

CREATE TABLE "GD_JIANGUAN"."EMP" 
   (	"ENAME" VARCHAR2(20 BYTE), 
	"ESAL" NUMBER, 
	"EADDR" VARCHAR2(20 BYTE), 
	"ENO" VARCHAR2(20 BYTE)
   ) ;
   COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ENAME" IS '用户名';
   COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ESAL" IS '薪水';
   COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."EADDR" IS '地址';
   COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ENO" IS '编号';
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('张三',10000,'中国','1');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('李四',8000,'美国','2');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('王五',12000,'广州','3');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('赵六',7800,'深圳','4');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('钱七',9000,'上海','5');

举例

--查询emp表中赵六的个人信息,打印姓名、薪水和地址
DECLARE
  --姓名 声明变量并直接赋值
  v_name   emp.ename%TYPE;
  --薪水
  v_sal    emp.esal%TYPE;
  --地址
  v_addr   emp.eaddr%TYPE;
BEGIN
  --查询表中的姓名和薪水并赋值给变量
  --注意查询的字段和赋值的变量的顺序、个数、类型需要一致
  SELECT ename,esal,eaddr INTO v_name,v_sal,v_addr FROM emp WHERE ename = '赵六';
  --打印结果
  dbms_output.put_line('姓名:' || v_name || ' 薪水:' || v_sal || ' 地址:' || v_addr);
END;

结果为:
在这里插入图片描述

1.2.3 记录型变量

接收表中的一整行记录,相当于java中的一个对象
语法:变量名称 表名%rowtype,例如:v_emp emp%type;
如果一张表有几十个字段,程序中需要使用这些字段,那定义一个记录型变量则可以方便的解决这个问题。
举例:

--查询emp表中赵六的个人信息,打印姓名、薪水和地址
DECLARE
  --定义记录型变量
  v_emp emp%ROWTYPE;
BEGIN
  --记录型变量默认接收表中的一行数据,不能指定字段
  SELECT * INTO v_emp FROM emp WHERE ename = '赵六';
  --打印结果,通过变量名.属性的方式获取变量中的值
  dbms_output.put_line('姓名:' || v_emp.ename || ' 薪水:' || v_emp.esal || ' 地址:' || v_emp.eaddr);
END;

结果为:
在这里插入图片描述
使用记录型变量常见的问题及注意点:
1)记录型变量只能存储一条完整的行数据,不支持存储部分字段
在这里插入图片描述
2)返回的行太多,记录型变量也接收不了
在这里插入图片描述

1.3 流程控制

1.3.1 条件分支

条件分支的语法一般为

begin
    if 条件1 then 执行1        
	elsif 条件2 then 执行2
	else 执行3
    end if;
end;

举例:

-- 判断emp表中记录数是否超过5条,3-5条,小于3条
DECLARE
  --声明变量接收emp表中的记录数
  v_count NUMBER;
BEGIN
  SELECT COUNT(1) INTO v_count FROM emp;
  IF v_count > 5 THEN
    dbms_output.put_line('记录数超过5条,具体数量为:' || v_count);
  ELSIF v_count >= 3 THEN
    dbms_output.put_line('记录数为3~5条,具体数量为:' || v_count);
  ELSE
    dbms_output.put_line('记录数小于3条,具体数量为:' || v_count);
  END IF;
END;

结果为:
在这里插入图片描述
注意:oracle中所有的字符拼接符都是“||”,而不是“+”。如果报错“数字或值错误”,则检查程序是否使用了“+”号作为拼接符使用。

1.3.2 循环

oracle中,循环的方式有3钟,for、while、loop,当然,使用goto语法,也能进行循环下面分别列举这4种循环的用法。

-- goto循环
DECLARE
  x number;
BEGIN
  x := 0;
  <<repeat_loop>> --循环点
  x := x + 1;
  DBMS_OUTPUT.PUT_LINE(X);
  IF X < 5 THEN
    GOTO repeat_loop; --当x的值小于9时,就goto到repeat_loop
  END IF;
END;
/
--for循环
DECLARE
  X number; --声明变量
BEGIN
  x := 1; --给初值
  FOR X IN REVERSE 1 .. 10 LOOP
    --reverse由大到小
    DBMS_OUTPUT.PUT_LINE('内:x=' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); --x=1
END;
/
--while循环
DECLARE
  x number;
BEGIN
  x := 0;
  WHILE x < 9 LOOP
    x := x + 1;
    DBMS_OUTPUT.PUT_LINE('内:x=' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('外:x=' || x);
END;
--loop循环
DECLARE
  x number;
BEGIN
  x := 0;
  LOOP
    x := x + 1;
    EXIT WHEN x > 9;
    DBMS_OUTPUT.PUT_LINE('内:x=' || x);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('外:x=' || x);
END;

2 游标

2.1 什么是游标

游标用于临时存储一个查询返回的多行数据(结果集,类似于java中jdbc连接返回的ResultSet集合),通过遍历游标,可以逐行访问处理该结果集的数据。
游标的使用方式:申明 --> 打开 --> 读取 --> 关闭

2.2 语法

游标的声明:cursor 游标名[{参数列表}] is 查询语句;
游标打开: open 游标名;
游标读取:fetch 游标名 into 变量列表;
游标关闭:close 游标名;

2.3 游标的属性

属性 类型 说明
%rowcount 整型 获得fetch语句返回的数据行数
%found 布尔型 最近的fetch语句返回一行数据则为真,否则为假
%notfound 布尔型 与%found返回值相反
%isopen 布尔型 游标已经开启时值为真,否则为假

其中%notfound是在游标中找不到元素的时候返回true,通常用来判断退出循环。

2.4 创建和使用

2.4.1 不带参数
-- 查询EMP表中所有员工姓名和工资,并依次打印出来
DECLARE
  -- 声明游标
  CURSOR C_EMP IS SELECT ENAME, ESAL FROM EMP;
  --申明变量接收游标中的数据
  V_NAME EMP.ENAME%TYPE;
  V_SAL  EMP.ESAL%TYPE;
BEGIN
  --打开游标
  OPEN C_EMP;
  --遍历游标
  LOOP
    -- 获取游标中的数据,如果有的话就赋值给变量
    FETCH C_EMP INTO V_NAME, V_SAL;
    EXIT WHEN C_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_NAME || ' 薪水:' || V_SAL);
  END LOOP;
END;

结果为:
在这里插入图片描述

2.4.2 带参数
-- 查询EMP表中编号为5的员工姓名和工资,并依次打印出来
DECLARE
  -- 声明游标
  CURSOR C_EMP(V_NO EMP.ENO%TYPE) IS
    SELECT ENAME, ESAL FROM EMP WHERE ENO = V_NO;
  --申明变量接收游标中的数据
  V_NAME EMP.ENAME%TYPE;
  V_SAL  EMP.ESAL%TYPE;
BEGIN
  --打开游标
  OPEN C_EMP(5);
  --遍历游标
  LOOP
    -- 获取游标中的数据,如果有的话就赋值给变量
    FETCH C_EMP INTO V_NAME, V_SAL;
    EXIT WHEN C_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('姓名:' || V_NAME || ' 薪水:' || V_SAL);
  END LOOP;
END;

结果为:
在这里插入图片描述

3 存储过程

3.1 概念

之前编写的plsql程序可以进行表的操作、判断、循环逻辑处理的工作,但是没有办法重复调用。这可以理解为之前的代码全部编写在了main方法中,是匿名的程序,那在java中可以通过封装对象和方法来解决复用的问题。那plsql是将一个个的程序处理过程存储起来进行复用,那这些被存储起来的plsql程序称之为存储过程,相当于java中的方法。

存储过程的作用:
1)在开发程序的过程中,为了一个特定的业务功能,会向数据库进行多次请求查询,需要对数据库进行多次I/O读写,性能比较低,而且数据库的连接关闭是非常耗资源的。如果把这些业务处理放到plsql中,在应用程序中调用plsql就可以做到连接关闭一次数据库就可以实现我们的业务,那将可以大大提升效率。
2)oracle官方建议:能够让数据库操作的,就不要放到程序中,在数据库中实现基本不会出现错误,而在程序中可能会出错(如果在数据库中操作数据,有一定的日志恢复等功能)。

3.2 语法

create or replace procedure 过程名称[{参数列表}] is
begin

end [过程名称];

根据参数的类型,可以分为以下3种:
1)不带参数的

create or REPLACE PROCEDURE p_hello is
  --这里声明变量,declare被省略
begin
  DBMS_OUTPUT.PUT_LINE('hello');
end p_hello;
/
exec p_hello;

2)带输入参数的

--创建一个查询并打印某个员工的姓名和薪水的存储过程,要求调用的时候传入员工的编号,自动在控制台打印
CREATE OR REPLACE PROCEDURE QUERYNAMEANDSAL(I_NO EMP.ENO%TYPE) AS
  -- 声明变量
  V_ENAME EMP.ENAME%TYPE;
  V_ESAL  EMP.ESAL%TYPE;
BEGIN
  SELECT ENAME, ESAL INTO V_ENAME, V_ESAL FROM EMP WHERE ENO = I_NO;
  DBMS_OUTPUT.PUT_LINE(V_ENAME || ' ' || V_ESAL);
END QUERYNAMEANDSAL;
/
exec QUERYNAMEANDSAL(5);

3)带输入参数和输出参数(返回值)的

--创建一个查询并打印某个员工薪水的存储过程,要求调用的时候传入员工的编号,程序中获取输出
CREATE OR REPLACE PROCEDURE QUERYNAMEANDSAL02(I_NO in EMP.ENO%TYPE ,O_SAL out EMP.ESAL%TYPE) AS
BEGIN
  SELECT ESAL INTO O_SAL FROM EMP WHERE ENO = I_NO;
END QUERYNAMEANDSAL02;
/
-- 以下程序在java中编写
public static void main(String[] args) {
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
        String userName = "gd_jianguan";
        String userpwd = "gd_jianguan";
        Connection conn = DriverManager.getConnection(url, userName, userpwd);
        //获取语句对象
        String sql = "{call QUERYNAMEANDSAL02(?,?)}";
        CallableStatement call = conn.prepareCall(sql);
        //设置输入参数
        call.setInt(1, 5);
        //注册输出参数
        call.registerOutParameter(2, OracleTypes.DOUBLE);
        //执行存储过程
        call.execute();
        //获取输出参数
        double sal = call.getDouble(2);
        System.out.println("薪水:" + sal);
        //释放资源
        call.close();
        conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}
展开阅读全文

没有更多推荐了,返回首页