Oracle PL/SQL/Stored Procedure Function/Function Parameters
Содержание
- 1 A stored function with no parameters.
- 2 Boolean value function parameter
- 3 Clob type parameter
- 4 Get circle area
- 5 Mixed Name and Position Notation Calls
- 6 Named Notation
- 7 Pass number value to function
- 8 Pass value to function parameter
- 9 Positional Notation
- 10 The FullName Function
- 11 Use Column type as the function parameter type
- 12 Varray type parameter
A stored function with no parameters.
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE NoParamsP AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE("No Paramaters!");
4 END NoParamsP;
5 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE FUNCTION NoParamsF
2 RETURN DATE AS
3 BEGIN
4 RETURN SYSDATE;
5 END NoParamsF;
6 /
Function created.
SQL>
SQL> BEGIN
2 NoParamsP;
3 DBMS_OUTPUT.PUT_LINE("Calling NoParamsF on " || TO_CHAR(NoParamsF, "DD-MON-YYYY"));
4 END;
5 /
No Paramaters!
Calling NoParamsF on 18-JUN-2008
PL/SQL procedure successfully completed.
SQL>
Boolean value function parameter
SQL>
SQL> create or replace
2 function ite(
3 p_expression boolean,
4 p_true varchar2,
5 p_false varchar2 ) return varchar2 as
6 begin
7 if p_expression then
8 return p_true;
9 end if;
10 return p_false;
11 end ite;
12 /
Function created.
SQL>
SQL> set serverout on
SQL>
SQL>
SQL> exec dbms_output.put_line( ite( 1=2, "Equal", "Not Equal" ) );
Not Equal
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_output.put_line( ite( 2>3, "True", "False" ) );
False
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Clob type parameter
SQL> CREATE TABLE myTable (
2 id INTEGER PRIMARY KEY,
3 clob_column CLOB NOT NULL
4 );
Table created.
SQL>
SQL> -- create the PL/SQL procedures and methods
SQL> CREATE OR REPLACE PROCEDURE myProcedure(clob_par IN OUT CLOB,id_par IN INTEGER) IS
2 BEGIN
3 SELECT clob_column INTO clob_par FROM myTable WHERE id = id_par;
4 END myProcedure;
5 /
Procedure created.
SQL>
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
Get circle area
SQL> CREATE OR REPLACE FUNCTION circle_area (p_radius IN NUMBER) RETURN NUMBER AS
2 v_pi NUMBER := 3.1415926;
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>
Mixed Name and Position Notation Calls
SQL> CREATE OR REPLACE FUNCTION add_three_numbers( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
2 BEGIN
3 RETURN a + b + c;
4 END;
5 /
Function created.
Named Notation
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION add_three_numbers(a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
2 BEGIN
3 RETURN a + b + c;
4 END;
5 /
Function created.
SQL>
SQL> BEGIN
2 dbms_output.put_line(add_three_numbers(c => 4,b => 5,c => 3));
3 END;
4 /
dbms_output.put_line(add_three_numbers(c => 4,b => 5,c => 3));
*
ERROR at line 2:
ORA-06550: line 2, column 24:
PLS-00703: multiple instances of named argument in list
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
Pass number value to function
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>
Pass value to function parameter
SQL> -- The stored function squareme.
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>
SQL>
SQL> -- Passing parameters to squareme.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("9 squared is " || squareme(9) );
3 END;
4
5 /
9 squared is 81
PL/SQL procedure successfully completed.
Positional Notation
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION add_three_numbers( a NUMBER := 0, b NUMBER := 0, c NUMBER := 0 ) RETURN NUMBER IS
2 BEGIN
3 RETURN a + b + c;
4 END;
5 /
Function created.
SQL>
SQL> BEGIN
2 dbms_output.put_line(add_three_numbers(3,4,5));
3 END;
4 /
PL/SQL procedure successfully completed.
The FullName 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> CREATE OR REPLACE FUNCTION FullName (
2 p_ID lecturer.ID%TYPE)
3 RETURN VARCHAR2 IS
4
5 myResult VARCHAR2(100);
6 BEGIN
7 SELECT first_name || " " || last_name
8 INTO myResult
9 FROM lecturer
10 WHERE ID = p_ID;
11
12 RETURN myResult;
13 END FullName;
14 /
Function created.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL>
SQL>
Use Column type as the function parameter type
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> 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 TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION CountCredits (
2 p_ID IN lecturer.ID%TYPE)
3 RETURN NUMBER AS
4
5 v_TotalCredits NUMBER := 0; -- Total number of credits
6 v_CourseCredits NUMBER; -- Credits for one course
7 CURSOR c_RegisteredCourses IS
8 SELECT department, course
9 FROM myStudent
10 WHERE student_id = p_ID;
11 BEGIN
12 FOR v_CourseRec IN c_RegisteredCourses LOOP
13 SELECT num_credits
14 INTO v_CourseCredits
15 FROM session
16 WHERE department = v_CourseRec.department AND course = v_CourseRec.course;
17
18 v_TotalCredits := v_TotalCredits + v_CourseCredits;
19 END LOOP;
20
21 RETURN v_TotalCredits;
22 END CountCredits;
23 /
Function created.
SQL>
SQL> select CountCredits(10002) from dual;
COUNTCREDITS(10002)
-------------------
8
1 row selected.
SQL>
SQL> drop table lecturer;
Table dropped.
SQL> drop table session;
Table dropped.
SQL>
SQL> drop table myStudent;
Table dropped.
SQL>
SQL>
--
Varray type parameter
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", "TRAINER", 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", "TRAINER", 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", "TRAINER", 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", "TRAINER", 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>
SQL> create table e
2 as
3 select empno, ename, init, mgr, deptno
4 from emp;
Table created.
SQL>
SQL>
SQL> create or replace type numberlist_t
2 as varray(4) of varchar2(20);
3 /
Type created.
SQL>
SQL> column numlist format a60
SQL>
SQL> alter table e add (numlist numberlist_t);
Table altered.
SQL>
SQL> describe e
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(8)
INIT VARCHAR2(5)
MGR NUMBER(4)
DEPTNO NUMBER(2)
NUMLIST NUMBERLIST_T
SQL>
SQL> create or replace function ext
2 (p_varray_in numberlist_t)
3 return varchar2
4 is
5 v_ext varchar2(20);
6 begin
7 v_ext := p_varray_in(1);
8 return v_ext;
9 end;
10 /
Function created.
SQL>
SQL>
SQL> drop table e;
Table dropped.
SQL> drop table emp;
Table dropped.