立此存照
匿名PL/SQL
语法结构：PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL程序包含了一个或多个逻辑块，每一块都可以划分3个部分。变量在使用前必须声明，PL/SQL提供了独立的专门用于处理异常的部分。
在PL/SQL块中可以使用SELECT INSERT UPDATE DELETE等DML语句、事务控制语句以及SQL函数等，不允许直接使用CREATE DROP或者ALERT等DDL语句，但可以通过动态SQL来执行。
三个部分为：
1、 声明部分：声明部分包含了块中使用的变量、游标、自定义异常，由关键字DECLARE开始。如果不需要声明变量或常量，可以忽略这部分。
2、 执行部分：是PL/SQL块中的指令部分，由关键字BEGIN开始，END结束，这部分是必选项。
3、 异常处理部分：可选，处理异常或错误。
语法：
[DECLARE]
DECLARATION STATEMENTS
BEGIN
EXECUTABLE STATEMENTS
[EXCEPTION]
EXCEPTION STATEMENTS
END;
变量的声明和赋值 --------- PL/SQL是强类型语言，变量在使用前必须声明。
声明语法：VAR_NAME [CONSTANT] DATETYPE [NOT NULL] [:=|DEFAULT VALUE]
VAR_NAME:表示变量名称 datetype：表示变量的SQL或PL/SQL数据类型 VAULE：表示变量的初始值
NOT NULL 可选，表示给变量强制地加约束条件，此时变量必须初始化。
CONSTANT 可选，表示常量。VALUE在声明时必须初始化。且常量的值在程序内部不能改变。
赋值：1、varname:=expression; 2、通过SELECT INTO给变量赋值
示例：
DECLARE
SDNAME VARCHAR2(20);
BEGIN
SDNAME:=’JOHN’;
END;
示例：
DECLARE
SDNAME VARCHAR2(20);
BEGIN
SELECT ENAME INTO SDNAME FROM EMP WHERE EMPNO=’7902’;
END;
属性用于引用变量或数据库列的数据类型，以及表中的一行数据。
例:属性用于引用变量的数据类型 %TYPE
--查询员工SMITH的所有信息
DECLARE
BIANHAO EMP.EMPNO%TYPE;
XINGMING EMP.ENAME%TYPE;
GONGZUO EMP.JOB%TYPE;
LINGDAO EMP.ENAME%TYPE;
RUZHISHIJIAN EMP.HIREDATE%TYPE;
GONGZI EMP.SAL%TYPE;
JIANGJIN EMP.COMM%TYPE;
BUMENG DEPT.DNAME%TYPE;
BEGIN
SELECT E.EMPNO,E.ENAME,E.JOB,(SELECT ENAME FROM EMP WHERE EMPNO=E.MGR ),
E.HIREDATE,E.SAL,NVL(E.COMM,0),(SELECT DNAME FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO)
INTO BIANHAO,XINGMING,GONGZUO,LINGDAO,RUZHISHIJIAN,GONGZI,JIANGJIN,BUMENG FROM EMP E WHERE ENAME='SMITH';
DBMS_OUTPUT.PUT_LINE('员工编号:'||BIANHAO);
DBMS_OUTPUT.PUT_LINE('姓名:'||XINGMING);
DBMS_OUTPUT.PUT_LINE('工种:'||GONGZUO);
DBMS_OUTPUT.PUT_LINE('领导:'||LINGDAO);
DBMS_OUTPUT.PUT_LINE('入职时间:'||RUZHISHIJIAN);
DBMS_OUTPUT.PUT_LINE('工资:'||GONGZI);
DBMS_OUTPUT.PUT_LINE('奖金:'||JIANGJIN);
DBMS_OUTPUT.PUT_LINE('所属部门:'||BUMENG);
END;
另一种属性类型%ROWTYPE
例：
--创建视图
CREATE VIEW EMP_VIEW AS
SELECT EMPNO,ENAME,JOB,(SELECT ENAME FROM EMP WHERE EMPNO=E.MGR ) MGR,HIREDATE,SAL,NVL(COMM,0) COMMM,
(SELECT DNAME FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO) DNAME FROM EMP E;
--查询视图
SELECT * FROM EMP_VIEW;
--使用%ROWTYPE属性
DECLARE
EMP_SOURCE EMP_VIEW%ROWTYPE;--声明变量 EMP_SOURCE变量保存的是视图某一行的所有对象
BEGIN
SELECT * INTO EMP_SOURCE FROM EMP_VIEW WHERE EMP_VIEW.ENAME='SMITH';
DBMS_OUTPUT.PUT_LINE('员工编号:'||EMP_SOURCE.EMPNO);
DBMS_OUTPUT.PUT_LINE('姓名:'||EMP_SOURCE.ENAME);
DBMS_OUTPUT.PUT_LINE('工种:'||EMP_SOURCE.JOB);
DBMS_OUTPUT.PUT_LINE('领导:'||EMP_SOURCE.MGR);
DBMS_OUTPUT.PUT_LINE('入职时间:'||EMP_SOURCE.HIREDATE);
DBMS_OUTPUT.PUT_LINE('工资:'||EMP_SOURCE.SAL);
DBMS_OUTPUT.PUT_LINE('奖金:'||EMP_SOURCE.COMMM);
DBMS_OUTPUT.PUT_LINE('所属部门:'||EMP_SOURCE.DNAME);
END;
--变量直接赋值
--部门编号 名称 地址 使用insert 语句
CREATE VIEW DEPT_VIEW AS SELECT * FROM DEPT;--创建视图
SELECT * FROM DEPT_VIEW;--查询视图
--向视图里插入数据
DECLARE
DEPTNO DEPT_VIEW.DEPTNO%TYPE;
DNAME DEPT_VIEW.DNAME%TYPE;
LOC DEPT_VIEW.LOC%TYPE;
BEGIN
DEPTNO:=50;
DNAME:='JISHUBU';
LOC:='SHANGHAI';
INSERT INTO DEPT_VIEW VALUES (DEPTNO,DNAME,LOC);
END;
循环控制语句用于重复执行一系列语句，包括loop和exit语句，使用exit语句可以立即退出循环，使用exit when语句可以根据条件结束循环。
循环共分3种类型：loop循环：在loop和end loop之间的一系列语句，为避免陷入无限循环，loop循环中必须使用exit和exit when语句。
语法：loop
seq_of_statements;
exit(exit when);
end loop;
---loop循环
DECLARE
X NUMBER:=1000;
Y NUMBER;
BEGIN
LOOP
X:=X-10;
Y:=X;
DBMS_OUTPUT.put_line(Y);
DBMS_OUTPUT.PUT_LINE(X);
EXIT WHEN X<100;
END LOOP;
END;
PL/SQL支持的内置数据类型：
数据类型 标量类型：数字 字符 布尔型 日期时间
ＬＯＢ类型 BFILE BLOB CLOB NCLOB
属性类型 %type 提供某个变量或数据库表列的数据类型 %rowtype 提供表示表中一行的记录类型
使用属性类型的优点：1、不需要知道被引用的列或表的具体数据类型
2、如果更改了被引用对象的数据库定义，那么PL/SQL在运行时变量的数据类型也随之更改
|
名称 |
类型 |
说明 |
|
NUMBER |
数字型 |
用于存储整数、定点数和浮点数，可以定义精度和取值范围 |
|
BINARY_INTEGER |
数字型 |
用于存储带符号整数 |
|
INTEGER |
数字型 |
NUMBER的子类型，用于声明高精度为38位的十进制整数 |
|
REAL |
数字型 |
NUMBER的子类型，用于声明高精度为63位的二进制浮点数 |
|
FLOAT |
数字型 |
NUMBER的子类型，用于声明高精度为126位的二进制浮点数 |
|
CHAR |
字符型 |
用于存储固定长度字符，指定不超过32767个字节的长度 |
|
VARCHAR2 |
字符型 |
用于存储可变长度字符，指定不超过32767个字节的长度 |
|
DATE |
日期型 |
用于存储日期和时间值 |
|
BOOLEAN |
布尔型 |
用于存储逻辑值，TRUE、FALSE和NULL |
|
BFILE |
LOB类型 |
该数据类型用于将大型二进制对象存储在系统文件中，最大存储4GB |
|
BLOB |
LOB类型 |
该数据类型用于将大型二进制对象存储在数据库中，最大存储4GB |
|
CLOB |
LOB类型 |
该数据类型用于将大型字符数据存储在数据库中，最大存储4GB |
|
NCLOB |
LOB类型 |
该数据类型用于将大型NCHAR数据存储在数据库中，最大存储4GB |
运算符
|
运算符类型 |
运算符 |
示例 |
说明 |
|
算术运算符 |
+、- |
a+b，a-b |
分别计算a和b的和与a和b的差 |
|
*、/ |
a*b,a/b |
分别计算a和b的乘积与a和b的商 |
|
|
关系运算符 |
= |
num1=num2 |
如果num1等于num2，则返回true |
|
<>,!= |
num1<>num2 |
如果num1不等于num2，则返回true |
|
|
< |
num1<num2 |
如果num1小于num2，则返回true |
|
|
> |
num1>num2 |
如果num1大于num2，则返回true |
|
|
<= |
num1<=num2 |
如果num1小于等于num2，则返回true |
|
|
>= |
num1>=num2 |
如果num1大于等于num2，则返回true |
|
|
逻辑运算符 |
NOT |
NOT True |
取反的逻辑值 |
|
AND |
TRUE AND TRUE |
两个为真则结果为真 |
|
|
OR |
TRUE OR FALSE |
只要一个为真则结果为真 |
|
|
其他 |
/* */ |
多行注释符 |
|
|
-- |
单行注释符 |
||
|
|| |
‘abc’||’def’ |
连接运算符 |
|
|
:= |
a:=10 |
赋值运算符 |
流程控制语句：条件控制、循环控制、顺序控制
条件控制语句包括IF语句和CASE语句
IF语句主要有3种形式：IF-THEN IF-THEN-ELSE IF-THEN-ELSIF
例1：
DECLARE
DEPTNO EMP.DEPTNO%TYPE;
BEGIN
SELECT DEPTNO INTO DEPTNO FROM EMP WHERE EMPNO=’7902’;
IF DEPTNO=30 THEN
UPDATE EMP SET SAL=SAL*1.1
WHERE EMPNO=’7902’;
DBMS_OUTPUT.PUT_LINE(‘薪水上升10%’);
ELSE --------关键字ELSE后面不能加THEN条件语句
DBMS_OUTPUT.PUT_LINE(‘薪水保存不变’);
END IF;
END;
例2：
DECLARE
deptno emp.deptno%type;
BEGIN
SELECT deptno INTO deptno FROM emp
WHERE empno='7902';
IF deptno=30 THEN
UPDATE emp SET sal=sal*1.1
WHERE empno='7902';
DBMS_OUTPUT.PUT_LINE('薪水上升10%');
ELSIF deptno=20 THEN ----关键字ELSIF后面要跟THEN语句
UPDATE emp SET sal=sal*1.2
WHERE empno='7902';
DBMS_OUTPUT.PUT_LINE('薪水上升20%');
ELSE
UPDATE emp SET sal=sal*1.3
WHERE empno='7902';
DBMS_OUTPUT.PUT_LINE('薪水上升30%');
END IF;
END;
CASE语句 用于根据单个变量或表达式与多个值进行比较
1、 执行CASE语句前，先计算选择器的值
BEGIN
CASE &deptno ----&表示接收一个输入的数据
WHEN 10 THEN DBMS_OUTPUT.PUT_LINE('ACCOUNTING');
WHEN 20 THEN DBMS_OUTPUT.PUT_LINE('RESEARCH');
WHEN 30 THEN DBMS_OUTPUT.PUT_LINE('SALES');
WHEN 40 THEN DBMS_OUTPUT.PUT_LINE('OPERATIONS');
ELSE DBMS_OUTPUT.PUT_LINE('此部门编号不存在');
END CASE;
END;
2、 CASE另一种用法--没有选择器
DECLARE
deptno number;
BEGIN
deptno:=&deptno; -----不加单引号时，默认为数字类型，加单引号才能输入字符’&string’
CASE
WHEN deptno=10 THEN DBMS_OUTPUT.PUT_LINE('ACCOUNTING');
WHEN deptno=20 THEN DBMS_OUTPUT.PUT_LINE('RESEARCH');
WHEN deptno=30 THEN DBMS_OUTPUT.PUT_LINE('SALES');
WHEN deptno=40 THEN DBMS_OUTPUT.PUT_LINE('OPERATIONS');
ELSE DBMS_OUTPUT.PUT_LINE('此部门编号不存在');
END CASE;
END;
FOR循环中循环次数是已知的。循环计数器变量需要事先声明，可将循环计数器作为常量引用。在for循环语句序列中不能给计数器变量赋值。
for counter in [reverse] value1..value2 loop seq_of_statements; end loop;
关键字reverse 只有在需要从大到小执行循环时才会使用。
--FOR循环
BEGIN
FOR COUNTS IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(COUNTS);
END LOOP;
END;
while循环：将条件与一系列语句结合在一起，根据条件执行语句，此系列语句包含在关键字loop和end loop之中。
--while
DECLARE
VOU NUMBER:=1;
BEGIN
WHILE VOU <= 10
LOOP
DBMS_OUTPUT.PUT_LINE(VOU);
VOU:=VOU+1;
END LOOP;
END;
异常：在运行程序时出现的错误叫做异常，异常有两种类型：
预定义异常：当PL/SQL程序违反了Oracle规则或超越系统限制时隐式引发异常
DECLARE
empno emp.empno%type;
empname emp.ename%type;
BEGIN
SELECT empno,empname INTO empno,empname FROM emp WHERE empno='8888';
DBMS_OUTPUT.PUT_LINE('员工编号：'|| empno );
DBMS_OUTPUT.PUT_LINE('员工姓名：'|| empname );
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'此编号员工不存在！');
END;
例2：
DECLARE
VAR_NAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO VAR_NAME FROM EMP WHERE EMPNO='7902';
DBMS_OUTPUT.PUT_LINE(VAR_NAME);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('查询返回不止一行');
END;
用户定义异常：用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发
--输入成绩 如果不在正常范围则RAISE异常
DECLARE
VAR_SCORE NUMBER;
OUT_OF_RANGE EXCEPTION;
BEGIN
VAR_SCORE :=&VAR_SCORE;
CASE
WHEN VAR_SCORE BETWEEN 90 AND 100 ------BETWEEN number1 AND number2 只能从小到大
THEN DBMS_OUTPUT.PUT_LINE('优秀');
WHEN VAR_SCORE BETWEEN 80 AND 89
THEN DBMS_OUTPUT.PUT_LINE('良好');
WHEN VAR_SCORE BETWEEN 70 AND 79
THEN DBMS_OUTPUT.PUT_LINE('一般');
WHEN VAR_SCORE BETWEEN 60 AND 69
THEN DBMS_OUTPUT.PUT_LINE('及格');
WHEN VAR_SCORE BETWEEN 0 AND 59
THEN DBMS_OUTPUT.PUT_LINE('不及格');
ELSE RAISE OUT_OF_RANGE;
END CASE;
EXCEPTION WHEN OUT_OF_RANGE THEN
DBMS_OUTPUT.PUT_LINE('该成绩无效');
END;
RAISE_APPLICATION_ERROR 过程： 用于创建用户定义的错误信息， 可以在可执行部分和异常处理部分使用
RAISE_APPLICATION_ERROR(ERROR_NUMBER,ERROR_MESSAGE)
ERROR_NUMBER:是用户为异常指定的编号，该编号必须介于-20000~-20999之间
ERROR_MESSAGE:是用户为异常指定的消息，消息的长度可达2048字节。
示例：
DECLARE
empno emp.empno%type;
empname emp.ename%type;
BEGIN
SELECT empno,empname INTO empno,empname FROM emp
WHERE empno='8888';
DBMS_OUTPUT.PUT_LINE('员工编号：'|| empno );
DBMS_OUTPUT.PUT_LINE('员工姓名：'|| empname );
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'此编号员工不存在！');
END;
静态SQL
静态SQL是直接嵌到PL/SQL块中的SQL语句，用于完成特定或固定的任务。静态SQL的性能要优于动态SQL，因此在编写PL/SQL块时，如果功能完全确定，应使用静态SQL。
动态SQL：
动态SQL是指在运行时动态形成的SQL语句。如果需要在PL/SQL中执行DDL语句（如CREATE、ALTER、DROP）、DCL语句（GRANT、REVOKE），或者在PL/SQL块中需要执行更加灵活的SQL语句（如在SELECT语句中使用不同的WHERE条件），那么就必须借助于动态SQL。
动态SQL的执行
在大部分情况下，可以使用EXECUTE IMMEDIATE来执行动态SQL语句，语法如下：
EXECUTE IMMEDIATE dynamic_sql_string
[INTO variable_list]
[USING bind_argument_list];
示例：
DECLARE
sql_string VARCHAR2(200);
emp_rec emp%ROWTYPE;
BEGIN
sql_string:='SELECT * FROM emp WHERE empno=:id';
EXECUTE IMMEDIATE sql_string INTO emp_rec USING &emp_id;
DBMS_OUTPUT.PUT_LINE('查询出的员工姓名是：'||emp_rec.ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该编号的员工不存在!');
END;