Oracle代码块儿——匿名代码块

Oracle的流程控制语句必须在代码块中执行。代码块分为两种：命名块和匿名块。

匿名代码块：以DECLARE或BEGIN开始，每次提交都被编译。匿名块因为没有名称，所以不能在数据库中存储并且不能直接从其他PL/SQL块中调用。
命名代码块：包括存储过程、函数、包和触发器。命名代码块可以在数据库中存储并在适当的时候调用。

一、定义Oracle匿名代码块

在代码块中可以定义变量、使用逻辑控制语句，使用代码块组织多条语句，作为一个整体执行，只向数据库服务器发送一次请求调用。
没有名称的代码块称为匿名代码块，它不存放在数据库中。当执行一个匿名代码块完毕后，会将其从程序缓存中清除。除非在应用程序中重新调入代码块代码，否则这个匿名代码块将不能执行。对于执行快速简单测试程序来说，匿名代码块很有用。

declare
　　--声明变量
begin
　　--执行业务逻辑
exception
　　--异常处理
end;

二、Oracle匿名代码块使用

Oracle匿名代码块由三部分构成：定义部分（declare，可选）、执行部分（必选）、异常处理部分（exception，可选）。

1、在定义部分声明类型和变量

（1）声明变量

SQL>

declare
    v_name varchar2(20);
    v_hiredate date:=sysdate;
begin
    v_name:='Mark';
    dbms_output.put_line('姓名：'||v_name||
          '，雇佣日期：'||v_hiredate);
end;

9 /

姓名：Mark，雇佣日期：2020-02-14 12:22:34

PL/SQL procedure successfully completed.

（2）使用%type和%rowtype声明变量

SQL>

declare
    v_empno scott.emp.empno%type;
    row_emp scott.emp%rowtype;
begin
    v_empno:=&eno;
    select * into row_emp from emp where empno=v_empno;
    dbms_output.put_line('姓名：'||row_emp.ename||
          '，雇佣日期：'||row_emp.hiredate);
exception
    when no_data_found then
        dbms_output.put_line('没有该雇员！');
end;

13 /

Enter value for eno: 7788
old 5: v_empno:=&eno;
new 5: v_empno:=7788;
姓名：SCOTT，雇佣日期：1987-04-19 00:00:00

PL/SQL procedure successfully completed.

（3）声明record类型和table类型变量

SQL>

declare
    type t_rec_emp is record(
        empno scott.emp.empno%type,
        ename scott.emp.ename%type,
        sal scott.emp.sal%type,
        deptno scott.emp.deptno%type
    );
    v_rec_emp t_rec_emp;

    type t_table_emp is table of t_rec_emp
    index by binary_integer;
    v_table_emp t_table_emp;
begin
    select empno,ename,sal,deptno into v_table_emp(1) 
    from emp where empno=7369;
    dbms_output.put_line('雇员编号：'||v_table_emp(1).empno
        ||'，姓名：'||v_table_emp(1).ename
        ||'，工资：'||v_table_emp(1).sal
        ||'，部门编号：'||v_table_emp(1).deptno);
    select empno,ename,sal,deptno into v_table_emp(2) 
    from emp where empno=7788;
    dbms_output.put_line('雇员编号：'||v_table_emp(2).empno||
          '  姓名：'||v_table_emp(2).ename||
          '，工资：'||v_table_emp(2).sal||
          '，部门编号：'||v_table_emp(2).deptno);
end;

27 /

雇员编号：7369，姓名：SMITH，工资：800，部门编号：20
雇员编号：7788 姓名：SCOTT，工资：2000，部门编号：20

PL/SQL procedure successfully completed.

2、在执行部分使用分支结构

（1）if … then … else

格式：

if <条件表达式> then
   .....
else
   .....
end if;

说明：当<条件表达式>为true 时，程序会执行 if 和else之间的语句；<条件表达式>为 false时，程序会执行else和end if 之间的语句。

举例：

SQL>

DECLARE 
    stu_age number;
BEGIN
    stu_age:=&age;
    IF stu_age>=18 THEN
        dbms_output.put_line('你是一个成年人！');
    ELSE
        dbms_output.put_line('你是一个未成年人！');
    END IF;
END;

12 /

Enter value for age: 12
old 5: stu_age:=&age;
new 5: stu_age:=12;
你是一个未成年人！

PL/SQL procedure successfully completed.

（2）if … then … elsif

格式：

if <条件表达式1> then
    .....
elsif <条件表达式2> then
    .....
    .....
elsif <条件表达式n> then
    .....
else
    .....
end if;

举例：

SQL>

DECLARE 
    score number;
BEGIN
    score:=&s;
    IF (score>100 or score<0) THEN
        dbms_output.put_line('成绩输入错误！');
    ELSIF score>=90 THEN
        dbms_output.put_line('成绩优秀！');
    ELSIF score>=60 THEN
        dbms_output.put_line('成绩还说的过去！');
    ELSE
        dbms_output.put_line('成绩太糟糕，不及格！');
    END IF;
END;

Enter value for s: 88
old 4: score:=&s;
new 4: score:=88;
成绩还说的过去！

PL/SQL procedure successfully completed.

（3）case 语句

格式1：

case <表达式>
when <表达式1> then ....;
when <表达式2> then ....;
...
when <表达式n> then ....;
[else ....]
end case;

说明：让WHEN后面的表达式和CASE后面的表达式进行比较。哪个WHEN后面的表达式和CASE后面的表达式相等，就执行相应的THEN后面的命令。如果所有的WHEN后面的表达式和CASE后面的表达式都不相等，就执行else后面的语句。

举例：

SQL>

DECLARE 
    deptno number;
BEGIN
    deptno:=&dept_no;
    CASE deptno
    WHEN 10 THEN
        dbms_output.put_line('经济系');
    WHEN 20 THEN
        dbms_output.put_line('管理系');
    WHEN 30 THEN
        dbms_output.put_line('计算机系');
    WHEN 40 THEN
        dbms_output.put_line('外语系');
    ELSE
        dbms_output.put_line('该系不存在');
    END CASE;
END;

18 /

Enter value for dept_no: 11
old 4: deptno:=&dept_no;
new 4: deptno:=11;
该系不存在

PL/SQL procedure successfully completed.

格式2：

case 
when <逻辑表达式1> then ....;
when <逻辑表达式2> then ....;
...
when <逻辑表达式n> then ....;
[else ....]
end case;

说明：哪个WHEN后面的逻辑表达式为真，就执行相应的THEN后面的命令。如果所有的逻辑表达式都为假，就执行else后面的语句。

举例：

SQL>

DECLARE 
    score number;
BEGIN
    score:=&s;
    CASE 
    WHEN (score>100 or score<0) THEN
        dbms_output.put_line('成绩输入错误！');
    WHEN score>=90 THEN
        dbms_output.put_line('成绩优秀！');
    WHEN score>=60 THEN
        dbms_output.put_line('成绩还说的过去！');
    ELSE
        dbms_output.put_line('成绩太糟糕，不及格！');
    END CASE;
END;

16 /

Enter value for s: 55
old 4: score:=&s;
new 4: score:=55;
成绩太糟糕，不及格！

PL/SQL procedure successfully completed.

3、在执行部分使用循环结构

（1）while 循环

格式：

while <逻辑表达式> loop
      ....
end loop;

说明：当<逻辑表达式>为 true 时，程序执行循环体。否则退出循环体，程序每次执行循环体之前，都判断该表达式是否为 true。

举例：

SQL>

DECLARE 
    n number default 1;
    result number default 0;
BEGIN
    WHILE n<=100 LOOP
       result:=result+n;
       n:=n+1;
    END LOOP;
    dbms_output.put_line('1-100之间所有自然数的和：'||result);
END;

1-100之间所有自然数的和：5050

PL/SQL procedure successfully completed.

（2）loop 语句

格式：

loop
    ....
exit when <逻辑表达式>
end loop;

举例：

SQL>

DECLARE 
    n number default 1;
    result number default 0;
BEGIN
    LOOP
       result:=result+n;
       n:=n+1;
    EXIT WHEN n>100;
    END LOOP;
    dbms_output.put_line('1-100之间所有自然数的和：'||result);
END;

12 /

1-100之间所有自然数的和：5050

PL/SQL procedure successfully completed.

（3）for 语句

格式：

for variable_counter_name in [reverse] lower_limit..upper_limit loop
      ....
end loop;

说明：

（1）variable_counter_name：表示一个变量，通常为整数类型，用来作为计数器。
（2）默认情况下计数器的值会递增，当在循环中使用 reverse 关键字时，计数器的值会随循环递减。
（3）lower_limit：计数器下限值，当计数器的值小于下限值时，退出循环。
（4）upper_limit：计数器上限值，当计数器的值大于上限值时，退出循环。

举例：

SQL>

DECLARE 
    result number default 0;
BEGIN
    FOR i IN 1..100 LOOP
       result:=result+i;
    END LOOP;
    dbms_output.put_line('1-100之间所有自然数的和：'||result);
END;

/
1-100之间所有自然数的和：5050

PL/SQL procedure successfully completed.

三、在执行部分处理异常

1、处理预定义异常

在exception部分捕获异常名称，编写相应的处理程序即可

SQL>

declare
    row_emp emp%rowtype;
    v_sal number;
begin
    v_sal:=&emp_sal;
    select * into row_emp 
    from emp where sal=v_sal;
    dbms_output.put_line('工资为'||v_sal||'的员工的姓名为：'||row_emp.ename);
exception
    when no_data_found then  --no_data_found：查询结果为空
        dbms_output.put_line('没有工资为'||v_sal||'的员工！');
    when too_many_rows then  --too_many_rows：查询到多条记录
        dbms_output.put_line('有多个工资为'||v_sal||'的员工！');
    when others then         --其它的异常出现
        dbms_output.put_line('出现其他错误.');
end;

17 /
Enter value for emp_sal: 88
old 5: v_sal:=&emp_sal;
new 5: v_sal:=88;
没有工资为88的员工！

PL/SQL procedure successfully completed.

2、处理非预定义异常处理（ORACLE错误）

对于非预定义异常的处理的步骤如下：

（1）代码块的定义部分定义异常情况

<异常情况>  EXCEPTION;

（2）将其定义好的异常情况，与标准的ORACLE错误关联起来

PRAGMA EXCEPTION_INIT(<异常情况>,<错误代码>);

（3）在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

例如：创建两张表，定义外键约束，插入数据

SQL>

create table department(
    department_id number(2) primary key,
    department_name varchar2(20)
);

create table student(
    student_id char(11) primary key,
    student_name varchar2(20) not null,
    birth date,
    department_id number(2), 
    foreign key(department_id) 
    references department(department_id)
);

Table created.

SQL>

 select * from department;

DEPARTMENT_ID DEPARTMENT_NAME

------------- --------------------
10 经济系
20 管理系
30 计算机系

SQL> select * from student;

STUDENT_ID STUDENT_NAME BIRTH DEPARTMENT_ID
----------- -------------------- ------------------- -------------
20190224101 王鹏 2001-11-23 00:00:00 10
20190224102 刘晓云 2001-06-03 00:00:00 10
20190224103 张静静 2002-08-09 00:00:00 10
20190224104 刘涛 2000-06-23 00:00:00 20
20190224105 张晓刚 2001-03-31 00:00:00 20

定义异常处理：

SQL>

DECLARE
    v_deptid number;
    def_myerror exception;  --定义异常
    pragma exception_init(def_myerror,-2292);  --与标准的ORACLE错误关联起来（-2292是违反一致性约束的错误代码）
BEGIN
    v_deptid:=&did;
    select department_id into v_deptid
    from department where department_id=v_deptid;
  9  
    delete from department where department_id=v_deptid;
    if sql%found then
        dbms_output.put_line('删除成功！');
    end if;
EXCEPTION
    when def_myerror then
        dbms_output.put_line('无法删除，违反数据完整性约束!');
    when others then
        dbms_output.put_line('错误代码：'||SQLCODE||', 错误信息：'||SQLERRM);
END;

20 /

Enter value for did: 10
old 6: v_deptid:=&did;
new 6: v_deptid:=10;
无法删除，违反数据完整性约束!

PL/SQL procedure successfully completed.

3、用户自定义的异常处理

用户定义的异常是通过显式使用RAISE语句来触发。用户定义的异常的处理步骤如下：

（1）在代码块的定义部分定义异常

<异常情况>  EXCEPTION;

（2）使用RAISE抛出异常

RAISE <异常情况>;

（3）在代码块的异常情况处理部分对异常情况做出相应的处理

SQL>

declare
    sal_error exception;
    v_sal number(4);
begin
    v_sal:=&sal;
    if v_sal<800 or v_sal>=10000 then
        raise sal_error;
    end if;
    update emp set sal=v_sal where empno=7788;
exception
    when sal_error then
        dbms_output.put_line('工资的范围必须高于或等于800并且低于10000！');
        dbms_output.put_line('错误代码：'||sqlcode||', 错误信息'||sqlerrm);
    when others then
        dbms_output.put_line('出现其他错误！');
end;

Enter value for sal: 22
old 5: v_sal:=&sal;
new 5: v_sal:=22;
工资的范围必须高于或等于800并且低于10000！
错误代码：1, 错误信息User-Defined Exception

PL/SQL procedure successfully completed.

4、使用RAISE_APPLICATION_ERROR函数处理自定义异常

调用RAISE_APPLICATION_ERROR函数，可以重新定义异常错误消息，它为应用程序提供了一种与ORACLE交互的方法。语法如下：

RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);

说明：

（1）error_number：自定义错误代码，范围从–20,000 到 –20,999之间；
（2）error_message：自定义提示信息；
（3）keep_errors：可选参数，逻辑值，默认keep_errors=FALSE。

例子：

SQL>

declare
    v_sal number(4);
    sal_error exception;
    pragma exception_init(sal_error,-20001);
begin
    v_sal:=&sal;
    if v_sal<800 or v_sal>=10000 then
        raise_application_error(-20001,'工资的范围必须高于或等于800并且低于10000！');
    end if;
    update emp set sal=v_sal where empno=7788;
exception
    when sal_error then
        dbms_output.put_line('错误代码：'||sqlcode||', 错误信息'||sqlerrm);
    when others then
        dbms_output.put_line('出现其他错误！');
end;

17 /

Enter value for sal: 11
old 6: v_sal:=&sal;
new 6: v_sal:=11;
错误代码：-20001, 错误信息ORA-20001: 工资的范围必须高于或等于800并且低于10000！

PL/SQL procedure successfully completed.

可以创建一张表，在exception中把错误代码和错误信息保存到表中。表结构如下：

SQL>

create sequence seq_log_id;

create table t_log(
    log_id number(10) primary key,
    log_code number(6),
    log_msg varchar(1000),
    log_time date default sysdate
);

Table created.

修改上例中的代码并执行：

SQL>

declare
    v_sqlcode number(6);
    v_sqlerrm varchar2(1000);
    v_sal number(4);
    sal_error exception;
    pragma exception_init(sal_error,-20001);
begin
    v_sal:=&sal;
    if v_sal<800 or v_sal>=10000 then
        raise_application_error(-20001,'工资的范围必须高于或等于800并且低于10000！');
    end if;
    update emp set sal=v_sal where empno=7788;
exception
    when sal_error then
        v_sqlcode:=sqlcode;
        v_sqlerrm:=sqlerrm;
        insert into t_log(log_id,log_code,log_msg)
        values(seq_log_id.nextval,v_sqlcode,v_sqlerrm);
    when others then
        dbms_output.put_line('出现其他错误！');
end;

22 /

Enter value for sal: 300
old 8: v_sal:=&sal;
new 8: v_sal:=300;

PL/SQL procedure successfully completed.

查看t_log表中的数据，结果如下：

SQL>

select * from t_log;

LOG_ID LOG_CODE LOG_MSG LOG_TIME

---------- ---------- ------------------------------------------------------- ------------

3 -20001 ORA-20001: 工资的范围必须高于或等于800并且低于10000！ 2020-02-13 22:15:48

4 -20001 ORA-20001: 工资的范围必须高于或等于800并且低于10000！ 2020-02-13 22:15:58

5 -20001 ORA-20001: 工资的范围必须高于或等于800并且低于10000！ 2020-02-13 22:16:11

四、匿名代码块使用综合案例

1、编写一个代码块，在控制台输入一个圆的半径，在屏幕上打印出圆的周长和面积

代码如下：

SQL>

declare 
   Radius number default 0;
   Area number default 0;
   Perimeter number default 0;
begin
   Radius:=&r;
   Area:=Radius*Radius*3.1415926;
   Perimeter:=Radius*2*3.1415926;
   dbms_output.put_line('Area:'||Area);
   dbms_output.put_line('Perimeter:'||Perimeter);
end;

12 /

Enter value for r: 5
old 6: Radius:=&r;
new 6: Radius:=5;
Area:78.539815
Perimeter:31.415926

PL/SQL procedure successfully completed.

2、编写一个代码块，输入一个部门的编号，显示出这个部门的名称，总人数，平均工资

SQL>

declare
    d_no number;
    d_name varchar2(20);
    emp_count number;
    avg_sal number;
begin
    d_no:=&dno;
    select dname into d_name
    from dept
    where deptno=d_no;

    select count(*),avg(sal) 
    into emp_count,avg_sal
    from emp
    where deptno=d_no;
    dbms_output.put_line('该部门名称：'||d_name);
    dbms_output.put_line('该部门员工人数：'||emp_count);
    dbms_output.put_line('该部门平均工资：'||avg_sal);
exception
    when no_data_found then
        dbms_output.put_line('该部门不存在或者该部门没有员工！');  
end;

Enter value for dno: 20
old 7: d_no:=&dno;
new 7: d_no:=20;
该部门名称：RESEARCH
该部门员工人数：5
该部门平均工资：2175

PL/SQL procedure successfully completed.

3、编写一个代码块，显示所有雇员的编号、姓名、工资和部门号

SQL>

declare
    row_emp emp%rowtype;
    cursor cur_emp is select * from emp;
begin
    open cur_emp;
    loop
        fetch cur_emp into row_emp;
        exit when cur_emp%notfound;
        dbms_output.put_line('雇员编号：'||row_emp.empno||'  姓名：'||
             row_emp.ename||'  工资：'||row_emp.sal||'部门号：'||row_emp.deptno); 
    end loop;
    close cur_emp;
end;

14 /

雇员编号：7934 姓名：MILLER 工资：1300部门号：10
雇员编号：7698 姓名：BLAKE 工资：2850部门号：30
雇员编号：7499 姓名：ALLEN 工资：1600部门号：30
雇员编号：7788 姓名：SCOTT 工资：2000部门号：20
雇员编号：7876 姓名：ADAMS 工资：1100部门号：20
雇员编号：7654 姓名：MARTIN 工资：1250部门号：30
雇员编号：7900 姓名：JAMES 工资：950部门号：30
雇员编号：7566 姓名：JONES 工资：2975部门号：20
雇员编号：7902 姓名：FORD 工资：3000部门号：20
雇员编号：7369 姓名：SMITH 工资：800部门号：20
雇员编号：7521 姓名：WARD 工资：1250部门号：30
雇员编号：7844 姓名：TURNER 工资：1500部门号：30
雇员编号：7782 姓名：CLARK 工资：2450部门号：10
雇员编号：7839 姓名：KING 工资：5000部门号：10
雇员编号：8101 姓名：TOMMY 工资：8000部门号：40
雇员编号：8101 姓名：MARK DOWN 工资：3000部门号：40

PL/SQL procedure successfully completed.

4、编写一个代码块，显示所有比本部门平均工资高的员工信息

SQL>

declare
    sal_avg number;
    row_emp scott.emp%rowtype;
    row_dept scott.dept%rowtype;
    cursor cur_dept is select * from scott.dept;
    cursor cur_emp(dept_no number) is select * 
        from scott.emp where deptno=dept_no;
begin
    open cur_dept;
    loop
        fetch cur_dept into row_dept;
        exit when cur_dept%notfound;
        dbms_output.put_line('部门编号：'||row_dept.deptno||
            '  部门名称：'||row_dept.dname); 
        select avg(sal) into sal_avg 
        from emp where deptno=row_dept.deptno;
        dbms_output.put_line(row_dept.deptno||'号部门的平均工资为：'||sal_avg);
        open cur_emp(row_dept.deptno);
        loop
            fetch cur_emp into row_emp;
            exit when cur_emp%notfound;
            if row_emp.sal>sal_avg then
                dbms_output.put_line('雇员编号：'||row_emp.empno||'  姓名：'||row_emp.ename||'  工资：'||row_emp.sal);
            end if;
        end loop;
        close cur_emp;
    end loop;
    close cur_dept;
end;

30 /

部门编号：10 部门名称：ACCOUNTING
10号部门的平均工资为：2916.666666666666666666666666666666666667
雇员编号：7839 姓名：KING 工资：5000
部门编号：20 部门名称：RESEARCH
20号部门的平均工资为：1975
雇员编号：7788 姓名：SCOTT 工资：2000
雇员编号：7566 姓名：JONES 工资：2975
雇员编号：7902 姓名：FORD 工资：3000
部门编号：30 部门名称：SALES
30号部门的平均工资为：1566.666666666666666666666666666666666667
雇员编号：7698 姓名：BLAKE 工资：2850
雇员编号：7499 姓名：ALLEN 工资：1600
部门编号：40 部门名称：OPERATIONS
40号部门的平均工资为：5500
雇员编号：8101 姓名：TOMMY 工资：8000

PL/SQL procedure successfully completed.

5、在控制台输入雇员的编号，查询该员工的信息，如果员工存在，则输出其员工号、姓名、工资、部门号。如果该员工不存在，则插入一条新记录，姓名为WANGP，工资为5000元，奖金为0，入职日期为系统日期，部门号为40。

SQL>

declare
    v_empno number;
    row_emp scott.emp%rowtype;
begin
    v_empno:=&eno;
    select * into row_emp 
    from emp where empno=v_empno;
    dbms_output.put_line('员工编号：'||row_emp.empno||
         '  姓名：'||row_emp.ename||
         '  工资：'||row_emp.sal||
         '  部门编号：'||row_emp.deptno);
exception
    when no_data_found then
        insert into emp values(row_emp.empno,
           'WANGP',null,null,sysdate,5000,0,40);
    when too_many_rows then
        for r_emp in (select * from emp where empno=v_empno) loop
            dbms_output.put_line
              ('员工编号：'||row_emp.empno||
               '  姓名：'||row_emp.ename||
               '  工资：'||row_emp.sal||
               '  部门编号：'||row_emp.deptno);
        end loop;
    when others then
        dbms_output.put_line('系统错误！');
end;

27 /

Enter value for eno: 7788
old 5: v_empno:=&eno;
new 5: v_empno:=7788;
员工编号：7788 姓名：SCOTT 工资：2000 部门编号：20

PL/SQL procedure successfully completed.

本文转载自：https://blog.csdn.net/weixin_44377973/article/details/104314501

上一篇常用正则表达式/反向选择(取反)

下一篇Java调用Bartender Demo

本文作者：Journey&Flower

本文链接：https://www.cnblogs.com/JourneyOfFlower/p/15264550.html

posted @ 2021-09-13 19:42 Journey&Flower 阅读(2363) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

Journey&Flower

Oracle代码块儿——匿名代码块

一、定义Oracle匿名代码块

1、在定义部分声明类型和变量

（1）声明变量

（2）使用%type和%rowtype声明变量

（3）声明record类型和table类型变量

2、在执行部分使用分支结构

（1）if … then … else

（2）if … then … elsif

（3）case 语句

格式1：

格式2：

3、在执行部分使用循环结构

（1）while 循环

（2）loop 语句

（3）for 语句

三、在执行部分处理异常

1、处理预定义异常

2、处理非预定义异常处理（ORACLE错误）

3、用户自定义的异常处理

4、使用RAISE_APPLICATION_ERROR函数处理自定义异常

四、匿名代码块使用综合案例

1、编写一个代码块，在控制台输入一个圆的半径，在屏幕上打印出圆的周长和面积

2、编写一个代码块，输入一个部门的编号，显示出这个部门的名称，总人数，平均工资

3、编写一个代码块，显示所有雇员的编号、姓名、工资和部门号

4、编写一个代码块，显示所有比本部门平均工资高的员工信息

5、在控制台输入雇员的编号，查询该员工的信息，如果员工存在，则输出其员工号、姓名、工资、部门号。如果该员工不存在，则插入一条新记录，姓名为WANGP，工资为5000元，奖金为0，入职日期为系统日期，部门号为40。

公告

常用链接

我的标签

积分与排名

随笔档案

文章档案

推荐排行榜