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.
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
Calling a Function for a table
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>
Create a simple function
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.
CREATE OR REPLACE a function
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>
create or replace FUNCTION iifn(boolean_expression in BOOLEAN,true_number IN NUMBER,false_number IN NUMBER)
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;
/
Creating a Function to deal with business rules
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>
Dependency Example
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.
Describe a user-defined function
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.
Exit a function
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>
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:
CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN type
{IS | AS}
BEGIN
function_body
END function_name;
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
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>
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.
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>
Local Subprograms
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>
PLW-05005: function NO_RETURN returns without value at line 21
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>
Storing PL/SQL function in the Database
create [or replace]
function function name (parameters)
return ...
is
...
begin
...
end;
The FullName Function
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>
Use function to check passwords
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>