в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T10:01:01Z

1 версия

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

==CALL statement.==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE CallProc2(p1 IN OUT VARCHAR2) AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE("CallProc2 called with " || p1);
4 p1 := p1 || " returned!";
5 END CallProc2;
6 /
Procedure created.
SQL>
SQL> set serveroutput on
SQL>
SQL> CALL CallProc2(:v_Output);
CallProc2 called with Hello!
Call completed.
SQL> PRINT v_Output
V_OUTPUT
--------------------------------------------------------------------------------
Hello! returned!
SQL>
SQL>
SQL>

</source>

==demonstrates using DBMS_SQL to execute CALL statement.==



<source lang="sql">

SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE DynamicCall AS
2 PROCEDURE DoInsert(p_NumCol IN MyTable.num_col%TYPE,
3 p_CharCol IN MyTable.char_col%TYPE);
4
5 PROCEDURE Go;
6 END DynamicCall;
7 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY DynamicCall AS
2 PROCEDURE DoInsert(p_NumCol IN MyTable.num_col%TYPE,
3 p_CharCol IN MyTable.char_col%TYPE) IS
4 BEGIN
5 INSERT INTO MyTable (num_col, char_col)
6 VALUES (p_NumCol, p_CharCol);
7 END DoInsert;
8
9 PROCEDURE Go IS
10 v_CallStmt VARCHAR2(100);
11 v_CursorID INTEGER;
12 v_NumCol MyTable.num_col%TYPE;
13 v_CharCol MyTable.char_col%TYPE;
14 v_Dummy INTEGER;
15 BEGIN
16 -- Open the cursor
17 v_CursorID := DBMS_SQL.OPEN_CURSOR;
18
19 v_CallStmt := "CALL DynamicCall.DoInsert(:num_col, :char_col)";
20 DBMS_SQL.PARSE(v_CursorID, v_CallStmt, DBMS_SQL.NATIVE);
21
22 v_NumCol := 123;
23 v_CharCol := "I like CALL statements!";
24 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":num_col", v_NumCol);
25 DBMS_SQL.BIND_VARIABLE(v_CursorID, ":char_col", v_CharCol);
26 v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
27 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
28 EXCEPTION
29 WHEN OTHERS THEN
30 -- Close the cursor, then raise the error again.
31 DBMS_SQL.CLOSE_CURSOR(v_CursorID);
32 RAISE;
33
34 END Go;
35 END DynamicCall;
36 /
Package body created.
SQL> show errors
No errors.
SQL>
SQL>
SQL> drop table mytable;
Table dropped.
SQL>

</source>

==EXECUTE IMMEDIATE dynamic statement and return value==



<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> DECLARE
2 myResult VARCHAR2(50);
3 BEGIN
4 EXECUTE IMMEDIATE
5 "CALL CallFunc(""Hello from PL/SQL"") INTO :myResult"
6 USING OUT myResult;
7 END;
8 /
CallFunc called with Hello from PL/SQL
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

==illegal procedure call==



<source lang="sql">

SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE("CallProc1 called with " || p1);
4 END CallProc1;
5 /
Procedure created.
SQL> BEGIN
2 CALL CallProc1();
3 END;
4 /
CALL CallProc1();
*
ERROR at line 2:
ORA-06550: line 2, column 8:
PLS-00103: Encountered the symbol "CALLPROC1" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "CALLPROC1" to continue.

</source>

==This script demonstrates the use of the CALL statement.==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE myProcedure(p1 IN VARCHAR2 := NULL) AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE("myProcedure called with " || p1);
4 END myProcedure;
5 /
Procedure created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE myProcedure2(p1 IN OUT VARCHAR2) AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE("myProcedure2 called with " || p1);
4 p1 := p1 || " returned!";
5 END myProcedure2;
6 /
Procedure created.
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>
SQL> set serveroutput on
SQL>
SQL> -- Some valid calls direct from SQL.
SQL> CALL myProcedure("Hello!");
myProcedure called with Hello!
Call completed.
SQL>
SQL> Call completed.
2
SQL> CALL myProcedure();
myProcedure called with
Call completed.
SQL>
SQL> Call completed.
2
SQL> VARIABLE v_Output VARCHAR2(50);
SQL>
SQL>
SQL> Call completed.
2
SQL> PRINT v_Output
V_OUTPUT
--------------------------------------------------------------------------------

SQL>
SQL> CALL myProcedure2(:v_Output);
myProcedure2 called with
Call completed.
SQL>
SQL> Call completed.
2
SQL> PRINT v_Output
V_OUTPUT
--------------------------------------------------------------------------------
returned!
SQL>
SQL>

</source>

==Using call statement in a dynamic statement is legal==



<source lang="sql">

SQL>
SQL> CREATE OR REPLACE PROCEDURE CallProc1(p1 IN VARCHAR2 := NULL) AS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE("CallProc1 called with " || p1);
4 END CallProc1;
5 /
Procedure created.
SQL>
SQL> DECLARE
2 myResult VARCHAR2(50);
3 BEGIN
4 EXECUTE IMMEDIATE "CALL CallProc1(""Hello from PL/SQL"")";
5 END;
6 /
CallProc1 called with Hello from PL/SQL
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия