--序列
create sequence teaSeq 
  minvalue 1
  increment by 1
  start with 1
  nocache;

create sequence stuSeq 
  minvalue 1
  increment by 1
  start with 1
  nocache;

--表
create table Teacher (
  teaid int,
  tname varchar2(15),
  sex char(2) check(sex='男' or sex='女'),
  age int,
  phonenumber varchar2(15),
  taddress varchar2(30),
  primary key(teaid)
);

create table Student(
  stuid int,
  sname varchar2(15),
  sex char(2),
  age int,
  teacherid int,
  phonenumber varchar2(12),
  address varchar2(30),
  primary key(stuid),
  foreign key(teacherid) references Teacher(teaid)
);

-- modify column
alter table Student modify phonenumber varchar2(15);
-- add constraint
alter table Student add constraint sex check(sex in ('男','女'));
-- rename column
alter table Student rename column address to saddress;

-- insert data/添加数据
insert into Teacher values(teaSeq.nextval, '张孝祥', '男', 33, '13826754208', '中国河南省南阳市');
insert into Teacher values(teaSeq.nextval, '李清照', '女', 43, '13426754204', '中国上海');
insert into Teacher values(teaSeq.nextval, '赵子龙', '男', 28, '13626754206', '中国浙江省杭州市');
insert into Teacher values(teaSeq.nextval, '李小龙', '男', 36, '13526754205', '中国湖北省武汉市');
insert into Teacher values(teaSeq.nextval, '武则天', '女', 45, '13226754202', '中国广东省深圳市');
insert into Teacher values(teaSeq.nextval, '西施', '女', 21, '13126754201', '中国香港九龙');
insert into Teacher values(teaSeq.nextval, '诸葛亮', '男', 27, '13726754207', '中国广西省南宁市');
insert into Teacher values(teaSeq.nextval, '王昭君', '女', 18, '13026754200', '中国云南省昆明市');
insert into Teacher values(teaSeq.nextval, '王永民', '男', 56, '13326754203', '中国青海省西宁市');
insert into Teacher values(teaSeq.nextval, '于丹', '女', 31, '13926754209', '中国陕西省西安市');

insert into Student values(stuSeq.nextval, '影影', '女', 21, 1, '1234567891', '中国广西省南宁市');
insert into Student values(stuSeq.nextval, '寒梅', '女', 22, 1, '1234567892', '中国广西省南宁市');
insert into Student values(stuSeq.nextval, 'Lily', '女', 23, 1, '1234567893', '中国广东省深圳市');
insert into Student values(stuSeq.nextval, 'Lucy', '女', 24, 2, '1234567894', '中国广东省深圳市');
insert into Student values(stuSeq.nextval, 'Tom', '男', 25, 3, '1234567895', '中国西藏拉萨市');
insert into Student values(stuSeq.nextval, 'Alice', '女', 26, 3, '1234567896', '中国广西省南宁市');
insert into Student values(stuSeq.nextval, 'Jack', '男', 27, 2, '1234567897', '中国广西省南宁市');
insert into Student values(stuSeq.nextval, 'Jack', '男', 27, 4, '1234567897', '中国广西省南宁市');
commit;

select user from dual;
select sysdate from dual;

-- 最简单的关连查询
select * from Student stu, Teacher tea where teacherid = teaid; -- 等效于全连接
select * from Student stu left join Teacher tea on teacherid=teaid; -- 左连接
select * from Student stu right join Teacher tea on teacherid=teaid; -- 右连接
select * from Student stu join Teacher tea on teacherid=teaid; -- 全连接
-- 很奇怪，当查询id的时候distinct就无效了(各路神仙，求解)……
select distinct sname, sex, age, saddress from student stu;
select * from Teacher order by age desc; -- asc(default)/desc

-- no parameter
create or replace procedure Proc1
is
begin
     Dbms_Output.put_line('Hello world!'||chr(10)||'I am a happy DBA secooler');
end;
-- parameter: in(default)
create or replace procedure proc2(stname student.sname%type)
is
address student.saddress%type;
begin
     select saddress into address from student where sname=stname;
     dbms_output.put_line('Name:'||stname||' Address:'||address);
     exception
         when VALUE_ERROR then
              dbms_output.put_line('赋值错误/parameter error!');
         when NO_DATA_FOUND then
              dbms_output.put_line('No data!');
         when TOO_MANY_ROWS then
              dbms_output.put_line('Too many rows!');
         when OTHERS then
              dbms_output.put_line('Other error!');
end;
-- parameter: in out out out
create or replace procedure proc3(stid in student.stuid%type, stname out student.sname%type, stphone out student.phonenumber%type, stuadd out student.saddress%type)
as countnumber number;
begin
     select count(*) into countnumber from student where stuid=stid;
     if countnumber=1 then
        select phonenumber into stphone from student where stuid=stid;
        select saddress into stuadd from student where stuid=stid;
        select sname into stname from student where stuid=stid;
     else
         dbms_output.put_line('返回值过多');
     end if;
end;
-- use these procedures/调用存储过程.
declare
       stuid student.stuid%type;
       stuname student.sname%type;
       stuphone student.phonenumber%type;
       stuadd student.saddress%type;
begin
     proc1; -- 调用存储过程proc1
     stuname :='Lucy'; -- 调用输入参数前必须先赋值，输出参数不必要
     proc2(stuname); -- use proc2
     stuname :='Jack';
     proc2(stuname); -- Too many rows error.
     stuid := 1;
     proc3(stuid,stuname,stuphone,stuadd); -- use proc3
     dbms_output.put_line('Name:'||stuname||' Phone:'||stuphone||' Address'||stuadd);
     proc3(stid=>2,stphone=>stuphone,stname=>stuname,stuadd=>stuadd); -- 此种写法参数顺序可变
     dbms_output.put_line('Name:'||stuname||' Phone:'||stuphone||' Address'||stuadd);
end;

-- VALUE_ERROR
declare
       stuid student.stuid%type;
begin
     stuid :='ss'; -- value_error
     exception
              when VALUE_ERROR then
                   dbms_output.put_line('赋值错误');
              when OTHERS then
                   dbms_output.put_line('其它错误');
end;
-- user-defined exception/自定义异常
declare
       stuname student.sname%type;
       VALUE_NOT_EXIST exception;
begin
     stuname := 'd';
     if stuname not in('a','b','c') then
        raise VALUE_NOT_EXIST;
     end if;
     exception
              when VALUE_NOT_EXIST then
                   dbms_output.put_line('该值不存在');
              when OTHERS then
                   dbms_output.put_line('其它错误');
end;