в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:01Z

1 версия

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

==Behavior of OUT variables and raised exceptions==



<source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE RaiseError (p_Raise IN BOOLEAN,pA OUT NUMBER) AS
2 BEGIN
3 pA := 7;
4
5 IF p_Raise THEN
6 RAISE DUP_VAL_ON_INDEX;
7 ELSE
8 RETURN;
9 END IF;
10 END RaiseError;
11 /
Procedure created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> set serveroutput on
SQL>
SQL>
SQL> DECLARE
2 v_Num NUMBER := 1;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Value before first call: " || v_Num);
5 RaiseError(FALSE, v_Num);
6 DBMS_OUTPUT.PUT_LINE("Value after successful call: " || v_Num);
7 DBMS_OUTPUT.PUT_LINE("");
8
9 v_Num := 2;
10 DBMS_OUTPUT.PUT_LINE("Value before second call: " || v_Num);
11 RaiseError(TRUE, v_Num);
12 EXCEPTION
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " || v_Num);
15 END;
16 /
Value before first call: 1
Value after successful call: 7
Value before second call: 2
Value after unsuccessful call: 2
PL/SQL procedure successfully completed.
SQL>

</source>

==behavior of unhandled exceptions and OUT variables.==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
2 p_Raise IN BOOLEAN,
3 p_ParameterA OUT NUMBER) AS
4 BEGIN
5 p_ParameterA := 7;
6
7 IF p_Raise THEN
8 RAISE DUP_VAL_ON_INDEX;
9 ELSE
10 RETURN;
11 END IF;
12 END RaiseError;
13 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> DECLARE
2 v_TempVar NUMBER := 1;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Initial value: " || v_TempVar);
5 RaiseError(FALSE, v_TempVar);
6 DBMS_OUTPUT.PUT_LINE("Value after successful call: " ||
7 v_TempVar);
8
9 v_TempVar := 2;
10 DBMS_OUTPUT.PUT_LINE("Value before 2nd call: " || v_TempVar);
11 RaiseError(TRUE, v_TempVar);
12 EXCEPTION
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " ||
15 v_TempVar);
16 END;
17 /
Initial value: 1
Value after successful call: 7
Value before 2nd call: 2
Value after unsuccessful call: 2
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

==Define "out" parameters==



<source lang="sql">

SQL>
SQL> create or replace
2 procedure swap( p_parm1 in out number, p_parm2 in out number ) as
3 --
4 l_temp number;
5 begin
6 l_temp := p_parm1;
7 p_parm1 := p_parm2;
8 p_parm2 := l_temp;
9 end swap;
10 /
SQL>
SQL>
SQL>

</source>

==Out parameter is assignable==



<source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE myTable3 (
2 a INTEGER,
3 b INTEGER
4 );
Table created.
SQL>
SQL> CREATE or replace PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)
2 AS
3 BEGIN
4 b := 4;
5 INSERT INTO myTable3 VALUES(a, b);
6 END;
7 /
Procedure created.
SQL>
SQL> DECLARE
2 v NUMBER;
3 BEGIN
4 addtuple3(10, v);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myTable3;
Table dropped.

</source>

==Out with NOCOPY modifier.==



<source lang="sql">

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
2 p_Raise IN BOOLEAN,
3 p_ParameterA OUT NOCOPY NUMBER) AS
4 BEGIN
5 p_ParameterA := 7;
6 IF p_Raise THEN
7 RAISE DUP_VAL_ON_INDEX;
8 ELSE
9 RETURN;
10 END IF;
11 END RaiseError;
12 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 v_TempVar NUMBER := 1;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Initial value: " || v_TempVar);
5 RaiseError(FALSE, v_TempVar);
6 DBMS_OUTPUT.PUT_LINE("Value after successful call: " ||
7 v_TempVar);
8
9 v_TempVar := 2;
10 DBMS_OUTPUT.PUT_LINE("Value before 2nd call: " || v_TempVar);
11 RaiseError(TRUE, v_TempVar);
12 EXCEPTION
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " ||
15 v_TempVar);
16 END;
17 /
Initial value: 1
Value after successful call: 7
Value before 2nd call: 2
Value after unsuccessful call: 7
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

==Parameter Modes==



<source lang="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_OutParameter := 7; -- Legal
11
12 v_LocalVariable := p_InOutParameter; -- Legal
13
14 p_InOutParameter := 7; -- Legal
15 END ModeTest;
16 /
Procedure created.
SQL>
SQL>

</source>

==This procedure takes a single OUT. Out parameter is assignable==



<source lang="sql">

SQL> CREATE OR REPLACE PROCEDURE ModeOut (p_Out OUT NUMBER) AS
2 v_LocalVariable NUMBER := 0;
3 BEGIN
4 IF (p_Out IS NULL) THEN
5 DBMS_OUTPUT.PUT_LINE("p_Out is NULL");
6 ELSE
7 DBMS_OUTPUT.PUT_LINE("p_Out = " || p_Out);
8 END IF;
9
10 p_Out := 7;
11
12 v_LocalVariable := p_Out;
13
14 DBMS_OUTPUT.PUT("At end of ModeOut: ");
15 IF (p_Out IS NULL) THEN
16 DBMS_OUTPUT.PUT_LINE("p_Out is NULL");
17 ELSE
18 DBMS_OUTPUT.PUT_LINE("p_Out = " || p_Out);
19 END IF;
20 END ModeOut;
21 /
Procedure created.
SQL>
SQL>
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> DECLARE
2 v_Out NUMBER := 1;
3 BEGIN
4
5 DBMS_OUTPUT.PUT_LINE("Before calling ModeOut, v_Out = " || v_Out);
6 ModeOut(v_Out);
7 DBMS_OUTPUT.PUT_LINE("After calling ModeOut, v_Out = " || v_Out);
8 END;
9 /
Before calling ModeOut, v_Out = 1
p_Out is NULL
At end of ModeOut: p_Out = 7
After calling ModeOut, v_Out = 7
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

</source>

==Unhandled exceptions and OUT variables==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE RaiseError (
2 p_Raise IN BOOLEAN,
3 p_ParameterA OUT NUMBER) AS
4 BEGIN
5 p_ParameterA := 7;
6
7 IF p_Raise THEN
8 RAISE DUP_VAL_ON_INDEX;
9 ELSE
10 RETURN;
11 END IF;
12 END RaiseError;
13 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL> DECLARE
2 v_TempVar NUMBER := 1;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Initial value: " || v_TempVar);
5 RaiseError(FALSE, v_TempVar);
6 DBMS_OUTPUT.PUT_LINE("Value after successful call: " || v_TempVar);
7
8 v_TempVar := 2;
9 DBMS_OUTPUT.PUT_LINE("Value before 2nd call: " || v_TempVar);
10 RaiseError(TRUE, v_TempVar);
11 EXCEPTION
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE("Value after unsuccessful call: " || v_TempVar);
14 END;
15 /
Initial value: 1
Value after successful call: 7
Value before 2nd call: 2
Value after unsuccessful call: 2
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

==Use out parameter to get value out==



<source lang="sql">

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL>
SQL> create or replace procedure emp_lookup(
2 p_empno in number,
3 o_ename out emp.ename%type,
4 o_sal out emp.sal%type ) as
5 begin
6 select ename, sal
7 into o_ename, o_sal
8 from emp
9 where empno = p_empno;
10 exception
11 when NO_DATA_FOUND then
12 o_ename := "NULL";
13 o_sal := -1;
14 end emp_lookup;
15 /
Procedure created.
SQL>
SQL> set serverout on
SQL>
SQL> declare
2 l_ename emp.ename%type;
3 l_sal emp.sal%type;
4 begin
5 emp_lookup( 7782, l_ename, l_sal );
6 dbms_output.put_line( "Ename = " || l_ename );
7 dbms_output.put_line( "Sal = " || l_sal );
8 end;
9 /
Ename = CLARK
Sal = 2450
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.

</source>

==Using out parameter==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE FUNCTION CallFunc(p1 IN VARCHAR2)
2 RETURN VARCHAR2 AS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("CallFunc called with " || p1);
5 RETURN p1;
6 END CallFunc;
7 /
Function created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 myResult VARCHAR2(50);
3 BEGIN
4 EXECUTE IMMEDIATE "CALL CallFunc(""Hello from PL/SQL"") INTO :myResult"
5 USING OUT myResult;
6 END;
7 /
CallFunc called with Hello from PL/SQL
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

</source>

==Using Output Parameters==



<source lang="sql">

SQL>
SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat");
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor");
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE GetempName(i_empID IN INT,o_empName OUT VARCHAR)
2 AS
3 BEGIN
4 SELECT Name INTO o_empName FROM emp
5 WHERE empID = i_empID;
6 END;
7 /
SP2-0804: Procedure created with compilation warnings
SQL> SET SERVEROUT ON
SQL> DECLARE
2 empName VARCHAR(50);
3 BEGIN
4 GetempName(3, empName);
5 dbms_output.put_line(empName);
6 END;
7 /
Mary
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>

</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия