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的主要特性包括：
- 块结构：PL/SQL使用BLOCK结构作为其基本结构，每个PL/SQL程序都由构成PL/SQL块的SQL和PL/SQL语句组成。
- 可移植性：PL/SQL程序可以移植到所有支持Oracle的环境中。
- 与Oracle数据字典集成：PL/SQL程序可以直接访问Oracle数据库的数据字典，便于进行数据库管理。
- 模块化：支持过程和函数的创建，这些模块可以被存储在数据库中，并通过名称来调用，以实现代码的复用和模块化开发。
- 异常处理：提供了完善的异常处理机制，允许开发者编写健壮的代码来处理可能出现的错误和异常情况。
- 数据类型丰富：支持多种数据类型，包括标量数据类型（如NUMBER, VARCHAR2, DATE等）和复合数据类型（如RECORD, TABLE等）。
问题3：PL/SQL中的存储过程和函数有什么区别？
答案：PL/SQL中的存储过程和函数都是命名的PL/SQL块，被编译后存储在数据库中，以备执行。它们的主要区别在于：
- 返回值：函数（FUNCTION）总是向调用者返回一个值，而存储过程（PROCEDURE）则不返回任何值。
- 调用方式：函数可以作为表达式的一部分被调用，而存储过程通常通过执行（EXECUTE）语句来调用。
- 用途：函数一般用于计算并返回一个结果，而存储过程则用于执行一系列的操作，这些操作可能不直接返回结果给调用者，而是通过修改数据库中的数据或状态来实现。
问题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
BEGINres:= num1+num2;
END;
/
Calling Procedure from other subprogram
DECLAREvar1 NUMBER := 10;var2 NUMBER := 20;res NUMBER;
BEGINsp_proc_number_add(10, 5,res);dbms_output.put_line(' run dbms_output.put_line is :'||res);
END;
/
-- 调用
HR@orcl> DECLARE2 var1 NUMBER := 10;3 var2 NUMBER := 20;4 res NUMBER;5 BEGIN6 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 :15PL/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-- 处理异常1WHEN exception2 THEN-- 处理异常2...
END;
/
5.1、创建一个函数返回员工工资总和的
例如，创建一个返回部门ID=20的所有员工工资总和的函数：
CREATE OR REPLACE FUNCTION get_employee_salary_sum
(dept_id NUMBER)
RETURN NUMBER ISv_sum NUMBER := 0;
BEGINSELECT SUM(salary) INTO v_sumFROM employeesWHERE department_id = dept_id;RETURN v_sum;
END;
/
-- 调用函数（部门ID=20是入参）
HR@orcl> SELECT get_employee_salary_sum(20) as v1 FROM DUAL;V1
----------190005.2、创建一个函数实现两个整数相加的和
CREATE OR REPLACE FUNCTION fn_get_number_add
(num1 IN NUMBER,num2 IN NUMBER
)
RETURN NUMBER
IS
BEGINRETURN 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> DECLARE2 var1 NUMBER := 10;3 var2 NUMBER := 20;4 res NUMBER;5 BEGIN6 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 :30PL/SQL procedure successfully completed.
问题6：如何调用一个PL/SQL存储过程？
答案：调用PL/SQL存储过程的基本语法如下：
EXECUTE procedure_name(parameter1, parameter2, ...);
或者，在PL/SQL块中调用：
BEGINprocedure_name(parameter1, parameter2, ...);
END;
/
例如，调用上述假设的update_employee_status存储过程：
EXECUTE update_employee_status(101, 'ACTIVE');
或者在PL/SQL块中：
BEGINupdate_employee_status(101, 'ACTIVE');
END;
/
请注意，这里的update_employee_status是一个假设的存储过程名称，你需要用实际的存储过程名称来替换它。