企业官网建设流程全解析

网站建设的流程ppt,网上110在线咨询,广州百度提升优化,门户网站的基本功能DM数据库存储过程实战指南 存储过程基础概念 存储过程是预编译的SQL语句集合#xff0c;具有以下优势#xff1a; 执行效率高#xff1a;减少SQL解析和编译时间安全性强#xff1a;通过权限控制保护数据网络开销低#xff1a;仅需传输调用指令维护便捷#xff1a;业务…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;