plsql基础知识_站点基地[zdbase.com]

详细内容

[关闭窗口]

plsql基础知识

发布日期：2012-10-23 点击：3366 字体：[大中小]

一、关系数据库的一些概念
1、主键的值一般不可以改变
2、外键：指向另一个表或本表的主键或唯一键的字段。外键的值一定要和某一主键相同，或者为空。
3、数据库对像：表，视图，序列，索引，同义词，程序（进程，函数，sql和pl/sql数据）4、sql command 类别
data retrieval数据检索:select
data manipulationlanguage (DML)数据操作语言:insert,update,delete
data definition language (DDL)数据定义语言：create,alter,drop,rename,truncate
transaction control事务控制:commit,rollback,savepoint
data control language（DCL）数据控制语言：grant,revoke
DCL 和DDL命令的执行会导致一次隐式提交，之前未提交的操作（包括DML 命令）都会提交写入日志文件，并在适当地时候写入数据文件。
二、SQL的语法
1) 连接号：||
2) 把两个字符连接起来
eg:select game_card_type_id||name from game_card_type;
3) select distinct dept_id,title from emp：对多个字段的唯一
4) order by desc(降序)
order by asc(升序)
5) where column is (not) null
6) like ‘_a%’ _表示一个字符。％表示多少字符
like ‘%x\_y%’ escape ‘\’:显示包括x_y的字符
7) where table1.column(+)=table2.column
place the operator on the side of the join where there is no value to join to.
8) 联接类型:
equijoin:等式查询
non_equijoin:不等式查询
self:自己和自己建立关联
out join:where a.column=b.column(+)
可以用的操作符是：’=’，’and’，不可以用’or’，’in’
9)  COUNT 函数所用的列包含空值时，空值行被忽略。
10)  where 后的in any all 的区别
in ：等于子查询的任何一个数
any ：与子查询的每一个值相比
只要比其中一个大（小）就可以了
all:与子查询的所有值相比要比所有的的都大（小）
!=ALL作用跟NOT IN 一样

三、SQL*PLUS的环境(可以在glogin.sql中定义初始参数)
1) START 命令用以执行一个已储存的文件,等同于@
2) SAVE命令用以创建一个文件
3) EDIT命令用以调用编辑器编辑已存文件的内容
4) CHANGE 是SQL*Plus的编辑命令，用于在当前一行把旧文段转换为新文段
5) DEL 命令用以删除文本中一行或多行文字
6) SPOOL命令用以把查询结果储存在一个已有文件中，注意与SAVE区别
7) GET命令用以一个文件的内容写进一个SQL块中
8) SPOOL OUT命令用以把文件的结果发送到系统打印机。
9) set pause off/on:设置页面的滚动。按enter看下一页。
10) PAGESIZE：指定每页显示的行的数值，
11) LONG：设置LONG类型数据显示的最大宽度
12) FEEDBACK：设置查询返回记录的最大值
13) DESCRIBE :用于显示表和视图的结构，同义词，或指定函数和过程的详述。
14) Timing：可以看到语句执行的时间
15) Autotrace：可以看到sql的执行计划，sysdba执行/home/oracle/product/9.2.0/sqlplus/admin/plustrce.sql脚本，而且必须把plustrace角色赋给执行用户。执行用户必须运行/home/oracle/product/9.2.0/rdbms/admin/utlxplan.sql
16) 在各种数据类型中，只有NUMBER数据类型的默认显示是靠右对齐的，而CHAR, DATE和VARCHAR2是靠左对齐的
17) QUIT 是SQL*PLUS命令，用以结束一个SQL*PLUS的对话。
18) ttitle:
   ttitle ‘selina’ 设select 的结果的抬头为selina
tti ：显示ttitle的状态
tti off/on
btitle ‘end’ 设结果的尾部
19) Column：
a) column name heading ‘名字’format a15
b) column id justify left format 999999
c) column start_date format a9 null ‘not hired’//当字段为空的时候则显示成not hired
d) column :显示所有的column设置
e) column columnname:显示某一个字段的设置
f) clear column :清除所有column设置
g) column columnname clear:清除某一字段的设置
h) 以上的column可用col代替.clear可以用cle代替
   13）定义变量
用&：由用户输入变量值，此变量可以存在于where后，做为整个查询语句的变量。也可以在order by 后。做为字段的变量。也可以放在select 后，做为字段或表达式的变量。
用&&：如果多个地方引用此变量。。只用输入一次
   SQL> SELECT empno, ename, job, &&column_name
      FROM emp
      ORDER BY &column_name;

accept：由用户输入变量值
  accept 变量名 datatype prompt ‘告诉用户需要输入的信息：’ hide
  引用的时候：&变名
define(undefined)：一开始就定义变量值
四、函数：
1、字符函数
lower:把字符转成小写
upper:把字符转成大写
initcap:把单词的第一个字母变成大写
concat:连接字符  concat(‘good’,’morning’)=goodmoring
SUBSTR (column\expression, m[,n]) 用于对字符串进行截取操作，从第m个位置开始，把其后的连续n个字符的部分截取下来，如果m位负值，则从末尾开始计算。
eg:substr(‘string’,1,3) =str
      substr(‘string’-3,3)=ing
INSTR('String', 'r')=3
LPAD(sal,10,'*') =*******sal
length: 用于返回表达式中的字符数，注意返回的是NUMBER。
NVL(expression1, expression2)
NVL 函数用以把一个空值转换为一个实值,如：
NVL(100/quantity, 0) ,要是quantity为空值，该函数返回一个0。
如果两个字段类型不同必须进行转换。
Min():返回最小值。。如果是字符。。A<a

2、数学函数
round:四舍五入
round(2.566,2)=2.27
round(45,-1)=50
trunc: 截断
  trunc(2.566,2)=2.56
  trunc(45,-1)=40
mod:
  mod(m,n):m-n*flood(m/n)    //flood是取整数

3、日期函数:
a) months_between(date1,date2)：算date1和date2之间的月的数量,可以是小数可以是负数
months_between(’01-sep-95’,’11-jan-94’)=1.9774194
b) add_months(date,n)：为date加上N个月，N只可以是整数
c) next_date(date,’char’)：查找date的下一个星期N
next_date(’01-sep-95’,’FRIDAY’)=08-SEP-95
d) last_day(date):查找date月的最后一天。
e) rount(date)：把日期四舍五入
f) rount(25-MAY-95’,’MONTH’)=01-JUN-95
g) rount(25-MAY-95’,’YEAR’)=01-JAN-95
h) trunc(date)：把日期截断
i) trunc (25-MAY-95’,’MONTH’)=01-MAY-95
j) trunc (25-MAY-95’,’YEAR’)=01-JAN-95
k) 日期中RR与YY的区别，RR格式对日期作类似于舍入的操作，YY格式对日期作类似于截取的操作
            RR YY
1995 27-oct-95 1995 1995
1995 27-oct-17 2017 1917
2001 27-oct-17 2017 2017
2001 27-oct-95 1995 2095
用法：select to_char(sysdate, 'YY') from dual;
select to_char(to_date('95-11-27', 'RR-MM-DD'), 'YYYY-MM-DD') from dual;
select to_char(to_date('95-11-27', 'YY-MM-DD'), 'YYYY-MM-DD') from dual;
4、转换函数
TO_CHAR：
TO_CHAR(date,’fmt’)：fm前缀用来去除首尾的空字符或0
TO_CHAR(total,’fm$999999’)
   如果想转成$0.25,那就要写成fm$9999990.99
可以把日期转换成字符
   TO_CHAR(log_time,’MM/YY’)
   TO_CHAR(lot_time,’fmdd’’of;’’mm yyyy’)
   具体格式如下
HH24:MI:SS AM-----------15:24:32 pm
DD’’of’’MONTH-----------12 of MAY
Ddspth------------------------fourteenth
Ddsp--------------------------fourteen
ddth---------------------------4th
YYYY-----------------------1978
MM-----------------------------12
MONTH-------------------------MAY
5、group 函数avg,count,max,min,stddev,sum,variance

五、数据字典
用户表：由用户创建，包含用户的内容
数据字典：由系统建立，包含数据库的信息

前缀:
USER_ ：由用户创建,显示用户拥有的所有对象。

ALL_ ：由受权的用户访问, 用户可以访问的对象名。
DBA_ ：由受了DBA权限的人访问，显示数据库的所有对象。
V$ ：由受了DBA权限的人访问，显示数据字典数据库服务器性能信息。通常是DBA用于显示系统的统计表和动态性能表。

数据字典
DICTIONARY数据字典提供用户可以访问的数据字典表和视图的描述。
USER_OBJECTS显示用户拥有的对象。
USER_VIEWS显示用户拥有的视图。
USER_TABLES显示用户拥有的表。
ALL_TABLES显示用户可以访问的表。
ALL_VIEWS显示用户可以访问的视图。
USER_CATALOG显示用户拥有的所有表，视图，同义词和序列。
USER_CONS_COLUMNS显示带约束的列。
DBA_CONS_COLUMNS显示数据库里的所有表，视图和同义词。
USER_TAB_PRIVS_MADE：本用户赋给别的用户赋予权限的表
USER_TAB_PRIVS_RECD：其他用户给本用户赋予权限的表
USER_COL_PRIVS_MADE：本用户赋给别的用户赋予权限的字段
USER_COL_PRIVS_RECD：其他用户给本用户赋予权限的字段
ROLE_SYS_PRIVS:有什么系统权限赋给role
ROLE_TAB_PRIVS:有什么关于表的权限赋给role
USER_ROLE_PRIVS:role和用户的对应表

常用的表
user_objects：用户对象表（存储用户的所有对象）存储以下的类型的数据
Selina Sql>select distinct object_type from user_objects;
INDEX
LOB
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER
VIEW
User_catalog:用户类表，存储以下的类型的数据
Selina Sql>select distinct table_type from user_catalog;
SEQUENCE
SYNONYM
TABLE
VIEW

六、建立对象
1、表
a) 基本概念
? 数据库会分配一定的空间从而定义表的大小，所以表的大小不用指定。
? 表可容纳最多1000列。
? 表可在用户使用数据库的同时创建，而且表的结构可在表联机时修改。
? RENAME语句的语法 : RENAME old name TO new name; 注意要是表名中包含空格，符号或数字，必须使用双引号
? 在CREATE TABLE子句中使用子查询，新表中的列数必须与子查询返回的列数相等，列位置对应。另外，除NOT NULL约束外，新列不会继承源列的所有约束。
? 你不能对一列重命名。在表中添加一列时，你不能指定该列的位置，它会被默认放置在最后。你可以为一列增加长度。通常当一列包含数据时，你不能修改该列的数据类型，不能减少一列的长度，但当列中包含空值或你不改变列的大小时，你可以把CHAR 转换为VARCHAR2数据类型。
? 在DROP TABLE table命令后加上CASCADE CONSTRAINTS回把表中的相关约束一并删除。如命令："DROP TABLE employee CASCADE CONSTRAINTS;"会把employee表中的数据，结构，和相关约束一并删除。
? 当你创建一个数据类型为VARCHAR2的列时，必须指定长度。
? 为表加注释:comment on table tablename  is ‘………’;
? COMMENT ON TABLE命令用于在数据字典里添加关于表，视图或快照的注释。
? 但你对一表执行了DML语句的INSERT操作时，但没有提交，别人可以同时访问该表，但看不到你所作的修改，由于对象被加锁，所以别人不能作同样的修改。
b) 建表原则
以字母开头
不可以超过30个字
只能由数字,字母,_,$,#组成
用子查询建立表
create table tablename as select …..

c) 删除表
drop table tablename
删除所有的数据
删除所有相关的index
不可以回滚

truncate table tablename
删除所有的数据
释放该表所占用的空间
不可以回滚
将存储容量参数重置为定义值
要成功执行TRUNCATE TABLE 命令，你必须拥有该表或拥有DELETE TABLE的系统权限。

2、约束
? 约束类别
column level:
NOT NULL
table level:
UNIQUE Key
PRIMARY Key
FOREIGN Key
CHECK
CHECK can be defined either on column level or on table level. Plus, you can say something like:
create table abc
(
column1 number,
column2 number,
constraint less_ck CHECK (column2 < column1)
);
This constraint will make sure column2 is less than column1 when you enter data into the table.

? 建立约束
Table constraint level
column,...
  [CONSTRAINT constraint_name] constraint_type  (column, ...),
EG:
CREATE TABLE emp(
  empno  NUMBER(4),
  ename  VARCHAR2(10),
  deptno  NUMBER(7,2) NOT NULL,
CONSTRAINT emp_empno_pk PRIMARY KEY (EMPNO))
CONSTRAINT emp_deptno_fk FOREIGN KEY (deptno)
         REFERENCES dept (deptno)
         CONSTRAINT emp_deptno_ck  CHECK (DEPTNO BETWEEN 10 AND 99));
? 修改约束
添加：
ALTER TABLE 命令可为一个现有的表添加一个约束，所有约束都是用ADD来添加，但是为一个列添加NOT NULL 约束，只能使用MODIFY子句，但需注意的是，添加NOT NULL约束时，必须符合以下两个条件中的一个：1.表中没有数据 2.添加约束的目标行没有空值。
ALTER TABLE emp ADD CONSTRAINT  emp_mgr_fk
FOREIGN KEY(mgr) REFERENCES emp(empno);
            删除：
SQL> ALTER TABLE    emp
  2  DROP CONSTRAINT  emp_mgr_fk;
SQL> ALTER TABLE dept
  2  DROP PRIMARY KEY CASCADE;
删除primary key ，而添加CASCADE则表示相关的完整性约束也一并删除。
禁用/启用
SQL> ALTER TABLE emp
  2  DISABLE/enable CONSTRAINT emp_empno_pk CASCADE;

? 数据字典
USER_CONSTRAINTS
SQL>  SELECT constraint_name, constraint_type,
  2 search_condition
  3 FROM user_constraints
  4 WHERE table_name = 'EMP';

USER_CONS_COLUMNS(查看被约束的column)
SQL> SELECT constraint_name, column_name
  2  FROM user_cons_columns
  3 WHERE table_name = 'EMP';

? 知识点：如果A表中的a列在B表中拥有一个外键约束，在你删除B表记录之前，你必须先删除A表中的所有子记录。如果你尝试在A表中插入一个记录，而B表中不存在它的副记录的会得到一个违反约束的错误。

3、序列
CREATE（ALTER） SEQUENCE name
[INCREMENT BY n] ；指定序列以n递增，如果没指定，默认值1会使用
[START WITH n] ；由n开始计数
[(MAXVALUE n | NOMAXVALUE)] ；设置最大值n或不设置最大值
[(MINVALUE n | NOMINVALUE)] ；设置最小值n或不设置最小值
[(CYCLE | NOCYCLE)] ；当达到最大值时循环或不循环
[(CACHE n | NOCACHE)] ；预分配缓存大小，默认为20

删除序列
drop sequence name

CURRVAL伪列用于在当前序列中检索连续序列号，它能用在UPDATE语句的SET子句中和INSERT语句的VALUES子句中，还可以用在INSERT语句的子查询的SELECT列表中。但不能用于视图的SELECT语句和带HAVING子句的SELECT语句中。

4、视图
a) 建立
修改一个视图最简单的方法是使用带OR REPLACE的 CREATE VIEW语句，这允许视图的旧版本被代替，避免了删除视图和重建视图的必要，要是删除了视图，你必须在新视图上重新给对象授权。
create [or replace][force/noforce] view viewname
as subquery             //子查询不可以用order by
[with check option]  //则指明只有允许被视图访问的行才能被插入或更新。
[constraint constraintname]
[with read only] //不可以通过view进行表的修改

b) 对view的DML操作的限制
可以在simple view执行DML操作
不可以删除一行如果view 包含
   group funtion
   a group by clause
   the distinct command
不可以修改数据如果view包含
   以上说的条件
由表达式定义的列
   the rownum pseudocolumn
不可以添加数据，如果view包含：
   以上所有的条件
   存在非空的字段没在包含在view中

c) 数据字典
USER_VIEWS数据字典显示用户所拥有的视图的描述
ALL_VIEWS数据字典显示用户有权访问的视图的描述
ALL_OBJECTS数据字典显示用户有权访问的对象（包括视图）的信息USER_OBJECTS数据字典显示用户所拥有的对象的描述
5、序列
CREATE SEQUENCE sequence [INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];       //序列放在内存中。使读取更快
eg: SQL> CREATE SEQUENCE dept_deptno
  2 INCREMENT BY 1
  3 START WITH 91
  4 MAXVALUE 100
  5 NOCACHE
  6 NOCYCLE;
可以在USER_SEQUENCES查看所建立的表
SQL> SELECT sequence_name, min_value, max_value,
  2    increment_by, last_number
  3  FROM user_sequences;
?The LAST_NUMBER ：显示nextval的值

?Gaps in sequence values can occur when:
–发生rollback
–系统崩溃
–另一个表在用此序列
你必须是the sequence.的拥有者和对此the sequence.有ALTER privilege 的权限
如果想改开始值的话只有重建序列

6、索引
a) 建立
? 自动：当指定表的列为primary key or union时。系统自动为此表建立一个index
? 手动：可以为表的非唯一值的列手动建立index
? create index indexname on table(column)

? 数据字典
用户建立的索引在USER_INDEXES 中(详细的信息)
USER_IND_COLUMNS (列的一些简单信息)
SELECT ic.index_name, ic.column_name,ic.column_position col_pos,ix.uniqueness
  FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = ' ANNOUNCEMENT';

? 建立索引的方针
在where 和join中经常用到的列
列的值范围很大
此列包含很多空值
表很大，但只要其中的2-4％的数据
很多索引不一定能提高速度。

? 不建索引的方针
表很小
列不常用来查询
查询结果超过表的2-4％
表常被更新

7、同义词
建立：create synonym 别名 for 原名
CREATE SYNONYM d_sum  FOR dept_sum_vu;
删除： DROP SYNONYM d_sum;
在创建同义词时，语句中出现PRIVATE 和OR REPLACE 是无效的

七、安全
数据库的安全：包括系统安全和数据安全
1. 系统权限:访问数据库的权限，用于执行数据定义和数据控制命令，和其他数据导向性行为。CREATE SESSION,CREATE USER, DROP USER, 和 BACKUP ANY TABLE,这些是系统权限
2. 用户系统权限:建立会话,建立表,建立序列,建立视图,建立过程

3. 赋权
grant select on tablename to username (public//给所有用户)
   with grant option; //被赋予的人就可以把权限再给别人
         GRANT update (dname, loc)  ON dept  TO scott, manager;
可以具体到列
GRANT ALL ON inventory TO joe;
这命令使用了关键字ALL,向joe授予了在inventory表上ALTER, INDEX, INSERT, REFERENCES, SELECT, UPDATE, 和DELETE的权限。

4. 消权限
revoke select ,insert on tablename from username
REVOKE references
ON inventory
FROM joe
CASCADE CONSTRAINTS;
这命令实现了两个作用：
1 所有由joe创建的FOREIGN KEY 约束被删除
2 他创建 FOREIGN KEY约束的能力被取消了。

撤消只能相对表不能相对字段
如果撤消了某个用户，那么由这个用户为其他用户赋的权限也一起取消
要是你想通过命令REVOKE来回收用户A的某一权限，那你必须是当初的授权人。

5. 知识点：
a) INDEX对象权限可被授予一个用户，但不能授予一个角色（role）。它允许用户使用CREATE INDEX
b) 对象权限REFERENCE只能授予用户，不能授予角色（role）。
c) 只有拥有DBA权限或ALTER UAER权限的用户才能替普通用户修改密码
d) 要能创建用户，你必须拥有CREATE USER权限。要想有权访问数据库，你需拥有CREATE SESSION权限。要想在任何方案（schema）中创建和删除次表，你必须拥有CREATE ANY TABLE 和DROP ANY TABLE权限，要想在自己的方案（schema）中创建和删除次表，你必须拥有CREATE TABLE 和DROP TABLE权限。
e) 如果要删除kate select 表的权限。那么删除者必须是当初给kate赋权限的人
f) CREATE OR REPLACE ROLE rolename是个无效的命令
正确的只有CREATE ROLE rolename
g) 如何一次性删除某个用户的所有对象?
drop user username cascade
八、事务结束标志
明确的提交：
当 commit or rollback command is  issued
暗含的的结束：
ddl command,例如create,或dcl command例如 grant
发生死锁
退出sqlplus，系统自动回滚
硬件或系统出错，系统自动回滚

commit后的操作
数据写入数据库
之前的数据会丢失
解除受影响的行的锁
所有的savepoints被释放

savepoint的用法
2. update……………
3. savepoint update
4. insert into …..
rollback to update

用户评论