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

   <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>

Oracle PL/SQL Tutorial/Function Procedure Packages/Parameters

Содержание

Define function with NUMBER type parameter

Defining Formal Parameters

Function without parameter

Mixed Name and Position Notation Calls

Parameter Default Values

Parameter Modes

Passing parameters to procedures

Positional Notation

Positional vs. named parameter passing.

Specifying procedure or function parameters Positional notation

Table collection type parameter

There are three types of formal parameters in subprograms: IN, OUT, and IN OUT.

Use IF/ELSIF/ELSE to verify the input parameter

Use mixed notation to avoid the second parameter, but keep the first and third

Use ROWTYPE as the parameter

Using Named Notation

Навигация

Персональные инструменты

Пространства имён

Варианты

Просмотры

Ещё

Поиск

Разделы

Навигация

Инструменты