建表:
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;