Oracle database exercises (6) sequence and index

Employee table (emp)
字段类型描述
empnonumber(4)员工编号
enamevarchar2(10)员工姓名
jobvarchar2(9)员工岗位
mgrnumber(4)经理编号
hiredateDate入职时间
salnumber(7,2)基本工资
commnumber(7,2)奖金
deptnonumber(2)所属部门编号

--员工表
create table EMP
(
empno NUMBER(4) primary key,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2)
);

insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (1, 'dog', 'code', 7369, to_date('05-06-2018', 'dd-mm-yyyy'), 5000, 5000, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);

--部门表
create table DEPT
(
deptno NUMBER(2) primary key,
dname VARCHAR2(14),
loc VARCHAR2(13)
);


insert into DEPT (deptno, dname, loc)
values (10, '财务', 'NEW YORK');
insert into DEPT (deptno, dname, loc)
values (20, '研发', 'DALLAS');
insert into DEPT (deptno, dname, loc)
values (30, '销售', 'CHICAGO');
insert into DEPT (deptno, dname, loc)
values (40, '运营', 'BOSTON');

--工资等级表
create table SALGRADE
(
GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER
);
INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

--奖金表
create table BOUNS
(
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER
);

Sequence is an object used to generate continuous integer data. It is often used as an increase column in the primary key. It can be generated in ascending or descending order.

(1) Create a sequence enoSeq, starting from 1, the default maximum value, increasing by 1 each time, and 30 pre-allocated sequence numbers are cached.

CREATE SEQUENCE enoSeq
INCREMENT BY 1  
START WITH 1 
NOMAXVALUE 
CACHE 30

(2) Access the current value and the next value of the sequence from the pseudo table dual through the two pseudo columns of the sequence enoSeq, CurrVal and NextVal

SELECT enoSeq.NextVal,enoSeq.CurrVal FROM DUAL

(3-1) Use the sequence value to insert a new record in the EMP table; query the empno and ename attributes of the newly generated record through ename.

INSERT INTO 
EMP(empno,ename,job,mgr,hiredate,sal,comm,deptno)
VALUES(enoSeq.NextVal,'TQ','SALESMAN', 7698, to_date('21-02-1981', 'dd-mm-yyyy'), 1700, 500, 30);
SELECT empno,ename FROM EMP WHERE ename='TQ'

(3-2) When the generated sequence cannot meet the actual needs, you can modify the sequence definition to recreate it: modify the enoSeq sequence created in the above question, and set the maximum value to 1000 and the minimum value to -1000. Note: Modifying the sequence cannot change its Start With parameter, because the CurrVal and NextVal of the sequence have values ​​at this time.

ALTER SEQUENCE enoSeq MAXVALUE 1000 MINVALUE -1000

(4) Delete the enoSeq sequence

DROP SEQUENCE enoSeq

(5) Create a single-column index on the ename column. Note: The naming rule of the index is usually idx_tableName_columnName

CREATE INDEX idx_EMP_ename ON EMP(ename)

(6) If you often use Job and Sal in the Order By clause as the sorting basis, you can create a composite index on job and sal.

CREATE INDEX idx_EMP_job_sal ON EMP(job,sal)

(7) If you often perform DML operations on the index column, you need to rebuild the index regularly to improve the space utilization of the index.
Use alter index index_name rebuild; to rebuild the index created in the previous question.

ALTER INDEX idx_EMP_job_sal REBUILD

(8) Delete the index created in Question 5

DROP INDEX idx_EMP_ename

(9) View the index in the data dictionary IND. Note: The object names in the data dictionary are all uppercase.

SELECT index_name FROM ind WHERE table_name = 'EMP'

(10) Use PL/SQL programming to program output 26 capital letters between A and Z.

DECLARE
i INT:=65;
BEGIN
  WHILE i<=90 LOOP 
        dbms_output.put_line(chr(i));
        i:=i+1;
    END LOOP;
END;

(11) Use PL/SQL programming to prompt to enter the employee number. After entering the employee number, use dbms_output.put_line to display all the employee's information.

  • %ROWTYPE declares the row object of a record type cursor
DECLARE
	v_row EMP % ROWTYPE;
BEGIN
	eno NUMBER(5):= &eno;
    SELECT * INTO v_row FROM EMP WHERE empno=eno;
    dbms_output.put_line('empno:'||v_row.empno||',ename:'||v_row.ename||',job:'||v_row.job||',mgr:'||v_row.mgr||',hiredate:'||v_row.hiredate||',sal:'||v_row.sal||',comm:'||v_row.comm||',deptno:'||v_row.deptno);
END;