plsql编程练习

天才就是勤奋

3881人浏览 · 2021-12-18 12:25:25

天才就是勤奋 · 2021-12-18 12:25:25 发布

一、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;