Oracle创建用户、创建表、分页、存储过程
--创建用户
create user OracleTest identified by password
--给用户授权
grant connect,resource to OracleTest
conn OracleTest/password;
-- 创建新表 DEPARTMENT。
-- DEPARTMENT : Department 的表
--  D_ID : D_ID 标识 Department
--  D_NAME : D_Name 属于 Department 
create table DEPARTMENT (
D_ID VARCHAR2(10) not null,
D_NAME VARCHAR2(10) not null, constraint DEPARTMENT_PK primary key (D_ID) );

-- 创建新表 REGISTER。
-- REGISTER : Register 的表
--  R_ID : R_ID 标识 Register
--  C_ID : C_ID 属于 Register
--  S_ID : S_ID 属于 Register
--  T_ID : T_ID 属于 Register
--  R_DATE : R_Date 属于 Register
--  R_SCORE : R_Score 属于 Register 
create table REGISTER (
R_ID VARCHAR2(10) not null,
C_ID VARCHAR2(10) not null,
S_ID VARCHAR2(10) not null,
T_ID VARCHAR2(10) not null,
R_DATE DATE not null,
R_SCORE NUMBER(38,0) null, constraint REGISTER_PK primary key (R_ID) );

-- 创建新表 COURSE。
-- COURSE : Course 的表
--  C_ID : C_ID 标识 Course
--  C_NAME : C_Name 属于 Course
--  C_COMMENT : C_Comment 属于 Course 
create table COURSE (
C_ID VARCHAR2(10) not null,
C_NAME VARCHAR2(10) not null,
C_COMMENT VARCHAR2(50) null, constraint COURSE_PK primary key (C_ID) );

-- 创建新表 STUDENT。
-- STUDENT : Student 的表
--  S_ID : S_ID 标识 Student
--  S_NAME : S_Name 属于 Student
--  S_MAIL : S_Mail 属于 Student 
create table STUDENT (
S_ID VARCHAR2(10) not null,
S_NAME VARCHAR2(10) not null,
S_MAIL VARCHAR2(50) null, constraint STUDENT_PK primary key (S_ID) );

-- 创建新表 TEACHER。
-- TEACHER : Teacher 的表
--  T_ID : T_ID 标识 Teacher
--  T_NAME : T_Name 属于 Teacher
--  D_ID : D_ID 属于 Teacher
--  T_MAIL : T_Mail 属于 Teacher 
create table TEACHER (
T_ID VARCHAR2(10) not null,
T_NAME VARCHAR2(10) not null,
D_ID VARCHAR2(10) not null,
T_MAIL VARCHAR2(50) null, constraint TEACHER_PK primary key (T_ID) );

-- 在表 REGISTER 中添加外键约束。
alter table REGISTER
add constraint STUDENT_REGISTER_FK1 foreign key (S_ID)references STUDENT (S_ID);
alter table REGISTER
add constraint COURSE_REGISTER_FK1 foreign key (C_ID)references COURSE (C_ID);
alter table REGISTER
add constraint TEACHER_REGISTER_FK1 foreign key (T_ID)references TEACHER (T_ID);
-- 在表 TEACHER 中添加外键约束。
alter table TEACHER
add constraint DEPARTMENT_TEACHER_FK1 foreign key (D_ID)references DEPARTMENT (D_ID);
--创建序列和触发器
create sequence xulie
create sequence bumen
create sequence stu_xl
create sequence course_xl
create sequence register_xl
--创建触发器
--teacher
create or replace trigger teacher_id
before insert on teacher
for each row
declare
v_num number(3);
begin
select xulie.nextval into v_num from dual;
:new.T_ID:='HYT'||lpad(v_num,3,'0');
end;
-----------
--部门
create or replace trigger Department_id
before insert on Department
for each row
declare
v_num number(2);
begin
select bumen.nextval into v_num from dual;
:new.D_ID:='HYD'||lpad(v_num,2,'0');
end;
------
--学生
create or replace trigger stu_id
before insert on Student
for each row
declare
v_num number(3);
begin
select stu_xl.nextval into v_num from dual;
:new.S_ID:='HYS'||lpad(v_num,3,'0');
end;
--课程
create or replace trigger course_id
before insert on course
for each row
declare
v_num number(3);
begin
select course_xl.nextval into v_num from dual;
:new.C_ID:='HYC'||lpad(v_num,3,'0');
end;
--选课表
create or replace trigger register_id
before insert on regist