1,什么是plsql:
是专用于Oracle服务器,在SQL基础之上,添加了一些过程化控制语句,叫PLSQL过程化包括有:类型定义,判断,循环,游标,异常或例外处理。
PLSQL强调过程
2,Plsql语句块:
PL/SQL程序都是以块(block)为基本单位,整个PL/SQL块分三部分:
- 声明部分(用declare开头)
- 执行部分(以 begin开头)其中执行部分是必须的,其他两个部分可选
- 异常处理部分(以exception开头)
- 结束:end
--基本输出语句:
BEGIN
dbms_output.put_line('hello,world');
END;
--基本输出语句
BEGIN
dbms_output.put_line('hello,world');
dbms_output.put_line(157);
dbms_output.put_line(sysdate);
dbms_output.put_line(true);--不能传入布尔值
END;
3,Plsql语句块分类:
- 匿名块:动态构造只执行一次(main)
- 子程序:存储在数据库中的存储过程,函数及包等。当在数据库建立好后可以在其他子程序中调用
- 触发器:当数据库发生操作时,会触发一些事件,从而自动执行相应的程序
4,Plsql中的变量类型:
5,变量的使用:
plsql中声明和执行部分要严格分开,在java中时先声明后写变量plsql中相反 先声明在写数据类型:
变量在声明后没有赋值输出结果为空:
变量的声明和初始化:(boolean中只能声明不能输出)
DECLARE
i NUMBER(4) := 157;
j NUMBER(6);
c VARCHAR2(200) := 'HELLO,WORLD';
d DATE := sysdate;
b BOOLEAN := TRUE;
BEGIN
j := 1000;--只能写在执行部分
dbms_output.put_line('i=' || i);
dbms_output.put_line('j=' || j);
dbms_output.put_line('c=' || c);
dbms_output.put_line('d=' || d);
END;
在声明块中对常量的声明:
变量名称[constant] type [not null] [:value]
DECLARE
c CONSTANT NUMBER(4) := 100;
c2 NUMBER(4) NOT NULL := 101;
BEGIN
--c := 101; --常量不能重复赋值
dbms_output.put_line('c=' || c);
dbms_output.put_line('c2=' || c2);
END;
--空语句 NULL 执行语句中必须写东西
DECLARE
c CONSTANT NUMBER(4) := 100;
c2 NUMBER(4) NOT NULL := 101;
BEGIN
NULL;--空语句,作为占位符使用
END;
6,PLSQL中使用sql:
--更新100员工,工资增加1块钱:(不能再执行块中直接输出select语句)
- 声明变量:员工的编号
- 声明变量:工资追加的钱数
DECLARE
v_empid BINARY_INTEGER := 100;
v_money BINARY_INTEGER := 1;
BEGIN
UPDATE employees SET salary=salary+v_money
WHERE employee_id=v_empid;
COMMIT;
END;
DML或DCL语句:直接执行 PLSQL中使用SQL语句的几种情况:
- 查询语句:SELECT...INTO 或者 游标
- DDL语句:动态SQL执行
7,SELECT...INTO...的使用:
语法:SELECT 列1,列2,…… INTO 变量1,变量2,……
--查询某个员工编号的姓名和工资:
- 员工编号
- 姓名
- 工资
列和变量的顺序,个数必须保持一致(如果没有数据则会抛出异常,返回的结果超过一行 也会有异常)
DECLARE
v_empid BINARY_INTEGER := 101;
v_name VARCHAR2(50);
v_salary NUMBER(8,2);
BEGIN
SELECT last_name,salary
INTO v_name,v_salary
FROM employees
WHERE employee_id=v_empid;
dbms_output.put_line(v_empid || ',' || v_name || ',' || v_salary);
END;
--查询某个员工编号的姓名,工资,入职日期,部门编号:(以上思路 我们需要声明多个变量)
DECLARE
v_empid BINARY_INTEGER := 101;
v_name VARCHAR2(50);
v_salary NUMBER(8,2);
v_hiredate DATE;
v_deptid BINARY_INTEGER;
BEGIN
SELECT last_name,salary,hire_date,department_id
INTO v_name,v_salary,v_hiredate,v_deptid
FROM employees
WHERE employee_id=v_empid;
dbms_output.put_line(v_empid || ',' || v_name || ',' || v_salary || ',' || v_hiredate || ',' || v_deptid);
END;
java中处理多个数据我们可以封装为对象:
plsql中可以声明记录类型:时把逻辑相关的数据作为一个单元存储起来:
--记录类型record
--查询某个员工编号的姓名,工资,入职日期,部门编号
DECLARE
TYPE emp_record_type IS RECORD (
empid BINARY_INTEGER := 102,
ename VARCHAR2(50),
salary NUMBER(8,2),
hiredate DATE,
deptid BINARY_INTEGER
);
e emp_record_type;
BEGIN
SELECT employee_id,last_name,salary,hire_date,department_id
INTO e
FROM employees
WHERE employee_id=e.empid;
dbms_output.put_line(e.empid);
dbms_output.put_line(e.ename);
dbms_output.put_line(e.salary);
dbms_output.put_line(e.hiredate);
dbms_output.put_line(e.deptid);
END;
--参照引用类型 --参照引用类型:参照已有的数据类型
--参照引用变量类型:变量名称%TYPE
--参照引用表中的列类型:表名.列名%TYPE
--参照引用表的记录类型:表名%ROWTYPE
demo:
DECLARE
i NUMBER(4);
j i%TYPE := 100;--参照i的类型
k employees.employee_id%TYPE := 101;--参照员工表中员工编号的数据类型
BEGIN
dbms_output.put_line('j=' || j);
dbms_output.put_line('k=' || k);
END;
--查询某个员工编号的姓名,工资,入职日期,部门编号(参照类型)
DECLARE
TYPE emp_record_type IS RECORD (
empid employees.employee_id%TYPE := 102,
ename employees.last_name%TYPE,
salary employees.salary%TYPE,
hiredate employees.hire_date%TYPE,
deptid employees.department_id%TYPE
);
e emp_record_type;
BEGIN
SELECT employee_id,last_name,salary,hire_date,department_id
INTO e
FROM employees
WHERE employee_id=e.empid;
dbms_output.put_line(e.empid);
dbms_output.put_line(e.ename);
dbms_output.put_line(e.salary);
dbms_output.put_line(e.hiredate);
dbms_output.put_line(e.deptid);
END;
--查询某个员工编号的姓名,工资,入职日期,部门编号(参照引用表的记录类型)
DECLARE
e employees%ROWTYPE;
BEGIN
e.employee_id := 103;
SELECT *
INTO e
FROM employees
WHERE employee_id=e.employee_id;
dbms_output.put_line(e.employee_id);
dbms_output.put_line(e.last_name);
dbms_output.put_line(e.salary);
dbms_output.put_line(to_char(e.hire_date,'yyyy-mm-dd'));
dbms_output.put_line(e.department_id);
END;
--PLSQL表类型
TYPE 自定义类型名称 IS TABLE OF 元素类型
INDEX BY BINARY_INTEGER;
DECLARE
TYPE name_table_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
n name_table_type;
BEGIN
n(-7) := 'tom';
n(4) := 'jack';
n(9) := 'rose';
dbms_output.put_line('元素的长度:' || n.count);
dbms_output.put_line(n(4));
END;
--表类型的常用属性
DECLARE
TYPE name_table_type IS TABLE OF VARCHAR2(50)
INDEX BY BINARY_INTEGER;
n name_table_type;
BEGIN
--设置元素
n(-5) := 'tom';
n(3) := 'jack';
n(17) := 'rose';
n(23) := 'zhang';
n(24) := 'wangwu';
--删除指定下标的元素
--n.delete(17);
--删除一个范围的元素
--n.delete(10,24);
--删除所有元素
--n.delete;
--返回元素长度
dbms_output.put_line('元素个数:' || n.count);
--判断下标是否存在
IF n.exists(17) THEN
dbms_output.put_line('true');
ELSE
dbms_output.put_line('false');
END IF;
--输出最小下标
dbms_output.put_line(n(n.first));
--输出最大下标
dbms_output.put_line(n.last);
--返回指定下标的元素
dbms_output.put_line(n(3));
--返回上一个或下一个存在的下标,如果没有,返回NULL
dbms_output.put_line(n.next(10));
dbms_output.put_line(n.prior(10));
END;
--BULK COLLECT INTO:把查询结果一次性赋给一个表类型的变量,下标自动从1开始递增
--查询所有的员工姓名,存储到一个表类型的变量中
DECLARE
TYPE name_table_type IS TABLE OF VARCHAR2(50)
INDEX BY BINARY_INTEGER;
n name_table_type;
BEGIN
SELECT BILL_CODE BULK COLLECT INTO n
FROM CONTRACT_BILLING_INFO;
dbms_output.put_line('元素长度:' || n.count);
dbms_output.put_line(n(2));
END;
--查询所有的员工信息,存储到一个表类型的变量中
DECLARE
TYPE emp_table_type IS TABLE OF employees%ROWTYPE
INDEX BY BINARY_INTEGER;
e emp_table_type;
BEGIN
SELECT * BULK COLLECT INTO e
FROM employees;
dbms_output.put_line('元素长度:' || e.count);
dbms_output.put_line(e(2).last_name);
dbms_output.put_line(e(3).salary);
END;
8,DML语句返回值:
--RETURNING语句:DML操作返回值赋给变量
RETURN 列1,列2,... INTO 变量1,变量2,...
--更新某个员工的工资,输出这个员工姓名,新工资
DECLARE
v_empid employees.employee_id%TYPE := 100;
v_name employees.last_name%TYPE;
v_money employees.salary%TYPE := 1;
v_salary employees.salary%TYPE;
BEGIN
UPDATE employees
SET salary=salary+v_money
WHERE employee_id=v_empid
RETURNING last_name,salary
INTO v_name,v_salary;
dbms_output.put_line('姓名:' || v_name);
dbms_output.put_line('新工资:' || v_salary);
END;
--插入语句中使用RETURNING语句:
DECLARE
d dept%ROWTYPE;
BEGIN
INSERT INTO dept VALUES (50,'AA','BB')
RETURN deptno,dname,loc INTO d;
dbms_output.put_line(d.deptno);
dbms_output.put_line(d.dname);
dbms_output.put_line(d.loc);
END;
--删除语句中使用RETURNING语句
DECLARE
d dept%ROWTYPE;
BEGIN
DELETE FROM dept
WHERE deptno=50
RETURN deptno,dname,loc INTO d;
dbms_output.put_line(d.deptno);
dbms_output.put_line(d.dname);
dbms_output.put_line(d.loc);
END;
--DML操作返回多行数据
--更新某个部门的员工的工资,返回被更新的员工姓名,新工资
DECLARE
TYPE emp_record_type IS RECORD (
ename employees.last_name%TYPE,
salary employees.salary%TYPE
);
TYPE emp_table_type IS TABLE OF emp_record_type
INDEX BY BINARY_INTEGER;
e emp_table_type;
BEGIN
UPDATE employees
SET salary=salary+1
WHERE department_id=50
RETURN last_name,salary BULK COLLECT INTO e;
dbms_output.put_line('更新了' || e.count || '个员工');
dbms_output.put_line(e(1).ename || ',' || e(1).salary);
END;