课外天地 李树青学习天地数据库系统原理课件 → [推荐]Oracle数据库PL-SQL学习资料之二——PL/SQL中的SQL


  共有20928人关注过本帖树形打印复制链接

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

帅哥哟,离线,有人找我吗?
admin
  1楼 博客 | 信息 | 搜索 | 邮箱 | 主页 | UC


加好友 发短信 管理员
等级:管理员 帖子:1945 积分:26736 威望:0 精华:34 注册:2003/12/30 16:34:32
[推荐]Oracle数据库PL-SQL学习资料之二——PL/SQL中的SQL  发帖心情 Post By:2008/11/21 19:10:37 [只看该作者]

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;

[此贴子已经被作者于2010-12-11 20:01:04编辑过]

 回到顶部