Shulou(Shulou.com)06/01 Report--

= Example 1 =

[oracle@localhost notes] $vim s81.sql

CREATE OR REPLACE FUNCTION check_sal RETURN Boolean

IS

V_dept_idemployees.department_id%TYPE

V_empnoemployees.employee_id%TYPE

V_sal employees.salary%TYPE

V_avg_salemployees.salary%TYPE

BEGIN

V_empno:=205

SELECT salary,department_id

INTO v_sal,v_dept_id

FROM employees

WHERE employee_id= v_empno

SELECT avg (salary)

INTO v_avg_sal

FROM employees

WHEREdepartment_id=v_dept_id

IF v_sal > v_avg_salTHEN

RETURN TRUE

ELSE

RETURN FALSE

END IF

EXCEPTION

WHEN NO_DATA_FOUNDTHEN

RETURN NULL

END

/

[oracle@localhost notes] $vim s81_1.sql

SET SERVEROUTPUT ON

BEGIN

IF (check_sal ISNULL) THEN

DBMS_OUTPUT.PUT_LINE ('Thefunction returned NULL due to exception')

ELSIF (check_sal) THEN

DBMS_OUTPUT.PUT_LINE ('Salary > average')

ELSE

DBMS_OUTPUT.PUT_LINE ('Salary

@ notes/s81.sql

Function created.

SQL > @ notes/s81_1.sql

Salary > average

PL/SQL procedure successfully completed

= Example 2 =

[oracle@localhost notes] $vim s82.sql

CREATE OR REPLACE FUNCTION tax (p_value IN NUMBER)

RETURN NUMBER IS

BEGIN

RETURN (p_value * 0.08)

END tax

/

SELECT employee_id, last_name, salary, tax (salary)

FROM employees

WHERE department_id = 100

SQL > @ notes/s82.sql

Function created.

EMPLOYEE_ID LAST_NAME SALARY TAX (SALARY)

--

108Greenberg 12008 960.64

109Faviet 9000 720

110 Chen 8200 656

111Sciarra 7700 616

112 Urman 7800 624

113 Popp 6900 552

6 rows selected.

= Example 3 =

[oracle@localhost notes] $vim s87.sql

CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER)

RETURN NUMBER

IS

BEGIN

INSERT INTOemployees (employee_id, last_name, email, hire_date, job_id, salary)

VALUES (1 VALUES, 'jfrost@company.com', SYSDATE,' SA_MAN', p_sal)

RETURN (pendant sal100)

END

/

UPDATE employees SET salary = dml_call_sql (2000)

WHERE employee_id= 170

SQL > @ notes/s87.sql

Function created.

UPDATEemployees SET salary = dml_call_sql (2000)

*

ERROR at line 1:

ORA-04091: table HR.EMPLOYEES is mutating, trigger/functionmay not see it

ORA-06512: at "HR.DML_CALL_SQL", line 5

[oracle@localhost notes] $vim s87.sql

CREATE OR REPLACE FUNCTION dml_call_sql (p_sal NUMBER)

RETURN NUMBER

IS

BEGIN

RETURN (pendant sal100)

END

/

UPDATE employeesSET salary = dml_call_sql (2000)

WHERE employee_id = 170

SQL > @ notes/s87.sql

Function created.

1 row updated.

= Example 4: Can't searchthe same table=

[oracle@localhost notes] $vims88.sql

CREATE OR REPLACE FUNCTIONdml_call_sql (p_sal NUMBER)

RETURN NUMBER

IS

Name employees.last_name%TYPE

BEGIN

SELECT last_name

INTO name

FROM employees

WHERE employee_id = 170

RETURN (p_sal + 100)

END

/

UPDATE employees SET salary = dml_call_sql (2000)

WHERE employee_id = 170

SQL > @ notes/s88.sql

Function created.

UPDATE employees SET salary = dml_call_sql (2000)

*

ERROR at line 1:

ORA-04091: table HR.EMPLOYEES ismutating, trigger/function may not see it

ORA-06512: at "HR.DML_CALL_SQL", line 9

= Example 5: Can searchanother table====

[oracle@localhost notes] $vims89.sql

CREATE OR REPLACE FUNCTIONdml_call_sql (p_sal NUMBER)

RETURN NUMBER

IS

Id departments.department_id%TYPE

BEGIN

SELECT department_id

INTO id

FROM departments

WHERE department_id = 270

DBMS_OUTPUT.PUT_LINE ('Department ID is:' | | id)

RETURN (p_sal + 100)

END

/

UPDATE employees SET salary = dml_call_sql (2000)

WHERE employee_id = 170

SQL > @ notes/s89.sql

Function created.

Department ID is: 270

1 row updated.

= Example 6 =

[oracle@localhostnotes] $vim s90.sql

CREATE OR REPLACEFUNCTION f (

P_parameter_1 IN NUMBER DEFAULT 1

P_parameter_5 IN NUMBER DEFAULT 5)

RETURN NUMBER

IS

V_var number

BEGIN

V_var: = p_parameter_1 + (p_parameter_5 * 2)

RETURN v_var

END f

/

SELECTf (p_parameter_5 = > 10) FROM DUAL

SQL > @ notes/s90.sql

Function created.

F (Please PARAMETERTER 5 = > 10)

-

twenty-one

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.