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:
<source lang="sql">
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>
</source>
A local function
<source lang="sql">
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>
</source>
A stored function.
<source lang="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> SQL> drop table session; Table dropped. SQL> SQL>
</source>
Count Employee from a function and return value back
<source lang="sql">
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.
</source>
Define and call a function
<source lang="sql">
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>
</source>
Define and use function in select clause
<source lang="sql">
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>
</source>
demonstrates the behavior of the DETERMINISTIC keyword.
<source lang="sql">
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.
</source>
Function to convert celsius to fahrenheit
<source lang="sql">
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>
</source>
Function to convert fahrenheit to celsius
<source lang="sql">
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>
</source>
function with no return type
<source lang="sql">
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>
</source>
How stored functions can be called from SQL
<source lang="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.
</source>
Raise exception from inner function
<source lang="sql">
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.
</source>
Recursive function
<source lang="sql">
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>
</source>
Recursive function 2
<source lang="sql">
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>
</source>
Recursive function Factorial
<source lang="sql">
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> --
</source>
Use user-defined function in if statement
<source lang="sql">
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.
</source>
