Oracle 的基本操作（3）

--创建用户及授权
CREATE USER A_hr IDENTIFIED BY 123456;
GRANT CONNECT,RESOURCE to A_hr;

--drop user A_hr cascade

--部门表
create table dept(
       dNO number primary key not null,
       dName varchar2(50) not null
)
--员工表
create table employee(
       eId number primary key not null,
       eName varchar2(20) not null,
       eSal number(10,2) not null,
       eJob varchar2(50) not null,
       eDeptno number not null,
       foreign key(eDeptno) references dept(dNO)
)

--部门表序列号
create sequence seq_dept
     increment by 1  
     start with 1 
     nomaxvalue   
     nocycle   
     cache 10;  
     
--员工表序列号
create sequence seq_employee
     increment by 1  
     start with 1 
     nomaxvalue   
     nocycle   
     cache 10;       
     
--部门表插入数据
insert into dept
  (dno, dname)
values
  (seq_dept.nextval, '技术部');
insert into dept
  (dno, dname)
values
  (seq_dept.nextval, '客服部');
insert into dept
  (dno, dname)
values
  (seq_dept.nextval, '售后部');
insert into dept
  (dno, dname)
values
  (seq_dept.nextval, '财务部');     
insert into dept
  (dno, dname)
values
  (seq_dept.nextval, '发货部');   
  
select * from dept;
     
--员工表插入数据     
insert into employee
  (eid, ename, esal, ejob, edeptno)
values
  (seq_employee.nextval, '景临境1', 3000.00, '工种', 2);
insert into employee
  (eid, ename, esal, ejob, edeptno)
values
  (seq_employee.nextval, '景2', 3200.00, '工种', 3);
insert into employee
  (eid, ename, esal, ejob, edeptno)
values
  (seq_employee.nextval, '景临3', 3300.00, '工种', 4);
insert into employee
  (eid, ename, esal, ejob, edeptno)
values
  (seq_employee.nextval, '景临境444', 3400.00, '工种', 5);
insert into employee
  (eid, ename, esal, ejob, edeptno)
values
  (seq_employee.nextval, '景临境555555', 3500.00, '工种', 2);
  
select * from employee;


--创建一个员工表，用来接收职员编号并检索职员姓名。姓名存储在 v_eanme 变量中，类型为 VARCHAR2（4）
declare
     v_eid constant employee.eid%TYPE :=5;
     v_ename varchar2(4);
begin
     select ename into v_ename from employee where eid=v_eid;  
exception
  when NO_DATA_FOUND then
    dbms_output.put_line('职员不存在！错误为:'||SQLCODE||SQLERRM);
  when VALUE_ERROR then
    dbms_output.put_line('可能找到姓名，但长度超过变量长度');
  when others then
    dbms_output.put_line('出现了其他的异常'); 
end;


--编写一个程序，根据员工编号查询职员信息，如果代码引发 NO_DATA_FOUND 异常，则显示一则信息。
declare
     v_eid constant employee.eid%TYPE :=6;
     v_ename employee.ename%TYPE;
begin
     select ename into v_ename from employee where eid=v_eid;  
exception
  when NO_DATA_FOUND then
    dbms_output.put_line('职员不存在！错误为:'||SQLCODE||SQLERRM);
end;


--编写一个程序，接收用户输入部门编号，并检索该职员的部门编程，如果代码引发了 TOO_MANY_ROWS 异常，则显示消息“返回多行”
declare
     v_dNO dept.dno%TYPE :=#
begin
     select eDeptno into v_dNO from employee where eDeptno=v_dNO;
exception
  when TOO_MANY_ROWS then
     DBMS_OUTPUT.put_line('返回多行');
end;