发信人: ljfling(plays)
整理人: tidycc(2001-02-11 22:11:37), 站内信件
|
【 在 cbd_xxj 的大作中提到：】
:请问如何在oracle编写一个存储过程用select语句查询
:一个结果集返回到调用它的程序中.
:多谢!
:......
create or replace package pkg_emp
as
type emptyp is record(
empno number(4),
ename varchar2(10),
sal number(7,2),
deptno number(2));
type v_emp_dept is ref cursor return emptyp; ---據說這個類型可以讓前端(pb)接收得到，但我沒試過，你試試吧.
type v_emp_job is table of emptyp index by binary_integer;
end;
CREATE OR REPLACE PROCEDURE EM_PERLIST
( fact IN VARCHAR,
day IN VARCHAR,
day_type IN VARCHAR,
brn_beg IN VARCHAR,
brn_end IN VARCHAR,
PER_CUR IN OUT EM_PERPKG.PerCurTyp)
AS
v_CursorID INTEGER;
v_Select VARCHAR2(1000);
v_Processed INTEGER;
v_SessionID VARCHAR2(12) := dbms_session.unique_session_id;
type_temp CHAR(20);
v_pnl_no EM_PERWK.PNL_NO%TYPE;
BEGIN
type_temp := 'WK_TYPE' || LTRIM(SUBSTR(day,7,2),'0');
v_CursorID := dbms_sql.open_cursor;
v_Select := 'SELECT EM_PERWK.PNL_NO FROM EM_PERWK,EM_PNL WHERE EM_PERWK.FACT_NO = EM_PNL.FACT_NO AND EM_PERWK.PNL_NO = EM_PNL.PNL_NO AND ' ||
'BRANCH_NO >= :a AND BRANCH_NO <= :b AND ' ||
'EM_PERWK.FACT_NO = :x AND CARD_YM = :y AND ' || type_temp || ' = :z';
dbms_sql.parse(v_CursorID,v_Select,dbms_sql.v7);
dbms_sql.bind_variable(v_CursorID,':a',brn_beg);
dbms_sql.bind_variable(v_CursorID,':b',brn_end);
dbms_sql.bind_variable(v_CursorID,':x',fact);
dbms_sql.bind_variable(v_CursorID,':y',SUBSTR(day,1,6));
dbms_sql.bind_variable(v_CursorID,':z',day_type);
dbms_sql.define_column(v_CursorID,1,v_pnl_no,8);
v_Processed := dbms_sql.execute(v_CursorID);
LOOP
IF dbms_sql.fetch_rows(v_CursorID) = 0 THEN
EXIT;
END IF;
dbms_sql.column_value(v_CursorID,1,v_pnl_no);
INSERT INTO EM_PERTMP VALUES (v_SessionID,day,day_type,fact,v_pnl_no);
END LOOP;
dbms_sql.close_cursor(v_CursorID);
OPEN PER_CUR FOR
SELECT CARD_D,TYPE,EM_PNL.FACT_NO,FACT_NM,EM_PNL.PNL_NO,PNL_NM,EM_PNL.BRANCH_NO,BRANCH_NM
FROM EM_PNL,EM_FACT,EM_BRANCH,EM_PERTMP
WHERE EM_PERTMP.SESSION_ID = v_SessionID AND
EM_PERTMP.FACT_NO = EM_PNL.FACT_NO AND
EM_PERTMP.PNL_NO = EM_PNL.PNL_NO AND
EM_PNL.FACT_NO = EM_FACT.FACT_NO AND
EM_PNL.BRANCH_NO = EM_BRANCH.BRANCH_NO;
DELETE EM_PERTMP WHERE SESSION_ID = v_SessionID;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(v_CursorID);
RAISE;
END EM_PERLIST;
----
.-""""-. .-""""-.
/ \ / \
/_ _\ /_ _\
// \ / \\ // \ / \\
|\__\ /__/| |\__\ /__/|
\ || / \ || /
\ / \ /
\ __ / \ __ /
'.__.' '.__.'
| | 克隆时代 | |
| | | |
|
|