Oracle PL SQL :TIPS

//z 11/9/2011 2:49 PM @is2120@csdn
2. ORACLE 中查看和修改存储过程
2.1
select text from user_source where name='PROCEDURE' order by line;
2.2
select text from all_source where owner='USERNAME' and type='PROCEDURE' and name='PROCEDURENAME';
2.3
set heading off
set echo off
set feedback off
set pages off
set long 90000
SEELCT DBMS_METADATA.GET_DDL( 'PROCEDURE ', 'YOURPROCEDURENAME ') FROM USER_OBJECTS WHERE OJBECT_TYPE= 'PROCEDURE ' AND OBJECT_NAME= 'YOURPROCEDURENAME ';


1. 空字符串和null value的区别 zz
在面向sql server和oracle的代码中因为加了Column<>''导致返回的结果不一致，经查询才知道oracle对空字符串("")做了特殊处理；应该是常识，
记录一下。

Oracle/PLSQL:Difference between an empty string and a null value

---

Question: What is the differencebetween an "empty" value and a "null" value? When Iselect those fields that are "empty" versus "null", I gettwo different result sets.

Answer: An empty string is treatedas a null value in Oracle. Let's demonstrate.

We've created a table calledsuppliers with the following table definition:

create table suppliers
( supplier_id	number,
supplier_name	varchar2(100));

Next, we'll insert two records intothis table.

insert into suppliers (supplier_id,supplier_name )
values ( 10565, null );

insert into suppliers (supplier_id,supplier_name )
values ( 10567, '' );

The first statement inserts a recordwith a supplier_name that is null, while the second statement inserts arecord with an empty string as asupplier_name.

Now, let's retrieve all rows with asupplier_name that is an empty string value as follows:

select * from suppliers
where supplier_name = '';

When you run this statement, you'dexpect to retrieve the row that you inserted above. But instead, this statementwill not retrieve any records at all.

Now, try retrieving all supplier_idsthat contain a null value:

select * from suppliers
where supplier_name is null;

When you run this statement, youwill retrieve both rows. This is because Oracle has now changed its rules sothat empty strings behave as null values.

It is also important to note thatthe null value is unique in that you can not use the usual operands (=,<, >, etc) on a null value. Instead, you must use theIS NULL and IS NOT NULLconditions.

Acknowledgements: We'd like to thank Charles Rothfor contributing to this solution!

c# sql server oracle 空字符串 String.Empty "" '' <> null