1、RETURNING语句用法 The RETURNING Clause
Include the RETURNING clause with an INSERT, UPDATE, and DELETE to return column values.
UPDATE customers
SET credit_limit = credit_limit * 1.10 WHERE customer_id = p_in_id
RETURNING credit_limit INTO o_credit;
2、自定义数据类型
Type t_ic07 IS Table OF ic07%rowtype Index By Binary_Integer; tab_ic07 t_ic07;
Type cur_ic07 IS REF Cursor Return ic07%rowtype;
Type rec_treatment IS Record (
str_aac001 ic10.aac001%type, str_aab001 ic10.aab001%type, str_aae002 ic14.aae002%type, str_aic234 ic13.aic234%type, n_aic142 ic13.aic142%type, str_bic201 ic10.bic201%type, str_aae145 ic10.aae145%type );
Type t_pertreat IS TABLE OF rec_treatment INDEX BY BINARY_INTEGER; tab_treatment t_pertreat; tab_PerTreat t_pertreat;
Type cur_treatment IS REF CURSOR Return rec_treatment;
3、批绑定用法 DECLARE
TYPE Replist IS VARRAY(100) OF NUMBER; Repids REPLIST := REPLIST(153, 155, 156, 161);
TYPE Numlist IS TABLE OF orders.order_total%TYPE; Totlist NUMLIST;
big_total CONSTANT NUMBER := 60000; BEGIN
FORALL i IN Repids.FIRST..Repids.LAST UPDATE orders
SET order_total = .95 * order_total WHERE sales_rep_id = Repids(i) AND order_total > big_total
RETURNING order_total BULK COLLECT INTO Totlist; END; /
4、用自定义记录集作为参数（Use User-Defined Records as Parameters） DECLARE
TYPE CustRec IS RECORD (
customer_id customers.customer_id%TYPE, cust_last_name VARCHAR2(20), cust_email VARCHAR2(30), credit_limit NUMBER(9,2)); ...
PROCEDURE raise_credit (cust_info CustRec);
5、用集合作为参数（Use Collections as Arguments） PACKAGE cust_actions IS
TYPE NameTabTyp IS TABLE OF customer.cust_last_name%TYPE INDEX BY BINARY_INTEGER;
TYPE CreditTabTyp IS TABLE OF customers.credit_limit%TYPE INDEX BY BINARY_INTEGER; ...
PROCEDURE credit_batch( name_tab IN NameTabTyp , credit_tab IN CreditTabTyp, ...);
PROCEDURE log_names ( name_tab IN NameTabTyp ); END cust_actions;
6、动态SQL执行
CREATE PROCEDURE insert_into_table (table_name VARCHAR2, p_prod_id NUMBER, p_wh_id NUMBER, p_quantity NUMBER) IS
v_stmt_str VARCHAR2(200); BEGIN
v_stmt_str := 'INSERT INTO ' || table_name || ' values (:prod_id, :wh_id, :qoh)'; EXECUTE IMMEDIATE v_stmt_str USING p_prod_id, p_wh_id, p_quantity; END insert_into_table; /
7、动态SQL执行2
n_cursor := dbms_sql.open_cursor;
dbms_sql.parse(n_cursor,str_sql,dbms_sql.native); n_rows := dbms_sql.execute(n_cursor); dbms_sql.close_cursor(n_cursor);
8、分析PL/SQL(Profiling PL/SQL)
1. Starting profiler data collection in the run.
2. Executing PL/SQL code for which profiler and code coverage data is required. 3. Flushing the data to the profiler tables. 4. Stopping profiler data collection. 5. Analyzing the data collected. 例如：
CREATE OR REPLACE PROCEDURE my_profiler
(p_comment1 IN VARCHAR2, p_comment2 IN VARCHAR2) IS
v_return_code NUMBER; BEGIN
--start the profiler
v_return_code:=DBMS_PROFILER.START_PROFILER(p_comment1, p_comment2); dbms_output.put_line ('Result from START: '||v_return_code);
-- now run a program...
raise_list_price (102099, .025);
--flush the collected data to the dictionary tables
v_return_code := DBMS_PROFILER.FLUSH_DATA;
dbms_output.put_line ('Result from FLUSH: '||v_return_code);
--stop profiling
v_return_code := DBMS_PROFILER.STOP_PROFILER; dbms_output.put_line ('Result from STOP: '||v_return_code); END; /
查询plsql_profiler_runs表数据可以获知执行情况。 SELECT runid, run_owner, run_date, run_comment,
run_comment1, run_total_time
FROM plsql_profiler_runs;
SELECT runid, unit_number, unit_type, unit_owner, unit_name FROM plsql_profiler_units inner JOIN plsql_profiler_runs USING ( runid );
SELECT line#, total_occur, total_time, min_time, max_time
FROM plsql_profiler_data
WHERE runid = 1 AND unit_number = 2;
9、常用的ORACLE包
You cannot use DDL commands in a PL/SQL block.
dbms_session provides an interface to some of the options in the ALTER SESSION command.
dbms_session Subprograms
? close_database_link ? is_role_enabled ? reset_package ? set_sql_trace
? unique_session_id
? set_close_cached_open_cursors ? free_unused_user_memory ? is_session_alive ? set_nls ? set_role ? set_context
dbms_lock Programs
? allocate_unique ? request ? convert ? release ? sleep
utl_smtp
? Functions:
– helo – mail – rcpt – data
– open_data – write_data – close_data – rset – quit ? Record Types:
– connection – reply – replies
10、Using Collection Methods
? EXISTS ? COUNT ? LIMIT
? FIRST and LAST ? PRIOR and NEXT ? EXTEND ? TRIM ? DELETE
