PL/SQL初级学习笔记

最新推荐文章于 2024-04-18 15:18:05 发布

suer_yh

最新推荐文章于 2024-04-18 15:18:05 发布

阅读量804

点赞数

本文链接：https://blog.csdn.net/weixin_42694672/article/details/81254067

版权

PLSQL的块

Plsql的块分为三种：匿名块、存储过程、函数。

匿名块declare：

begin

……………………….

end;

存储过程Procedure：

Procedure name is

begin

……………………

End;

函数function：

Function name return datatype is

Begin

………………………….

End;

声明变量语法：

变量名变量类型（长度）：= 变量初始值（可以没有）

比如：

Declare

v_hiredate date;

v_number number(2) := 10;

v_char varchar2(10) := ‘book’;

…………

也可以按照数据库的字段类型声明：

变量 table.colum_name%type

比如

Declare

v_name employees.last_name%type

…………

变量命名规则：

1、变量命名一般规则为v_表示一个变量，c_表示一个常量

2、变量命名时避免与数据库中的字段名相同

PLSQL使用sql语句：

将查询到的值赋予变量

DECLARE

v_last_name employees.last_name%TYPE;

BEGIN

SELECT last_name

INTO v_last_name

FROM employees

WHERE employee_id = 100;

dbms_output.put_line(v_last_name);

END;

DECLARE

v_last_name employees.last_name%TYPE;

v_e_id employees.employee_id%TYPE := 100;

BEGIN

SELECT last_name

INTO v_last_name

FROM employees

WHERE employee_id = v_e_id;

dbms_output.put_line(v_last_name);

END;

需要注意的是，into的值只能赋予一个值，若where语句使用不当，导致出现多个值，则会报出多行的错误

PLSQL的控制语句

判断语句：

if 条件 then 结果;

elsif 条件 then 结果;

else 结果;

end if;

对null的判断：

And:

true and true = true,

false and false = false

true and false = false,

ture and null = null ,

false and null = false;

or:

true or true = true

true or false = true

false or false = false

false or null = null

true or null = true

循环语句：

Loop循环：

Loop

Statement1 (代码);

……

Exit (结束条件);

End loop;

While循环：

While condition(运行条件) loop

Statement(代码)

……

End loop;

For循环：

FOR i IN 1..3 LOOP

INSERT INTO locations(location_id, city, country_id)

VALUES((v_location_id + i), v_city, v_country_id );

END LOOP;

PLSQL自定义数据类型

Type typename is record

(name type.

name type

………………);

记录整张表的类型

变量 tablename%rowtype

游标

常用属性：

Sql%rowcount 受最近的SQL语句影响的行数

Sql%found 最近的SQL语句是否影响了一行以上的数据

Sql%notfound 最近的SQL语句是否未影响任何数据

Sql%isopen 对于隐式游标而言永远为FALSE

游标的使用：

Cursor cursorname is select....from,,,,,

Begin

Open cursorname 打开游标

Loop 遍历游标

Fetch cursorname 获取游标当前值

Into 变量赋予变量

Exit when cursorname%notfound 当游标遍历完以后

End loop; 结束循环

End;

例子：

DECLARE

v_employee_id employees.employee_id%TYPE;

CURSOR emp_cur IS

SELECT employee_id

FROM employees;

BEGIN

OPEN emp_cur;

LOOP

FETCH emp_cur

INTO v_employee_id;

EXIT WHEN emp_cur%NOTFOUND;

dbms_output.put_line(v_employee_id);

END LOOP;

END;

使用For循环遍历游标：

For recordname in cursorname

Loop

If recordname.()………….

For update nowait: 锁定更新目标，防止锁定失败时出现长时间等待资源。

用法：

For update of 列名 nowait;

Where current of cursor:更新或删除数据时，快速指向游标里面的内容

使用方法

FOR emp_record IN sal_cursor

LOOP

IF emp_record.salary < 5000

THEN

UPDATE employees

SET salary = emp_record.salary * 1.10

WHERE CURRENT OF sal_cursor;

END IF; END LOOP;

PLSQL例外处理

例外有两种：

oracle内部错误抛出异常：预定义（有错误号和常量定义），非预定义（只有错误号）；
程序员自己显式抛出例外

例外捕获使用when………then……

例如：

When no_data_found then

Statement;

When too_many_rows then

Statement;

When others then

Statement;

………

End;

Oracle提供了两个内置函数来获取错误号和错误描述

Sqlcode、sqlerrm

使用方法：

When others then

Rollback;

Intsert into errors

Values(sqlcode,sqlerrm);

End;

非预定义的oracle错误

例子：

E_emp exception;

Pragma exception_init(e_emp,-2292);

Begin

Exception

When e_emp then

……

End;

Oracle内部错误号很多，想了解全部的Ora错误号，请参考：http://www.ora-code.com/

自定义错误：

例子：

E_emp exception;

Begin

Raise e_emp;

Exception

When e_emp then

Dbms_output.put_line();

End;

RAISE_APPLICATION_ERROR() 函数：对于用户自定义的业务错误，如果觉得先定义再使用很麻烦，那么也可以简单的使用raise_application_error() 来简化处理。它可以无需预先定义错误，而在需要抛出错误的地方直接使用此函数抛出例外，例外可以包含用户自定义的错误吗和错误描述

例子：

RAISE_APPLICATION_ERROR(-20202,'This is not a valid manager');

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR (-20201,'Manager is not a valid employee.');

END;

PLSQL存储过程和函数

存储过程：

例子：

CREATE OR REPLACE PROCEDURE raise_salary (p_id IN employees.employee_id%TYPE)

BEGIN

UPDATE employees

SET salary = salary * 1.10

WHERE employee_id = p_id;

END raise_salary; /

过程的例外处理：

函数：

CREATE OR REPLACE FUNCTION get_sal(p_id IN employees.employee_id%TYPE) RETURN NUMBER IS

v_salary employees.salary%TYPE :=0;

BEGIN

SELECT salary

INTO v_salary

FROM employees

WHERE employee_id = p_id;

RETURN v_salary;

END get_sal; /

跟存储过程唯一的区别就是函数有一个return值。

哪些SQL语句中可以使用用户自定义的函数：

Select 语句 
Where条件和Having子句 
CONNECT BY, START WITH, ORDER BY, 和GROUP BY 子句 
INSERT的Values子句 
UPDATE的Set子句

想要在SQL语句中可以使用用户自定义的函数，那么这样的用户定义函数有哪些限制？

必须是个函数（不能是过程-Procedure） 
只能用IN 模式的参数（不能有OUT, IN OUT 模式的参数） 
只能接收SQL数据类型的参数，不能接收PLSQL 中特有的参数（比如记录、PLSQL内存表） 
函数返回的数据类型也必须是有效的数据类型，而不能是PLSQL特有的数据类型 
在SQL中使用的函数，其函数体内部不能有DML语句。 
在UPDATE/DELETE语句中调用的函数，其函数体内部不能有针对同一张表的查询语
在SQL中调用的函数，其函数体内部不能有事务结束语句（比如Commit，Rollback)

PLSQL中的包PACKAGE

Package概念：按照业务逻辑、把相关的Func , Procedure 组织到一起，形成一个函数或者过程集合，这就是一个Package，这是PLSQL中程序的一种组织形式。也是我们写PLSQL最主要的形式

Package组成：Package由包说明（package Specification)和包体(package body)两部分构成; 包说明部分相当于C语言里面的.H文件，包体部分相当于 C语言里面针对.H实现的C文件。

Package好处：

1、模块化：一般把有相关性的函数和过程放到一个Package中；

2、易设计：可以把包说明和包体分别编写和编译，先编写和编译包说明部分，在编写和说明包体部分；这有利于分工合作；

3、信息隐藏：包体中函数可以部分出现在包说明中，只有出现在包说明中的函数和过程才是该Package的公有函数和过程，可以被其他包中的函数调用，否则对其他包中的函数是不可见的，未在包说明部分出现的函数和过程相当于私有的。

4、加载性能提高：当Package中有一个函数或过程被调用时，整个Packege就被加载到内存中，这样当该Package中其他函数被调用时，就直接从内存读取了，可以减少磁盘IO,从而提高性能。这个特性也提醒我们不要去搞巨无霸的Package, 把你用到的任何函数都写到一个Package中，这会导致严重的内存浪费。

5、重载：一个package 中可以定义同名、不同参数的函数或过程。

包说明声明公有变量以及共有过程等

包体则用来写具体的过程等

内置PLSQL工具包

动态sql：可以拼凑的sql语句，使用bdms_sql或者execute immediate语句来执行。

使用execute immediate时 into不能放在sql字符串内

程序中执行DDL：如果想在程序中执行DDL,可使用Oracle 内置包：DBMS_DDL

比如在程序中执行编译命令：

DBMS_DDL.ALTER_COMPILE('PROCEDURE','A_USER','QUERY_EMP')；

比如在程序中执行数据收集命令：

DBMS_DDL.ANALYZE_OBJECT('TABLE','A_USER','JOBS','COMPUTE')

Oracle数据库JOB：定义JOB 可以定期执行某个程序。

Oracle提供内置包 DBMS_JOB，可完成JOB的定义、提交、更改、停止、移除。

例子：：提交一个JOB 每隔1天执行一次：

DECLARE jobno NUMBER;

BEGIN

DBMS_JOB.SUBMIT (

job => jobno ,

what => 'OVER_PACK.ADD_DEPT(''EDUCATION'',2710);',

next_date => TRUNC(SYSDATE + 1),

interval => 'TRUNC(SYSDATE + 1)'

);

dbms_output.put_line(„job_no =„ || jobno )

COMMIT;

END;

例子：更改JOB的执行频率为：每4小时执行一次：

BEGIN

DBMS_JOB.CHANGE(1, NULL, TRUNC(SYSDATE+1)+6/24, ‟SYSDATE+4/24');

END;

如何找到自己提交的job号：

SELECT job, log_user, next_date, next_sec, broken, what

FROM DBA_JOBS;

数据库触发器

Trigger

时机：Before 或者 After 或 Instead of

事件：Insert 或 Update 或 Delete

对象：表名（或视图名）

类型：Row 或者 Statement级；

条件：满足特定Where条件才执行；

内容：通常是一段PLSQL块代码；

重点注意：

Instead of : 用Trigger的内容替换事件本身的动作

Row级：SQL语句影响到的每一行都会引发Trigger

Statement级：一句SQL语句引发一次，不管它影响多少行（甚至0行）

插入之前执行触发器例子：

CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT ON employees

BEGIN

IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))

(TO_CHAR(SYSDATE,'HH24:MI') NOT BETWEEN '08:00' AND '18:00')

THEN

RAISE_APPLICATION_ERROR (-20500,'You may insert into EMPLOYEES table only during business hours.');

END IF;

END; /

suer_yh

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL初级学习笔记

PLSQL的块Plsql的块分为三种：匿名块、存储过程、函数。匿名块declare：begin ……………………….end; 存储过程Procedure：Procedure name isbegin……………………End; 函数function：Function name return datatype isBegin……………………...
复制链接

扫一扫