Several calling methods of Oracle stored procedure

Record ：284

scene ：Oracle Stored procedure call mode , Include PL/SQL Of SQL Call in window 、 stay PL/SQL Command window for 、 stay Procedures Select the name of the stored procedure in the menu list and right-click test 、 stay Package Bodies Select the name of the stored procedure in the menu list and right-click test 、 stay Jobs Select configured in the menu list Job Right click the stored procedure of and click Run 、Java Code call stored procedure .

edition ：Oracle Database 11g

Tools ：PL/SQL Developer

One 、 Case scenario

1. Based on the environment

This example environment , Use Packages and Packages bodies Manage stored procedures Procedure.

Packages and Packages bodies name ：PKG_ZBZ_PORTAL

Procedure name ：PRO_INSERT_INFO_ARGS

complete SQL：

-- Package definition 
CREATE OR REPLACE PACKAGE PKG_ZBZ_PORTAL IS
  PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE IN VARCHAR2 := NULL,
        FLAG OUT NUMBER,
        FAIL_INFO OUT VARCHAR2);
END PKG_ZBZ_PORTAL;
-- Inclusion body 
CREATE OR REPLACE PACKAGE BODY PKG_ZBZ_PORTAL IS
  PROCEDURE PRO_INSERT_INFO_ARGS(IN_DATE   IN VARCHAR2 := NULL,
        FLAG  OUT NUMBER,
        FAIL_INFO OUT VARCHAR2) IS
  BEGIN
    DECLARE
      V_DATE DATE := TRUNC(SYSDATE - 1);
    BEGIN
      FLAG      := 1;
      FAIL_INFO := '';
      IF (IN_DATE IS NOT NULL) THEN
        V_DATE := TO_DATE(IN_DATE, 'YYYYMMDD');
      END IF;
      INSERT INTO B_LOG_INFO
        SELECT SEQ_DEMO_ID.NEXTVAL,V_DATE,
               SYSDATE,'1',' Successful implementation ',50,'DUAL'
          FROM DUAL;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        FLAG      := 0;
        FAIL_INFO := ' This time : ' || IN_DATE || '  Output results : FLAG = ' || FLAG ||
                     ' Abnormal information :  ' || SQLERRM;
        ROLLBACK;
    END;
  END PRO_INSERT_INFO_ARGS;
END PKG_ZBZ_PORTAL;

Note the following tests , Stored procedures or packages must be compiled , That is to say .

 2.SQL Call the stored procedure in the window

stay PL/SQL Developer Tools , Menu path in turn ： file -> newly build ->SQL window , You can enter SQL Window .

scene ： After the stored procedure is submitted , Directly in SQL Window execution , Call stored procedures to verify and test stored procedures .

DECLARE
FLAG NUMBER;
FAIL_INFO VARCHAR2(512);
BEGIN
  PKG_ZBZ_PORTAL.PRO_INSERT_INFO_ARGS( '20220710',
        FLAG,FAIL_INFO);
  dbms_output.put_line('FLAG=' || FLAG); 
  dbms_output.put_line('FAIL_INFO=' || FAIL_INFO); 
END;

3. Call the stored procedure in the command window

stay PL/SQL Developer Tools , Menu path in turn ： file -> newly build -> Command window , That is, you can enter the command window .

scene ： After the stored procedure is submitted , Execute directly in the command window , Call stored procedures to verify and test stored procedures .

var FLAG NUMBER;
var FAIL_INFO VARCHAR2(512);
exec PKG_ZBZ_PORTAL.PRO_INSERT_INFO_ARGS( '20220710',:FLAG,:FAIL_INFO);

4.Procedures In the menu list Calling stored procedure

stay PL/SQL Developer Tools , In the left object navigation window , Menu path in turn ：Users-> User name ->Objects->Procedures, Select the stored procedure to be tested , Right click to select , Click test , Enter the test window , Fill in parameters according to requirements （ If there is any input ）. Be careful , The premise of the test menu is that the stored procedure must be compiled and submitted first .

scene ： After the stored procedure is submitted , Directly in Procedures Select the stored procedure in the menu list , Call stored procedures to verify and test stored procedures .

 5.Package or Package Bodies Call the stored procedure in the menu list

stay PL/SQL Developer Tools , In the left object navigation window , Menu path in turn ：Users-> User name ->Objects->Package or Package Bodies, Select the stored procedure to be tested , Right click to select , Click test , Enter the test window , Fill in parameters according to requirements （ If there is any input ）. Be careful , The premise of the test menu is that the stored procedure must be compiled and submitted first .

scene ： After the stored procedure is submitted , Directly in Package or Package Bodies Select the stored procedure in the menu list , Call stored procedures to verify and test stored procedures .

5.1 Right click the package name , Menu selection ： Check the description and body . That is, enter the stored procedure in the package .

 5.2 Right click the stored procedure name , Choose a test , Enter the test window .

 6.Jobs Menu list Calling stored procedure

stay PL/SQL Developer Tools , In the left object navigation window , Menu path in turn ：Users-> User name ->Objects->Jobs, Select the job Mission , Right click to select , Click on the run .

scene ： After the stored procedure is submitted , Directly in Jobs Select the stored procedure in the menu list , Call stored procedures to verify and test stored procedures .

7.Java Calling stored procedures in code

This example refers to ：https://blog.csdn.net/zhangbeizhen18/article/details/100127703

scene :

Use Java Code calls oracle Stored procedure , This example USES JdbcTemplate Template class operation .

above , thank .

2022 year 7 month 12 Japan