ITPub博客

利用REF CURSOR，可以在程序间传递结果集（一个程序里打开游标变量，在另外的程序里处理数据）。也可以利用REF CURSOR实现BULK SQL，提高SQL性能。[@more@]REF CURSOR分两种，Strong REF CURSOR 和 Weak REF CURSOR。Strong REF CURSOR:指定retrun type，CURSOR变量的类型必须和return type一致。DECLARETYPE strongcurtyp IS REF CURSOR RETURN emp%ROWTYPE;emp_cv strongcurtyp;Weak REF CURSOR:不指定return type，能和任何类型的CURSOR变量匹配。DECLARETYPE weakcurtyp IS REF CURSOR;weak_cv weakcurtyp;any_cv SYS_REFCURSOR; --使

使用Strong REF CURSOR例子[@more@]CREATE OR REPLACE PACKAGE emp_data ASTYPE empcurtyp IS REF CURSOR RETURN emp%rowtype; --定义TYPE as Strong REF CURSOR PROCEDURE open_emp_cv(emp_cv IN OUT empcurtyp, choice INT); --根据不同的choice选择不同的CURSORPROCEDURE retrieve_data(choice INT); --通过调用procedure open_emp_cv，返回指定的结果集。END emp_data;=============================================CREATE OR REPLACE PACKAGE BODY emp_data AS--procedure open_emp_cv--------------------------------------------

察看某个用户的各sql对shared pool的使用情况[@more@]rem rem FUNCTION: Generate a report of SQL Area Memory Usage rem showing SQL Text and memory catagories rem rem sqlmem.sql rem column sql_text format a30 heading Text word_wrapped column sharable_mem heading Shared|Bytes column persistent_mem heading Persistent|Bytes column runtime_mem heading Runtime|Bytes column loads heading Loads column users format a15 heading 'User' column executions heading 'Executions' column users_executing