问题: 以下一系列工作,怎么连起来工作,存储过程应该怎么写?-- Create 了一个 table
如果已经存在改表则不用再create了,或者覆盖它
CREATE TABLE SAL00
...
CREATE INDEX sal00ind01 ON SAL00 (PARENTL,EntityL,C2L,ACCOUNTL,VALUEL); -- 又Create了一个table
如果已经存在改表则不用再create了,或者覆盖它
create table Sal01
...
CREATE INDEX sal01ind01 ON sal01 (ENTITYL,C2L,ACCOUNTL,VALUEL); --判断sal00里是否有数据,如果有数据执行delete所有记录操作
如何判断我也不会写--向sal00里Insert Records
INSERT INTO sal00
SELECT PARENTID,EntityID,Custom2ID,ACCOUNTID,VALUEID,DDATA
FROM sal_fact;
COMMIT;--在sale00里做更新操作
UPDATE sal00 SET
PARENTL = (SELECT distinct label from sal_parent where id = sal00.parentl),
ENTITYL = (SELECT distinct label from sal_entity where id = sal00.entityl),
C2L = (SELECT distinct label from sal_custom2 where id = sal00.c2l),
ACCOUNTL = (SELECT distinct label from sal_account where id = sal00.accountl),
VALUEL = (SELECT distinct label from sal_value where id = sal00.valuel);
COMMIT;----判断sal01里是否有数据,如果有数据执行delete所有记录操作
如何判断我也不会写--Insert Data
Insert into sal01
SELECT CONCAT(CONCAT(Parentl,'-'),entityl),C2L,AccountL,'Elimination' ValueL,SUM(DECODE(Valuel,'[Contribution Total]',DDATA,'[Parent]',-DDATA))
FROM sal00
GROUP BY ParentL,EntityL,C2L,AccountL;
Commit;
如果已经存在改表则不用再create了,或者覆盖它
CREATE TABLE SAL00
...
CREATE INDEX sal00ind01 ON SAL00 (PARENTL,EntityL,C2L,ACCOUNTL,VALUEL); -- 又Create了一个table
如果已经存在改表则不用再create了,或者覆盖它
create table Sal01
...
CREATE INDEX sal01ind01 ON sal01 (ENTITYL,C2L,ACCOUNTL,VALUEL); --判断sal00里是否有数据,如果有数据执行delete所有记录操作
如何判断我也不会写--向sal00里Insert Records
INSERT INTO sal00
SELECT PARENTID,EntityID,Custom2ID,ACCOUNTID,VALUEID,DDATA
FROM sal_fact;
COMMIT;--在sale00里做更新操作
UPDATE sal00 SET
PARENTL = (SELECT distinct label from sal_parent where id = sal00.parentl),
ENTITYL = (SELECT distinct label from sal_entity where id = sal00.entityl),
C2L = (SELECT distinct label from sal_custom2 where id = sal00.c2l),
ACCOUNTL = (SELECT distinct label from sal_account where id = sal00.accountl),
VALUEL = (SELECT distinct label from sal_value where id = sal00.valuel);
COMMIT;----判断sal01里是否有数据,如果有数据执行delete所有记录操作
如何判断我也不会写--Insert Data
Insert into sal01
SELECT CONCAT(CONCAT(Parentl,'-'),entityl),C2L,AccountL,'Elimination' ValueL,SUM(DECODE(Valuel,'[Contribution Total]',DDATA,'[Parent]',-DDATA))
FROM sal00
GROUP BY ParentL,EntityL,C2L,AccountL;
Commit;
declare
i int;
begin
select count(1) into i from user_tables
where table_name='DATALOG1';
if i>0 then
dbms_output.put_line('Delete '); --存在则删除
else
dbms_output.put_line('Create ');--不存在则创建
end if ;
end;
/
num1 number;
num2 number;
begin
select count(*) into num1 from user_tables where table_name='SAL00';
if num1=0 then
CREATE TABLE SAL00
...
end if;CREATE INDEX sal00ind01 ON SAL00 (PARENTL,EntityL,C2L,ACCOUNTL,VALUEL); -- 又Create了一个table
如果已经存在改表则不用再create了,或者覆盖它
select count(*) into num2 from user_tables where table_name='SAL01';
if num2=0 then
CREATE TABLE SAL01
...
end if;CREATE INDEX sal01ind01 ON sal01 (ENTITYL,C2L,ACCOUNTL,VALUEL); --判断sal00里是否有数据,如果有数据执行delete所有记录操作
如何判断我也不会写 --向sal00里Insert Records
INSERT INTO sal00
SELECT PARENTID,EntityID,Custom2ID,ACCOUNTID,VALUEID,DDATA
FROM sal_fact;
COMMIT; --在sale00里做更新操作
UPDATE sal00 SET
PARENTL = (SELECT distinct label from sal_parent where id = sal00.parentl),
ENTITYL = (SELECT distinct label from sal_entity where id = sal00.entityl),
C2L = (SELECT distinct label from sal_custom2 where id = sal00.c2l),
ACCOUNTL = (SELECT distinct label from sal_account where id = sal00.accountl),
VALUEL = (SELECT distinct label from sal_value where id = sal00.valuel);
COMMIT; ----判断sal01里是否有数据,如果有数据执行delete所有记录操作
如何判断我也不会写 --Insert Data
Insert into sal01
SELECT CONCAT(CONCAT(Parentl,'-'),entityl),C2L,AccountL,'Elimination' ValueL,SUM(DECODE(Valuel,'[Contribution Total]',DDATA,'[Parent]',-DDATA))
FROM sal00
GROUP BY ParentL,EntityL,C2L,AccountL;
Commit;
end;
,v_object_type in user_objects.object_type%type)
return number
--************************************************************
--* Input:
--* v_object_name - name of the Object
--* v_object_type - type of the Object
--*
--* Output:
--* n_cnt - Returns count of records. 1 if object exists, 0 if does not
--************************************************************
is n_cnt number :=0; begin select count(1)
into n_cnt
from user_objects
where object_name = v_object_name
and object_type = v_object_type; return n_cnt; exception
when others
then
dbms_output.put_line (to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
dbms_output.put_line (substr (sqlerrm, 1, 254));
raise; end GET_OBJECT_CNT; create or replace procedure proce
is
v_rec_cnt number := 0; begin if GET_OBJECT_CNT('SAL00','TABLE') = 0
then
execute immediate 'CREATE TABLE SAL00...';
end if;
if GET_OBJECT_CNT('SAL00IND01','INDEX') = 0
then
execute immediate 'CREATE INDEX sal00ind01 ON SAL00 (PARENTL,EntityL,C2L,ACCOUNTL,VALUEL)';
end if; if GET_OBJECT_CNT('SAL01','TABLE') = 0
then
execute immediate 'CREATE TABLE SAL01...';
end if; if GET_OBJECT_CNT('SAL01IND01','INDEX') = 0
then
execute immediate '...';
end if;
select count(1) into v_rec_cnt from SAL00 where rownum <= 1;
if v_rec_cnt = 1
then
delete from SAL00;
end if;
insert into SAL00 SELECT PARENTID,EntityID,Custom2ID,ACCOUNTID,VALUEID,DDATA FROM sal_fact;
UPDATE sal00 SET
PARENTL = (SELECT distinct label from sal_parent where id = sal00.parentl),
ENTITYL = (SELECT distinct label from sal_entity where id = sal00.entityl),
C2L = (SELECT distinct label from sal_custom2 where id = sal00.c2l),
ACCOUNTL = (SELECT distinct label from sal_account where id = sal00.accountl),
VALUEL = (SELECT distinct label from sal_value where id = sal00.valuel);
select count(1) into v_rec_cnt from SAL01 where rownum <= 1;
if v_rec_cnt = 1
then
delete from SAL01;
end if;
Insert into sal01
SELECT CONCAT(CONCAT(Parentl,'-'),entityl),C2L,AccountL,'Elimination' ValueL,SUM(DECODE(Valuel,'[Contribution Total]',DDATA,'[Parent]',-DDATA))
FROM sal00
GROUP BY ParentL,EntityL,C2L,AccountL;
commit;
exception
when others
then
dbms_output.put_line (to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
dbms_output.put_line (substr (sqlerrm, 1, 254));
end proce;
return (datatype)在函数体的结尾需要有return语句。其他基本和procedure相同。何时使用function何时使用procedure:
有一个经验仅供参考,
当需要对表进行操作时,使用procedure;
当需要进行运算或者实现某个功能时,使用function
另外,删除所有记录用truncate,不用delete。
您好function GET_OBJECT_CNT(v_object_name in user_objects.object_name%type
,v_object_type in user_objects.object_type%type)
return number /*返回值的类型是number*/
--************************************************************
--* Input:
--* v_object_name - name of the Object
--* v_object_type - type of the Object
--*
--* Output:
--* n_cnt - Returns count of records. 1 if object exists, 0 if does not
--************************************************************
is n_cnt number :=0; /*is部分我不是很理解*/ begin select count(1) /*为什么用count(1)呀?*/
into n_cnt /*把select的结果传给 n_cnt*/
from user_objects
where object_name = v_object_name
and object_type = v_object_type; return n_cnt; /*这个是语法就要求这么写么,我书上写的不清楚*/ exception
when others
then
dbms_output.put_line (to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
dbms_output.put_line (substr (sqlerrm, 1, 254));
raise; /* raise是干什么用的呀?*/ end GET_OBJECT_CNT;
is 部分用来定义变量
count(1)得出符合下面where条件的记录数
into 将count(1)值赋给n_cnt
return 是将后面的变量值作为这个function的值
raise引发异常。这边的raise应该没有必要
非常感谢,但是我还是不太明白 count(1),count不是组函数的计数函数么,为什么参数是1?谢谢
后面没有group by的时候,分组函数将整个表当成一个组
count(1)表示查询结果的记录总数,只要符合查询条件,就返回一个'1',再count之
1代表一个常量,你随便用一个数字替代都会得到同样的结果
你自己写几个select 语句试一下就知道了