pl/sql编程学习笔记(一)

作为oracle PL/SQL编程的学习笔记基础记录一下。

SQL结构化查询语言，没有变量，没有流程控制(分支，循环)，多条执行时，是逐一发送给数据库。

PL/SQL在SQL之上得到了补充，一种过程化查询语言

            

1 程序结构

语法：

DECLARE
  --声明部分 变量 常量 游标
BEGIN
  --分支 循环 SQL
EXCEPTION 
  -- 处理异常
END;
/

demo:

SET serveroutput ON; --使DBMS_OUTPUT.put_line可以输出
DECLARE
  v_eno NUMBER ; 
  v_ename VARCHAR2(10);
BEGIN
  v_eno := &empno; --键盘输入 并赋值
  SELECT ename INTO v_ename FROM emp WHERE empno=&empno; -- ename INTO v_name 列名存入变量
  DBMS_OUTPUT.put_line('No:' || v_eno || 'Name:' || v_ename);
END;
/

常量：

SET serveroutput ON; --使DBMS_OUTPUT.put_line可以输出
DECLARE
  v_temp CONSTANT  NUMBER NOT NULL := 100 ; --常量并且不为空
BEGIN
  DBMS_OUTPUT.put_line(v_temp);
END;
/

%TYPE与%ROWTYPE:

<span style="font-weight: normal;">SET serveroutput ON; --使DBMS_OUTPUT.put_line可以输出
DECLARE
  v_eno emp.empno%TYPE; --变量v_eno类型与 emp表中empno数据类型一致
  v_ename emp.ename%TYPE;
  v_deptRow dept%ROWTYPE; --可以存储一整行数据的内容 
BEGIN
  SELECT ename INTO v_ename FROM emp WHERE empno=7499;
  DBMS_OUTPUT.put_line('编号:' || v_eno || '姓名:' || v_ename); --输出变量
  SELECT * INTO v_deptRow FROM dept WHERE deptno=10; --查询一整行内容 INTO 至rowtype声明的变量
  DBMS_OUTPUT.put_line(v_deptRow.deptno || '  ' || v_deptRow.dname);
END;
/</span>

运算:

<span style="font-weight: normal;">DECLARE
 v_url VARCHAR2(50):='www.b.com';
 v_num1 NUMBER := 30;
 v_num2 NUMBER := 20;
 
 v_flag1 BOOLEAN := TRUE;
 v_flag2 BOOLEAN := FALSE;
 v_flag3 BOOLEAN ;  --默认为null
BEGIN
  IF v_num1 = v_num2 THEN -- 关系运算符 >,<,<>,!=,>=,<=,=
    DBMS_OUTPUT.put_line('v_num1 > v num2');  
  END IF;
  IF v_url LIKE '%baidu%' THEN
    DBMS_OUTPUT.put_line('baidu.com');
  ELSE
    DBMS_OUTPUT.put_line('no baidu.com');
  END IF;
  --逻辑运算 not,and,or 
  IF v_flag1 AND (NOT v_flag2) THEN
    DBMS_OUTPUT.put_line('v_flag1 AND (NOT v_flag2) = TRUE');
  END IF;
  IF v_flag1 OR v_flag3 THEN
    DBMS_OUTPUT.put_line('v_flag1 OR v_flag3 = TRUE');
  END IF;
  IF v_flag1 AND v_flag3 IS NULL THEN
    DBMS_OUTPUT.put_line('null');
  END IF;
END;
/</span>

数据类型：

<span style="font-weight: normal;">SET serveroutput ON; --使DBMS_OUTPUT.put_line可以输出
DECLARE
 v_number NUMBER(5,2) := 999.88 ; --NUMBER类型，一共五位数其中三位整数两位小数 十进制 22字节
 v_float BINARY_FLOAT := 1313.51F; --5字节 二进制运算
 v_double BINARY_DOUBLE := 8989.89D; --9字节 
 v_char CHAR(10) := 'oracle';  --定长字符串
 v_varchar2 VARCHAR2(10) := 'oracle' ;--变长字符串
 v_rowid ROWID ; --数据物理行地址
 v_urowid UROWID; --通用性ROWID 增加了一个逻辑行地址
 v_date1 DATE := SYSDATE;  --不包含毫秒
 v_date2 DATE := SYSTIMESTAMP;
 v_date3 DATE := '23-10月-2010';     
 v_timestamp1 TIMESTAMP := SYSDATE;  --包含毫秒   TIMESTAMP WITH LOCAL TIME ZONE
 v_timestamp2 TIMESTAMP := SYSTIMESTAMP;
 v_timestamp3 TIMESTAMP := '23-10月-2010';
 v_timestamp4 TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP; --与格林威治时间差
 v_timestamp5 TIMESTAMP WITH LOCAL TIME ZONE := SYSTIMESTAMP; --数据库本地时区

 SUBTYPE score_subtype IS NUMBER(5,2) NOT NULL; --子类型，可以增加约束
 v_score score_type =:= 99.35;
BEGIN
  DBMS_OUTPUT.put_line('v_number = ' || v_number);
  DBMS_OUTPUT.put_line('v_float = ' || v_float);
  DBMS_OUTPUT.put_line('v_double = ' || v_double);
  DBMS_OUTPUT.put_line('v_char  ' || LENGTH(v_char));
  DBMS_OUTPUT.put_line('v_varchar2  ' || LENGTH(v_varchar2));
  SELECT ROWID INTO v_rowid FROM emp WHERE empno = 7369;
  DBMS_OUTPUT.put_line('v_rowid  ' || v_rowid);
  SELECT ROWID INTO v_urowid FROM emp WHERE empno = 7369;
  DBMS_OUTPUT.put_line('v_urowid  ' || v_urowid);
  DBMS_OUTPUT.put_line('v_date1  ' || TO_CHAR(v_date1,'yyyy-mm-dd hh24:mi:ss'));
  DBMS_OUTPUT.put_line('v_date2  ' || TO_CHAR(v_date2,'yyyy-mm-dd hh24:mi:ss'));
  DBMS_OUTPUT.put_line('v_date3  ' || TO_CHAR(v_date3,'yyyy-mm-dd hh24:mi:ss'));
  DBMS_OUTPUT.put_line('v_timestamp1  ' || v_timestamp1);
  DBMS_OUTPUT.put_line('v_timestamp2  ' || v_timestamp2);
  DBMS_OUTPUT.put_line('v_timestamp3  ' || v_timestamp3);
  DBMS_OUTPUT.put_line('v_timestamp4  ' || v_timestamp4);
  DBMS_OUTPUT.put_line('v_timestamp5  ' || v_timestamp5);
END;
/</span>

分支语法:

<span style="font-weight: normal;">DECLARE
  v_choose  NUMBER := 1;
BEGIN
  IF v_choose = 1 THEN
    --do something
  ELSE
    --do something
  END IF;
  ------------------------------
  IF v_choose = 1 THEN
    --do something
  ELSIF v_choose = 2 THEN
    --do something
  ELSE
    --do something
  END IF;
  ------------------------------
  
  CASE v_choose --变量
    WHEN 0 THEN 
        DBMS_OUTPUT.put_line('0');
    WHEN 1 THEN
        DBMS_OUTPUT.put_line('1');
    ELSE
        DBMS_OUTPUT.put_line('no pick');
    END CASE;
END;
/</span>

循环语法：

<span style="font-weight: normal;">DECLARE
    v_i  NUMBER := 1;
    v_i1  NUMBER := 1;
    v_i2 NUMBER ;
  BEGIN
    --LOOP循环        类似while循环
    LOOP
        DBMS_OUTPUT.put_line('v_i =' || v_i);
        EXIT WHEN v_i>=3; --循环退出条件
        v_i := v_i+1 ;
    END LOOP;
    --WHILE LOOP 循环
    WHILE(v_i1>=3) LOOP
        DBMS_OUTPUT.put_line('v_i1 =' || v_i1);
        v_i := v_i+1 ;
    END LOOP;
    --FOR LOOP循环
     --FOR循环 REVERSE倒序  
    FOR v_i2 IN REVERSE 1..3 LOOP
        IF v_i2 = 3 THEN
          EXIT; --EXIT 退出循环体， countinue 退出本次循环
        END IF;
        DBMS_OUTPUT.put_line('v_i2 =' || v_i2);
    END LOOP;
  END;
  /</span>

2 集合概念

记录类型

DECLARE
   TYPE dept_type IS RECORD( --定义某些字段为一个复合类型  记录类型
        v_deptno dept.deptno%TYPE ,
        v_dname dept.dname%TYPE ,
        v_loc dept.loc%TYPE
   ); 
   v_dept dept_type ; --定义复合类型变量
  BEGIN
    SELECT deptno,dname,loc INTO v_dept FROM dept WHERE deptno=10; -- insert update 均可支持 记录类型操作 例如 
    --INSERT INTO dept VALUES v_dept; 
    --UPDATE dept SET ROW=v_dept  WHERE deptno = v_dept.v_deptno ; row表示一行
    DBMS_OUTPUT.put_line('deptno ' || v_dept.v_deptno || ', dname ' || v_dept.v_dname);
    v_dept.v_dname := 'haha' ; --记录类型直接操作 类型变量名.内部变量名
    DBMS_OUTPUT.put_line('deptno ' || v_dept.v_deptno || ', dname ' || v_dept.v_dname);
  END;
  /

索引表

  DECLARE
  --TYPE 索引表名称 IS TABLE OF 数据类型例如 %ROWTYPE等或者是复合类型 INDEX BY 索引类型， 也可更换成varchar2 等
  --- pls_integer -2^31~2^31范围 二进制运算 占用较少的存储空间 
   TYPE table_index IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER ;
   --TYPE table_index IS TABLE OF dept%ROWTYPE INDEX BY PLS_INTEGER ;
   v_table_index table_index; --声明索引表变量
  BEGIN
    v_table_index(1) := 'baidu'; --第一个索引的内容
    --v_table_index(1).deptno := 90;
    --v_table_index(1).dname := 'haha';
    --v_table_index(1).loc := 'nanjing';
    v_table_index(10) := 'java'; --第十个索引的内容
    IF v_table_index.EXISTS(1) THEN  --exists(index)判断是否存在 存在为true
      DBMS_OUTPUT.put_line(v_table_index(1));
    END IF;
    DBMS_OUTPUT.put_line(v_table_index(10));
    
  END;
  /

嵌套表

<pre name="code" class="sql">--CREATE[OR REPLACE] TYPE 类型名称 AS|IS(PLSQL中使用IS) TABLE OF 数据类型  NOT NULL
CREATE OR REPLACE TYPE project_nested AS TABLE OF VARCHAR2(50)  NOT NULL ; --定义嵌套类型

DROP TABLE department PURGE ;
CREATE TABLE department (
  did   NUMBER,
  deptname  VARCHAR2(30) NOT NULL,
  projects  project_nested,
  CONSTRAINT pk_did PRIMARY KEY (did)  --定义主键
) NESTED TABLE projects STORE AS projects_nested_table ; --嵌套表定义存储
INSERT INTO department (did,deptname,projects) VALUES  (10,'Hello',project_nested('123','321'));
COMMIT;
SELECT * FROM TABLE (SELECT projects FROM department WHERE did = 10); --查询嵌套表字段，返回多列
UPDATE TABLE （SELECT projects FROM department WHERE did = 10）pro SET VALUE(pro)='123' WHERE pro.COLUMN_VALUE='321'; --更新嵌套表内容
DELETE FROM TABLE (SELECT projects FROM department WHERE did = 10) p WHERE p.column_value='123' ;  --删除嵌套表中的内容

--复合类型嵌套表
CREATE OR REPLACE TYPE project_type AS OBJECT( --定义一个对象类型
  projectid NUMBER,
  projectname VARCHAR2(20),
  projectfunds NUMBER,
  pubdate DATE
);
DROP TABLE department PURGE ;
CREATE OR REPLACE TYPE project_nested AS TABLE OF project_type  NOT NULL ; --定义嵌套类型
INSERT INTO department (did,deptname,projects) VALUES (10,'name',  --插入数据
	project_nested(project_type(1,'hehe',8900,TO_DATE('2015-09-27','yyyy-mm-dd')) ,
                  project_type(2,'haha',13900,TO_DATE('2015-07-19','yyyy-mm-dd')))) ;
--更新嵌套表中的数据
UPDATE TABLE (SELECT projects FROM department WHERE did=10) pro SET VALUE(pro) = project_type(1,'hehe',69.8,TO_DATE('2013-06-26','yyyy-mm-dd'))
WHERE pro.projectid=1 ;
DELETE FROM TABLE(SELECT projects FROM department WHERE did=10) pro WHERE pro.projectid=1 ; --删除嵌套表中的数据
COMMIT;
SELECT * FROM TABLE (SELECT projects FROM department WHERE did=10) ; --查询结果

-- PLSQL 循环嵌套表示例

--FOR i IN v_projects.FIRST.. v_projects.LAST LOOP

可变数组:

<span style="font-weight: normal;">CREATE OR REPLACE TYPE project_varray AS VARRAY(3) OF VARCHAR2(50) ; --定义可以数组，varray(3)数组的长度，每个元素varchar2(50)
/
DROP TABLE department PURGE ;
CREATE TABLE department ( --创建表
	did		NUMBER ,
	deptname	VARCHAR2(30) 	NOT NULL ,
	projects 	project_varray ,
	CONSTRAINT pk_did PRIMARY KEY (did) --主键约束
) ;
INSERT INTO department(did,deptname,projects) VALUES (20,'name1',project_varray('AAA','BBB','CCC')) ; --插入可变数组
SELECT * FROM TABLE (SELECT projects FROM department WHERE did=20) ; --查询指定did下的 可变数组
UPDATE department SET projects=project_varray('CCC','BBB','AAA') WHERE did=20 ; --更新可变数组
COMMIT ;
--定义复合类型的可变数组
CREATE OR REPLACE TYPE project_type AS OBJECT(
	projectid		NUMBER ,
	projectname	VARCHAR(50),
	projectfunds	NUMBER ,
	pubdate		DATE
) ;
/
DECLARE
	TYPE project_varray IS VARRAY(3) OF project_type NOT NULL ;
	v_projects	project_varray := project_varray(
		project_type(10,'panpan',33.3,TO_DATE('2014-08-13','yyyy-mm-dd')) ,
		project_type(11,'wangwang',33.3,TO_DATE('2015-08-27','yyyy-mm-dd')) ,
		project_type(12,'lulu',33.3,TO_DATE('2011-03-19','yyyy-mm-dd'))) ;
BEGIN
	FOR x IN v_projects.FIRST .. v_projects.LAST LOOP
		DBMS_OUTPUT.put_line('编号：' || v_projects(x).projectid || '，名称：' || v_projects(x).projectname
			|| '，金额：' || v_projects(x).projectfunds || '，日期：' || v_projects(x).pubdate) ;
	END LOOP ;
END ;
/</span>

集合运算符：

DECLARE
  TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL;
  v_all list_nested := list_nested ('a','a','b','c','c','d','e') ; 
  v_a VARCHAR2(50) := 'a';
  v_allA		list_nested := list_nested ('abc','cba','ddd') ;
	v_allB		list_nested := list_nested ('abc','sss') ;
	v_newlist		list_nested ;
BEGIN
  DBMS_OUTPUT.put_line('集合长度：' || CARDINALITY(v_all)) ; --计算长度，不去重
  DBMS_OUTPUT.put_line('集合长度：' || CARDINALITY(SET(v_all))) ; --消除重复内容
  IF v_all IS NOT EMPTY THEN  -- 判空
		DBMS_OUTPUT.put_line('v_all不是一个空集合！') ;
	END IF ;
  IF v_a MEMBER OF v_all THEN  --member of 判断字符为 另一个字符串的子集
		DBMS_OUTPUT.put_line('a字符串存在。') ;
	END IF ;
  v_newlist := v_allA MULTISET EXCEPT v_allB ; --串A中在串B不存在的子串
  FOR i IN 1 .. v_newlist.COUNT LOOP  --循环输出串 v_newlist
		DBMS_OUTPUT.put_line(v_newlist(i)) ;
	END LOOP ;
  v_newlist := v_allA MULTISET INTERSECT v_allB ; --串A与串B交集 相同返回
	FOR x IN 1 .. v_newlist.COUNT LOOP
		DBMS_OUTPUT.put_line(v_newlist(x)) ;
	END LOOP ;
  v_newlist := v_allA MULTISET UNION v_allB ; --串A与串B并集
    FOR x IN 1 .. v_newlist.COUNT LOOP
      DBMS_OUTPUT.put_line(v_newlist(x)) ;
    END LOOP ;
  IF v_allA IS A SET THEN  --SET 判断是否是集合
		DBMS_OUTPUT.put_line('v_allA是一个集合。') ;
	END IF ;
  IF v_allB SUBMULTISET v_allA THEN   --submultiset 判断串B是否是串A的子集
		DBMS_OUTPUT.put_line('v_allB是v_allA的一个子集合。') ;
	END IF ;
END;
/

集合函数：

DECLARE
  TYPE list_nested IS TABLE OF VARCHAR2(50) NOT NULL; 
  v_all list_nested := list_nested ('a','a','b','c','c','d','e') ; 
  TYPE list_varray IS VARRAY(8) OF VARCHAR2(50) ;  --声明数组 长度8 类型varchar2
  v_info		list_varray := list_varray('1','2','3') ;
BEGIN
  DBMS_OUTPUT.put_line('集合长度：' || v_all.COUNT) ;--COUNT计算元素个数
  v_all.DELETE(1) ; 	-- 删除指定索引的数据 v_all.DELETE(1,3)范围删除
	FOR x IN v_all.FIRST .. v_all.LAST LOOP
		DBMS_OUTPUT.put_line(v_all(x)) ;
	END LOOP ;
  IF v_all.EXISTS(1) THEN --判断索引下的数据是否存在
		DBMS_OUTPUT.put_line('索引为1的数据存在。') ;
	END IF ;
  v_all.EXTEND(2) ;	-- 集合扩充2个长度 此扩充是无数据的 --v_all.EXTEND(2,1) ; 集合扩充2个长度，使用原始集合的第1个数据填充
	v_all(4) := 'android' ;
	v_all(5) := 'java' ;
  DBMS_OUTPUT.put_line('集合长度：' || v_all.COUNT) ; --扩充后的集合长度
  DBMS_OUTPUT.put_line('数组集合的最大长度：' || v_info.LIMIT) ;--声明长度为8
	DBMS_OUTPUT.put_line('数组集合的数据量：' || v_info.COUNT) ; --数组中元素个数
  DBMS_OUTPUT.put_line('索引为1的下一个索引是：' || v_all.NEXT(1)) ; --第一个索引的下一个索引是
  v_info.TRIM(2) ;	-- 删除2个数据之后还剩下1个 总共3个 ，由后向前删除数据
END;
/

使用FORALL批量：

DECLARE
	TYPE emp_varray IS VARRAY(8) OF emp.empno%TYPE ;
	v_empno		emp_varray := emp_varray(7369,7566,7788,7839,7902) ;
BEGIN
  --发送5次更新SQL
	--FOR x IN v_empno.FIRST .. v_empno.LAST LOOP 
	--	UPDATE emp SET sal=9000 WHERE empno=v_empno(x) ;
	--END LOOP ;
  --5条一次发送
  FORALL x IN v_empno.FIRST .. v_empno.LAST  --FORALL 进行批量绑定
		UPDATE emp SET sal=9000 WHERE empno=v_empno(x) ;
	FOR x IN v_empno.FIRST .. v_empno.LAST LOOP --输出
		DBMS_OUTPUT.put_line('雇员编号：' || v_empno(x) || '更新操作受影响的数据行为：' || SQL%BULK_ROWCOUNT(x)) ;--SQL%BULK_ROWCOUNT(x) 返回操作影响的数据行
	END LOOP ;
END;

/

使用BULK CONLLECT批量接收

DECLARE
	TYPE dept_nested IS TABLE OF dept%ROWTYPE ;
	v_dept		dept_nested ;
BEGIN
	SELECT * BULK COLLECT INTO v_dept FROM dept ;	-- 将雇员表全部数据拷贝到嵌套表之中
	FOR x IN v_dept.FIRST .. v_dept.LAST LOOP
		DBMS_OUTPUT.put_line('部门编号：' || v_dept(x).deptno || '，名称：' || v_dept(x).dname || '，位置：' || v_dept(x).loc) ;
	END LOOP ;
END ;
/

3 游标

游标简介:

将数据库查询出来的结果集存入内存中，由游标来控制操作。 所有的DML语句为隐式游标，通过隐式游标属性可以获取SQL语句的信息；用户显示声明的游标，即指定结果集的，当查询结果超过一行时，就需要一个显示的游标。以上两种成为静态游标。 REF游标：动态关联结果集的对象

隐式游标属性

No.	属性	描述
1	%FOUND	当用户使用DML操作数据时，该属性返回TRUE
2	%ISOPEN	判断游标是否打开，隐式游标返回FALSE，表示已经打开
3	%NOTFOUND	DML执行没有结果数据返回TRUE，否则FALSE
4	%ROWCOUNT	返回受影响的行数

隐式游标：

DECLARE
	v_count		NUMBER ;
BEGIN
	SELECT COUNT(*) INTO v_count FROM dept ;	-- 只返回一行结果
	DBMS_OUTPUT.put_line('SQL%ROWCOUNT = '|| SQL%ROWCOUNT) ; --SQL%ROWCOUNT 返回所影响的行

UPDATE emp SETsal=sal*1.2 ;         

IF SQL%FOUND THEN              -- 发现数据                   

DBMS_OUTPUT.put_line('更新记录行数：' ||SQL%ROWCOUNT) ;         

ELSE                   DBMS_OUTPUT.put_line('没有记录被修改！') ;        

END IF ;

END ;
/

游标存入索引表:

DECLARE
	CURSOR cur_emp IS 
		SELECT * FROM emp ;			-- 定义游标取得emp表数据
	TYPE emp_index IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER ;-- 定义索引表数据类型为emp行结构
	v_emp	emp_index ;				-- 定义索引表变量
BEGIN
	FOR emp_row IN cur_emp LOOP			-- 利用循环取得每一行记录
		v_emp(emp_row.empno) := emp_row ;	-- 将雇员编号作为索引表下标
	END LOOP ;
	DBMS_OUTPUT.put_line('雇员编号：' || v_emp(7369).empno || '，姓名：' || v_emp(7369).ename || '，职位：' || v_emp(7369).job) ;
END ;
/

定义参数游标：

DECLARE
	CURSOR cur_emp (p_dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=p_dno;
BEGIN
	FOR emp_row IN cur_emp(&inputDeptno) LOOP
		DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || emp_row.ename || '，职位：' || emp_row.job || '，工资：' || emp_row.sal) ;
	END LOOP ;
END ;
/

--FETCH cur_dept BULK COLLECT INTO v_dept ; 保存整个游标
--FETCH cur_dept BULK COLLECT INTO v_dept LIMIT v_rows ;     -- limit 保存指定数组的行数
--CURSOR cur_emp IS SELECT * FROM emp WHERE deptno=10 FOR UPDATE OF sal,comm. ; 为游标增加行级锁 FOR UPDATE OF[列]  FOR UPDATE NOTWAIT 不等待游标
--WHERE CURRENT OF cur_emp 更新当前行游标的数据