函数功能如下:当对departments表的departmentid字段值进行修改时,对employees表中对应的departmentid字段值也进行相应修改。
对employees表进行修改时,不允许对departments表进行修改。 SQL> create or replace function d_e
2 (tabname in char,d_old in char,d_new in char)
3 return char
4 as
5 num char:=tabname;
6 begin
7 if num='departments' then
8 update departments
9 set departmentid=d_new
10 where departmentid=d_old;
11 update employees
12 set departmentid=d_new
13 where departmentid=d_old;
14 elsif num='employees' then
15 update employees
16 set departmentid=d_new
17 where departmentid=d_old;
18 end if;
19 return num;
20 end;
21 / 函数已创建。 SQL> declare
2 num char;
3 begin
4 num:=d_e('departments','0002','0007');
5 end;
6 /
declare
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在"SYSTEM.D_E", line 5
ORA-06512: 在line 4
对employees表进行修改时,不允许对departments表进行修改。 SQL> create or replace function d_e
2 (tabname in char,d_old in char,d_new in char)
3 return char
4 as
5 num char:=tabname;
6 begin
7 if num='departments' then
8 update departments
9 set departmentid=d_new
10 where departmentid=d_old;
11 update employees
12 set departmentid=d_new
13 where departmentid=d_old;
14 elsif num='employees' then
15 update employees
16 set departmentid=d_new
17 where departmentid=d_old;
18 end if;
19 return num;
20 end;
21 / 函数已创建。 SQL> declare
2 num char;
3 begin
4 num:=d_e('departments','0002','0007');
5 end;
6 /
declare
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在"SYSTEM.D_E", line 5
ORA-06512: 在line 4
emp和deptSQL> create or replace function d_e(tabname in char,d_old in char,d_new in char)
2 return char
3 as
4 num char(10) := tabname;
5 begin
6 if num='dept' then
7 update dept set deptno = d_new where deptno = d_old;
8 update emp set deptno = d_new where deptno=d_old;
9 elsif num='emp' then
10 update emp set deptno = d_new where deptno = d_old;
11 end if;
12 return num;
13 end;
14 /Function createdSQL>
SQL> declare
2 num char(30);
3 begin
4 num:=d_e('dept','0002','0007');
5 dbms_output.put_line(num);
6 end;
7 /dept PL/SQL procedure successfully completedSQL> num char(10) := tabname;//这个地方指定Char()长度
2 num char(30); //这个地方也指定长度