Oracle PL/SQL Tutorial/Function Procedure Packages/Parameters — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Версия 16:45, 26 мая 2010
Содержание
- 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
<source lang="sql">
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></source>
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
<source lang="sql">
parameter_name [MODE] parameter_type [:= value | DEFAULT value]</source>
Function without parameter
<source lang="sql">
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></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>
Parameter Default Values
<source lang="sql">
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.</source>
Parameter Modes
<source lang="sql">
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></source>
Passing parameters to procedures
<source lang="sql">
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></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>
Positional vs. named parameter passing.
<source lang="sql">
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></source>
Specifying procedure or function parameters Positional notation
<source lang="sql">
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 /</source>
Table collection type parameter
<source lang="sql">
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.</source>
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.
<source lang="sql">
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></source>
Use IF/ELSIF/ELSE to verify the input parameter
<source lang="sql">
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></source>
Use mixed notation to avoid the second parameter, but keep the first and third
<source lang="sql">
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></source>
Use ROWTYPE as the parameter
<source lang="sql">
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></source>
Using Named Notation
<source lang="sql">
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></source>