oracle pl/sql

本文demo实战均使用PL/SQL Developer实现。

pl/sql(procedural language/sql)是oracle在标准的sql语言上的扩展。个人认为，这就很类似于C语言，可以理解为是一个面向过程的语言，用来编一些代码块，实现一些功能。块是这个语言的单元，块里面包括存储过程、函数等。

一个简单的存储过程实例

存储过程用于执行特定的操作，当建立存储过程时，既可以指定输入参数(in)，也可以指定输出参数(out)，通过在过程中使用输入参数，可以将数据传递到执行部分；通过使用输出参数，可以将执行部分的数据传递到应用环境。

1、创建一张简单的表
CREATE TABLE mytest(
   username VARCHAR2(30),
   pwd VARCHAR2(30)
);
    
2、创建过程(replace:表示如果有insert_proc，就替换)
CREATE OR REPLACE PROCEDURE insert_proc IS
BEGIN
   INSERT INTO mytest VALUES('林计钦', '123456');
END;
/
   
3、如何查看错误信息：show error; 
注意要在命令窗口执行
    
4、如何调用该过程：exec 过程名(参数值1，参数值2...); 
eg、exec insert_proc;
注意要在命令窗口执行

执行上面的存储过程之后（注意要进行commit），再次查询表格，就发现增加了一条记录。

pl/sql块介绍

块(block)是pl/sql的基本程序单元，编写pl/sql程序实际上就是编写pl/sql块，要完成相对简单的应用功能，可能只需要编写一个pl/sql块，但是如果想要实现复杂的功能，可能需要在一个pl/sql块中嵌套其它的pl/sql块。

pl/sql块由三个部分构成：定义部分，执行部分，例外处理部分，如下所示：

		declare

		/*定义部分——定义常量、变量、游标、例外、复杂数据类型*/

		begin

		/*执行部分——要执行的pl/sql 语句和sql 语句*/

		exception

		/*例外处理部分——处理运行的各种错误*/

		end;

说明：
定义部分是从declare开始的，该部分是可选的；
执行部分是从begin开始的，该部分是必须的；
外处理部分是从exception开始的，该部分是可选的。
以和java编程结构做一个简单的比较。

实例1

功能：输出hello world

set serveroutput on; --打开输出选项
begin 
   dbms_output.put_line('hello world');
end;
/ --执行

相关说明：

dbms_output是oracle所提供的包(类似java 的开发包)，该包包含一些过程，put_line就是dbms_output包的一个过程。

实例2

功能：根据输入的用户名，查询对应的密码，并把该密码存到对应的临时变量中，显示出来。

set serveroutput on; --打开输出选项
DECLARE
    --定义字符串变量
    v_pwd varchar2(30); 
BEGIN
    --执行部分，输入的用户名等于mytest表里面的username的对应的密码存储到v_pwd
    select pwd into v_pwd from mytest where username=&username; --& 表示要接收从控制台输入的变量 --在控制台显示雇员名	
    dbms_output.put_line('该用户的密码：'||v_pwd);
END;
/

注意输入的fengyi要有引号：

实例3

功能：根据输入的用户名，查询对应的用户名和密码，如果输入的用户名存在，就显示查询结果；如果不存在就报错。

--打开输出选项
set serveroutput on; 
DECLARE
    --定义字符串变量
    v_username varchar2(10); 
    v_pwd NUMBER(7,2);
BEGIN
    --执行部分
    select username, pwd into v_username, v_pwd from mytest where username=&username; 
    dbms_output.put_line('用户名：'||v_username||'，密码：'||v_pwd);
EXCEPTION
    --异常处理    
    WHEN no_data_found THEN dbms_output.put_line('朋友，您的用户名输入有误！');
end;
/

函数

--输入用户名，返回该用户的密码
CREATE OR REPLACE FUNCTION getPwd(uname VARCHAR2)
RETURN VARCHAR2 IS 
myPwd VARCHAR2(30);
BEGIN 
   SELECT mt.pwd INTO myPwd FROM mytest mt WHERE mt.username=uname and rownum=1;
   RETURN myPwd;
END;

注意上面加上rownum=1这个，保证只拿这一条数据，否则会报错（因为有可能会查到多条数据）。

包

包用来存放函数和存储过程，包是包括包和包体的，其中包是用来对存储过程或函数的声明，而包体是对包中声明的函数或存储过程的实现。

下面是一个包的例子，包含一个存储过程（通过用户名去修改相应的密码）；包含一个函数，根据用户名获取密码。

oracle pl/sql 变量

在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：

1)、标量类型(scalar)
2)、复合类型(composite) --用于操作单条记录
3)、参照类型(reference) --用于操作多条记录
4)、lob(large object)

pl/sql中定义变量和常量的语法如下：
identifier [constant] datatype [not null] [:=| default expr]
identifier: 名称
constant：指定常量。需要指定它的初始值，且其值是不能改变的
datatype：数据类型
not null：指定变量值不能为null
:= 给变量或是常量指定初始值
default 用于指定初始值
expr ：指定初始值的pl/sql表达式，可以是文本值、其它变量、函数等。

标量定义的案例：
1.定义一个变长字符串
v_ename varchar2(10);
2.定义一个小数,范围-9999.99~9999.99
v_sal number(6,2);
3.定义一个小数并给一个初始值为5.4，:=是pl/sql的赋值号
v_sal2 number(6,2):=5.4;
4.定义一个日期类型的数据
v_hiredate date;
5.定义一个布尔变量，不能为空，初始值为false
v_valid boolean not null default false;

使用标量

为了后续的演示，现在为表增加一个工资字段。

alter table mytest add salary number(10,2);

以输入用户名，显示该用户的姓名、工资、个人所得税(税率为0.03)为例

set serveroutput on; --打开输出选项
DECLARE
  --税率为0.03
  C_TAX_RATE NUMBER(3, 2) :=0.03;
  --雇员姓名
  V_ENAME   mytest.username%TYPE; --用VARCHAR2(5);会提示缓冲区太小，因为输入的字符串长度超过了5
  --工资
  V_SAL     NUMBER(7, 2);
  --个人所得税
  V_TAX_SAL NUMBER(7, 2);
BEGIN
  --执行
  SELECT USERNAME, SALARY INTO V_ENAME, V_SAL FROM mytest WHERE USERNAME=&username; --'fengyi'
  --计算所得税
  V_TAX_SAL := V_SAL * C_TAX_RATE;
  --输出
  DBMS_OUTPUT.PUT_LINE('雇员姓名：' || V_ENAME || '工资：' || V_SAL || ' 交税：' || V_TAX_SAL);
END;
/

上面定义V_ENAME 的时候，如果使用VARCHAR2(5)，当输入的字符串长度大于5的时候就会报错（字符串缓冲区太小），为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样它会按照数据库列来确定你定义的变量的类型和长度。

标识符名 表名.列名%type;

复合类型——pl/sql记录

类似于高级语言中的结构体，需要注意的是，当引用pl/sql记录成员时，必须要加记录变量作为前缀(记录变量.记录成员)如下：

set serveroutput on; --打开输出选项
DECLARE
  --定义一个pl/sql记录类型emp_record_type，
  --类型包含2个数据NAME, SALARY。说白了，就是一个类型可以存放2个数据，主要是为了方便管理 
  TYPE EMP_RECORD_TYPE IS RECORD(
    NAME   mytest.USERNAME%TYPE,
    SALARY mytest.SALARY%TYPE);
  --定义了一个sp_record变量，这个变量的类型是emp_record_type
  SP_RECORD EMP_RECORD_TYPE;
BEGIN
  SELECT USERNAME, SALARY INTO SP_RECORD FROM mytest WHERE username = 'fengyi';
  DBMS_OUTPUT.PUT_LINE('用户名:' || SP_RECORD.NAME || '工资：' || SP_RECORD.SALARY);
END;
/

复合类型——pl/sql表

相当于Java中的HashMap，且键必须是字符串类型或PSL_INTEGER类型。如下官方文档说明。

官方文档示例：

参照变量——ref cursor游标变量

用游标时，当定义游标时不需要指定相应的select语句，但是当使用游标时（open 时）需要指定select语句，这样一个游标与一个select语句结合了。实例如下：
请使用pl/sql编写一个块，可以输入部门号，并显示该部门所有员工姓名和他的工资。

为了方便上述例子演示，给mytest表再增加一个部门字段并插入几条数据。

alter table mytest add department varchar2(30);

SET serveroutput ON;
DECLARE 
  --定义游标
  TYPE sp_emp_cursor IS REF CURSOR;
  --定义一个游标变量
  sp sp_emp_cursor;
  --定义变量
  v_ename mytest.username%TYPE;
  v_sal mytest.salary%TYPE;
BEGIN
  OPEN sp FOR SELECT mt.username, mt.salary FROM mytest mt WHERE mt.department = 'dep1';
  --方法一 loop循环
  LOOP 
  FETCH sp INTO v_ename, v_sal;
  EXIT WHEN sp%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME || ' 工资：' || V_SAL);
  END LOOP;
END;
/

上面用的是LOOP循环，还可以使用下面的while循环和for循环，替换掉相应部分也是可以的。

WHILE 1=1 LOOP
    FETCH sp INTO v_ename, v_sal;
    EXIT WHEN sp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('名字：' || V_ENAME || ' 工资：' || V_SAL);
  END LOOP;

FOR cur IN (SELECT mt.username, mt.salary FROM mytest mt WHERE mt.department='dep1') LOOP
    DBMS_OUTPUT.PUT_LINE('名字：' || cur.username || ' 工资：' || cur.salary);
  END LOOP;

oracle pl分支

实例1（简单的条件判断if–then）

编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该员工工资增加10%。

SET serveroutput ON;
CREATE OR REPLACE PROCEDURE gwli(uname VARCHAR2) IS
  --定义
  V_SAL mytest.salary%TYPE;
BEGIN
  --执行
  SELECT salary INTO V_SAL FROM mytest WHERE username = uname;
  --判断
  IF V_SAL < 2000 THEN
    UPDATE mytest SET salary = V_SAL + V_SAL * 0.1 WHERE username = uname;
    COMMIT;
  END IF;
END;

实例2（二重条件分支 if–then–else）

编写一个过程，可以输入一个用户名，如果该用户的salary小于100，就补助100，否则就减少100。

CREATE OR REPLACE PROCEDURE gwli(uname VARCHAR2) IS
  --定义
  V_SAL mytest.salary%TYPE;
BEGIN
  --执行
  SELECT salary INTO V_SAL FROM mytest WHERE username = uname;
  --判断
  IF V_SAL < 100 THEN
    UPDATE mytest SET salary = salary + 100 WHERE username = uname;
  ELSE
    UPDATE mytest SET salary = salary - 100 WHERE username = uname;
  END IF;
  COMMIT;
END;

实例3（多重条件分支 if–then–ELSIF–then）

编写一个过程，可以输入一个用户名，如果该用户的salary小于100，就补助100，大于100小于1000，就补助1000，否则就减少2000。

CREATE OR REPLACE PROCEDURE gwli(uname VARCHAR2) IS
  --定义
  V_SAL mytest.salary%TYPE;
BEGIN
  --执行
  SELECT salary INTO V_SAL FROM mytest WHERE username = uname;
  --判断
  IF V_SAL < 100 THEN
    UPDATE mytest SET salary = salary + 100 WHERE username = uname;
  ELSIF V_SAL < 1000 THEN
    UPDATE mytest set salary = salary + 1000 WHERE username = uname;
  ELSE
    UPDATE mytest SET salary = salary - 2000 WHERE username = uname;
  END IF;
  COMMIT;
END;

oracle pl循环

循环语句–loop

输入n，循环打印1-n

CREATE OR REPLACE PROCEDURE gwli(n NUMBER) IS
  --定义
  V_NUM NUMBER := 1;
BEGIN
  LOOP
    EXIT WHEN V_NUM > n;
    dbms_output.put_line(V_NUM);
    V_NUM := V_NUM + 1; 
  END LOOP;
END;

循环语句–while循环

输入n，循环打印1-n

CREATE OR REPLACE PROCEDURE gwli(n NUMBER) IS
  --定义
  V_NUM NUMBER := 1;
BEGIN
  WHILE V_NUM <= n LOOP
    dbms_output.put_line(V_NUM);
    V_NUM := V_NUM + 1; 
  END LOOP;
END;

循环语句–for循环

输入n，循环打印n-1

CREATE OR REPLACE PROCEDURE gwli(n NUMBER) IS
BEGIN
  FOR i IN REVERSE 1 .. n LOOP
    dbms_output.put_line(i);
  END lOOP;
END;

上面代码中，去掉REVERSE，就表示循环打印1-n了。

oracle pl控制

null语句

null语句就是给空语句，仅仅标明这个语句是空语句，不会产生任何影响。

CREATE OR REPLACE PROCEDURE gwli(n NUMBER) IS
BEGIN
  IF n >= 1 THEN
    FOR i IN 1 .. n LOOP
      dbms_output.put_line(i);
    END lOOP;
  ELSE
    NULL;--这是一个空语句，可以提高程序的可读性，不写也可以
    dbms_output.put_line('这行当然会打印输出的');
  END IF;
END;

goto语句

输入的数大于等于1的时候就依次打印出来，否则利用goto语句跳转到打印“输入非法”的地方。

CREATE OR REPLACE PROCEDURE gwli(n NUMBER) IS
BEGIN
  IF n >= 1 THEN
    FOR i IN 1 .. n LOOP
      dbms_output.put_line(i);
    END lOOP;
  ELSE
    GOTO END_LOOP;
  END IF;
  <<END_LOOP>>
  DBMS_OUTPUT.PUT_LINE('输入非法');
END;

oracle pl/sql 分页

存储过程（含输入输出）

in表示输入，out表示输出。

--输入和输出的存储过程
CREATE OR REPLACE PROCEDURE SP_PROC(SPNO   IN NUMBER,
                                    SPNAME OUT VARCHAR2,
                                    SPSAL  OUT NUMBER,
                                    SPJOB  OUT VARCHAR2) IS
BEGIN
  SELECT ENAME, SAL, JOB INTO SPNAME, SPSAL, SPJOB FROM EMP WHERE EMPNO = SPNO;
END;

存储过程（返回结果集）

如果要返回结果集，就返回游标！

建立包，里面搞一个游标。

CREATE OR REPLACE PACKAGE TESTPACKAGE AS
  TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;

建立存储过程，里面的返回值就是上面的游标，拿到这个游标给Java就可以调用返回查询的结果集了。

CREATE OR REPLACE PROCEDURE SP_PROC(SPNO IN NUMBER,
	        P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS
BEGIN
  OPEN P_CURSOR FOR
    SELECT * FROM EMP WHERE DEPTNO = SPNO;
END SP_PROC;

分页

--ROWNUM用法（ROWNUM是记录的行数）
SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10;
----oracle分页sql语句；在分页时，大家可以把下面的sql语句当做一个模板使用
SELECT *
FROM (SELECT o.*, ROWNUM RN FROM (SELECT * FROM EMP) o WHERE ROWNUM <= 10)
WHERE RN >= 6;

下面就是分页代码了，分页功能对应的存储过程。

--建立一个包
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
  TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;
/

--开始编写分页的过程
CREATE OR REPLACE PROCEDURE FENYE(TABLENAME IN VARCHAR2, 
                                  PAGESIZE IN NUMBER, --每页显示记录数
                                  PAGENOW IN NUMBER, --页数
                                  MYROWS OUT NUMBER, --总记录数
                                  MYPAGECOUNT OUT NUMBER, --总页数
                                  P_CURSOR OUT TESTPACKAGE.TEST_CURSOR) IS --返回的记录集   
--定义部分
--定义sql语句字符串
V_SQL VARCHAR2(1000);
--定义两个整数
V_BEGIN NUMBER := (PAGENOW - 1) * PAGESIZE + 1; 
V_END NUMBER := PAGENOW * PAGESIZE;
BEGIN
--执行部分
V_SQL := 'select * from (select t1.*, rownum rn from (select * from ' || TABLENAME || ') t1 where rownum<=' || V_END || ') where rn>=' || V_BEGIN;
--把游标和sql关联
OPEN P_CURSOR FOR V_SQL;
--计算myrows和myPageCount
--组织一个sql语句
V_SQL := 'select count(*) from ' || TABLENAME;
--执行sql,并把返回的值，赋给myrows；
EXECUTE ImMEDIATE V_SQL INTO MYROWS; --它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前，
                                     --EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能，较之以前它相当容易编码.
                                     --尽管DBMS_SQL仍然可用，但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上。 
--计算myPageCount
--if myrows%Pagesize=0 then 这样写是错的
IF MOD(MYROWS, PAGESIZE) = 0 THEN 
  MYPAGECOUNT := MYROWS/PAGESIZE; 
ELSE 
  MYPAGECOUNT := MYROWS/PAGESIZE + 1;
END IF;
--关闭游标
--CLOSE P_CURSOR; --不要关闭，否则java调用该存储过程会报错
END;

然后用Java代码调用这个存储过程就可以了。

分页为啥用子查询，可以参考下面这个文章：
https://blog.youkuaiyun.com/u010708434/article/details/17094893
主要说的是查询的rownum是按照插入的顺序给出的。。所以要得用子查询先把所有的rownum都查出来，作为新增的一列形成新表，然后再在这个新表里面去查去用。。下面是上面博客中博主举的一个例子：

现有一个商品销售表sale，表结构为：
month　　　 char(6)　　　　　 --月份
sell　　　　number(10,2)　　　--月销售金额

create table sale (month char(6),sell number);
insert into sale values('200001',1000);
insert into sale values('200002',1100);
insert into sale values('200003',1200);
insert into sale values('200004',1300);
insert into sale values('200005',1400);
insert into sale values('200006',1500);
insert into sale values('200007',1600);
insert into sale values('200101',1100);
insert into sale values('200202',1200);
insert into sale values('200301',1300);
insert into sale values('200008',1000);
commit;

rowid 与 rownum 虽都被称为伪列，但它们的存在方式是不一样的，rowid 可以说是物理存在的，表示记录在表空间中的唯一位置ID，在DB中唯一。只要记录没被搬动过，rowid是不变的。rowid 相对于表来说又像表中的一般列，所以以 rowid 为条件就不会有 rownum那些情况发生。



关于ROWNUM，官方文档如下说明，和上面作者说的是一致的：
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ROWNUM-Pseudocolumn.html#GUID-2E40EC12-3FCF-4A4F-B5F2-6BC669021726

异常

暂略

触发器

触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。然后，触发器的触发条件其实在你定义的时候就已经设定好了。这里面需要说明一下，触发器可以分为语句级触发器和行级触发器。简单的说就是语句级的触发器可以在某些语句执行前或执行后被触发。而行级触发器则是在定义的了触发的表中的行数据改变时就会被触发一次。

触发器的语法：

	create [or replace] tigger 触发器名 触发时间 触发事件

	on 表名

	[for each row]

	begin

	  pl/sql语句

	end

	其中：

	触发器名：触发器对象的名称。由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。

	触发时间：指明触发器何时执行，该值可取：

	before：表示在数据库动作之前触发器执行;

	after：表示在数据库动作之后触发器执行。

	触发事件：指明哪些数据库动作会触发此触发器：

	insert：数据库插入会触发此触发器;

	update：数据库修改会触发此触发器;

	delete：数据库删除会触发此触发器。

	表 名：数据库触发器所在的表。

	for each row：对表的每一行触发器执行一次。如果没有这一选项，则只对整个表执行一次。

	触发器能实现如下功能：

	功能：

	1、 允许/限制对表的修改

	2、 自动生成派生列，比如自增字段

	3、 强制数据一致性

	4、 提供审计和日志记录

	5、 防止无效的事务处理

	6、 启用复杂的业务逻辑

实战暂略。

参考资料

http://www.hechaku.com/Oracle/oracle_pl_sql.html
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/overview.html#GUID-2FBCFBBE-6B42-4DB8-83F3-55B63B75B1EB