PL/SQL学习笔记(一)

　　 

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

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

包：

数据库安全：

1、用户

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

3、权限

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

环境变量

-ORACLE_BASE基本目录

-ORACLE_NAME 当前的主目录

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

-PATH 路径

////////////////////////////////////////////////////////////////////////////////////////////////////////////

基本的SQL SELECT 语句

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

语句：

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

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

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

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

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

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

 

SELECT：

SELECT 查询列表 FROM 数据源；

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

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

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

 

DESCRIBE(desc) DEPARTMENTS
关键字  表名

描述表命令（SQLPLUS 命令）

SQL语句说明：

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

 

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

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

日期不能加日期，但日期可以减日期。

字符不能加减。

 

定义空（NULL）值

空值出现在表达式中会导致整个表达式的值为空。

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

annual_salary年薪

别名

可以加中文的字段别名。

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

spool +路径；
保存命令（将显示保存）

 

 

连接操作符：
 
 ||

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

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

文本字符串：

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

 

 

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

DISTINCT 消除重复行关键字，放在整查询列表的最前面。

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

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

select distinct department_id,job_id from employees;

 

SQLPLUS 与 SQL 的关系

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

*描述表的结构
*编辑SQL语句

查询 SQLPLUS 命令

help + 命令

SQLPLUSW 在 WINDOWS 下运行的分析器。
 

登陆ISQLPLUS

（1）先到服务中启动OracleOraHome92HTTPServer

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

SQLPLUS 命令：

与文件相关的命令：
  spool + 路径
 .
 .
 .
 .
   spool off

save
把当前内存中的语句保存为文本文件。

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

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

start @
读取并执行

get
读取不执行

 

编辑命令：

list
列出一条语句
*表示当前行

change
修改命令
原来c/jj/kk
c/jjj/xxx

input
在当前行之后插入一行新的数据

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

n
写数字显示对应行

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

edit

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

 

查看当前用户

SHOW USER

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

日期可以进行比较；

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

IN（set）或NOT IN 匹配任何列表中的值；

LIKE 模糊匹配字符串值；

IS NULL 是否空值；
IS NOT NULL 是否不为空；

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

 

通配符

%
S_mith

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

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

 

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

AND

OR

NOT

先执行 NOT ，再执行 AND 最后执行 OR。

 

ORDER BY

ORDER BY子句在 SELECT 语句的最后。 

ASC：升序
DESC：降序

空值作为无穷大来处理。

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

可以按照查询列表中序号进行排序。

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

 

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

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

character字符类型函数：

LOWER()强制小写

UPPER()强制大写

INITCAP()每个单词首字母大写

可以用在WHERE子句中。

CONCAT（‘’，‘’）连接函数
    {
 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代码点为单位。

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

 

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

 

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

 

LPAD（列名,数字,‘要补上的字符’）左补位

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

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

LTRIM（）左截取

RTRIM（）右截取

ascii(x)函数，返回'X'字符的十进制数，即X的ASCII码值。

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

length(x)函数，求串X的长度，与之相似的是lengthb(x)函数，用在多字节字符中。

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

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;

NLS函数
除了NCHR，这些函数都是以字符类型为参数返回字符类型值。

CONVERT(string,dest_charset[,source_charset])

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

NCHR(X)

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

NLS_CHARSET_DECL_LEN(byte_width,charset)

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

NLS_CHARSET_ID(charset_name)

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

NLS_CHARSET_NAME([charset_id])

返回指定字符集ID charset_id 的名字。如果 charset_id 是一个无效字符集ID，将返回NULL;

NLS_INITCAP(string[,nlsparams])

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

NLS_UPPER(string[,nlsparams])

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

NLS_LOWER

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

NLSSORT(string[,nlsparams])

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

TRANSLATE(string USING {CHAR_CSINCHAR_CS})

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

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

TRANSL
------
asd

SQL> select translate('asd' using CHAR_CS) from dual;--数据库字符集

TRA
---
asd

UNSTR(s)

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

general
//number
//conversion
//date

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

原文转自：http://www.ltesting.net