Oracle PL/SQL/Stored Procedure Function/Parameter IN
Содержание
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>