Calling the required procedure from a package using an input parameter-CodePudding

tell me how to organize the call of the required procedure from the package by the input parameter. An example of my code, where the meaning is clear, I will attach. I really need help in the implementation of this assignment. At the input, we receive a line and, depending on the text of this line, we use the necessary procedure inside the package, deleting, clearing or copying the table. Thanks in advance for your help!

    create  table test1
(
    test1   varchar2(10),
    valtest number(5)
);
insert into test1 values('hello',0110);
insert into test1 values('bye',1010);



CREATE OR REPLACE PACKAGE pck as
TYPE refc IS REF CURSOR;
PROCEDURE p1
(   intake          varchar2, 
    rc    IN OUT refc
);
PROCEDURE p2 (
    intake          varchar2, 
    rc    IN  OUT refc
    
);
PROCEDURE p3 (
  intake          varchar2, 
    rc    IN  OUT refc
);
 
end;
create or replace package body pck as
PROCEDURE pr1 (
    intake          varchar2(10),
    rc    IN OUT mrc
)
IS
BEGIN
    OPEN rc FOR SELECT * FROM test1;
    IF rc IN ('delt')
    THEN DROP TABLE test1;
    dbms_output.put_line ('table droped!');
    END IF;
END;


PROCEDURE pr2 (
    intake          varchar2(10),
    rc    IN  OUT mrc
)
BEGIN
   OPEN rc FOR SELECT * FROM test1;
   IF rc IN ('trunct')
    THEN TRUNCATE TABLE test1;
    dbms_output.put_line ('table truncated!');
    END IF;
END;


PROCEDURE pr3 (
    intake          varchar2(10),
    rc    IN  OUT mrc
)
IS
BEGIN
  OPEN rc FOR SELECT * FROM test1;
   IF rc IN ('copy')
    THEN CREATE TABLE test2 AS SELECT * FROM test1;
    dbms_output.put_line ('table copyed!');
    END IF;
END;


end;

CodePudding user response：

To me, your approach looks a little bit too complicated. Why not a single procedure? Why ref cursor? You'll need dynamic SQL (because you can't execute DDL otherwise). Note that this will fail if hardcoded table test1 doesn't exist or - while copying - test2 already exists. You should handle errors and, most probably, avoid hardcoded values.

SQL> CREATE OR REPLACE PACKAGE pck
  2  AS
  3     PROCEDURE p1 (intake VARCHAR2);
  4  END;
  5  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY pck
  2  AS
  3     PROCEDURE p1 (intake VARCHAR2)
  4     IS
  5     BEGIN
  6        IF intake = 'delt'
  7        THEN
  8           EXECUTE IMMEDIATE 'drop table test1';
  9
 10           DBMS_OUTPUT.put_line ('Table dropped');
 11        ELSIF intake = 'trunct'
 12        THEN
 13           EXECUTE IMMEDIATE 'truncate table test1';
 14
 15           DBMS_OUTPUT.put_line ('Table truncated');
 16        ELSIF intake = 'copy'
 17        THEN
 18           EXECUTE IMMEDIATE 'create table test2 as select * from test1';
 19
 20           DBMS_OUTPUT.put_line ('Table copied');
 21        END IF;
 22     END;
 23  END;
 24  /

Package body created.

Testing:

SQL> SELECT * FROM test1;

TEST1         VALTEST
---------- ----------
hello             110
bye              1010

SQL> BEGIN
  2     pck.p1 ('copy');
  3     pck.p1 ('trunct');
  4  END;
  5  /
Table copied
Table truncated

PL/SQL procedure successfully completed.

Result:

SQL> SELECT * FROM test1;

no rows selected

SQL> SELECT * FROM test2;

TEST1         VALTEST
---------- ----------
hello             110
bye              1010

SQL>