PL/SQL循环

1.if循环做判断

 1 SET SERVEROUTPUT ON 
 2 accept num prompt 'qinshuu';
 3 DECLARE 
 4 pnum NUMBER :=& num ;
 5 BEGIN
 6 IF pnum = 0 THEN
 7     SYS.DBMS_OUTPUT.PUT_LINE ('0') ;
 8 ELSIF pnum = 1 THEN
 9     dbms_output.put_line ('2') ;
10 ELSE
11     DBMS_OUTPUT.PUT_LINE ('999999999999') ;
12 END
13 IF ;
14 END ;
15 /

2.引用变量

1 SET SERVEROUTPUT ON 
2 DECLARE
3 pename  emp.ename%type;
4 psal   emp.sal%type;
5 BEGIN
6 SELECT ename,sal into pename,psal FROM emp where empno= 7902;
7 dbms_output.put_line(pename ||'hahah'||psal);
8 end;
9 /

3.基本变量 char类型会补全

 1 set SERVEROUTPUT ON
 2 DECLARE
 3 var_char1 char;
 4 var_char2 CHAR(20);
 5 BEGIN
 6 var_char1 :='a';
 7 var_char2 :='abc';
 8 DBMS_OUTPUT.PUT_LINE('var_char1 is' ||var_char1||'*');
 9 DBMS_OUTPUT.PUT_LINE('var_char2 is' ||var_char2||'*');
10 end;
11 /
12 /*
13 PL/SQL 过程已成功完成。
14 var_char1 isa*
15 var_char2 isabc                 *
16 */

4.while循环10次

1 SET SERVEROUTPUT ON
2 DECLARE pnum NUMBER := 1 ;
3 BEGIN
4     while pnum <= 10 
5   loop dbms_output.put_line (pnum) ; 
6   pnum := pnum + 1 ;
7 END loop ;
8 END ;
9 /

5.loop循环

 1 SET SERVEROUTPUT ON
 2 DECLARE pnum NUMBER := 1 ;
 3 BEGIN
 4     loop exit
 5 WHEN pnum > 10 ; 
 6 dbms_output.put_line (pnum) ; 
 7 pnum := pnum + 1 ;
 8 END loop ;
 9 END ;
10 /

6.for循环

1 SET SERVEROUTPUT ON
2 DECLARE pnum NUMBER := 1 ;
3 BEGIN
4     FOR pnum IN 1..10 
5   loop DBMS_OUTPUT.PUT_LINE (pnum) ;
6 END loop ;
7 END ;
8 /

7.游标打印所有人薪水

 1 SET SERVEROUTPUT ON
 2 DECLARE CURSOR cemp IS SELECT
 3     ENAME,
 4     sal
 5 FROM
 6     EMP ; 
 7 pename emp.ename % TYPE ; 
 8 psal emp.sal % TYPE ;
 9 BEGIN
10     OPEN cemp ; 
11     loop FETCH cemp INTO pename,
12     psal ; exit
13 WHEN cemp % notfound ; 
14 dbms_output.put_line (pename || 'xin shui shi' || psal) ;
15 END loop ; CLOSE cemp ;
16 END ;
17 /

8.游标所有人涨薪水

 1 set SERVEROUTPUT ON
 2 DECLARE
 3 CURSOR cemp is select empno,job FROM emp;
 4 pempno EMP.EMPNO%type;
 5 pjob emp.job%type;
 6 begin
 7 open cemp;
 8 loop
 9   fetch cemp into pempno,pjob;
10   exit when cemp%notfound;
11   if pjob = 'PRESIDENT' then update emp set sal = sal+1000 where empno = pempno;
12     elsif pjob='MANAGER' then update emp set sal = sal+800 where empno = pempno;
13     else update emp set sal = sal+400 where empno = pempno;
14   end if;
15 end loop;
16 close cemp;
17 commit;
18 dbms_output.put_line('wanc');
19 end;
20 /

9.游标自定义选项

SET SERVEROUTPUT ON
DECLARE CURSOR cemp (dno NUMBER) IS SELECT
    ename
FROM
    emp
WHERE
    deptno = dno ; pename emp.ename % TYPE ;
BEGIN
    OPEN cemp (20) ; loop FETCH cemp INTO pename ; exit
WHEN cemp % notfound ; dbms_output.put_line (pename) ;
END loop ; CLOSE cemp ;
END ;
/

备注：实验环境为orcle SQL Developer;

sys登录为系统自带scott解锁并且授予SCOTT用户开发权限

1 ALTER USER scott IDENTIFIED BY xxxxxx ACCOUNT UNLOCK;
2 GRANT RESOURCE TO scott;