SQL只能在投影中使用函数:它需要返回值的东西。因此,您需要编写一些函数。这是坏消息。好消息是,您可以重复使用存储过程中的所有投资。
这是一个强制执行完全公正业务规则的过程:只有经理才能拥有高薪水。
SQL> create or replace procedure salary_rule
2 ( p_sal in emp.sal%type
3 , p_job in emp.job%type)
4 is
5 x_sal exception;
6 begin
7 if p_sal > 4999 and p_job != 'MANAGER' then
8 raise x_sal;
9 end if;
10 exception
11 when x_sal then
12 raise_application_error(-20000, 'Only managers can earn that much!');
13 end salary_rule;
14 /
Procedure created.
SQL>
由于它是一个过程,我们不能在SELECT语句中使用它;我们需要将其封装在函数中。这个函数只调用存储过程。它返回输入参数P_SAL。换句话说,如果薪水有效(根据规则),它将被返回。否则,函数将重新抛出存储过程的异常。
SQL> create or replace function validate_salary
2 ( p_sal in emp.sal%type
3 , p_job in emp.job%type)
4 return emp.sal%type
5 is
6 begin
7 salary_rule(p_sal, p_job);
8 return p_sal;
9 end validate_salary;
10 /
Function created.
SQL>
这个函数必须返回一个值,我们想要将其插入到我们的表中。它不能返回一些无意义的短语,比如“工资还行”。此外,如果我们想要验证两个列,我们需要为每个列编写单独的函数,即使它们之间存在关系并且我们使用相同的存储过程来验证它们。DETERMINISTIC 关键字可以很好地解决这个问题。
以下是测试内容:水管工不能赚取5000个斯邦利克......
SQL> insert into emp
2 (empno
3 , ename
4 , job
5 , deptno
6 , sal )
7 select
8 emp_seq.nextval
9 , 'HALL'
10 , 'PLUMBER'
11 , 60
12 , validate_salary(5000, 'PLUMBER')
13 from dual
14 /
, validate_salary(5000, 'PLUMBER')
*
ERROR at line 12:
ORA-20000: Only managers can earn that much!
ORA-06512: at "APC.SALARY_RULE", line 12
ORA-06512: at "APC.VALIDATE_SALARY", line 7
SQL>
...但是管理者可以(因为他们值得):
SQL> insert into emp
2 (empno
3 , ename
4 , job
5 , deptno
6 , sal )
7 select
8 emp_seq.nextval
9 , 'HALL'
10 , 'MANAGER'
11 , 60
12 , validate_salary(5000, 'MANAGER')
13 from dual
14 /
1 row created.
SQL>
请注意,抛出的异常对于此操作至关重要。我们不能在SQL语句中编写一些奇怪的IF SALARY IS VALID THEN INSERT逻辑。因此,如果存储过程没有引发异常,而是返回一些软弱的错误状态,则包装函数将不得不解释输出并抛出自己的异常。