Oracle存储过程是一种在Oracle数据库中创建、存储和执行的一组SQL语句的命名代码块。存储过程通常用于执行特定的任务或操作,并且可以在数据库中多次调用,以提高性能、可维护性和安全性。
一、基础存储过程语法
1、创建存储过程:
在Oracle中,可以使用CREATE PROCEDURE
语句创建存储过程。存储过程由PL/SQL语言编写,并且可以包含SQL查询、控制结构、变量声明等。例如:
CREATE PROCEDURE procedure_name
(parameter1 datatype, parameter2 datatype, ...)--括号内为参数
IS
-- 变量声明
BEGIN
-- 代码块
END;
2、参数:
存储过程可以接受零个或多个参数。参数可以是输入参数、输出参数或输入/输出参数,用于在调用过程时向其提供数据或从中检索数据。参数在存储过程的CREATE
语句中声明,并在调用过程时使用。例如:
CREATE PROCEDURE procedure_name
(parameter1 IN datatype, parameter2 OUT datatype, ...)
IS
...
3、执行存储过程:
存储过程可以使用EXECUTE
或EXEC
命令执行,也可以通过调用它们来执行。例如:
EXECUTE procedure_name;
--或者
CALL procedure_name;
4、修改存储过程:
可以使用ALTER PROCEDURE
语句修改现有的存储过程。例如:
ALTER PROCEDURE procedure_name
...
5、删除存储过程:
可以使用DROP PROCEDURE
语句删除存储过程。例如:
DROP PROCEDURE procedure_name;
6、存储过程的优势:
- 性能提升:存储过程在数据库中编译和存储,因此可以减少网络流量和数据库服务器的负载。
- 安全性:通过存储过程可以实现访问控制和数据保护,提高数据的安全性。
- 重用性:存储过程可以在多个应用程序中调用,提高代码的重用性和可维护性。
- 封装性:存储过程将一组SQL语句封装在一个单独的单元中,提高了代码的模块化程度。
二、增删改查简单例子
1、插入(INSERT)操作的存储过程示例:
CREATE OR REPLACE PROCEDURE insert_employee
(
p_employee_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2
)
IS
BEGIN
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (p_employee_id, p_first_name, p_last_name);
COMMIT;
END;
/
2、删除(DELETE)操作的存储过程示例:
CREATE OR REPLACE PROCEDURE delete_employee
(
p_employee_id IN NUMBER
)
IS
BEGIN
DELETE FROM employees
WHERE employee_id = p_employee_id;
COMMIT;
END;
/
3、更新(UPDATE)操作的存储过程示例:
CREATE OR REPLACE PROCEDURE update_employee
(
p_employee_id IN NUMBER,
p_first_name IN VARCHAR2,
p_last_name IN VARCHAR2
)
IS
BEGIN
UPDATE employees
SET first_name = p_first_name, last_name = p_last_name
WHERE employee_id = p_employee_id;
COMMIT;
END;
/
4、查询(SELECT)操作的存储过程示例:
CREATE OR REPLACE PROCEDURE select_employee
(
p_employee_id IN NUMBER
)
IS
BEGIN
FOR emp IN (SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = p_employee_id) LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', First Name: ' || emp.first_name || ', Last Name: ' || emp.last_name);
END LOOP;
END;
/
5、汇总示例
下面是一个简单的存储过程示例,用于实现增删改查(CRUD)操作:
CREATE OR REPLACE PROCEDURE CRUD_example
(
p_action IN VARCHAR2, -- 操作类型('INSERT'、'DELETE'、'UPDATE'、'SELECT')
p_employee_id IN NUMBER DEFAULT NULL, -- 员工ID(用于DELETE、UPDATE、SELECT操作)
p_first_name IN VARCHAR2 DEFAULT NULL, -- 员工名字(用于INSERT、UPDATE操作)
p_last_name IN VARCHAR2 DEFAULT NULL -- 员工姓氏(用于INSERT、UPDATE操作)
)
IS
BEGIN
IF p_action = 'INSERT' THEN
INSERT INTO employees (employee_id, first_name, last_name)
VALUES (p_employee_id, p_first_name, p_last_name);
ELSIF p_action = 'DELETE' THEN
DELETE FROM employees
WHERE employee_id = p_employee_id;
ELSIF p_action = 'UPDATE' THEN
UPDATE employees
SET first_name = p_first_name, last_name = p_last_name
WHERE employee_id = p_employee_id;
ELSIF p_action = 'SELECT' THEN
-- 简单查询示例,将结果打印到控制台
FOR emp IN (SELECT employee_id, first_name, last_name FROM employees WHERE employee_id = p_employee_id) LOOP
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', First Name: ' || emp.first_name || ', Last Name: ' || emp.last_name);
END LOOP;
ELSE
-- 其他操作类型处理
NULL;
END IF;
END;
/
三、游标
1、显示游标
CREATE OR REPLACE PROCEDURE display_employees_cursor
IS
-- 定义游标
CURSOR employee_cursor IS
SELECT employee_id, first_name, last_name
FROM employees;
-- 定义变量
v_employee_id employees.employee_id%TYPE;
v_first_name employees.first_name%TYPE;
v_last_name employees.last_name%TYPE;
BEGIN
-- 打开游标
OPEN employee_cursor;
-- 循环遍历游标
LOOP
-- 获取下一个记录
FETCH employee_cursor INTO v_employee_id, v_first_name, v_last_name;
-- 退出循环,如果游标没有更多记录
EXIT WHEN employee_cursor%NOTFOUND;
-- 输出员工信息到控制台
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_employee_id || ', First Name: ' || v_first_name || ', Last Name: ' || v_last_name);
END LOOP;
-- 关闭游标
CLOSE employee_cursor;
END;
/
在这个存储过程中,FOR emp IN (SELECT ...)
语句定义了一个隐式游标,它从员工表中选择员工的 employee_id
、first_name
和 last_name
列。然后,在 LOOP
中使用 FOR
循环遍历查询结果,对于每个迭代,游标会自动获取下一个记录,并将其存储在 emp
变量中。然后,存储过程将每个员工的信息打印到控制台上。
2、隐式游标
CREATE OR REPLACE PROCEDURE display_employees_implicit_cursor
IS
BEGIN
-- 循环遍历查询结果
FOR emp IN (SELECT employee_id, first_name, last_name FROM employees) LOOP
-- 输出员工信息到控制台
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', First Name: ' || emp.first_name || ', Last Name: ' || emp.last_name);
END LOOP;
END;
/
在这个存储过程中,FOR emp IN (SELECT ...)
语句定义了一个隐式游标,它从员工表中选择员工的 employee_id
、first_name
和 last_name
列。然后,在 LOOP
中使用 FOR
循环遍历查询结果,对于每个迭代,游标会自动获取下一个记录,并将其存储在 emp
变量中。然后,存储过程将每个员工的信息打印到控制台上。
注:在Oracle中,隐式游标(使用FOR...IN
语句声明的游标)会在循环结束时自动关闭。这意味着当循环遍历完游标返回的所有记录后,或者在循环内部使用EXIT
语句提前退出循环时,游标会被自动关闭。
隐式游标的打开和关闭是隐含在FOR...IN
循环语句中的,因此不需要显式地编写打开和关闭游标的代码。这简化了代码,并且使得代码更加易读和易于维护。
总之,在使用隐式游标时,无需显式地关闭游标,它会在循环结束时自动关闭。
3、区别点
-
显式游标循环:
- 使用
CURSOR...IS SELECT...
声明游标。 - 需要显式地打开和关闭游标。
- 使用
FETCH
语句获取下一个记录。 - 代码相对较为冗长,但更加灵活。
- 使用
-
隐式游标循环:
- 使用
FOR...IN (SELECT...) LOOP
声明和定义游标。 - 游标在
FOR
语句中隐式地打开和关闭,不需要显式操作。 - 在
FOR
循环中直接通过游标获取下一个记录,无需使用FETCH
语句。 - 代码较为简洁,更加紧凑。
- 使用
在实际使用中,选择使用哪种方式取决于个人偏好以及具体情况。隐式游标循环通常在简单的情况下更为方便,但在需要更多控制或处理游标的特殊情况下,显式游标循环可能更合适。