here is a simple example of a package specification:CREATE OR REPLACE PACKAGE my_pkg AS PROCEDURE my_proc(arg1 IN VARCHAR2); FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2;END my_pkg; and its matching package body:CREATE OR REPLACE PACKAGE BODY my_pkg AS FUNCTION my_private_func(arg1 IN NUMBER) RETURN VARCHAR2 IS return_val VARCHAR2(20); BEGIN SELECT col1 INTO return_val FROM tab2 WHERE col2 = arg1; RETURN return_val; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'NOT FOUND'; END my_private_func; PROCEDURE my_proc(arg1 IN VARCHAR2) IS tmp LONG; v_name VARCHAR2 (4000); a number; c1 IS SELECT text, view_name FROM all_views WHERE view_name LIKE 'MTL%';
CURSOR c1;
BEGIN OPEN c1; LOOP FETCH c1 INTO tmp, v_name; EXIT WHEN c1%NOTFOUND; IF INSTR (tmp, 'MTL_CUSTOMER_ITEMS') > 0 and instr(tmp, 'CUSTOMER_ITEM_NUMBER') > 0 THEN DBMS_OUTPUT.put_line (v_name); END IF; END LOOP;
CLOSE c1; COMMIT; END my_proc; FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN my_private_func(arg1); END my_func;END my_pkg; 可以接受日期参数。
在SQL PLUS里执行的时侯怎么写语名来调用PACKAGE呢
不用那么复杂: create or replace package pg_HMBSTCUR as TYPE CurTyp is ref cursor; inval_cur_num EXCEPTION; pragma exception_init(inval_cur_num,-1023); end pg_HMBSTCUR ;
楼上的只是定义了一个Package的头部说明,而没有定义Package body ,还是一楼的详细些啊!
CREATE OR REPLACE PACKAGE BODY S_CRIMS2.P_TEST AS PROCEDURE MAIN(JIKOTIME IN DATE) is CURSOR MYCOR IS SELECT * FROM TEST WHERE DATETIME=JIKOTIME; MYTEMP MYCOR%TYPE; BEGIN OPEN MYCOR; LOOP FETCH MYCOR INTO MYTEMP; EXIT WHEN MYCOR%NOTFOUND; --DBMS_OUTPUT.put_line (MYCOR.DATETIME); --MYNUM:=100; END LOOP; CLOSE MYCOR; COMMIT; END MAIN; END; MYTEMP MYCOR%TYPE;出类型不配的错误,大家帮我看看,语法哪里错了
and its matching package body:CREATE OR REPLACE PACKAGE BODY my_pkg AS FUNCTION my_private_func(arg1 IN NUMBER) RETURN VARCHAR2 IS return_val VARCHAR2(20); BEGIN SELECT col1 INTO return_val FROM tab2 WHERE col2 = arg1; RETURN return_val; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'NOT FOUND'; END my_private_func; PROCEDURE my_proc(arg1 IN VARCHAR2) IS
tmp LONG;
v_name VARCHAR2 (4000);
a number; c1 IS
SELECT text, view_name
FROM all_views
WHERE view_name LIKE 'MTL%';
CURSOR c1;
BEGIN
OPEN c1;
LOOP
FETCH c1
INTO tmp, v_name;
EXIT WHEN c1%NOTFOUND;
IF INSTR (tmp, 'MTL_CUSTOMER_ITEMS') > 0 and instr(tmp, 'CUSTOMER_ITEM_NUMBER') > 0
THEN
DBMS_OUTPUT.put_line (v_name);
END IF;
END LOOP;
CLOSE c1; COMMIT;
END my_proc; FUNCTION my_func(arg1 IN NUMBER) RETURN VARCHAR2 IS BEGIN RETURN my_private_func(arg1); END my_func;END my_pkg;
可以接受日期参数。
create or replace package pg_HMBSTCUR
as
TYPE CurTyp is ref cursor;
inval_cur_num EXCEPTION;
pragma exception_init(inval_cur_num,-1023);
end pg_HMBSTCUR
;
AS
PROCEDURE MAIN(JIKOTIME IN DATE) is
CURSOR MYCOR IS SELECT * FROM TEST WHERE DATETIME=JIKOTIME;
MYTEMP MYCOR%TYPE;
BEGIN
OPEN MYCOR;
LOOP
FETCH MYCOR INTO MYTEMP;
EXIT WHEN MYCOR%NOTFOUND;
--DBMS_OUTPUT.put_line (MYCOR.DATETIME);
--MYNUM:=100;
END LOOP;
CLOSE MYCOR;
COMMIT;
END MAIN;
END;
MYTEMP MYCOR%TYPE;出类型不配的错误,大家帮我看看,语法哪里错了