以下是关于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
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
是一个假设的存储过程名称,你需要用实际的存储过程名称来替换它。