create table 基本_电压等级 ( 分压编码 NUMERIC(2) not null, 分压名称 CHAR(10) null , 基准值 NUMERIC(7) null , 电压考核上限 NUMERIC(7) null , 电压考核下限 NUMERIC(7) null , 母线不平衡率限值 NUMERIC(9,5) null , constraint SYS_基本_电压等级 primary key (分压编码) ) go insert into 基本_电压等级 values(0,'500kV',500000,525000,475000,1) go insert into 基本_电压等级 values(1,'220kV',220000,231000,209000,1) go insert into 基本_电压等级 values(2,'110kV',110000,117700,107000,1) go insert into 基本_电压等级 values(3,'66kV',66000,69500,62700,1) go insert into 基本_电压等级 values(4,'35kV',35000,36750,33250,2) go insert into 基本_电压等级 values(5,'10kV',10000,10700,10000,2) go insert into 基本_电压等级 values(6,'6kV',6000,6300,5700,2) go然后保存为SQL文件
自己在notepad或者其它的文本編輯工具中寫即可。
select count(1) from dba_objects where owner='YOUR_USER' and object_type='TABLE';
查询这个系统表dba_objects,tabs select count(*) from tabs where tname='表名'; .SQL 的文本用文本编辑器就可以写了,这里有个例子: CREATE OR REPLACE FUNCTION F_GET_APPENDIX (STR VARCHAR2,p_BBH INTEGER,p_BMBH VARCHAR2,p_RQ VARCHAR2) RETURN VARCHAR2 IS LS_VALUE VARCHAR2(100); LS_VALUE1 VARCHAR2(1024); LS_VALUE2 VARCHAR2(100); ls_xh VARCHAR2(10); LI_XH INTEGER; v_i int; i int; CURSOR C_CONS IS SELECT nvl(A.VALUE,'0'),A.XH FROM OA_PVT_APPENDIX_VALUE A where a.bbh=p_bbh and a.bmbh=p_bmbh and to_char(a.rq,'yyyy/mm/dd')=p_rq ; BEGIN if str is null or str='' then RETURN ''; else LS_VALUE1 := str; v_i := 0; OPEN C_CONS; LOOP BEGIN FETCH C_CONS INTO LS_VALUE,LI_XH; EXIT WHEN C_CONS%NOTFOUND; v_i := v_i + 1; -- dbms_output.put_line('1i='||v_i||',val='||ls_value||',xh='||li_xh); ls_xh :='%%'||TO_CHAR(li_xh); -- if LS_VALUE = 0 then -- LS_VALUE := ''; -- end if; if INSTR(LS_VALUE1,ls_xh,1) > 0 then LS_VALUE1 := replace(LS_VALUE1,ls_xh, ls_VALUE); end if; EXCEPTION WHEN OTHERS THEN null; END; END LOOP; CLOSE C_CONS; if v_i = 0 then i :=1; loop if INSTR(LS_VALUE1,'%%'||to_char(i),1) > 0 then LS_VALUE1 := replace(LS_VALUE1,'%%'||to_char(i), ''); elsif INSTR(LS_VALUE1,'%%',1) <= 0 then exit; end if; i:=i+1; end loop; end if; if LS_VALUE1 is null then LS_VALUE1 := str; end if ; RETURN LS_VALUE1; end if; END F_GET_APPENDIX; / show errors
(
分压编码 NUMERIC(2) not null,
分压名称 CHAR(10) null ,
基准值 NUMERIC(7) null ,
电压考核上限 NUMERIC(7) null ,
电压考核下限 NUMERIC(7) null ,
母线不平衡率限值 NUMERIC(9,5) null ,
constraint SYS_基本_电压等级 primary key (分压编码)
)
go
insert into 基本_电压等级 values(0,'500kV',500000,525000,475000,1)
go
insert into 基本_电压等级 values(1,'220kV',220000,231000,209000,1)
go
insert into 基本_电压等级 values(2,'110kV',110000,117700,107000,1)
go
insert into 基本_电压等级 values(3,'66kV',66000,69500,62700,1)
go
insert into 基本_电压等级 values(4,'35kV',35000,36750,33250,2)
go
insert into 基本_电压等级 values(5,'10kV',10000,10700,10000,2)
go
insert into 基本_电压等级 values(6,'6kV',6000,6300,5700,2)
go然后保存为SQL文件
select count(*) from tabs where tname='表名';
.SQL 的文本用文本编辑器就可以写了,这里有个例子:
CREATE OR REPLACE FUNCTION F_GET_APPENDIX (STR VARCHAR2,p_BBH INTEGER,p_BMBH VARCHAR2,p_RQ VARCHAR2)
RETURN VARCHAR2
IS
LS_VALUE VARCHAR2(100);
LS_VALUE1 VARCHAR2(1024);
LS_VALUE2 VARCHAR2(100);
ls_xh VARCHAR2(10);
LI_XH INTEGER;
v_i int;
i int;
CURSOR C_CONS IS SELECT nvl(A.VALUE,'0'),A.XH
FROM OA_PVT_APPENDIX_VALUE A where a.bbh=p_bbh and a.bmbh=p_bmbh and to_char(a.rq,'yyyy/mm/dd')=p_rq ;
BEGIN
if str is null or str='' then
RETURN '';
else
LS_VALUE1 := str;
v_i := 0;
OPEN C_CONS;
LOOP
BEGIN
FETCH C_CONS INTO LS_VALUE,LI_XH;
EXIT WHEN C_CONS%NOTFOUND;
v_i := v_i + 1;
-- dbms_output.put_line('1i='||v_i||',val='||ls_value||',xh='||li_xh);
ls_xh :='%%'||TO_CHAR(li_xh);
-- if LS_VALUE = 0 then
-- LS_VALUE := '';
-- end if;
if INSTR(LS_VALUE1,ls_xh,1) > 0 then
LS_VALUE1 := replace(LS_VALUE1,ls_xh, ls_VALUE);
end if;
EXCEPTION
WHEN OTHERS THEN
null;
END;
END LOOP;
CLOSE C_CONS;
if v_i = 0 then
i :=1;
loop
if INSTR(LS_VALUE1,'%%'||to_char(i),1) > 0 then
LS_VALUE1 := replace(LS_VALUE1,'%%'||to_char(i), '');
elsif INSTR(LS_VALUE1,'%%',1) <= 0 then
exit;
end if;
i:=i+1;
end loop;
end if;
if LS_VALUE1 is null then
LS_VALUE1 := str;
end if ;
RETURN LS_VALUE1;
end if;
END F_GET_APPENDIX;
/
show errors