前言
本文简单记录下oracle数据库中,如何用plsql编程、以及存储过程的创建和使用。
相关的概念我就不列举了,大家不清楚的可以自行搜索,本篇主要列举实际的sql语法。
那plsql编程,大家可以使用windows的命令行,也可以使用工具进行编写调试,我这边主要使用Oracle Sql Developer来举例。
1 plsql编程
1.1 程序结构
首先说说plsql的程序,可以分为3个部分:声明部分、可执行部分、异常处理部分。其中declare部分用来声明变量或者游标(结果集类型变量),如果程序中没有变量声明则可以省略。
DECLARE
--声明变量、游标
-- 相当于java中的 public class A{}
BEGIN
--执行部分,相当于java中的main方法
--可以进行异常处理
dbms_output.put_line('hello world');
END;
这段程序中,dbms_output是oracle的内置程序包,相当于java中的System.out,而put_line()是调用的方法,相当于println()方法。执行结果为:
那如果是在命令行执行这段程序,则需要在执行程序之前,打开控制台的输出(set serveroutput on),并在结尾加“/”标识程序已结束,然后回车即可看到打印的结果。
1.2 变量
在plsql中,常见的变量分为两大类:
1、普通数据类型(char,varchar2,date,number,boolean,long);
2、特殊变量类型(引用型变量、记录型变量);
变量的声明方式为:变量名 变量类型(变量长度) 例如:v_name varchar2(20)
1.2.1 普通变量
普通变量的赋值方式有两种:
1)使用“:=”直接赋值,比如:v_name :=‘zhangsan’
2)语句赋值,语法为:select 值 into 变量名
– 声明一个人的信息,姓名、薪水、地址
DECLARE
-- 姓名
v_name VARCHAR2(50) := 'zhangsan';
-- 薪水
v_sal NUMBER;
-- 地址
v_addr VARCHAR2(200);
begin
-- 直接赋值
v_sal := 10000;
-- 语句赋值
select '中国' into v_addr from dual;
-- 打印变量
DBMS_OUTPUT.PUT_LINE('姓名:' || v_name || '薪水:' || v_sal || '地址:' || v_addr);
end;
执行结果为:
1.2.2 引用型变量
变量的类型和长度取决于表中字段的类型和长度;
通过表名.列名%type指定变量的类型和长度,例如:v_name emp.ename%type;
引用型变量的好处:
使用普通变量定义方式,需要知道表中列的类型,而使用引用类型,不需要考虑列的类型,使用%type是非常好的编程风格,因为它使得plsql更加灵活,更加适应于对数据库定义的更新。
建表语句:
CREATE TABLE "GD_JIANGUAN"."EMP"
( "ENAME" VARCHAR2(20 BYTE),
"ESAL" NUMBER,
"EADDR" VARCHAR2(20 BYTE),
"ENO" VARCHAR2(20 BYTE)
) ;
COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ENAME" IS '用户名';
COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ESAL" IS '薪水';
COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."EADDR" IS '地址';
COMMENT ON COLUMN "GD_JIANGUAN"."EMP"."ENO" IS '编号';
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('张三',10000,'中国','1');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('李四',8000,'美国','2');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('王五',12000,'广州','3');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('赵六',7800,'深圳','4');
Insert into emp (ENAME,ESAL,EADDR,ENO) values ('钱七',9000,'上海','5');
举例
--查询emp表中赵六的个人信息,打印姓名、薪水和地址
DECLARE
--姓名 声明变量并直接赋值
v_name emp.ename%TYPE;
--薪水
v_sal emp.esal%TYPE;
--地址
v_addr emp.eaddr%TYPE;
BEGIN
--查询表中的姓名和薪水并赋值给变量
--注意查询的字段和赋值的变量的顺序、个数、类型需要一致
SELECT ename,esal,eaddr INTO v_name,v_sal,v_addr FROM emp WHERE ename = '赵六';
--打印结果
dbms_output.put_line('姓名:' || v_name || ' 薪水:' || v_sal || ' 地址:' || v_addr);
END;
结果为:
1.2.3 记录型变量
接收表中的一整行记录,相当于java中的一个对象
语法:变量名称 表名%rowtype,例如:v_emp emp%rowtype;
如果一张表有几十个字段,程序中需要使用这些字段,那定义一个记录型变量则可以方便的解决这个问题。
注:在使用记录型变量查询一行数据时,不能指定列,需用select * into 查询
获取取查询结果方法:变量名.列名
举例:
--查询emp表中赵六的个人信息,打印姓名、薪水和地址
DECLARE
--定义记录型变量
v_emp emp%ROWTYPE;
BEGIN
--记录型变量默认接收表中的一行数据,不能指定字段
SELECT * INTO v_emp FROM emp WHERE ename = '赵六';
--打印结果,通过变量名.属性的方式获取变量中的值
dbms_output.put_line('姓名:' || v_emp.ename || ' 薪水:' || v_emp.esal || ' 地址:' || v_emp.eaddr);
END;
结果为:
使用记录型变量常见的问题及注意点:
1)记录型变量只能存储一条完整的行数据,不支持存储部分字段
2)返回的行太多,记录型变量也接收不了
1.3 流程控制
1.3.1 条件分支
条件分支的语法一般为
begin
if 条件1 then 执行1
elsif 条件2 then 执行2
else 执行3
end if;
end;
举例:
-- 判断emp表中记录数是否超过5条,3-5条,小于3条
DECLARE
--声明变量接收emp表中的记录数
v_count NUMBER;
BEGIN
SELECT COUNT(1) INTO v_count FROM emp;
IF v_count > 5 THEN
dbms_output.put_line('记录数超过5条,具体数量为:' || v_count);
ELSIF v_count >= 3 THEN
dbms_output.put_line('记录数为3~5条,具体数量为:' || v_count);
ELSE
dbms_output.put_line('记录数小于3条,具体数量为:' || v_count);
END IF;
END;
结果为:
注意:oracle中所有的字符拼接符都是“||”,而不是“+”。如果报错“数字或值错误”,则检查程序是否使用了“+”号作为拼接符使用。
1.3.2 循环
oracle中,循环的方式有3钟,for、while、loop,当然,使用goto语法,也能进行循环下面分别列举这4种循环的用法。
-- goto循环
DECLARE
x number;
BEGIN
x := 0;
<<repeat_loop>> --循环点
x := x + 1;
DBMS_OUTPUT.PUT_LINE(X);
IF X < 5 THEN
GOTO repeat_loop; --当x的值小于9时,就goto到repeat_loop
END IF;
END;
/
--for循环
DECLARE
X number; --声明变量
BEGIN
x := 1; --给初值
FOR X IN REVERSE 1 .. 10 LOOP
--reverse由大到小
DBMS_OUTPUT.PUT_LINE('内:x=' || x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('end loop:x=' || X); --x=1
END;
/
--while循环
DECLARE
x number;
BEGIN
x := 0;
WHILE x < 9 LOOP
x := x + 1;
DBMS_OUTPUT.PUT_LINE('内:x=' || x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外:x=' || x);
END;
--loop循环
DECLARE
x number;
BEGIN
x := 0;
LOOP
x := x + 1;
EXIT WHEN x > 9;
DBMS_OUTPUT.PUT_LINE('内:x=' || x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外:x=' || x);
END;
2 游标
2.1 什么是游标
游标用于临时存储一个查询返回的多行数据(结果集,类似于java中jdbc连接返回的ResultSet集合),通过遍历游标,可以逐行访问处理该结果集的数据。
游标的使用方式:申明 --> 打开 --> 读取 --> 关闭
2.2 语法
游标的声明:cursor 游标名[{参数列表}] is 查询语句;
游标打开: open 游标名;
游标读取:fetch 游标名 into 变量列表;
游标关闭:close 游标名;
2.3 游标的属性
属性 | 类型 | 说明 |
---|---|---|
%rowcount | 整型 | 获得fetch语句返回的数据行数 |
%found | 布尔型 | 最近的fetch语句返回一行数据则为真,否则为假 |
%notfound | 布尔型 | 与%found返回值相反 |
%isopen | 布尔型 | 游标已经开启时值为真,否则为假 |
其中%notfound是在游标中找不到元素的时候返回true,通常用来判断退出循环。
2.4 创建和使用
2.4.1 不带参数
-- 查询EMP表中所有员工姓名和工资,并依次打印出来
DECLARE
-- 声明游标
CURSOR C_EMP IS SELECT ENAME, ESAL FROM EMP;
--申明变量接收游标中的数据
V_NAME EMP.ENAME%TYPE;
V_SAL EMP.ESAL%TYPE;
BEGIN
--打开游标
OPEN C_EMP;
--遍历游标
LOOP
-- 获取游标中的数据,如果有的话就赋值给变量
FETCH C_EMP INTO V_NAME, V_SAL;
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:' || V_NAME || ' 薪水:' || V_SAL);
END LOOP;
--关闭游标
CLOSE C_EMP;
END;
结果为:
2.4.2 带参数
-- 查询EMP表中编号为5的员工姓名和工资,并依次打印出来
DECLARE
-- 声明游标
CURSOR C_EMP(V_NO EMP.ENO%TYPE) IS
SELECT ENAME, ESAL FROM EMP WHERE ENO = V_NO;
--申明变量接收游标中的数据
V_NAME EMP.ENAME%TYPE;
V_SAL EMP.ESAL%TYPE;
BEGIN
--打开游标
OPEN C_EMP(5);
--遍历游标
LOOP
-- 获取游标中的数据,如果有的话就赋值给变量
FETCH C_EMP INTO V_NAME, V_SAL;
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('姓名:' || V_NAME || ' 薪水:' || V_SAL);
END LOOP;
--关闭游标
CLOSE C_WMP;
END;
结果为:
3 存储过程
3.1 概念
之前编写的plsql程序可以进行表的操作、判断、循环逻辑处理的工作,但是没有办法重复调用。这可以理解为之前的代码全部编写在了main方法中,是匿名的程序,那在java中可以通过封装对象和方法来解决复用的问题。那plsql是将一个个的程序处理过程存储起来进行复用,那这些被存储起来的plsql程序称之为存储过程,相当于java中的方法。
存储过程的作用:
1)在开发程序的过程中,为了一个特定的业务功能,会向数据库进行多次请求查询,需要对数据库进行多次I/O读写,性能比较低,而且数据库的连接关闭是非常耗资源的。如果把这些业务处理放到plsql中,在应用程序中调用plsql就可以做到连接关闭一次数据库就可以实现我们的业务,那将可以大大提升效率。
2)oracle官方建议:能够让数据库操作的,就不要放到程序中,在数据库中实现基本不会出现错误,而在程序中可能会出错(如果在数据库中操作数据,有一定的日志恢复等功能)。
3.2 语法
create or replace procedure 过程名称[{参数列表}] is
begin
end [过程名称];
根据参数的类型,可以分为以下3种:
1)不带参数的
create or REPLACE PROCEDURE p_hello is
--这里声明变量,declare被省略
begin
DBMS_OUTPUT.PUT_LINE('hello');
end p_hello;
/
--调用存储过程
exec p_hello;
2)带输入参数的
--创建一个查询并打印某个员工的姓名和薪水的存储过程,要求调用的时候传入员工的编号,自动在控制台打印
CREATE OR REPLACE PROCEDURE QUERYNAMEANDSAL(I_NO EMP.ENO%TYPE) AS
-- 声明变量
V_ENAME EMP.ENAME%TYPE;
V_ESAL EMP.ESAL%TYPE;
BEGIN
SELECT ENAME, ESAL INTO V_ENAME, V_ESAL FROM EMP WHERE ENO = I_NO;
DBMS_OUTPUT.PUT_LINE(V_ENAME || ' ' || V_ESAL);
END QUERYNAMEANDSAL;
/
--在窗口命令栏里调用存储过程
exec QUERYNAMEANDSAL(5);
3)带输入参数和输出参数(返回值)的
--创建一个查询并打印某个员工薪水的存储过程,要求调用的时候传入员工的编号,程序中获取输出
CREATE OR REPLACE PROCEDURE QUERYNAMEANDSAL02(I_NO in EMP.ENO%TYPE ,O_SAL out EMP.ESAL%TYPE) AS
BEGIN
SELECT ESAL INTO O_SAL FROM EMP WHERE ENO = I_NO;
END QUERYNAMEANDSAL02;
/
--调用存储过程
declare
--设置变量
O_SAL EMP.ESAL%type;
begin
--执行过程
QUERYNAMEANDSAL02(1024,O_SAL);
dbms_output.put_line(O_SAL);
/
--也可以通过java程序调用存储过程
-- 以下程序在java中编写
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
String userName = "gd_jianguan";
String userpwd = "gd_jianguan";
Connection conn = DriverManager.getConnection(url, userName, userpwd);
//获取语句对象
String sql = "{call QUERYNAMEANDSAL02(?,?)}";
CallableStatement call = conn.prepareCall(sql);
//设置输入参数
call.setInt(1, 5);
//注册输出参数
call.registerOutParameter(2, OracleTypes.DOUBLE);
//执行存储过程
call.execute();
//获取输出参数
double sal = call.getDouble(2);
System.out.println("薪水:" + sal);
//释放资源
call.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}