Oracle PL/SQL Tutorial/Function Procedure Packages/Parameters
Содержание
- 1 Define function with NUMBER type parameter
- 2 Defining Formal Parameters
- 3 Function without parameter
- 4 Mixed Name and Position Notation Calls
- 5 Parameter Default Values
- 6 Parameter Modes
- 7 Passing parameters to procedures
- 8 Positional Notation
- 9 Positional vs. named parameter passing.
- 10 Specifying procedure or function parameters Positional notation
- 11 Table collection type parameter
- 12 There are three types of formal parameters in subprograms: IN, OUT, and IN OUT.
- 13 Use IF/ELSIF/ELSE to verify the input parameter
- 14 Use mixed notation to avoid the second parameter, but keep the first and third
- 15 Use ROWTYPE as the parameter
- 16 Using Named Notation
Define function with NUMBER type parameter
SQL>
SQL> set serveroutput on
SQL> set echo on
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>
SQL>
SQL>
Defining Formal Parameters
A parameter is a value that you can pass from a block of statements to a function.
The Syntax for Defining a Parameter
parameter_name [MODE] parameter_type [:= value | DEFAULT value]
Function without parameter
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE FUNCTION mypi
2 RETURN NUMBER IS
3 BEGIN
4 NULL;
5 RETURN 3.14;
6 END mypi;
7 /
Function created.
SQL>
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("value of pi is " || mypi);
3 END;
4 /
value of pi is 3.14
PL/SQL procedure successfully completed.
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.
Parameter Default Values
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>
SQL> CREATE OR REPLACE PROCEDURE AddNewEmployee (
2 p_FirstName employee.first_name%TYPE,
3 p_LastName employee.last_name%TYPE,
4 p_City employee.city%TYPE DEFAULT "AAA") AS
5 BEGIN
6 INSERT INTO employee (id, first_name, last_name,city)VALUES ("99",
7 p_FirstName, p_LastName, p_city);
8
9 COMMIT;
10 END AddNewEmployee;
11 /
Procedure created.
SQL>
SQL> DECLARE
2 v_NewFirstName employee.first_name%TYPE := "Margaret";
3 v_NewLastName employee.last_name%TYPE := "Mason";
4 BEGIN
5 -- Add Margaret Mason to the database.
6 AddNewEmployee(v_NewFirstName, v_NewLastName);
7 END;
8 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from employee;
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
99 Margaret Mason AAA
9 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Parameter Modes
SQL>
SQL> CREATE OR REPLACE PROCEDURE ModeTest (
2 p_InParameter IN NUMBER,
3 p_OutParameter OUT NUMBER,
4 p_InOutParameter IN OUT NUMBER) IS
5
6 v_LocalVariable NUMBER;
7 BEGIN
8 v_LocalVariable := p_InParameter; -- Legal
9
10 --p_InParameter := 7; -- Illegal
11
12 p_OutParameter := 7; -- Legal
13
14 --v_LocalVariable := p_outParameter; -- Illegal
15
16 v_LocalVariable := p_InOutParameter; -- Legal
17
18 p_InOutParameter := 7; -- Legal
19 END ModeTest;
20 /
Procedure created.
SQL>
SQL>
Passing parameters to procedures
SQL>
SQL> set SERVEROUTPUT ON
SQL> Create or replace procedure p_helloTo (i varchar2)
2 is
3 v_string varchar2(256):="Hello, "||i||"!";
4 begin
5 dbms_output.put_line(v_string);
6 end;
7 /
Procedure created.
SQL>
SQL> begin
2 p_helloTo("Everybody");
3 p_helloTo("You");
4 p_helloTo("A");
5 p_helloTo("PL/SQL");
6 end;
7 /
Hello, Everybody!
Hello, You!
Hello, A!
Hello, PL/SQL!
PL/SQL procedure successfully completed.
SQL>
SQL>
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.
Positional vs. named parameter passing.
SQL> CREATE OR REPLACE PROCEDURE CallMe(pA VARCHAR2,pB NUMBER,pC BOOLEAN,pD DATE) AS
2 BEGIN
3 NULL;
4 END CallMe;
5 /
SP2-0804: Procedure created with compilation warnings
SQL>
SQL>
SQL> -- This call uses positional notation
SQL> DECLARE
2 v1 VARCHAR2(10);
3 v2 NUMBER(7,6);
4 v3 BOOLEAN;
5 v4 DATE;
6 BEGIN
7 CallMe(v1, v2, v3, v4);
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
Specifying procedure or function parameters Positional notation
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE show_line(ip_line_length IN NUMBER,ip_separator IN VARCHAR2)
2 IS
3
4 myString VARCHAR2(150);
5
6 BEGIN
7
8 FOR idx in 1..ip_line_length LOOP
9
10 myString := myString || ip_separator;
11
12 END LOOP;
13
14 DBMS_OUTPUT.PUT_LINE(myString);
15
16 EXCEPTION WHEN OTHERS THEN
17
18 dbms_output.put_line(SQLERRM);
19
20 END;
21 /
Procedure created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_length NUMBER :=50;
3
4 v_separator VARCHAR2(1):="=";
5
6 BEGIN
7
8 show_line(v_length,v_separator);
9
10 END;
11 /
Table collection type parameter
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE strings_nt IS TABLE OF VARCHAR2(100);
2 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE employees_pkg
2 IS
3 vancouver_employees strings_nt := strings_nt ("R", "H", "D", "S", "C");
4 newyork_employees strings_nt := strings_nt ("H", "S", "A");
5 boston_employees strings_nt := strings_nt ("S", "D");
6
7 PROCEDURE show_employees(title_in IN VARCHAR2,employees_in IN strings_nt);
8 END;
9 /
Package created.
SQL> SHO ERR
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY employees_pkg
2 IS
3 PROCEDURE show_employees (title_in IN VARCHAR2,employees_in IN strings_nt)
4 IS
5 BEGIN
6 DBMS_OUTPUT.put_line (title_in);
7
8 FOR indx IN employees_in.FIRST .. employees_in.LAST
9 LOOP
10 DBMS_OUTPUT.put_line (indx || " = " || employees_in (indx));
11 END LOOP;
12
13 END show_employees;
14 END;
15 /
Package body created.
SQL>
SQL>
SQL> SHOw error
No errors.
SQL>
SQL>
SQL> DECLARE
2 our_favorites strings_nt := strings_nt ();
3 BEGIN
4 our_favorites := employees_pkg.vancouver_employees MULTISET UNION
5 employees_pkg.newyork_employees;
6
7 employees_pkg.show_employees ( "STEVEN then VEVA", our_favorites);
8 END;
9 /
PL/SQL procedure successfully completed.
There are three types of formal parameters in subprograms: IN, OUT, and IN OUT.
IN parameters are used to pass values into the subprogram. but can"t be changed.
IN Parameters are really a constant.
IN Parameters work in only one direction from the main program to subprogram.
SQL> create or replace function getArea (i_rad NUMBER)
2 return NUMBER
3 is
4 begin
5 if i_rad is null -- legal
6 then
7 -- i_rad:=10; -- ILLEGAL
8 return null;
9 end if;
10 return 3.14*(i_rad**2); -- legal
11 end;
12 /
Function created.
SQL>
Use IF/ELSIF/ELSE to verify the input parameter
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> CREATE OR REPLACE FUNCTION emptype (paytype CHAR)
2 RETURN VARCHAR2 IS
3 BEGIN
4 IF paytype = "H" THEN
5 RETURN "Hourly";
6 ELSIF paytype = "S" THEN
7 RETURN "Salaried";
8 ELSIF paytype = "E" THEN
9 RETURN "Executive";
10 ELSE
11 RETURN "Invalid Type";
12 END IF;
13 EXCEPTION
14 WHEN OTHERS THEN
15 RETURN "Error Encountered";
16 END emptype;
17 /
Function created.
SQL>
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("emptype " || emptype("H"));
3 DBMS_OUTPUT.PUT_LINE("emptype " || emptype("S"));
4 DBMS_OUTPUT.PUT_LINE("emptype " || emptype("E"));
5 DBMS_OUTPUT.PUT_LINE("emptype " || emptype("A"));
6 END;
7 /
emptype Hourly
emptype Salaried
emptype Executive
emptype Invalid Type
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
Use mixed notation to avoid the second parameter, but keep the first and third
SQL>
SQL> create or replace procedure p_print(i_str1 VARCHAR2 :="hello",
2 i_str2 VARCHAR2 :="world",
3 i_end VARCHAR2 :="!" )
4 is
5 begin
6 DBMS_OUTPUT.put_line(i_str1||","||i_str2||i_end);
7 end;
8 /
Procedure created.
SQL>
SQL> declare
2 begin
3 p_print("Hi",i_end=>"..."); -- mixed
4 p_print(i_str1=>"Hi",i_end=>"..."); -- pure named
5 end;
6 /
Hi,world...
Hi,world...
PL/SQL procedure successfully completed.
SQL>
SQL>
Use ROWTYPE as the parameter
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
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> CREATE OR REPLACE PROCEDURE update_emp (emp_rec employee%ROWTYPE) IS
2 BEGIN
3 UPDATE employee
4 SET start_date = emp_rec.start_date + 100
5 WHERE id = emp_rec.id;
6 END update_emp;
7 /
Procedure created.
SQL>
SQL>
SQL> DECLARE
2 a employee%ROWTYPE;
3 BEGIN
4 a.id := "01";
5 update_emp(a);
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 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> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Using Named Notation
SQL>
SQL> create or replace procedure p_print(i_str1 VARCHAR2 :="hello",
2 i_str2 VARCHAR2 :="world",
3 i_end VARCHAR2 :="!" )
4 is
5 begin
6 DBMS_OUTPUT.put_line(i_str1||","||i_str2||i_end);
7 end;
8 /
Procedure created.
SQL>
SQL> declare
2 begin
3 p_print(i_str2=>"people"); -- just the second
4 p_print(i_end=>"..."); -- just the third
5 p_print(i_end=>"...",i_str2=>"people"); -- mix
6 end;
7 /
hello,people!
hello,world...
hello,people...
PL/SQL procedure successfully completed.
SQL>
SQL>