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