# 1. 需求分析
– 功能需求
– 批量导入用户数据
– 支持事务处理
– 支持错误处理
– 数据需求
– 用户ID
– 用户名
– 用户邮箱
– 用户状态
# 2. 存储过程开发
– 创建存储过程
SQL> CREATE PROCEDURE sp_batch_import_users()
AS
v_user_id INT;
v_user_name VARCHAR(50);
v_user_email VARCHAR(100);
v_user_status INT;
v_error_count INT := 0;
v_success_count INT := 0;
BEGIN
FOR cur_user IN (SELECT user_id, user_name, user_email, user_status FROM temp_users) LOOP
BEGIN
INSERT INTO fgedu_user (user_id, user_name, user_email, user_status)
VALUES (cur_user.user_id, cur_user.user_name, cur_user.user_email, cur_user.user_status);
v_success_count := v_success_count + 1;
EXCEPTION
WHEN OTHERS THEN
v_error_count := v_error_count + 1;
PRINT ‘Error importing user: ‘ || cur_user.user_id || ‘, Error: ‘ || SQLERRM;
END;
END LOOP;
PRINT ‘Import completed. Success: ‘ || v_success_count || ‘, Error: ‘ || v_error_count;
END;
# 3. 测试存储过程
– 准备测试数据
SQL> CREATE TABLE temp_users (
user_id INT,
user_name VARCHAR(50),
user_email VARCHAR(100),
user_status INT
);
SQL> INSERT INTO temp_users VALUES (4, ‘user4’, ‘user4@fgedu.net.cn’, 1);
SQL> INSERT INTO temp_users VALUES (5, ‘user5’, ‘user5@fgedu.net.cn’, 1);
SQL> INSERT INTO temp_users VALUES (6, ‘user6’, ‘user6@fgedu.net.cn’, 1);
– 执行存储过程
SQL> CALL sp_batch_import_users();
# 输出结果
# Import completed. Success: 3, Error: 0
# 4. 验证结果
– 查询导入的用户
SQL> SELECT * FROM fgedu_user WHERE user_id IN (4, 5, 6);
# 输出结果
# USER_ID USER_NAME USER_EMAIL USER_STATUS CREATE_TIME
# ——– ———- ——————– ———— ——————-
# 4 user4 user4@fgedu.net.cn 1 2024-01-01 10:00:00
# 5 user5 user5@fgedu.net.cn 1 2024-01-01 10:00:00
# 6 user6 user6@fgedu.net.cn 1 2024-01-01 10:00:00
# 5. 实施结果
– 存储过程开发成功
– 批量导入功能正常
– 事务处理正常
– 错误处理正常

# 1. 需求分析
– 功能需求
– 记录用户操作日志
– 记录操作类型
– 记录操作时间
– 数据需求
– 用户ID
– 操作类型
– 操作时间
# 2. 创建日志表
– 创建用户日志表
SQL> CREATE TABLE fgedu_user_log (
log_id INT PRIMARY KEY,
user_id INT,
action VARCHAR(50),
action_time TIMESTAMP DEFAULT SYSDATE
);
– 创建序列
SQL> CREATE SEQUENCE seq_fgedu_user_log;
# 3. 创建触发器
– 创建触发器
SQL> CREATE TRIGGER trg_fgedu_user_log
AFTER INSERT OR UPDATE OR DELETE ON fgedu_user
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO fgedu_user_log (log_id, user_id, action, action_time)
VALUES (seq_fgedu_user_log.NEXTVAL, :NEW.user_id, ‘INSERT’, SYSDATE);
ELSIF UPDATING THEN
INSERT INTO fgedu_user_log (log_id, user_id, action, action_time)
VALUES (seq_fgedu_user_log.NEXTVAL, :NEW.user_id, ‘UPDATE’, SYSDATE);
ELSIF DELETING THEN
INSERT INTO fgedu_user_log (log_id, user_id, action, action_time)
VALUES (seq_fgedu_user_log.NEXTVAL, :OLD.user_id, ‘DELETE’, SYSDATE);
END IF;
END;
# 4. 测试触发器
– 插入数据
SQL> INSERT INTO fgedu_user (user_id, user_name, user_email, user_status)
VALUES (7, ‘user7’, ‘user7@fgedu.net.cn’, 1);
– 更新数据
SQL> UPDATE fgedu_user SET user_status = 0 WHERE user_id = 7;
– 删除数据
SQL> DELETE FROM fgedu_user WHERE user_id = 7;
# 5. 验证结果
– 查询日志
SQL> SELECT * FROM fgedu_user_log WHERE user_id = 7;
# 输出结果
# LOG_ID USER_ID ACTION ACTION_TIME
# ——- ——– ——- ——————-
# 1 7 INSERT 2024-01-01 10:00:00
# 2 7 UPDATE 2024-01-01 10:00:01
# 3 7 DELETE 2024-01-01 10:00:02
# 6. 实施结果
– 触发器开发成功
– 日志记录功能正常
– 操作类型记录正常
– 操作时间记录正常

# 1. 需求分析
– 功能需求
– 用户查询
– 用户添加
– 用户更新
– 用户删除
– 技术需求
– Java语言
– JDBC连接
– 事务处理
# 2. 数据库连接
– 创建数据库连接类
import java.sql.*;
public class DMConnection {
private static final String URL = “jdbc:dm://fgedu.localhost:5236/fgedudb”;
private static final String USER = “SYSDBA”;
private static final String PASSWORD = “SYSDBA”;
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
}
# 3. 用户查询功能
– 创建用户查询类
import java.sql.*;
public class UserQuery {
public static void queryUser(int userId) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DMConnection.getConnection();
String sql = “SELECT * FROM fgedu_user WHERE user_id = ?”;
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userId);
rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println(“User ID: ” + rs.getInt(“user_id”));
System.out.println(“User Name: ” + rs.getString(“user_name”));
System.out.println(“User Email: ” + rs.getString(“user_email”));
System.out.println(“User Status: ” + rs.getInt(“user_status”));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
# 4. 用户添加功能
– 创建用户添加类
import java.sql.*;
public class UserAdd {
public static void addUser(int userId, String userName, String userEmail, int userStatus) {
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DMConnection.getConnection();
String sql = “INSERT INTO fgedu_user (user_id, user_name, user_email, user_status) VALUES (?, ?, ?, ?)”;
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, userId);
pstmt.setString(2, userName);
pstmt.setString(3, userEmail);
pstmt.setInt(4, userStatus);
int rows = pstmt.executeUpdate();
System.out.println(“Rows inserted: ” + rows);
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (pstmt != null) pstmt.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
# 5. 测试应用
– 创建测试类
public class UserManagementTest {
public static void main(String[] args) {
// 查询用户
UserQuery.queryUser(1);
// 添加用户
UserAdd.addUser(8, ‘user8’, ‘user8@fgedu.net.cn’, 1);
// 查询用户
UserQuery.queryUser(8);
}
}
# 输出结果
# User ID: 1
# User Name: fgedu_user1
# User Email: fgedu_user1@fgedu.net.cn
# User Status: 1
# Rows inserted: 1
# User ID: 8
# User Name: user8
# User Email: user8@fgedu.net.cn
# User Status: 1
# 6. 实施结果
– 应用开发成功
– 用户查询功能正常
– 用户添加功能正常
– 事务处理正常