设为首页收藏本站
开启辅助访问

Oracle代码块儿

 您所在的位置：网站首页 › oracle重命名存储过程的语句 › Oracle代码块儿

Oracle代码块儿

2024-06-22 14:02| 来源: 网络整理| 查看: 265

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: 7788old   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: 12old   5:     stu_age:=&age;new   5:     stu_age:=12;你是一个未成年人！

PL/SQL procedure successfully completed.

（2）if … then … elsif

格式：

if then ..... elsif then ..... ..... elsif then ..... else ..... end if;

举例：

SQL> 

DECLARE score number; BEGIN score:=&s; IF (score>100 or 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: 88old   4:     score:=&s;new   4:     score:=88;成绩还说的过去！

PL/SQL procedure successfully completed.

（3）case 语句 格式1： case when then ....; when then ....; ... when 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: 11old   4:     deptno:=&dept_no;new   4:     deptno:=11;该系不存在

PL/SQL procedure successfully completed.

格式2： case when then ....; when then ....; ... when then ....; [else ....] end case;

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

举例：

SQL> 

DECLARE score number; BEGIN score:=&s; CASE WHEN (score>100 or 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: 55old   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 

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: 88old   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            1020190224102 刘晓云               2001-06-03 00:00:00            1020190224103 张静静               2002-08-09 00:00:00            1020190224104 刘涛                 2000-06-23 00:00:00            2020190224105 张晓刚               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: 10old   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=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: 22old   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=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: 11old   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=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: 300old   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: 5old   6:    Radius:=&r;new   6:    Radius:=5;Area:78.539815Perimeter: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: 20old   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  部门名称：ACCOUNTING10号部门的平均工资为：2916.666666666666666666666666666666666667雇员编号：7839  姓名：KING  工资：5000部门编号：20  部门名称：RESEARCH20号部门的平均工资为：1975雇员编号：7788  姓名：SCOTT  工资：2000雇员编号：7566  姓名：JONES  工资：2975雇员编号：7902  姓名：FORD  工资：3000部门编号：30  部门名称：SALES30号部门的平均工资为：1566.666666666666666666666666666666666667雇员编号：7698  姓名：BLAKE  工资：2850雇员编号：7499  姓名：ALLEN  工资：1600部门编号：40  部门名称：OPERATIONS40号部门的平均工资为：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: 7788old   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



【本文地址】


今日新闻

推荐新闻

CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3