Oracle-7 Oracle编程

小墨Sang

已于 2023-06-16 15:25:14 修改

阅读量404

点赞数 1

分类专栏： Oracle 文章标签： oracle 数据库 sql Powered by 金山文档

于 2023-01-12 10:20:44 首次发布

本文链接：https://blog.csdn.net/xmosang/article/details/128654644

版权

Oracle 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

本文介绍了PL/SQL的基本语法结构，包括变量声明与赋值，异常处理机制，以及存储函数和存储过程的创建与调用。通过示例展示了如何在PL/SQL中进行条件判断、循环控制，并讲解了游标的使用。同时，文章还涵盖了存储过程的参数模式和JDBC调用存储过程的方法。

摘要由CSDN通过智能技术生成

7.1、PL/SQL

PL/SQL( Procedure Language/SQL)是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句(如分支、循环等)，使SQL语言具有过程处理能力。把SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来，使得PL/SQL面向过程但比过程语言简单、高效、灵活和实用。

基本语法结构：

[DECLARE
    --声明变量
]
BEGIN
    --代码逻辑
[EXCEPTION
    --异常处理
]
END;

7.1.1、变量

变量声明：

变量名 类型(长度);

变量赋值：

变量名:=变量值
--SELECT INTO 赋值
SELECT 列名1,列名2... INTO 变量名1,变量名2... FROM 表名 WHERE 条件

SELECT INTO赋值时，结果必须为一条记录

--声明变量水费单价、水费字数、吨数、金额。对水费单价、字数、进行赋值。吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。计算金额，金额-单价*吨数输出单价 、数量和金额
DECLARE 
  V_PRICE NUMBER(10,2);--单价
  V_USENUM NUMBER;    --水费
  V_TON NUMBER(10,2);--吨数
  V_MONEY NUMBER(10,2);--金额
BEGIN
  V_PRICE:=2.3;
  V_USENUM:=9436;
  --SELECT USENUM INTO V_USENUM FROM T_ACCOUNT WHERE ID=3;
  V_TON:=ROUND(V_USENUM/1000,2);
  V_MONEY:=V_PRICE*V_TON;
  DBMS_OUTPUT.put_line('单价:'||V_PRICE||' 数量：'||V_TON||' 金额:'||V_MONEY||'');
END;

7.1.2、属性类型

引用型

当我们知道是从某个表的某列获取数值，但我们不知道该值的类型时，可以使用引用属性类型的方式来引用该列的类型

语法：

-- 属性名 表名.列明%TYPE
DECLARE 
  V_PRICE NUMBER(10,2);--单价
  V_USENUM T_ACCOUNT.USENUM%TYPE;    --水费
  V_TON NUMBER(10,2);--吨数
  V_MONEY NUMBER(10,2);--金额

记录型

代表某表的某一行的类型

语法：

-- 属性名 表名%ROWTYPE
DECLARE 
  V_PRICE NUMBER(10,2);--单价
  V_USENUM T_ACCOUNT.USENUM%TYPE;    --水费
  V_TON NUMBER(10,2);--吨数
  V_MONEY NUMBER(10,2);--金额
  V_ACCOUNT T_ACCOUNT%ROWTYPE;--行记录类型 
BEGIN
  V_PRICE:=2.3;
  SELECT*INTO V_ACCOUNT FROM T_ACCOUNT WHERE ID=3;
  V_USENUM:=V_ACCOUNT.USENUM;
  V_TON:=ROUND(V_USENUM/1000,2);
  V_MONEY:=V_PRICE*V_TON;
  DBMS_OUTPUT.put_line('单价:'||V_PRICE||' 数量：'||V_TON||' 金额:'||V_MONEY||'');
END;

7.1.3、异常（例外）

在运行程序时出现的错误叫做异常

发生异常后，语句讲停止执行，控制权转移到PL/SQL块的异常处理部分

异常有两种类型：

预定义异常：当PL/SQL程序违反Oracle规则或超越系统限制时隐式触发

用户定义异常：用户可以在PL/SQL 块的声明部分定义异常，自定义的异常通过RAISE语句显示触发

预定义异常

语法：

EXCEPTION
 WHEN 异常类型 THEN
 异常处理逻辑
--异常处理
 DECLARE 
  V_PRICE NUMBER(10,2);--单价
  V_USENUM T_ACCOUNT.USENUM%TYPE;    --水费
  V_TON NUMBER(10,2);--吨数
  V_MONEY NUMBER(10,2);--金额
  V_ACCOUNT T_ACCOUNT%ROWTYPE;--行记录类型 
BEGIN
  V_PRICE:=2.3;
  SELECT*INTO V_ACCOUNT FROM T_ACCOUNT WHERE ID=100;--数据库中无此纪录
  V_USENUM:=V_ACCOUNT.USENUM;
  V_TON:=ROUND(V_USENUM/1000,2);
  V_MONEY:=V_PRICE*V_TON;
  DBMS_OUTPUT.put_line('单价:'||V_PRICE||' 数量：'||V_TON||' 金额:'||V_MONEY||'');
END;

-- NO_DATA_FOUND 异常
DECLARE
  V_PRICE NUMBER(10,2);-- 单价
  V_USENUM T_ACCOUNT.USENUM%TYPE;    --水费
  V_TON NUMBER(10,2);-- 吨数
  V_MONEY NUMBER(10,2);-- 金额
  V_ACCOUNT T_ACCOUNT%ROWTYPE;-- 行记录类型 
BEGIN
  V_PRICE:=2.3;
  SELECT*INTO V_ACCOUNT FROM T_ACCOUNT WHERE ID=200;-- 数据库中无此纪录
  V_USENUM:=V_ACCOUNT.USENUM;
  V_TON:=ROUND(V_USENUM/1000,2);
  V_MONEY:=V_PRICE*V_TON;
  DBMS_OUTPUT.put_line('单价:'||V_PRICE||' 数量：'||V_TON||' 金额:'||V_MONEY||'');
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('ID错误');
END;

-- TO_MANY_ROWS 异常
DECLARE
  V_PRICE NUMBER(10,2);-- 单价
  V_USENUM T_ACCOUNT.USENUM%TYPE;    -- 水费
  V_TON NUMBER(10,2);-- 吨数
  V_MONEY NUMBER(10,2);-- 金额
  V_ACCOUNT T_ACCOUNT%ROWTYPE;-- 行记录类型 
BEGIN
  V_PRICE:=2.3;
  SELECT * INTO V_ACCOUNT FROM T_ACCOUNT;--返回多行结果
  V_USENUM:=V_ACCOUNT.USENUM;
  V_TON:=ROUND(V_USENUM/1000,2);
  V_MONEY:=V_PRICE*V_TON;
  DBMS_OUTPUT.put_line('单价:'||V_PRICE||' 数量：'||V_TON||' 金额:'||V_MONEY||'');
  EXCEPTION
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('多行数据');
END;

7.1.4、条件判断

语法：
-- IF 条件
IF 条件 THEN
    ...
END IF;
-- IF ELSE 条件
IF 条件 THEN
    ...
ELSE
    ...
END IF;
-- IF ELSE IF 条件
IF 条件 THEN
    ...
ELSIF 条件 THEN
    ...
ELSE
    ...
END IF;

-- 设置三个等级的水费
-- 5吨以下2.45元/吨 5吨到10吨部分3.45元/吨 超过10吨部分4.45元/吨，根据使用水费的量来计算阶梯水费
DECLARE
  V_PRICE NUMBER(10,2);--单价
  V_USENUM T_ACCOUNT.USENUM%TYPE;    --水费
  V_TON NUMBER(10,2);--吨数
  V_MONEY NUMBER(10,2);--金额
  V_ACCOUNT T_ACCOUNT%ROWTYPE;--行记录类型 
BEGIN
  SELECT*INTO V_ACCOUNT FROM T_ACCOUNT WHERE ID=3;
  V_USENUM:=V_ACCOUNT.USENUM;
  V_TON:=ROUND(V_USENUM/1000,2);
  IF V_TON<5THEN
    V_MONEY:=2.45*V_TON;
    ELSIF V_TON<10THEN
      V_MONEY:=5*2.45+3.45*(V_TON-5);
      ELSE
        V_MONEY:=5*2.45+5*3.45+4.45*(V_TON-10);
        END IF;
  DBMS_OUTPUT.put_line('单价:'||V_PRICE||' 数量：'||V_TON||' 金额:'||V_MONEY||'');
END;

7.1.5、循环

无条件循环

LOOP
-- 循环语句
END LOOP
-- 输出1-100
DECLARE
 V_NUM NUMBER:=1;
BEGIN
  LOOP
    DBMS_OUTPUT.put_line(V_NUM);
    V_NUM:=V_NUM+1;
    EXIT WHEN V_NUM>100;
  END LOOP;
END;

有条件循环

WHILE 条件
LOOP
END LOOP
-- 输出1-100
DECLARE
 V_NUM NUMBER:=1;
BEGIN
  WHILE V_NUM<=100
    LOOP
      DBMS_OUTPUT.put_line(V_NUM);
      V_NUM:=V_NUM+1;
     END LOOP;
END;

FOR循环

for 变量 in 起始值..终止值
loop
 ...
end loop;
-- 输出1-100
DECLARE
 V_NUM NUMBER;-- 全局变量
BEGIN
  FOR V_NUM IN1..100-- 局部变量
    LOOP
     DBMS_OUTPUT.PUT_LINE(V_NUM);
     END LOOP;
END;

7.1.6、游标

游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。我们可以把游标理解为PL/SQL中的结果集。

语法：

-- 声明游标
CURSOR 游标名称 IS SQL语句
-- 使用游标
OPEN 游标名称
LOOP
    FETCH 游标名称 INTO 变量
    EXIT WHEN 游标名称%NOTFOUND
END LOOP;
CLOSE 游标名称;
-- 打印业主类型为1的价格表
DECLARE
 V_PRICE T_PRICETABLE%ROWTYPE;
CURSOR CUR_PT ISSELECT*FROM T_PRICETABLE WHERE OWNERTYPEID=1;
BEGIN
  OPEN CUR_PT;
   LOOP
     FETCH CUR_PT INTO V_PRICE;
     EXIT WHEN CUR_PT%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('价格：'||V_PRICE.PRICE||' 吨位：'||V_PRICE.MINNUM||'-'||V_PRICE.MAXNUM);
   END LOOP;
  CLOSE CUR_PT;
END;

带参数的游标

DECLARE 
 V_PRICE T_PRICETABLE%ROWTYPE;
 CURSOR CUR_PT(V_OWNERTYPE NUMBER) 
IS 
SELECT * FROM T_PRICETABLE WHERE OWNERTYPEID=V_OWNERTYPE;
BEGIN
  OPEN CUR_PT(1);
   LOOP
     FETCH CUR_PT INTO V_PRICE;
     EXIT WHEN CUR_PT%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('价格：'||V_PRICE.PRICE||' 吨位：'||V_PRICE.MINNUM||'-'||V_PRICE.MAXNUM);
   END LOOP;
  CLOSE CUR_PT;
END;

FOR循环游标

DECLARE 
 CURSOR CUR_PT(V_OWNERTYPE NUMBER) 
IS
 SELECT * FROM T_PRICETABLE WHERE OWNERTYPEID=V_OWNERTYPE;
BEGIN
  FOR V_PRICE IN CUR_PT(1)
   LOOP
     DBMS_OUTPUT.PUT_LINE('价格：'||V_PRICE.PRICE||' 吨位：'||V_PRICE.MINNUM||'-'||V_PRICE.MAXNUM);
   END LOOP;
END;

7.2、存储函数

存储函数（自定义函数），可以接受一个或多个参数，返回一个结果。在函数中可以使用PL/SQL进行逻辑的处理

语法结构：

-- 创建存储函数
CREATE [OR REPLACE] FUNCTION FUNC_NAME
(参数名称 参数类型,参数名称 参数类型...)
RETURN 结果变量数据类型
IS
 变量声明
BEGIN
 ...
RETURN 结果变量
[EXCEPTION]
END;
-- 创建存储函数，根据地址ID查询地址名称
CREATE OR RE PLACE FUNCTION FUNC_GETADD
(V_ID NUMBER)
RETURN VARCHAR2
IS
 V_NAME VARCHAR2(30);
BEGIN
  SELECT NAME INTO V_NAME FROM T_ADDRESS WHERE ID=V_ID;
  RETURN V_NAME;
END;
-- 函数调用
SELECT FUNC_GETADD(5) FROM DUAL
-- 函数应用
-- 查询业主ID,业主名称，业主地址(函数实现)
SELECT ID,NAME,FUNC_GETADD(ADDRESSID) FROM T_OWNERS

7.3、存储过程

存储过程是被命名的PL/SQL块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，其区别有：

存储函数有返回值，且必须返回，而存储过程没有返回值，可以通过传出参数返回多个值

存储函数可以在select语句中直接使用，而存储过程不能。过程多数是被应用程序所调用

存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码

7.3.1、语法结构

-- 创建存储过程

CREATE[ORREPLACE]PROCEDURE PROC_NAME

(参数名称参数类型,参数名称参数类型...)

IS|AS

变量声明

BEGIN

...

RETURN 结果变量

[EXCEPTION]

END;

存储过程参数的三种模式：

IN：传入参数（默认，可省略）

OUT 传出参数，主要用于返回程序运行结果

IN OUT：传入传出参数

不带传出参数的存储过程

-- 创建存储过程，添加业主信息

CREATE SEQUENCE SEQ_ID STARTWITH11;--编号序列

CREATEORREPLACEPROCEDURE PROC_INSERT

(

V_NAME VARCHAR2,

V_ADD NUMBER,

V_HOUSENUM VARCHAR2,

V_WATER VARCHAR2,

V_OWNERTYPE NUMBER)

BEGIN

INSERTINTO T_OWNERS VALUES(SEQ_ID.NEXTVAL,V_NAME,V_ADD,V_HOUSENUM,V_WATER,SYSDATE,V_OWNERTYPE);

COMMIT;

END;

-- 存储过程调用

CALL PROC_INSERT('小王','2','6-1','30475',2)

-- PL/SQL语法

BEGIN

PROC_INSERT('小王','2','6-1','30475',2);

END;

带传出参数的存储过程

-- 添加业主信息，传出参数为新增业主的ID

CREATEORREPLACEPROCEDURE PROC_INSERT_OUT

(

V_NAME VARCHAR2,

V_ADD NUMBER,

V_HOUSENUM VARCHAR2,

V_WATER VARCHAR2,

V_OWNERTYPE NUMBER,

V_ID OUT NUMBER)

BEGIN

SELECT SEQ_ID.NEXTVALINTO V_ID FROMDUAL;

INSERTINTO T_OWNERS VALUES(V_ID,V_NAME,V_ADD,V_HOUSENUM,V_WATER,SYSDATE,V_OWNERTYPE);

COMMIT;

END;

-- 存储过程调用（只能PL/SQL语法）

DECLARE

V_ID NUMBER;

BEGIN

PROC_INSERT_OUT('小李','6','6-2','30605',1,V_ID);

DBMS_OUTPUT.put_line('ID: '||V_ID);

END;

7.3.2、JDBC调用存储过程

不带传出参数的存储过程的调用

staticpublicvoidadd(Ownersowners){

Connectionconnection=null;

CallableStatementstmt=null;

try {

connection=Basedao.getConnection();

stmt=connection.prepareCall("{call proc_insert(?,?,?,?,?)}");

stmt.setString(1,owners.getName());

stmt.setLong(2,owners.getAddress());

stmt.setString(3,owners.getHousenumber());

stmt.setString(4,owners.getWatermeter());

stmt.setLong(5,owners.getOwnertypeid());

stmt.execute();

} catch (SQLExceptione) {

thrownewRuntimeException(e);

}finally {

Basedao.CloseAll(null,stmt,connection);

}

测试：

@Test

publicvoidproctest(){

Ownersowners=newOwners();//("张三",2L,"4-4","2-5",1L);

owners.setName("张三");

owners.setAddress(2L);

owners.setHousenumber("4-5");

owners.setWatermeter("4-5");

owners.setOwnertypeid(1L);

ProcTest.add(owners);

}

带传出参数的存储过程的调用

staticpubliclongadd_out(Ownersowners){

longid=0;

Connectionconnection=null;

CallableStatementstmt=null;

try {

connection=Basedao.getConnection();

stmt=connection.prepareCall("{call proc_insert_out(?,?,?,?,?,?)}");

stmt.setString(1,owners.getName());

stmt.setLong(2,owners.getAddress());

stmt.setString(3,owners.getHousenumber());

stmt.setString(4,owners.getWatermeter());

stmt.setLong(5,owners.getOwnertypeid());

//注册传出参数的类型

stmt.registerOutParameter(6, OracleTypes.NUMBER);

stmt.execute();

//在执行后拿出传出参数值

id=stmt.getLong(6);

} catch (SQLExceptione) {

thrownewRuntimeException(e);

}finally {

Basedao.CloseAll(null,stmt,connection);

}

returnid;

}

测试：

@Test

publicvoidproctestout(){

Ownersowners=newOwners();//("张三",2L,"4-4","2-5",1L);

owners.setName("张三");

owners.setAddress(2L);

owners.setHousenumber("4-5");

owners.setWatermeter("4-5");

owners.setOwnertypeid(1L);

System.out.println(ProcTest.add_out(owners));

}

7.4、触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(INSERT,UPDATE,DELETE)在指定的表上发出时，Oracle自动的执行触发器中定义的语句序列

触发器可用于

数据确认

实施复杂的安全性检查

审计，跟踪表上所作的数据操作

数据的备份与同步

触发器分类

前置触发器（BEFORE）

后置触发器（AFTER）

语法：

CREATE[ORREPLACE]TRIGGER TRIG_NAME

BEFORE|AFTER

[DELETE][[OR]INSERT][[OR]UPDATE[OF 列名]]

ON 表名

[FOREACHROW][WHEN 条件]

DECLARE

...

BEGIN

...

END;

FOR EACH ROW：用于标注该触发器是行级触发器或语句级触发器

触发器中触发语句与伪记录变量值

触发语句	:old	:new
INSERT	所有字段都是空(NULL)	将要插入的数据
UPDATE	更新前该行的值	更新后的值
DELETE	删除前该行的值	所有字段都是空(NULL)

前置触发器

-- 当用户输入本月累计表数后，自动计算出本月使用数

CREATEORREPLACETRIGGER TRIG_ACC

BEFORE

UPDATE OF NUM1

ON T_ACCOUNT

FOREACHROW

DECLARE

BEGIN

--通过伪记录变量修改usenum字段的值

:NEW.USENUM:=:NEW.NUM1-:NEW.NUM0;

END;

后置触发器

-- 当用户修改了业主信息表的数据时记录修改前与后的值

CREATEORREPLACETRIGGER TRIG_LOG

AFTER

UPDATE OF NAME

ON T_OWNERS

FOREACHROW

DECLARE

BEGIN

--向日志表插入数据

INSERTINTO T_OWNERS_LOG

VALUES(SYSDATE,:NEW.ID,:OLD.NAME,:NEW.NAME);

END;

小墨Sang

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Oracle-7 Oracle编程

PL/SQL( Procedure Language/SQL)是Oracle对sql语言的过程化扩展，指在SQL命令语言中增加了过程处理语句(如分支、循环等)，使SQL语言具有过程处理能力。//("张三",2L,"4-4","2-5",1L);PROC_INSERT_OUT('小李','6','6-2','30605',1,V_ID);CALL PROC_INSERT('小王','2','6-1','30475',2)PROC_INSERT('小王','2','6-1','30475',2);
复制链接

扫一扫

专栏目录