в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:00:56Z

1 версия

Новая страница

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

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL/Stored Procedure Function/Parameter IN - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия