Oracle PL/SQL Tutorial/Function Procedure Packages/Function
Содержание
- 1 Calling a Function
- 2 Calling a Function for a table
- 3 Create a simple function
- 4 CREATE OR REPLACE a function
- 5 create or replace FUNCTION iifn(boolean_expression in BOOLEAN,true_number IN NUMBER,false_number IN NUMBER)
- 6 Creating a Function to deal with business rules
- 7 Dependency Example
- 8 Describe a user-defined function
- 9 Exit a function
- 10 Functions
- 11 Function without parameters
- 12 How to Call a Function from PL/SQL With or Without Parameters
- 13 Local Subprograms
- 14 PLW-05005: function NO_RETURN returns without value at line 21
- 15 Storing PL/SQL function in the Database
- 16 The FullName Function
- 17 Use function to check passwords
Calling a Function
You call your own functions as you would call any of the built-in database functions.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE FUNCTION circle_area (p_radius IN NUMBER) RETURN NUMBER AS
2 v_pi NUMBER := 3.14; 3 v_area NUMBER; 4 BEGIN 5 v_area := v_pi * POWER(p_radius, 2); 6 RETURN v_area; 7 END circle_area; 8 /
Function created. SQL> SQL> select circle_area(12) from dual; CIRCLE_AREA(12)
452.16</source>
Calling a Function for a table
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION average_salary RETURN NUMBER AS
2 v_average_salary NUMBER; 3 BEGIN 4 SELECT AVG(salary) 5 INTO v_average_salary 6 FROM employee; 7 RETURN v_average_salary; 8 END average_salary; 9 /
Function created. SQL> SQL> select average_salary from dual; AVERAGE_SALARY
4071.7525
SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL></source>
Create a simple function
<source lang="sql">
SQL> create or replace FUNCTION iifn(boolean_expression in BOOLEAN,
2 true_number IN NUMBER, 3 false_number IN NUMBER) 4 RETURN NUMBER IS 5 BEGIN 6 IF boolean_expression THEN 7 RETURN true_number; 8 ELSIF NOT boolean_expression THEN 9 RETURN false_number; 10 ELSE 11 --nulls propagate, i.e. null input yields null output. 12 RETURN NULL; 13 END IF; 14 END; 15 /
Function created.</source>
CREATE OR REPLACE a function
<source lang="sql">
SQL> set serveroutput on SQL> set echo on SQL> SQL> CREATE OR REPLACE FUNCTION ss_thresh
2 RETURN NUMBER AS 3 x NUMBER(9,2); 4 BEGIN 5 x := 65400; 6 RETURN x; 7 END ss_thresh; 8 /
Function created. SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("ss_thresh " || ss_thresh());
3 END;
4 /
ss_thresh 65400 PL/SQL procedure successfully completed. SQL> SQL></source>
create or replace FUNCTION iifn(boolean_expression in BOOLEAN,true_number IN NUMBER,false_number IN NUMBER)
<source lang="sql">
RETURN NUMBER IS BEGIN
IF boolean_expression THEN
RETURN true_number;
ELSIF NOT boolean_expression THEN
RETURN false_number;
ELSE
RETURN NULL;
END IF;
END;
/</source>
Creating a Function to deal with business rules
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE FUNCTION AlmostFull (
2 p_first_name employee.first_name%TYPE, 3 p_last_name employee.last_name%TYPE) 4 RETURN BOOLEAN IS 5 6 v_CurrentEmployees NUMBER; 7 v_MaxEmployees NUMBER; 8 v_ReturnValue BOOLEAN; 9 v_FullPercent CONSTANT NUMBER := 90; 10 BEGIN 11 SELECT salary 12 INTO v_CurrentEmployees 13 FROM employee 14 WHERE first_name = p_first_name 15 AND last_name = p_last_name; 16 17 SELECT max(salary) 18 INTO v_MaxEmployees 19 FROM employee; 20 21 22 IF (v_CurrentEmployees / v_MaxEmployees * 100) > v_FullPercent THEN 23 v_ReturnValue := TRUE; 24 ELSE 25 v_ReturnValue := FALSE; 26 END IF; 27 28 RETURN v_ReturnValue; 29 END AlmostFull; 30 /
SP2-0806: Function created with compilation warnings SQL> SQL> SQL> DECLARE
2 CURSOR c_employee IS 3 SELECT first_name, last_name 4 FROM employee; 5 BEGIN 6 FOR v_employee IN c_employee LOOP 7 IF AlmostFull(v_employee.first_name, v_employee.last_name) THEN 8 DBMS_OUTPUT.put_line(v_employee.first_name || " " || v_employee.last_name || " is almost full!"); 9 END IF; 10 END LOOP; 11 END; 12 /
David Larry is almost full! PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Dependency Example
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE FUNCTION AlmostFull (
2 p_first_name employee.first_name%TYPE, 3 p_last_name employee.last_name%TYPE) 4 RETURN BOOLEAN IS 5 6 v_CurrentEmployees NUMBER; 7 v_MaxEmployees NUMBER; 8 v_ReturnValue BOOLEAN; 9 v_FullPercent CONSTANT NUMBER := 90; 10 BEGIN 11 SELECT salary 12 INTO v_CurrentEmployees 13 FROM employee 14 WHERE first_name = p_first_name 15 AND last_name = p_last_name; 16 17 SELECT max(salary) 18 INTO v_MaxEmployees 19 FROM employee; 20 21 22 IF (v_CurrentEmployees / v_MaxEmployees * 100) > v_FullPercent THEN 23 v_ReturnValue := TRUE; 24 ELSE 25 v_ReturnValue := FALSE; 26 END IF; 27 28 RETURN v_ReturnValue; 29 END AlmostFull; 30 /
SP2-0806: Function created with compilation warnings SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE RecordFullClasses AS
2 CURSOR c_employee IS 3 SELECT first_name, last_name 4 FROM employee; 5 BEGIN 6 FOR v_employee IN c_employee LOOP 7 IF AlmostFull(v_employee.first_name, v_employee.last_name) THEN 8 DBMS_OUTPUT.put_line(v_employee.first_name || " " || v_employee.last_name || " is almost full!"); 9 END IF; 10 END LOOP; 11 END RecordFullClasses; 12 /
Procedure created. SQL> SQL> SQL> call RecordFullClasses(); David Larry is almost full! Call completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped.</source>
Describe a user-defined function
<source lang="sql">
SQL> SQL> create table employees(
2 empno NUMBER(4) 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , msal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) ) ;
Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> SQL> SQL> create or replace function emp_count(p_deptno in number)
2 return number is 3 cnt number(2) := 0; 4 begin 5 select count(*) into cnt 6 from employees e 7 where e.deptno = p_deptno; 8 return (cnt); 9 end; 10 /
Function created. SQL> SQL> describe emp_count; FUNCTION emp_count RETURNS NUMBER
Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_DEPTNO NUMBER IN
SQL> SQL> drop table employees; Table dropped.</source>
Exit a function
<source lang="sql">
SQL> set serveroutput on SQL> set echo on SQL> SQL> CREATE OR REPLACE function exitfunc(myString VARCHAR2)
2 RETURN NUMBER IS 3 v_current_position INTEGER := 1; 4 v_counter NUMBER := 0; 5 BEGIN 6 WHILE v_current_position <= LENGTH(myString) LOOP 7 IF SUBSTR(myString,v_current_position,1) != " " THEN 8 v_counter := v_counter + 1; 9 ELSE 10 NULL; 11 END IF; 12 v_current_position := v_current_position + 1; 13 EXIT WHEN SUBSTR(myString,v_current_position,1) = " "; 14 END LOOP; 15 RETURN v_counter ; 16 END exitfunc; 17 /
Function created. SQL> SQL> SQL> DECLARE
2 v_MYTEXT VARCHAR2(20) := "THIS IS A TEST";
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Total count is " || exitfunc(v_MYTEXT));
5 END;
6 /
Total count is 4 PL/SQL procedure successfully completed. SQL></source>
Functions
A function is similar to a procedure except that a function must return a value.
You create a function using the CREATE FUNCTION statement.
The simplified syntax for the CREATE FUNCTION statement is as follows:
<source lang="sql">
CREATE [OR REPLACE] FUNCTION function_name [(parameter_name [IN | OUT | IN OUT] type [, ...])] RETURN type {IS | AS} BEGIN
function_body
END function_name;</source>
where
- OR REPLACE specifies the function that is to replace an existing function if present.
- type specifies the PL/SQL type of the parameter.
- The body of a function must return a value of the PL/SQL type specified in the RETURN clause.
Function without parameters
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> CREATE OR REPLACE FUNCTION average_salary RETURN NUMBER AS
2 v_average_salary NUMBER; 3 BEGIN 4 SELECT AVG(salary) 5 INTO v_average_salary 6 FROM employee; 7 RETURN v_average_salary; 8 END average_salary; 9 /
Function created. SQL> SQL> select average_salary from dual; AVERAGE_SALARY
4071.7525
SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> --Dropping a Function SQL> SQL> --You drop a function using DROP FUNCTION. SQL> SQL> CREATE OR REPLACE FUNCTION circle_area (
2 p_radius IN NUMBER 3 ) RETURN NUMBER AS 4 v_pi NUMBER := 3.14; 5 v_area NUMBER; 6 BEGIN 7 v_area := v_pi * POWER(p_radius, 2); 8 RETURN v_area; 9 END circle_area; 10 /
Function created. SQL> SQL> select circle_area(12) from dual; CIRCLE_AREA(12)
452.16
SQL> SQL> DROP FUNCTION circle_area; Function dropped. SQL></source>
How to Call a Function from PL/SQL With or Without Parameters
If there are no parameters to pass, you can simply call the function without the parentheses.
To pass actual values, you can use commas as placeholders for parameters.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE FUNCTION squareme(thenum number)
2 RETURN NUMBER IS 3 BEGIN 4 RETURN thenum * thenum; 5 END squareme; 6 /
Function created. SQL> SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("9 squared is " || squareme(9) );
3 END;
4 /
9 squared is 81 PL/SQL procedure successfully completed. SQL></source>
Local Subprograms
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> DECLARE
2 CURSOR c_AllEmployees IS 3 SELECT first_name, last_name 4 FROM employee; 5 6 v_FormattedName VARCHAR2(50); 7 8 /* Function which will return the first and last name 9 concatenated together, separated by a space. */ 10 FUNCTION FormatName(p_FirstName IN VARCHAR2, 11 p_LastName IN VARCHAR2) 12 RETURN VARCHAR2 IS 13 BEGIN 14 RETURN p_FirstName || " " || p_LastName; 15 END FormatName; 16 17 -- Begin main block. 18 BEGIN 19 FOR v_EmployeeRecord IN c_AllEmployees LOOP 20 v_FormattedName := 21 FormatName(v_EmployeeRecord.first_name, 22 v_EmployeeRecord.last_name); 23 DBMS_OUTPUT.put_line(v_FormattedName); 24 END LOOP; 25 26 COMMIT; 27 END; 28 /
Jason Martin Alison Mathews James Smith Celia Rice Robert Black Linda Green David Larry James Cat PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
PLW-05005: function NO_RETURN returns without value at line 21
<source lang="sql">
SQL> SQL> CREATE OR REPLACE FUNCTION no_return (check_in IN BOOLEAN)
2 RETURN VARCHAR2
3 AS
4 BEGIN
5 IF check_in
6 THEN
7 RETURN "abc";
8 ELSE
9 DBMS_OUTPUT.put_line ("Here I am, here I stay");
10
11 IF check_in
12 THEN
13 RETURN "def";
14 ELSIF SYSDATE IS NOT NULL
15 THEN
16 RETURN "qrs";
17 ELSE
18 DBMS_OUTPUT.put_line ("Hello!");
19 END IF;
20 END IF;
21 END no_return;
22 /
SP2-0806: Function created with compilation warnings SQL> SQL> SHOW ERRORS FUNCTION no_return Errors for FUNCTION NO_RETURN: LINE/COL ERROR
-----------------------------------------------------------------
1/1 PLW-05005: function NO_RETURN returns without value at line 21 SQL></source>
Storing PL/SQL function in the Database
<source lang="sql">
create [or replace] function function name (parameters) return ... is
...
begin
...
end;</source>
The FullName Function
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE FUNCTION FullName (
2 p_ID employee.ID%TYPE) 3 RETURN VARCHAR2 IS 4 5 v_Result VARCHAR2(100); 6 BEGIN 7 SELECT first_name || " " || last_name 8 INTO v_Result 9 FROM employee 10 WHERE ID = p_ID; 11 12 RETURN v_Result; 13 END FullName; 14 /
Function created. SQL> SQL> select FULLName(id) from employee; FULLNAME(ID)
Jason Martin Alison Mathews James Smith Celia Rice Robert Black Linda Green David Larry James Cat 8 rows selected. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Use function to check passwords
<source lang="sql">
SQL> SQL> CREATE OR REPLACE FUNCTION myFunction(in_username IN VARCHAR,in_new_password IN VARCHAR,in_old_password IN VARCHAR)
2 RETURN BOOLEAN 3 AS 4 pwd_okay BOOLEAN; 5 BEGIN 6 IF in_new_password = in_username THEN 7 raise_application_error(-20001, "Password may not be username"); 8 END IF; 9 RETURN TRUE; 10 END myFunction; 11 /
Function created. SQL></source>
