PL/SQL学习笔记(一)

基本概念：
                                                       
数据库对象：                                         
                                                          
表                                                    网管下载dl.bitscn.com
                                                      
约束条件：保证数据完整性。                            
                                                   
视图：虚表，命名的查询语句。                        
                                                      网管论坛bbs_bitsCN_com
索引：加速查询（加快查询的速度）。                 
                                                    
序列：一串连续递增或递减的数字，步长相同，（代理键）。
                                                                                                    网管网www_bitscn_com
同义词：一个对象的另外一个叫法（对象的别名）。                                                                                                
                                                                                              中国网管论坛bbs.bitsCN.com
存储过程：用于操作 网管网www.bitscn.com

函数：用作复杂运算的。用于计算。                                                                                         
                                                                                                网管网www_bitscn_com
触发器：由事件触发的存储过程。                                                                                         

网管下载dl.bitscn.com

包： 网管论坛bbs_bitsCN_com

数据库安全：

1、用户 网管网www_bitscn_com

2、方案或模式(Schema)：是用户所对应的对象的集合。用户名等于方案名 网管论坛bbs_bitsCN_com

3、权限 网管u家bitscn.net

4、角色：权限组，一组权限。
                                                      
5、配额(quota)：允许被使用的空间。用户可以在表空间上可以使用的空间。                                            
                                               
端口：2030 网管网www.bitscn.com

环境变量 网管u家bitscn.net

-ORACLE_BASE基本目录

-ORACLE_NAME 当前的主目录

网管u家www.bitscn.net

-ORACLE_NLS33
使用US7ASCLL字符集时不用设

-PATH 路径

网管联盟bitsCN_com

//////////////////////////////////////////////////////////////////////////////////////////////////////////// 网管网www.bitscn.com

基本的SQL SELECT 语句 网管论坛bbs_bitsCN_com

口令中的第一个字符不能为数字。

网管u家u.bitscn@com

语句：

（一）查询：SELECT
数据操作语句：DML（数据的插入INSERT、删除DELETE、修改UPDATE、合并MERGE）

网管网www.bitscn.com

（二）合并：把一个表中的数据合并到另一个表中去，如果数据在原表中存在做UPDATE，否则INSERT（9I独有）。 网管有家bitscn.net

（三）事务控制语句：COMMIT 提交、ROLLBACK 回滚、

SAVEPOINT 存储点（与 ROLLBACK 搭配使用）在回滚的时候可以回滚到某个存储点上。否则回滚到最初起点上。

网管论坛bbs_bitsCN_com

（四）数据定义语句：对对象操作。TRUNCATE 清除表中所有数据 /CREATE 创建 /DROP 删除 /ALTER 修改 

（五）权限控制语句（DCL）：GRANT 授予权限 /REVOKE 移除权限

网管联盟bitsCN@com

  网管下载dl.bitscn.com

SELECT：

SELECT 查询列表 FROM 数据源；

*&* SQL命令必须加分号。

ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK；
修改用户   解锁
给HR解锁

CONNECT(conn) HR/HR（密码） 用HR用户连接数据库。 
*&*（不是SQL命令 是 SQLPLUS 命令）不用加分号

网管有家bitscn.net

 

DESCRIBE(desc) DEPARTMENTS
关键字  表名 网管联盟bitsCN_com

描述表命令（SQLPLUS 命令）

SQL语句说明： 网管有家www.bitscn.net

（1）语句文本的书写不区分大小写。（但字符串在作为值的时候要注意大小写）
（2）语句可以写单行也可以写多行。
（3）关键字不能缩写或跨行。
（4）语句通常被分多行书写。
（5）缩进被用于提高语句的可读性。 网管u家u.bitscn@com

 

数字和日期都可以使用数学运算符建立表达式。
+，-，*，/ <>不等于 网管朋友网www_bitscn_net

日期可以加减数字，数字默认为天。

日期不能加日期，但日期可以减日期。 中国网管论坛bbs.bitsCN.com

字符不能加减。 网管有家www.bitscn.net

 

网管有家bitscn.net

定义空（NULL）值

空值出现在表达式中会导致整个表达式的值为空。 网管u家u.bitscn@com

NVL（字段名，将要赋予的值）函数
作用：将空值转换成其他有ASCLL码的值。

网管u家u.bitscn@com

annual_salary年薪

网管网www_bitscn_com

别名

网管u家u.bitscn@com

可以加中文的字段别名。 网管联盟bitsCN_com

如果想强制地改变列名的大小写，可以在别名的定义时加上双引号,列名有空格时也要在列名上加双引号。
例：
select lastname as "employees name" from employees; 网管论坛bbs_bitsCN_com

spool +路径；
保存命令（将显示保存） 网管u家u.bitsCN.com

  网管u家u.bitsCN.com

 

网管u家u.bitsCN.com

连接操作符：
 
 || 网管网www_bitscn_com

select lastname || 'work in' || department_id from tablename;

select last_name || '''s salary is ' || salary 员工月薪 from employees;
     ~~~ 
     在单引号中还要使用单引号的话，就必须使用两个单引号来实现一个单引号的功能。  网管u家u.bitsCN.com

文本字符串： 网管有家www.bitscn.net

*可以代表字符、数字或是日期。
*当代表字符或日期的时候用单引号括起来，数字不需要。 网管有家bitscn.net

  网管有家bitscn.net

 

在查询时默认显示所有的行，包括有重复值的行。

DISTINCT 消除重复行关键字，放在整查询列表的最前面。 网管下载dl.bitscn.com

作用范围：整个查询列表的组合。 网管bitscn_com

消除重复行后会按字段的特性，做升序排列。（执行过程：先排序，再消除重复） 网管u家bitscn.net

select distinct department_id,job_id from employees;

  网管u家u.bitscn@com

SQLPLUS 与 SQL 的关系 网管bitscn_com

SQL     *是一种语言
 *ANSI标准
 *关键字不能缩写
 *用于操作数据库中的数据和表的定义
  
SQLPLUS命令的功能： 网管bitscn_com

*描述表的结构
*编辑SQL语句 网管联盟bitsCN_com

查询 SQLPLUS 命令 网管联盟bitsCN@com

help + 命令 网管联盟bitsCN_com

SQLPLUSW 在 WINDOWS 下运行的分析器。
 

网管网www.bitscn.com

登陆ISQLPLUS 中国网管论坛bbs.bitsCN.com

（1）先到服务中启动OracleOraHome92HTTPServer 中国网管论坛bbs.bitsCN.com

（2）在浏览器中输入：http://wnj:7778/isqlplus
       ~~~~~~~~~~~~~~~ 
       URL(网页中的地址)
      
 

SQLPLUS 命令： 网管u家www.bitscn.net

与文件相关的命令：
  spool + 路径
 .
 .
 .
 .
   spool off 网管联盟bitsCN_com

save
把当前内存中的语句保存为文本文件。 网管u家www.bitscn.net

run 或  /
运行当前内存中的语句

clear buffer（cl buff）
清空当前内存中的语句

start @
读取并执行

get
读取不执行 网管下载dl.bitscn.com

  网管联盟bitsCN_com

编辑命令：

中国网管联盟bitsCN.com

list
列出一条语句
*表示当前行 中国网管论坛bbs.bitsCN.com

change
修改命令
原来c/jj/kk
c/jjj/xxx 网管u家u.bitscn@com

input
在当前行之后插入一行新的数据 网管u家u.bitscn@com

append
在当前行中插入新的东西

n
写数字显示对应行 网管联盟bitsCN@com

delete
del + 回车 删除当前行
del 1 3 删除第一到第三行

edit 中国网管论坛bbs.bitsCN.com

l,c,i,a,n,d,e

 

查看当前用户

网管联盟bitsCN_com

SHOW USER 中国网管联盟bitsCN.com

默认的日期格式
DD-MON-RR
日-月-年

日期可以进行比较；

字符可以进行比较（以字母的ASCLL码比较）； 网管网www_bitscn_com

IN（set）或NOT IN 匹配任何列表中的值； 网管联盟bitsCN_com

LIKE 模糊匹配字符串值； 网管bitscn_com

IS NULL 是否空值；
IS NOT NULL 是否不为空； 网管联盟bitsCN_com

BETWEEN 可以做数字、日期和字符的比较。

 

网管下载dl.bitscn.com

通配符

%
S_mith

网管网www.bitscn.com

WHERE first_name like 's/_%'escape'/';
    解释这个符号后的下划线为正常的字符。
    如果不加，将被视为通配符作用的下划线。 网管下载dl.bitscn.com

'_'只能通配一个字符
主要用于通配固定位数的字符。例如查询月收入五位数以上的员工。五位就可以用'_'来查询。

 

网管有家www.bitscn.net

逻辑操作符（用在WHERE子句中）

网管联盟bitsCN@com

AND 网管下载dl.bitscn.com

OR 网管有家bitscn.net

NOT

先执行 NOT ，再执行 AND 最后执行 OR。 网管u家u.bitsCN.com

  网管联盟bitsCN_com

ORDER BY 网管下载dl.bitscn.com

ORDER BY子句在 SELECT 语句的最后。  网管bitscn_com

ASC：升序
DESC：降序 网管u家u.bitscn@com

空值作为无穷大来处理。 网管u家u.bitsCN.com

rownum 显示行数量约束的关键字（在结果中可以做代理键使用）；

网管u家u.bitscn@com

可以按照查询列表中序号进行排序。 网管联盟bitsCN_com

系统在用户写出查询列表的同时就赋予每个列名一个序号，升序赋予。
例：
SELECT name,phone,adress from.........;
          1      2          3

  网管bitscn_com

单行函数：对单行数据进行计算并返回一个值的函数。

*修改数据项
*接受参数返回一个值。
*对每行进行操作。
*每行返回一个结果。
*可以修改数据类型
*可以嵌套 网管网www_bitscn_com

character字符类型函数：

LOWER()强制小写

UPPER()强制大写 网管u家bitscn.net

INITCAP()每个单词首字母大写 网管u家www.bitscn.net

可以用在WHERE子句中。

CONCAT（‘’，‘’）连接函数
    {
 SUBSTR(string,a[,b])返回string的一部分，a和b以字符为单位。 网管论坛bbs_bitsCN_com

 SUBSTRB(string,a[,b])返回string的一部分，a和b是以字节为单位。 网管网www_bitscn_com

 SUBSTRC(string,a[,b])返回string的一部分，a和b是以UNICODE完全字符为单位。

网管下载dl.bitscn.com

 SUBSTR2(string,a[,b])返回string的一部分，a和b是以UCS2代码点为单位。

 SUBSTR4(string,a[,b])返回string的一部分，a和b是以UCS4代码点为单位。

    以上函数都是返回string的一部分，从字符位置A开始，长为B个字符。如果A是0，那它就被认为是1（字符串的开始位置）。如果A是正数，那么字符从左边开始数。如果是负数，则从STRING的末尾开始，从右边数。如果B不存在，那么缺省是整个字符串。如果B小于1，将返回NULL。如果A或B使用了浮点数，那么该数值首先被节取成一个整数，返回类型与STRING相同。
    } 网管u家u.bitsCN.com

 

网管网www.bitscn.com

{
 LENGTH(string)
 LENGTHB(string)
 LENGTHC(string)
 LENGTH2(string)
 LENGTH4(string)
以上函数返回string的长度。因为CHAR类型的值是填充空格的，所以如果string是CHAR数据类型，那么末尾的空格算在长度之内。如果string是NULL，函数返回NULL。
}

网管u家www.bitscn.net

 

{
 INSTR(string1,string2[,a][,b])   返回string1中包含string2的位置。a和b以字符为单位。
 
 INSTRB(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b是以字节为单位。  中国网管联盟bitsCN.com

 INSTRC(string1,string2[,a][,b])返回string1中包含string2的位置。a和b是以UNICODE完全字符为单位。

网管u家bitscn.net

 INSTR2(string1,string2[,a][,b]) 返回string1中包含string2的位置。a和b是以UCS2代码点为单位

网管朋友网www_bitscn_net

 INSTR4(string1,string2[,a][,b]) 返回string1中包含string2的位置a和b是以UCS4代码点为单位。
以上函数返回string1中包含string2的位置。从左边开始扫描string1,起始位置是A。如果A为负数那么从右边开始扫描。第B次出现的位置将被返回。A和B缺省都为1，即返回在string1中第一次出现string2的位置。如果string2在A和B的规定下没有找到那么就返回0。位置的计算是相对于string1的开始位置的，而不关A和B的取值。
}

 

网管有家bitscn.net

LPAD（列名,数字,‘要补上的字符’）左补位 网管网www.bitscn.com

RPAD（列名，数字，‘要补上的字符’）右补位

网管bitscn_com

TRIM（‘child_str’ FROM ‘parents_str’）将连续子串（只能有一个字符）从主串的两边截取出来，区分大小写。
默认为截取空格。

LTRIM（）左截取 网管联盟bitsCN@com

RTRIM（）右截取 网管u家u.bitscn@com

ascii(x)函数，返回'X'字符的十进制数，即X的ASCII码值。 网管u家www.bitscn.net

chr(x)函数，返回ASCII码为X的字符。

length(x)函数，求串X的长度，与之相似的是lengthb(x)函数，用在多字节字符中。 中国网管论坛bbs.bitsCN.com

replace(x,y[,z])函数，返回值为将串X中的Y串用Z串替换后的结果字符串。若省略Z参数，则将串X中为Y串的地方删除。 中国网管联盟bitsCN.com

soundex(x)函数，返回串X的语音描述，这个描述由4个字符组成，说明串X的声音表示形式发音，有时在只知道一个名字的发音而不知道拼写情况下或许能用到。
例：select soundex('smith') from dual; 返回值为：S530.
 
translate(x,y,z)函数，返回将X串中每个字符按它在Y串中出现的位置翻译成Z串中相应位置的字符后的结果，相当与替换。
例：select translate('this is an example','my is','@#$%^&') from dual;

网管论坛bbs_bitsCN_com

NLS函数
除了NCHR，这些函数都是以字符类型为参数返回字符类型值。 网管联盟bitsCN@com

CONVERT(string,dest_charset[,source_charset]) 网管bitscn_com

将输入string转换为指定字符集dest_charset。source_charset是输入值的字符集——如果它没有被指定，则缺省为数据库字符集。输入值可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB和NCLOB类型。返回值为
VARCHAR2类型。如果dest_charset中没有输入字符串中的一个字符，将会使用一个代替字符（由dest_charset定义） 网管网www.bitscn.com

NCHR(X)

返回数据库国家字符集中值为X的字符。NCHR(X) 等价于CHR(x USING NCHAR_CS). 中国网管联盟bitsCN.com

NLS_CHARSET_DECL_LEN(byte_width,charset)

网管u家bitscn.net

返回一个NCHAR值的声明宽度(以字符为单位).byte_width是该值以字节为单位的长度charset是该值的字符集ID。 网管论坛bbs_bitsCN_com

NLS_CHARSET_ID(charset_name) 网管有家www.bitscn.net

返回指定字符集charset_name的数字ID。为charset_name指定“CHAR_CS”将返回数据库字符集的ID，为charset_name指定“NCHAR_CS”将返回数据库国家字符集的ID。如果charset_name是一个无效字符集名，将返回
NULL。NLS_CHARSET_ID和NLS_CHARSET_NAME是互为反函数。

网管网www_bitscn_com

NLS_CHARSET_NAME([charset_id])

返回指定字符集ID charset_id 的名字。如果 charset_id 是一个无效字符集ID，将返回NULL; 网管下载dl.bitscn.com

NLS_INITCAP(string[,nlsparams])

网管bitscn_com

以字符串中每个单词第一个字符大写而单词中其余字母小写的形式返回string.nlsparams指定了一个与该会话缺省的不同的排序次序。如果没有指定该参数，NLS_INITCAP与INITCAP相同。nlsparams应该采取下面的形式：
'NLS_SORT=sort',其中sort是一个语言排序序列。

网管u家u.bitscn@com

NLS_UPPER(string[,nlsparams])

网管u家u.bitsCN.com

以大写形式返回string,不是字母的字符不受影响。如果没有指定nlsparams,NLS_UPPER与UPPER相同。 网管bitscn_com

NLS_LOWER

网管有家www.bitscn.net

以小写形式返回string,不是字母的字符不受影响，如果没有指定nlsparams，NLS_LOWER与LOWER相同。

NLSSORT(string[,nlsparams]) 网管有家bitscn.net

返回用于排序string的字符串字节。所有值都被转换为字节字符串，这样在不同数据库之间就保持了一致性。
如果没有指定nlsparams,那么就会使用会话中缺省排序序列。 中国网管联盟bitsCN.com

TRANSLATE(string USING {CHAR_CSINCHAR_CS}) 网管u家u.bitsCN.com

TRANSLATE...USING 将输入string参数转换为数据库字符集（指定CHAR_CS）或数据库国家字符集（指定NCHAR_CS）.string可以是CHAR、VARCHAR2、NCHAR、NVARCHAR2类型。如果指定CHAR_CS，返回类型为VARCHAR2，如果指定NCHAR_CS,返回类型为NVARCHAR2。TRANSLATE...USING是CONVERT功能的子集。
如果输入值包含UCS2字符或反斜线符号要使用UNSTR函数。 网管有家bitscn.net

例：
SQL> select translate('asd' using NCHAR_CS) from dual;--数据库国家字符集 网管网www.bitscn.com

TRANSL
------
asd 中国网管联盟bitsCN.com

SQL> select translate('asd' using CHAR_CS) from dual;--数据库字符集 网管有家www.bitscn.net

TRA
---
asd 网管有家www.bitscn.net

UNSTR(s)

网管有家bitscn.net

返回转换为数据库UNICODE字符集的字符串。s可包含escaped UCS2 代码点字符。它由一个反斜线符号加上十六进制代码点数字组成。因此，要在字符串中包含一个反斜线符号就必须使用双反斜线符号(\\).UNISTR与
TRANSLATE...USING相似，差别是它仅能转换为UNICODE，而且可以包含escaped 字符。 网管下载dl.bitscn.com

general
//number
//conversion
//date 网管论坛bbs_bitsCN_com

多行函数：对多行数据（一组数据）进行计算并返回一个值的函数。

网管有家www.bitscn.net