1.SELECT 字段列表 FROM 表名 WHERE 条件 ORDER BY 字段名1 [ASC|DESC][,字段名2 [ASC|DESC]...];
(在组函数中可使用DISTINCT或ALL关键字)
2.插入数据:
INSERT INTO 表名[(字段列表)] VALUES ( 表达式列表);
3.复制数据:
INSERT INTO 表名(字段列表) SELECT(字段名1, 字段名2, ...) FROM 另外的表名;
4.修改数据的语句UPDATE的基本语法如下:
UPDATE 表名 SET 字段名1=表达式1, 字段名2=表达式2, ... WHERE 条件;
5.删除数据
删除数据的基本语法如下:
DELETE FROM表名 WHERE 条件; (删除满足条件的记录)
TRUNCATE TABLE 表名; (删除一个大表里的全部记录,表依旧存在)
6.事务处理的数据库事务操作语句:
commit rollback savepoint
7.创建表的语法
表的创建需要CREATE TABLE 系统权限,表的基本创建语法如下:
CREATE TABLE 表名
(列名 数据类型(宽度)[DEFAULT 表达式]
[COLUMN CONSTRAINT],
...
[TABLE CONSTRAINT]
);
通过子查询创建表
CREATE TABLE 表名(列名...) AS SQL查询语句;
8.删除已创建的表
删除表的语法如下:
DROP TABLE 表名[CASCADE CONSTRAINTS];
9.表的操作:
表的重命名
语法如下:
RENAME 旧表名 TO 新表名;
添加注释
(1) 为表添加注释的语法为:
COMMENT ON TABLE 表名 IS '...';
(2) 为列添加注释的语法为:
COMMENT ON COLUMN 表名.列名 IS '...';
注意:如IS后的字符串为空,则清除表注释。
表中的五种约束:
主键、非空、惟一、检查和外键
主键约束的语法如下:
[CONSTRANT 约束名] PRIMARY KEY --列级
[CONSTRANT 约束名] PRIMARY KEY(列名1,列名2,...) --表级
非空约束语法如下:
[CONSTRANT 约束名] NOT NULL --列级
惟一约束的语法如下:
[CONSTRANT 约束名] UNIQUE --列级
[CONSTRANT 约束名] UNIQUE(列名1,列名2,...) --表级
检查约束的语法如下:
[CONSTRANT 约束名] CHECK(约束条件) --列级,约束条件中只包含本列
[CONSTRANT 约束名] CHECK(约束条件) --表级,约束条件中包含多列
外键约束的语法如下:
第一种语法,如果子记录存在,则不允许删除主记录:
[CONSTRANT 约束名] FOREIGN KEY(列名1,列名2,...)REFERENCES 表名(列名1,列名2,...)
第二种语法,如果子记录存在,则删除主记录时,级联删除子记录:
[CONSTRANT 约束名] FOREIGN KEY(列名1,列名2,...)REFERENCES 表名(列名1,列名2,...)on delete cascade
第三种语法,如果子记录存在,则删除主记录时,将子记录置成空:
[CONSTRANT 约束名] FOREIGN KEY(列名1,列名2,...)REFERENCES 表名(列名1,列名2,...)on delete set null (其中的表名为要参照的表名)
在以上5种约束的语法中,CONSTRANT关键字用来定义约束名,如果省略,则系统自动生成以SYS_开头的惟一约束名。
10.增加新列
增加新列的语法如下:
ALTER TABLE 表名
ADD 列名 数据类型[DEFAULT 表达式][COLUMN CONSTRAINT];
11.修改列的语法如下:
ALTER TABLE 表名
MODIFY 列名 数据类型 [DEFAULT 表达式][COLUMN CONSTRAINT]
12.删除列
删除列的语法如下:
ALTER TABLE 表名
DROP COLUMN 列名[CASCADE CONSTRAINTS];
13.视图的创建
视图的创建语法如下:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW 视图名[(别名1[,别名2...])]
AS 子查询
[WITH CHECK OPTION [CONSTRAINT 约束名]]
[WITH READ ONLY];
删除视图的语法如下:
DROP VIEW 视图名;
14.索引的创建
创建索引不需要特定的系统权限。建立索引的语法如下:
CREATE [{UNIQUE|BITMAP}] INDEX 索引名 ON 表名(列名1[,列名2,...]); (不指明则默认为创建非惟一、B*树索引索引)
删除索引的语法是:
DROP INDEX 索引名;
15.序列的创建
序列的创建语法如下:
CREATE SEQUENCE 序列名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n|NOMAXVALUE}]
[{MINVALUE n|NOMINVALUE}]
[{CYCLE|NOCYCLE}]
[{CACHE n|NOCACHE}];
删除序列的语法是:
DROP SEQUENCE 序列名;
序列的使用:
产生序列的当前值:序列名.CURRVAL.
生成序列中的下一个序列号:序列名.NEXTVAL
查看序列:
查看用户的序列:
SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER FROM USER_SEQUENCES;
16.PL/SQL程序
(1) DECLARE:声明部分标志。
(2) BEGIN:可执行部分标志。
(3) EXCEPTION:异常处理部分标志。
(4) END:程序结束标志。
17.PL/SQL程序中的输出:
第一种形式:
DBMS_OUTPUT.PUT(字符串表达式);
用于输出字符串,但不换行,括号中的参数是要输出的字符串表达式。
第二种形式:
DBMS_OUTPUT.PUT_LINE(字符串表达式);
用于输出一行字符串信息,并换行,括号中的参数是要输出的字符串表达式。
第三种形式:
DBMS_OUTPUT.NEW_LINE;
用来输出一个换行,没有参数。
用来打开DBMS_OUTPUT.PUT_LINE函数的屏幕输出功能:
SET SERVEROUTPUT ON [SIZE n] (系统默认状态是OFF)
18.接收查询结果的变量:
SELECT 列名1,列名2... INTO 变量1,变量2... FROM 表名 WHERE 条件;
19.变量定义的方法是:
变量名 [CONSTANT] 类型标识符 [NOT NULL][:=值|DEFAULT 值];
在程序中为变量赋值的方法是:
变量名:=值 或 PL/SQL 表达式;
记录变量的定义:
记录变量的定义方法是:
记录变量名 表名%ROWTYPE;
获得记录变量的字段的方法是:记录变量名.字段名,如emp_record.ename。
20.结构控制语句
IF语句有如下的形式:
IF 条件1 THEN
语句序列1;
[ELSIF 条件2 THEN
语句序列2;
ELSE
语句序列n;]
END IF;
根据具体情况,分支结构可以有以下几种形式:
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-ELSE-END IF
21.选择结构:
基本CASE结构
语句的语法如下:
CASE 选择变量名
WHEN 表达式1 THEN
语句序列1
WHEN 表达式2 THEN
语句序列2
WHEN 表达式n THEN
语句序列n
ELSE
语句序列n+1
END CASE;
表达式结构CASE语句:
它的基本结构如下:
变量=CASE 选择变量名
WHEN 表达式1 THEN 值1
WHEN 表达式2 THEN 值2
WHEN 表达式n THEN 值n
ELSE值n+1
END;
搜索CASE结构:
CASE
WHEN 条件表达式1 THEN
语句序列1
WHEN 条件表达式2 THEN
语句序列2
WHEN 条件表达式n THEN
语句序列n
ELSE
语句序列n+1
END CASE;
22.循环结构:
PL/SQL循环结构可划分为以下3种:
* 基本LOOP循环。
* FOR LOOP循环。
* WHILE LOOP循环。
基本LOOP循环
基本循环的结构如下:
LOOP --循环起始标识
语句1;
语句2;
EXIT [WHEN 条件];
END LOOP; --循环结束标识
(EXIT用于在循环过程中退出循环,WHEN用于定义EXIT的退出条件)
FOR LOOP循环
FOR循环是固定次数循环,格式如下:
FOR 控制变量 in [REVERSE] 下限..上限
LOOP
语句1;
语句2;
END LOOP;
(REVERSE关键字表示循环控制变量的取值由上限到下限递减)
WHILE LOOP循环
WHILE循环是有条件循环,其格式如下:
WHILE 条件
LOOP
语句1;
语句2;
END LOOP;
23.游标的定义和操作:
A.声明游标
在DECLEAR部分按以下格式声明游标:
CURSOR 游标名[(参数1 数据类型[,参数2 数据类型...])] IS SELECT语句;
B.打开游标
在可执行部分,按以下格式打开游标:
OPEN 游标名[(实际参数1[,实际参数2...])];
(打开游标时,SELECT语句的查询结果就被传送到了游标工作区)
C.提取数据
提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1[,变量名2...];
或
FETCH 游标名 INTO 记录变量;
D.关闭游标
CLOSE 游标名;
(显式游标打开后,必须显式地关闭)
显式游标属性:
游标的属性:%ROWCOUNT 返回值类型:整型 意义:获得FETCH语句返回的数据行数
游标的属性:%FOUND 返回值类型:布尔型 意义:最近的FETCH语句返回一行数据则为真,否则为假
游标的属性:%NOTFOUND 返回值类型:布尔型 意义:与%FOUND属性返回值相反
游标的属性:%ISOPEN 返回值类型:布尔型 意义:游标已经打开时值为真,否则为假
可按照以下形式取得游标的属性:
游标名%属性;
24.异常处理
错误处理的语法如下:
EXCEPTION
WHEN 错误1[OR 错误2] THEN
语句序列1;
WHEN 错误3[OR 错误4] THEN
语句序列2;
WHEN OTHERS
语句序列n;
END;
自定义异常:
错误名 EXCEPTION;
用户定义的错误不能由系统来触发,必须由程序显式地触发,触发的语法是:
RAISE 错误名;
使用RAISE_APPLICATION_ERROR函数引发系统异常
使用RAISE_APPLICATION_ERROR函数也可以引发异常。该函数要传递两个参数,第一个是用户自定义的错误编号,第二个参数是用户自定义的错误信息。使用该函数引发的异常的编号应该在-20 000和-20 999之间选择。
25.创建和删除存储过程
A.创建一个存储过程的基本语句如下:
CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]
{AS|IS}
[说明部分]
BEGIN
可执行部分
[EXCEPTION
错误处理部分]
END ;
删除存储过程的语法如下:
DROP PROCEDURE 存储过程名;
重新编译一个存储过程:
ALTER PROCEDURE 存储过程名 COMPILE;
B.执行(或调用)存储过程:
执行的方法如下:
方法1:
EXECUTE 模式名.存储过程名[(参数...)];
方法2:
BEGIN
模式名.存储过程名[(参数...)];
END;
参数的类型有三种:
参数类型:IN 说明:定义一个输入参数变量,用于传递参数给存储过程
参数类型:OUT 说明:定义一个输出参数变量,用于从存储过程获取数据
参数类型:IN OUT 说明:定义一个输入、输出参数变量,兼有以上两者的功能
参数名 IN 数据类型 DEFAULT 值;(定义一个输入参数变量,用于传递参数给存储过程)
参数名 OUT 数据类型;(定义一个输出参数变量,用于从存储过程获取数据)
参数名 IN OUT 数据类型 DEFAULT 值;(定义一个输入、输出参数变量,兼有以上两者的功能)
(如果省略IN、OUT或IN OUT,则默认模式是IN)
在SQL*Plus输入区中输入并编译以下存储过程:
CREATE OR REPLACE PROCEDURE EMP_LIST
AS
CURSOR emp_cursor IS
SELECT empno,ename FROM emp;
BEGIN
FOR Emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);
END LOOP;
EMP_COUNT;
END;
步骤2:调用存储过程,在输入区中输入以下语句并执行:
EXECUTE EMP_LIST
26.创建和删除存储函数
创建存储函数的语法和创建存储过程的类似,即
CREATE [OR REPLACE] FUNCTION 函数名[(参数[IN] 数据类型...)]
RETURN 数据类型
{AS|IS}
[说明部分]
BEGIN
可执行部分
RETURN (表达式)
[EXCEPTION
错误处理部分]
END [函数名];
其中,参数是可选的,但只能是IN类型(IN关键字可以省略)
删除函数:
删除函数语法如下:
DROP FUNCTION 函数名;
重新编译一个存储函数的语法如下:
ALTER PROCEDURE 函数名 COMPILE;
27.触发器
DML触发器的创建
创建DML触发器的语法如下:
CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE|AFTER|INSTEAD OF} 触发事件1 [OR 触发事件2...]ON 表名
[FOR EACH ROW]
WHEN 触发条件
DECLARE
声明部分
BEGIN
主体部分
END;
对于UPDATE事件,还可以用以下形式表示对某些列的修改会引起触发器的动作:
UPDATE OF 列名1,列名2...
删除触发器的语法如下:
DROP TRIGGER 触发器名
通过命令设置触发器的可用状态,使其暂时关闭或重新打开
该命令语法如下:
ALTER TRIGGER 触发器名 {DISABLE|ENABLE}其中,DISABLE表示使触发器失效,ENABLE表示使触发器生效。
28.数据库事件触发器
数据库事件或模式事件触发器的创建语法如下:
CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE|AFTER }
{DDL事件1 [DDL事件2...]| 数据库事件1 [数据库事件2...]}
ON {DATABASE| [模式名.]SCHEMA }
[WHEN (条件)]
DECLARE
声明部分
BEGIN
主体部分
END;
其中:DATABASE表示创建数据库级触发器,数据库级要给出数据库事件;SCHEMA表示创建模式级触发器,模式级要给出模式事件或DDL事件