Oracle里的一些基本语法Word格式文档下载.docx

1、-sql-sql statement executor 7、分类 procedures：执行一个动作,做为一个pl/sql来执行，可以返回一个值 function：计算一个值,用于嵌入到表达式中，并必须返回一个值 package：把函数和过程逻辑的关联起来 8、pl/sql block structure header: Contains the subprogram name, type, and arguments.Only used for subprograms. Declarative: Contains the local identifiers for the block. Ex

2、ecutable: Contains the SQL statements and PL/SQL control statements. Exception: Performs actions when errors occur. 9、 DCL和DDL都不被pl/sql支持。DML和commit可以被支持. 10、 一个嵌套块成为封闭块里的一个可执行语句，一个块可嵌套在任何允许放置可执行语句的地方，包括执行部分和异常处理部分。11、 PL/SQL表达式不能包含组函数，但一个PL/SQL块里的SQL语句可以。12、 当一个语句中有混合的数据类型时，PL/SQL可以动态转变。如：想把一个NUMBE

3、R值存进一个VARCHAR2的变量里，PL/SQL会动态地把NUMBER值转变为VARCHAR2类型的字符值。13、 PL/SQL表达式可以包含SQL函数。14、 嵌套块里的语句不能包含一个异常段。15、 PL/SQL中的SELECT语句必须使用INTO子句。二、 数据类型：1） Base Scalar Datatypes•Ø VARCHAR2 ：在PL/SQL中可以存储2000，在oracle8中是4000字节 NUMBER （precision, scale）：当声明时。默认值是null DATE:存储日期型 CHAR （maximum_length）: 如没有指

4、定，数据类型为CHAR的列默认长度为1。这个长度的范围是1到2000。 LONG:用于长度不定，最大值为2G的字符型数据。 Row用来存二进制，在PL/SQL中最大长度是32767，在oracle8中是255字节 LONG RAW：可以容纳raw binary data BOOLEAN:只能为true or false or null BINARY_INTEGER PLS_INTEGER 2） Composite Datatypes PL/SQL TABLES:Primary Key：KEY的类型是BINARY_INTEGER VALUE：真正的值 声明：TYPE type_name IS T

5、ABLE OF column_type | variable%TYPE | table.column%TYPE NOT NULL INDEX BY BINARY_INTEGER;identifier type_name;eg1:TYPE t_nametable IS TABLE OF VARCHAR（2） INDEX BY BINARY_INTEGER;引用：t_nametable（1）:=jact eg2:DECLARE TYPE dept_table_type IS TABLE OF dept%ROWTYPE dept_table dept_table_type;dept_table.ro

6、w1（1）:=jact; PL/SQL RECORDS 定义: TYPE type_name IS RECORD （field_declaration, field_declaration）;TYPE record_name IS RECORD（ name varchar（20）, id number（9） ） student record_name student.name student.id 3） 3）Lob: 用来存二进制，最大长度是4GB CLOB:RECIPE BLOB:PHOTO（可以容纳4G的二进制） BFILE:MOVIE把二进制存在一个扩展的文件中。容量是4G NCLOB

7、三、PL/SQL Block Structure DECLARE-Optional -Variables,Constants,cursors,user-defined exceptions BEGIN-Mandatory -SQL statements -PL/SQL control statements EXCEPTION-Optional -Actions to perform when errors occur END:-Mandatory Block type Anonymous:DECLARE BEGIN . EXCEPTION END;Procedure:PROCEDURE nam

8、e IS Function:FUNTION name RETURN datatype IS RETURN value;四、变量 1、变量的赋值：identifier CONSTANT datatype NOT NULL := | DEFAULT expr;2、变量名number（9,2） NOT NULL :=0; /在声明一个PL/SQL参数时，可以使用赋值运算符 ：= 为该参数赋初值。如果没有为参数赋初值，参数会被设置为null。如果参数被定义NOT NULL约束，那么就必须赋初值。3、变量的类型：变量名number（9,2）;变量名 saray%type; /这样value的类型就和sa

9、ray一样，也可以用列名取代saray 变量名parts%rowtype /parts是表名，这是个记录类型的变量。其内部结构和parts一样 是根据表或视图的列来定义各变量 用表名做为前缀 结构和表的结构一样 4、在PL/SQL调用sqlplus的变量，在变量前面加”：” :g_monthly_sal := v_sal / 12;五、控制程序流 1、 loop 程序段 exit when 表达式 end loop;可以为循环设定标签 EG：LOOP v_counter := v_counter+1;EXIT WHEN v_counter10;Inner_loop. EXIT Outer_lo

10、op WHEN total_done = YES;- Leave both loops EXIT WHEN inner_done = - Leave inner loop only END LOOP Inner_loop;END LOOP Outer_loop;2、 while 表达式 loop 3、for I in 1 . 20 loop 4、if not.then elsif .then else end if;5、 message1 message1a:=a+1 注意：不可以同外层跳到里层 不可以从一个IF子句跳到另一个IF子句 不可以从异常处理块跳到当前块 6、 select into

11、 为变量赋值 select name into v_name where 六、游标 游标的本质是SQL语句的一个工作区域，用于处理多行记录集的查询。分类：Implicit cursors：由DML和PL/SQL的SELECT隐式的定义，不可以使用open,fetch和close去控制这个sql 游标。每一次只处理一行。但是可以使用游标的属性 Explicit cursors：由程序员定义, 显式游标用于返回多于一行的查询 以下讨论的都是显式游标 执行的四个步骤：1、声明：定义游标的名字和结构，select 中可以使用order by 2、 打开游标：执行查询同时绑定所有涉及到的变量 执行的内容

12、：为select 分配内存并分析select语句 绑定输入的变量 配置指针在活动集的第一行 如果 查询不返回结果，不会引发PL/SQL的异常，你可以在执行fetch后测试返回的结果 如果游标内的声明包括update。一样会执行行锁定 3、 Fetch：把当前行的值赋给变量，每个fetch都会把游标指针向下移动一行。如果到了最后一行就会自动退出for loop 4、 关闭：释放活动的集，可以再次使用open 游标的几个属性：SQL%ROWCOUNT 受最近执行的SQL语句影响的行的数目。（一个整数值） SQL%FOUND Boolean属性，如果最近的SQL语句影响了一行或多行，其值为 TRUE

13、。SQL%NOTFOUND Boolean属性，如果最近的SQL语句没有影响任何行，其值为 SQL%ISOPEN 总是为FALSE，原因是PL/SQL总是它们结束执行后立即关闭内隐游标。例子 EG1:常规用法 CURSOR c1 IS SELECT empno, ename FROM emp;emp_record c1%ROWTYPE;OPEN c1;. . . FETCH c1 INTO emp_record;EG2:使用for循环实现游标 FOR emp_record IN c1 LOOP - implicit open and implicit fetch occur IF emp_re

14、cord.empno = 7839 THEN END LOOP; - implicit close occurs 游标FOR循环立时不需要FETCH语句的。游标打开，在循环中每次重复提取一行，所有的行都处理后，游标会自动关闭。EG3:不定义游标的方式 FOR emp_record IN （ SELECT empno, ename FROM emp） LOOP EG4:带变量的游标- 你必须指定指定参数的数据类型，但不用指定大小 CURSOR c1 （v_deptno NUMBER, v_job VARCHAR2） IS FROM emp WHERE deptno = v_deptno AND

15、job = v_job;OPEN c1（10, CLERK）;EG5:FOR UPDATE-当加上for update则把整个表或字段锁住了。SELECT . FROM . FOR UPDATE OF column_referenceNOWAIT FOR UPDATE NOWAIT;NOWAIT:返回一个oracle的错误信息如果此行给其他的会话锁住了。EG6：WHERE CURRENT OF 用于在游标中删除和更新当前行 必须使用 FORUPDATE去锁住行 使用WHERE CURRENT OF去指向当前的行 SELECT . UPDATE . WHERE CURRENT OF c1;COM

16、MIT;七、处理异常 Predefined Exception BEGIN SELECT . COMMIT;EXCEPTION WHEN NO_DATA_FOUND THEN statement1;statement2;WHEN TOO_MANY_ROWS THEN WHEN OTHERS THEN statement3;. Non-Predefined Exception e_products_invalid EXCEPTION;PRAGMA EXCEPTION_INIT （ e_products_invalid, -2292）;v_message VARCHAR2（50）;WHEN e_p

17、roducts_invalid THEN g_message := Product code specified is not valid.User-Defined Exception e_amount_remaining EXCEPTION;RAISE e_amount_remaining;WHEN e_amount_remaining THEN There is still an amount in stock.RAISE_APPLICATION_ERROR Invalidpart EXCEPTION;IF SQL%NOTFOUND THEN RAISE invalidpart;END I

18、F;WHEN invalidpart THEN Raise_application_error（-20003,Invalid Part id #| partnum）;Raise_application_error（-20000,errNum|errMsg）;END sup2; 使用EXCEPTION关键字在一个PL/SQL块的声明部分声明用户自定义异常 使用PL/SQL命令RAISE检测用户自定义异常 PL/SQL可使用Raise_application_error过程返回一个用户自定义错误数和消息给调用环境。所有的用户自定义错误消息必须在-20000到-20999之间 PL/SQL程序可以使

19、用WHEN OTHERS 异常处理来处理没有特定处理的所有异常 PL/SQL程序可以使用特殊的SQLCODE和SQLERRM函数返回最新的oracle错误号码和消息 v_error_code NUMBER;v_error_message VARCHAR2（255）;ROLLBACK;v_error_code := SQLCODE ;v_error_message := SQLERRM ;INSERT INTO errors VALUES（v_error_code, v_error_message）;数据库联接 定义：CREATE DATABASE LINK link_name CONNECT

20、TO username IDENTIFIED BY password USING sqlnet_string;使用：表名link_name 替代名 CREATE SYNONYM synonym_name FOR reference;Eg：CREATE SYNONYM backup FOR backupesal 一、关系数据库的一些概念 1、主键的值一般不可以改变 2、外键：指向另一个表或本表的主键或唯一键的字段。外键的值一定要和某一主键相同，或者为空。3、数据库对像：表，视图，序列，索引，同义词，程序（进程，函数，sql和pl/sql数据）4、sql command 类别 data retri

21、eval数据检索: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 命令）都会提交写入日志文件，并在适当地时

22、候写入数据文件。二、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（+）=table

23、2.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 命令用以执行一个已储存的文件,等同于