1、cursor传入参数
定义：cursor [cursor变量名称]([参数名称] [参数类型]) IS [SQL语句，可以使用传入参数]
例子：
cursor moTypeNames(domain VARCHAR2) IS
select t1.modelname from pm4h_mo.mdl_resmodel t1,pm4h_mo.mdl_domain t2
where t2.domainname=domain
and t1.domainid = t2.domainid;
使用：open [cursor变量名称]([参数])
例子：
open moTypeNames(domainName);
2、loop fetch使用
fetch [cursor变量] into [循环变量]
when [cursor变量] %notfound;
例子：
loop
fetch domainNames into domainName;
exit when domainNames%notfound;
end loop;
3、if使用
if(条件) then
[真值语句块]
end if;
4、单引号字符表示
''''：最外层两个单引号表示字符串引用，字符串中间第一个单引号表示转义，第二个单引号表示单引号。
例子：
v_sql :='select count(*)
from pm4h_mo.mdl_resmodel t1, pm4h_mo.mdl_resmodeltable t2
where t1.modelname='''||moTypeName||''' and t1.modelid=t2.modelid';
5、open cursor后要注意close cursor
declare
--存储域名
type t_cur is ref cursor;
domainNames t_cur;
domainName varchar2(50) :=''; --存储每个域下的网元类型
cursor moTypeNames(domain VARCHAR2) IS
select t1.modelname from pm4h_mo.mdl_resmodel t1,pm4h_mo.mdl_domain t2
where t2.domainname=domain
and t1.domainid = t2.domainid;
moTypeName varchar2(500) :=''; --拼接sql临时变量
v_sql varchar2(500) :=''; --网元实体表名称
tableName varchar2(500) :=''; --每个网元类型实体数
v_count_number number(30) :=0; --总实体数
v_count_sum number(30) :=0;
begin
--不限制输出长度
dbms_output.enable(null); --查询域名
open domainNames for
select domainname from pm4h_mo.mdl_domain; --循环域名
loop
fetch domainNames into domainName;
exit when domainNames%notfound;
dbms_output.put_line(domainName); --查询MoType
open moTypeNames(domainName); --循环MoType
loop
fetch moTypeNames into moTypeName;
exit when moTypeNames%notfound;
dbms_output.put(moTypeName); --查询是否存在实体表：ElementMoType不存在实体表
v_sql :='select count(*)
from pm4h_mo.mdl_resmodel t1, pm4h_mo.mdl_resmodeltable t2
where t1.modelname='''||moTypeName||''' and t1.modelid=t2.modelid';
execute immediate v_sql
into v_count_number; if(v_count_number <> 0) then
--存在实体表，查询实体表名
v_sql :='select t2.tablename
from pm4h_mo.mdl_resmodel t1, pm4h_mo.mdl_resmodeltable t2
where t1.modelname='''||moTypeName||''' and t1.modelid=t2.modelid';
execute immediate v_sql
into tableName; --查询实体数量
v_sql := 'select count(*) from pm4h_mo.obj_'||tableName || ' where verendtime is null';
execute immediate v_sql
into v_count_number;
dbms_output.put_line(':'||v_count_number); --计算实体总量
v_count_sum :=v_count_sum+v_count_number;
end if;
end loop;
dbms_output.put_line('');
close moTypeNames;
end loop;
dbms_output.put_line('total:'||v_count_sum);
close domainNames;
end;