PL/SQL中的一些知识

1.在PL/SQL中，两个点号(')表示为一个点号，比如：SELECT ' ''你好'' ' FROM DUAL; 则返回的值为 '你好',在Oracle 10开始，支持q'来区分，分隔符分别为(),[],{},||,如下图:

SELECT '''你好''',q'('你好')',q'{'你好'}',q'['你好']',q'|'你好'|' FROM dual;  
如果是 q'(你好)',则返回 你好。

　　

2.PL/SQL中产生异常有三种形式：

     1）.PL/SQL 自动产生的异常

     2). Raise产生的异常

     3). Raise_Application_Error .

 第三种产生的异常后，对数据库所做的变更，比如Insert,update,deleate不会回滚，需要手动rollback.

3.从Oracle 9.0开始，在进行DML(Delete,Update,Insert)时，可以用变量 SQL%Found,SQL%NOTFound,SQL%RowCount来判断是否有影响的记录，

当有记录被操作时，则SQL%FOUND为True, SQL%NOTFOUND是和SQL%Found相对的。

SQL%RowCOUNT是影响的行数.

BEGIN
  DELETE FROM Sys_Emp x WHERE x.emp_name='xx';
  IF SQL%NOTFOUND THEN
     dbms_output.put_line('沒有此人');
  ELSE
     dbms_output.put_line('已刪除此人');
  END IF;
  dbms_output.put_line('删除了'||SQL%ROWCOUNT||'条记录');
  END;

 SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT是隐式游标用到的，DB在执行一条语句时会自动产生一个隐式游标，可用这些变更来判断。

如果是显示游标，则用到%FOUND,%NOTFOUND,%ROWCOUNT,%ISOPEN,%BULK_ROWCOUNT,%BULK_EXCEPTIONS来判断。

比如

declare
    cursor mycur is ...
begin
   for rec in mycur loop
      if mycur%Found then
        dbms_out.putline(mycur%ROWCOUNT);
      end if;
   end loop;
end;

 在Oracle8.0之前一直被告知尽量不用单行Fetch隐式游标，因为隐式游标遵循ISO标准且始终必须执行两次Fetch过程，从而它比显示游标（对于显式游标来说，可以只执行一次TETCH过程）效率低。

从8.0以后，Oracle专门做了针对性的优化，目前两者都差不多，大家可以按自己的意愿选择。

4.在DML數據時，可以同時把相關的數據傳給一個變量，此時用Returning語句

DML  SQL Returning 列名【或相關操作】 into 變量

DECLARE p_no sajet.sys_emp.emp_no%TYPE;
BEGIN
  DELETE FROM Sys_Emp x WHERE x.emp_name='xxx' RETURNING x.emp_no||'vv' INTO p_no;
  IF SQL%FOUND THEN
     dbms_output.put_line(p_no);
  ELSE
     dbms_output.put_line('没有此人');
  END IF;
  dbms_output.put_line(SQL%ROWCOUNT);
  END;

 

  如果返回的列是當時更新的列，則返回變更是更新後的值。

5.自制事物

在执行一个事物块时，需要手工用commit 或rollback来控制事物，

还有些应用程序或DB在会话结束时，如果出现DML语句失败，则会自动调用RollBack,此时会把所有的DML全部取消。

但有些情况下这些情况不能满足需要，比如写入log 日志，主要记录在主要的DML中失败时要写入 log.这时就要用到自制事物。

自制事物可以有效隔离主事物的影响。

声明自制事物时在最小操作块的声明处用

 PRAGMA AUTONOMOUS_TRANSACTION;声明，这表示此时此块是用自制事物的，它会独立处理事物并不影响它的主事物，主事物也不影响它自己。

比如：在向一个表中写入数据时会报错，需要把报错的信息写入到一log表中。

如果直接写如下语句：

  

begin
   insert into table values ....; --此时出错
  exception
    when others then
       insert into logtable values(sqlcode,sqlerrm);
end;

 出错的，DB会自动 RollBack，这时它的log也会RollBack.

 要想解决此问题，就要用到自制事物。

自制事物又是写在SQL块中的，所以在出错写入log 的部分要用个语句块来处理才行。

自制事物中一定要调用commit或rollback，否则系统会报相关错误

begin
     insert into table values...;
   exception
   when others then
      log(sqlcode,sqlerrm); --自制事物块
end;


procedure log(code,err) is
  pragma autoonomous_transaction; --块的开始处声明自制事物
begin
    insert into log values (code,err);
   commit; --一定写
  exception
    when others then
      null;
     rollback;  --一定写
end;

因此当出错的，主事物可以RollBack但写入log的就会完整的记录下来了。

如果一个过程中用到自制事物且会自我回调，这时的自制事物只会对自己的块有作用，对外面的调用没有影响，比如A过程调用A过程，第二A过程会独自处理自己的事物，但不会受第一个A事物的影响 ，虽然它们是一样的程序。

以下为实例代码：

----------------------Create Table-----------------------
create table MYTESTTABLE
(
  id         NUMBER,
  name       VARCHAR2(20),
  updatedate DATE default sysdate
)

create table LOG
(
  code       INTEGER,
  text       VARCHAR2(4000),
  createdate DATE default sysdate
)
---------------------------Create Table End------------------------

-----------------------Create Log Package-----------------------------
create or replace package body PKG_Log IS
 PROCEDURE putline(code IN INTEGER,text IN VARCHAR2) IS --未用自制事物
   BEGIN     
     INSERT INTO sajet.log(code,  text )  VALUES (code,text);  
     EXCEPTION 
       WHEN OTHERS THEN
           dbms_output.put_line(SQLERRM);
   END;
   
  PROCEDURE saveline(code IN INTEGER,text IN VARCHAR2) IS --用到自制事物
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
      putline(code,text);
      COMMIT;
    END;
end PKG_Log;

-------------------------Create Log Package End ---------------

---------------------Main Process----------------------------
BEGIN
   INSERT INTO sajet.mytesttable(ID,NAME) VALUES(1,'abc');
   INSERT INTO sajet.mytesttable(id,   NAME  )
   VALUES (2,'01234567891011121314151617181920'); --Error:Large Length
   COMMIT;
   EXCEPTION
     WHEN OTHERS THEN        
        --PKG_Log.putline(SQLCODE,SQLERRM);--非自制事物，则此处也会回滚，等于没有log功能
        PKG_log.saveline(SQLCODE,SQLERRM);--自制事物，不会受主事物影响，可以写入log功能
        dbms_output.put_line(SQLERRM);
     ROLLBACK; 
  END;
-------------------------Main Process End-------------------------------------

6.动态SQL语句

  用EXECUTE IMMEDIATE来执行sql字符串，动态SQL可用到参数及返回值，但有以下几点说明或注意点:

  1).  如果用到Returning需返回值，则只能用DML（Insert,update,Delete），Select是不行的

  2); 如果传参数时有相同名字的参数（一般是传入),即重复的占位符

      i) 如果用动态SQL,即没有begin ..end这样的标识符且字符串结尾不用;结尾时，则要传入相同占位符数量的参数。如下图：

      

 DECLARE
 p_sql VARCHAR2(4000):= 'update  sys_emp set host_name=:1 where emp_no=:2 and :2 is not null returning emp_name into :3';  --：2参数是相同的 另此语句中没有;号
 p_name VARCHAR2(20);
BEGIN
    
    EXECUTE IMMEDIATE  p_sql  USING 'xxx', '217110053' ,'217110053',OUT p_name ;--传入相同的参数值
    dbms_output.put_line(p_name);
    
  END;

   2)如果用PL/SQL时，则在 begin..end的sql中有分号的，这样相同的参数只传一个

 DECLARE
 p_sql VARCHAR2(4000):= 'begin update  sys_emp set host_name=:1 where emp_no=:2 and :2 is not null returning emp_name into :3;end;';--要加上begin end;且里面的要有;号                                
                                 
 p_name VARCHAR2(20);
BEGIN
    
    EXECUTE IMMEDIATE  p_sql  USING 'xxx', '217110053' ,OUT p_name ;--传一个参数即可。
    dbms_output.put_line(p_name);
    
  END;