-
范例3,清理数据表代码段
declare
sql1 varchar2(1000);
begin
for table_name in (select object_name
from user_objects
where created < trunc(sysdate, 'yy')
and object_type = 'TABLE' and object_name like 'TMP%') loop
sql1:='drop table '||table_name.object_name||' purge';
execute immediate sql1;
end loop;
end;
-
exception列举
--异常处理部分:当上面的条件都不满足时进入exception部分2种方式定义 when others then
+sql statements 或者 when no_data_found then +sql statements;
在游标中经常使用的退出循环语句为 exit when cursor%notfound;
-
for循环、及decode用法
trh
--FOR循环隐试打开游标,注意此游标不是事先定义的游标,而是直接利用sql语句生成的,
其中ob此类似数组的类型不用声明,可直接使用ob.NAME,ob.status..................。
--使用此decode()函数对于中间两项的参数可以写多组,并与第一个参数进行判断,返回不同的值,都不满足则返回最后的参数值。
动态游标的使用
带boolean类型存储过程
--知识点总结:首先建立的boolean类型数据用于if条件的使用。使用方法 olaptok:=FALSE,olaptok:==TRUE, IFt条件的使用 if olaptok and olaptokl then sql statements esle sql statements end; 此例中注意if not(item_date=’201309’) 的使用。
-
Execute immediate
create or replace procedure ora$_sys_rep_auth as
begin
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repschema TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.repcat$_repprop TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_aqcall TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_calldest TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_error TO SYS ' ||
'WITH GRANT OPTION';
EXECUTE IMMEDIATE 'GRANT SELECT ON SYSTEM.def$_destination TO SYS ' ||
'WITH GRANT OPTION';
end;
范例代码2
CREATE OR REPLACE PROCEDURE INSERTPROJECT IS
TYPE TYPECURSOR IS REF CURSOR;
ROWCURSOR TYPECURSOR;
TABLEROW visu_role%ROWTYPE;
TABLEROW1 visu_reve_template.id%TYPE;
TABLEROW2 visu_reve_templatecategory.id%TYPE;
TABLEROW3 VISU_DATASET_DEFINE.Id%TYPE;
TABLEROW4 VISU_SERVICE_DEFINE.Id%TYPE;
TABLEROW5 visu_exportedpicture.templateid%TYPE;
TABLEROW6 visu_importedpicture.templateid%TYPE;
TABLEROW7 visu_remoteaddress.id%TYPE;
PROJECTID VARCHAR2(50);
BEGIN
PROJECTID:='40fa3881-c21d-4d8d-8c70-b1aa788e9cfda';
OPEN ROWCURSOR FOR
select t.* from visu_role t where t.id not in (select distinct(t1.ROLEID) from visu_role_projects t1);
LOOP
FETCH ROWCURSOR
INTO TABLEROW;
EXIT WHEN ROWCURSOR%NOTFOUND;
EXECUTE IMMEDIATE 'insert into visu_role_projects values(:1,:2)'
using TABLEROW.id,PROJECTID;
END LOOP;
IF ROWCURSOR%ISOPEN THEN
CLOSE ROWCURSOR;
END IF;
OPEN ROWCURSOR FOR
select t.id from visu_reve_template t where t.id not in (select distinct(t1.revetemplateid) from visu_project_reve t1);
LOOP
FETCH ROWCURSOR
INTO TABLEROW1;
EXIT WHEN ROWCURSOR%NOTFOUND;
EXECUTE IMMEDIATE 'insert into visu_project_reve values(:1,:2)'
using TABLEROW1,PROJECTID;
END LOOP;
IF ROWCURSOR%ISOPEN THEN
CLOSE ROWCURSOR;
END IF;