—.内置函数的使用
1.日期函数
(1)sysdate(系统当前时间)
select sysdate from dual;
(2)将字符串类型的时间转为日期型的时间(日期连接符号必须保持一致)
select to_date('2022-2-11','yyyy-mm-dd') from dual;
select to_date('2022/2/11','yyyy/mm/dd') from dual;
(3)将日期变为字符类型(日期连接符号必须保持一致)
select to_char('2022-2-11','yyyy年yy月dd日') from dual;
(4)求两个月之后是多久
select add_months(sysdate,2) from dual;//求两个月之后的时间
(5)求当前年份/月份/天数extract(year/month/day from d1)
select extract(year from sysdate) from dual;//当前年份
select extract(month from sysdate) from dual;//当前月份
select extract(day from sysdate) from dual;//当前天数
(6)求过去时间到现在所经历的月份months_between(d1,d2)
SELECT MONTHS_BETWEEN(SYSDATE,('2020-1-1','YYYY-MM-DD')) FROM DUAL;
(7)取当前月份的最后一天 last_day(d1)
select last_day(sysdate) from dual;
(8)取下一周周五的时间 next_day(d1,'mon')
select next_day(SYSDATE,'星期五') from dual;
(10)日期中的四舍五入 round(d1)
select round(sysdate) from dual;
(11)日期中的取整 trunc(d1)
select trunc(SYSDATE) FROM DUAL;//不会四舍五入
2.字符函数
(1)字符截取 substr(s1,i1,i2)
SELECT SUBSTR('ABCD',1,2) FROM DUAL;//注意substr是从第一位开始截取
(2)长度 length(s1)
SELECT LENGTH('ABCD我') FROM DUAL;//注意有多少个字符就是多少
(3)查找 instr(s1,s2,i1)
SELECT INSTR('ABCD我B','B') FROM DUAL;//注意是按下标查询
(4)连接concat(s1,s2)//可以把两列连接起来
SELECT 'AA'||'BB' FROM DUAL;
(5)替换 replace(s1,s2,s3)
SELECT REPLACE('BABY','A','钝角') FROM DUAL;
(6)左填充 lpad(s1,i1,s2)
SELECT LPAD('1',5,'0') FROM DUAL;
(7)右填充 rpad(s1,i1,s2)
SELECT TRIM(' AA BB CC ') FROM DUAL;//注意是去除两边空格
3.数字函数
(1)四舍五入 round(i1,i2)
SELECT ROUND(1.89121,3) FROM DUAL;//保留三位小数
(2)取整 trunc(i1,i2)
SELECT trunc(1.89191,3) FROM DUAL;//注意不会四舍五入
(3)取模 mod(i1,i2)
SELECT MOD(10,3) FROM DUAL;//10%3=1
(4)向下取整 floor(i1)
SELECT floor(1.89191) FROM DUAL;//结果为1
(5)幂 power(i1,i2)
SELECT POWER(2,3) FROM DUAL;//2的3次方
(6)平方根 sqrt(i1)
SELECT sqrt(9.9) FROM DUAL;
(7)正数 sign(i1)//大于0为1,等于0为0,小于0为-1
SELECT SIGN(-100) FROM DUAL;//结果为-1
(8)绝对值 abs(i1)
select abs(-2) from dual;
(9) decode 判断decode(c1,a1,a2,b1,b2)//相当于java中的三元运算符
select DECODE(1,1,'AA',2,'BB',3,'CC')//结果为AA
(10)NVL(i1,0)//为空就是0
select nvl(i1,0) from dual;
二.PL-SQL的使用(pl-sql就相当于用数据库写java代码)
形式:declare -- 定义
i number := 0;//赋值
begin//开始
-- 写代码
-- 字符到数值的转换错误
DBMS_OUTPUT.PUT_LINE('值是' || i);-- syso//输出
end;//结束
基本语句:
(1)if条件
--if条件:
--查询7369的工资
--如果工资大于3500交税,=3500刚好,<3500努力
declare
my_sal emp.sal%type;//薪水列的类型结构
begin
select sal into my_sal from emp where EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE(my_sal);
if my_sal > 3500 then
DBMS_OUTPUT.PUT_LINE('交税');
elsif my_sal = 3500 then
DBMS_OUTPUT.PUT_LINE('刚好');
else
DBMS_OUTPUT.PUT_LINE('努力');
end if;
end;
(2)case(选择结构)
--查询7369员工对应的部门名称
-- 10:A 20:B 30:C
declare
my_dept emp.DEPTNO%type;
begin
select DEPTNO into my_dept from emp where EMPNO = 7369;
DBMS_OUTPUT.PUT_LINE(my_dept);
case my_dept
when 10 then DBMS_OUTPUT.PUT_LINE('A');
when 20 then DBMS_OUTPUT.PUT_LINE('B');
else DBMS_OUTPUT.PUT_LINE('C');
end case;
end;
(3)loop(死循环)循环结构
declare
i number := 0;
begin
loop
i := i + 1;
DBMS_OUTPUT.PUT_LINE('我好帅');
exit when i >= 100;//退出条件
end loop;
end;
(4) while循环
declare
i number := 0;
begin
while i < 100
loop
i := i + 1;
DBMS_OUTPUT.PUT_LINE(i);
end loop;
end;
(5)for循环
begin
for i in 1..100
loop
DBMS_OUTPUT.PUT_LINE(101 - i);
end loop;
end;
(6)异常的抓捕(相当于java中的抛异常)
declare
my_date emp.hiredate%type;-- 和列的类型保持一致
begin
select HIREDATE
into my_date
from emp
where EMPNO = 10000;
exception
when too_many_rows then DBMS_OUTPUT.PUT_LINE('行太多了,放不下啦');//超行数
when no_data_found then DBMS_OUTPUT.PUT_LINE('已失踪');//没有该数据
end;
--函数(oracle)的创建(函数就相当于java中的方法)必须具备返回值+执行函数
create or replace function test01 --名字
return number --返回类型
as
begin
return extract(month from sysdate);
end;
--执行函数
select test01()
from dual;
--
三.游标(cursor)(游标就相当于java中的结果集)
基本使用
-- 1.定义游标(在declare中)
-- cursor myc is select * from xx;
-- 2.开启游标(在begin中)
-- open myc;
-- 3.数据的处理工作(从游标中取出需要的数据)
-- fetch myc into xx;
-- 4.关闭游标
-- close myc;
游标的四种状态:
-- %found 游标中有数据
-- %notfound 游标中没有数据了
-- %rowcount 收到影响的行数
-- %isopen 游标是否打开
游标的分类:
--显式游标:自己定义的游标 myc myd mye
--隐式游标:隐式游标的名字都叫sql(增,删,改)
--动态游标
-- 游标在声明时没有设定,在打开时可以对其进行修改
-- 强类型游标 有return(只能单表查询)
-- type my_t is ref cursor return xx%rowtype;
-- 弱类型游标 无return(可以多表查询)
-- type c1 is ref cursor;
-- 动态游标无法使用for循环遍历
例子:
(1)利用循环来遍历游标
declare
cursor myc is select *
from emp;
my_row emp%rowtype;//定义游标
begin
--打开游标
open myc;
loop
--拿出数据
fetch myc into my_row;
exit when myc%notfound;//退出条件
DBMS_OUTPUT.PUT_LINE(my_row.ENAME || my_row.SAL);//输出语句
end loop;
--关闭游标
close myc;
end;
(2)带参数的游标
-- 用户输入一个部门编号,显示部门对应的人
--计算20部门的人的工资总和
declare
cursor myc(no number) is select * from emp where DEPTNO=no;
my_sum number:=0;
begin
for e in myc(no) loop
my_sum:=my_sum+e.SAL;
end loop;
DBMS_OUTPUT.PUT_LINE(my_sum);
end;
(3)隐式游标
--修改薪水小于2000的
begin
update 表名 set sal(薪水)=sal+1000 where sal<2000;
--出发了 增删改 就会有隐式游标 sql
DBMS_OUTPUT.PUT_LINE('加薪的人数:'||sql%rowcount)//受到影响的行;
end;
-- 删除表中的员工
-- 如果没有 则输出没有了
begin
delete from db_emp;
--触发了 增删改 就会有隐式游标 sql
if sql%rowcount=0 then
DBMS_OUTPUT.PUT_LINE('无了');
end if;
end;
(4)使用强类型游标查看emp(员工表)表的数据
declare
type myt is ref cursor return emp%rowtype; --声明了一个类型,游标中只能存放emp的行数据
mye myt;--定义游标
my_row emp%rowtype;//使用行类型
begin
open mye for select * from emp;--手动指定了sql语句(动态游标)
loop
fetch mye into my_row;//从游标中获取数据
exit when mye%notfound;//退出条件,游标中没有数据了
DBMS_OUTPUT.PUT_LINE(my_row.ENAME);
end loop;
close mye;
open mye for select * from emp where deptno=20;--手动指定了sql语句(动态游标)
loop
fetch mye into my_row;//从游标中获取数据
exit when mye%notfound;//退出条件,游标中没有数据了
DBMS_OUTPUT.PUT_LINE(my_row.DEPTNO);
end loop;
close mye;
end;
(5)-- 使用弱类型游标查看emp(员工表),dept(部门表)表的数据
declare
type myc is ref cursor;--弱类型就是说 这个游标可以为多表服务
myt myc;
mye emp%rowtype;
myd dept%rowtype;
begin
open myt for select * from emp;--手动指定了sql语句(动态游标)
loop
fetch myt into mye;
exit when myt%notfound;
DBMS_OUTPUT.PUT_LINE(mye.ENAME);
end loop;
close myt;
open myt for select * from dept;--手动指定了sql语句(动态游标)
loop
fetch myt into myd;
exit when myt%notfound;
DBMS_OUTPUT.PUT_LINE(myd.DNAME);
end loop;
close myt;
end;
四.触发器(trigger)的使用
1.什么时候调用触发器:
(1) 数据发生改变的时候(增删改)
(2) 系统自己调用
2. 触发器的分类:
before 前
after 后
3.触发器按类型:
行级触发器 for each row
每一行都会触发
语句级触发器 不写for each row
执行一次
4.触发器触发的时候会建立两个临时表:new old
增: new
删:old
改:old new
5.触发器顺序
before --先执行前触发
sql语句执行 --才会执行sql
after --才会后触发
触发器使用方法:
--1.要求李斯文不能删除
create or replace trigger tgr_01
after delete on stuinfo --定义触发的时间与绑定的表
for each row --行级触发器
begin
if :old.stuname='李斯文' then
raise_application_error(-20001,'不能删除该学生'); --抛出异常
end if;
end;
delete stuinfo where stuname='李斯文'//调用触发器
五.序列(sequence)的使用 //序列可以实现主键自增
--创建序列
create sequence seq_401
-- seq_401.nextval 下一个值
--seq_401.currval 当前的值
--5.触发器与序列的使用
create table tb_401(
sid number primary key,
sname varchar2(20)
);//建表
create or replace trigger tb_401_insert
before insert on tb_401
for each row
begin
--修改新增的数据
--新增数据里面的sid改成序列的值
-- :new.sid:=seq_401.nextval; //版本11的oracle oracle11的
select seq_401.nextval into :new.sid from dual;//版本10的orcal-- oracle10的
end;
--测试
insert into tb_401(sname) values('马云');