Creation and use of 15.pl_sql--function

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/53/wKioL1QTAgnQa1thAAFl7T5FNfc880.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1qtagnqa1thaafl7t5fnfc880.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/51/wKiom1QTAfjDemajAAFrPKHl4XU190.png "style=" float: none; "title=" 2.PNG "alt=" Wkiom1qtafjdemajaafrpkhl4xu190.png "/>

================example 1===============

[Email protected] 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;

/

[Email protected] 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 < average ');

END IF;

END;

/

Sql> @notes/s81.sql

Function created.

Sql> @notes/s81_1.sql

Salary > Average

PL/SQL procedure successfully completed

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/53/wKioL1QTAtDQoiRCAAHlLU_Scio374.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1qtatdqoircaahllu_scio374.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/51/wKiom1QTAsSgHwZYAAIgd6UCxwQ396.jpg "style=" float: none; "title=" 4.png "alt=" Wkiom1qtassghwzyaaigd6ucxwq396.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/51/wKiom1QTAs2CqLiaAAHNveRsGpM612.jpg "style=" float: none; "title=" 5.png "alt=" Wkiom1qtas2cqliaaahnversgpm612.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/53/wKioL1QTAuKBEe4eAAGf9CjZobQ148.jpg "style=" float: none; "title=" 6.png "alt=" Wkiol1qtaukbee4eaagf9cjzobq148.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/51/wKiom1QTAtWhwYE8AAFysddg_OA360.jpg "style=" float: none; "title=" 7.png "alt=" Wkiom1qtatwhwye8aafysddg_oa360.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/53/wKioL1QTAujw8hPfAAKMTpJvrUI534.jpg "style=" float: none; "title=" 8.png "alt=" Wkiol1qtaujw8hpfaakmtpjvrui534.jpg "/>

===========example 2==============

[Email protected] 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

Chen 8200 656

111Sciarra 7700 616

Urman 7800 624

113 Popp 6900 552

6 rows selected.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/51/wKiom1QTAzXjrmM-AAD64lolLIU397.jpg "title=" 9.png " alt= "Wkiom1qtazxjrmm-aad64lolliu397.jpg"/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/53/wKioL1QTA4rjCLI4AAG5CXjU9yA562.jpg "style=" float: none; "title=" 10.png "alt=" Wkiol1qta4rjcli4aag5cxju9ya562.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/51/wKiom1QTA3qD5dF2AAH8YxP5QvU748.jpg "style=" float: none; "title=" 11.png "alt=" Wkiom1qta3qd5df2aah8yxp5qvu748.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/53/wKioL1QTA-eyDDW1AAFcMIdkA7I088.jpg "style=" float: none; "title=" 12.png "alt=" Wkiol1qta-eyddw1aafcmidka7i088.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/51/wKiom1QTA9egMxFfAAHKd7UdeL0149.jpg "style=" float: none; "title=" 13.png "alt=" Wkiom1qta9egmxffaahkd7udel0149.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/53/wKioL1QTA-jyiwmlAAGz89DjGb0162.jpg "style=" float: none; "title=" 14.png "alt=" Wkiol1qta-jyiwmlaagz89djgb0162.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/49/51/wKiom1QTA9eyze6dAAIlwmXMGX0044.jpg "style=" float: none; "title=" 15.png "alt=" Wkiom1qta9eyze6daailwmxmgx0044.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/53/wKioL1QTA-nzJ6JpAAIh84tuaMM204.jpg "style=" float: none; "title=" 16.png "alt=" Wkiol1qta-nzj6jpaaih84tuamm204.jpg "/>

================example 3=====================

[Email protected] 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, ' Frost ', ' [email protected] ', sysdate, ' Sa_man ', p_sal);

RETURN (p_sal+ 100);

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 don't see it

Ora-06512:at "HR. Dml_call_sql ", line 5

[Email protected] notes]$ vim S87.sql

CREATE OR REPLACE FUNCTION dml_call_sql (p_sal number)

RETURN number

Is

BEGIN

RETURN (p_sal+ 100);

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===================

[Email protected] 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 isn't see it

Ora-06512:at "HR. Dml_call_sql ", line 9

==================example 5:can Searchanother table========================

[Email protected] 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.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/49/51/wKiom1QTBGTC5-bLAAIrymgy05E370.jpg "title=" 1.png " alt= "Wkiom1qtbgtc5-blaairymgy05e370.jpg"/>

==================example 6=========================

[Email protected]]$ 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);

RETURN V_var;

END F;

/

SELECTF (p_parameter_5 = ten) from DUAL;

Sql> @notes/s90.sql

Function created.

F (P_PARAMETER_5=>10)

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

21st

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/49/51/wKiom1QTBL-BKuWlAACVXFOgQHY805.jpg "title=" 2.png " alt= "Wkiom1qtbl-bkuwlaacvxfogqhy805.jpg"/>
650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/49/53/wKioL1QTBN6iWPQnAADjvgixzng510.png "title=" 3.PNG " alt= "Wkiol1qtbn6iwpqnaadjvgixzng510.png"/>

This article is from the "Big sword without front of the great Qiao Not Work" blog, please make sure to keep this source http://wuyelan.blog.51cto.com/6118147/1551787

Creation and use of 15.pl_sql--function