经典PLSQL培训示例

1、数据库操作

（1）建立SDD用户，默认表空间为SDD，授权给SDD用户对数据库中的表进行增删改查；

CREATE TABLESPACE SDD

DATAFILE'D:\oracle\product\10.2.0\oradata\orcl\SDD.dbf'

SIZE50M

AUTOEXTEND ON

NEXT50M MAXSIZE20480M

EXTENT MANAGEMENT LOCAL;

CREATE USER SDD IDENTIFIED BY "SDD"

DEFAULT TABLESPACE SDD

QUOTA UNLIMITED ON SDD;

GRANT INSERT,DELETE,UPDATE,SELECT ON SCOTT.EMP TO SDD;

（2）按数据结构要求，建立员工信息表、部门信息表，要求存放在SDD表空间下，将scott 用户下emp、dept表的数据复制到当前用户下；

CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP;

CREATE TABLE DEPT AS SELECT * FROM SCOTT.DEPT;

（3）使用sql*plus登录数据库，导出员工信息表、部门信息表的表结构及数据，分别导出到文件bak.dmp，导入到当前用户下。

exp scott/tiger@orcl file=d:/bak.dmp tables=(emp,dept);

imp sdd/sdd@orcl file=d:/bak.dmp ignore=y full=y;

（4）给部门信息表增加主键，以部门编号为主键。

ALTER TABLE DEPT ADD CONSTRAINT PK_DEPTNO PRIMARY KEY(DEPTNO);

（5）建立SDDTMP用户，授权对SDD用户下emp表的查询功能；

CREATE USER SDDTMP IDENTIFIED BY "SDDTMP";

GRANT CONNECT TOSDDTMP;

GRANT SELECT ON SDD.EMP TO SDDTMP;

2、常用查询

（1）按部门分组查询员工基本信息；

SELECT * FROM EMP

WHERE DEPTNO IN (SELECT DISTINCT DEPTNO FROM EMP)

ORDER BY DEPTNO;

（2）查询research部门下工资最高的人；

SELECT * FROM EMP,DEPT

WHERE SAL=(SELECT MAX(SAL)

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME='RESEARCH')

AND EMP.DEPTNO=DEPT.DEPTNO AND DNAME='RESEARCH';

（3）展示每个部门收入排名前三位的员工信息（员工编号、员工姓名、部门名称、收入总和），收入=工资+通讯费；

SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL+NVL(COMM,0) DESC) NO,E.* FROM EMP E;

（4）展示所有通讯费为空的员工信息（员工编号、员工姓名）；

SELECT * FROM EMP WHERE COMM IS NULL;

（5）统计出平均工资高于3000的部门有哪些；

SELECT DEPTNO FROM EMP GROUP BY DEPTNO HAVING(AVG(SAL))>3000;

（6）统计在1981年4月至9月间入职了多少员工；

SELECT COUNT(*) FROM EMP

WHERE HIREDATE BETWEEN TO_DATE(19810401,'YYYY-MM-DD')

AND TO_DATE(19810930,'YYYY-MM-DD');

（7）共有多少名姓名以“S”开头的员工；

SELECT COUNT(*) FROM EMP WHERE ENAME LIKE'S%';

（8）统计员工人数高于各部门平均人数的部门；

SELECT DEPTNO, COUNT(*) FROM EMP

GROUP BY DEPTNO

HAVING(COUNT(*))>(SELECT COUNT(*)/4FROM EMP);

（9）实现依据员工编号查询出所有上级信息的功能（注意，不仅直接主管）；

这里以查询员工编号为7369的员工为例：

SELECT * FROM EMP

WHERE EMPNO!=7369START WITH EMPNO= 7369

CONNECT BY PRIOR MGR= EMPNO;

（10）实现依据员工编号查询出所有下级信息的功能（注意，不仅直属下级）。

这里以员工编号为7839的员工为例：

SELECT * FROM EMP

WHERE EMPNO!=7839START WITH EMPNO=7839

CONNECT BY PRIOR EMPNO=MGR;

3、程序设计I

CREATE OR REPLACE PACKAGE BODY PKG_JZ IS

/*------------------------------------------------------------------------- || 过程名称：PRC_查询个人信息

|| 参数描述：参数标识名称输入/输出类型

||-------------------------------------------------------------

|| NO 编号输入 NUMBER

|| prm_ErrorMsg 出错信息输出 VARCHAR2

||

|| 作者：金泽完成日期：2011-08-31

||---------------------------------------------------------------------*/ PROCEDURE PRC_查询个人信息(PRM_EMPNO IN NUMBER,

PRM_ERRORMSG OUT VARCHAR2) IS

V_EMPNO EMP.EMPNO%TYPE;

V_ENAME EMP.ENAME%TYPE;

V_SAL EMP.SAL%TYPE;

V_COMM M%TYPE;

V_JOB EMP.JOB%TYPE;

V_HIREDATE EMP.HIREDATE%TYPE;

V_DNAME DEPT.DNAME%TYPE;