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||',薪资是:'||V_EMP.SAL||'元');
   END;
DECLARE
 V_EMPNO NUMBER(4) :=&给定员工编号;
 TYPE BBB IS RECORD
 (V_ENAME EMP.ENAME%TYPE,V_JOB EMP.JOB%TYPE,
 V_SAL EMP.SAL%TYPE);
 V_EMP BBB;
 BEGIN
SELECT ENAME,JOB,SAL INTO  V_EMP
   FROM EMP WHERE EMPNO=V_EMPNO;
   DBMS_OUTPUT.PUT_LINE('该员工命名为:'||V_EMP.V_ENAME||',岗位是:'
   ||V_EMP.V_JOB||',薪资是:'||V_EMP.V_SAL||'元');
   END;
DECLARE
V_EMPNO NUMBER(4) :=&给定员工编号;
V_ENAME EMP.ENAME%TYPE;
V_JOB EMP.JOB%TYPE;
V_SAL EMP.SAL%TYPE;
 BEGIN
SELECT ENAME,JOB,SAL INTO V_ENAME,V_JOB,V_SAL
   FROM EMP WHERE EMPNO=V_EMPNO;
   DBMS_OUTPUT.PUT_LINE('该员工命名为:'||V_ENAME||',岗位是:'
   ||V_JOB||',薪资是:'||V_SAL||'元');
   END;

3.给定员工工号、姓名、岗位、薪资、部门编号和入职日期(自动获取当天),
将其录入到员工表录入前先检查部门编号对应部门信息,
若部门不存在,返回:该部门尚未建立,请确认 
若存在,录入数据后返回:员工信息录入完毕
DECLARE
V_EMPNO EMP.EMPNO%TYPE :=8888;
V_ENAME EMP.ENAME%TYPE :='HAHAHA';
V_JOB EMP.JOB%TYPE :='POLICE';
V_SAL EMP.SAL%TYPE :=6666;
V_DEPTNO EMP.DEPTNO%TYPE :=40;
V_HIREDATE EMP.HIREDATE%TYPE :=SYSDATE;
V_SUM INT;
BEGIN
  SELECT COUNT(1)INTO V_SUM FROM DEPT WHERE DEPTNO=V_DEPTNO;
  IF V_SUM=0  THEN
DBMS_OUTPUT.PUT_LINE('该部门尚未建立,请确认');    
  ELSIF V_SUM=1  THEN
INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,DEPTNO,HIREDATE) VALUES(V_EMPNO,V_ENAME,V_JOB,V_SAL,V_DEPTNO,V_HIREDATE); 
 DBMS_OUTPUT.PUT_LINE('员工信息录入完毕');    
    END IF;
  END;
SELECT * FROM EMP;
/*DECLARE
V_HIREDATE EMP.HIREDATE%TYPE := 
TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD');
BEGIN
 DBMS_OUTPUT.PUT_LINE(V_HIREDATE);
END;*/

4.给定两个数值,返回其中较大的那个数
DECLARE
A INT :=&输入1;
B INT :=&输入2;
BEGIN
  IF A>B THEN 
    DBMS_OUTPUT.PUT_LINE(A);
    ELSE
     DBMS_OUTPUT.PUT_LINE(B);
    END IF;
END;

5.给定三个数值,返回其中较大的那个数
DECLARE
A INT :=&输入1;
B INT :=&输入2;
C INT :=&输入3;
BEGIN
  IF A>B AND A>C THEN 
    DBMS_OUTPUT.PUT_LINE(A);
    ELSIF B>A AND B>C THEN
     DBMS_OUTPUT.PUT_LINE(B);
    ELSIF C>A AND C>B THEN
     DBMS_OUTPUT.PUT_LINE(C);    
    END IF;
END;
6.给定三个数值,按照从大到小的顺序返回
DECLARE
A INT :=&输入1;
B INT :=&输入2;
C INT :=&输入3;
BEGIN
IF A>B AND A>C THEN 
      IF B>C THEN
      DBMS_OUTPUT.PUT_LINE(A||','||B||','||C);
      ELSIF C>B THEN
      DBMS_OUTPUT.PUT_LINE(A||','||C||','||B);
      END IF;
ELSIF B>A AND B>C THEN
      IF A>C THEN
      DBMS_OUTPUT.PUT_LINE(B||','||A||','||C);
      ELSIF C>A THEN
      DBMS_OUTPUT.PUT_LINE(B||','||C||','||A);
      END IF;
ELSIF C>A AND C>B THEN
      IF A>B THEN
      DBMS_OUTPUT.PUT_LINE(C||','||A||','||B);
      ELSIF B>A THEN
      DBMS_OUTPUT.PUT_LINE(C||','||B||','||A);
      END IF;  
    END IF;
END;

7.给定一个数值,判断其是奇数还是偶数
DECLARE
BABA  NUMBER :=&给定一个数值;
BEGIN
IF MOD(BABA,2)=0 THEN
DBMS_OUTPUT.PUT_LINE('偶数');
ELSE
DBMS_OUTPUT.PUT_LINE('奇数');
END IF;
END;

8.给定两个数值,返回两数相除的商,若第二个数为0,返回:除数为0!
DECLARE
A INT :=&被除数输入1;
B INT :=&除数输入2;
C INT;
BEGIN
  IF B = 0 THEN
DBMS_OUTPUT.PUT_LINE('除数为0'); 
ELSE
    C:=(A-MOD(A,B))/B;
  DBMS_OUTPUT.PUT_LINE(C); 
  END IF; 
END;
9.判断一个年份是不是闰年
闰年:能被4整除但不能被100整除   或者被400整除
DECLARE
V_YEAR INT :=&年份;
BEGIN
IF MOD(V_YEAR,4)=0 AND MOD(V_YEAR,100)!=0 OR MOD(V_YEAR,400)=0 THEN
DBMS_OUTPUT.PUT_LINE('闰年');
ELSE
DBMS_OUTPUT.PUT_LINE('平年');   
END IF;
END;

10.录入身高和体重,并返回体质指数及体质状况  m^n=power(m,n)
体质指数(BMI)=体重(kg)÷身高^2(m)
体质状况:
偏瘦  <= 18.4
正常  18.5 ~ 23.9
过重  24.0 ~ 27.9
肥胖  >= 28.0
DECLARE
M INT :=&身高;
KG INT :=&体重;
BMI NUMBER(3,1);
BEGIN
BMI:=KG/(POWER(M,2));
DBMS_OUTPUT.PUT_LINE('体质指数'||BMI); 
IF BMI <= 18.4 THEN
DBMS_OUTPUT.PUT_LINE('偏瘦');   
ELSIF  BMI>=18.5 AND BMI<=23.9 THEN
DBMS_OUTPUT.PUT_LINE('正常');  
ELSIF  BMI>=24.0 AND BMI<=27.9 THEN
DBMS_OUTPUT.PUT_LINE('过重');  
ELSE
DBMS_OUTPUT.PUT_LINE('肥胖');      
END IF;
END;

DECLARE
M INT :=&身高;
KG INT :=&体重;
BMI NUMBER(3,1);
BEGIN
BMI:=KG/(POWER(M,2));
DBMS_OUTPUT.PUT_LINE('体质指数'||BMI); 
CASE 
  WHEN BMI>27.9 THEN DBMS_OUTPUT.PUT_LINE('肥胖');  
   WHEN BMI>=24.0 THEN DBMS_OUTPUT.PUT_LINE('过重');  
    WHEN BMI>=18.5 THEN DBMS_OUTPUT.PUT_LINE('正常');  
   ELSE DBMS_OUTPUT.PUT_LINE('偏瘦');  
 END  CASE ;
END;

11.请根据员工的职位来加薪,公司决定按下列加薪结构处理:
   JOB              SAL_CHANGE
   ------------     --------
   CLERK            500
   SALESMAN         1000
   ANALYST          1500
   OTHERS           2000

SELECT * FROM EMP1;
DROP TABLE EMP1;

DECLARE
  EMP1 EMP%ROWTYPE;
BEGIN
  EXECUTE IMMEDIATE 'CREATE  TABLE EMP1 AS SELECT * FROM EMP';
  IF EMP1.JOB = 'CLERK' THEN
    EMP1.SAL := EMP1.SAL + 500;
  ELSIF EMP1.JOB = 'SALESMAN' THEN
    EMP1.SAL := EMP1.SAL + 1000;
  ELSIF EMP1.JOB = 'ANALYST' THEN
    EMP1.SAL := EMP1.SAL + 1500;
  ELSE
    EMP1.SAL := EMP1.SAL + 2000;
  END IF;
END;

DECLARE
  EMP1 EMP%ROWTYPE;
  V_SQL VARCHAR2(2000):='CREATE  TABLE EMP1 AS SELECT * FROM EMP';
BEGIN
  EXECUTE IMMEDIATE V_SQL;
  IF EMP1.JOB = 'CLERK' THEN
    EMP1.SAL := EMP1.SAL + 500;
  ELSIF EMP1.JOB = 'SALESMAN' THEN
    EMP1.SAL := EMP1.SAL + 1000;
  ELSIF EMP1.JOB = 'ANALYST' THEN
    EMP1.SAL := EMP1.SAL + 1500;
  ELSE
    EMP1.SAL := EMP1.SAL + 2000;
  END IF;
END;

12.假定EMP表中无外键约束(没错  就是做题之前把外键先删掉),
给定员工工号、姓名、岗位、薪资、部门编号和入职日期(自动获取当天),将其录入到员工表
数据录入后检查数据是否合理,
若合理(即存在对应部门),则提交数据并返回:员工信息录入完成!
若不合理(即对应部门不存在),则回滚数据并返回:信息有误,申请部门不存在,请确认!
DECLARE
V_EMPNO EMP.EMPNO%TYPE :=8888;
V_ENAME EMP.ENAME%TYPE :='HAHAHA';
V_JOB EMP.JOB%TYPE :='POLICE';
V_SAL EMP.SAL%TYPE :=6666;
V_DEPTNO EMP.DEPTNO%TYPE :=50;
V_HIREDATE EMP.HIREDATE%TYPE :=SYSDATE;
V_SUM INT;
BEGIN
INSERT INTO EMP1(EMPNO,ENAME,JOB,SAL,DEPTNO,HIREDATE) 
VALUES(V_EMPNO,V_ENAME,V_JOB,V_SAL,V_DEPTNO,V_HIREDATE); 
  
SELECT COUNT(1)INTO V_SUM FROM DEPT WHERE DEPTNO=V_DEPTNO;
  IF V_SUM=0  THEN
DBMS_OUTPUT.PUT_LINE('信息有误,申请部门不存在,请确认!'); 
ROLLBACK;   
  ELSIF V_SUM=1  THEN
COMMIT;
 DBMS_OUTPUT.PUT_LINE('员工信息录入完成');    
    END IF;
  END;

SELECT * FROM EMP1;
CREATE TABLE EMP1 AS SELECT * FROM EMP;
SELECT * FROM EMP1 FOR UPDATE;
DROP TABLE EMP1;


DECLARE
V_EMPNO EMP.EMPNO%TYPE :=8888;
V_ENAME EMP.ENAME%TYPE :='HAHAHA';
V_JOB EMP.JOB%TYPE :='POLICE';
V_SAL EMP.SAL%TYPE :=6666;
V_DEPTNO EMP.DEPTNO%TYPE :=50;
V_HIREDATE EMP.HIREDATE%TYPE :=SYSDATE;
V_SUM INT;
BEGIN
  EXECUTE IMMEDIATE 'SELECT * FROM EMP1';
  SAVEPOINT A1;
INSERT INTO EMP1(EMPNO,ENAME,JOB,SAL,DEPTNO,HIREDATE) 
VALUES(V_EMPNO,V_ENAME,V_JOB,V_SAL,V_DEPTNO,V_HIREDATE); 
  
SELECT COUNT(1)INTO V_SUM FROM DEPT WHERE DEPTNO=V_DEPTNO;
  IF V_SUM=0  THEN
DBMS_OUTPUT.PUT_LINE('信息有误,申请部门不存在,请确认!'); 
ROLLBACK TO SAVEPOINT A1;   
  ELSIF V_SUM=1  THEN
COMMIT;
 DBMS_OUTPUT.PUT_LINE('员工信息录入完成');    
    END IF;
  END;
----------------------------------------------------------
---循环语句
1.从1打印到100
BEGIN
  FOR I IN 1..100 LOOP
    DBMS_OUTPUT.PUT_LINE(I);
END LOOP; 
END;
2.计算:S=1+3+5+7+······+99 打印S值
DECLARE
S INT:=0;
BEGIN
  FOR I IN 1..100 LOOP
     IF MOD(I,2)<>0 THEN
       S:=S+I;
       END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(S);
END;

循环语句练习
1.给定某正整数N,计算从0到N的所有整数累加值
DECLARE
N INT:=&给定某正整数;
S INT:=0;
BEGIN
 FOR I IN 1..N LOOP
   S:=S+I;  
 END LOOP;
  DBMS_OUTPUT.PUT_LINE(S);
END;

2.打印1-100之间的偶数
BEGIN
  FOR I IN 1..100 LOOP
    IF MOD(I,2)=0 THEN 
 DBMS_OUTPUT.PUT_LINE(I);
 END IF;
 END LOOP;
END;
3.现有如下计算公式:
S=1/(1*2)+1/(2*3)+1/(3*4)+...+1/(N*(N+1))
计算 ,当末项即(1/(N*(N+1)))小于0.001时,S的数值
DECLARE
S NUMBER :=0;
N NUMBER :=0;
BEGIN
  LOOP
  N:=N+1;
  S:=S+1/(N*(N+1));
  EXIT WHEN 1/(N*(N+1))<0.001;
  END LOOP;
DBMS_OUTPUT.PUT_LINE(S);  
END;

DECLARE
  N NUMBER := 1;
  S NUMBER := 0;
BEGIN
  LOOP 
    S := S+1/(N*(N+1));
    EXIT WHEN 1/(N*(N+1)) < 0.001;
    N := N+1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(S);
END;
4.计算:S=1*2+2*3+...+N*(N+1),当N=50时,S的数值
DECLARE
S INT:=0;
N INT :=&P_PM;
B INT;
BEGIN
 FOR I IN 1..N LOOP
   B:=I*(I+1);
   S:=S+B;
END LOOP;
 DBMS_OUTPUT.PUT_LINE(S); 
END;

5.求满足不等式1+3^2+5^2+...+N^2 > 2000时,N的最小数值
DECLARE
S INT :=1;
N INT :=1;
BEGIN
  LOOP
    N:=N+2;
    S:=S+POWER(N,2);
    EXIT WHEN S>2000;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(N); 
END;

DECLARE
S INT :=1;
N INT :=1;
BEGIN
 WHILE S<=2000
  LOOP
    N:=N+2;
    S:=S+POWER(N,2);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(N); 
END;

DECLARE
S INT :=1;
N INT :=1;
BEGIN
FOR I IN 1..2000 LOOP
    N:=N+2;
    S:=S+POWER(N,2);
    EXIT WHEN S>2000;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(N); 
END;

6.计算:S=1!+2!+...+10! 其中5!= 5*4*3*2*1 1*2*3*4*5
拓展,给定N值,计算N!+(N-1)!+(N-2)!+...1!
DECLARE
  S INT := 0;
  N INT := &给定N值;
  J INT;
BEGIN
FOR L IN 1..N LOOP 
            J := 1;
            FOR I IN 1 .. L LOOP
              J := J * I;
            END LOOP;
S := S + J;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(S);
END;

7.计算:S=1+2-3+4-5+6...+(-)N 给定N值,求出S的值
DECLARE
S INT :=0;
I INT :=0;
N INT :=&给定N值;
BEGIN
    LOOP
     I:=I+1;
          IF MOD(I,2)=0 OR I=1 THEN
          S:=S+I;
          ELSE S:=S-I;
          END IF;
        EXIT WHEN N=I;
    END LOOP;
 DBMS_OUTPUT.PUT_LINE(S);     
END;

8.打印九九乘法口诀表
DECLARE
  H NUMBER := 0; 
  L NUMBER := 0; 
  J NUMBER := 0; 
BEGIN
  LOOP
    H := H + 1;
    L := 0;
    LOOP
    
      L := L + 1;
      J := H * L;
      DBMS_OUTPUT.PUT(L || '*' || H || '=' || J||' ');
      EXIT WHEN H = L;
    END LOOP;
 dbms_output.put_line('');  
    EXIT WHEN H = 9;
  END LOOP;
END;

9.输入一个正整数N,计算其所有约数
例如,输入:10,返回:1 2 5 10
DECLARE
N INT:=&输入一个正整数;
BEGIN
    FOR I IN 1..N LOOP
           IF MOD(N,I)=0 THEN
           dbms_output.put(I||'  ');    
           END IF;
    END LOOP;
 DBMS_OUTPUT.NEW_LINE;
END;

DECLARE
  N NUMBER :=&输入;
  A NUMBER := 0;
BEGIN
  LOOP
    A := A+1;
      IF MOD(N,A) = 0 THEN DBMS_OUTPUT.PUT(A||' ');
      END IF; 
  EXIT WHEN A = N;
  END LOOP;
  DBMS_OUTPUT.NEW_LINE;
END;

10.计算:S = 2 + 22 + 222 + 2222 + 22222...并返回累加结果 加数个数通过输入指定
例如:输入3,则S = 2+22+222
DECLARE
S INT:=0;
N INT:=&输入;
I INT:=0;
V_SQL INT;
BEGIN
  LOOP
    I:=I+1;
    V_SQL:=LPAD(1,I,1)*2;
    S:=S+V_SQL;
    EXIT WHEN I=N;
    END LOOP;
DBMS_OUTPUT.PUT_LINE(S);    
END;

DECLARE
  S NUMBER := 0;
  N NUMBER := &请指定正整数N;
  M NUMBER := 0;
BEGIN
  FOR A IN 1..N LOOP
    M := M + 2*POWER(10,A-1);
DBMS_OUTPUT.PUT_LINE(M);     
    S := S + M;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(S);
END;

DECLARE
S INT:=0;
N INT:=&输入;
M INT;
BEGIN
  FOR A IN 1..N LOOP
    M:=LPAD(1,A,1)*2;
    S :=S+M;
    END LOOP;
DBMS_OUTPUT.PUT_LINE(S);    
END;

DECLARE
  N NUMBER :=&输入;
  S NUMBER := 0;
  A NUMBER := 0;
BEGIN
  FOR B IN 1 .. N LOOP
    A := A||2;
    S := S+A;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(S);
END;
 

上一篇:odoo开发教程十六:定时任务


下一篇:select file in need through time when file was created by python