一 pivot操作符
这个东西应该是从sql server里学过来的，不过话说回来，这个功能的确很好用。说白了，就是做行转列。举个例子说明吧。要运行以下的例子，必须要有scott账户下的emp和dept两个表，如果没有的话，可以运行以下语句：
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;
通常来说我们做行转列都是使用以下做法（在做报表的时候）：
select deptno,
sum( decode( job, 'CLERK', sal ) ) clerk,
sum( decode( job, 'SALESMAN', sal ) ) salesman,
sum( decode( job, 'MANAGER', sal ) ) manager,
sum( decode( job, 'ANALYST', sal ) ) analyst,
sum( decode( job, 'PRESIDENT', sal ) ) president
from scott.emp
group by deptno
order by deptno；
这个查询得到的结果如下：
DEPTNO CLERK SALESMAN MANAGER ANALYST PRESIDENT
---------- ---------- ---------- ---------- ---------- ----------
10 1300 2450 5000
20 1900 2975 6000
30 950 5600 2850
而在11g中，我们可以这么写查询：
select *
from (select deptno, job, sal
from scott.emp ) e
pivot( sum(sal) for job in
( 'CLERK', 'SALESMAN', 'MANAGER',
'ANALYST', 'PRESIDENT' ) )
order by deptno；
更好的是，我们现在可以不用指定列表：
select *
from (select deptno, job, sal
from scott.emp ) e
pivot( sum(sal) for job in
(select distinct JOB from emp))
order by deptno；
二 sequence
在11g以前，在PLSQL里使用sequence的值必须用使用这种方法：
select t_seq.nextval() into var from dual;
而11g里可以直接赋值：
var:=t_seq.nextval();
三 log_error脚本
这是ORACLE提供的一个记录错误的日志脚本，特别适合在PLSQL编程的时候碰见WHEN OTHERS THEN。
CREATE OR REPLACE FUNCTION getosuser RETURN VARCHAR2 IS
vOSUser user_users.username%TYPE;
BEGIN
SELECT osuser
INTO vOSUser
FROM sys.v_$session
WHERE sid = (
SELECT sid
FROM sys.v_$mystat
WHERE rownum = 1);
RETURN vOSUser;
EXCEPTION
WHEN OTHERS THEN
RETURN 'UNK';
END getosuser;
CREATE TABLE errorlog (
procname VARCHAR2(61),
loadfilename VARCHAR2(40),
runtime DATE DEFAULT SYSDATE,
osuser VARCHAR2(30),
mesgtext VARCHAR2(250));
CREATE OR REPLACE PROCEDURE log_error (
pProcName VARCHAR2,
pLoadFile VARCHAR2,
pMesgText VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO errorlog
(procname, loadfilename, osuser, mesgtext)
VALUES
(pProcName, pLoadFile, getOSUser, pMesgText);
COMMIT;
-- No exception handler intentionally. Why?
END log_error;
/
当然这个脚本也是自治事务的一个好例子。
以下是ORACLE提供的另外一个处理异常的脚本：
CREATE TABLE error_log (
error_timestamp TIMESTAMP(9),
database_name VARCHAR(50),
instance_number NUMBER,
error_number NUMBER,
error_message VARCHAR2(255),
logged_on_as VARCHAR2(30),
client_host VARCHAR2(50),
service_name VARCHAR2(30));
CREATE OR REPLACE PROCEDURE error_trap IS
odbname VARCHAR2(50); -- Oracle database name
oinst NUMBER; -- Oracle instance number
enum NUMBER; -- Error Message number
emsg VARCHAR2(250); -- Error text
curschema VARCHAR2(30);
clihost VARCHAR2(50);
serv_name VARCHAR2(30);
-- PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
enum := sqlcode;
emsg := sqlerrm;
odbname := ora_database_name;
oinst := ora_instance_num;
SELECT sys_context('USERENV', 'CURRENT_SCHEMA')
INTO curschema
FROM dual;
SELECT sys_context('USERENV', 'HOST')
INTO clihost
FROM dual;
SELECT sys_context('USERENV', 'SERVICE_NAME')
INTO serv_name
FROM dual;
INSERT INTO error_log
(error_timestamp, database_name, instance_number,
error_number, error_message, logged_on_as,
client_host, service_name)
VALUES
(SYSTIMESTAMP, odbname, oinst, enum, emsg,
curschema, clihost, serv_name);
COMMIT;
END error_trap;
/
CREATE OR REPLACE TRIGGER error_trig
AFTER SERVERERROR ON DATABASE
CALL error_trap
/
BEGIN
RAISE zero_divide;
END;
/
CREATE TABLE errors (
module VARCHAR2(50),
seq_number NUMBER,
error_number NUMBER,
error_mesg VARCHAR2(100),
error_stack VARCHAR2(2000),
call_stack VARCHAR2(2000),
timestamp DATE);
ALTER TABLE errors
ADD CONSTRAINT pk_errors
PRIMARY KEY (module, seq_number)
USING INDEX
TABLESPACE indx_sml;
CREATE TABLE call_stacks (
module VARCHAR2(50),
seq_number NUMBER,
call_order NUMBER,
object_handle VARCHAR2(10),
line_num NUMBER,
object_name VARCHAR2(80));
ALTER TABLE call_stacks
ADD CONSTRAINT pk_call_stacks
PRIMARY KEY (module, seq_number, call_order)
USING INDEX
TABLESPACE indx_sml;
ALTER TABLE call_stacks
ADD CONSTRAINT fk_cs_errors
FOREIGN KEY (module, seq_number)
REFERENCES errors (module, seq_number)
ON DELETE CASCADE;
CREATE TABLE error_stacks (
module VARCHAR2(50),
seq_number NUMBER,
error_order NUMBER,
facility CHAR(3),
error_number NUMBER(5),
error_mesg VARCHAR2(100));
ALTER TABLE error_stacks
ADD CONSTRAINT pk_error_stacks
PRIMARY KEY (module, seq_number, error_order)
USING INDEX
TABLESPACE indx_sml;
ALTER TABLE error_stacks
ADD CONSTRAINT fk_es_errors
FOREIGN KEY (module, seq_number)
REFERENCES errors (module, seq_number)
ON DELETE CASCADE;
CREATE SEQUENCE error_seq
START WITH 1
INCREMENT BY 1;
CREATE OR REPLACE PACKAGE ErrorPkg AS
/* Generic error handling package, using DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.FORMAT_CALL_STACK. This package stores general error information in the errors table, with detailed call stack and error stack information in the call_stacks and error_stacks tables, respectively.
Entry point for handling errors. HandleAll should be called from all exception handlers where you want the error to be logged. p_Top should be TRUE only at the topmost level of procedure nesting. It should be FALSE at other levels.
*/
PROCEDURE HandleAll(p_Top BOOLEAN);
/*
Prints the error and call stacks (using DBMS_OUTPUT) for the given module and sequence number.
*/
PROCEDURE PrintStacks(p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE);
/*
Unwinds the call and error stacks, and stores them in the errors and call stacks tables. Returns the sequence number under which the error is stored. If p_CommitFlag is TRUE,
then the inserts are committed. In order to use StoreStacks, an error must have been handled. Thus HandleAll should have been called with p_Top = TRUE.
*/
PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
p_CommitFlag BOOLEAN DEFAULT FALSE);
END ErrorPkg;
/
CREATE OR REPLACE PACKAGE BODY ErrorPkg IS
v_NewLine CONSTANT CHAR(1) := CHR(10);
v_Handled BOOLEAN := FALSE;
v_ErrorStack VARCHAR2(2000);
v_CallStack VARCHAR2(2000);
PROCEDURE HandleAll(p_Top BOOLEAN) IS
BEGIN
IF p_Top THEN
v_Handled := FALSE;
ELSIF NOT v_Handled THEN
v_Handled := TRUE;
v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK;
v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK;
END IF;
END HandleAll;
--===================================================
PROCEDURE PrintStacks(
p_Module IN errors.module%TYPE,
p_SeqNum IN errors.seq_number%TYPE)
IS
v_TimeStamp errors.timestamp%TYPE;
v_ErrorMsg errors.error_mesg%TYPE;
CURSOR c_CallCur IS
SELECT object_handle, line_num, object_name
FROM call_stacks
WHERE module = p_Module
AND seq_number = p_SeqNum
ORDER BY call_order;
CURSOR c_ErrorCur IS
SELECT facility, error_number, error_mesg
FROM error_stacks
WHERE module = p_Module
AND seq_number = p_SeqNum
ORDER BY error_order;
BEGIN
SELECT timestamp, error_mesg
INTO v_TimeStamp, v_ErrorMsg
FROM errors
WHERE module = p_Module
AND seq_number = p_SeqNum;
-- Output general error information.
dbms_output.put_line(TO_CHAR(v_TimeStamp, 'DD-MON-YY HH24:MI:SS'));
dbms_output.put(' Module: ' || p_Module);
dbms_output.put(' Error #' || p_SeqNum || ': ');
dbms_output.put_line(v_ErrorMsg);
-- Output the call stack.
dbms_output.put('Complete Call Stack:');
dbms_output.put(' Object Handle Line Number Object Name');
dbms_output.put_line(' ------------- ----------- -----------');
FOR v_CallRec in c_CallCur
LOOP
dbms_output.put(RPAD(' ' || v_CallRec.object_handle, 15));
dbms_output.put(RPAD(' ' || TO_CHAR(v_CallRec.line_num), 13));
dbms_output.put_line(' ' || v_CallRec.object_name);
END LOOP;
-- Output the error stack.
dbms_output.put_line('Complete Error Stack:');
FOR v_ErrorRec in c_ErrorCur
LOOP
dbms_output.put(' ' || v_ErrorRec.facility || '-');
dbms_output.put(TO_CHAR(v_ErrorRec.error_number) || ': ');
dbms_output.put_line(v_ErrorRec.error_mesg);
END LOOP;
END PrintStacks;
--===================================================
PROCEDURE StoreStacks(p_Module IN errors.module%TYPE,
p_SeqNum OUT errors.seq_number%TYPE,
p_CommitFlag BOOLEAN DEFAULT FALSE)
IS
v_SeqNum NUMBER;
v_Index NUMBER;
v_Length NUMBER;
v_End NUMBER;
v_Call VARCHAR2(100);
v_CallOrder NUMBER := 1;
v_Error VARCHAR2(120);
v_ErrorOrder NUMBER := 1;
v_Handle call_stacks.object_handle%TYPE;
v_LineNum call_stacks.line_num%TYPE;
v_ObjectName call_stacks.object_name%TYPE;
v_Facility error_stacks.facility%TYPE;
v_ErrNum error_stacks.error_number%TYPE;
v_ErrMsg error_stacks.error_mesg%TYPE;
v_FirstErrNum errors.error_number%TYPE;
v_FirstErrMsg errors.error_mesg%TYPE;
BEGIN
-- Get the error sequence number.
SELECT error_seq.nextval
INTO v_SeqNum
FROM dual;
p_SeqNum := v_SeqNum;
-- Insert first part of header info. into the errors table
INSERT INTO errors
(module, seq_number, error_stack, call_stack, timestamp)
VALUES
(p_Module, v_SeqNum, v_ErrorStack, v_CallStack, SYSDATE);
/*
Unwind the error stack to get each error out by scanning the
error stack string. Start with the index at the beginning of
the string
*;
v_Index := 1;
/*
Loop through the string, finding each newline
A newline ends each error on the stack
*/
WHILE v_Index < LENGTH(v_ErrorStack) LOOP
-- v_End is the position of the newline.
v_End := INSTR(v_ErrorStack, v_NewLine, v_Index);
-- The error is between the current index and the newline
v_Error := SUBSTR(v_ErrorStack, v_Index, v_End - v_Index);
-- Skip over the current error, for the next iteration
v_Index := v_Index + LENGTH(v_Error) + 1;
/* An error looks like 'facility-number: mesg'. Get each
piece out for insertion. The facility is the first 3
characters of the error.
*/
v_Facility := SUBSTR(v_Error, 1, 3);
-- Remove the facility and the dash (always 4 characters)
v_Error := SUBSTR(v_Error, 5);
-- Next get the error number
v_ErrNum := TO_NUMBER(SUBSTR(v_Error, 1, INSTR(v_Error,
':') - 1));
-- Remove the error number, colon & space (always 7 chars)
v_Error := SUBSTR(v_Error, 8);
-- What's left is the error message
v_ErrMsg := v_Error;
/*
Insert the errors, and grab the first error number and
message while we're at it
*/
INSERT INTO error_stacks
(module, seq_number, error_order, facility, error_number,
error_mesg)
VALUES
(p_Module, p_SeqNum, v_ErrorOrder, v_Facility, v_ErrNum,
v_ErrMsg);
IF v_ErrorOrder = 1 THEN
v_FirstErrNum := v_ErrNum;
v_FirstErrMsg := v_Facility || '-' || TO_NUMBER(v_ErrNum)
|| ': ' || v_ErrMsg;
END IF;
v_ErrorOrder := v_ErrorOrder + 1;
END LOOP;
-- Update the errors table with the message and code
UPDATE errors
SET error_number = v_FirstErrNum,
error_mesg = v_FirstErrMsg
WHERE module = p_Module
AND seq_number = v_SeqNum;
/*
Unwind the call stack to get each call out by scanning the
call stack string. Start with the index after the first call
on the stack. This will be after the first occurrence of
'name' and the newline.
*/
v_Index := INSTR(v_CallStack, 'name') + 5;
/* Loop through the string, finding each newline. A newline
ends each call on the stack.
*/
WHILE v_Index < LENGTH(v_CallStack) LOOP
-- v_End is the position of the newline
v_End := INSTR(v_CallStack, v_NewLine, v_Index);
-- The call is between the current index and the newline
v_Call := SUBSTR(v_CallStack, v_Index, v_End - v_Index);
-- Skip over the current call, for the next iteration
v_Index := v_Index + LENGTH(v_Call) + 1;
/*
Within a call, we have the object handle, then the line
number, then the object name, separated by spaces. Separate
them out for insertion.
-- Trim white space from the call first.
*/
v_Call := TRIM(v_Call);
-- First get the object handle
v_Handle := SUBSTR(v_Call, 1, INSTR(v_Call, ' '));
-- Remove the object handle,then the white space
v_Call := SUBSTR(v_Call, LENGTH(v_Handle) + 1);
v_Call := TRIM(v_Call);
-- Get the line number
v_LineNum := TO_NUMBER(SUBSTR(v_Call,1,INSTR(v_Call,' ')));
-- Remove the line number, and white space
v_Call := SUBSTR(v_Call, LENGTH(v_LineNum) + 1);
v_Call := TRIM(v_Call);
-- What is left is the object name
v_ObjectName := v_Call;
-- Insert all calls except the call for ErrorPkg
IF v_CallOrder > 1 THEN
INSERT INTO call_stacks
(module, seq_number, call_order, object_handle, line_num,
object_name)
VALUES
(p_Module, v_SeqNum, v_CallOrder, v_Handle, v_LineNum,
v_ObjectName);
END IF;
v_Callorder := v_CallOrder + 1;
END LOOP;
IF p_CommitFlag THEN
COMMIT;
END IF;
END StoreStacks;
END ErrorPkg;
/
四 游标转化
DBMS_SQL.TO_CURSOR_NUMBER
DBMS_SQL.TO_REFCURSOR
这两个函数可以进行游标转换。
declare
l_rcursor sys_refcursor;
l_colCnt number;
l_descTbl dbms_sql.desc_tab;
begin
open l_rcursor for select * from all_users;
dbms_sql.describe_columns
( c => dbms_sql.to_cursor_number(l_rcursor),
col_cnt => l_colCnt,
desc_t => l_descTbl );
for i in 1 .. l_colCnt
loop
dbms_output.put_line( l_descTbl(i).col_name );
end loop;
end;
/
declare
l_cursor number := dbms_sql.open_cursor;
l_refcursor sys_refcursor;
l_data varchar2(30);
begin
dbms_sql.parse
( l_cursor,
'select username from all_users where rownum <= 5',
dbms_sql.native );
if ( dbms_sql.execute( l_cursor ) != 0 )
then
raise program_error;
end if;
l_refcursor := dbms_sql.to_refcursor(l_cursor);
loop
fetch l_refcursor into l_data;
exit when l_refcursor%notfound;
dbms_output.put_line( l_data );
end loop;
close l_refcursor;
end;
五 本地编译
本地编译比普通编译更慢而且不能调试，但运行速度更快。
alter procedure p compile
PLSQL_Warnings = 'enable:all'
PLSQL_Optimize_Level = 2
PLSQL_CCFlags = 'Simple:true'
PLSQL_Code_Type = interpreted
reuse settings
/
alter procedure p compile
PLSQL_Warnings = 'enable:all'
PLSQL_Optimize_Level = 2
PLSQL_CCFlags = 'Simple:false'
PLSQL_Code_Type = native
reuse settings
/
六 复合触发器：
create trigger t for update on t compound trigger
g_data varchar2(200) := 'begin state';
before statement is
begin
dbms_output.put_line( g_data );
dbms_output.put_line( 'before statement...' ); g_data := 'before statement, ';
end before statement;
before each row is
begin
dbms_output.put_line( 'before each row...' ); g_data := g_data || 'before each row, ';
end before each row;
after each row is
begin
dbms_output.put_line( 'after each row...' ); g_data := g_data || 'after each row, ';
end after each row;
after statement is
begin
dbms_output.put_line( 'after statement...' ); g_data := g_data || 'after statement';
dbms_output.put_line( g_data );
end after statement;
end;
七 Named and Mixed Notation in SQL
create or replace function f
( x in number default 1,
y in number default 2,
z in number default 3 )
return number
as
begin
return x+y+z;
end;
/
pause
clear screen
select f(4,5,6) from dual;
select f(10, z=>200) from dual;
select f(z=>10) from dual;
select f(y=>20,z=>10) from dual;
八 闪回数据归档
这个功能很强，如果有足够的归档闪回数据，你甚至可以闪回十年前的数据。
