【网学网提醒】：网学会员为大家收集整理了Oracle数据库PL-SQL学习资料之二——PLSQL中的SQL提供大家参考，希望对大家有所帮助!
[推荐]Oracle数据库PL-SQL学习资料之二——PL/SQL中的SQLPostBy：2008-11-2119:10:37[只看该作者]
4PL/SQL中的SQL
4、1简介
在PL/SQL中使用的SQL语句只能是DML语句和事务语句，但是不能使用DDL语句和权限语句。主要原因在于PL/SQL使用前期绑定，即在编译期间，确定数据库对象和检查这些对象的使用权限，而使用DDL语句和权限语句显然使得PL/SQL无法进行上述的对象检查控制，如：
begin
createtablestus(sidnumber);
end;
/
但是通过动态SQL可以在PL/SQL中使用DDL语句和权限语句，因为动态SQL语句运行前并未编译，运行期间编译运行，如：
begin
executeimmediate'createtablestus(sidnumber)';
end;
/
可以检查descstus;
begin
executeimmediate'droptablestus';
end;
/
4、2几个说明
1）将表的每条记录拷贝一份，并存放于此表中
insertintostuselect*fromstu;
2）变量名称不要和表以及字段名称相同，否则在处理时，这些名称都以表以及字段名称优先，会产生问题，如
DECLARE
DepartmentCHAR(3);
BEGIN
Department:='CS';
--Removeallrecords
DELETEFROMclasses
WHEREdepartment=Department;--alwaysistrue
END;
/
如果一定名称相同，解决方法为使用标号
<
>
DECLARE
DepartmentCHAR(3);
BEGIN
Department:='CS';
--RemoveallComputerScienceclasses
DELETEFROMclasses
WHEREdepartment=l_DeleteBlock.Department;
END;
/
3）字符串比较问题
PL/SQL规定比较固定长度的字符串时采用自动填充空格再比较的方式（如char，字符串常量'Hello!'），而对于有一个数值是变长的情况，就采用非填充空格方式直接进行比较（varchar2）。
4、3批绑定
下面的程度在PL/SQL引擎和SQL引擎间切换太多，影响效率
DECLARE
TYPEt1ISTABLEOFstu.sid%TYPEINDEXBYBINARY_INTEGER;
TYPEt2ISTABLEOFstu.name%TYPEINDEXBYBINARY_INTEGER;
v_t1t1;
v_t2t2;
BEGIN
--Fillupthearrayswith500rows.
FORv_CountIN100..110LOOP
v_t1(v_Count):=v_Count;
v_t2(v_Count):='Stu'||v_Count;
ENDLOOP;
--Andinsertthemintothedatabase.
FORv_CountIN100..110LOOP
INSERTINTOstuVALUES(v_t1(v_Count),v_t2(v_Count),'M');
ENDLOOP;
END;
/
可以通过select*fromstu;查看
使用批绑定可以一次性将表传给SQL引擎，只有一次交换，具体做法是使用FORALL，如
DECLARE
TYPEt1ISTABLEOFstu.sid%TYPEINDEXBYBINARY_INTEGER;
TYPEt2ISTABLEOFstu.name%TYPEINDEXBYBINARY_INTEGER;
v_t1t1;
v_t2t2;
BEGIN
--Fillupthearrayswith500rows.
FORv_CountIN1100..1110LOOP
v_t1(v_Count):=v_Count;
v_t2(v_Count):='Stu'||v_Count;
ENDLOOP;
--Andinsertthemintothedatabase.
FORALLv_CountIN1100..1110
INSERTINTOstuVALUES(v_t1(v_Count),v_t2(v_Count)
,'M');
END;
/
4、4RETURNING子句
用在任何DML语句的末尾，获取刚处理的一行或者多行的信息
setserveroutputonsize1000000;
DECLARE
v_SIDstu.sid%TYPE;
v_NAMEstu.name%TYPE;
v_SEXstu.sex%TYPE;
BEGIN
--Insertanewrowintothestudentstable,andgetthe
--rowidofthenewrowatthesametime.
INSERTINTOstuVALUES(9999,'XX','F')RETURNINGnameINTOv_NAME;
DBMS_OUTPUT.PUT_LINE('NewlyinsertedNameis'||v_NAME);
--Updatethisnewrowtoincreasethesid,andget
--thenameback.
UPDATEstuSETsid=sid-1WHEREname=v_nameRETURNINGsid,sexINTOv_SID,v_SEX;
DBMS_OUTPUT.PUT_LINE('SID:'||v_SID||'SEX:'||v_SEX);
--Deletetherow,andgettheSIDofthedeletedrowback.
DELETEFROMstuWHEREname=v_nameRETURNINGsidINTOv_SID;
DBMS_OUTPUT.PUT_LINE('IDofnewrowwas'||v_SID);
END;
/
4、5关于表的一些其他引用方法
4、5、1使用同义词
select*fromstu;和select*fromsys.stu;功能一样，其中sys.表示所有者。
表后面还可以添加数据库连接，如：select*fromsys.stu@myoracle;
为了简化表示，可以使用同义词来缩写，如
createsynonymsys_stuforsys.stu;
select*fromsys_stu;
4、5、2使用数据库连接
createdatabaselinkdblconnecttoscottidentifiedbytigerusing'scott_table';
4、6伪列
伪列是指仅能从SQL语句中调用的其他功能，处理方式和表的列很类似，但是存在方式与列不同
4、6、1CURRVAL和NEXTVAL
它们配合序列使用，序列和SQLServer的标识很相似
只能在投影字段、insert语句的values子句和update的set子句中使用，不能在where选择语句或者其他PL-SQL过程语句中
CREATESEQUENCEstudent_sequenceSTARTWITH10000INCREMENTBY1;
INSERTINTOstuVALUES(student_sequence.NEXTVAL,'X1','F');
INSERTINTOstuVALUES(student_sequence.NEXTVAL,'X2','F');
selectstudent_sequence.CURRVALfromdual;
selectstudent_sequence.NEXTVALfromdual;
select*fromstu;
4、6、2LEVEL
4、6、3ROWID
用在查询的选择列表中，返回行特定的行标识符，如
selectROWIDfromstu;
具体格式可能会因为条件和版本而不一样
4、6、4ROWNUM
在查询中返回当前的行号，可以用在where和update的set中，类型为NUMBER，如
select*fromstuwhereROWNUM<3;
但是排序等操作不会影响记录的行号，所以不能利用排序后的查询获取前面的最大值，如下面的语句并没有返回最大的值
select*fromstuwhereROWNUM<3orderbyname;
4、7事务控制
4、7、1事务与语句块
事务和语句块并不完全一样，相互之间也没有一定的对应关系，如启动一个新的语句块并不代表一个新事务的开始
insertintostu...
begin
updatestuset...
rollback
end
回滚的事务会导致两个语句全部撤消
再如，一个
语句块也可以包含多个事务
setserveroutputonsize1000000;
declare
v_iNUMBER;
begin
v_i:=0;
forv_counterin200..210loop
insertintostuvalues(v_counter,'SomeOne','F');
v_i:=v_i+1;
ifv_i=2then
COMMIT;
v_i:=0;
DBMS_OUTPUT.PUT_LINE(v_counter);
endif;
endloop;
ROLLBACK;
end;
/
观察语句为
deletefromstuwheretrim(name)='SomeOne';
select*fromstu;
4、7、2自治事务
在父事务中运行，自己可以完成提交或者回滚，与父事务的处理没有关系
createorreplaceprocedureautoproas
begin
insertintostuvalues(999,'999','M');
COMMIT;
endautopro;
/
begin
insertintostuvalues(9999,'9999','M');
autopro;
ROLLBACK;
end;
/
显示结果为插入两条成功，原因在于COMMIT
进一步修改存储过程
createorreplaceprocedureautoproas
pragmaautonomous_transaction;
begin
insertintostuvalues(999,'999','M');
COMMIT;
endautopro;
/
begin
autopro;
insertintostuvalues(9999,'9999','M');
ROLLBACK;
end;
/
显示结果为只有一条记录
注意：
自治事务不是可以任意使用，只有在顶层语句块、子程序、对象类型的方法和触发器中才能使用
4、7、3保存点
利用保存点可以进行有控制的撤销，如：
begin
insertintostuvalues(222,'222','F');
savepointa;
insertintostuvalues(333,'333','F');
rollbacktoa;
commit;
end;
甚至利用保存点可以撤销到父事务的保存点，如：
createorreplaceprocedureautoproc
as
begin
rollbacktosavepointa;
endautoproc;
begin
savepointa;
insertintostuvalues(444,'444','M');
autoproc;
end;
但是对于自治事务，保存点对于当前事务来说是局部的，不能撤销到父事务的保存点，如：
createorreplaceprocedureautoproc
as
pragmaautonomous_transaction;
begin
rollbacktosavepointa;
endautoproc;
begin
savepointa;
insertintostuvalues(444,'444','M');
autoproc;
end;
4、8权限
数据控制语句不能直接应用于PL_SQL中，但是在PL_SQL中能够运行的数据操纵语句必须获得相关数据库对象的处理权限
建立用户：
CREATEUSER"LSQ"PROFILE"DEFAULT"IDENTIFIEDBY"lsq"DEFAULTTABLESPACE"USERS"ACCOUNTUNLOCK;
4、8、1GRANT和REVOKE
1）对象权限的使用
授予登录权限：
GRANT"CONNECT"TO"LSQ";
授予查询stu表的权限：
grantselectonstutolsq;
可以以lsq登录来查看：
select*fromsys.stu;
撤销权限：
revokeselectonstufromlsq;
注意：
如果指定CASCADECONSTRAINTS语句，并且被取消的是references权限，则使用该权限创建的所有引用完整性都会被取消
如果取消一个具有表依赖性的对象类型的execute权限时，要使用force关键字
可以以lsq登录来查看：
select*fromsys.stu;
也可以让lsq用户可以为其他用户授
予此对象的查询权限：
grantselectonstutolsqwithgrantoption;
建立新用户：
CREATEUSER"XX"PROFILE"DEFAULT"IDENTIFIEDBY"XX"DEFAULTTABLESPACE"USERS"ACCOUNTUNLOCK;
GRANT"CONNECT"TO"XX";
可以以lsq登录来授予查询stu表的权限：
grantselectonsys.stutoxx;
但是不能越权授权：
grantdeleteonsys.stutoxx;
也可以指定多个权限来授权，如
grantselect,deleteonstutolsq;
2）系统权限的使用
grantcreatetable,alteranyproceduretolsqwithadminoption;
其中的withadminoption表示该用户可以向其他用户进一步授权
createtablestus(numnumber)TABLESPACEsystem;
撤销系统权限：
revokecreatetable,alteranyprocedurefromlsq;
4、8、2角色
角色为一组权限的集合
1）定义角色
createrolestu_operation;
grantselectonstutostu_operation;
grantdeleteonstutostu_operation;
2）使用角色
grantstu_operationtolsq;
由于每个用户都默认具有public角色，所以直接将特定权限赋予public角色，即可自动赋予每个用户特定权限，如：
grantstu_operationtopublic
此时以任何用户登录都可以访问stu表，如：
select*fromsys.stu;
