ORACLE数据库管理plsql编程

1. 范例3&＃xff0c;清理数据表代码段

declaresql1 varchar2(1000);
beginfor table_name in (select object_namefrom user_objectswhere created end;


1. exception列举

2. 

--异常处理部分&＃xff1a;当上面的条件都不满足时进入exception部分2种方式定义 when others then

&＃43;sql statements 或者 when no_data_found then &＃43;sql statements;

在游标中经常使用的退出循环语句为 exit when cursor%notfound;

1. for循环、及decode用法

trh

--FOR循环隐试打开游标&＃xff0c;注意此游标不是事先定义的游标&＃xff0c;而是直接利用sql语句生成的&＃xff0c;

其中ob此类似数组的类型不用声明&＃xff0c;可直接使用ob.NAME,ob.status..................。

--使用此decode()函数对于中间两项的参数可以写多组&＃xff0c;并与第一个参数进行判断&＃xff0c;返回不同的值&＃xff0c;都不满足则返回最后的参数值。

动态游标的使用

带boolean类型存储过程

--知识点总结&＃xff1a;首先建立的boolean类型数据用于if条件的使用。使用方法 olaptok:&＃61;FALSE,olaptok:&＃61;&＃61;TRUE, IFt条件的使用 if olaptok and olaptokl then sql statements esle sql statements end; 此例中注意if not(item_date&＃61;’201309’) 的使用。

1. Execute immediate

create or replace procedure ora$_sys_rep_auth asbeginEXECUTE IMMEDIATE &＃39;GRANT SELECT ON SYSTEM.repcat$_repschema TO SYS &＃39; ||&＃39;WITH GRANT OPTION&＃39;;EXECUTE IMMEDIATE &＃39;GRANT SELECT ON SYSTEM.repcat$_repprop TO SYS &＃39; ||&＃39;WITH GRANT OPTION&＃39;;EXECUTE IMMEDIATE &＃39;GRANT SELECT ON SYSTEM.def$_aqcall TO SYS &＃39; ||&＃39;WITH GRANT OPTION&＃39;;EXECUTE IMMEDIATE &＃39;GRANT SELECT ON SYSTEM.def$_calldest TO SYS &＃39; ||&＃39;WITH GRANT OPTION&＃39;;EXECUTE IMMEDIATE &＃39;GRANT SELECT ON SYSTEM.def$_error TO SYS &＃39; ||&＃39;WITH GRANT OPTION&＃39;;EXECUTE IMMEDIATE &＃39;GRANT SELECT ON SYSTEM.def$_destination TO SYS &＃39; ||&＃39;WITH GRANT OPTION&＃39;;end;

范例代码2

CREATE OR REPLACE PROCEDURE INSERTPROJECT ISTYPE 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:&＃61;&＃39;40fa3881-c21d-4d8d-8c70-b1aa788e9cfda&＃39;;OPEN ROWCURSOR FORselect t.* from visu_role t where t.id not in (select distinct(t1.ROLEID) from visu_role_projects t1);LOOPFETCH ROWCURSORINTO TABLEROW;EXIT WHEN ROWCURSOR%NOTFOUND;EXECUTE IMMEDIATE &＃39;insert into visu_role_projects values(:1,:2)&＃39;using TABLEROW.id,PROJECTID;END LOOP;IF ROWCURSOR%ISOPEN THENCLOSE ROWCURSOR;END IF;OPEN ROWCURSOR FORselect t.id from visu_reve_template t where t.id not in (select distinct(t1.revetemplateid) from visu_project_reve t1);LOOPFETCH ROWCURSORINTO TABLEROW1;EXIT WHEN ROWCURSOR%NOTFOUND;EXECUTE IMMEDIATE &＃39;insert into visu_project_reve values(:1,:2)&＃39;using TABLEROW1,PROJECTID;END LOOP;IF ROWCURSOR%ISOPEN THENCLOSE ROWCURSOR;END IF;