公司管理系统（阶段测试）（answer）

题目在这里

-----------------------------------------------------------------源代码-----------------------------------------------------------------

建表：

CREATE USER companyManager IDENTIFIED BY 123456;

GRANT DBA TO companyManager;

-------------创建权限表---------
CREATE TABLE userPower(
ID INT PRIMARY KEY,
position VARCHAR2(20),
powerInfo VARCHAR(50)
);
DELETE FROM userPower;
INSERT INTO userPower VALUES(1,'管理员','删除所有用户，查看用户详情信息');
INSERT INTO userPower VALUES(2,'经理','删除本部门用户，查看详情信息');
INSERT INTO userPower VALUES(3,'用户','更新本人详情信息，查看本人详情信息');--普通员工
SELECT * FROM userPower;

------------创建部门表--------------
CREATE TABLE dept(
ID INT PRIMARY KEY,
deptName VARCHAR2(20)
);

INSERT INTO dept VALUES(1,'销售部');
INSERT INTO dept VALUES(2,'运营部');
INSERT INTO dept VALUES(3,'财政部');
SELECT * FROM dept;

-------------创建员工表-------------
CREATE TABLE employee(
ID INT PRIMARY KEY,
empName VARCHAR2(10),--用户名
empAlias VARCHAR2(15),--用户昵称
age INT,--年龄
empPwd VARCHAR2(6),--密码
deptNo INT,--部门
powerNo INT,--权限
modifiedTime DATE--最近一次修改时间
);

ALTER TABLE employee ADD (img VARCHAR(50)); --上传的头像地址
ALTER TABLE employee ADD (addTime DATE); --添加日期，为当前系统数据
ALTER TABLE employee ADD (isExisted VARCHAR2(1));--逻辑删除的判断

-------------创建实现员工表主键自增的序列和触发器
CREATE SEQUENCE emp_sequences
START WITH 1
INCREMENT BY 1
CACHE 10;
-------------------------------------------
CREATE OR REPLACE TRIGGER  emp_id_triggers
  BEFORE INSERT ON employee
  FOR EACH ROW 
DECLARE 
  -- local variables here
BEGIN
  SELECT emp_sequences.nextval INTO :new.id FROM dual;
END emp_id_triggers;

----------------插入员工数据------------------------------
DELETE  FROM employee;
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('张一','一名惊人',30,'123',1,1,to_date('2018-09-09','yyyy-mm-dd'));
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('张二','二阳',25,'123',1,2,to_date('2017-09-09','yyyy-mm-dd'));
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('张三','三个臭皮匠',20,'123',1,3,to_date('2015-09-09','yyyy-mm-dd'));

INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('李一','一名惊人2',30,'123',2,1,to_date('2017-09-09','yyyy-mm-dd'));
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('李二','二阳2',25,'123',2,2,to_date('2018-09-05','yyyy-mm-dd'));
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('李三','三个臭皮匠2',20,'123',2,3,to_date('2018-06-09','yyyy-mm-dd'));

INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('王一','一名惊人3',30,'123',3,1,to_date('2018-02-09','yyyy-mm-dd'));
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('王二','二阳3',25,'123',3,2,to_date('2018-06-09','yyyy-mm-dd'));
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime) VALUES('王三','三个臭皮匠3',20,'123',3,3,to_date('2015-09-09','yyyy-mm-dd'));
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime,img,addTime) VALUES('zhangsan','三个臭皮匠3',20,'123',3,3,to_date('2015-09-09','yyyy-mm-dd'),'hahahaImg',to_date('2015-09-09','yyyy-mm-dd'));
INSERT INTO employee(empName,empAlias,age,empPwd,deptNo,powerNo,modifiedTime,img,addTime) VALUES('zhaosi','三个臭皮匠3',20,'123',3,1,to_date('2015-09-09','yyyy-mm-dd'),'hahahaImg',to_date('2015-09-09','yyyy-mm-dd'));

UPDATE employee SET isExisted='T';--T为存在，F表示已被逻辑删除
UPDATE employee SET addTime=to_date('1949-10-01','yyyy-mm-dd');
UPDATE employee SET img='../img/none.gif';


------------------------------------------------------
SELECT * FROM employee;
SELECT e.*,d.*,u.* FROM employee e 
INNER JOIN dept d ON e.deptNo=d.id
INNER JOIN userPower u ON e.powerNo=u.id
ORDER BY modifiedTime 

SELECT * FROM employee WHERE ID>30  ORDER BY modifiedTime

数据库：

--①---------------------------------------------------------------
--------------输入部门编号，返回该部门名称和部门人数------------
CREATE OR REPLACE PROCEDURE q1(c1 INT ,n1 OUT INT ,n2 OUT VARCHAR2)
AS
BEGIN
  SELECT COUNT(*) INTO n1 FROM employee WHERE deptNo=c1;
  SELECT deptName INTO n2 FROM dept WHERE ID=c1;
  dbms_output.put_line('部门名称：'||n2);
  dbms_output.put_line('部门人数：'||n1);
END;
----------------------
DECLARE
  c INT :='&输入部门编号';
  c2 INT ;
  c3 VARCHAR2(8);
BEGIN
  q1(c,c2,c3);
  dbms_output.put_line('部门名称：'||c3);
  dbms_output.put_line('部门人数：'||c2);
END;


--②----------------------------------------------------------------------------------------------------
--输入部门编号和开始位置、结束位置，存储过程使用游标返回符合条件的所有员工信息，输出显示员工信息-----


---------------创建存储过程-----------------------------
CREATE OR REPLACE PROCEDURE q2(c1 INT,a1 INT ,a2 INT ,refc OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN refc FOR SELECT * FROM (SELECT e.*,ROWNUM r FROM employee e WHERE deptNo=c1) WHERE r BETWEEN a1 AND a2;
END;
-----------------------------------------------------------------
--------创建新表方便游标处理结果集-------------------------------
CREATE TABLE A AS (SELECT e.*,ROWNUM r FROM employee e WHERE 1=2);
SELECT * FROM A;
-----------------------------------------------------------------
DECLARE
--接收数据用的参数
  empInfo A%ROWTYPE;--行级变量
  TYPE c2 IS REF CURSOR;
  c3 c2;
--查询用的参数
  c1 INT:='&输入部门编号';
  a1 INT:='&输入开始位置';
  a2 INT:='&输入结束位置';
BEGIN
  q2(c1,a1,a2,c3);
  
  LOOP
    FETCH c3 INTO empInfo;
    EXIT WHEN c3%NOTFOUND;
    dbms_output.put_line('-----------------------------');
    dbms_output.put_line('rownum：'||empInfo.r);  
    dbms_output.put('id：'||empInfo.id);
    dbms_output.put_line('empName：'||empInfo.empName);  
    dbms_output.put_line('empAlias：'||empInfo.empAlias);  
    dbms_output.put_line('age：'||empInfo.age);  
    dbms_output.put_line('modifiedTime：'||empInfo.modifiedTime);  
    dbms_output.put_line('addTime：'||empInfo.addTime);  

  END LOOP;
  
END;