-- 作者:admin
-- 发布时间:2008/11/21 19:10:37
-- [推荐]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;
[此贴子已经被作者于2010-12-11 20:01:04编辑过]
|