感觉应该是很简单的程序,我就是不知道应该写到什么地方。举个例子吧。 create or procedure p1 is begin exception end; / 我应该把我的sql语句写在哪里呀?begin前面还是exception前面呢?还有就是写完sql语句,直接写dbms_output.putline的话,当我执行exec p1也不能输出出来结果呀?
create or procedure p1 is cursor acct is 查询语句 begin open acct; fetch acct into a; while (acct%FOUND) loop dbms_output.put_line(这个函数里面应该填什么呀???); end loop; end; /
CREATE OR REPLACE PROCEDURE ZXL_SP_GQITV_20110624 (P_LOAD_DATE IN VARCHAR2) IS ---V_DATE VARCHAR2(10); --运行日期 V_SQL VARCHAR2(8000); --动态SQL ---VC_P_DATE date; --保存传参日期 ---VC_NEXT_DATE date; --传参日期的下一天 SUM_DATE_NULL exception; --日期报错信息 vnErr_code number; -- 错误代码 vcErr_text varchar2(2000);-- 错误信息 vnBatchId number; --V_LOAD_DATE VARCHAR2(8); --V_SALE_DATE DATE; BEGIN -- if (length(P_LOAD_DATE) = 8) then --VC_P_DATE := TO_DATE(SUBSTR(P_LOAD_DATE,1,8),'YYYYMMDD'); --VC_NEXT_DATE := VC_P_DATE+1; --else ---raise SUM_DATE_NULL; ----end if; select ZXL_SEQ_BATCH.nextval into vnBatchId from dual; --程序写日志开始 ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624','0', ''); commit; ----------------------------------------------------------------------- execute immediate 'alter session enable parallel dml';FOR RPTT IN (select CORP_ID,CORP_NAME,CORP_HEAD from report.dim_corp where corp_id <> '440' ORDER BY CORP_ID) LOOP ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624','0', ' 开始处理-'||rptt.corp_name); commit; IF ZXL_IS_TABLE_EXISTS('IT_SZX','ZXL_GQITV_20110624_tmp1') THEN EXECUTE IMMEDIATE ' DROP TABLE ZXL_GQITV_20110624_tmp1 purge'; END IF; v_sql := 'CREATE TABLE ZXL_GQITV_20110624_tmp1 PARALLEL 10 NOLOGGING AS select /*+ PARALLEL(t,10)*/ t.acc_nbr2,t.create_date,t.cust_id,t.serv_id,t.new_prod_id from summary_sjz_'||RPTT.CORP_HEAD||'.rpt_comm_cm_serv_201105 t where t.net_type in(''1'',''2'',''3'') and t.state in (''B0A'',''B0T'',''B0S'') and t.Speed_value in (12,20,100) '; execute immediate v_sql; commit; execute immediate 'create index ind_ZXL_GQITV_20110624_1 on ZXL_GQITV_20110624_tmp1(cust_id) parallel 10 nologging'; execute immediate 'create index ind_ZXL_GQITV_20110624_2 on ZXL_GQITV_20110624_tmp1(SERV_ID) parallel 10 nologging'; ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624','0', ' 完成处理-'||rptt.corp_name); commit; END LOOP; ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624','0', '高清itv客户运行结束'); commit; ----------------------------------------------------------------------- exception WHEN SUM_DATE_NULL THEN rollback; DBMS_OUTPUT.put_line('输入的月份格式有错!'); --异常要记录入表中备查 ZXL_sp_write_log(P_LOAD_DATE, 'ZXL_SP_GQITV_20110624','-1','输入的统计月份格式有错!'); commit; WHEN OTHERS THEN rollback; DBMS_OUTPUT.PUT_LINE('异常号:'||substr(to_char(sqlcode),1,200)||'; 异常信息:'||sqlerrm); vnErr_code := sqlcode; vcErr_text := sqlerrm; --异常要记录入表中备查 ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624',vnErr_code,vcErr_text,V_SQL); commit;END ZXL_SP_GQITV_20110624; 自己看着改,这是模板!
oracle可不像sqlserver那样,直接在过程中输入select * FROM T ,调用过程就能显示表的数据出来,你的需求再oracle比较麻烦。。需要通过其他办法实现。1、过程返回一个游标变量,接收select结果,然后在外面调用的时候一条条取出来打印2、不用过程,用函数,利用管道的功能 。 .......
DBMS_OUTPUT.PUT_LINE 只用来打印相关信息,看执行到什么地方了
create or procedure p1 is
begin
exception
end;
/
我应该把我的sql语句写在哪里呀?begin前面还是exception前面呢?还有就是写完sql语句,直接写dbms_output.putline的话,当我执行exec p1也不能输出出来结果呀?
cursor acct is 查询语句
begin
open acct;
fetch acct into a;
while (acct%FOUND) loop
dbms_output.put_line(这个函数里面应该填什么呀???);
end loop;
end;
/
---V_DATE VARCHAR2(10); --运行日期
V_SQL VARCHAR2(8000); --动态SQL
---VC_P_DATE date; --保存传参日期
---VC_NEXT_DATE date; --传参日期的下一天
SUM_DATE_NULL exception; --日期报错信息
vnErr_code number; -- 错误代码
vcErr_text varchar2(2000);-- 错误信息
vnBatchId number;
--V_LOAD_DATE VARCHAR2(8);
--V_SALE_DATE DATE;
BEGIN
-- if (length(P_LOAD_DATE) = 8) then
--VC_P_DATE := TO_DATE(SUBSTR(P_LOAD_DATE,1,8),'YYYYMMDD');
--VC_NEXT_DATE := VC_P_DATE+1;
--else
---raise SUM_DATE_NULL;
----end if; select ZXL_SEQ_BATCH.nextval into vnBatchId from dual; --程序写日志开始
ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624','0', '');
commit; -----------------------------------------------------------------------
execute immediate 'alter session enable parallel dml';FOR RPTT IN (select CORP_ID,CORP_NAME,CORP_HEAD from report.dim_corp where corp_id <> '440' ORDER BY CORP_ID) LOOP ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624','0', ' 开始处理-'||rptt.corp_name);
commit;
IF ZXL_IS_TABLE_EXISTS('IT_SZX','ZXL_GQITV_20110624_tmp1') THEN
EXECUTE IMMEDIATE ' DROP TABLE ZXL_GQITV_20110624_tmp1 purge';
END IF; v_sql := 'CREATE TABLE ZXL_GQITV_20110624_tmp1 PARALLEL 10 NOLOGGING AS
select /*+ PARALLEL(t,10)*/ t.acc_nbr2,t.create_date,t.cust_id,t.serv_id,t.new_prod_id
from summary_sjz_'||RPTT.CORP_HEAD||'.rpt_comm_cm_serv_201105 t
where t.net_type in(''1'',''2'',''3'')
and t.state in (''B0A'',''B0T'',''B0S'') and t.Speed_value in (12,20,100)
';
execute immediate v_sql;
commit;
execute immediate 'create index ind_ZXL_GQITV_20110624_1 on ZXL_GQITV_20110624_tmp1(cust_id) parallel 10 nologging';
execute immediate 'create index ind_ZXL_GQITV_20110624_2 on ZXL_GQITV_20110624_tmp1(SERV_ID) parallel 10 nologging';
ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624','0', ' 完成处理-'||rptt.corp_name);
commit; END LOOP; ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624','0', '高清itv客户运行结束');
commit;
-----------------------------------------------------------------------
exception
WHEN SUM_DATE_NULL THEN
rollback;
DBMS_OUTPUT.put_line('输入的月份格式有错!');
--异常要记录入表中备查
ZXL_sp_write_log(P_LOAD_DATE, 'ZXL_SP_GQITV_20110624','-1','输入的统计月份格式有错!');
commit; WHEN OTHERS THEN
rollback;
DBMS_OUTPUT.PUT_LINE('异常号:'||substr(to_char(sqlcode),1,200)||'; 异常信息:'||sqlerrm); vnErr_code := sqlcode;
vcErr_text := sqlerrm;
--异常要记录入表中备查
ZXL_sp_write_log(P_LOAD_DATE,'ZXL_SP_GQITV_20110624',vnErr_code,vcErr_text,V_SQL);
commit;END ZXL_SP_GQITV_20110624;
自己看着改,这是模板!
set serveroutput on;
.......