Oracle PL/SQL/Stored Procedure Function/Parameter IN

Материал из SQL эксперт
Перейти к: навигация, поиск

Column type parameter

   <source lang="sql">
  

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> CREATE OR REPLACE PROCEDURE AddNewStudent (

 2    p_FirstName  lecturer.first_name%TYPE,
 3    p_LastName   lecturer.last_name%TYPE,
 4    p_Major      lecturer.major%TYPE) AS
 5  BEGIN
 6    INSERT INTO lecturer (ID, first_name, last_name,
 7                          major, current_credits)
 8      VALUES (10018, p_FirstName, p_LastName,
 9              p_Major, 0);
10  END AddNewStudent;
11  /

Procedure created. SQL> SQL> SQL> DECLARE

 2    v_NewFirstName  lecturer.first_name%TYPE := "Cynthia";
 3    v_NewLastName   lecturer.last_name%TYPE := "Camino";
 4    v_NewMajor      lecturer.major%TYPE := "History";
 5  BEGIN
 6    AddNewStudent(v_NewFirstName, v_NewLastName, v_NewMajor);
 7  END;
 8  /

PL/SQL procedure successfully completed. SQL> SQL> select * from lecturer;

       ID FIRST_NAME           LAST_NAME

-------------------- --------------------

MAJOR CURRENT_CREDITS


---------------
    10001 Scott                Lawson

Computer Science 11

    10002 Mar                  Wells

History 4

    10003 Jone                 Bliss

Computer Science 8

       ID FIRST_NAME           LAST_NAME

-------------------- --------------------

MAJOR CURRENT_CREDITS


---------------
    10004 Man                  Kyte

Economics 8

    10005 Pat                  Poll

History 4

    10006 Tim                  Viper

History 4

       ID FIRST_NAME           LAST_NAME

-------------------- --------------------

MAJOR CURRENT_CREDITS


---------------
    10007 Barbara              Blues

Economics 7

    10008 David                Large

Music 4

    10009 Chris                Elegant

Nutrition 8

       ID FIRST_NAME           LAST_NAME

-------------------- --------------------

MAJOR CURRENT_CREDITS


---------------
    10010 Rose                 Bond

Music 7

    10011 Rita                 Johnson

Nutrition 8

    10012 Sharon               Clear

Computer Science 3

       ID FIRST_NAME           LAST_NAME

-------------------- --------------------

MAJOR CURRENT_CREDITS


---------------
    10018 Cynthia              Camino

History 0

13 rows selected. SQL> SQL> drop table lecturer; Table dropped. SQL> SQL>


 </source>
   
  


Define in parameter

   <source lang="sql">
 

SQL> -- define in parameter SQL> SQL> set serverout on; SQL> SQL> create or replace

 2  procedure out_parms( p_parm in number ) as
 3  begin
 4   null;
 5   dbms_output.put_line(p_parm);
 6  end out_parms;
 7  /

Procedure created. SQL> SQL> exec out_parms( 123 ); 123 PL/SQL procedure successfully completed. SQL>


 </source>
   
  


Demonstrate the behavior of IN, OUT, and IN OUT parameter modes.

   <source lang="sql">
 

SQL> SQL> set serveroutput on format wrapped SQL> SQL> CREATE OR REPLACE PROCEDURE ModeIn (p_In IN NUMBER) AS

 2     v_LocalVariable NUMBER := 0;
 3   BEGIN
 4     DBMS_OUTPUT.PUT("Inside ModeIn: ");
 5     IF (p_In IS NULL) THEN
 6       DBMS_OUTPUT.PUT_LINE("p_In is NULL");
 7     ELSE
 8       DBMS_OUTPUT.PUT_LINE("p_In = " || p_In);
 9     END IF;
10
11     v_LocalVariable := p_In;
12
13     IF (p_In IS NULL) THEN
14        DBMS_OUTPUT.PUT_LINE("p_In is NULL");
15     ELSE
16       DBMS_OUTPUT.PUT_LINE("p_In = " || p_In);
17     END IF;
18   END ModeIn;
19   /

Procedure created. SQL> SQL> SQL> show errors No errors. SQL> SQL> SQL> SQL> DECLARE

 2     v_In NUMBER := 1;
 3   BEGIN
 4     DBMS_OUTPUT.PUT_LINE("Before calling ModeIn, v_In = " || v_In);
 5     ModeIn(v_In);
 6     DBMS_OUTPUT.PUT_LINE("After calling ModeIn, v_In = " || v_In);
 7   END;
 8   /

Before calling ModeIn, v_In = 1 Inside ModeIn: p_In = 1 p_In = 1 After calling ModeIn, v_In = 1 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL>


 </source>
   
  


This procedure will not compile, since it attempts to modify an IN parameter.

   <source lang="sql">
  

SQL> CREATE OR REPLACE PROCEDURE IllegalModeIn (p_In IN NUMBER) AS

 2   BEGIN
 3     p_In := 7;
 4   END IllegalModeIn;
 5   /

Warning: Procedure created with compilation errors. SQL> SQL> SQL> show errors Errors for PROCEDURE ILLEGALMODEIN: LINE/COL ERROR


-----------------------------------------------------------------

3/4 PL/SQL: Statement ignored 3/4 PLS-00363: expression "P_IN" cannot be used as an assignment

        target

SQL> SQL>


 </source>
   
  


Use "IN" parameters

   <source lang="sql">
 

SQL> SQL> -- 1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function. SQL> -- 2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function. SQL> -- 3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function. SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE),
 3    First_Name         VARCHAR2(20 BYTE),
 4    Last_Name          VARCHAR2(20 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(20 BYTE),
 9    Description        VARCHAR2(80 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"), 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("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","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("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2334.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"), 2334.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"), 2334.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"), 2334.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 2334.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 2334.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2334.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 2334.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 2334.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 2334.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE Procedure UpdateMyEmployee

 2         ( name_in IN varchar2 )
 3      IS
 4          mySalary number;
 5
 6          cursor c1 is
 7          select salary
 8            from employee
 9            where first_name = name_in;
10
11      BEGIN
12
13          open c1;
14          fetch c1 into mySalary;
15
16          if c1%notfound then
17               mySalary := 9999;
18          end if;
19
20          insert into employee(first_name, salary) values ( name_in, mySalary );
21
22          commit;
23
24          close c1;
25
26      EXCEPTION
27      WHEN OTHERS THEN
28            raise_application_error(-20001,"An error was encountered - "||SQLCODE||" -ERROR- "||SQLERRM);
29      END UpdateMyEmployee;
30      /

Procedure created. SQL> SQL> exec UpdateMyEmployee("Linda"); PL/SQL procedure successfully completed. SQL> 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 2334.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 2334.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2334.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 2334.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 2334.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 2334.78 Vancouver Tester

    Linda                                                            2334.78

9 rows selected. SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL>


 </source>