PL/SQL学习笔记-2

Oracle9i PL/SQL程序设计 的学习笔记

第4章到第5章

[@more@]

第四章 PL/SQL中的SQL
4.1 SQL语句
类别 SQL范例
数据操纵语言(DML) SELECT,INSERT,UPDATE,DELETE,EXPLAIN PLAN
数据定义语言(DDL) DROP,CREATE,ALTER,GRANT,REVOKE
事务控制 COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION
会话控制 ALTER SESSION,SET ROLE
系统控制 ALTER SYSTEM

在PL/SQL中可以直接使用的SQL语句只有DML(除了EXPLAIN PLAN)和事务控制语句。特别的是，使用DDL语句是非法的。
PL/SQL采用了早期绑定，就是在编译的时候绑定变量。
这种设计导致了DDL语句被禁止使用了。因为DDL语句将修改数据库对象，权限必须重新进行验证，对于权限的验证需要标识符是绑定过的，而这已经在编译时就完成。

用动态SQL可以执行所有的SQL。PL/SQL中的动态SQL有两种：本地动态SQL和DBMS_SQL包。

4.2 在PL/SQL中使用DML
SELECT...INTO [PL/SQL_record|variable] FROM ... WHERE ...
INSERT INITO ... ...
UPDATE ... [CURRENT OF cursor]
DELETE FROM ... [CURRENT OF cursor]

在PL/SQL中如果变量名字和字段一样，例如 department=Department 。PL/SQL的匹配规则是先查看是否与表中的列相匹配，然后再检查他们是不是PL/SQL块中的变量。由于PL/SQL不区分大小写，所以PL/SQL会把刚才的语句认为是恒等的。
这在SQL语句中是很危险的。
在字符串比较的时候，如果一边是char，一边是varchar时要注意比较的填充空格问题。
-------------------------------------------------------------------------------------------------------------
SQL> desc test
Name Type Nullable Default Comments
---- -------- -------- ------- --------
ID INTEGER Y
NAME CHAR(20) Y

SQL> select * from test;

ID NAME
--------------------------------------- --------------------
1 a
2 b
3 c

SQL> declare
2 v_name varchar(20);
3 begin
4 v_name:='a';
5 delete from test where name=v_name;
6 end;
7 /

PL/SQL procedure successfully completed

SQL> select * from test;

ID NAME
--------------------------------------- --------------------
1 a
2 b
3 c

SQL> declare
2 v_name test.name%TYPE;
3 begin
4 v_name:='a';
5 delete from test where name=v_name;
6 end;
7 /

PL/SQL procedure successfully completed

SQL> select * from test;

ID NAME
--------------------------------------- --------------------
2 b
3 c

SQL>
--------------------------------------------------------------------------------------------------------------

批绑定：
for v_nums in 1..10000 loop
insert into test values(v_nums,'a');
end loop;
可以改写成
forall i in 1..10000
insert into ....
将SQL语句打包一次性提交给数据库，可以减少PL/SQL引擎和SQL引擎之间的上下文切换。

不过有个奇怪的情况，就是forall后面的变量，无法在SQL语句中直接使用。
--------------------------------------------------------------------------------------------------------------
SQL> declare
2 v_num int;
3 begin
4 for v_nums in 1..10000 loop
5 insert into test values(v_nums,'a');
6 end loop；
7 end;
8 /

PL/SQL procedure successfully completed

Executed in 0.766 seconds

SQL> declare
2 v_num int;
3 begin
4 forall v_nums in 1..10000 loop
5 insert into test values(v_nums,'a');
6 end loop;
7 end;
8 /

ORA-06550: 第 5 行, 第 29 列:
PLS-00430: 该上下文中不允许使用 FORALL 循环变量 V_NUMS
ORA-06550: 第 5 行, 第 5 列:
PLS-00435: 没有 BULK In-BIND 的 DML 语句在 FORALL 内不能使用

SQL> declare
2 v_num int;
3 type t_numbers is table of int index by binary_integer;
4 v_nums t_numbers;
5 begin
6 for v_count in 1..10000 loop
7 v_nums(v_count):=v_count;
8 end loop;
9 forall v_count in 1..10000
10 insert into test values(v_nums(v_count),'a');
11 end;
12 /
PL/SQL procedure successfully completed

Executed in 0.047 seconds
--------------------------------------------------------------------------------------------------------------

RETURNING子句
RETURNING expr INTO variable
例如：
INSERT INTO test values(...) RETURNING rowid INTO v_rowid;
UPDATE test Set id=... RETURNING xx INTO v_xx;
DELETE test where name='...' RETURNING id INTO v_id;
用RETURNING来获取插入的记录的自动增长的序列号或者rowid，是个不错的方法。

数据库链接
CREATE DATABASE LINK link_name CONNECT TO username IDENTIFIED BY password USING sqlnet_string
当数据库链接用作某个事务的一部分的时，该事务就被称为
同义词
CREATE SYNONYM synonym_name FOR reference

4.3 伪列
伪列是仅能从SQL语句中调用的其他功能。
CURRVAL和NEXTVAL 这2个伪列和序列一起使用。序列是一种Oracle对象，用来产生唯一的数字。

LEVEL 仅仅用在对表执行层次树遍历的SELECT语句中，使用START WITH 和 CONNECT BY 子句。

ROWID 这个不用我说了吧。

ROWNUM 在查询中返回当前的行号。

4.4 GRANT、REVOKE和权限
有两种类型的权限-对象权限和系统权限。
对于对象权限：GRANT privilege ON object TO grantee [WITH GRANT OPTION];
对于系统权限：GRANT privilege TO grantee [WITH GRANT OPTION];
对于对象权限：REVOKE privilege ON object FROM grantee [CASCADE CONSTRANINTS] [FORCE];
对于系统权限：REVOKE privilege FROM grantee;

CASCADE CONSTRAINTS
This clause is relevant only if you revoke the REFERENCES privilege or ALL [PRIVILEGES].
It drops any referential integrity constraints that the revokee has defined using the REFERENCES privilege (which might have been granted either explicitly or implicitly through a grant of ALL [PRIVILEGES]).

FORCE
Specify FORCE to revoke the EXECUTE object privilege on user-defined type objects with table or type dependencies. You must use FORCE to revoke the EXECUTE object privilege on user-defined type objects with table dependencies.
If you specify FORCE, then all privileges will be revoked, but all dependent objects are marked INVALID, data in dependent tables becomes inaccessible, and all dependent function-based indexes are marked UNUSABLE. (Regranting the necessary type privilege will revalidate the table.)

对于 CASCADE CONSTRAINTS 和 FORCE 看的有点似懂非懂。英文在上面，以后研究吧。

4.5 事务控制
COMMIT [WORK]
ROLLBACK [WORk]
可选择的WORK关键字可以用来提高阅读性，没有实际的意义。
保存点： SAVEPOINT name;
ROLLBACK [WORK] TO SAVEPOINT name;
注意：
* 从保存点之后的所有工作都被撤销，但是保存点依然有效。
* 从该保存点之后的SQL所需要的锁和资源都被释放了。
* 该事务并没有结束，因为SQL语句依然挂起。

自治事务：
执行自治事务的唯一方法是在PL/SQL语句块内部执行。在声明部分使用编译指示。
CREATE OR REPLACE PROCEDURE Autonomous AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO ...;
COMMIT;
END Autonomous;
自治块的类型 不是所有的语句块都可以被标记为自治，只有下面的是合法的：
* 顶层自治块。
* 本地的、单个的和打包的子程序。
* 对象类型的方法。
* 数据库触发器。

自治事务开始于自治块的第一条SQL语句，结束于COMMIT或者ROLLBACK语句，如果没有COMMIT或者ROLLBACK，则自治块会报ORA-6519的错误。
--------------------------------------------------------------------------------------------------------------
SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 BEGIN
3 INSERT INTO test VALUES(1,'a');
4 END Autonomous;
5 /

Procedure created

Executed in 0.047 seconds

SQL> exec Autonomous;

PL/SQL procedure successfully completed

Executed in 0.015 seconds

SQL>
SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 BEGIN
3 INSERT INTO test VALUES(1,'a');
4 COMMIT;
5 END Autonomous;
6 /

Procedure created

Executed in 0.016 seconds

SQL> exec Autonomous;

PL/SQL procedure successfully completed

Executed in 0 seconds

SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 INSERT INTO test VALUES(1,'a');
5 COMMIT;
6 END Autonomous;
7 /

Procedure created

Executed in 0.109 seconds
SQL> exec Autonomous;

PL/SQL procedure successfully completed

Executed in 0 seconds

SQL>
SQL> CREATE OR REPLACE PROCEDURE Autonomous AS
2 PRAGMA AUTONOMOUS_TRANSACTION;
3 BEGIN
4 INSERT INTO test VALUES(1,'a');
5 END Autonomous;
6 /

Procedure created

Executed in 0.093 seconds

SQL> exec Autonomous;

begin Autonomous; end;

ORA-06519: 检测到活动的自治事务处理，已经回退
ORA-06512: 在"IPNET.AUTONOMOUS", line 4
ORA-06512: 在line 1
--------------------------------------------------------------------------------------------------------------

第5章 内置SQL函数

5.1 概述
SQL提供了许多预定义函数，你可以从SQL语句中调用他们。

5.2 返回字符值的字符函数
CHAR(x[USING NCHAR_CS]) 返回数据库字符集中与x值相等的字符。
CONCAT(string1,string2) 返回连接string1和string2的字符串，等同用 ||
INITCAP(string) 返回首字母大写，其他改为小写
LOWER(string) 以小写返回string
LPAD(string1,x[,string2]) 左填充
LTRIM(string1,string2) 从string1左边开始，删除出现在string2中的字符，直到碰到第一个在string2种找不到的字符。
REPLACE(string,search_str[,replace_str]) 替换字符
RPAD(string1,x[,string2]) 同LPAD，只不过是从右边开始
RTIRM(string1,string2) 同LTRIM，只不过是从右边开始
SOUNDEX(string) 返回string的语音表示形式。
SUBSTR(string,a[,b]) 返回string的一部分，a和b是以字符为单位。
SUBSTRB(string,a[,b]) 返回string的一部分，a和b是以字节为单位。
SUBSTRC(string,a[,b]) 返回string的一部分，a和b是以Unicode完全字符为单位。
SUBSTR2(string,a[,b]) 返回string的一部分，a和b是以UCS2代码点为单位。
SUBSTR4(string,a[,b]) 返回string的一部分，a和b是以UCS4代码点为单位。
TRANSLATE(string.from_str,to_str) 返回将所有出现的from_str中每个字符替代为to_str中相应字符后的string。
TRIM([{{LEADING|TRAILING|BOTH} [trim_char|trim_char]} FROM] string) 返回删除了在开头、结尾或开头结尾都出现的trim_char之后的string。
UPPER(string) 以大写形式返回string。

仔细看下列的例子，注意replace 和 translate 的区别。
--------------------------------------------------------------------------------------
SQL> select replace('abccba','abc','123') from dual;

REPLACE('ABCCBA','ABC','123')
-----------------------------
123cba

SQL> select translate('abccba','abc','123') from dual;

TRANSLATE('ABCCBA','ABC','123'
------------------------------
123321

--------------------------------------------------------------------------------------


5.3 返回数字值得字符函数
ASCII(string) 返回string首字母的十进制表示形式。
INSTR(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以字符为单位.
INSTRB(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以字节为单位.
INSTRC(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以Unicode完全字符为单位.
INSTR2(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以UCS2代码点为单位.
INSTR4(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b都是以UCS4代码点为单位.
LENGTH(string1) 返回以字符为单位的长度.
LENGTHB(string1) 返回以字节为单位的长度.
LENGTHC(string1) 返回以Unicode完全字符为单位的长度.
LENGTH2(string1) 返回以UCS2代码点为单位的长度.
LENGTH4(string1) 返回以UCS4代码点为单位的长度.

5.4 NLS函数
从来没用过。略

5.5 数字函数

ACOS(x) x的反余旋值。
ASIN(x) x的反正旋值
ATAN(x) x的反正切值
ATAN2(x,y) x和y的反正切值
BITAND(x,y) 返回x和y的与结果
COS(x) x的余旋
COSH(x) x的双曲余旋
EXP(x) x的次幕
LN(x) x的自然对数
LOG(x,y) 以x为底的y的对数
SIN(x) x的正旋
SINH(x) x的双曲正旋
SQRT(x) x的平方根
TAN(x) x的正切
TANH(x) x的双曲正切
SIGN(x) 如果x<0 返回-1 如果x=0 返回 0 如果x>0 返回1
POWER(x,y) x的y次幕
ABS(x) x的绝对值
CEIL(x) 返回大于或等于x的最大整数
FLOOT(x) 等于或者小于x的最大整数
MOD(x,y) x除以y的余数
ROUND(x[,y]) 四舍五入到小数点右边y位的x值
TRUNC(x[,y]) 返回截取到y为小数的x值
WIDTH_BUCKET(x,min,max,num_buckets)
范围min到max被分为num_buckets节，每节有相同的大小。返回x所在的那一节。如果x小于min,将返回0。如果x大于或等于max,则返回 num_buckets+1。


5.6 时间和日期函数
ADD_MONTH(d,x) 加上x月后的日期d的值。
CURRENT_DATE 以date类型返回会话时区当前的日期
CURRENT_TIMESTAMP[(precision)] 以TIMESTAMP WITH TIMEZONE 类型返回会话时区当前的日期。
DBTIMEZONE 返回数据库的时区
EXTRACE({YEAR,MONTH,DAY,HOUR,DATE字段仅在oracle8i中使用，其余用于MINUTE,SECOND,TIMEZONE_HOUR,TIMEZONE_MINUTE,TIMEZONE_REGION,TIMEZONE_ABBR} FROM datetime_or_interval)
返回从datetime_or_interval中选择的日期。
LAST_DAY(d) 返回d所在月份的最后一天
LOCALTIMESTAMP[(precision)] 以TIMESTAMP类型返回会话时区的当前日期。
MONTHS_BETWEEN(date1,date2) 返回date1和date2之间月的数目。
NEW_TIME(d,zone1,zone2) 当时区zone1中的日期和时间是d时，返回时区zone2中的日期和时间。
NEXT_DAY(d,string) 返回在日期d之后由string限定的第一天。
ROUND(d[,format]) 将日期d按照由format指定的格式进行四舍五入
SESSIONTIMEZONE 返回当前会话的时区。
SYS_EXTRACT_UTC(datetime) 从提供的datetime中以UTC返回时间。
SYSDATE 以Date类型返回当前日期和时间。
SYSTIMESTAMP 以TIMESTAMP WITH TIMEZONE类型返回当前日期和时间。
TRUNC(d[,format]) 返回截尾到由format指定的单位的日期上。
TZ_OFF(timezone) 以字符字符串返回提供的timezone和UTC之间的偏移量。

--------------------------------------------------------------------------------------
SQL> select extract(year from sysdate),NUMTODSINTERVAL(99,'MINUTE'),extract(minute from NUMTODSINTERVAL(99,'MINUTE')) from dual;

EXTRACT(YEARFROMSYSDATE) NUMTODSINTERVAL(99,'MINUTE') EXTRACT(MINUTEFROMNUMTODSINTER
------------------------ --------------------------------------- ------------------------------
2007 +000000000 01:39:00.000000000 39

SQL> select sysdate,to_char(sysdate,'fmDay'),next_day(sysdate,'星期四') from dual;

SYSDATE TO_CHAR(SYSDATE,'FMDAY') NEXT_DAY(SYSDATE,'星期四')
----------- ------------------------ --------------------------
2007-6-13 2 星期三 2007-6-14 22:14:33

--------------------------------------------------------------------------------------

5.7 转换函数
ASCIISTR(string) 返回只包含有效的SQL字符和斜线的字符串。
BIN_TO_NUM(num[,num]...) 将一个位矢量转换为相当的数字。
CHARTOROWID(string) 将包含外部格式rowid的char或者varchar转换成内部格式
COMPOSE(string) 以相同字符集中完全规格化Unicode形式返回string。
DECOMPOSE(string) 返回一个Unicode字符串，它是string的规范化分解。
FROM_TZ(timestamp,ktimezone) 返回一个TIMESTAMP WITH TIMEZONE类型值
HEXTORAW(string) 将包含十六进制的CHAR转换为一个RAW数值。
NUMTODSINTERVAL(x,unit) 将x转换为interval day to second值。unit是 day hour monute second
NUMTOYMINTERVAL(x,unit) 将x转换为interval year to month 值。unit是 year month
REFTOHEX(refvalue) 返回一个REF refvalue的十六进制表示。
RAWTOHEX(rawvalue) 将RAW数值转换为一个包含十六进制的CHAR值。
RAWTONHEX(rawvalue) 将RAW数值转换为一个包含十六进制的NCHAR值。
ROWIDTOCHAR(ROWID) 将一个ROWID数值转换为VARCHAR2数据类型。
ROWIDTONCHAR(ROWID) 将一个ROWID数值转换为NVARCHAR2数据类型。
TO_CHAR
TO_CLOB(string) 将string转化成CLOB
TO_DATE
TO_DSINTERVAL(string[,nlsparams]) 将string 转换成 interval day to second类型
TO_LOB(long_column) 将long_column转化成CLOB
TO_MULTI_BYTE(string) 将所有的单字节替换成等价的双字节
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE(string) 将所有的双字节替换成等价的单字节
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL(string) 将string 转换成 interval year to month 类型

---------------------------------------------------------------------------------
SQL> select BIN_TO_NUM(1,1,0,0,1) from dual;

BIN_TO_NUM(1,1,0,0,1)
---------------------
25
---------------------------------------------------------------------------------

5.8 聚合和分析函数
AVG([distinct|all]col) 求平均值
CORR(x1,x2) 略
COUNT(*|[distinct|all]col) 返回查询中的数目
GROUP_ID() 返回一个唯一数字用于在GROUP BY字句中辨别组
GROUPING
GROUPING_ID
MAX([distinct|all]col)
MIN([distinct|all]col)
RANK
SUM([distinct|all]col)
下面的都不懂：
COVAR_POP(x1,x2) 返回表达式x1和x2组成的集合的人口协方差
COVAR_SAMP(x1,x2) 返回表达式x1和x2组成的集合的相同协方差
CUME_DIST 返回一组值中一个值的累积分布
DENSE_RANK 返回有序分组的行中以行的一
PERCENTILE_CONT
PERCENTILE_DISC
PERVENT_RANK
REGR
STDDEV
STDDEV_POP(col)
STDDEV_SAMP(col)
VAR_POP(x)
VAR_SAMP(x)
VARIANCE([distinct|all]col)
注意count(*)和count(col)的区别
---------------------------------------------------------------------------------
SQL> select * from test2;

ID
---------------------------------------
0
1
2
3
4
5
6
7
8

10 rows selected

SQL> select count(*) from test2;

COUNT(*)
----------
10

SQL> select count(id) from test2;

COUNT(ID)
----------
9

SQL>
---------------------------------------------------------------------------------

5.9 其他函数
BFILENAME(directory,file_name) 返回操作系统中与物理文件file_name相关的BFILE位置指示符。
COALESCE(expr,...[exprn]) 返回参数列表中第一个非NULL的expr。
DECODE(base_expr,comare1,value1,...)
DUMP
EMPTY_BLOB/EMPTY_CLOB 返回一个空的LOB指示符
EXISTSNODE(XMLType_instace,Xpath_string)
EXTRACT(XMLType_instace,Xpath_string)
GREATEST(expr1[,expr2]) 返回其参数中最大的表达式
LEAST(expr1[,expr2]) 返回其参数中最小的表达式
NULLIF(a,b) 如果a等于b，返回NULL否则返回b
NVL(expr1,expr2) 如果expr1是null 返回expr2 否则返回expr1
NVL2(expr1,expr2,expr3) 如果expr1是null 返回expr2 否则返回expr3
SYS_CONNECT_BY_PATH()
SYS_CONTEXT
SYS_DBURIGEN
SYS_GUID 以16位RAW类型返回一个全局唯一的标识符
SYS_TYPEID(object_type) 返回指定对象的类型ID
SYS_XMLAGG
SYS_XMLGEN
TREAT
UID 当前数据库用户的整数表识
USER 当前数据库用户名
USERENV
VSIZE(x) 返回x内部表示的字节数

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/133835/viewspace-918798/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/133835/viewspace-918798/