Oracle PL/SQL/Stored Procedure Function/Function Definition
Содержание
- 1 A function is executed like any other SQL built-in function:
- 2 A local function
- 3 A stored function.
- 4 Count Employee from a function and return value back
- 5 Define and call a function
- 6 Define and use function in select clause
- 7 demonstrates the behavior of the DETERMINISTIC keyword.
- 8 Function to convert celsius to fahrenheit
- 9 Function to convert fahrenheit to celsius
- 10 function with no return type
- 11 How stored functions can be called from SQL
- 12 Raise exception from inner function
- 13 Recursive function
- 14 Recursive function 2
- 15 Recursive function Factorial
- 16 Use user-defined function in if statement
A function is executed like any other SQL built-in function:
SQL>
SQL> create or replace function calculate_area
2 (v_length in number, v_width in number)
3 return number
4 is
5 v_area number;
6 begin
7 v_area := v_length * v_width;
8 return v_area;
9 end;
10 /
Function created.
SQL>
SQL>
A local function
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 CURSOR myAllLecturer IS
3 SELECT first_name, last_name
4 FROM lecturer;
5
6 v_FormattedName VARCHAR2(50);
7
8 FUNCTION FormatName(p_FirstName IN VARCHAR2,
9 p_LastName IN VARCHAR2)
10 RETURN VARCHAR2 IS
11 BEGIN
12 RETURN p_FirstName || " " || p_LastName;
13 END FormatName;
14
15 BEGIN
16 FOR v_StudentRecord IN myAllLecturer LOOP
17 v_FormattedName :=
18 FormatName(v_StudentRecord.first_name,
19 v_StudentRecord.last_name);
20 DBMS_OUTPUT.PUT_LINE(v_FormattedName);
21 END LOOP;
22 END;
23 /
Scott Lawson
Mar Wells
Jone Bliss
Man Kyte
Pat Poll
Tim Viper
Barbara Blues
David Large
Chris Elegant
Rose Bond
Rita Johnson
Sharon Clear
PL/SQL procedure successfully completed.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
A stored function.
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION AlmostFull (
2 p_Department session.department%TYPE,
3 p_Course session.course%TYPE)
4 RETURN BOOLEAN IS
5
6 studentCount NUMBER;
7 studentMax NUMBER;
8 v_ReturnValue BOOLEAN;
9 v_FullPercent CONSTANT NUMBER := 80;
10 BEGIN
11 SELECT current_lecturer, max_lecturer
12 INTO studentCount, studentMax
13 FROM session
14 WHERE department = p_Department
15 AND course = p_Course;
16
17 IF (studentCount / studentMax * 100) >= v_FullPercent THEN
18 v_ReturnValue := TRUE;
19 ELSE
20 v_ReturnValue := FALSE;
21 END IF;
22
23 RETURN v_ReturnValue;
24 END AlmostFull;
25 /
Function created.
SQL>
SQL>
SQL>
SQL> drop table session;
Table dropped.
SQL>
SQL>
Count Employee from a function and return value back
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
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 emp e
7 where e.deptno = p_deptno;
8 return (cnt);
9 end;
10 /
Function created.
SQL>
SQL> drop table emp;
Table dropped.
Define and call a function
SQL>
SQL>
SQL> -- A function block.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 temp NUMBER;
3
4 FUNCTION iifn(boolean_expression IN BOOLEAN,
5 true_number IN NUMBER,
6 false_number IN NUMBER)
7 RETURN NUMBER IS
8 BEGIN
9 IF boolean_expression THEN
10 RETURN true_number;
11 ELSIF NOT boolean_expression THEN
12 RETURN false_number;
13 ELSE
14 RETURN NULL;
15 END IF;
16 END;
17
18 BEGIN
19 DBMS_OUTPUT.PUT_LINE(iifn(2 > 1,1,0));
20 DBMS_OUTPUT.PUT_LINE(iifn(2 > 3,1,0));
21
22 temp := iifn(null,1,0);
23 IF temp IS NULL THEN
24 DBMS_OUTPUT.PUT_LINE("NULL");
25 ELSE
26 DBMS_OUTPUT.PUT_LINE(temp);
27 END IF;
28 END;
29 /
1
0
NULL
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
Define and use function in select clause
SQL>
SQL>
SQL> -- The SS_THRESH function.
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;
8 /
Function created.
SQL>
SQL> select ss_thresh() from dual;
SS_THRESH()
-----------
65400
SQL>
demonstrates the behavior of the DETERMINISTIC keyword.
SQL>
SQL> set echo on
SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL>
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
SQL>
SQL> CREATE OR REPLACE FUNCTION lecturerStatus(
2 p_NumCredits IN NUMBER)
3 RETURN VARCHAR2 AS
4 BEGIN
5 IF p_NumCredits = 0 THEN
6 RETURN "Inactive";
7 ELSIF p_NumCredits <= 12 THEN
8 RETURN "Part Time";
9 ELSE
10 RETURN "Full Time";
11 END IF;
12 END lecturerStatus;
13 /
Function created.
SQL>
SQL> SELECT id
2 FROM lecturer
3 WHERE SUBSTR(lecturerStatus(current_credits), 1, 20) =
4 "Part Time";
ID
----------
10001
10002
10003
10004
10005
10006
6 rows selected.
SQL>
SQL> CREATE OR REPLACE FUNCTION lecturerStatus(p_NumCredits IN NUMBER)
2 RETURN VARCHAR2
3 DETERMINISTIC AS
4 BEGIN
5 IF p_NumCredits = 0 THEN
6 RETURN "Inactive";
7 ELSIF p_NumCredits <= 12 THEN
8 RETURN "Part Time";
9 ELSE
10 RETURN "Full Time";
11 END IF;
12 END lecturerStatus;
13 /
Function created.
SQL>
SQL> CREATE INDEX lecturer_index ON lecturer
2 (SUBSTR(lecturerStatus(current_credits), 1, 20))
3 COMPUTE STATISTICS;
Index created.
SQL>
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;
Session altered.
SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;
Session altered.
SQL>
SQL> SELECT /*+ index(lecturer,lecturer_index) */ id
2 FROM lecturer
3 WHERE SUBSTR(lecturerStatus(current_credits), 1, 20) =
4 "Part Time";
ID
----------
10001
10002
10003
10004
10005
10006
6 rows selected.
SQL>
SQL> DROP INDEX lecturer_index;
Index dropped.
SQL>
SQL> drop table lecturer;
Table dropped.
Function to convert celsius to fahrenheit
SQL> CREATE OR REPLACE FUNCTION celsius_to_fahrenheit (degree NUMBER) RETURN NUM
BER IS
2 buffer NUMBER;
3 BEGIN
4 buffer := (degree * 9/5) + 32;
5 RETURN buffer;
6 END celsius_to_fahrenheit;
7 /
Function created.
SQL>
SQL>
Function to convert fahrenheit to celsius
SQL> CREATE OR REPLACE FUNCTION fahrenheit_to_celsius (degree NUMBER) RETURN NUMBER IS
2 buffer NUMBER;
3
4 BEGIN
5 buffer := (5/9) * (degree - 32);
6 RETURN buffer;
7 END fahrenheit_to_celsius;
8
9 /
Function created.
SQL>
SQL>
function with no return type
SQL>
SQL> create or replace function no_return_type as
2 begin
3 return null;
4 end no_return_type;
5 /
Warning: Function created with compilation errors.
SQL>
SQL> show errors
Errors for FUNCTION NO_RETURN_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
1/25 PLS-00103: Encountered the symbol "AS" when expecting one of the
following:
( return compress compiled wrapped
SQL>
SQL>
How stored functions can be called from SQL
SQL> CREATE TABLE emp (
2 id NUMBER PRIMARY KEY,
3 fname VARCHAR2(50),
4 lname VARCHAR2(50)
5 );
Table created.
SQL>
SQL> INSERT INTO emp (id, fname, lname)VALUES (1, "A", "B");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (2, "C", "D");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (3, "Enn", "F");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (4, "G", "H");
1 row created.
SQL> INSERT INTO emp (id, fname, lname)VALUES (5, "G", "Z");
1 row created.
SQL>
SQL> CREATE TABLE myTable
2 (num_col NUMBER
3 ,char_col VARCHAR2(60));
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION FullName (p_empID emp.ID%TYPE) RETURN VARCHAR2 IS
2
3 v_Result VARCHAR2(100);
4 BEGIN
5 SELECT fname || " " || lname INTO v_Result FROM emp WHERE ID = p_empID;
6
7 RETURN v_Result;
8 END FullName;
9 /
Function created.
SQL>
SQL>
SQL> SELECT FullName(ID) full_name FROM emp WHERE ID < 10 ORDER BY full_name;
FULL_NAME
--------------------------------------------------------------------------------
A B
C D
Enn F
G H
G Z
5 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table myTable;
Table dropped.
Raise exception from inner function
SQL> DECLARE
2 myException EXCEPTION;
3 FUNCTION innerFunction
4 RETURN BOOLEAN IS
5 retval BOOLEAN := FALSE;
6 BEGIN
7 RAISE myException;
8 RETURN retval;
9 END;
10 BEGIN
11 IF innerFunction THEN
12 dbms_output.put_line("No raised exception");
13 END IF;
14 EXCEPTION
15 WHEN others THEN
16 dbms_output.put_line("DBMS_UTILITY.FORMAT_ERROR_BACKTRACE");
17 dbms_output.put_line(dbms_utility.format_error_backtrace);
18
19 END;
20 /
PL/SQL procedure successfully completed.
Recursive function
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create or replace procedure company_listing (
2 p_start_with varchar2,
3 p_level number default 0 ) as
4 begin
5 dbms_output.put_line( lpad( " ", p_level*2, " " ) || p_start_with );
6 for c in ( select * from emp
7 where mgr in ( select empno from emp where ename = p_start_with )
8 order by ename )
9 loop
10 company_listing( c.ename, p_level+1 );
11 end loop;
12 end company_listing;
13 /
Procedure created.
SQL>
SQL> set serveroutput on format wrapped
SQL>
SQL> exec company_listing( "KING" );
KING
BLAKE
ALLEN
JAMES
MARTIN
TURNER
WARD
CLARK
MILLER
JONES
FORD
SMITH
SCOTT
ADAMS
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp cascade constraints;
Table dropped.
SQL> drop table dept cascade constraints;
Table dropped.
SQL>
Recursive function 2
SQL> CREATE OR REPLACE FUNCTION Fib(n IN BINARY_INTEGER)
2 RETURN BINARY_INTEGER AS
3 BEGIN
4 RETURN Fib(n - 1) + Fib(n - 2);
5 END Fib;
6 /
Function created.
SQL>
SQL> CREATE OR REPLACE FUNCTION Fib(n IN BINARY_INTEGER)
2 RETURN BINARY_INTEGER AS
3 BEGIN
4 IF n = 0 OR n = 1 THEN
5 RETURN n;
6 ELSE
7 RETURN Fib(n - 1) + Fib(n - 2);
8 END IF;
9 END Fib;
10 /
Function created.
SQL>
SQL> set serveroutput on
SQL>
SQL> BEGIN
2 FOR v_Count IN 1..10 LOOP
3 DBMS_OUTPUT.PUT_LINE(
4 "Fib(" || v_Count || ") is " || Fib(v_Count));
5 END LOOP;
6 END;
7 /
Fib(1) is 1
Fib(2) is 1
Fib(3) is 2
Fib(4) is 3
Fib(5) is 5
Fib(6) is 8
Fib(7) is 13
Fib(8) is 21
Fib(9) is 34
Fib(10) is 55
PL/SQL procedure successfully completed.
SQL>
SQL>
Recursive function Factorial
SQL>
SQL> CREATE OR REPLACE FUNCTION Factorial(p_MyNum INTEGER)
2 RETURN NUMBER AS
3 BEGIN -- Start of Factorial Function
4 IF p_MyNum = 1 THEN -- Checking for last value to process of n-1
5 RETURN 1;
6 ELSE
7 RETURN(p_MyNum * Factorial(p_MyNum-1)); -- Recursive
8 END IF;
9 END;
10 /
Function created.
SQL>
SQL> DECLARE
2 v_test NUMBER := 10;
3 v_Counter INTEGER ; -- Counter for For Loop
4 BEGIN
5 FOR v_Counter IN 1..v_test LOOP
6 DBMS_OUTPUT.PUT_LINE("The factorial of " ||
7 v_Counter || " is " || factorial(v_Counter));
8 END LOOP;
9 END;
10 /
The factorial of 1 is 1
The factorial of 2 is 2
The factorial of 3 is 6
The factorial of 4 is 24
The factorial of 5 is 120
The factorial of 6 is 720
The factorial of 7 is 5040
The factorial of 8 is 40320
The factorial of 9 is 362880
The factorial of 10 is 3628800
PL/SQL procedure successfully completed.
SQL>
SQL> --
Use user-defined function in if statement
SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL>
SQL> CREATE TABLE session (
2 department CHAR(3),
3 course NUMBER(3),
4 description VARCHAR2(2000),
5 max_lecturer NUMBER(3),
6 current_lecturer NUMBER(3),
7 num_credits NUMBER(1),
8 room_id NUMBER(5)
9 );
Table created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created.
SQL>
SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created.
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION AlmostFull (
2 p_Department session.department%TYPE,
3 p_Course session.course%TYPE)
4 RETURN BOOLEAN IS
5
6 studentCount NUMBER;
7 studentMax NUMBER;
8 v_ReturnValue BOOLEAN;
9 v_FullPercent CONSTANT NUMBER := 80;
10 BEGIN
11 SELECT current_lecturer, max_lecturer
12 INTO studentCount, studentMax
13 FROM session
14 WHERE department = p_Department
15 AND course = p_Course;
16
17 IF (studentCount / studentMax * 100) >= v_FullPercent THEN
18 v_ReturnValue := TRUE;
19 ELSE
20 v_ReturnValue := FALSE;
21 END IF;
22
23 RETURN v_ReturnValue;
24 END AlmostFull;
25 /
Function created.
SQL>
SQL>
SQL> DECLARE
2 CURSOR c_session IS
3 SELECT department, course
4 FROM session;
5 BEGIN
6 FOR v_ClassRecord IN c_session LOOP
7 IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
8 DBMS_OUTPUT.PUT_LINE(v_ClassRecord.department || " " || v_ClassRecord.course || " is almost full!");
9 END IF;
10 END LOOP;
11 END;
12 /
MUS 410 is almost full!
PL/SQL procedure successfully completed.
SQL>
SQL> drop table session;
Table dropped.