PL/SQL存储过程

一只特立独行的_小猫

已于 2022-06-05 00:02:33 修改

阅读量3.3k

点赞数 3

分类专栏： PLSQL 文章标签： sql linq 数据库

于 2022-06-05 00:01:25 首次发布

本文链接：https://blog.csdn.net/weixin_42051619/article/details/125126721

版权

PLSQL 专栏收录该内容

18 篇文章 3 订阅

订阅专栏

PL/SQL存储过程

存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑。不能在赋值语句（如函数）的右侧使用存储过程。

1、语法

CREATE [OR REPLACE] PROCEDURE <procedure_name> [(
<parameter_name_1> [IN | OUT | IN OUT] <parameter_data_type_1> [DEFAULT value_1],
<parameter_name_2> [IN | OUT | IN OUT] <parameter_data_type_2> [DEFAULT value_2],
...
<parameter_name_N> [IN | OUT | IN OUT] <parameter_data_type_N> [DEFAULT value_N])]
IS
    --声明部分
BEGIN
    -- 执行部分
EXCEPTION
    -- 可选的异常错误处理程序
END procedure_name; 
/

<procedure_name>是PROCEDURE的名称；<parameter_name>是要传递的参数的名称；<parameter_data_type>是相应参数的PL / SQL数据类型。

2、实例

-- 创建表
CREATE TABLE LOGTABLE (
    USERID VARCHAR2(100),
    LOGDATE DATE
);

-- 创建存储过程
CREATE OR REPLACE PROCEDURE LOGEXECUTION
IS
BEGIN
    INSERT INTO logtable (USERID, LOGDATE) VALUES (USER, SYSDATE);
END LOGEXECUTION;
/

-- 调用存储过程
DECLARE
    V_INSERT_COUNT NUMBER;
BEGIN
    LOGEXECUTION;
    SELECT COUNT(*) INTO V_INSERT_COUNT FROM LOGTABLE;
    DBMS_OUTPUT.PUT_LINE(V_INSERT_COUNT);
END;

3、调用存储过程

存储过程建立完成后，只要通过授权，用户就可以在SQLPLUS 、ORACLE开发工具或第三方开发工具中来调用运行。对于参数的传递也有三种：按位置传递、按名称传递和组合传递，传递方法与函数一样。ORACLE 使用EXECUTE 语句来实现对存储过程的调用，如：

EXECUTE logexecution;

（1）计算指定部门的工资总和，并统计其中的职工数量。

CREATE OR REPLACE PROCEDURE proc_demo
(
    dept_no NUMBER DEFAULT 10,
    sal_sum OUT NUMBER,
    emp_count OUT NUMBER
)
IS
BEGIN
    SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
    FROM employees WHERE department_id = dept_no;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('data not found');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;
/

DECLARE
    V_num NUMBER;
    V_sum NUMBER(8, 2);
BEGIN
    Proc_demo(30, v_sum, v_num);
    DBMS_OUTPUT.PUT_LINE('温馨提示:30号部门工资总和:'||v_sum||',人数:'||v_num);

    Proc_demo(sal_sum => v_sum, emp_count => v_num);
    DBMS_OUTPUT.PUT_LINE('温馨提示:10号部门工资总和:'||v_sum||',人数:'||v_num);
END;
/

在PL/SQL 程序中还可以在块内建立本地函数和过程，这些函数和过程不存储在数据库中，但可以在创建它们的PL/SQL 程序中被重复调用。本地函数和过程在PL/SQL 块的声明部分定义，它们的语法格式与存储函数和过程相同，但不能使用CREATE OR REPLACE 关键字。

如：建立本地过程，用于计算指定部门的工资总和，并统计其中的职工数量

DECLARE
    V_num NUMBER;
    V_sum NUMBER(8, 2);
PROCEDURE proc_demo
(
    Dept_no NUMBER DEFAULT 10,
    Sal_sum OUT NUMBER,
    Emp_count OUT NUMBER
)
IS
BEGIN
    SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count 
    FROM employees WHERE department_id=dept_no;
EXCEPTION
   WHEN NO_DATA_FOUND THEN 
      DBMS_OUTPUT.PUT_LINE('data not found!');
   WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;
-- 调用存储过程
BEGIN
    Proc_demo(30, v_sum, v_num);
    DBMS_OUTPUT.PUT_LINE('30号部门工资总和:'||v_sum||'，人数：'||v_num);

    Proc_demo(sal_sum => v_sum, emp_count => v_num);
    DBMS_OUTPUT.PUT_LINE('10号部门工资总和:'||v_sum||'，人数：'||v_num);
END;
/

4、删除存储过程和函数

（1）删除存储过程

可以使用DROP PROCEDURE命令对不需要的过程进行删除，语法如下：

DROP PROCEDURE [user.]Procudure_name;

（2）删除存储函数

可以使用DROP FUNCTION 命令对不需要的函数进行删除，语法如下：

DROP FUNCTION [user.]Function_name;

5、存储过程与函数的比较

1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。

2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

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

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