psql的简单题目答案

CREATE TABLE tb_student(
ID NUMBER PRIMARY KEY,
NAME VARCHAR2(18) NOT NULL,
sex VARCHAR2(2)  not NULL CHECK(sex = '男' OR sex = '女'),
age NUMBER NOT NULL CHECK(age>=18 AND age<=50),
address VARCHAR2(100) DEFAULT'不祥'
);
CREATE SEQUENCE seq_tb_student;
INSERT INTO TB_STUDENT (ID,NAME,SEX,AGE,ADDRESS)VALUES(seq_tb_student.nextval,'张秋丽' ,'男',18,'北京海淀');
INSERT INTO TB_STUDENT (ID,NAME,SEX,AGE,ADDRESS) VALUES(seq_tb_student.nextval,'李斯文' ,'女',22,'河南洛阳');
INSERT INTO TB_STUDENT (ID,NAME,SEX,AGE) VALUES(seq_tb_student.nextval,'李文才','男',22);
INSERT INTO TB_STUDENT (ID,NAME,SEX,AGE,ADDRESS) VALUES(seq_tb_student.nextval,'欧阳俊','男',28,'新疆威武哈');
SELECT * FROM tb_student;


CREATE TABLE tb_marks (
ID NUMBER PRIMARY KEY,
course VARCHAR2(18) NOT NULL,
student_id NUMBER NOT NULL,
writtenexam NUMBER DEFAULT 0 CHECK(writtenexam>=0 AND writtenexam<=100),
labexam NUMBER DEFAULT 0 CHECK(labexam>=0 AND labexam<=100),
CONSTRAINT fk_student_id FOREIGN KEY (student_id) REFERENCES tb_student(ID)
);
CREATE SEQUENCE seq_tb_marks;
INSERT INTO TB_MARKS (ID,Course,STUDENT_ID, WRITTENEXAM, LABEXAM) VALUES(seq_tb_marks.nextval,'JBDC', 1,80,58);
INSERT INTO TB_MARKS (ID,Course,STUDENT_ID, WRITTENEXAM) VALUES(seq_tb_marks.nextval,'HIbernate',2,50);
INSERT INTO TB_MARKS (ID,Course,STUDENT_ID, WRITTENEXAM, LABEXAM) VALUES(seq_tb_marks.nextval,'JDBC', 2,97,82);

–1.为了使表看起来更加易懂，建立视图，把字段都显示为中文

–创建学生视图
CREATE VIEW v_tb_student
AS SELECT NAME 学生姓名,sex 性别,age 年龄,address 地址
FROM tb_student;
SELECT * FROM v_tb_student;

—创建课程分数视图
CREATE VIEW v_tb_marks
AS SELECT course 课程, student_id 学生id,writtenexam 笔试分数,labexam 机式分数
FROM tb_marks;
SELECT * FROM v_tb_marks;

CREATE VIEW v_tb_table
AS SELECT s.name 学生名字,s.age 年龄, s.sex 性别, s.address 地址, m.course 课程, m.writtenexam 笔试分数, m.labexam 机试分数
FROM tb_student s ,tb_marks m
WHERE s.id = m.student_id;
SELECT * FROM v_tb_table;

–2.存储过程proc_failStudent，显示笔试或者机试成绩不及格的所有学员。
proc_failStudent.prc文件

create or replace procedure proc_failStudent 
IS
       CURSOR c_tb_marks IS SELECT * FROM tb_marks m WHERE m.writtenexam<60 OR m.labexam<60;
       CURSOR c_tb_student(v_id tb_student.id%TYPE) IS SELECT * FROM tb_student s WHERE s.id = v_id ;
begin
  FOR r_tb_marks IN c_tb_marks
    LOOP

    FOR r_tb_student IN c_tb_student(r_tb_marks.student_id)
        LOOP
          dbms_output.put_line(r_tb_student.name||', '||r_tb_student.sex||', '||r_tb_student.age||', '||r_tb_student.address);
        END LOOP;

    dbms_output.put_line(' ');      
    END LOOP;
end proc_failStudent;

--调用存储过程
BEGIN
  proc_failStudent;
END;

–3.存储过程proc_addExam，添加学生考试成绩
提示：参数包括学生编号、课程名称、笔试成绩、机试成绩。
添加时注意先检查学生编号在学生表里面有没有，没有给出错误提示。有的话进行添加
proc_addExam文件

/**
插入数据到tb_marks(student_id,course,writtenexam,labexam)中，先判端学生的id是否存在，如果存在则插入，否则在控制台输出提升信息
*/
create or replace procedure 
proc_addExam(v_id number,v_student_id number, v_course varchar2, v_writtenexam varchar2, v_labexam varchar2) 
IS
        CURSOR c_tb_student IS SELECT * FROM tb_student WHERE tb_student.id = v_student_id;                  
begin
  FOR r_tb_student IN c_tb_student
    LOOP
      IF(r_tb_student.id IS NULL) THEN 
          dbms_output.put_line('该学生不存在');
      ELSE
         INSERT INTO tb_marks(id,student_id,course,writtenexam,labexam) 
         VALUES (v_id,v_student_id,v_course,v_writtenexam,v_labexam);
      END IF;
    END LOOP;
end proc_addExam;

--调用存储过程插入数据到tb_marks(id,student_id,course,writtenexam,labexam)
--执行存储过程中无法使用sequence语句，是因为存储过程是预先编译好进行存储的
--而,sequence是会动态改变的，所以有可能会造成键值的重复无法插入
BEGIN 
  proc_addExam(4,1,'Hibername',50,65);
END;

–4.创建触发器trig_delStu，删除学生信息表时，把有关此学生的考试信息也删除掉
提示：参数包括学生编号
如果删除不了的话，先删除外键约束，再测试.
trig_delStu文件

create or replace trigger trig_delStu
  before delete on tb_student  
  for each row
declare

BEGIN

  DELETE FROM tb_marks m WHERE m.student_id = :Old.id; 

end trig_delStu;

DELETE FROM tb_student s WHERE s.id = 2;