PL/SQL块的 循环语法 及 实例汇总

写在BEGIN主题部分的循环语法：

一、LOOP循环，须有初始值

LOOP 执行内容;

EXIT WHEN 终止条件;

迭代因子，循环条件;

END LOOP;

二、WHILE 循环,WHILE循环和LOOP循环条件相反

BEGIN  COU := 1;  --NUMBER类型，初始值为1

WHILE(COU < 10)  LOOP

DBMS_OUTPUT.PUT_LINE('COU := ' || cou);

END LOOP;

三、FOR循环

BEGIN

FOR cou IN 1..10 LOOP

DBMS_OUTPUT.PUT_LINE('COU := ' || cou);

END LOOP;

END;

四、IF语句

BEGIN  cou := 11 ;

IF cou > 10 THEN

DBMS_OUTPUT.PUT_LINE('COU := ' || cou);

ELSE

DBMS_OUTPUT.PUT_LINE('条件不满足‘);

END IF;

五、IF-ELSIF-ELSE语句

BEGIN  cou := 11 ;

IF cou > 10 THEN

DBMS_OUTPUT.PUT_LINE('COU := ' || cou);

ELSIF cou < 5

DBMS_OUTPUT.PUT_LINE('值小于5');

ELSE

DBMS_OUTPUT.PUT_LINE('条件不满足‘);

END IF;

六、GOTO语句

IF sal > 3500 THEN GOTO PO1;

ELSIF sal > 2000 THEN GOTO PO2;

ELSE GOTO PO3;

END IF;

<<PO1>> DBMS_OUTPUT.PUT_LINE('高工资');

<<PO2>>DBMS_OUTPUT.PUT_LINE('中等工资');

<<PO3>>DBMS_OUTPUT.PUT_LINE('低工资');

END;

实例汇总：

--问题 1： 输入一个雇员的编号，如果其工资高于 3500，则显示高工资，工资大于 2000，则显示中等工资，
--工资小于 2000 的则认为是低等工资。
DECLARE
eno emp.empno%TYPE ;
sal emp.sal%TYPE ;
BEGIN
eno := &en ;
SELECT sal INTO sal FROM emp WHERE empno=eno;
IF sal>3500 THEN
DBMS_OUTPUT.put_line('高工资');
ELSIF sal>2000 THEN
DBMS_OUTPUT.put_line('中等工资');
ELSE  DBMS_OUTPUT.put_line('低等工资');
END IF;
END;
/


DECLARE
eno emp.empno%TYPE ;
sal emp.sal%TYPE ;
BEGIN
eno := &en ;
SELECT sal INTO sal FROM emp WHERE empno=eno ;
IF sal>3500 THEN
DBMS_OUTPUT.put_line('高工资。。。 ') ;
ELSIF sal>2000 THEN
DBMS_OUTPUT.put_line('中等工资。。 ') ;
ELSE
DBMS_OUTPUT.put_line('底工资。。。 ') ;
END IF ;
END ;
/


--问题 2：输入一个雇员编号，根据它所在的部门涨工资，规则：
--• 10 部门上涨 10%
--• 20 部门上涨 20%
--• 30 部门上涨 30%
--所有部门的上涨工资，最不能超过 5000，如果超过 5000，则工资就为 5000。
DECLARE 
eno emp.empno%TYPE;
dno emp.deptno%TYPE;
sal emp.sal%TYPE;
BEGIN
  eno := &en ;
  SELECT deptno,sal INTO dno,sal FROM emp WHERE empno = eno;
  IF dno =10 THEN
    IF sal*1.1>5000 THEN
      UPDATE emp SET sal = 5000 WHERE empno =eno;
    ELSE UPDATE emp SET sal = sal*1.1 WHERE empno =eno;
    END IF;
  ELSIF dno =20 THEN
     IF sal*1.2 > 5000 THEN
       UPDATE emp SET sal = 5000 WHERE empno = eno;
     ELSE UPDATE emp SET sal = sal*1.2 WHERE empno =eno;
    END IF;
  ELSIF dno =  30 THEN
     IF sal*1.3 >5000 THEN
       UPDATE emp SET sal = 5000 WHERE empno = eno;
     ELSE UPDATE emp SET sal = sal*1.3 WHERE empno =eno;
     END IF;
  ELSE
     NULL;
  END IF;
  END;
/
--第二种方式
declare
eno emp.empno%TYPE;
dno emp.deptno%TYPE;
esal emp.sal%TYPE;
begin
eno:=&no;
select deptno,sal into dno,esal from emp where empno=eno;
if dno=10 then
esal := esal + esal*0.1;
elsif dno=20 then
esal := esal + esal*0.2;
elsif dno=30 then
esal := esal + esal*0.3;
end if;
if esal>5000 then
esal:=5000;
end if;
update emp set sal=esal where empno=eno;
end;
/


--接收部门编号，显示部门名和地理位置
declare
sno dept.deptno%type;
dname dept.dname%type;
loc dept.loc%type;
begin
sno:=&no;
select dname,loc into dname,loc from dept where deptno=sno;
dbms_output.put_line(dname||' '||loc);
exception
when no_data_found then
dbms_output.put_line('not find data');
end;
/


--接收雇员号，显示 该雇员的工资和提成，没有提成的用 0 替代。（用%type 实现）


declare
sno emp.empno%type;
sal emp.sal%type;
comm emp.comm%type;
begin
sno:=&no;
select sal,nvl(comm,0) into sal,comm from emp where empno=sno;
dbms_output.put_line(sal||' '||comm);
exception
when no_data_found then
dbms_output.put_line('not find data');
end;
/
DECLARE
sno emp.empno%TYPE;
sal emp.sal%TYPE;
comm emp.comm%TYPE;
BEGIN
  sno:=&en;
  SELECT empno,sal,nvl(comm,0) INTO sno,sal,comm FROM emp WHERE empno = sno;
  --连接字符串，表达式类型的自动提升
  dbms_output.put_line(sal||'  '||comm);
  EXCEPTION 
    WHEN No_data_found THEN
      dbms_output.put_line('not find data');
      END;
/ 


UPDATE emp
SET sal = 800
WHERE ename ='SMITH';
SELECT * FROM emp;


--问题 5：接收雇员号，显示 该雇员的所有信息，没有提成的用 0 替代。（用%rowtype 实现）
--%rowtype接收一行记录
DECLARE
eno emp.empno%TYPE;
e1 emp%ROWTYPE;
BEGIN
  eno := &en ;
  SELECT * INTO e1 FROM emp WHERE empno = eno;
  dbms_output.put_line(e1.empno||' '||e1.ename||' '||e1.job||' '||e1.mgr||' '||e1.hiredate||' '||e1.sal||' '
  ||nvl(e1.comm,0)||' '||e1.deptno );
  EXCEPTION
    WHEN no_data_found THEN
    dbms_output.put_line('not find data');
  END;
/


--问题 6：接收一个雇员名，判断他的 job，根据 job 不同，为他增加相应的 sal（用 if-elsif 实现）
DECLARE
sname emp.ename%TYPE;
sjob emp.job%TYPE;
ssal emp.sal%TYPE;
BEGIN
  sname := &en ;
  SELECT job,sal INTO sjob,ssal FROM emp WHERE ename = sname;
  IF sjob ='CLARK' THEN ssal = ssal*1.1;
    ELSIF sjob ='ANALYST' THEN ssal = ssal*1.2;
    ELSE 
      ssal = ssal* 1.08;
    END IF;
    UPDATE emp SET sal = ssal;
    EXCEPTION
    WHEN not_data_found THEN
      dbms_output.put_line('not find data');
    END;
/
--查倒数3-5人的工资
SELECT * FROM
(SELECT e.*,ROWNUM r FROM
(SELECT * FROM emp ORDER BY sal ASC) e)
WHERE r>=3 AND r <=5;


--查6-10的员工
SELECT * FROM
(SELECT e.*,ROWNUM r FROM emp e)
WHERE r >=6 AND r<= 10;


--利用10-5的差集求 6-10的员工
SELECT * FROM emp WHERE ROWNUM <= 10
MINUS
SELECT * FROM emp WHERE ROWNUM <= 5;


--查1-5的员工,小于直接查，大于加行号
SELECT * FROM emp WHERE ROWNUM <=5;


--问题 7： 用 loop 循环结构，为 dept 表增加 50-90 这些部门
DECLARE
NO NUMBER;
BEGIN
  NO := 51;
  LOOP 
    INSERT INTO dept VALUES(NO,NULL,NULL);
    EXIT WHEN (NO > 55);
    NO := NO+1;
    END LOOP;
    END;
/
SELECT * FROM dept


--问题 8：接收一个雇员名，显示该雇员的所有内容，（用%rowtype 实现），当没有这个雇员时（no_data_found），
--用异常来显示错误提示
DECLARE
sname emp.ename%TYPE;
e1 emp%ROWTYPE;
BEGIN
  sname := '&en' ;
  SELECT * INTO e1 FROM emp WHERE ename = sname;
  dbms_output.put_line(e1.ename||' '||e1.job||' 等等');
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('not find data');
END;
/ 


--问题 9： 编写一个 PL/SQL 程序块以计算某个雇员的年度薪水总额
DECLARE
sname emp.ename%TYPE;
esal emp.sal%TYPE;
BEGIN
  sname := '&en' ;
  SELECT sal INTO esal FROM emp WHERE ename = sname;
  dbms_output.put_line(sname||'的年度薪水总额：'||esal*12);
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('没有该雇员');
END;
/ 


--问题 10：编写一个 PL/SQL 程序块以向 emp 表添加 10 新雇员编号（7901-7910）
--NO NUMBER;
DECLARE
NO emp.empno%TYPE;
BEGIN
  NO := 8888;
  LOOP INSERT INTO emp(empno) VALUES(NO);
  EXIT WHEN NO >8890;
  NO := NO+1;
  END LOOP;
  END;
/


--问题 11：接受 2 个数相除，并显示结果，如果除数为 0，则显示错误提示;--SELECT ONE/TWO FROM dual;
DECLARE
ONE NUMBER;
TWO NUMBER;
BEGIN
  ONE := &en;
  TWO := &en;
  dbms_output.put_line('结果为:'||ONE/TWO);
EXCEPTION 
  WHEN zero_divide THEN
   dbms_output.put_line('除数不能为0');
   END;
/
--各部门涨工资，使用游标
DECLARE
CURSOR mycur IS SELECT * FROM emp;
empInfo emp%ROWTYPE;
esal emp.sal%TYPE;
cou NUMBER;
begin
FOR empinfo IN mycur LOOP
  --使用ROWCOUNT对游标所操作的行数进行记录
  cou:= mycur%ROWCOUNT;
  dbms_output.put_line(cou||'游标');
if empinfo.deptno=10 then
esal := empinfo.sal*1.1;
elsif empinfo.deptno=20 then
esal := empinfo.sal*1.2;
elsif empinfo.deptno=30 then
esal := empinfo.sal*0.3;
end if;
if esal>5000 then
esal:=5000;
end if;
update emp set sal=esal where empno=empinfo.empno;
END LOOP;
end;
/