例如
我要执行: select * from tb_user_group; 这一语句,怎么把它用存储过程封装我用的工具是:PLSQL Developer 8.0我直接创建的过程,不能执行,不知道为什么,好像是声明:packages,要求给一个详细的例子!谢谢
我要执行: select * from tb_user_group; 这一语句,怎么把它用存储过程封装我用的工具是:PLSQL Developer 8.0我直接创建的过程,不能执行,不知道为什么,好像是声明:packages,要求给一个详细的例子!谢谢
AS
num1 int := 0;
num2 int := 0;
BEGIN
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || desTable || ' WHERE cTaskID = ''' || taskID || '''' INTO num1;
IF num1 > 0 THEN
EXECUTE IMMEDIATE 'SELECT COUNT(DISTINCT cTaskID) FROM ' || desTable INTO num2;
IF num2 > 1 THEN
EXECUTE IMMEDIATE 'DELETE FROM ' || desTable || ' WHERE cTaskID = ''' || taskID || '''';
ELSE
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || desTable;
END IF;
END IF;
COMMIT;
END;
/
可以写个过程,用游标传出
create or replace procedure proc(pcur out sys_refcursor)
as
begin
open pcur for select * from tb_user_group;
end;不用存储过程,使用权限限制,视图,或同义词可能就能达到你的要求
然后使用execute immediatecreate or replace procedure p_test1
is
v_sql varchar2(100);
begin
v_sql:='create table t_table1 as select * from tb_user_group';
execute immediate v_sql;
end p_test1;
is
v_sql varchar2(100);
begin
v_sql:='create table t_table1 as select * from tb_user_group';
execute immediate v_sql;
end p_test1;
prompt Creating procedure PROC_T_7
prompt ===========================
prompt
CREATE OR REPLACE PROCEDURE PROC_T_7
(
P_RESULT OUT BINARY_INTEGER
)
IS
-- Local Variant
V_RESULT BINARY_INTEGER;-- Exception Variant
e_wrong EXCEPTION;
BEGIN
--Step 1:为确保T_7中为最新数据,首先调用PROC_T_2
PROC_T_2(v_result); IF v_result != 0
THEN p_result := v_result;
RETURN;
END IF;--Step 2:清空T_CHN_DISPART_DETAIL_REPORT表
DELETE T_CHN_DISPART_DETAIL_REPORT;--Step 3: 由T_VENDEE_BASE_INFO_REPORT和VENDEE_CHANNEL生成T_CHN_DISPART_DETAIL_REPORT数据
INSERT INTO T_CHN_DISPART_DETAIL_REPORT(
/*1 */ channel_detail_index, -- 序列号
/*2 */ agm_id, -- 协议标识
/*3 */ area_corp_id, -- 网络商标识
/*5 */ corp_id, -- 频道商标识
/*7 */ chn_id, -- 频道标识
/*9 */ channel_num, -- 频道数目
/*10*/ chn_month_net_income, -- 月分账额
/*11*/ pay_stm, -- 起始日
/*12*/ pay_etm) -- 终止日
SELECT
/*1 */ seq_t_7.nextval,
/*2 */ f.agm_id,
/*3 */ f.corp_id,
/*5 */ c.corp_id,
/*7 */ c.chn_id,
/*9 */ f.channel_num,
/*10*/ DECODE(f.channel_num, 0, 0,
f.sat_month_net_income / f.channel_num),
/*11*/ f.pay_stm,
/*12*/ f.pay_etm
FROM VENDEE_CHANNEL c,
T_VENDEE_CONTRACT_REPORT f
WHERE c.rec_stt = 1
AND c.agm_id = f.agm_id
AND c.area_corp_id = f.corp_id;
--Step 3: 正常运行结束,设置返回代码
COMMIT;
p_result := 0;EXCEPTION
WHEN e_wrong THEN
p_result := SQLCODE;
ROLLBACK;
WHEN OTHERS THEN
p_result := SQLCODE;
ROLLBACK;END PROC_T_7;
/
spool off