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