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.
<source lang="sql">
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>
</source>
Boolean value function parameter
<source lang="sql">
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>
</source>
Clob type parameter
<source lang="sql">
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>
</source>
Get circle area
<source lang="sql">
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>
</source>
Mixed Name and Position Notation Calls
<source lang="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.
</source>
Named Notation
<source lang="sql">
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
</source>
Pass number value to function
<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>
Pass value to function parameter
<source lang="sql">
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.
</source>
Positional Notation
<source lang="sql">
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.
</source>
The FullName 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> 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>
</source>
Use Column type as the function parameter type
<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> 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> --
</source>
Varray type parameter
<source lang="sql">
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.
</source>
