tailieunhanh - Oracle PL/SQL for dummies phần 5

Tham khảo tài liệu 'oracle pl/sql for dummies phần 5', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 158 Part II Getting Started with PL SQL The reason for these rules is simple. Oracle can t be sure that modifying data the session the system or object structure doesn t have any impact on the data you re querying or even on objects you re processing. If such activity isn t blocked a logical loop or conflict that can t be resolved might result. Think about what should happen if the function in the next example is called in SQL. This function updates the salary of the specified employee and tells you whether the update was successful create or replace function f_giveRaise_tx i_empNo NUMBER i_pcnt NUMBER return VARcHar2 is begin update emp set sal sal i_pcnt 100 sal where empNo i_empNo return OK exception when others then return Error substr sqlerrm 1 256 end f_giveRaise_tx Instead of the update confirmation the result of the query is an Oracle error which is caught by the exception handler of the function SQL select f_giveRaise_tx 7369 100 2 from dual F_GIVERAISE_TX 7369 100 Error ORA-14551 cannot perform a DML operation inside a query SQL Oops Oracle just told you that you cannot make that UPDATE. Performance impact How does the Oracle know what exactly is happening under the hood of the function that you placed inside the query How can it determine what impact that function could have on overall execution It can t. In terms of performance using functions in SQL is risky. With functions in SQL the whole idea of SQL optimization gains another dimension namely decreasing the impact of function calls. There are some guidelines you can follow. The next example shows a display function for an employee that returns name and job. It also includes a view that uses this display function for managers Chapter 6 PL SQL and SQL Working Together 159 create or replace function f_emp_dsp i_empNo NUMBER return VARCHAR2 iS v_out_tx VARCHAR2 256 begin Inside of F_EMP_DSP select initcap eName initcap job into v_out_tx from emp where empNo i_empNo return v_out_tx .