下载文档原格式
PL/SQL最详细教程PL/SQL是ORACLE对标准数据库语言的扩展，ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了，近几年中更多的开发人员和DBA开始使用PL/SQL，本文将讲述PL/SQL基础语法，结构和组件、以及如何设计并执行一个PL/SQL程序。
PL/SQL的优点从版本6开始PL/SQL就被可靠的整合到ORACLE中了，一旦掌握PL/SQL的优点以及其独有的数据管理的便利性，那么你很难想象ORACLE缺了PL/SQL的情形。
PL/SQL 不是一个独立的产品，他是一个整合到ORACLE服务器和ORACLE工具中的技术，可以把PL/SQL看作ORACLE服务器内的一个引擎，sql语句执行者处理单个的sql语句，PL/SQL引擎处理PL/SQL程序块。
当PL/SQL程序块在PL/SQL引擎处理时，ORACLE服务器中的SQL语句执行器处理pl/sql程序块中的SQL语句。
PL/SQL的优点如下：. PL/SQL是一种高性能的基于事务处理的语言，能运行在任何ORACLE环境中，支持所有数据处理命令。
通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。
. PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有ORACLE对象类型. PL/SQL块可以被命名和存储在ORACLE服务器中，同时也能被其他的PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。
. 可以使用ORACLE数据工具管理存储在服务器中的PL/SQL程序的安全性。
可以授权或撤销数据库其他用户访问PL/SQL程序的能力。
. PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何ORACLE能够运行的操作系统都是非常便利的. 对于SQL，ORACLE必须在同一时间处理每一条SQL语句，在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。
ORACLEPLSQL之EXECUTEIMMEDIATE使用EXECUTE IMMEDIATE是Oracle PL/SQL中的一个重要的特性和语句，它允许在运行时执行动态SQL语句。
通过使用EXECUTE IMMEDIATE，可以在PL/SQL代码中构建和执行动态SQL语句，而不需要预先定义和编译查询。
EXECUTEIMMEDIATE语句的基本语法如下：```EXECUTE IMMEDIATE dynamic_query_string [USINGbind_argument_list] [INTO {variable_list ， record}] [RETURNING INTO bind_argument_list];```动态查询字符串dynamic_query_string可以包含任何合法的SQL语句，包括SELECT、INSERT、UPDATE和DELETE语句以及所有其他DDL和DML语句。
它还可以包含变量、绑定参数和PL/SQL代码。
EXECUTEIMMEDIATE语句使用动态查询字符串在运行时生成SQL语句。
由于SQL语句不是在编译时生成的，因此可以根据运行时的需求动态构建查询。
这对于动态表名、动态列名和条件查询等情况非常有用。
下面是一个使用EXECUTEIMMEDIATE的例子，展示了如何动态执行一个SELECT语句并将结果存储到一个游标中：```sqlDECLAREquery_string VARCHAR2(200);result_cursor SYS_REFCURSOR;id NUMBER := 1;BEGINquery_string := 'SELECT * FROM employees WHERE employee_id = :1';EXECUTE IMMEDIATE query_string INTO result_cursor USING id;-- 使用游标result_cursor进行结果处理...END;```在上面的例子中，我们声明了一个动态查询字符串query_string，将SELECT语句赋值给它，并使用EXECUTE IMMEDIATE执行该查询。
Oracle中PLSQL的循环语句在PL/SQL中可以使⽤LOOP语句对数据进⾏循环处理，利⽤该语句可以循环执⾏指定的语句序列。
常⽤的LOOP循环语句包含3种形式：基本的LOOP、WHILE...LOOP和FOR...LOOP。
LOOP语句的基本语法结构如下：[<<label_name>>]LOOPstatement...END LOOP [label_name]【语法说明】<<label_name>>：LOOP结构的标签，是可选项。
LOOP：LOOP循环开始标志。
statement：LOOP语句中循环执⾏的语句序列。
END LOOP：LOOP循环结束标志，可以添加LOOP结构的标签。
1.基本的LOOP语句实例：要求声明变量，每次循环都需要为变量增加数字1，并输出结果。
当变量值⼤于3时，退出循环操作。
a、利⽤EXIT...WHEN结束循环操作。
SQL> set serveroutput on;SQL> --exit whenSQL> declare2 v_rlt number(8):=-3;3 begin4 <<fst_loop>>5 loop6 dbms_output.put_line('v_rlt = '||v_rlt);7 v_rlt:=v_rlt+1;8 exit fst_loop when v_rlt > 3;9 end loop;10 dbms_output.put_line('LOOP循环已经结束！');11 end;12 /v_rlt = -3v_rlt = -2v_rlt = -1v_rlt = 0v_rlt = 1v_rlt = 2v_rlt = 3LOOP循环已经结束！PL/SQL procedure successfully completedb、利⽤IF...EXIT语句结束循环SQL> --if exitSQL> declare2 v_rlt number(8):=-3;3 begin4 <<fst_loop>>5 loop6 dbms_output.put_line('v_rlt = '||v_rlt);7 v_rlt:=v_rlt+1;8 if v_rlt > 3 then9 dbms_output.put_line('变量的值已经⼤于3，当前值为'||v_rlt);10 exit fst_loop;11 end if;12 end loop fst_loop;13 dbms_output.put_line('LOOP循环已经结束！');14 end;15 /v_rlt = -3v_rlt = -2v_rlt = -1v_rlt = 0v_rlt = 1v_rlt = 2v_rlt = 3变量的值已经⼤于3，当前值为4LOOP循环已经结束！PL/SQL procedure successfully completed2、WHILE...LOOP结构WHILE...LOOP结构和基本的LOOP语句不同，它本⾝可以结束LOOP循环。
PLSQL语法Procedural Language和SQL的结合体。
通过增加变量、控制语句，使我们可以写些逻辑更加复杂的数据库操作语句框架组成declare – 可选声明各种变量或游标的地⽅。
begin – 必要开始执⾏语句。
--单⾏注释语句⽤两个连在⼀起的‘-’表⽰。
/*多⾏注释语句，可以换⾏*/exception – 可选出错后的处理。
end; – 必要（请注意end后⾯的分号）简单helloworld程序1declare2 v_temp number(6):=123; /* := 是⼀个赋值符号 */3begin4 dbms_output.put_line('middle');5 dbms_output.put_line(v_temp);6end;注意要想显⽰程序的东西需要设置环境set serveroutput off -->关闭输出（默认关闭）set serveroutput on -->打开输出基本数据类型Number 数字型Int 整数型Pls_integer 整数型，产⽣溢出时出现错误Binary_integer 整数型，表⽰带符号的整数Char 定长字符型，最⼤255个字符Varchar2 变长字符型，最⼤2000个字符Long 变长字符型，最长2GBDate ⽇期型Boolean 布尔型（TRUE、FALSE、NULL三者取⼀）%type 表⽰不需要知道具体的⼀个字段类型，与指定的字段类型⼀致即可。
例如:v_empno emp.empno%type;%rowtype 与表结构完全⼀致 %rowtype 举例:1declare2 v_tt emp%rowtype;3begin4select*into v_tt from emp where empno=7521;5 dbms_output.put_line(v_tt.job);6 dbms_output.put_line(v_tt.sal);7end;89输出:SALESMAN101250varray定义语法TYPE VARRAY NAMEIS VARRAY(SIZE) OF ELEMENT TYPE [NOT NULL];其中，varray name是VARRAY数据类型的名称，size是正整数，表⽰可以容纳的成员的最⼤数量，每个成员的数据类型是element typeo默认时，成员可以取空值，否则需要使⽤NOT NULL加以限制。
前言：使用变量可以保存计算机需要处理的数据，为了给该变量分配适当的内存空间，还需要指定数据类型，有的数据类型还需要指定长度，如字符串。
有些类型可以用于建表(如char，有些则不能(如boolean，rowtype。
同样是字符串，建表时的限制为4000,在脚本中则为3万多。
简单语法：变量名数据类型; 完整语法：变量名 [constant] 变量类型 [not null] [default 值 | :=值] 其中“[ ]”表示可以不写，“|”表示任选其一。
下面给出变量定义及解释，数据类型先用建表时所用的数据类型： v1 char：说明：没有给出长度，所以v1只能保存一个字符。
超过了则会出错：数字或值错误 : 字符串缓冲区太小。
v2 varchar2(10; 说明：v2最多只能保存10个字符。
如果不写长度，会出错：字符串长度限制在范围(1...32767 v3 number; 说明：v3保存的数字范围非常大，几乎可以认为是没有限制的。
v4 number(5; 说明：v4最多能够保存5位整数。
如果有小数，Oracle会自动四舍五入。
如果整数部分超过5位，则会报错：数字或值错误 : 数值精度太高。
v5 number(5,2; 说明：v5最多能够保存3位整数,2位小数。
如果小数位不止2位，则Oracle会自动四舍五入。
整数位超过3位会报错，同上。
v6 date; 说明：可以直接保存sysdate的值；如果是指定日期，则要用to_date来转化。
否则报错：文字与格式字符串不匹配。
定义了变量，变量的默认值为空，此时进行计算，结果一定为NULL。
所以变量必须初始化。
初始化有三种方式： v7 constant number := 100; 说明：定义v7为常量，定义时就必须给定值。
然后在程序中就不能再对v7进行赋值了，否则会报错：表达式 'V7' 不能用作赋值目标。
v8 number default 10; 说明：定义v8时就给定默认值10。
PL/SQL UPDATE语句1. 概述PL/SQL（Procedural Language/Structured Query Language）是一种为Oracle数据库设计的过程化编程语言。
它结合了SQL的数据操作能力和过程化语言的灵活性，可以用于编写存储过程、触发器、函数等数据库对象。
本文将深入探讨PL/SQL中的UPDATE语句的用法和相关技巧。
2. UPDATE语句基本语法UPDATE语句用于修改表中的数据。
其基本语法如下：UPDATE 表名SET 列名1 = 新值1, 列名2 = 新值2, ...[WHERE 条件];其中，表名是要更新数据的表的名称，列名是要更新的字段的名称，新值是要设置的字段的新值。
WHERE子句是可选的，用于指定更新的条件。
如果没有指定WHERE子句，将会更新表中的所有记录。
3. UPDATE语句示例下面通过一个示例来演示UPDATE语句的用法。
假设我们有一个名为employees的表，包含以下字段：employee_id、last_name、first_name和salary。
现在我们要将employee_id为1的员工的薪水增加10%。
UPDATE employeesSET salary = salary * 1.1WHERE employee_id = 1;执行以上UPDATE语句后，employee_id为1的员工的薪水将会增加10%。
4. UPDATE语句高级用法4.1 更新多个列UPDATE语句可以一次更新多个列的值。
只需在SET子句中指定多个列和对应的新值即可。
UPDATE employeesSET last_name = 'Smith', first_name = 'John'WHERE employee_id = 2;以上语句将会将employee_id为2的员工的last_name修改为’Smith’，first_name修改为’John’。
OraclePLSQL：IF语句的基本使⽤原⽂:IF语句的使⽤A.基本的IF条件语句：基本语法:程序代码IF THENEND IF;Example:程序代码SQL> set serveroutput on;SQL> declarex number(3):=9;beginif x<10 thendbms_output.put_line('x is less than10');end if;end;/结果：x is less than10PL/SQL procedure successfully completedB.IF - ELSE 语句基本语法：程序代码IF THENELSEEND IF;Example:程序代码DECLAREx NUMBER(3) := 10;BEGINIF x < 10 THENdbms_output.put_line('X is less than 10');ELSEdbms_output.put_line('X is not less than 10');END IF;END;/结果：X is not less than 10PL/SQL procedure successfully completedC:IF - ELSIF - ELSE 语句基本语法:程序代码IF THENELSIF THENELSIF THENELSEEND IF;Example:程序代码set serveroutput onDECLAREx NUMBER(3) := 47;BEGINIF x < 10 THENdbms_output.put_line('X is less than 10');ELSIF x = 10 THENdbms_output.put_line('X is equal to 10');ELSIF x < 100 THENdbms_output.put_line('X is between 11 and 99'); ELSEdbms_output.put_line('X is greater than 99');END IF;END;/结果：X is between 11 and 99PL/SQL procedure successfully completedD:与NULL值⽐较处理Example:程序代码declarev NUMBER;beginif v = 1 thenDBMS_OUTPUT.put_line('Equal to 1');elsif v!= 1 thenDBMS_OUTPUT.put_line('Not equal to 1'); elsif v = v thenDBMS_OUTPUT.put_line('Equal to itself'); elseDBMS_OUTPUT.put_line('Undefined result'); end if;v:=v+1;DBMS_OUTPUT.put_line('New value: <'||v||'>'); end;/结果:Undefined resultNew value: <>。
plsql使用技巧PL/SQL是Oracle数据库的一种编程语言，可以用于编写存储过程、触发器、函数等程序。
本文将从以下几个方面介绍PL/SQL的使用技巧：一、变量和常量的使用1.1 变量的定义在PL/SQL中，可以使用DECLARE语句来定义变量。
例如：DECLAREv_name VARCHAR2(100);BEGINv_name := 'John';END;1.2 常量的定义在PL/SQL中，可以使用CONSTANT关键字来定义常量。
例如：DECLAREc_pi CONSTANT NUMBER := 3.1415926;BEGINNULL;END;1.3 变量和常量的命名规则在PL/SQL中，变量和常量的命名规则与其他编程语言类似。
变量和常量的名称必须以字母开头，并且只能包含字母、数字和下划线。
二、条件语句的使用2.1 IF语句IF语句用于根据条件执行不同的代码块。
例如：DECLAREv_age NUMBER := 18;BEGINIF v_age >= 18 THENDBMS_OUTPUT.PUT_LINE('You are an adult.');ELSEDBMS_OUTPUT.PUT_LINE('You are a minor.');END IF;END;2.2 CASE语句CASE语句用于根据不同情况执行不同代码块。
例如：DECLAREv_day_of_week NUMBER := 5;BEGINCASE v_day_of_weekWHEN 1 THEN DBMS_OUTPUT.PUT_LINE('Monday');WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('Tuesday');WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('Wednesday'); WHEN 4 THEN DBMS_OUTPUT.PUT_LINE('Thursday'); WHEN 5 THEN DBMS_OUTPUT.PUT_LINE('Friday');ELSE DBMS_OUTPUT.PUT_LINE('Weekend');END CASE;END;三、循环语句的使用3.1 FOR循环FOR循环用于执行一组代码块一定次数。
PL/SQL DECLARE用法PL/SQL是Oracle数据库中的一种编程语言，它结合了SQL语句和过程化编程语言的特性。
DECLARE是PL/SQL中的一个关键字，用于定义变量、游标、异常和子程序等。
本文将详细介绍PL/SQL DECLARE的用法。
1. 声明变量在PL/SQL中，可以使用DECLARE关键字来声明变量。
变量可以是任何有效的数据类型，例如整型、字符型、日期型等。
下面是一个声明整型变量的示例：DECLAREnum INTEGER;BEGIN-- 在这里可以使用num变量进行计算或赋值操作END;在上面的示例中，我们声明了一个名为num的整型变量。
在BEGIN和END之间的代码块内，我们可以对num进行计算或赋值操作。
2. 声明常量除了变量，我们还可以使用DECLARE关键字来声明常量。
常量是指其值在程序执行期间不会发生改变的变量。
下面是一个声明常量的示例：DECLAREPI CONSTANT NUMBER := 3.14159;BEGIN-- 在这里可以使用PI常量进行计算或赋值操作END;在上面的示例中，我们声明了一个名为PI的常数，并将其初始化为3.14159。
在BEGIN和END之间的代码块内，我们可以使用PI进行计算或赋值操作。
3. 声明游标DECLARE关键字还可以用于声明游标。
游标是一种用于处理查询结果集的数据结构。
下面是一个声明游标的示例：DECLARECURSOR emp_cursor IS SELECT * FROM employees;emp_record emp_cursor%ROWTYPE;BEGIN-- 在这里可以使用emp_cursor和emp_record进行查询操作END;在上面的示例中，我们声明了一个名为emp_cursor的游标，并将其初始化为从employees表中选择所有列的结果集。
我们还声明了一个名为emp_record的记录类型变量，该变量与emp_cursor的列具有相同的数据类型。
Ⅰ、介绍：在编写pl/sql程序时候，可以定义变量和常量，在pl/sql中包括有：标量类型（scalar）复合类型（composite）参照类型（reference）lob（large object）；㈠、标量：（scalar）常用的类型：在编写pl/sql的时候，如果要使用变量，需要在定义部分定义变量；pl/sql中定义变量和常量的语法如下：identifier [constant] datatype [ not null ] [:=|default expt]identifier :名称；constant :指定常量，需要指定他的初始值，而且他的值是不能够改变的；datatype：数据类型；not null ；指定变量不能为null；:= 给变量或是常量指定初始值；default：用于指定初始值；expr：指定初始值的pl/sql表达式，可以是文本、其他变量、函数等；①定义一个变长字符串：v_ename varchar2（20）；②定义一个数：v_sal number（6,3）；③定义一个数并给定初始值：v_sal number（5,3）:=5.4④定义一个日期类型的数据：v_hitedate date；⑤定义一个bool变量，不能为null，初始值为false；v_valid boolean not null default false；㈡、标量：使用标量在定义好变量后就可以使用这些边玲了，这里需要说明的是pl/sql块为变量赋值不同于其他编程语言，需要使用:=符号；SQL> --下面以输入员工号，显示员工姓名等信息；SQL> declare2 c_tax_rate number(3,2):=0.03;3 --用户名；4 v_name varchar2(5);5 v_sal number(6,2);6 v_tax_sal number(6,2);7 begin8 --执行910 select ename ,sal into v_name , v_sal from emp where empno=&no;11 --计算所得税‘12 v_tax_sal:=v_sal*c_tax_rate;13 --输出；14 dbms_output.put_line('Name is : ' || v_name || ' sal is '||v_sal||' tax is '||v_tax_sal);15 end;16 /Enter value for no: 7788old 10: select ename ,sal into v_name , v_sal from emp where empno=&no; new 10: select ename ,sal into v_name , v_sal from emp where empno=7788; Name is : SCOTT sal is 3000 tax is 90PL/SQL procedure successfully completed.㈢、标量：使用%type类型；对于上面的pl/sql块有一个问题：如果员工的姓名操过了5个字符的话救护出错，为了降低pl/sql程序的维护工作量，可以使用%type属性定义变量，这样他会按照数据库列来确定你定义的变量类型和长度；标识符名称表明.列名%type；SQL> --下面以输入员工号，显示员工姓名等信息；SQL> declare2 c_tax_rate number(3,2):=0.03;3 --用户名；4 v_name emp.ename%type;5 v_sal emp.sal%type;6 v_tax_sal number(6,2);7 begin8 --执行910 select ename ,sal into v_name , v_sal from emp where empno=&no;11 --计算所得税‘12 v_tax_sal:=v_sal*c_tax_rate;13 --输出；14 dbms_output.put_line('Name is : ' || v_name || ' sal is '||v_sal||'tax is '||v_tax_sal);15 end;16 /Enter value for no: 7788old 10: select ename ,sal into v_name , v_sal from emp where empno=&no; new 10: select ename ,sal into v_name , v_sal from emp where empno=7788; Name is : SCOTT sal is 3000 tax is 90PL/SQL procedure successfully completed.SQL>Ⅱ、复合变量（composite）；㈠、介绍：用于存放多个值的变量。
SQL PL/SQL语法手册目录第一部分 SQL语法部分 ...................................................................................................... 错误!未定义书签。
一、CREATE TABLE语句 .................................................................................. 错误!未定义书签。
二、CREATE SEQUENCE语句 .............................................................................. 错误!未定义书签。
三、CREATE VIEW语句...................................................................................... 错误!未定义书签。
四、INSERT语句:.............................................................................................. 错误!未定义书签。
五、UPDATE语句:.............................................................................................. 错误!未定义书签。
六、DELETE语句:.............................................................................................. 错误!未定义书签。
七、TRUNCATE语句:.......................................................................................... 错误!未定义书签。
八、各类FUNCTIONS:........................................................................................ 错误!未定义书签。
1. 转换函数: .............................................. 错误!未定义书签。
2. 日期函数............................................... 错误!未定义书签。
3. 字符函数............................................... 错误!未定义书签。
4. 数值函数............................................... 错误!未定义书签。
5. 单行函数: .............................................. 错误!未定义书签。
6. 多行函数............................................... 错误!未定义书签。
第二部分 PL/SQL语法部分 ................................................................................................ 错误!未定义书签。
一、PL/SQL语言简介........................................................................................ 错误!未定义书签。
二、变量说明.................................................................................................... 错误!未定义书签。
三、PL/SQL控制程序流.................................................................................... 错误!未定义书签。
四、存储过程.................................................................................................... 错误!未定义书签。
五、存储函数.................................................................................................... 错误!未定义书签。
六、PACKAGE ..................................................................................................... 错误!未定义书签。
七、触发器 ....................................................................................................... 错误!未定义书签。
八、应用实例.................................................................................................... 错误!未定义书签。
第一部分 SQL语法部分一、Create table 语句语句: CREATE TABLE [schema.]table_name( { column datatype [DEFAULT expr] [column_constraint] ...| table_constraint}[, { column datatype [DEFAULT expr] [column_constraint] ...| table_constraint} ]...)[ [PCTFREE integer] [PCTUSED integer][INITRANS integer] [MAXTRANS integer][TABLESPACE tablespace][STORAGE storage_clause][ RECOVERABLE | UNRECOVERABLE ][ PARALLEL ( [ DEGREE { integer | DEFAULT } ][ INSTANCES { integer | DEFAULT } ])| NOPARALLEL ][ CACHE | NOCACHE ]| [CLUSTER cluster (column [, column]...)] ][ ENABLE enable_clause| DISABLE disable_clause ] ...[AS subquery]表是Oracle中最重要的数据库对象,表存储一些相似的数据集合,这些数据描述成若干列或字段.create table 语句的基本形式用来在数据库中创建容纳数据行的表.create table 语句的简单形式接收表名,列名,列数据类型和大小.除了列名和描述外,还可以指定约束条件,存储参数和该表是否是个cluster的一部分.Schema 用来指定所建表的owner,如不指定则为当前登录的用户.Table_name 用来指定所创建的表名,最长为30个字符,但不可以数字开头(可为下划线),但不可同其它对象或Oracle的保留字冲突.Column 用来指定表中的列名,最多254个.Datatype 用来指定列中存储什么类型的数据,并保证只有有效的数据才可以输入.column_constraint 用来指定列约束,如某一列不可为空,则可指定为not null.table_constraint 用来指定表约束,如表的主键,外键等.Pctfree 用来指定表中数据增长而在Oracle块中预留的空间. DEFAULT为10%,也就是说该表的每个块只能使用90%,10%给数据行的增大时使用.Pctused 用来指定一个水平线,当块中使用的空间低于该水平线时才可以向该中加入新数据行.Parallel 用来指定为加速该表的全表扫描可以使用的并行查询进程个数.Cache 用来指定该表为最应该缓存在SGA数据库缓冲池中的候选项.Cluster 用来指定该表所存储的 cluster.Tablespace 用来指定用数据库的那个分区来存储该表的数据.Recoverable|Unrecoverable 用来决定是否把对本表数据所作的变动写入Redo 文件.以恢复对数据的操作.As 当不指定表的各列时,可利用As子句的查询结果来产生数据库结构和数据.例:1) create table mytab1e(mydec decimal,myint inteter)tablespace user_datapctfree 5pctused 30;2) create table mytable2as ( select * from mytable1);二、create sequence语句语句: CREATE SEQUENCE [schema.]sequence_name[INCREMENT BY integer][START WITH integer][MAXVALUE integer | NOMAXVALUE][MINVALUE integer | NOMINVALUE][CYCLE | NOCYCLE][CACHE integer | NOCACHE][ORDER | NOORDER]序列用来为表的主键生成唯一的序列值.Increment by 指定序列值每次增长的值Start with 指定序列的第一个值Maxvalue 指定产生的序列的最大值Minvalue 指定产生的序列的最小值Cycle 指定当序列值逵到最大或最小值时,该序列是否循环.Cache 指定序列生成器一次缓存的值的个数Order 指定序列中的数值是否按访问顺序排序.例:1) create sequence myseqincrement by 4start with 50maxvalue 60minvalue 50cyclecache 3;2)sql> create sequence new_s;sql>insert into new (new_id,last_name,first_name)values(new_s.nextval,’daur’,’permit’);三、create view语句语句: CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema.]view_name [(alias [,alias]...)]AS subquery[WITH CHECK OPTION [CONSTRAINT constraint]]视图实际上是存储在数据库上旳 select语句.每次在sql语句中使用视图时,表示该视图的select语句就用来得到需要的数据.Or replace 创建视图时如果视图已存在,有此选项,新视图会覆盖旧的视图.Force 如有此选项,当视图基于的表不存在或在该模式中没有创建视图的权限时,也可以建立视图.As subquery 产生视图的select查询语句With check option 如果视图是基于单表的且表中所有的非空列都包含在视图中时,该视图可用于insert和update语句中,本选项保证在每次插入或更新数据后,该数据可以在视图中查到例:1)create or place view new_vasselect substr(d.d_last_name,1,3),d.d_lastname,d.d_firstname,b.b_start_date,b.b_locationfrom new1 d,new2 bwhere d.d_lastname=b.b_lastname;四、INSERT语句:1.语法INSERT INTO [schema.]{table | view | subquery }[@dblink][ (column [, column] ...) ]{VALUES (expr [, expr] ...) | subquery}[WHERE condition]2.插入单行使用VALUES关键词为新行的每一列指定一个值.如果不知道某列的值,可以使用NULL关键词将其值设为空值(两个连续的逗号也可以表示空值,也可使用NULL关键词)插入一行时试图为那些NOT NULL的列提供一个NULL值,会返回错误信息.举例:插入一条记录到DEPARTMENT表中INSERT INTO DEPARTMENT(DEPARTMENT_ID,NAME,LOCATION_ID)VALUES (01,’COMPUTER’,167)3.插入多行将SELECT语句检索出来的所有数据行都插入到表中.这条语句通常在从一个表向另一个表快速复制数据行.举例:INSERT INTO ORDER_TEMPSELECT A.ORDER_ID,B.ITEM_ID,,E.FIRST_NAME||'.'||ST_NAME,A.ORDER_DATE,A.SHIP_DATE,D.DESCRIPTION,B.ACTUAL_PRICE,B.QUANTITY,B.TOTALFROM SALES_ORDER A, ITEM B, CUSTOMER C,PRODUCT D, EMPLOYEE EWHERE MONTHS_BETWEEN(TO_DATE(A.ORDER_DATE),TO_DATE('01-7月-91'))>0 AND A.CUSTOMER_ID=C.CUSTOMER_IDAND C.SALESPERSON_ID=E.EMPLOYEE_IDAND A.ORDER_ID=B.ORDER_IDAND B.PRODUCT_ID=D.PRODUCT_ID4.从其它表复制数据:要快速地从一个表向另一个尚不存在的表复制数据,可以使用CREATE TABLE语句定义该表并同时将SELECT语句检索的结果复制到新表中.CREATE TABLE EMPLOYEE_COPYASSELECT *FROM EMPLOYEE1.语法UPDATE [schema.]{table | view | subquery}[@dblink] [alias]SET { (column [, column] ...) = (subquery)| column = { expr | (subquery) } }[, { (column [, column] ...) = (subquery)| column = { expr | (subquery) } } ] ...[WHERE condition]UPDATE语句更新所有满足WHERE子句条件的数据行.同样,该语句可以用SELECT 语句检索得到.但SELECT必须只检索到一行数据值.否则报错.而且每更新一行数据,均要执行一次SELECT语句.举例:UPDATE EMPLOYEE_COPSET SALARY=SALARY-400WHERE TO_NUMBER(TO_CHAR(HIRE_DATE,'YYMMDD'))<850101UPDATE ITEM_COP ASET A.ACTUAL_PRICE=(SELECT B.LIST_PRICEFROM PRICE B,SALES_ORDER CWHERE A.PRODUCT_ID=B.PRODUCT_ID ANDA.ORDER_ID=C.ORDER_ID ANDTO_NUMBER(TO_CHAR(C.ORDER_DATE,'YYYYMMDD')) BETWEEN TO_NUMBER(TO_CHAR(B.START_DATE,'YYYYMMDD')) ANDNVL(TO_NUMBER(TO_CHAR(END_DATE,'YYYYMMDD')),29991231) )1.语法DELETE [FROM] [schema.]{table | view}[@dblink] [alias] [WHERE condition]DELETE语句删除所有满足WHERE子句条件的数据行.举例:DELETE FROM itemWHERE ORDER_ID=510七、TRUNCATE语句:1.语法TRUNCATE [schema.]table八、各类Functions:1.转换函数:1.1. 函數:TO_CHAR语法:TO_CHAR(number[,format])用途:将一个数值转换成与之等价的字符串.如果不指定格式,将转换成最简单的字符串形式.如果为负数就在前面加一个减号.Oracle为数值提供了很多格式,下表列出了部分可接受的格式:语法:TO_CHAR(date[,format])用途:将按format参数指定的格式将日期值转换成相应的字符串形式.同样,Oracle提供许多的格式模型,用户可以用它们的组合来表示最终的输出格式.唯一限制就是最终的掩码不能超过22个字符.下表列出了部分日期格式化元素.Oracle为数值提供了很多格式,下表列出了部分可接受的格式:语法:TO_DATE(string,format)用途:根据给定的格式将一个字符串转换成Oracle的日期值.该函数的主要用途是用来验证输入的日期值.在应用程序中,用户必须验证输入日期是否有效,如月份是否在1~12之间和日期中的天数是否在指定月份的天数内.语法:TO_NUMBER(string[,format])用途:该函数将一个字符串转换成相应的数值.对于简单的字符串转换数值(例如几位数字加上小数点).格式是可选的.2.日期函数2.1. 函數:ADD_MONTHS语法:ADD_MONTHS(date,number)用途:在日期date上加指定的月数,返回一个新日期.如果给定为负数,返回值为日期date之前几个月的日期.number应当是个整数,如果是小数,正数被截为小于该数的最大整数,负数被截为大于该数的最小整数.例如:SELECT TO_CHAR(ADD_MONTHS(sysdate,1),'DD-MON-YYYY') "Next month"FROM dualNext month-----------19-FEB-20002.2. 函數:LAST_DAY语法:LAST_DAY(date)用途:返回日期date所在月份的最后一天的日期.例如:SELECT SYSDATE, LAST_DAY(SYSDATE) "Last",LAST_DAY(SYSDATE) - SYSDATE "Days Left"FROM DUALSYSDATE Last Days Left--------- --------- ----------19-JAN-00 31-JAN-00 122.3. 函數:MONTHS_BETWEEN语法:MONTHS_BETWEEN(date1,date2)用途:返回两个日期之间的月份.如果两个日期月份内的天数相同(或者都是某个月的最后一天),返回值是整数.否则,返回值是小数,每于1/31月来计算月中剩余天数.如果第二个日期比第一个日期还早,则返回值是负数.例如:SELECT MONTHS_BETWEEN(TO_DATE('02-02-1992', 'MM-DD-YYYY'),TO_DATE('01-01-1992', 'MM-DD-YYYY'))"Months"FROM DUALMonths----------1.03225806SELECT MONTHS_BETWEEN(TO_DATE('02-29-1992', 'MM-DD-YYYY'),TO_DATE('01-31-1992', 'MM-DD-YYYY'))"Months"FROM DUALMonths----------12.4. 函數:NEXT_DAY语法:NEXT_DAY(date,day)用途:该函数返回日期date指定若天后的日期.注意:参数day必须为星期,可以星期几的英文完整拼写,或前三个字母缩写,或数字1,2,3,4,5,6,7分别表示星期日到星期六.例如,查询返回本月最后一个星期五的日期.例如:SELECT NEXT_DAY((last_day(sysdate)-7),'FRIDAY')FROM dualNEXT_DAY(---------28-JAN-002.5. 函數:ROUND语法:NEXT_DAY(date[,format])用途:该函数把一个日期四舍五入到最接近格式元素指定的形式.如果省略format,只返回date的日期部分.例如,如果想把时间(24/01/00 14:58:41)四舍五入到最近的小时.下表显示了所有可用格式元素对日期的影响.例如:SELECT to_char(ROUND(sysdate,'HH'),'DD-MON-YY HH24:MI:SS')FROM dualTO_CHAR(ROUND(SYSDATE,'HH'),'DD-MON-YYHH24:MI:SS')-----------------------------------------------------------------24-JAN-00 15:00:002.6. 函數:TRUNC语法:TRUNC(date[,format])用途:TRUNC函数与ROUND很相似,它根据指定的格式掩码元素,只返回输入日期用户所关心的那部分,与ROUND有所不同,它删除更精确的时间部分,而不是将其四舍五入.例如:SELECT TRUNC(sysdate)FROM dualTRUNC(SYS---------24-JAN-00FLOOR函数：求两个日期之间的天数用；select floor(sysdate - to_date('20080805','yyyymmdd')) from dual;3.字符函数3.1. 函數:ASCII语法:ASCII(character)用途:返回指定字符的ASCII码值.如果为字符串时,返回第一个字符的ASCII码值.例如:SELECT ASCII('Z')FROM dualASCII('Z')----------903.2. 函數:CHR语法:CHR(number)用途:该函数执行ASCII函数的反操作,返回其ASCII码值等于数值number的字符.该函数通常用于向字符串中添加不可打印字符.例如:SELECT CHR(65)||'BCDEF'FROM dualCHR(65------ABCDEF3.3. 函數:CONCAT语法:CONCAT(string1,string2)用途:该函数用于连接两个字符串,将string2跟在string1后面返回,它等价于连接操作符(||).例如:SELECT CONCAT(‘This is a’,’ computer’)FROM dualCONCAT('THISISA','------------------This is a computer它也可以写成这样:SELECT ‘This is a’||’ computer’FROM dual'THISISA'||'COMPUT------------------This is a computer这两个语句的结果是完全相同的,但应尽可能地使用||操作符.3.4. 函數:INITCAP语法:INITCAP(string)用途:该函数将字符串string中每个单词的第1个字母变成大写字母,其它字符为小写字母.例如:SELECT INITCAP(first_name||'.'||last_name)FROM employeeWHERE department_id=12INITCAP(FIRST_NAME||'.'||LAST_N-------------------------------Chris.AlbertsMatthew.FisherGrace.RobertsMichael.Douglas3.5. 函數:INSTR语法:INSTR(input_string,search_string[,n[,m]])用途:该函数是从字符串input_string的第n个字符开始查找搜索字符串的第m次出现,如果没有找到搜索的字符串,函数将返回0.如果找到,函数将返回位置.例如:SELECT INSTR('the quick sly fox jumped over thelazy brown dog','the',2,1)FROM dualINSTR('THEQUICKSLYFOXJUMPEDOVERTHELAZYBROWNDOG','THE',2,1)----------------------------------------------------------31语法:INSTRB(input_string,search_string[,n[,m]])用途:该函数类似于INSTR函数,不同之处在于INSTRB函数返回搜索字符串出现的字节数,而不是字符数.在NLS字符集中仅包含单字符时,INSTRB函数和INSTR函数是完全相同的.3.7. 函數:LENGTH语法:LENGTH(string)用途:该函数用于返回输入字符串的字符数.返回的长度并非字段所定义的长度,而只是字段中占满字符的部分.以列实例中,字段first_name定义为varchar2(15).语法:SELECT first_name,LENGTH(first_name)FROM employeeFIRST_NAME LENGTH(FIRST_NAME)--------------- ------------------JOHN 4KEVIN 53.8. 函數:LENGTHB语法:LENGTHB(string)用途:该函数用于返回输入字符串的字节数.对于只包含单字节字符的字符集来说LENGTHB函数和LENGTH函数完全一样.语法:LOWER(string)用途:该函数将字符串string全部转换为小写字母,对于数字和其它非字母字符,不执行任何转换.3.10. 函數:UPPER语法:UPPER(string)用途:该函数将字符串string全部转换为大写字母,对于数字和其它非字母字符,不执行任何转换.3.11. 函數:LPAD语法:LPAD(string,length[,’set’])用途:在字符串string的左边加上一个指定的字符集set,从而使串的长度达到指定的长度length.参数set可以是单个字符,也可以是字符串.如果string的长度小于length时,取string字符串的前length个字符.语法:SELECT first_name,LPAD(first_name,20,' ')FROM employeeFIRST_NAME LPAD(FIRST_NAME,20,'')--------------- -----------------------------------------JOHN JOHNKEVIN KEVIN语法:RPAD(string,length[,’set’])用途:在字符串string的右边加上一个指定的字符集set,从而使串的长度达到指定的长度length.参数set可以是单个字符,也可以是字符串.如果string的长度小于length时,取string字符串的前length个字符.例如:SELECT first_name,rpad(first_name,20,'-')FROM employeeFIRST_NAME RPAD(FIRST_NAME,20,'-')--------------- -----------------------------------------JOHN JOHN----------------KEVIN KEVIN---------------3.13. 函數:LTRIM语法:LTRIM(string[,’set’])用途:该函数从字符串的左边开始,去掉字符串set中的字符,直到看到第一个不在字符串set 中的字符为止.例如:SELECT first_name,ltrim(first_name,'BA')FROM employeeWHERE first_name='BARBARA'FIRST_NAME LTRIM(FIRST_NAM--------------- ---------------BARBARA RBARA语法:RTRIM(string[,’set’])用途:该函数从字符串的右边开始,去掉字符串set中的字符,直到看到第一个不在字符串set 中的字符为止.具有NULL值的字段不能与具有空白字符的字段相比较.这是因为空白字符与NULL字符是完全不同的两种字符.该函数的另外一个用途是当进行字段连接时去掉不需要的字符.3.15. 函數:SUBSTR语法:SUBSTR(string,start[,length])用途:该函数从输入字符串中取出一个子串,从start字符处开始取指定长度的字符串,如果不指定长度,返回从start字符处开始至字符串的末尾.3.16. 函數:REPLACE语法:REPLACE(string,search_set[,replace_set])用途:该函数将字符串中所有出现的search_set都替换成replace_set字符串.可以使用该函将字符串中所有出现的符号都替换成某个有效的名字.如果不指定replace_set,则将从字符串string中删除所有的搜索字符串search_set.例如:SELECT REPLACE('abcdefbdcdabc,dsssdcdrd','abc','ABC')FROM dualREPLACE('ABCDEFBDCDABC,-----------------------ABCdefbdcdABC,dsssdcdrd3.17. 函數:TRANSLATE语法:TRANSLATE(string,search_set,replace_set)用途:该函数用于将所有出现在搜索字符集search_set中的字符转换成替换字符集replace_set中的相应字符.注意:如果字符串string中的某个字符没有出现在搜索字符集中.则它将原封不动地返回.如果替换字符集replace_set比搜索字符集search_set小,那么搜索字符集search_set中后面的字符串将从字符串string中删除.例如:SELECT TRANSLATE('GYK-87M','0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',9999999999xxxxxxxxxxxxxx')FROM dualTRANSL------xx-99x4.1. 函數:ABS语法:ABS(number)用途:该函数返回数值number的绝对值.绝对值就是一个数去掉符号的那部分.4.2. 函數:SQRT语法:SQRT(number)用途:该函数返回数值number的平方根,输入值必须大于等于0,否则返回错误.4.3. 函數:CEIL语法:CEIL(number)用途:该函数返回大于等于输入值的下一个整数.4.4. 函數:FLOOR语法:FLOOR(number)用途:该函数返回小于等于number的最大整数.语法:MOD(n,m)用途:该函数返回n除m的模,结果是n除m的剩余部分.m,n可以是小数,负数.4.6. 函數:POWER语法:POWER(x,y)用途:该函数执行LOG函数的反操作,返回x的y次方.4.7. 函數:ROUND语法:ROUND(number,decimal_digits)用途:该函数将数值number四舍五入到指定的小数位.如果decimal_digits为0,则返回整数.decimal_digits可以为负数.4.8. 函數:TRUNC语法:TRUNC(number[,decimal_pluces])用途:该函数在指定的小数字上把一个数值截掉.如果不指定精度,函数预设精度为0. decimal_pluces可以为负数.语法:SIGN(number)用途:该函数返回number的符号,如果number为正数则返回1,为负数则返回-1,为0则返回0.4.10. 函數:SIN语法:SIN(number)用途:该函数返回弧度number的正弦值.4.11. 函數:SINH语法:SINH(number)用途:该函数返回number的返正弦值.4.12. 函數:COS语法:COS(number)用途:该函数返回弧度number的三角余弦值.要用角度计算余弦,可以将输入值乘以0.01745转换成弧度后再计算.语法:COSH(number)用途:该函数返回输入值的反余弦值.4.14. 函數:TAN语法:TAN(number)用途:该函数返回弧度number的正切值.4.15. 函數:TANH语法:TANH(number)用途:该函数返回数值number的反正切值.4.16. 函數:LN语法:LN(number)用途:该函数返回number自然对数.语法:EXP(number)用途:该函数返回e(2.71828183)的number次方.该函数执行自然对数的反过程.4.18. 函數:LOG语法:LOG(base,number)用途:该函数返回base为底,输入值number的对数.5.单行函数:单行函数中可以对任何数据类型的数据进行操作.5.1. 函數:DUMP语法:DUMP(expression[,format[,start[,length]]])用途:该函数按指定的格式显示输入数据的内部表示.下表列出了有效的格式.例如:SELECT DUMP('FARRELL',16)FROM dualDUMP('FARRELL',16)----------------------------------Typ=96 Len=7: 46,41,52,52,45,4c,4c5.2. 函數:GREATEST语法:GREATEST(list of values)用途:该函数返回列表中项的最大值.对数值或日期来说,返回值是最大值或最晚日期,如果列表中包含字符串,返回值是按字母顺序列表中的最后一项.例如:SELECT GREATEST(123,234,432,112)FROM dualGREATEST(123,234,432,112)-------------------------4325.3. 函數:LEAST语法:LEAST(list of values)用途:该函数返回列表中项的最小值.对数值或日期来说,返回值是最小值或最早日期,如果列表中包含字符串,返回值是按字母顺序列表中的第一项.例如:SELECT LEAST(sysdate,sysdate-10)FROM dualLEAST(SYS---------10-JAN-005.4. 函數:NVL语法:NVL(expression,replacement_value)用途:如果表达式不为空值,函数返回该表达式的值,如果是空值,就返回用来替换的值.例如:SELECT last_name,NVL(TO_CHAR(COMMISSION),'NOT APPLICABLE')FROM employeeWHERE department_id=30LAST_NAME NVL(TO_CHAR(COMMISSION),'NOTAPPLICABLE')--------------- ----------------------------------------ALLEN 300WARD 500MARTIN 1400BLAKE NOT APPLICABLE组函数可以对表达式的所有值操作,也可以只对其中不同值进行操作,组函数的语法如下所示:function[DISTINCT|ALL expression]如果既不指定DISTINCT,也不指定ALL,函数将对查询返回的所有数据行进行操作.不能在同一个SELECT语句的选择列中同时使用组函数和单行函数.6.1. 函數:AVG语法:AVG([DISTINCT|ALL] expression)用途:对查询返回的数据行求平均值.例如:SELECT AVG(sal) "Average"FROM empAverage----------2073.214296.2. 函數:COUNT语法:COUNT([DISTINCT|ALL] expression)用途:计算表达式的个数.要计算EMP表中雇员的个数.例如:SELECT COUNT(deptno)FROM empCOUNT(DEPTNO)-------------14SELECT COUNT(distinct deptno)FROM empCOUNT(DISTINCTDEPTNO)---------------------3语法:MAX([DISTINCT|ALL] expression)用途:对查询返回的行集求最大值.如果有多个最大值,将所有均返回.要检索公司中最高工资的雇员.语法:SELECT ename,salFROM empWHERE sal=(select max(sal)FROM emp)ENAME SAL---------- ---------KING 50006.4. 函數:MIN语法:MIN([DISTINCT|ALL] expression)用途:对查询返回的行集求最小值.如果有多个最小值,将所有均返回.例如:SELECT MIN(last_name)FROM employeeMIN(LAST_NAME)---------------ADAMS语法:SUM([DISTINCT|ALL] expression)用途:计算查询返回的所有非空数值的总和.如果返回的数据都是空值,则该函数也返回空值.例如:SELECT SUM(salary)"Total"FROM employeeWHERE department_id=10Total---------87506.6. 函數:VARIANCE语法:VARIANCE([DISTINCT|ALL] expression)用途:该函数计算返回所有行的统计方差.例如:SELECT VARIANCE(salary)FROM employeeVARIANCE(SALARY)----------------973659.27TABLE: LOCATION 部门地址表-------------------- -------- ----LOCATION_ID NOT NULL NUMBER(3) 地址IDREGIONAL_GROUP VARCHAR2(20) 地址名TABLE: DEPARTMENT 部门名称表-------------------- -------- ----DEPARTMENT_ID NOT NULL NUMBER(2) 部门IDNAME VARCHAR2(14) 部门名称LOCATION_ID NUMBER(3) 地址ID(LOCATION.LOCATION_ID)TABLE: JOB 工种表-------------------- -------- ----JOB_ID NOT NULL NUMBER(3) 工种IDFUNCTION VARCHAR2(30) 工种名称TABLE: EMPLOYEE 雇员信息表-------------------- -------- ----EMPLOYEE_ID NOT NULL NUMBER(4) 雇员IDLAST_NAME VARCHAR2(15)FIRST_NAME VARCHAR2(15)MIDDLE_INITIAL VARCHAR2(1)JOB_ID NUMBER(3) 工种ID(JOB.JOB_ID)MANAGER_ID NUMBER(4) 领导ID(EMPLOYEE.EMPLOYEE_ID)HIRE_DATE DATE 雇佣日期SALARY NUMBER(7,2) 薪水COMMISSION NUMBER(7,2) 佣金DEPARTMENT_ID NUMBER(2) 部门ID(DEPARTMENT.DEPARTMENT_ID)TABLE: SALARY_GRADE 薪资等级表-------------------- -------- ----GRADE_ID NOT NULL NUMBER(3) 等级IDLOWER_BOUND NUMBER(7,2) 最低工资UPPER_BOUND NUMBER(7,2) 最高工资TABLE: PRODUCT 产品信息表-------------------- -------- ----PRODUCT_ID NOT NULL NUMBER(6) 品号DESCRIPTION VARCHAR2(30) 品名TABLE: PRICE 产品价格表-------------------- -------- ----PRODUCT_ID NOT NULL NUMBER(6) 品号(PRODUCT.PRODUCT_ID)LIST_PRICE NUMBER(8,2) 价格MIN_PRICE NUMBER(8,2) 最低价格START_DATE NOT NULL DATE 生效日期END_DATE DATE 失效日期TABLE: CUSTOMER 客户信息表-------------------- -------- ----CUSTOMER_ID NOT NULL NUMBER(6) 客户ID(CUSTOMER.CUSTOMER_ID)NAME VARCHAR2(45) 客户名ADDRESS VARCHAR2(40) 客户地址CITY VARCHAR2(30) 城市STATE VARCHAR2(2) 州ZIP_CODE VARCHAR2(9) 邮编AREA_CODE NUMBER(3) 区号PHONE_NUMBER NUMBER(7) 电话号码SALESPERSON_ID NUMBER(4) 销售员ID(EMPLOYEE.EMPLOYEE_ID) CREDIT_LIMIT NUMBER(9,2) 信用限制COMMENTS LONG 备注TABLE: SALES_ORDER 订单单头表-------------------- -------- ----ORDER_ID NOT NULL NUMBER(4) 订单IDORDER_DATE DATE 订单日期CUSTOMER_ID NUMBER(6) 客户ID(CUSTOMER.CUSTOMER_ID)SHIP_DATE DATE 出货日期TOTAL NUMBER(8,2) 总金额TABLE: ITEM 订单单身表-------------------- -------- ----ORDER_ID NOT NULL NUMBER(4) 订单ID(SALES_ORDER.ORDER_ID) ITEM_ID NOT NULL NUMBER(4) 订单行号PRODUCT_ID NUMBER(6) 品号(PRODUCT.PRODUCT_ID)ACTUAL_PRICE NUMBER(8,2) 实际价格QUANTITY NUMBER(8) 订单数量TOTAL NUMBER(8,2) 总金额第二部分 PL/SQL语法部分一、PL/SQL语言简介(本讲义之所有程序均调式通过)首先我们看一个简单之例子,下面这个例子是统计从1至100的总和.declarei number:=0; /*声明变量井给初值*/t number:=1;error_message exception; /*声明一个出错处理*/beginfor t in 1..100 loopi:=i+t;end loop;if i>=5050 thenraise error_message; /*引发错误处理*/elseinsert into c_nt(c_t) values(i);end if;exceptionwhen error_message theninsert into c_nt(c_t) values(0);end;✧从上例中可以看出PL/SQL语法的一般规则.✷PL/SQL中语句以分号(;)结尾.✷开始程序块的PL/SQL语句(如IF…或BEGIN语句)没有分句.✷文本值括在单引号(‘‘)内,而不是(““).✷过程只允许最后有一个出口..✧PL/SQL程序可以分为三个部分✷DECLARE部分用于变量、常量、函数、过程、Cursor.✷BEGIN部分包含PL/SQL块中要执行的代码用于程序处理,其中可以调用函数、过程.Exception 部分用于出错处理.下面我们再看一个例子:declarei number :=1;t number :=1;p number :=1;/*create table c_ny(c_t number,cou_t number);*/function aa(xx number)return number is /* define function*/ tt number;ct number:=1;j number:=1;beginwhile j<=xx loopct:=ct+j;j:=j+1;end loop;return ct;end aa;begin/*create table c_nt(c_t number,cou_t number);*/commit;while i<=200 loopt:=t+i;i:=i+1;p:=aa(i); /* calling function*/insert into c_nt values(t,p);commit;end loop;end;/说明:1.在定义变量可以赋初值,赋初值有两种方法,一为上程序所示,另一种为如下所示:DeclareI number default 92;T number default 0;2.定义常量DeclareI constant number:=1;T constant number:=9;3.定义函数function function_name(parameter type)return type is…declare variantbegin……end function_name;在上面的例子中我们定义了一个函数aa,在begin模块部分引用了此函数aa().4.定义过程procedure procedure_name(parameter IN type) is…declare variantbegin……exception……end procedure_name;见下例:declare/*t_emp c_nt%rowtype;*/i number:=1;t number:=1;procedure te_t(t_t number) is /*定义一个函数*/ begininsert into c_nt1(t_1) values(t_t);end te_t;beginfor i in 1..100 loopte_t(i);調用函數end loop;end;/5.定义Cursordeclare/*t_emp c_nt%rowtype;*/t_emp1 number;t_emp2 number;cursor tes_t1is select * from c_nt;beginopen tes_t1;delete from c_nt1;commit;loopfetch tes_t1 into t_emp1,t_emp2;exit when tes_t1%notfound;insert into c_nt1 values(t_emp1,t_emp2);aend loop;close tes_t1;commit;end;/我们在open 一个cursor 时,可能会存在一种情况,即我们不需要cursor 中所有之记录,我们该如何处理:1.在定义一个cursor 时,可以附带参数如下所示declaecursor c1(p_emp_id) isselect emp_no,emp_name from dept_no where emp_id = p_emp_id;demp_pt c1%rowtype;……beginopen c1(123);loopfetch c1(123) into demp_pt…2.在将cursor 中之记录项转到变量中时进行控制,如下所示:declarecursor isselect empt_no,empt_name from dept_no;p_no number;p_name number;beginloopfetch c1 into (p_no,p_name); if condition1 then……end ifend;注意:✧ 因为PL/SQL 不支持I/O,所以程序所有结果都是放在数据档中.✧ Delete from accts where status=’bad debt ’If sql%rowcount>10 thenRaise out_of_bounds;用於到cursor 中變量進行控制End if;另:在声明一个变量时,PL/SQL提供两种变量类型:%TYPE,%ROWTYPE.1.%TYPE使用%TYPE时,可以有种用法:✧一用法见下例:declareBalance number(7,2);Minimum_balance balance%type:=10.00;在上例中,minimum_balance数据类型为number(7,2)具默认值为10.00.✧二用法见下例(将数据类型与table中一column datatype相对应起来,如果table中column datatype变更,则在运行时,上数据类型会自动的变换上):declaremy_dname empc.empto%type;2.%rowtype使用%rowtype数据类型用于将table和cursor中一数据行相对起来.见下例:DeclareCursor my_cursor is select sal+nvl(comm,0) salcomm,wages,enamefrom emp; My_rec my_cursor%rowtype;BeginOpen my_cursor;loop Fetch my_cursor into my_rec ;Exit when my_cursor%notfound;If my_rec.wages>200 thenInsert into temp values (null,my_rec.wages,my_rec.ename);End if;End loop;Close my_cursor;End;定義一個%rowtype 類型,與my_cursor 中記錄行對應.將my_cursor 中之記錄寫到my_rec 變量中去.二、 变量说明在PL/SQL 中包括以下几种常见的变量类型:CHAR -存储定长的ASCII 字符串,允许存储数字,文本文符等,最长可255个字符. VARCHAR2-存储变长的字符串,尽管伋按符串的最大长度来定义,但VARCHAR2和CHAR 的区别在于如果达不到定义的长度,下的空间不会自动的填写为空格,VARCHAR2最大可以放入2000个字符.DATE-实际上是存储时间信息的日期/时间戳,在使用日期时,应考虑怎样使用日期函数.有关日期函数的格式见函数说明部分.NUMBER-存储数值数据,包括整数和浮点数、数据范围可以从1 10 到38 10 ,而且,你有很大的数据空间.BOOLENA -存储布尔值.它表示是/否,真/假,1/0之类的东西.LONG-这是一种文本字符串,其长度大于VARCHAR2字段的2000个字符.该类最多可储存2 GB 个字符,与原始二进制数据相比,它只能存储字符信息.RAW-用来存储操作系统使用的原始二进制数据,可用于存储像图像或声音记录这样的信息,但这种数据长度最长度只有255字节.LONG RAW-与LONG 类型等价,但存储二进制数据,最长可达2GB 个字节.BINARY_INTEGER -这个字段按计算器使用的二进制格式存储信息,从-2到2 -1.127 31 31另:PL/SQL提位两种复合类型:TABLE和RECORD1.TABLE✧要定义一个数组,你使用表类型定义语句,例如要定义Last_name数组,可以使用下述语句:type last_name_list is table of varchar2(22)index by binary_integer;last_name last_name_list;✧当定义一个长类型时,就涉及到一个删除表的问题,PL/SQL表不能用Delete语句来删除,但可以将每一行空值如下所示:sal_tab(3):=null;另一种法是定义两个相同类型的表类型,如果要将另一表清空,只需将空表给要清空的表即可.如下所示.declaretype numtabtype is table of numberindex by binary_integer;sal_tab numbertype;empty_tab numbertype;beginfor I in 1..100 loopsal_tab(I):=I;end loop;….End;2.RECORDDeclareType deptrectype is record(deptnonumber(2), dnamechar(14), locchar(14),); dept_recdeptrectype; beginselect deptno,dname,loc into dept_rec from dept where deptno=30;……end;与所有的编程语言一样,定义一个变量时,同样存在变量作用范围问题:如下所示:declare x real;function function_name(variant type)return type is declarex char;begin………beginx:=expression1……end;變量x 為 real 類型. 在此範圍之內變量x 為char 變量x 為real 類型。
