Orcale数据库系统设计（使用 PLSQL Developer）

Question

实验内容
1、根据系统需求管理表空间
2、根据系统需求管理用户以及相应的权限
3、根据系统需求管理系统表对象
4、根据系统需求维护和管理表数据，其中包含序列的使用
5、根据系统需求进行数据的查询
6、根据系统需求进行数据业务的处理，完成数据库编程功能
7、根据需求进行数据的导入导出

Answer 1

引自免费微信小程序：皆我百晓生：

1. 管理表空间

1. 增加新的表空间：tp姓名全拼

   CREATE TABLESPACE tp_fullname;
   

2. 修改表空间的大小：增加新的数据文件

   ALTER TABLESPACE tp_fullname ADD DATAFILE '/path/to/filename.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M;
   

2. 管理用户以及权限

1. 根据实际情况设计一个概要文件，对资源进行相应的限制

概要文件示例：

CREATE PROFILE my_profile LIMIT
    CPU_PER_SESSION 1000
    CPU_PER_CALL 10
    SESSIONS_PER_USER 10
    LOGICAL_READS_PER_SESSION 10000
    IDLE_TIME 30;

2/3) 创建新的用户，概要文件为上面创建的文件，默认表空间为上面创建的表空间，并给新用户赋予对应的权限

CREATE USER new_user IDENTIFIED BY password
    DEFAULT TABLESPACE tp_fullname
    PROFILE my_profile;

1. 登录连接访问

   CONN new_user/password;
   

3. 管理系统表对象
根据系统需求的功能图、ER图、建表语句、数据类型和完整性约束条件，创建相应的表。

示例：

CREATE TABLE users (
    uiid VARCHAR2(12) CONSTRAINT users_pk PRIMARY KEY,
    uname VARCHAR2(20) NOT NULL,
    upassw VARCHAR2(30),
    uemail VARCHAR2(50),
    ugrade VARCHAR2(1) CHECK (ugrade IN ('A', 'B', 'C', 'D')),
    CONSTRAINT users_ugrade_ck CHECK (ugrade IN ('A', 'B', 'C', 'D'))
);

CREATE TABLE ttopic (
    tid NUMBER(10) CONSTRAINT ttopic_pk PRIMARY KEY,
    tsubject VARCHAR2(40) NOT NULL,
    tcontents VARCHAR2(100),
    tuser VARCHAR2(12) REFERENCES users(uiid),
    tdate DATE DEFAULT SYSDATE,
    treply NUMBER(20) DEFAULT 0,
    tclick NUMBER(5) DEFAULT 0
);

CREATE TABLE treply (
    rid VARCHAR2(10) CONSTRAINT treply_pk PRIMARY KEY,
    tid NUMBER(10) REFERENCES ttopic(tid),
    rsubject VARCHAR2(40) NOT NULL,
    rcontents VARCHAR2(100),
    ruser VARCHAR2(12) REFERENCES users(uiid),
    rdate DATE DEFAULT SYSDATE
);

4. 数据业务的处理
1)编写一个存储过程，实现新用户注册的功能，用户编号使用序列，用户等级默认为B

CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE register_user(
    p_uname IN users.uname%TYPE,
    p_upassw IN users.upassw%TYPE,
    p_uemail IN users.uemail%TYPE,
    p_ugrade IN users.ugrade%TYPE DEFAULT 'B'
)
AS
    v_uiid users.uiid%TYPE;
BEGIN
    SELECT 'U' || user_seq.NEXTVAL INTO v_uiid FROM DUAL;
    
    INSERT INTO users (uiid, uname, upassw, uemail, ugrade)
    VALUES (v_uiid, p_uname, p_upassw, p_uemail, p_ugrade);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('User registered successfully!');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/

2)编写一个存储过程，实现发表一条新帖子的功能，主题流水号使用序列，用户ID为当前登录的用户，发表日期为系统时间

CREATE SEQUENCE topic_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE post_topic(
    p_tsubject IN ttopic.tsubject%TYPE,
    p_tcontents IN ttopic.tcontents%TYPE,
    p_tuser IN ttopic.tuser%TYPE
)
AS
    v_tid ttopic.tid%TYPE;
BEGIN
    SELECT topic_seq.NEXTVAL INTO v_tid FROM DUAL;
    
    INSERT INTO ttopic (tid, tsubject, tcontents, tuser, tdate)
    VALUES (v_tid, p_tsubject, p_tcontents, p_tuser, SYSDATE);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Topic posted successfully!');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/

3)编写一个存储过程，实现回复一条新帖子的功能，回帖流水号使用序列，用户ID为当前登录的用户，回复日期为系统时间

CREATE SEQUENCE reply_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE PROCEDURE post_reply(
    p_rsubject IN treply.rsubject%TYPE,
    p_rcontents IN treply.rcontents%TYPE,
    p_ruser IN treply.ruser%TYPE,
    p_tid IN ttopic.tid%TYPE
)
AS
    v_rid treply.rid%TYPE;
BEGIN
    SELECT 'R' || reply_seq.NEXTVAL INTO v_rid FROM DUAL;
    
    INSERT INTO treply (rid, tid, rsubject, rcontents, ruser, rdate)
    VALUES (v_rid, p_tid, p_rsubject, p_rcontents, p_ruser, SYSDATE);
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Reply posted successfully!');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/

4)创建一个函数，输入参数为用户ID，返回该用户发的帖子数和共回复的帖子数

CREATE OR REPLACE FUNCTION get_post_reply_count(
    p_user_id IN users.uiid%TYPE
)
RETURN VARCHAR2
AS
    v_post_count NUMBER;
    v_reply_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_post_count FROM ttopic WHERE tuser = p_user_id;
    SELECT COUNT(*) INTO v_reply_count FROM treply WHERE ruser = p_user_id;
    
    RETURN 'Post Count: ' || v_post_count || ', Reply Count: ' || v_reply_count;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
        RETURN NULL;
END;
/

5)创建一个触发器，使得每增加一个回帖，就在对应的主题帖总修改回复数+1

CREATE OR REPLACE TRIGGER update_reply_count
    AFTER INSERT ON treply
    FOR EACH ROW
BEGIN
    UPDATE ttopic
    SET treply = treply + 1
    WHERE tid = :NEW.tid;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLCODE || ' - ' || SQLERRM);
END;
/

5. 数据的导入导出

1. 将这个用户下的所有内容逻辑备份，导出文件为exp学号.dmp

expdp user/password DIRECTORY=dir_name DUMPFILE=exp学号.dmp SCHEMAS=user

以上是根据你提供的问题情况给出的初步解答。由于详细需求和环境限制未提供，具体实施和细节可能有所不同。需要根据实际情况进行调整和进一步开发。