建站技术网专注分享网站建设技术,网站建设教程,真正帮你学会做网站技术,轻松解决网站建设过程中遇到的常见问题,为广大站长服务。 收藏本站 | 订阅
域名投资,就来找米网,认准网址:www.zhaomi.cc 专业仿站,就到仿站网:www.fangzhan.org
  站内公告: ·帝国cms教程内容已更新,欢迎关注     ·建站技术网采用阿里云服务器,备案已通    
您当前的位置:首页 > 数据库教程 > Oracle教程

oracle教程:PLSQL常用方法汇总

时间:2018-02-02 13:06:39  来源:网络收集  整理: 建站技术网 www.jzjsw.com     点击:6次

在SQLPLUS下,实现中-英字符集转换

alter session set nls_language='AMERICAN';

alter session set nls_language='SIMPLIFIED CHINESE'; 

主要知识点:

一、有关表的操作

1)建表


create table test as select * from dept; --从已知表复制数据和结构

create table test as select * from dept where 1=2; --从已知表复制结构但不包括数据

2)插入数据:

insert into test select * from dept;



二、运算符

算术运算符:+ - * / 可以在select 语句中使用

连接运算符:|| select deptno|| dname from dept; 

比较运算符:> >= = != < <= like between is null in

逻辑运算符:not and or 

集合运算符: intersect ,union, union all, minus 

要求:对应集合的列数和数据类型相同

查询中不能包含long 列

列的标签是第一个集合的标签

使用order by时,必须使用位置序号,不能使用列名

例:集合运算符的使用:

intersect ,union, union all, minus 

select * from emp intersect select * from emp where deptno=10 ;

select * from emp minus select * from emp where deptno=10;

select * from emp where deptno=10 union select * from emp where deptno in (10,20); --不包括重复行 

select * from emp where deptno=10 union all select * from emp where deptno in (10,20); --包括重复行

三,常用 ORACLE 函数

sysdate为系统日期 dual为虚表

一)日期函数[重点掌握前四个日期函数]

1,add_months[返回日期加(减)指定月份后(前)的日期]

select sysdate S1,add_months(sysdate,10) S2,

add_months(sysdate,5) S3 from dual;

2,last_day [返回该月最后一天的日期] 

select last_day(sysdate) from dual; 

3,months_between[返回日期之间的月份数]

select sysdate S1, months_between('1-4月-04',sysdate) S2,

months_between('1-4月-04','1-2月-04') S3 from dual

4,next_day(d,day): 返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日 

select sysdate S1,next_day(sysdate,1) S2,

next_day(sysdate,'星期日') S3 FROM DUAL 

5,round[舍入到最接近的日期](day:舍入到最接近的星期日)

select sysdate S1,

round(sysdate) S2 ,

round(sysdate,'year') YEAR,

round(sysdate,'month') MONTH ,

round(sysdate,'day') DAY from dual

6,trunc[截断到最接近的日期] 

select sysdate S1,

trunc(sysdate) S2,

trunc(sysdate,'year') YEAR,

trunc(sysdate,'month') MONTH ,

trunc(sysdate,'day') DAY from dual

7,返回日期列表中最晚日期

select greatest('01-1月-04','04-1月-04','10-2月-04') from dual

二)字符函数(可用于字面字符或数据库列)

1,字符串截取

select substr('abcdef',1,3) from dual

2,查找子串位置

select instr('abcfdgfdhd','fd') from dual

3,字符串连接

select 'HELLO'||'hello world' from dual;

4, 1)去掉字符串中的空格

select ltrim(' abc') s1,

rtrim('zhang ') s2,

trim(' zhang ') s3 from dual

2)去掉前导和后缀

select trim(leading 9 from 9998767999) s1,

trim(trailing 9 from 9998767999) s2,

trim(9 from 9998767999) s3 from dual;

5,返回字符串首字母的Ascii值

select ascii('a') from dual

6,返回ascii值对应的字母

select chr(97) from dual

7,计算字符串长度 

select length('abcdef') from dual

8,initcap(首字母变大写) ,lower(变小写),upper(变大写)

select lower('ABC') s1,

upper('def') s2, 

initcap('efg') s3 from dual; 

9,Replace

select replace('abc','b','xy') from dual;

10,translate

select translate('abc','b','xx') from dual; -- x是1位

11,lpad [左添充] rpad [右填充](用于控制输出格式)

select lpad('func',15,'=') s1, rpad('func',15,'-') s2 from dual;

select lpad(dname,14,'=') from dept;

12, decode[实现if ..then 逻辑]

select deptno,decode(deptno,10,'1',20,'2',30,'3','其他') from dept;

三)数字函数

1,取整函数(ceil 向上取整,floor 向下取整)

select ceil(66.6) N1,floor(66.6) N2 from dual;

2, 取幂(power) 和 求平方根(sqrt)

select power(3,2) N1,sqrt(9) N2 from dual;

3,求余

select mod(9,5) from dual;

4,返回固定小数位数 (round:四舍五入,trunc:直接截断)

select round(66.667,2) N1,trunc(66.667,2) N2 from dual; 

5,返回值的符号(正数返回为1,负数为-1)

select sign(-32),sign(293) from dual;

四)转换函数

1,to_char()[将日期和数字类型转换成字符类型]

1) select to_char(sysdate) s1,

to_char(sysdate,'yyyy-mm-dd') s2,

to_char(sysdate,'yyyy') s3,

to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,


to_char(sysdate, 'hh24:mi:ss') s5,

to_char(sysdate,'DAY') s6 from dual;

2) select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2 from emp

2, to_date()[将字符类型转换为日期类型] 

insert into emp(empno,hiredate) values(8000,to_date('2004-10-10','yyyy-mm-dd'));

3, to_number() 转换为数字类型 

select to_number(to_char(sysdate,'hh12')) from dual; //以数字显示的小时数

五)其他函数

user: 

返回登录的用户名称 

select user from dual; 

vsize: 

返回表达式所需的字节数

select vsize('HELLO') from dual;

nvl(ex1,ex2):  

ex1值为空则返回ex2,否则返回该值本身ex1(常用) 

例:如果雇员没有佣金,将显示0,否则显示佣金 

select comm,nvl(comm,0) from emp;

nullif(ex1,ex2): 

值相等返空,否则返回第一个值

例:如果工资和佣金相等,则显示空,否则显示工资

select nullif(sal,comm),sal,comm from emp;

coalesce:  

返回列表中第一个非空表达式

select comm,sal,coalesce(comm,sal,sal*10) from emp;

   nvl2(ex1,ex2,ex3) :

如果ex1不为空,显示ex2,否则显示ex3

如:查看有佣金的雇员姓名以及他们的佣金 

     select nvl2(comm,ename,') as HaveCommName,comm from emp;

六)分组函数

max min avg count sum

1,整个结果集是一个组

1) 求部门30 的最高工资,最低工资,平均工资,总人数,有工作的人数,工种数量及工资总和

select max(ename),max(sal), 

min(ename),min(sal),

avg(sal),

count(*) ,count(job),count(distinct(job)) ,

sum(sal) from emp where deptno=30;

2, 带group by 和 having 的分组

1)按部门分组求最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和

select deptno, max(ename),max(sal),

min(ename),min(sal),

avg(sal),

count(*) ,count(job),count(distinct(job)) ,

sum(sal) from emp group by deptno;

2)部门30的最高工资,最低工资,总人数,有工作的人数,工种数量及工资总和 

select deptno, max(ename),max(sal),

min(ename),min(sal),

avg(sal),

count(*) ,count(job),count(distinct(job)) ,

sum(sal) from emp group by deptno having deptno=30;

3, stddev 返回一组值的标准偏差

select deptno,stddev(sal) from emp group by deptno;

variance 返回一组值的方差差

select deptno,variance(sal) from emp group by deptno;

4, 带有rollup和cube操作符的Group By

rollup 按分组的第一个列进行统计和最后的小计

cube 按分组的所有列的进行统计和最后的小计

select deptno,job ,sum(sal) from emp group by deptno,job;

select deptno,job ,sum(sal) from emp group by rollup(deptno,job); 

cube 产生组内所有列的统计和最后的小计

select deptno,job ,sum(sal) from emp group by cube(deptno,job);

七、临时表

只在会话期间或在事务处理期间存在的表.

临时表在插入数据时,动态分配空间 

create global temporary table temp_dept

(dno number,

dname varchar2(10))

on commit delete rows;

insert into temp_dept values(10,'ABC');

commit;

select * from temp_dept; --无数据显示,数据自动清除

on commit preserve rows:在会话期间表一直可以存在(保留数据)

on commit delete rows:事务结束清除数据(在事务结束时自动删除表的数据)











========================== 连接操作 ================================ 


例如有A,B两张表: 


A表            B表 

a  b         c  d 

1  0         4  7 

2  9         1  5 



select * from A,B where A.a = B.c 

等同于 

select * from A join B on A.a = B.c 

结果: 

a b c d 

1 0 1 5 


左连接: 

select * from A,B where A.a = B.c(+) 

等同于 

select * from A left join B on A.a = B.c 

即以A表查询为主,附带查询出满足A.a = B.c条件的B表中的结果 

结果: 

a b c d 

1 0 1 5 

2 9 



右连接: 

select * from A,B where A.a(+) = B.c 

等同于 

select * from A right join B on A.a = B.c 

此查询为左连接,即以A表查询为主,附带查询出满足A.a = B.c条件的B表中的结果 

结果: 

c d a b 

1 5 1 0 

4 7 


=============================================================== 



===================== oracle 函数 =============================== 

DECODE: 

decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) 


A表 

a  b  c 

1  3  0 

3  2  9 

1  0  5 

3  7  7 

5  5  9 


select decode(A.a,1,4,3,8),A.b,A.c form A 

意思:如果A.a的值为1,则将A.a的值查询为4,如果A.a的值为3,则将A.a的值查询为8 

结果: 

a b c 

4 3 0 

8 2 9 

4 0 5 

8 7 7 

5 5 9 


select decode(A.a,1,4,8),A.b,A.c form A 

意思:如果A.a的值为1,则将A.a的值查询为4,否则A.a的值查询为8 

结果: 

a  b  c 

4  3  0 

8  2  9 

4  0  5 

8  7  7 

8  5  9 


select decode(A.a,1,4,3,8,7),A.b,A.c form A 

意思:如果A.a的值为1,则将A.a的值查询为4,如果A.a的值为3,则将A.a的值查询为8,否则查询为7 

结果: 

a b c 

4 3 0 

8 2 9 

4 0 5 

8 7 7 

7 5 9 

=============================================================== 



==========================SEQUENCE===================================== 


SEQUENCE: CREATE   SEQUENCE   LOCATIONS_SEQ  

          INCREMENT   BY   1  

          START   WITH   1  

          MAXVALUE   9900  

          MINVALUE   1  


SELECT LOCATIONS_SEQ.currval FROM DUAL ; 

SELECT LOCATIONS_SEQ.nextval FROM DUAL ; 


============================SEQUENCE=================================== 




============================函数=================================== 

日期函数: 

add_months(d,n)  日期d加n个月 

last_day(d)  包含d的月份的最后一天的日期 

month_between(d,e) 日期d与e之间的月份数,e先于d 

new_time(d,a,b)  a时区的日期和时间d在b时区的日期和时间 

next_day(d,day)  比日期d晚,由day指定的周几的日期 

sysdate  当前的系统日期和时间 

greatest(d1,d2,...dn) 给出的日期列表中最后的日期 

least(d1,k2,...dn) 给出的日期列表中最早的日期 

to_char(d [,fmt]) 日期d按fmt指定的格式转变成字符串 

to_date(st [,fmt]) 字符串st按fmt指定的格式转成日期值,若fmt忽略,st要用缺省格式 

round(d [,fmt])  日期d按fmt指定格式舍入到最近的日期 

trunc(d [,fmt])  日期d按fmt指定格式截断到最近的日期 


分组函数: 

avg([distinct/all] n) 列n的平均值 

count([all] *)  返回查询范围内的行数包括重复值和空值 

count([distinct/all] n) 非空值的行数 

max([distinct/all] n) 该列或表达式的最大值 

min([distinct/all] n) 该列或表达式的最小值 

stdev([distinct/all] n) 该列或表达式的标准偏差,忽略空值 

sum([distinct/all] n) 该列或表达式的总和 

variance([distinct/all] n) 该列或表达式的方差,忽略空值 


数值函数: 

abs(m)  m的绝对值 

mod(m,n) m被n除后的余数 

power(m,n) m的n次方 

round(m[,n]) m四舍五入至小数点后n位的值(n缺省为0) 

trunc(m[,n]) m截断n位小数位的值(n缺省为0) 


字符函数: 

initcap(st) 返回st将每个单词的首字母大写,所有其他字母小写 

lower(st) 返回st将每个单词的字母全部小写 

upper(st) 返回st将每个单词的字母全部大写 

concat(st1,st2)  返回st为st2接st1的末尾(可用操作符"||") 

lpad(st1,n[,st2]) 返回右对齐的st,st为在st1的左边用st2填充直至长度为n,st2的缺省为空格 

rpad(st1,n[,st2]) 返回左对齐的st,st为在st1的右边用st2填充直至长度为n,st2的缺省为空格 

ltrim(st[,set])  返回st,st为从左边删除set中字符直到第一个不是set中的字符。缺省时,指的是空格 

rtrim(st[,set])  返回st,st为从右边删除set中字符直到第一个不是set中的字符。缺省时,指的是空格 

replace(st,search_st[,replace_st]) 将每次在st中出现的search_st用replace_st替换,返回一个st。缺省时,删除search_st 

substr(st,m[,n]) n=返回st串的子串,从m位置开始,取n个字符长。缺省时,一直返回到st末端 

length(st) 数值,返回st中的字符数 

instr(st1,st2[,m[,n]])  数值,返回st1从第m字符开始,st2第n次出现的位置,m及n的缺省值为1 


文章标题: oracle教程:PLSQL常用方法汇总
文章网址:http://www.jzjsw.com/shujuku/Oracle/3785.html
上一篇:非常好的Oracle基础教程
下一篇:oracle基础教程(第一天)
来顶一下
返回首页
返回首页
相关文章
    无相关信息
推荐资讯
如何通过SEO优化排名赚钱?SEO赚钱方法
如何通过SEO优化排名赚
seo优化教程:网站seo内容优化
seo优化教程:网站seo
SEO细节:企业SEO如何快速为新站做出效果
SEO细节:企业SEO如何
电商技术将如何发展?2018年有这五个大胆预测
电商技术将如何发展?
最新文章
栏目更新
栏目热门