主题：[推荐]Oracle数据库PL-SQL学习资料之二——PL/SQL中的SQL

4 PL/SQL中的SQL
4、1 简介
在PL/SQL中使用的SQL语句只能是DML语句和事务语句，但是不能使用DDL语句和权限语句。主要原因在于PL/SQL使用前期绑定，即在编译期间，确定数据库对象和检查这些对象的使用权限，而使用DDL语句和权限语句显然使得PL/SQL无法进行上述的对象检查控制，如：
begin
create table stus(sid number);
end;
/

但是通过动态SQL可以在PL/SQL中使用DDL语句和权限语句，因为动态SQL语句运行前并未编译，运行期间编译运行，如：
begin
execute immediate 'create table stus(sid number)';
end;
/

可以检查 desc stus;

begin
execute immediate 'drop table stus';
end;
/

4、2 几个说明
1）将表的每条记录拷贝一份，并存放于此表中
insert into stu select * from stu;

2）变量名称不要和表以及字段名称相同，否则在处理时，这些名称都以表以及字段名称优先，会产生问题，如
DECLARE
  Department  CHAR(3);
BEGIN
  Department := 'CS';
  -- Remove all records
  DELETE FROM classes
    WHERE department = Department; --always is true
END;
/

如果一定名称相同，解决方法为使用标号
<<l_DeleteBlock>>
DECLARE
  Department CHAR(3);
BEGIN
  Department := 'CS';
  -- Remove all Computer Science classes
  DELETE FROM classes
    WHERE department = l_DeleteBlock.Department;
END;
/

3）字符串比较问题
PL/SQL规定比较固定长度的字符串时采用自动填充空格再比较的方式（如char，字符串常量'Hello!'），而对于有一个数值是变长的情况，就采用非填充空格方式直接进行比较（varchar2）。

4、3 批绑定
下面的程度在PL/SQL引擎和SQL引擎间切换太多，影响效率
DECLARE
  TYPE t1 IS TABLE OF stu.sid%TYPE INDEX BY BINARY_INTEGER;
  TYPE t2 IS TABLE OF stu.name%TYPE INDEX BY BINARY_INTEGER;
  v_t1 t1;
  v_t2 t2;
BEGIN
  -- Fill up the arrays with 500 rows.
  FOR v_Count IN 100..110 LOOP
    v_t1(v_Count) := v_Count;
    v_t2(v_Count) := 'Stu' || v_Count;
  END LOOP;

  -- And insert them into the database.
  FOR v_Count IN 100..110 LOOP
    INSERT INTO stu VALUES (v_t1(v_Count), v_t2(v_Count),'M');
  END LOOP;
END;
/

可以通过select * from stu;查看

使用批绑定可以一次性将表传给SQL引擎，只有一次交换，具体做法是使用FORALL，如
DECLARE
  TYPE t1 IS TABLE OF stu.sid%TYPE INDEX BY BINARY_INTEGER;
  TYPE t2 IS TABLE OF stu.name%TYPE INDEX BY BINARY_INTEGER;
  v_t1 t1;
  v_t2 t2;
BEGIN
  -- Fill up the arrays with 500 rows.
  FOR v_Count IN 1100..1110 LOOP
    v_t1(v_Count) := v_Count;
    v_t2(v_Count) := 'Stu' || v_Count;
  END LOOP;

  -- And insert them into the database.
  FORALL v_Count IN 1100..1110
    INSERT INTO stu VALUES (v_t1(v_Count), v_t2(v_Count),'M');
END;
/

4、4 RETURNING子句
用在任何DML语句的末尾，获取刚处理的一行或者多行的信息
set serveroutput on size 1000000;

DECLARE
  v_SID stu.sid%TYPE;
  v_NAME stu.name%TYPE;
  v_SEX stu.sex%TYPE;
BEGIN
  -- Insert a new row into the students table, and get the
  -- rowid of the new row at the same time.
  INSERT INTO stu VALUES (9999, 'XX', 'F') RETURNING name INTO v_NAME;
  
  DBMS_OUTPUT.PUT_LINE('Newly inserted Name is ' || v_NAME);
  
  -- Update this new row to increase the sid, and get
  -- the name back.
  UPDATE stu SET sid = sid - 1 WHERE name = v_name RETURNING sid,sex INTO v_SID, v_SEX;
    
  DBMS_OUTPUT.PUT_LINE('SID: ' || v_SID || ' SEX: ' || v_SEX);
  
  -- Delete the row,and get the SID of the deleted row back.
  DELETE FROM stu WHERE name = v_name RETURNING sid INTO v_SID;
    
  DBMS_OUTPUT.PUT_LINE('ID of new row was ' || v_SID);
END;
/

4、5 关于表的一些其他引用方法
4、5、1 使用同义词
select * from stu;和select * from sys.stu;功能一样，其中sys.表示所有者。
表后面还可以添加数据库连接，如：select * from sys.stu@myoracle;

为了简化表示，可以使用同义词来缩写，如
create synonym sys_stu for sys.stu;
select * from sys_stu;

4、5、2 使用数据库连接
create database link dbl connect to scott identified by tiger using 'scott_table';

4、6 伪列
伪列是指仅能从SQL语句中调用的其他功能，处理方式和表的列很类似，但是存在方式与列不同

4、6、1 CURRVAL和NEXTVAL
它们配合序列使用，序列和SQL Server的标识很相似
只能在投影字段、insert语句的values子句和update的set子句中使用，不能在where选择语句或者其他PL-SQL过程语句中

CREATE SEQUENCE student_sequence START WITH 10000 INCREMENT BY 1;

INSERT INTO stu VALUES (student_sequence.NEXTVAL, 'X1', 'F');
INSERT INTO stu VALUES (student_sequence.NEXTVAL, 'X2', 'F');

select student_sequence.CURRVAL from dual;
select student_sequence.NEXTVAL from dual;

select * from stu;

4、6、2 LEVEL

4、6、3 ROWID
用在查询的选择列表中，返回行特定的行标识符，如
select ROWID from stu;

具体格式可能会因为条件和版本而不一样

4、6、4 ROWNUM
在查询中返回当前的行号，可以用在where和update的set中，类型为NUMBER，如
select * from stu where ROWNUM<3;

但是排序等操作不会影响记录的行号，所以不能利用排序后的查询获取前面的最大值，如下面的语句并没有返回最大的姓名值
select * from stu where ROWNUM<3 order by name;

4、7 事务控制
4、7、1 事务与语句块
事务和语句块并不完全一样，相互之间也没有一定的对应关系，如启动一个新的语句块并不代表一个新事务的开始
insert into stu ...
begin
update stu set ...
rollback
end
回滚的事务会导致两个语句全部撤消

再如，一个语句块也可以包含多个事务
set serveroutput on size 1000000;

declare
  v_i NUMBER;
begin
  v_i:=0;
  for v_counter in 200 .. 210 loop
    insert into stu values (v_counter,'SomeOne','F');
    v_i:=v_i+1;
    if v_i=2 then
      COMMIT;
      v_i:=0;
      DBMS_OUTPUT.PUT_LINE(v_counter);
    end if;
  end loop;
  ROLLBACK;
end;
/

观察语句为
delete from stu where trim(name)='SomeOne';

select * from stu;

4、7、2 自治事务
在父事务中运行，自己可以完成提交或者回滚，与父事务的处理没有关系

create or replace procedure autopro as
begin
  insert into stu values(999,'999','M');
  COMMIT;
end autopro;
/

begin
insert into stu values(9999,'9999','M');
autopro;
ROLLBACK;
end;
/

显示结果为插入两条成功，原因在于COMMIT

进一步修改存储过程
create or replace procedure autopro as
pragma autonomous_transaction;
begin
  insert into stu values(999,'999','M');
  COMMIT;
end autopro;
/

begin
autopro;
insert into stu values(9999,'9999','M');
ROLLBACK;
end;
/

显示结果为只有一条记录

注意：
自治事务不是可以任意使用，只有在顶层语句块、子程序、对象类型的方法和触发器中才能使用

4、7、3 保存点
利用保存点可以进行有控制的撤销，如：

begin
  insert into stu values(222,'222','F');
  savepoint a;
  insert into stu values(333,'333','F');
  rollback to a;
  commit;
end;

甚至利用保存点可以撤销到父事务的保存点，如：
create or replace procedure autoproc
as
begin
rollback to savepoint a;
end autoproc;

begin
   savepoint a;
   insert into stu values(444,'444','M');
   autoproc;
end;

但是对于自治事务，保存点对于当前事务来说是局部的，不能撤销到父事务的保存点，如：
create or replace procedure autoproc
as
pragma autonomous_transaction;
begin
rollback to savepoint a;
end autoproc;

begin
   savepoint a;
   insert into stu values(444,'444','M');
   autoproc;
end;

4、8 权限
数据控制语句不能直接应用于PL_SQL中，但是在PL_SQL中能够运行的数据操纵语句必须获得相关数据库对象的处理权限
建立用户：
CREATE USER "LSQ" PROFILE "DEFAULT" IDENTIFIED BY "lsq" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;

4、8、1 GRANT和REVOKE
1）对象权限的使用
授予登录权限：
GRANT "CONNECT" TO "LSQ";

授予查询stu表的权限：
grant select on stu to lsq;

可以以lsq登录来查看：
select * from sys.stu;

撤销权限：
revoke select on stu from lsq;
注意：
如果指定CASCADE CONSTRAINTS语句，并且被取消的是references权限，则使用该权限创建的所有引用完整性都会被取消
如果取消一个具有表依赖性的对象类型的execute权限时，要使用force关键字

可以以lsq登录来查看：
select * from sys.stu;

也可以让lsq用户可以为其他用户授予此对象的查询权限：
grant select on stu to lsq with grant option;

建立新用户：
CREATE USER "XX" PROFILE "DEFAULT" IDENTIFIED BY "XX" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "XX";

可以以lsq登录来授予查询stu表的权限：
grant select on sys.stu to xx;
但是不能越权授权：
grant delete on sys.stu to xx;

也可以指定多个权限来授权，如
grant select,delete on stu to lsq;

2）系统权限的使用
grant create table,alter any procedure to lsq with admin option;
其中的with admin option表示该用户可以向其他用户进一步授权

create table stus(num number) TABLESPACE system;

撤销系统权限：
revoke create table,alter any procedure from lsq;

4、8、2 角色
角色为一组权限的集合

1）定义角色
create role stu_operation;
grant select on stu to stu_operation;
grant delete on stu to stu_operation;

2）使用角色
grant stu_operation to lsq;

由于每个用户都默认具有public角色，所以直接将特定权限赋予public角色，即可自动赋予每个用户特定权限，如：
grant stu_operation to public

此时以任何用户登录都可以访问stu表，如：
select * from sys.stu;