PL/SQL
是oracle对sql的扩展：sql+过程语言
程序结构
1
2
3
4
5
6
7declare
--定义部分（变量的定义，光标的定义，异常的定义)
begin
--程序的主体
exception
--程序执行异常捕获处理
end基本数据类型
1
2
3
4
5
6char,varchar2,date,number,boolean,long
var1 char(100);
var2 date;
var3 number;
...引用型变量
1
my_name student.sname%type; --定义引用变量my_name ,定义为student表的name属性的类型
记录型变量
1
2
3stu_row student%rowtype; --定义记录型变量stu_row,类型为student表的一行。
stu_row.name :="zhangsan"; --使用stu_row 一行中属性name光标类型
光标表示结果集(ResultSet)
1
2
3
4
5
6CURSOR 光标名 [(参数名 数据类型,...)]
cursor c1 is select * from student; --定义了光标c1，指向查询语句的结果集。
open c1; --打开光标，从光标取值
close c1; --关闭光标
fetch c1 into stu_row; --获取光标当前指向行的值赋值个stu_row例子
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17set serveroutput on
declare
cursor cstu is select sname,sid from student;
pname student.sname%type;
psid student.sid%type;
begin
open cursor;
loop
fetch cstu into pname,psid;
--循环退出条件
exit when cstu%notfound;
--打印
dbms_output.put_line('姓名:'||pname||'学号'||psid)；
end loop;
close cursor;
end;
/带参数的光标
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15set serveroutput on
declare
--定义待参数的光标
cursor cstu(psid) is select sname from student where sid = psid;
psname student.sname%type;
begin
--打开光标，查询学号为10的学生姓名
open curor(10);
loop
fetch cstu into psname;
exit when cstu%notfound;
dbms_output.put_line(psname);
end loop;
end;
/
PL/SQL 分支循环结构
条件
1
2
3
4if 条件 then 语句；
elsif 条件 then 语句；
else 语句；
end if；循环
1
2
3
4
5
6
7
8
9
10
11
12while 条件 loop
语句；
end loop;
loop
exit when 条件 --退出循环条件
语句；
end loop;
for 集合的每一项 in 集合 loop
语句；
end loop；
异常exception
1
2
3
4
5
6
7
8
9
10set serveroutput on
declare
pname student.sname%type;
begin
select sname into pname from student where sid =1000;
exception
when no_data_found when dbms_output.put_line("没有找到数据")；
when others then dbms_output.put_line("其他异常");
end;
/
存储过程，存储函数
存储过程结构
1
2
3
4
5
6
7create [or replace] procedure [过程名]（参数名 in/out 参数类型）
as
-- 参数定义
begin
--语句
end;
/存储函数结构
1
2
3
4
5
6
7
8create [or replace] function 函数名（参数名 in/out 参数类型)
return 返回值类型
as
--变量定义
begin
--语句
end;
/调用
1
2
3
4
5
6
7--方式一
exec 过程名(参数);
--方式二
begin
过程名(参数)；
end;
/
在应用程序中访问存储过程，函数
- 创建jdbc连接
1 | public class JDBCUtils{ |
访问存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54public class TestProcedure{
public void testProcedure(){
String sql ="{call queryInfo(?,?,?)}";
Connection conn =null
CallableStatement call =null;
ResultSet rs =null;
try{
conn =JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//输入参数赋值
call.setInt(1,10);
//输出参数申明类型
call.registerOutParameter(2,OracleTypes.Varchar);
call.registerOutParameter(3,OracleTypes.CURSOR);
//执行调用
call.execute();
String name =call.getString(2);
rs =((OracleCallableStatement)call).getCursor(3);
while(rs.next()){
String password =rs.getString("password");
}
catch(Execption e){
e.printStactTrace();
}finally{
JDBCUtils.release(conn,call,rs);
}
}
}
public void testFunction(){
String sql ="{? call queryname(?)}";
try{
conn =JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//输出参数申明类型
call.registerOutParameter(1,OracleTypes.Varchar);
//输入参数赋值
call.setInt(2,100);
//执行调用
call.execute();
String name = call.getDouble(1);
catch(Execption e){
e.printStactTrace();
}finally{
JDBCUtils.release(conn,call,null);
}
}
}
}
oracle常用函数
列转行
1
2
3
4
5
6
7
8
9
10
11--按用户分组，把每组的数据汇合（每个用户会的语言和级别 英语：六级；德语：三级）
left join(
SELECT
C_BH_USER,
REPLACE(wm_concat((N_WYYZ||':'||TO_CHAR(N_WYJB))),',',';') as n_wyjb
FROM t_ryxx_wyxx
WHERE
C_BH_USER IS NOT NULL
and D_ZSHQSJ <= endTime
GROUP BY C_BH_USER
)wyxx on wyxx.c_bh_user = u.c_idto_date(),to_char(),to_number,nvl(),sysdate
1
2
3
4
5to_date('2018-08-08') --将字符串转换成时间
to_char(sysdate,'yyyy')
to_number('10') --转换成数字
nvl(arg0,arg1) --arg0 为 null 区arg1的值
sysdate --当前时间case when then end
1
(case when a=1 and x=2 or y= 3 then 100 else 200 end) as money
分组函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16--按照 c_bh_user 分组，然后按 dt_cjsj,dt_cjsj 排序；取这一组的第一条
left join (
select
C_BH_USER,
DT_JRSJ,
n_zzmm
from
(
select T_RYXX_ZZMM.*,row_number() over(partition by c_bh_user order by DT_CJSJ desc,DT_CJSJ desc) rw
from T_RYXX_ZZMM
where
(dt_jrsj is null or dt_jrsj <= endTime)
and (dt_jssj > endTime or dt_jssj is null)
)
where rw = 1
)zzmm on zzmm.c_bh_user = u.c_id
Oracle 数据导入到导出
存储过程函数，表导出
1
2
31. 使用PL/SQL Developer 导出数据
2. 点击工具->导出用户对象->选择用户，选择要导出的对象（表，函数，存储过程等），选择导出位置
3. 可选项：单个文件（所有数据导出到一个文件中）；每个对象文件（每个对象创建一个文件
存储过程函数，表 导入
1
2
3使用 sqlplus 命令来导入数据
sqlplus username/password@192.168.12.103:1521/orcl @fn_sp.sql
执行结果会生成一个log文件。
