Introduction to PL/SQL

Author:Farhan Arain
Language: eng
Format: mobi
Published: 2016-03-18T22:00:00+00:00

SELECT empno,

ename,

sal,

inc_sal (sal) after_increment

FROM emp

WHERE sal > (SELECT MAX (inc_sal (sal))

FROM emp

WHERE deptno = 10)

/

Output

EMPNO ENAME SAL AFTER_INCREMENT

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

7900 JAMES 17600 19360

1ote: Output can be different according to the data in EMP tables

There are some restrictions in calling functions from SQL Expressions A function must follow following rules to be callable from SQL Expression 1. It should be a stored function

2. It should accept only IN parameters

3. It should have valid SQL data types in parameters not PL/SQL specific type like collections.

4. It should not contain any DML statement

5. It should not perform DML or it should not query the table if this function is used in UPDATE/DELETE statement of the same table otherwise “Table is mutating” error will rise.

6. It should not contain COMMIT, ROLLBAK, SAVPOINT statements

As shown in the following example, the function is inserting a record into EMP table so when same function is used in Update statement of the same table the error is raised.

Example 2

CREATE OR REPLACE FUNCTION insert_emp (p_val NUMBER) RETURN NUMBER

IS

BEGIN

INSERT INTO emp

(empno,

ename,

deptno

)

VALUES (101,

'AQSA',

10

);

RETURN (p_val * .1);

END;

/

OUTPUT

PLSQL procedure successfully completed

UPDATE emp

SET sal = insert_emp (sal)

WHERE empno = 7900

/

OUTPUT

SET sal=INSERT_emp(sal)

*

ERROR at line 2:

ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it ORA-06512: at "SCOTT.INSERT_EMP", line 5

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.