DM数据库存储过程实战：从入门到精通

DM数据库存储过程实战指南

存储过程基础概念

存储过程是预编译的SQL语句集合，具有以下优势：

• 执行效率高：减少SQL解析和编译时间
• 安全性强：通过权限控制保护数据
• 网络开销低：仅需传输调用指令
• 维护便捷：业务逻辑集中管理

DM数据库存储过程语法结构：

CREATE OR REPLACE PROCEDURE procedure_name [(param1 [IN|OUT|IN OUT] datatype, ...)] AS [declaration_section] BEGIN execution_section [EXCEPTION exception_section] END;

员工统计存储过程开发

测试环境搭建：

CREATE TABLE city ( city_id VARCHAR(3) PRIMARY KEY, city_name VARCHAR(40) NOT NULL ); CREATE TABLE employee ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50), city_id VARCHAR(3), department VARCHAR(50) ); INSERT INTO city VALUES ('BJ', '北京'), ('SH', '上海'); INSERT INTO employee VALUES (1, '张三', 'BJ', '技术部'), (2, '李四', 'BJ', '销售部'), (3, '王五', 'SH', '技术部');

统计存储过程实现：

CREATE OR REPLACE PROCEDURE p_employee_count_by_city AS v_city_name VARCHAR(40); v_count INT; CURSOR city_cursor IS SELECT c.city_name, COUNT(e.employee_id) FROM city c LEFT JOIN employee e ON c.city_id = e.city_id GROUP BY c.city_name ORDER BY c.city_name; BEGIN DBMS_OUTPUT.PUT_LINE('===== 城市员工统计 ====='); DBMS_OUTPUT.PUT_LINE('城市名称 | 员工数量'); DBMS_OUTPUT.PUT_LINE('-------------------------'); OPEN city_cursor; LOOP FETCH city_cursor INTO v_city_name, v_count; EXIT WHEN city_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(RPAD(v_city_name, 10) || ' | ' || v_count); END LOOP; CLOSE city_cursor; DBMS_OUTPUT.PUT_LINE('统计完成，总计城市数：' || city_cursor%ROWCOUNT); END;

调试与异常处理

增强版存储过程示例：

CREATE OR REPLACE PROCEDURE p_employee_count_by_city_v2 AS v_total_cities INT; v_total_employees INT; BEGIN SELECT COUNT(*) INTO v_total_cities FROM city; SELECT COUNT(*) INTO v_total_employees FROM employee; IF v_total_cities = 0 THEN DBMS_OUTPUT.PUT_LINE('错误：城市表无数据'); RETURN; END IF; IF v_total_employees = 0 THEN DBMS_OUTPUT.PUT_LINE('警告：员工表无数据'); END IF; p_employee_count_by_city; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('错误：' || SQLCODE || ' - ' || SQLERRM); DBMS_OUTPUT.PUT_LINE('建议检查表结构和数据完整性'); END;

性能优化方案

索引优化：

CREATE INDEX idx_employee_city ON employee(city_id); CREATE INDEX idx_city_id ON city(city_id);

参数化存储过程：

CREATE OR REPLACE PROCEDURE p_employee_count_by_city_param( p_min_count IN INT DEFAULT 0) AS BEGIN FOR rec IN ( SELECT c.city_name, COUNT(e.employee_id) as emp_count FROM city c LEFT JOIN employee e ON c.city_id = e.city_id GROUP BY c.city_name HAVING COUNT(e.employee_id) >= p_min_count ORDER BY c.city_name ) LOOP DBMS_OUTPUT.PUT_LINE(rec.city_name || ' : ' || rec.emp_count); END LOOP; END;