Oracle 的基本操作（3）

dslcoffsei

--创建用户及授权
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;