pl／sql个人笔记.

PL/SQL 块中只能直接嵌入SELECT,DML(INSERT,UPDATE,DELETE)以及事务 控制语句(COMMIT,ROLLBACK,SAVEPOINT), 而不能直接嵌入DDL语句 (CREATE,ALTER,DROP) 和DCL语句(GRANT,REVOKE) ### PL/SQL 个人笔记详解 #### 一、PL/SQL 块中可嵌入的 SQL 语句类型 PL/SQL（程序化SQL）是Oracle数据库的标准编程语言，它扩展了SQL的功能，允许在数据库环境中编写过程化的业务逻辑。在PL/SQL中，可以嵌入多种类型的SQL语句来实现复杂的数据处理任务。根据标题和描述中的内容，我们可以了解到： - **SELECT**：用于检索数据。 - **DML** (Data Manipulation Language)：包括INSERT（插入）、UPDATE（更新）、DELETE（删除），用于操作表中的数据。 - **事务控制语句**：COMMIT（提交）、ROLLBACK（回滚）、SAVEPOINT（保存点），用于管理事务。 这些语句可以直接嵌入到PL/SQL块中执行，但不支持直接嵌入DDL（Data Definition Language）语句（如CREATE、ALTER、DROP）和DCL（Data Control Language）语句（如GRANT、REVOKE）。这些特定的SQL语句通常需要通过外部过程调用来实现。 #### 二、检索单行数据 ##### 1.1 使用标量变量接收数据 示例代码展示了如何使用标量变量来接收查询结果。例如： ```sql v_ename emp.ename%type; v_sal emp.sal%type; SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = &no; ``` 这里，`ename%type`和`sal%type`定义了变量的数据类型与表中相应列的数据类型相匹配，确保类型一致。 ##### 1.2 使用记录变量接收数据 如果需要接收多列数据，可以使用记录变量： ```sql TYPE emp_record_type IS RECORD ( ename emp.ename%type, sal emp.sal%type ); emp_record emp_record_type; SELECT ename, sal INTO emp_record FROM emp WHERE empno = &no; ``` ##### 1.3 嵌入 SELECT 语句注意事项 - **返回单条数据**：使用`SELECT INTO`时必须确保只返回一条数据，否则将引发异常。 - `NO_DATA_FOUND`：没有找到数据。 - `TOO_MANY_ROWS`：返回多条数据。 - **WHERE 子句注意事项**：避免在 WHERE 子句中使用与列名相同的变量名，以防触发`TOO_MANY_ROWS`异常。 #### 三、操纵数据 ##### 2.1 使用 VALUES 子句插入数据 示例代码展示了如何使用变量来构造插入语句： ```sql v_deptno dept.deptno%type; v_dname dept.dname%type; v_deptno := no; v_dname := '&name'; INSERT INTO dept (deptno, dname) VALUES (v_deptno, v_dname); ``` ##### 2.2 使用子查询插入数据 也可以使用子查询的方式进行数据插入： ```sql v_deptno emp.deptno%type := &no; INSERT INTO employee SELECT * FROM emp WHERE deptno = v_deptno; ``` ##### 2.3 更新数据 使用表达式更新列值： ```sql v_deptno dept.deptno%type := no; v_loc dept.loc%type := '&loc'; UPDATE dept SET loc = v_loc WHERE deptno = v_deptno; ``` ##### 2.4 使用子查询更新列值 通过子查询更新列值： ```sql v_ename emp.ename%type := '&name'; UPDATE emp SET (sal, comm) = (SELECT sal, comm FROM emp WHERE ename = v_ename) WHERE job = (SELECT job FROM emp WHERE ename = v_ename); ``` ##### 2.5 删除数据 使用变量删除数据： ```sql v_deptno dept.deptno%type := &no; DELETE FROM dept WHERE deptno = v_deptno; ``` ##### 2.6 使用子查询删除数据 通过子查询删除数据： ```sql v_ename emp.ename%type := '&name'; DELETE FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = v_ename); ``` #### 四、SQL 游标 游标是一种数据库对象，它允许用户在程序中迭代地访问查询结果集中的每一行数据。 ##### 3.1 SQL%ISOPEN 属性 在PL/SQL中，`SQL%ISOPEN`属性始终为`FALSE`，因为SQL语句的执行会自动打开和关闭游标。 ##### 3.2 SQL%FOUND 属性 用于判断SQL语句是否成功执行并影响了至少一行数据： ```sql v_deptno emp.deptno%type := &no; UPDATE emp SET sal = sal * 1.1 WHERE deptno = v_deptno; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE('执行成功'); ELSE DBMS_OUTPUT.PUT_LINE('执行失败'); END IF; ``` ##### 3.3 SQL%NOTFOUND 属性 与`SQL%FOUND`相反，用于判断SQL语句是否未影响任何行： ```sql v_deptno emp.deptno%type := &no; UPDATE emp SET sal = sal * 1.1 WHERE deptno = v_deptno; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('未找到数据'); END IF; ``` ##### 3.4 SQL%ROWCOUNT 属性 返回SQL语句影响的行数： ```sql v_deptno emp.deptno%type := &no; UPDATE emp SET sal = sal * 1.1 WHERE deptno = v_deptno; DBMS_OUTPUT.PUT_LINE('修改了 ' || SQL%ROWCOUNT || ' 行'); ``` #### 五、事务控制 事务控制语句用于管理对数据库的操作，确保数据的一致性和完整性。主要包括： - **COMMIT**：提交当前事务中的所有更改。 - **ROLLBACK**：撤销当前事务中的所有更改。 - **SAVEPOINT**：在事务中设置一个保存点，之后可以回滚到这个保存点。 这些语句通常与PL/SQL块结合使用，以便更好地控制数据库操作的过程。例如，在复杂的业务逻辑处理过程中，通过适当地使用这些语句可以确保数据的安全性和一致性。