oracel 21c sql:

-- 创建 School 表
CREATE TABLE School (SchoolId CHAR(5) NOT NULL,SchoolName NVARCHAR2(500) NOT NULL,SchoolTelNo VARCHAR2(8) NULL,PRIMARY KEY (SchoolId)
);CREATE OR REPLACE PROCEDURE addschool(p_school_id IN CHAR,p_school_name IN NVARCHAR2,p_school_tel_no IN VARCHAR2
) AS
BEGININSERT INTO School (SchoolId, SchoolName, SchoolTelNo)VALUES (p_school_id, p_school_name, p_school_tel_no);COMMIT;
END;CREATE OR REPLACE PROCEDURE deleteschool(p_school_id IN CHAR
) AS
BEGINDELETE FROM School WHERE SchoolId = p_school_id;COMMIT;
END;CREATE OR REPLACE PROCEDURE updateschool(p_school_id IN CHAR,p_school_name IN NVARCHAR2,p_school_tel_no IN VARCHAR2
) AS
BEGINUPDATE SchoolSET SchoolName = p_school_name, SchoolTelNo = p_school_tel_noWHERE SchoolId = p_school_id;COMMIT;
END;CREATE OR REPLACE PROCEDURE getschoolbyid(p_school_id IN CHAR,p_school_name OUT NVARCHAR2,p_school_tel_no OUT VARCHAR2
) AS
BEGINSELECT SchoolName, SchoolTelNoINTO p_school_name, p_school_tel_noFROM SchoolWHERE SchoolId = p_school_id;
END;CREATE OR REPLACE PROCEDURE getallschools(p_cursor OUT SYS_REFCURSOR
) AS
BEGINOPEN p_cursor FOR SELECT * FROM School;
END;

复习一下代码

java:

/*** encoding: utf-8* 版权所有 2025 涂聚文有限公司* 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎* 描述：* # Author    : geovindu,Geovin Du 涂聚文.* # IDE       : IntelliJ IDEA 2023.1 Java 21* # database  : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1* # OS        : window10* # Datetime  : 2025 - 2025/2/9 - 16:30* # User      : geovindu* # Product   : IntelliJ IDEA* # Project   : oracledemo* # File      : Model/School.java  类* # explain   : 学习**/package Geovin.Model;/*** 学校表 实体*/
public class School {/****/private String schoolId;/****/private String schoolName;/****/private String schoolTelNo;/*** 构造方法*/public School() {}/**** @param schoolId* @param schoolName* @param schoolTelNo*/public School(String schoolId, String schoolName, String schoolTelNo) {this.schoolId = schoolId;this.schoolName = schoolName;this.schoolTelNo = schoolTelNo;}// Getter 和 Setter 方法/**** @return*/public String getSchoolId() {return schoolId;}/**** @param schoolId*/public void setSchoolId(String schoolId) {this.schoolId = schoolId;}/**** @return*/public String getSchoolName() {return schoolName;}/**** @param schoolName*/public void setSchoolName(String schoolName) {this.schoolName = schoolName;}/**** @return*/public String getSchoolTelNo() {return schoolTelNo;}/**** @param schoolTelNo*/public void setSchoolTelNo(String schoolTelNo) {this.schoolTelNo = schoolTelNo;}/**** @return*/@Overridepublic String toString() {return "School [schoolId=" + schoolId + ", schoolName=" + schoolName + ", schoolTelNo=" + schoolTelNo + "]";}
}/*** encoding: utf-8* 版权所有 2025 涂聚文有限公司* 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎* 描述：* # Author    : geovindu,Geovin Du 涂聚文.* # IDE       : IntelliJ IDEA 2023.1 Java 21* # database  : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1* # OS        : window10* # Datetime  : 2025 - 2025/2/9 - 16:33* # User      : geovindu* # Product   : IntelliJ IDEA* # Project   : oracledemo* # File      : Interface/ISchool.java  类* # explain   : 学习**/package Geovin.Interface;
import java.util.List;
import Geovin.Model.School;/*** 接口*/
public interface  ISchool {/*** 添加学校* @param school*/void addSchool(School school);/*** 删除学校* @param schoolId*/void deleteSchool(String schoolId);/*** 更新学校* @param school*/void updateSchool(School school);/*** 查询学校* @param schoolId* @return*/School getSchoolById(String schoolId);/*** 查询所有学校* @return*/List<School> getAllSchools();/*** 储存过程 添加* @param school* @return*/boolean addProc(School school);/*** 储存过程* @param schoolId* @return*/boolean deleteProc(String schoolId);/*** 储存过程* @param school* @return*/boolean updateProc(School school);/*** 储存过程* @param schoolId* @return*/School getProcById(String schoolId);/*** 储存过程* @return*/List<School> getAllProc();}/*** encoding: utf-8* 版权所有 2025 涂聚文有限公司* 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎* 描述：* # Author    : geovindu,Geovin Du 涂聚文.* # IDE       : IntelliJ IDEA 2023.1 Java 21* # database  : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1* # OS        : window10* # Datetime  : 2025 - 2025/2/9 - 16:36* # User      : geovindu* # Product   : IntelliJ IDEA* # Project   : oracledemo* # File      : DAL/SchoolDAL.java  类* # explain   : 学习**/package Geovin.DAL;
import java.sql.*;
import java.sql.Driver.*;
import oracle.jdbc.*;
import java.util.ArrayList;
import java.util.List;
import Geovin.Model.School;
import Geovin.Interface.ISchool;
import Geovin.UtilitieDB.SqlHelper;/*** 数据处理*/
public class SchoolDAL implements ISchool {/*** 构造方法*/public SchoolDAL() {}/**** @param school*/@Overridepublic void addSchool(School school) {String sql = "INSERT INTO School (SchoolId, SchoolName, SchoolTelNo) VALUES (?, ?, ?)";try (PreparedStatement pstmt = SqlHelper.getConnect().prepareStatement(sql)) {pstmt.setString(1, school.getSchoolId());pstmt.setString(2, school.getSchoolName());pstmt.setString(3, school.getSchoolTelNo());pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();}}/**** @param schoolId*/@Overridepublic void deleteSchool(String schoolId) {String sql = "DELETE FROM School WHERE SchoolId = ?";try (PreparedStatement pstmt = SqlHelper.getConnect().prepareStatement(sql)) {pstmt.setString(1, schoolId);pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();}}/**** @param school*/@Overridepublic void updateSchool(School school) {String sql = "UPDATE School SET SchoolName = ?, SchoolTelNo = ? WHERE SchoolId = ?";try (PreparedStatement pstmt = SqlHelper.getConnect().prepareStatement(sql)) {pstmt.setString(1, school.getSchoolName());pstmt.setString(2, school.getSchoolTelNo());pstmt.setString(3, school.getSchoolId());pstmt.executeUpdate();} catch (SQLException e) {e.printStackTrace();}}/**** @param schoolId* @return*/@Overridepublic School getSchoolById(String schoolId) {String sql = "SELECT * FROM School WHERE SchoolId = ?";try (PreparedStatement pstmt = SqlHelper.getConnect().prepareStatement(sql)) {pstmt.setString(1, schoolId);ResultSet rs = pstmt.executeQuery();if (rs.next()) {return new School(rs.getString("SchoolId"),rs.getString("SchoolName"),rs.getString("SchoolTelNo"));}} catch (SQLException e) {e.printStackTrace();}return null;}/**** @return*/@Overridepublic List<School> getAllSchools() {List<School> schools = new ArrayList<>();String sql = "SELECT * FROM School";try (Statement stmt =SqlHelper.getConnect().createStatement();ResultSet rs = stmt.executeQuery(sql)) {while (rs.next()) {schools.add(new School(rs.getString("SchoolId"),rs.getString("SchoolName"),rs.getString("SchoolTelNo")));}} catch (SQLException e) {e.printStackTrace();}return schools;}/*** 储存过程 添加* @param school* @return*/public boolean addProc(School school) {String sql = "{call ADDSCHOOL(?,?,?)}";try (CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {cstmt.setString(1, school.getSchoolId());cstmt.setString(2, school.getSchoolName());cstmt.setString(3, school.getSchoolTelNo());cstmt.execute();return true;} catch (SQLException e) {e.printStackTrace();return false;}}/*** 储存过程* @param schoolId* @return*/public boolean deleteProc(String schoolId) {String sql = "{call DELETESCHOOL(?)}";try (CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {cstmt.setString(1, schoolId);cstmt.execute();return true;} catch (SQLException e) {e.printStackTrace();return false;}}/*** 储存过程* @param school* @return*/public boolean updateProc(School school) {String sql = "{call UPDATESCHOOL(?,?,?)}";try (CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {cstmt.setString(1, school.getSchoolId());cstmt.setString(2, school.getSchoolName());cstmt.setString(3, school.getSchoolTelNo());cstmt.execute();return true;} catch (SQLException e) {e.printStackTrace();return false;}}/*** 储存过程* @param schoolId* @return*/public School getProcById(String schoolId) {String sql = "{call GETSCHOOLBYID(?,?)}";try (CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {cstmt.setString(1, schoolId);cstmt.registerOutParameter(2, OracleTypes.CURSOR);cstmt.execute();ResultSet rs = (ResultSet) cstmt.getObject(2);if (rs.next()) {String id = rs.getString("SchoolId");String name = rs.getString("SchoolName");String telNo = rs.getString("SchoolTelNo");return new School(id, name, telNo);}} catch (SQLException e) {e.printStackTrace();}return null;}/*** 储存过程* @return*/public List<School> getAllProc() {List<School> schoolList = new ArrayList<>();String sql = "{call GETALLSCHOOLS(?)}";try (CallableStatement cstmt = SqlHelper.getConnect().prepareCall(sql)) {cstmt.registerOutParameter(1, OracleTypes.CURSOR);cstmt.execute();ResultSet rs = (ResultSet) cstmt.getObject(1);while (rs.next()) {String id = rs.getString("SchoolId");String name = rs.getString("SchoolName");String telNo = rs.getString("SchoolTelNo");schoolList.add(new School(id, name, telNo));}} catch (SQLException e) {e.printStackTrace();}return schoolList;}}/*** encoding: utf-8* 版权所有 2025 涂聚文有限公司* 许可信息查看：言語成了邀功盡責的功臣，還需要行爲每日來值班嗎* 描述：* # Author    : geovindu,Geovin Du 涂聚文.* # IDE       : IntelliJ IDEA 2023.1 Java 21* # database  : Oracle21c,MySQL 9.0,SQL Server 2019,PostgreSQL 17.1* # OS        : window10* # Datetime  : 2025 - 2025/2/9 - 16:42* # User      : geovindu* # Product   : IntelliJ IDEA* # Project   : oracledemo* # File      : BLL/SchoolBLL.java  类* # explain   : 学习**/package Geovin.BLL;import Geovin.Interface.ISchool;
import Geovin.Factory.AbstractFactory;
import Geovin.Model.School;
import java.util.List;/*** 业务处理层*/
public class SchoolBLL {private ISchool dal;public SchoolBLL() {dal = AbstractFactory.getDAL(); // 通过工厂获取 DAL 实例}/*** 添加学校* @param school*/public void addSchool(School school) {dal.addSchool(school);}/*** 删除学校* @param schoolId*/public void deleteSchool(String schoolId) {dal.deleteSchool(schoolId);}/*** 更新学校* @param school*/public void updateSchool(School school) {dal.updateSchool(school);}/*** 查询学校* @param schoolId* @return*/public School getSchoolById(String schoolId) {return dal.getSchoolById(schoolId);}/*** 查询所有学校* @return*/public List<School> getAllSchools() {return dal.getAllSchools();}/*** 储存过程 添加* @param school* @return*/public boolean addProc(School school){return dal.addProc(school);}/*** 储存过程* @param schoolId* @return*/public boolean deleteProc(String schoolId){return dal.deleteProc(schoolId);}/*** 储存过程* @param school* @return*/public boolean updateProc(School school){return dal.updateProc(school);}/*** 储存过程* @param schoolId* @return*/public School getProcById(String schoolId){return dal.getProcById(schoolId);}/*** 储存过程* @return*/public List<School> getAllProc(){return dal.getAllProc();}}

调用：

//sql
//schoolBLL.getAllSchools().forEach(System.out::println);
schoolBLL.getAllProc().forEach(System.out::println);

输出：