98. with as
可以把使用频繁的sql查询 抽取出来, 将查询结果作为一个临时的表
相当于虚拟视图
用法:
with tempName as (select .....)
针对多个别名:
with
t1 as (select ...),
t2 as (select ...),
....
99. lag, lead函数
lag 用法: 查询这个字段前面offset条记录对应的值,默认为1,如果查不到记录结果为空,则用默认值defaultVl,defaultVal缺省记为null(如果原来的值就是空,那结果就是空)
select col1, col2, ..., lag(coln,offset, defaultVal) over(order by columnName) from table tableName where .....;
在where过滤的查询结果中, 查询 col1,col2,...,coln的第前offset个值(按columnName排序)
lead用法: 查询这个字段后面 后面offset条记录对应字段的值,默认为1,如果查不到记录结果为空,则用默认defaultVal,defaultVal缺省记为null(如果原来的值就是空,那结果就是空)
select col1, col2, ..., lead(coln,offset, defaultVal) over(order by columnName) from table tableName where .....;
在where过滤的查询结果中, 查询 col1,col2,...,coln的第后offset个值(按columnName排序)
100.trunc 对日期,数值进行截取
案例:
select sysdate from dual; --返回当前日期 精确到时分秒
select trunc(sysdate) from dual; --返回当前日期 精确到天 同 select trunc(sysdate,'dd') from dual
select trunc(sysdate, 'yyyy') from dual; --返回当年的第一天 日期精确到天
select trunc(sysdate, 'mm') from dual; --返回这个月的第一天 精确到天
select trunc(sysdate, 'd') from dual;--返回这个星期的第一天 (周日) 精确到天
select trunc(sysdate, 'hh') from dual; --返回当前日期 精确到小时
select trunc(666.666) from dual; ---666
select trunc(666.666, 0) from dual; ---666
select trunc(666.666,1) from dual; --666.6
select trunc(666.666, -1) from dual; --660
101.
create table tableName as select * tableName1;
insert into tableName select * from tableName1;
建立索引：
create index indexName on tableName(columnName1,columnName2....)
drop index indexName
建立序列：
create sequence seq;
insert into tableName (seq.nextval,......);
102. start with connect by
语法:
select * from tablename
start with 条件一
connect by 条件二
从第条件一条开始,根据条件二(向上或向下取决于prior放在哪)递归查询
有node表 字段为 id, no(编号), name(目录名),pno(上级编号)
select * from node
start with no = 3
connect by prior no = pno 从no为3开始查起 查询所有上一条记录的no 等当前记录的pno的记录 从上往下查询
select * from node
start with no = 3
connect by no = prior pno 从no为3开始查起 查询所有上一条记录的pno 等当前记录的no的记录 从下往上查询
103. execute immediate 动态执行sql
语法 execute immediate sqlString
into var1,var2... --将动态sql的结果传入var1...中
using para1,para2... --给动态sql传参
returning into v1,v2... --如果sqlString中有returning into 语句
主要用于 begin ...end中
sqlString := 'create table t1 (col1 number, col2 varchar2(20))';
execute immediate sqlString;
动态的创建表,原本t1是不存在的
sqlString := 'insert into t1(:d1, :d2)'; --使用占位符
execute immediate sqlString
using 10, 'zhangsan';
动态传入参数值, 也可以是变量
sqlString := 'update t1 set col1=:d1 where col2=:d2 returning col1 into :r1';
execute immediate sqlString returning into r1;
r1是变量
104. merge into using语法
merge into tablename as A
using [sql结果] as B
on [condition expression]
where matched then
[update sql]
where not matched then
[insert sql]
根据sql结果 通过 on 条件与A表进行匹配 如果匹配成功 则将sql结果(可选择其中某些字段)更新到A表中
如果不匹配 则将sql结果插入到A表中
where matched then
[update sql] --可以省略
where not matched then
[insert sql] --可以省略
如果想全部插入 on条件可改为 1=0
105.oracle 使用别名时 如果存在空格时 必须使用双引号
select lastname as "姓 名" from emp;
106. null值在排序中,默认是最大的
107.单行函数:
upper('xxx') 大写 lower('xxx') 小写 initcap('xxx')首字母大写
length('xxx') 长度 stbstr(v1,s1,n1) 从v1的s1位置开始截取,截取n1个字符
concat(v1,v2) 将v1,v2进行连接 ,类似于||
replace(v1,c1,c2) 将v1中的c1用c2代替
nvl(v1,d1) 如果v1为空取默认值d1
round(v1,n1)四舍五入保留小数点后n1位
trunc(v1,n1)保留小数点后n1位,直接截取
to_char(c1,c2) 将数值型转为c2格式的字符串 to_char(c1) 将c1转为字符串
add_months(d,n) 给日期d加减n个月
108.日期转字符串
to_char(date,'pattern');
yyyy:年
mm:月
dd:日
hh:小时
mi:分钟
ss:秒
day: 星期几，如星期一
month: 几月，如12月
109.drop table t1 cascade constraint;
删表的同时删除与该表相关的约束,比如外键约束
110.增加约束，删除约束 该写法非空约束除外
alter table tname add constraint cname ctype(columnname); --primary key (column..),foreign key (column..) references..,unique(column..).....
alter table tname drop constraint cname;
111.序列
create sequence seqname ; --省略相关属性
案例：
create SEQUENCE seq_stu
INCREMENT BY 2 MAXVALUE 5000 CYCLE;
112.伪列 rownum
rownum 作为过滤条件只能是 <,<=，between and 等 不能单使用>
反例：select rownum,s.* from student s where rownum >2; --查询结果始终为空
正例：select * from (select rownum r, s.* from student s) s1 where r>2; --查询结果有数据
113.存储过程输出
如果是命令窗口 比如cmd形式的
set serveroutput on;
begin dbms_output.put_line('hello world');
end;
/
一定要先set serveroutput on; 开启数据库输出，否则看不到输出信息； / 程序结束符号
114.plsql
declare
...
begin
...
end;
=用于判断 :=用于赋值 || 用于连接
115.常量 VS 非空变量
declare
name varchar2(100) not null :='xx';
salary constant int := '9999';
begin
name := '欣淡定';
dbms_output.put_line(name||'今年加'||salary||'薪');
end;
都必须赋初始值； 常量赋值后不能在修改，非空变量可以修改但不能改为空
116.type,recode，rowtype的使用
type: 声明某个变量的类型
record: 定义一组数据，相当于一行
rowtype: 声明为某个表一行的类型
案例：
declare
type sinfo is record(
no student.id%type,
name student.
sname%type );
stu sinfo; s1 student%rowtype;
begin
stu.no := 1001;
stu.name := '欣淡定';
dbms_output.put_line('编号'||stu.no);
dbms_output.put_line('姓名'||stu.name);
dbms_output.put_line('---------------------------');
select * into s1 from student where id = 1;
dbms_output.put_line('编号'||s1.id);
dbms_output.put_line('姓名'||s1.sname);
end;
117.plsql嵌套
<<wai>>
declare
val int := 10;
begin
<<wai1>>
declare
val int := 20;
begin
dbms_output.put_line(val); --20
dbms_output.put_line(wai.val); --10
dbms_output.put_line(wai1.val); --20
end;
end;
118.if结构
declare
v1 int;
begin
v1 := 11;
if(v1>10) then
dbms_output.put_line('工资'||v1||'+');
elsif v1 < 10 then
dbms_output.put_line('房子数量'||v1||'-');
else
dbms_output.put_line('豪车数量'||v1);
end if;
end;
119.循环
- loop循环
declare
tcount int :=1;
begin
loop
dbms_output.put_line('知识点' || tcount);
exit when (tcount=10); --退出条件
tcount := tcount + 1;
end loop;
end;
- while循环
declare
friend varchar(100) := 'monkey';
num int;
begin
num := 1;
while num < 10 loop
dbms_output.put_line(friend || num);
num := num + 1;
end loop;
end;
- for循环
declare
total int := 100;
begin
for money in 1..total loop
dbms_output.put_line('某付宝到账'|| money || '元');
end loop;
end;
--for money in reverse 1..total 从后往前输
120.退出嵌套循环
exit: 默认退出当前的整个循环，可以使用标签 退出标签所在的整个 循环
continue： 默认退出 本次循环， 可以使用标签 退出标签所在循环的本次循环
案例：
declare
x int :=10;
y int := 1;
begin
<<label1>>
while x >=0 loop
while y<=10 loop
dbms_output.put_line('坐标（'||x||','||y||')');
if y =2 then
exit label1; --退出指定的循环
end if;
y := y+1;
end loop;
y := 1;
x := x-1;
end loop;
end;
121.goto
可以跳转到指定标签的位置，但是标签不可以是最后一条语句。解决办法，在末尾加个null语句
案例：
begin
dbms_output.put_line('1');
dbms_output.put_line('2');
goto label1;
dbms_output.put_line('3');
dbms_output.put_line('4');
<<label1>>
null;
end;
122.游标的使用
游标属性：%rowtype,%found,%notfound;
使用%found,%notfound时 一定要先移动游标，也就是使用fetch一下
案例：
declare
cursor mycursor is select sname from student; --声明游标
sname student.sname%type;
begin
open mycursor; --打开游标
for i in 1..3 --取前三个 loop
fetch mycursor into sname; --游标取数
dbms_output.put_line('学生姓名'||sname);
end loop;
close mycursor; --关闭游标
end;
游标属性的使用：
declare
cursor mycursor is select sname from student;
sname student.sname%type;
begin
open mycursor;
loop fetch mycursor into sname; --移动游标
exit when mycursor%notfound;
dbms_output.put_line('学生姓名'||sname);
end loop;
close mycursor;
end;
for循环使用游标，自动打开/关闭游标
declare
cursor mycursor is select id,sname from student;
begin
for c in mycursor loop
dbms_output.put_line('学生编号'||c.id||'学生姓名'||c.sname);
end loop;
end;
- 参数游标的使用
declare
cursor mycursor(myid student.id%type) is select * from student where id=myid;
rowStu mycursor%rowtype;
myid student.id%type;
begin
myid := &请输入学生编号; --用户输入
open mycursor(myid);
fetch mycursor into rowStu;
if mycursor%found then
dbms_output.put_line('学生姓名：' || rowStu.sname);
else
dbms_output.put_line('查无此人');
end if;
end;
123.异常处理
格式：
exception
when 异常类型 then
。。。。
when 异常类型 then
。。。。
when others then
。。。。
案例：
declare
sname student.sname%type;
begin
select sname into sname from student where id = -1;
dbms_output.put_line(sname);
exception when no_data_found then
dbms_output.put_line('查无此人');
when others then
dbms_output.put_line('未知异常');
end;
124.存储过程
格式：
create or replace procedure pname(参数列表) --参数形式 in 默认,out, in out
as
变量声明区
begin
代码区
end;
调用：
1）declare
begin
pname(参数);
end;
2）exec pname(参数);
3）call pname(参数);
125.函数
格式
create or replace function fname(参数列表) return 参数类型
as
变量声明区
begin
代码区
end;
调用：
1.select fname(参数列表) from dual;
2.declare
begin
变量 := fname(参数列表);
end;
126.oracle定时任务
案例：
--建表
create table fruit( id int primary key, fname varchar2(100) )
--创建序列
create sequence seq_fruit;
--创建存储过程
create or replace procedure myp3
as
begin
insert into fruit(id, fname) values(seq_fruit.nextVal, 'x');
end;
declare job_id int;
begin
--提交任务
dbms_job.submit(job_id, 'myp3();', sysdate, 'sysdate + (5/(24*60*60))');
--运行任务
dbms_job.run(job_id);
end;
--查询数据
select * from fruit;
--查询任务表
select * from all_jobs;
--删除定时任务
begin
dbms_job.remove(23);
end;
submit(v1,v2,v3,v4)参数介绍：
v1: 任务id，属于输出参数
v2: 需要执行的存储过程， 属于输入参数
v3: 从指定时间开始执行定时任务，属于输入参数
v4: 第一次执行后，每隔多长时间执行任务，也就是输入的存储过程 属于输入参数
127.触发器,针对DML语句
格式：
create or replace trigger 名称 before|after insert|update|delete
on 表名 【for each row】
declare
begin
....; --触发时执行的代码
end;
如果有for each row ; dml语句涉及多行数据时，会多次执行 触发代码
案例1：行级触发器，触发时只执行一次
create or replace trigger myt1
after insert
on fruit
declare
begin
dbms_output.put_line('又有新水果了');
end;
insert into fruit(id, fname) values(seq_fruit.nextVal, 'apple'); --输出： 又有新水果了
案例2： 语句级触发器，涉及多行，触发代码就会执行多次
create or replace trigger myt2
after update
on fruit
for each row
declare
begin
dbms_output.put_line('水果更新了');
end;
update fruit set fname='banana' where id between 2 and 4; --输出： 水果更新了 水果更新了 水果更新了
8452
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
