ORACLE数据库管理-plsql编程

1. 范例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;

1. exception列举

2. 

 

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

+sql statements 或者 when no_data_found then +sql statements;

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

1. for循环、及decode用法

trh

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

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

 

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

动态游标的使用

 

带boolean类型存储过程

1. 

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

1. 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;