DESCRIBE可以用(只要用DESC即可)来显示表、视图的列的定义,也可以显示同义词、函数或存储过程的说明。语法如下:
DESC[RIBE] {[schema.]object[@connect_identifier]}
Schema:用户名,如果省去,则为对象的所有者。
object
可以是 表(table),视图(view),类型( type), 存储过程(procedure),函数( function), 包(package)或同义词( synonym)
@connect_identifier
数据库连接字串
例: 显示表emp结构
SQL> desc emp
名称 是否为空? 类型
----------------------------------------- -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
2. oracle中的instr()函數
INSTR(C1,C2,I,J)
在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
C1 被搜索的字符串
C2 希望搜索的字符串
I 搜索的开始位置,默认为1
J 出现的位置,默认为1 (C2在C1中第j次出现的位置)
SQL> select instr('oracle traning','ra',1,2) instring from dual;
INSTRING
---------
9
3.oracle中substr函数的用法
The syntax for the substr function is:
substr( string, start_position, [ length ] )
说明:
string is the source string.
start_position is the position for extraction. The first position in the string is always 1.
length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.
For example:
substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', 1, 4) would return 'Tech'
substr('TechOnTheNet', -3, 3) would return 'Net'
substr('TechOnTheNet', -6, 3) would return 'The'
substr('TechOnTheNet', -8, 2) would return 'On'
4.Oracle中Decode()函数使用技巧
DECODE函数是ORACLE PL/SQL是功能强大的函数之一,目前还只有ORACLE公司的SQL提供了此函数,其他数据库厂商的SQL实现还没有此功能。
DECODE有什么用途呢? 先构造一个例子,假设我们想给智星职员加工资,其标准是:工资在8000元以下的将加20%;工资在8000元以上的加15%
通常的做法是,先选出记录中的工资字段值? select salary into var-salary from employee,然后对变量var-salary用if-then-else或choose case之类的流控制语句进行判断。
如果用DECODE函数,那么我们就可以把这些流控制语句省略,通过SQL语句就可以直接完成。如下:select decode(sign(salary - 8000),1,salary*1.15,-1,salary*1.2,salary from employee 是不是很简洁?
DECODE的语法:DECODE(value,if1,then1,if2,then2,if3,then3,...,else),表示如果value等于if1时,DECODE函数的结果返回then1,...,如果不等于任何一个if值,则返回else。初看一下,DECODE 只能做等于测试,但刚才也看到了,我们通过一些函数或计算替代value,是可以使DECODE函数具备大于、小于或等于功能。
decode()函数使用技巧
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
使用方法:
1、比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
例如:
变量1=10,变量2=20
则sign(变量1-变量2)返回-1,decode解码结果为“变量1”,达到了取较小值的目的。
2、表、视图结构转化
现有一个商品销售表sale,表结构为:
month char(6) --月份
sell number(10,2) --月销售金额
现有数据为:
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300
想要转化为以下结构的数据:
year char(4) --年份
month1 number(10,2) --1月销售金额
month2 number(10,2) --2月销售金额
month3 number(10,2) --3月销售金额
month4 number(10,2) --4月销售金额
month5 number(10,2) --5月销售金额
month6 number(10,2) --6月销售金额
month7 number(10,2) --7月销售金额
month8 number(10,2) --8月销售金额
month9 number(10,2) --9月销售金额
month10 number(10,2) --10月销售金额
month11 number(10,2) --11月销售金额
month12 number(10,2) --12月销售金额
结构转化的SQL语句为:
create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),'01',sell,0)),
sum(decode(substrb(month,5,2),'02',sell,0)),
sum(decode(substrb(month,5,2),'03',sell,0)),
sum(decode(substrb(month,5,2),'04',sell,0)),
sum(decode(substrb(month,5,2),'05',sell,0)),
sum(decode(substrb(month,5,2),'06',sell,0)),
sum(decode(substrb(month,5,2),'07',sell,0)),
sum(decode(substrb(month,5,2),'08',sell,0)),
sum(decode(substrb(month,5,2),'09',sell,0)),
sum(decode(substrb(month,5,2),'10',sell,0)),
sum(decode(substrb(month,5,2),'11',sell,0)),
sum(decode(substrb(month,5,2),'12',sell,0))
from sale
group by substrb(month,1,4);
5. Oracle PL/SQL中如何使用%TYPE和%ROWTYPE
1. 使用%TYPE
在许多情况下,PL/SQL变量可以用来存储在数据库表中的数据。在这种情况下,变量应该拥有与表列相同的类型。例如,students表的first_name列的类型为VARCHAR2(20),我们可以按照下述方式声明一个变量:
DECLARE
v_FirstName VARCHAR2(20);
但是如果first_name列的定义改变了会发生什么(比如说表改变了,first_name现在的类型变为VARCHAR2(25))?那就会导致所有使用这个列的PL/SQL代码都必须进行修改。如果你有很多的PL/SQL代码,这种处理可能是十分耗时和容易出错的。
这时,你可以使用”%TYPE”属性而不是将变量类型硬性编码。
例如:
DECLARE
v_FirstName students.first_name%TYPE;
通过使用%TYPE,v_FirstName变量将同students表的first_name列的类型相同(可以理解为将两者邦定起来)。
每次匿名块或命名块运行该语句块以及编译存储对象(过程、函数、包、对象类和触发器)时,就会确定该类型。
使用%TYPE是非常好的编程风格,因为它使得PL/SQL更加灵活,更加适应于对数据库定义的更新。
2. 使用%ROWTYPE
2.1 PL/SQL记录
PL/SQL记录类型类似于C语言中的结构,是一种复合类型,是用户自定义的。
记录提供了一种处理独立的但又作为一个整体单元相关的变量的机制。请看:
DECLARE
v_StudentID NUMBER(5);
v_FirstName VARCHAR2(20);
v_LastName VARCHAR2(20);
这3个变量在逻辑上是相互关联的,因为他们指向students表中不同的字段。如果为这些变量声明一个记录类型,那么他们之间的关系就十分明显,可作为一个单元进行处理。
DECLARE
/*Define a record type to hold common student informationi*/
TYPE t_StudentRecord IS RECORD(
StudentID NUMBER(5),
FirstName VARCHAR2(20),
LastName VARCHAR2(20);
/*Declare a variable of this type.*/
v_StudentInfo t_StudentRecord;
2.2 记录赋值
可以用SELECT语句向记录赋值,这将会从数据库中检索数据并将该数据存储到记录中。注意的是,记录中字段应该和查询结果列表中的字段相匹配。
SELECT studentID,firstName,lastName
into v_StudentInfo
from students where studentID=32;
2.3 使用%ROWTYPE
在PL/SQL中将一个记录声明为具有相同类型的数据库行的作法是很常见的。PL/SQL提供了%ROWTYPE运算符,使得这样的操作更为方便。
例如:
DECLARE
v_RoomRecord rooms%ROWTYPE;
将定义一个记录,该记录中的字段将与rooms表中的列相对应。