Oracle PL/SQL/Stored Procedure Function/Call — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:01, 26 мая 2010
Содержание
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>