plsql编程练习

一、PL/SQL基础题目
1.创建一张表备份EMP数据，然后编写PL/SQL程序
1）清理EMP表中10号部门数据
2）从备份表中把10号部门数据恢复
3）调整30号部门的薪资为原来的两倍  
DECLARE 
V_SQL1 VARCHAR2(2000):='DELETE FROM EMP1 WHERE DEPTNO=10';
V_SQL2 VARCHAR2(2000):='INSERT INTO EMP1 SELECT * FROM EMP WHERE DEPTNO=10';
V_SQL3 VARCHAR2(2000):='UPDATE EMP1 SET SAL=SAL*2 WHERE DEPTNO=30';
BEGIN
  EXECUTE IMMEDIATE V_SQL1;
   EXECUTE IMMEDIATE V_SQL2;
    EXECUTE IMMEDIATE V_SQL3;
END;
SELECT * FROM EMP1;
2.编写PL/SQL
1）备份EMP表
2）清空EMP表
3）从备份表中恢复EMP表数据
SELECT * FROM EMP1 ;
DROP TABLE EMP1;

DECLARE 
V_SQL1 VARCHAR2(2000):='CREATE TABLE EMP1 AS SELECT * FROM EMP';
V_SQL2 VARCHAR2(2000):='TRUNCATE TABLE EMP1';
V_SQL3 VARCHAR2(2000):='INSERT INTO EMP1 SELECT * FROM EMP';
BEGIN
  EXECUTE IMMEDIATE V_SQL1;
   EXECUTE IMMEDIATE V_SQL2;
    EXECUTE IMMEDIATE V_SQL3;
END;

3.打印出
  SMITH的工资是800；（直接用 SELECT INTO）
  SMITH的工资是800；（用 EXECUTE）
  10号部门的总工资是 （用 SELECT ）
DECLARE
V_SAL1 EMP.SAL%TYPE;
V_SAL2 EMP.SAL%TYPE;
V_SAL3 EMP.SAL%TYPE;
V_SQL VARCHAR2(2000):='SELECT SAL FROM EMP WHERE ENAME='''||'SMITH'||'''';--EMO
BEGIN
SELECT SAL INTO V_SAL1 FROM EMP WHERE ENAME='SMITH';
DBMS_OUTPUT.PUT_LINE('SMITH的工资是'||V_SAL1);
EXECUTE IMMEDIATE V_SQL INTO V_SAL2;
DBMS_OUTPUT.PUT_LINE('SMITH的工资是'||V_SAL2);
SELECT SUM(SAL) INTO V_SAL3 FROM EMP WHERE DEPTNO =10;
DBMS_OUTPUT.PUT_LINE('10号部门的总工资是'||V_SAL3);
END;

SELECT * FROM EMP1;

4.键盘求出公司的最高薪资、最低薪资、平均薪资~~~~(保留整数位)
DECLARE
V_SAL1 EMP.SAL%TYPE;
V_SAL2 EMP.SAL%TYPE;
V_SAL3 EMP.SAL%TYPE;
BEGIN
SELECT MAX(SAL) INTO V_SAL1 FROM EMP ; 
DBMS_OUTPUT.PUT_LINE('最高薪资'||TRUNC(V_SAL1));
SELECT MIN(SAL) INTO V_SAL2 FROM EMP ;
DBMS_OUTPUT.PUT_LINE('最低薪资'||TRUNC(V_SAL2));
SELECT AVG(SAL) INTO V_SAL3 FROM EMP ;
DBMS_OUTPUT.PUT_LINE('平均薪资'||TRUNC(V_SAL3));
END;
---------------------------------------
/*第二章-条件语句练习题*/
1.给定部门编号，撤销对应部门信息，
撤销前检查该部门下员工情况，若存在员工，先移除员工后再撤销部门
SELECT * FROM EMP1;
CREATE TABLE DEPT1 AS SELECT * FROM DEPT;
SELECT * FROM DEPT1;

DECLARE
  V_DEPTNO NUMBER(2) := &给定部门编号;
  V_NUM1   INT;
  V_NUM2   INT;
BEGIN
  SELECT COUNT(1) INTO V_NUM1 FROM EMP1 WHERE DEPTNO = V_DEPTNO;
  SELECT COUNT(1) INTO V_NUM2 FROM DEPT1 WHERE DEPTNO = V_DEPTNO;
  IF V_NUM1 > 0 THEN
    DELETE FROM EMP1 WHERE DEPTNO = V_DEPTNO;
    DELETE FROM DEPT1 WHERE DEPTNO = V_DEPTNO;
    DBMS_OUTPUT.PUT_LINE('已移除员工且撤销部门');
  ELSIF V_NUM2 = 1 AND V_NUM1 = 0 THEN
    DELETE FROM DEPT1 WHERE DEPTNO = V_DEPTNO;
    DBMS_OUTPUT.PUT_LINE('已撤销部门');
  ELSE
    DBMS_OUTPUT.PUT_LINE('部门不存在');
  END IF;
END;
2.给定员工编号，返回：该员工命名为：xxx，岗位是：xxx，薪资是：元。
若该编号对应的员工不存在，返回：所查工号不存在，请确认输入内容！
DECLARE
 V_EMPNO NUMBER(4) :=&给定员工编号;
 V_EMP EMP%ROWTYPE;
 BEGIN
   SELECT ENAME,JOB,SAL INTO V_EMP.ENAME,V_EMP.JOB,V_EMP.SAL 
   FROM EMP WHERE EMPNO=V_EMPNO;
   DBMS_OUTPUT.PUT_LINE('该员工命名为：'||V_EMP.ENAME||'，岗位是：'
   ||V_EMP.JOB||'，薪资是࿱