告訴我如何通過輸入引數從包中組織所需程序的呼叫。我的代碼的一個例子，意思很清楚，我會附上。我真的需要幫助來完成這項任務。在輸入處，我們收到一行，根據該行的文本，我們在包內使用必要的程式，洗掉、清除或復制表。在此先感謝您的幫助！
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;
uj5u.com熱心網友回復：
對我來說，你的方法看起來有點太復雜了。為什么不是一個單一的程式？為什么要參考游標？您將需要動態 SQL（因為否則您將無法執行 DDL）。請注意，如果硬編碼表test1不存在或 -復制時-test2已經存在，這將失敗。您應該處理錯誤，并且最有可能避免硬編碼值。
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.
測驗：
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.
結果：
SQL> SELECT * FROM test1;
no rows selected
SQL> SELECT * FROM test2;
TEST1 VALTEST
---------- ----------
hello 110
bye 1010
SQL>
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/397727.html
標籤：sql 甲骨文 包裹 oracle-sqldeveloper
上一篇：如何洗掉大表的嵌套回圈連接
下一篇：使用匯總通過分組計算總計