Oracle PL/SQL存储过程和函数简单示例

以下是关于Oracle PL/SQL存储过程和函数的一些问题和答案：

问题1：什么是Oracle PL/SQL？

答案：Oracle PL/SQL（Procedural Language Extensions to SQL）是Oracle对SQL的过程语言扩展，它是一种编程语言，支持SQL语句的嵌入以及过程化的控制结构，如循环、条件分支等。PL/SQL由Oracle公司在90年代初开发，旨在增强SQL的功能，克服其非过程性的局限性。

问题2：PL/SQL的主要特性有哪些？

答案：PL/SQL的主要特性包括：

1. 块结构：PL/SQL使用BLOCK结构作为其基本结构，每个PL/SQL程序都由构成PL/SQL块的SQL和PL/SQL语句组成。
2. 可移植性：PL/SQL程序可以移植到所有支持Oracle的环境中。
3. 与Oracle数据字典集成：PL/SQL程序可以直接访问Oracle数据库的数据字典，便于进行数据库管理。
4. 模块化：支持过程和函数的创建，这些模块可以被存储在数据库中，并通过名称来调用，以实现代码的复用和模块化开发。
5. 异常处理：提供了完善的异常处理机制，允许开发者编写健壮的代码来处理可能出现的错误和异常情况。
6. 数据类型丰富：支持多种数据类型，包括标量数据类型（如NUMBER, VARCHAR2, DATE等）和复合数据类型（如RECORD, TABLE等）。

问题3：PL/SQL中的存储过程和函数有什么区别？

答案：PL/SQL中的存储过程和函数都是命名的PL/SQL块，被编译后存储在数据库中，以备执行。它们的主要区别在于：

1. 返回值：函数（FUNCTION）总是向调用者返回一个值，而存储过程（PROCEDURE）则不返回任何值。
2. 调用方式：函数可以作为表达式的一部分被调用，而存储过程通常通过执行（EXECUTE）语句来调用。
3. 用途：函数一般用于计算并返回一个结果，而存储过程则用于执行一系列的操作，这些操作可能不直接返回结果给调用者，而是通过修改数据库中的数据或状态来实现。

问题4：如何创建一个简单的储过程PROCEDURE？

CREATE OR REPLACE PROCEDURE <your_procedure>
(
<parameterl IN/OUT <datatype>
..
.
)
[ IS | AS ]<declaration_part>
BEGIN
-- 执行逻辑部分
<execution part>
-- 异常处理
EXCEPTION
<exception handling part>
END;

4.1、创建一个函数实现两个整数相加的和

CREATE OR REPLACE PROCEDURE sp_proc_number_add
(
    num1 IN NUMBER,
    num2 IN NUMBER,
    res out NUMBER
)
IS
BEGIN
    res:= num1+num2;
END;
/

Calling Procedure from other subprogram

DECLARE
    var1 NUMBER := 10;
    var2 NUMBER := 20;
    res  NUMBER;
BEGIN
    sp_proc_number_add(10, 5,res);
    dbms_output.put_line(' run dbms_output.put_line is :'||res);
END;
/
-- 调用
HR@orcl> DECLARE
  2      var1 NUMBER := 10;
  3      var2 NUMBER := 20;
  4      res  NUMBER;
  5  BEGIN
  6      sp_proc_number_add(var1, var2,res);
  7      dbms_output.put_line(' run dbms_output.put_line is :'||res);
  8  END;
  9  /
run dbms_output.put_line is :15

PL/SQL procedure successfully completed.

问题5：如何创建一个简单的函数FUNCTION？

答案：创建一个简单的PL/SQL函数的基本语法如下：

CREATE OR REPLACE FUNCTION function_name
(parameter1 datatype1 [, parameter2 datatype2, ...])
RETURN return_datatype IS
-- 变量声明
BEGIN
    -- 函数体
    RETURN value;
EXCEPTION
    -- 异常处理
    WHEN exception1 THEN
        -- 处理异常1
    WHEN exception2 THEN
        -- 处理异常2
    ...
END;
/

5.1、创建一个函数返回员工工资总和的

例如，创建一个返回部门ID=20的所有员工工资总和的函数：

CREATE OR REPLACE FUNCTION get_employee_salary_sum
(dept_id NUMBER)
RETURN NUMBER IS
    v_sum NUMBER := 0;
BEGIN
    SELECT SUM(salary) INTO v_sum
    FROM employees
    WHERE department_id = dept_id;
    RETURN v_sum;
END;
/
-- 调用函数（部门ID=20是入参）
HR@orcl> SELECT get_employee_salary_sum(20) as v1 FROM DUAL;

        V1
----------
     19000

5.2、创建一个函数实现两个整数相加的和

CREATE OR REPLACE FUNCTION fn_get_number_add
(
    num1 IN NUMBER,
    num2 IN NUMBER
)
RETURN NUMBER
IS
BEGIN
  RETURN num1+num2;
END;
/

调用函数fn_get_number_add

HR@orcl> SELECT fn_get_number_add(10, 5) as v1 FROM DUAL;

        V1
----------
        15
-- OR 
HR@orcl> DECLARE
  2      var1 NUMBER := 10;
  3      var2 NUMBER := 20;
  4      res  NUMBER;
  5  BEGIN
  6      res := fn_get_number_add(var1, var2);
  7      dbms_output.put_line(' run dbms_output.put_line is :'||res);
  8  END;
  9  /
run dbms_output.put_line is :30

PL/SQL procedure successfully completed.

问题6：如何调用一个PL/SQL存储过程？

答案：调用PL/SQL存储过程的基本语法如下：

EXECUTE procedure_name(parameter1, parameter2, ...);

或者，在PL/SQL块中调用：

BEGIN
    procedure_name(parameter1, parameter2, ...);
END;
/

例如，调用上述假设的update_employee_status存储过程：

EXECUTE update_employee_status(101, 'ACTIVE');

或者在PL/SQL块中：

BEGIN
    update_employee_status(101, 'ACTIVE');
END;
/

请注意，这里的update_employee_status是一个假设的存储过程名称，你需要用实际的存储过程名称来替换它。