Oracle PL/SQLThese are my personal notes that I use as a quick help in my work.
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||
nvl(val1, value-if-val1-is-null)decode(col, test_result_1, result_1
[, test_result_2, result_2
, test_result_n,
result_n]
[, else_result] )
decode(sign(some_date-sysdate), 1, 'x', 0, 'y', -1, 'z')decode(value,'x',0)*ysubstr(a_string, 1, decode(value, 'x', length(a_string), 0))bitand ( a, b)decode ( bitand(3, 2), 2, 'x', '0') --> x (011 && 010 --> 010)
decode ( bitand(4, 2), 2, 'x', '0') --> 0 (100 && 010 --> 000)
decode ( bitand(6, 2), 2, 'x', '0') --> x (110 && 010 --> 010)
coalesce(expr1, expr2, expr3, ...)
VARCHAR2CHARRAW(n) LONG(n)LONG RAW(n)TO_CHAR ( date or number, 'format') to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') lpad, rpad(c1, n [, c2] ) ltrim, rtrim (c [,s])substr(c, m [,n] ) substrb(c, m [,n] ) replace (c, str [,repl_str] )select replace(replace('line1
line2',chr(13),'[13]'),chr(10),'[10]') from dual;
translate(c, from, to)select translate ( 'line1
line2', chr(9) || chr(10) || chr(13), ' ') from dual;TRANSLATE(UPPER(variance_time),
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'nnnnnnnnnncccccccccccccccccccccccccc')
length , lengthbnvl(length(ltrim(rtrim(the_string))), 0)asciichr(n [using nchar_cs]) concat (c, c) c || c initcap, nls_initcap instr ( string_in_which_to_search, sub_string_to_compare[, start_pos, nth_occurence])substr(a_string, 1, instr(a_string, '-')-1)
-- String up to but not including the first dashcase when instr(a_string, '-')=0 then a_string else substr(a_string, 1, instr(a_string, '-')-1) end
-- String up to but not including the first dash, and return the full string if there is no dashregexp_instr ( string_in_which_to_search, sub_string_to_compare[, start_pos, nth_occurence])instrblower, upper, nls_lower, nls_upper nlssortsoundexnls_charset_idnls_charset_nameEncode a URL in PL/SQL (9i)
encodedURL varchar2(100);
url_in varchar2(100);
charset varchar2(40); -- Valid Oracle character set name, NULL for database character set
...
encodedURL := UTL_URL.ESCAPE(url_in, FALSE, charset);
Use LOB columns (CLOB, NCLOB, BLOB). Do not use LONG: supported only for backward compatibility.
When copying table, use TO_LOB function:
CREATE TABLE new_table (col1, lob_col CLOB);
INSERT INTO new_table (select o.col1, TO_LOB(o.old_long_col) FROM old_table o);
abs(n )
acos(r), asin, atan, atan2,
cei
NUMBER(precision[, scale])trunc(n, m) BINARY_INTEGERDECIMAL, DECDOUBLE PRECISION, NUMERIC, REALINTEGER, INTSMALLINTFLOAT(precision)**absacos, asin, atan, atan2cos, sin, tan
cosh, sinh, tanh
ceil(x)floor(x)exp(x)
ln (x)
log ( n , x )mod (x,y)power (x, y)rount (x, n)sign(n)sqrt xtrunc(x, n)TO_NUMBER ( 'string', 'format')TO_CHAR ( 0.25, '00D99')to_number('1A', 'x') bin_to_num(1,0,0,1) --> 9Number format models
0 Leading or trailing zeros
9 digit, includes 0 if no integer part
B blank for integer part
C ISO currency symbol as defined by NLS_ISO_CURRENCY parameter
L Local currency, as defined by NLS_CURRENCY
D decimal character, according to NLS_NUMERIC_CHARACTER
G group separator, according to NLS_NUMERIC_CHARACTER parameter
1.23EEEE Scientific notation
1.23MI Trailing negative sign
1.23PR Negative number in angle brackets
S Plus or minus sign, can be leading or trailing
FM... Removes the leading blank (format modifier): "In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number" (taken from Oracle documentation)
Custom is_number function:
create or replace function IS_NUMBER(in_str in varchar2) return varchar2 IS
dummy number;
begin
dummy := TO_NUMBER(in_str);
return ('TRUE');
exception when others then
return ('FALSE');
end;
1 Jan 4712 BC to 31 Dec 4712 AD
sysdateadd_months(date, num_months)last_day(m)trunc( date , 'DD')TO_DATE ( 'string', 'format')SELECT (CAST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE) - TO_DATE('01/01/1970','DD/MM/YYYY')) * 24 * 60 * 60 FROM DUAL;
Date formats (to_char, to_date) |
|||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||
Timestamps
Timestamp store fractional seconds
Conversion: cast(ts as date) or cast(dt as timestamp)
Current time: SYSTIMESTAMP instead of SYSDATE
Difference in timestamps gives an interval, not the number of days
Default format in SQLPlus:
ALTER SESSION SET NLS_DATE_FORMAT ='YYYY/MM/DD HH24:MI'
ALTER SESSION SET NLS_TIMESTAMP_FORMAT ='YYYY/MM/DD HH24:MI:SS:FF3';
Note: the documentation says that TRUNC does not work with timestamps but I can do it. I guess it converts to date in later versions of Oracle
select sessiontimezone from dual;: gives offset
'US/Eastern', 'US/Central', 'America/Denver', '', '', '', '', '', '', '' in smaller timezone file
'Europe/Zurich', 'America/Guayaquil', 'America/Lima', '', '', '', '', '', '', '' not in smaller timezone file
select to_char( cast (sysdate as timestamp with time zone) at time zone 'GMT' , 'HH24:MI TZH:TZM') from dual;
select to_char( cast (sysdate as timestamp with time zone) at time zone 'America/New_York' , 'HH24:MI TZH:TZM') from dual;
Examples:
DD/MM/YYYY HH24:MI:SSsysdate + 7 - to_number(to_char(sysdate, 'D')) ---> Next Saturday trunc(sysdate, 'DD') + 21/24 ---> At 9PMto_char (2004, 'RM') ---> MMIV (note: RM not RN)
Determine elapsed time:
v_start NUMBER;
v_start := DBMS_UTILITY.get_time;
ROLLBACK; -- or whatever
DBMS_OUTPUT.put_line('Elapsed time: ' ||(DBMS_UTILITY.get_time - v_start)||' hsecs');
|
|
Exponentiation Negation |
+ - |
Unary operators |
* / |
|
+ - || |
|
= |
Equality Non-equality |
AND |
|
OR |
Unequal: <> != ~=
It is a good idea to TRUNC when comparing dates
String comparisons are case-sensitive. Be careful when comparing CHAR with VARCHAR.
Wildcards for LIKE: % and _
x [NOT] BETWEEN a AND b
x [NOT]
IN (a, b, c, ...)
x IS [NOT] NULL
CURSOR cursor_name IS
SELECT a_column, ...
FROM ...;
OPEN cursor_name (params);
FETCH cursor_name INTO a_variable;
CLOSE cursor_name;
FOR rec_cursor IN cursor_name LOOP
... rec_cursor.a_column ...
END LOOP;
Without explicit cursor:
SELECT a_column, ...
INTO a_variable
FROM ...;
Explicit cursor attributes: cursor_name%attribute
%isopen --> true/false.%found / %notfound --> true/false: no rows fetched (~EOF)%rowcount --> number of rows fetched, 0 before first fetchImplicit cursor attributes: sql%attribute
sql%isopen always false (the implicit cursor is always closed after execution)sql%found / sql%notfound --> true/false: rows returned or rows affected (in insert, update, delete)sql%rowcount --> number of rows returnedUpdate one table from another:
DECLARE
CURSOR update_table_cursor is
select <source_columns>, <columns_for_join>
from <source_table>
where <cond> ;
BEGIN
FOR r IN update_table_cursor LOOP
UPDATE <target_table> t
SET t.<target_column> = r.<source_column>,
t.<target_column> = r.<source_column>
WHERE t.<join_column> = r.<join_column> ;
END LOOP;
END;
/
Example
declare
type a_record_type is record (one_col VARCHAR2(20), another_col table.id%TYPE);
a_record a_record_type;
begin
select a, b into a_record from a_source_table where ...;
DBMS_OUTPUT.put_line('a = ' || a_record.one_col );
end;
declare
row_record employee%ROWTYPE;
begin
select * into row_record from a_table where ...;
row_record.a :='new value';
update a_table set row = row_record where ...;
end;
Three types of collections:
Nested table:
TYPE nested_table IS TABLE OF NUMBER ; -- declares object AND intializes it
a_nested_table nested_table := nested_table(1,3,5,6,7);
begin
a_nested_table := nested_table(); -- or initialize here
a_nested_table.EXTEND(2); -- extends with two more elements
TYPE tb_in_mem_type IS TABLE OF tb_records;
tb_in_mem tb_in_mem_type;
-- Open source cursor and do a bulk load
OPEN a_cursor(p_facility, p_begin_date, p_end_date);
FETCH a_cursor BULK COLLECT INTO tb_in_mem;
CLOSE a_cursor;
-- Now loop through the table in memory
FOR i IN tb_in_mem.FIRST .. tb_in_mem.LAST LOOP
do things with tb_in_mem(i).id, tb_in_mem(i).account_number, etc
END LOOP;
NULL; -- null statement
You need the last "/" after a PL/SQL block.
[ DECLARE
|
Anonymous block (remember the last "/") See an example below. |
CREATE [OR REPLACE] PROCEDURE [schema_name.]name (args) |
Procedure |
CREATE [OR REPLACE] FUNCTION [schema_name.]name (args) RETURN datatype |
Function |
CREATE [OR REPLACE] PACKAGE [schema_name.]package_name
|
Package |
CREATE [OR REPLACE] TYPE [schema_name.]object_type_name
|
Object |
DEFINER is default. CURRENT_USER to force evaluation of rights at run-time. If an invoker-rights routine is called, the current user is the session user. If this routing calls another that is invoker-rights, then the current user is still the session user. This happens until a definer-rights routine is called. In all sub-routines called from this routine, the owner of the routine is the current user.
args are parameters separated by commas:
parameter_name [ IN | OUT | IN OUT ]
parameter_type [ := value | DEFAULT value ]IN: the parameter is protected from being changed
OUT: write-only, the value passed to the routine is ignored
No parenthesis if there are no parameters (as opposed to java and others)
Do not include the precision ("varchar", not "varchar(200)")
IF cond
THEN -- No ";"
...
ELSIF cond THEN
...
ELSE
...
END IF;
Logical AND: left to right
FOR loop_index IN [REVERSE] a..b LOOP
-- space and ";" here
....
EXIT WHEN condition;
...
END LOOP;
loop_index is implicitely defined as INTEGER.
The EXIT statement is not the best programming practice. Use with caution.
Note that EXIT without WHEN is possible.
WHILE condition LOOP
-- space and ";" here
....
END LOOP;
Tip: if incrementing (or decrementing) a variable in the loop, any test in the condition should have a comparison and not equality.
LOOP
....
EXIT WHEN condition;
....
END LOOP;
FOR v_record IN (SELECT ... FROM ... WHERE ...) LOOP
INSERT INTO ... -- or whatever other commands
VALUES (v_record.a_field);
END LOOP;
GOTO label_name;
...
<<label_name>> --No ";" after the label
A statement must exist after the label
As usual,
comments about the horrors of using labels apply here too, even if the Oracle compiler imposes a few rules about jumping.
CASE
WHEN cond1 THEN expr1
WHEN cond2 THEN expr2
ELSE expr3
END
CASE expr
WHEN val1 THEN statement1;
WHEN val2 THEN statement2;
ELSE statement3;
END;
set serveroutput on [size {50000 | unlimited}]
set linesize 500
create or replace function look_for_bad_chars (a_text varchar2) return integer is
i integer;
r integer;
begin
r := 0;
for i in 1 .. length(a_text) loop
if ascii(substr(a_text, i, 1))>255 then
sys.dbms_output.put_line('Char number ' || to_char(ascii(substr(a_text, i, 1))));
r := 1;
end if;
end loop;
return r;
end;
/
show errors
PL/SQL function
CREATE OR REPLACE function test_string_out (P_MESS OUT VARCHAR2)
RETURN NUMBER IS
MESSAGE VARCHAR2(32767):=''; -- the maximum is 32767
N_iterations number := 200000;
begin
for i in 1 .. N_iterations loop
MESSAGE := substr(MESSAGE || 'a' || to_char(i) || CHR(13) || CHR(10),1,32767);
end loop;
P_MESS := MESSAGE ;
DBMS_OUTPUT.PUT_LINE('string out length: '||to_char(length(MESSAGE)));
RETURN -1;
END test_string_out;
/
Anonymous calling block:
set serveroutput on
declare
the_message varchar2(32767); -- this is the maximum
rtn number;
begin
rtn := test_string_out(the_message);
DBMS_OUTPUT.PUT_LINE('string out length: '||to_char(length(the_message)));
if rtn = 0 then
DBMS_OUTPUT.PUT_LINE('test_string_out succeed. '||to_char(sysdate,'Dy Mon Dd HH24:MI:SS YYYY'));
else
DBMS_OUTPUT.PUT_LINE('test_string_out failed. '||to_char(sysdate,'Dy Mon Dd HH24:MI:SS YYYY')||'.'||' The message is:');
DBMS_OUTPUT.PUT_LINE(the_message);
end if;
end;
/
Triggers can be fired before or after the SQL statement.
The trigger can be at row-level (once for each row) or at statement-level (once for each statement). Note that row-level triggers cannot query the table.
:old.column_name (null for insert)
:new.column_name
(null for delete)CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER}
{INSERT | DELETE | UPDATE [OF col1, col2]} [OR ...]
ON table_name
[[REFERENCING
[OLD AS old_alias] [NEW as new_alias] ] FOR EACH ROW [WHEN (condition)]]
DECLARE
...
BEGIN
...
END;
| Example |
|---|
CREATE OR REPLACE TRIGGER table_update |
Enable and disable:
ALTER TRIGGER trigger_name { ENABLE | DISABLE };
select SUBSTR ( OWNER, 1, 20) AS owner
, TRIGGER_NAME
, STATUS
, TRIGGER_TYPE
, SUBSTR ( TRIGGERING_EVENT, 1, 40) as triggering_event
, BASE_OBJECT_TYPE
, substr ( TABLE_OWNER || '.' || TABLE_NAME, 1, 40) as table_name
, substr ( COLUMN_NAME, 1, 40) as column_name
, ACTION_TYPE
, substr( referencing_names, 1, 50) as referencing_names
, substr ( when_clause, 1, 100) as when_clause
FROM DBA_TRIGGERS;
just some more arrangements are needed:
set long 50000
select the_text from (
select trigger_name, 1 as nn, 'CREATE OR REPLACE TRIGGER ' || DESCRIPTION as the_text FROM DBA_TRIGGERS
UNION
select trigger_name, 2, 'WHEN ' || when_clause FROM DBA_TRIGGERS WHEN when_clause is not null
UNION
select trigger_name, 3, TRIGGER_BODY FROM DBA_TRIGGERS
UNION
select trigger_name, 4, '/' FROM DBA_TRIGGERS
UNION
select trigger_name, 5, ' ' FROM DBA_TRIGGERS
) order by trigger_name, nn
;
Error stops processing of PL/SQL block.
DECLARE
error_code NUMBER;
error_msg VARCHAR2(250)
BEGIN
...
BEGIN -- optionally start a sub-block
....
EXCEPTION
WHEN exception_1 THEN
...
WHEN exception_2 THEN
NULL; -- Null statement
WHEN OTHERS THEN
error_code := SQLCODE -->
returns 0 (no exception), 1 (user-defined exception),
-1403 (no_data_found), -nnn (error code)
error_msg := SQLERRM --> error message
ROLLBACK;
END;
-- end of sub-block
... -- code executed even if an error occured
END; -- end of block
Minimal:
error_code NUMBER;
error_msg VARCHAR2(250)
EXCEPTION WHEN OTHERS THEN
BEGIN
error_code := SQLCODE;
error_msg := SQLERRM;
dbms_output.put_line ('Error code=' || to_char(error_code) || ' Error msg:"' || error_msg || '"');
ROLLBACK;
RAISE; -- Without this, the message "PL/SQL procedure successfully completed." shows even if errors occured
-- another option is to not raise, but write a successful completion message if all goes well.
-- This will help cover cases when dbms output is not showing: a message should show in both cases.
END;
In declarative portion:
my_exception EXCEPTION;
PRAGMA EXCEPTION_INIT (my_exception, error_number);
Later:
EXCEPTION
WHEN my_exception THEN
...
END;
my_exception EXCEPTION; -- Declare the name in declarative section
IF
... THEN
RAISE my_exception;
END IF;
EXCEPTION
WHEN my_exception THEN
...
END;
...
SAVEPOINT any_name
...
ROLLBACK to any_name;
...
PRAGMA restrict_references (function-name, WNDS, WNPS);
WNDS = Writes No Database State: does not modify database tables
WNPS = Writes No Package State: does not modify packaged variables
RNDS = Reads No Database State: does not query database tables
RNPS = Reads No Package State: does not reference package variables.
Example:
function cross_rate (OrigCurr varchar2, DisplayCurr varchar2) return number;
PRAGMA restrict_references (cross_rate, WNDS, WNPS);
PROCEDURE a_sub_prog IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
...
END;A subprogram with this pragma can commit or roll back SQL operations without committing or rolling back the data in the main transaction. Useful to log error messages in a sub-program and roll back the main data operation that created the error.
"exec procedure;" is equivalent to "begin procedure; end;"
DECLARE
cursor c_indexes is
select owner || '.' || index_name as the_index from dba_indexes
where owner not in ('SYS', 'SYSTEM')
and
index_type in ('BITMAP', 'NORMAL')
;
-- or: index_type <> 'IOT - TOP', or 'CLUSTER',
or 'LOB'
begin
for r in c_indexes loop
execute immediate ('alter index ' || r.the_index || ' rebuild logging');
end loop;
end;
/
Also:cursor_name integer;
rows_processed integer;
sql_string varchar2(500);
begin
sql_string := 'alter user ' || uid || ' identified by ' || pwd;
cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(cursor_name,sql_string,0);
rows_processed := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
(remember to do: set serveroutput on [size {50000 | unlimited}] )
dbms_output.put ('...');
dbms_output.put_line ('...');
Fill the record first then assign it to the table
DECLARE
l_budget_lines_in PA_BUDGET_PUB.budget_line_in_tbl_type;
l_budget_lines_in_rec PA_BUDGET_PUB.budget_line_in_rec_type;
BEGIN
FOR i IN 1..a LOOP
l_budget_lines_in_rec.pa_task_id :=function_of(i);
l_budget_lines_in_rec.resource_list_member_id:=another_function_of(i);
l_budget_lines_in_rec.raw_cost:=300;
l_budget_lines_in(i) := l_budget_lines_in_rec;
END LOOP;
END;
VARIABLE var_name type -- in sql*plus
:var_name := 1 ; -- in PL/SQL block
print var_name -- in sql*plus
BEGIN
...
END; --> execute immediately
/
BEGIN
...
END; --> store in buffer (see SQL*Plus option
.SET BLOCKTERMINATOR)