把以下语句编写一个存储过程
drop table ty_04;
create table ty_04 as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr04@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn;
drop table ty_04_jg;
create table ty_04_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_04)
where rn = 1;
目的就是以上语句顺序执行,结果集展现这个表中ty_04_jg
drop table ty_04;
create table ty_04 as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr04@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn;
drop table ty_04_jg;
create table ty_04_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_04)
where rn = 1;
目的就是以上语句顺序执行,结果集展现这个表中ty_04_jg
as
begin
SQL语句;--把你的SQL语句语句放在这个位置上就OK了!
end proc_test;
不晓得这可是楼主想要的。
begin case declare exit
for goto if loop mod null pragma raise return select update
while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
Line: 5
Text: drop table ty_04;
DECLARE num INT DEFAULT 0;
BEGIN
num:=0;
select count(*) into num from user_tables where table_name = upper('ty_04');
if (num=1) then
execute immediate 'drop table ty_04';
execute immediate 'create table ty_04 as select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr04@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn';
end if;
num:=0;
select count(*) into num from user_tables where table_name = upper('ty_04_jg');
if (num=1) then
execute immediate 'drop table ty_04_jg';
execute immediate 'create table ty_04_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_04)
where rn = 1';
exception
when others then
dbms_output.put_line('sqlcode: '||sqlcode||',sqlerrm: '||sqlerrm);
end;
end test1;
/
create or replace procedure pro_create_tb()
as
v_sign_1 number;
v_sign_2 number;
v_sql_1 varchar2(10000);
v_sql_2 varchar2(10000);
begin
begin
select 1 into v_sign_1
from user_tables
where table_name='ty_04';
if v_sign_1=1 then
execute immediate 'drop table ty_04';
commit;
else v_sql_1 := 'create table ty_04 as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr04@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn';
execute immediate v_sql_1;
commit;
exception
when others then
rollback;
end;
begin
select 1 into v_sign_2
from user_tables
where table_name='ty_04_jg';
if v_sign_1=1 then
execute immediate 'drop table ty_04_jg';
commit;
else v_sql_2 :='create table ty_04_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_04)
where rn = 1';
execute immediate v_sql_2;
commit;
exception
when others then
rollback;
end;
end pro_create_tb;
Compilation errors for PROCEDURE BCJFADMIN.JZ_ZMZError: PLS-00103: 出现符号 "DECLARE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
external language
Line: 2
Text: DECLARE num INT DEFAULT 0;
<an identifier>
<a double-quoted delimited-identifier> current
Line: 1
Text: create or replace procedure pro_create_tb()Error: PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时:
begin case declare
end exit for goto if loop mod null pragma raise return select
update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
Line: 23
Text: exceptionError: PLS-00103: 出现符号 "BEGIN"在需要下列之一时:
end not pragma final
instantiable order overriding static member constructor map
Line: 27
Text: beginError: PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时:
begin case declare
end exit for goto if loop mod null pragma raise return select
update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
Line: 46
Text: exceptionError: PLS-00103: 出现符号 "PRO_CREATE_TB"在需要下列之一时:
;
Line: 50
Text: end pro_create_tb;
as
as 变成 is 吗
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ty_04';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table ty_04 as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr04@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn'; BEGIN
EXECUTE IMMEDIATE 'drop table ty_04_jg';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'create table ty_04_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_04)
where rn = 1';
END;
/
BEGIN
BEGIN
EXECUTE IMMEDIATE 'drop table ty_04';
EXCEPTION
WHEN OTHERS THEN
NULL;
END; EXECUTE IMMEDIATE 'create table ty_04 as
select b.lac, b.ci, b.name,a.msisdn,count(*) count
from ucr_bc1.tg_cdr04@ngbil a, tyjz b
where a.lac1 = b.lac
and a.cell_id1 = b.ci
group by b.lac, b.ci, b.name,a.msisdn'; BEGIN
EXECUTE IMMEDIATE 'drop table ty_04_jg';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
EXECUTE IMMEDIATE 'create table ty_04_jg as
select *
from (select name,
lac,
ci,
msisdn,
count,
row_number() over(partition by msisdn order by count desc) rn
from ty_04)
where rn = 1';
END;
/