sql server循环语句 with sql语句中的循环

转载

数码悟透 2023-11-13 21:00:23

文章标签 sql server循环语句 with 错误提示程序块 SQL 文章分类 SQL Server 数据库

写在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;
 /

sql server循环语句 with sql语句中的循环_SQL