Oracle里的一些基本语法.docx

1、Oracle里的一些基本语法Oracle里的一些基本语法 1.连接-内连接select * from dali.test1 a, dali.test2 b where a.a=b.a;-左连接select * from dali.test1 a, dali.test2 b where a.a=b.a(+);-右连接select * from dali.test1 a, dali.test2 b where a.a(+)=b.a;-完全连接select * from dali.test1 a, dali.test2 b where a.a=b.a(+)unionselect * from dal

2、i.test1 a, dali.test2 b where a.a(+)=b.a;-迪卡尔select * from dali.test1, dali.test2; 判断是否为空： 在SQl Server中为ISNULL(field1,0) 在Oracle中为NVL(field1,0) 一、基础与概念 1、PL/SQL不区分大小写，除非是由引号引起来的字符串。 2、 PL/SQL标识符的命名规则 标识符的最大长度是30个字符，包括字母,数字,$,_,# 不可包含保留字 要以字来打头 不能和同一块中的表中的字段名一样 3、命名规范 sql*plus substitution variable p

3、_name variable v_name constant c_name sql*plus global variable g_name exception e_name 4、注销方式 -注释内容 /*注释内容.*/ 5、只要表达式里有NULL，则整个表达式的值为NULL BOOLEAN和NULL 这里要注意的是：NULL AND TRUE =NULL NULL AND FALSE=FALSE NULL OR TRUE=TRUE NULL OR FALSE =NULL 6、PL/SQL Environment PL/SQL Blcok-mon-sql-procedural statement

4、 executor -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

5、 block. Executable: 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可以动态转变

6、。如：想把一个NUMBER值存进一个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 Ø DA

7、TE:存储日期型 Ø CHAR (maximum_length): 如没有指定，数据类型为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) Co

8、mposite Datatypes Ø PL/SQL TABLES: Primary Key：KEY的类型是BINARY_INTEGER VALUE：真正的值 声明：TYPE type_name IS TABLE 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_namet

9、able(1):=jact eg2: DECLARE TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER; dept_table dept_table_type; 引用：dept_table.row1(1):=jact; Ø PL/SQL RECORDS 定义: TYPE type_name IS RECORD (field_declaration, field_declaration); identifier type_name; TYPE record_name IS RECORD( na

10、me 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 三、PL/SQL Block Structure DECLARE-Optional -Variables,Constants,cursors,user-defined exceptions BEGIN-Mandatory -SQL statem

11、ents -PL/SQL control statements EXCEPTION-Optional -Actions to perform when errors occur END:-Mandatory Block type Anonymous:DECLARE BEGIN . EXCEPTION END; Procedure:PROCEDURE name IS DECLARE BEGIN . EXCEPTION END; Function:FUNTION name RETURN datatype IS DECLARE BEGIN . RETURN value; EXCEPTION END;

12、 四、变量 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的类型就和saray一样，也可以用列名取代saray 变量名parts%rowtype /parts是表名，这是个记录类型的

13、变量。其内部结构和parts一样 是根据表或视图的列来定义各变量 用表名做为前缀 结构和表的结构一样 4、在PL/SQL调用sqlplus的变量，在变量前面加”：” :g_monthly_sal := v_sal / 12; 五、控制程序流 1、 loop 程序段 exit when 表达式 end loop; 可以为循环设定标签 EG： BEGIN LOOP v_counter := v_counter+1; EXIT WHEN v_counter10; LOOP . EXIT Outer_loop WHEN total_done = YES; - Leave both loops EXIT

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

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

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

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

18、; emp_record c1%ROWTYPE; BEGIN FOR emp_record IN c1 LOOP - implicit open and implicit fetch occur IF emp_record.empno = 7839 THEN . END LOOP; - implicit close occurs END; 游标FOR循环立时不需要FETCH语句的。游标打开，在循环中每次重复提取一行，所有的行都处理后，游标会自动关闭。 EG3:不定义游标的方式 BEGIN FOR emp_record IN ( SELECT empno, ename FROM emp) LOO

19、P - implicit open and implicit fetch occur IF emp_record.empno = 7839 THEN . END LOOP; - implicit close occurs END; EG4:带变量的游标- 你必须指定指定参数的数据类型，但不用指定大小 DECLARE CURSOR c1 (v_deptno NUMBER, v_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = v_deptno AND job = v_job; BEGIN OPEN c1(10, CLERK)

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

21、c1 IS SELECT . FOR UPDATE NOWAIT; BEGIN . FOR emp_record IN c1 LOOP UPDATE . WHERE CURRENT OF c1; . END LOOP; COMMIT; END; 七、处理异常 Predefined Exception BEGIN SELECT . COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN statement1; statement2; WHEN TOO_MANY_ROWS THEN statement1; WHEN OTHERS THEN statement1; sta

22、tement2; statement3; END; . Non-Predefined Exception DECLARE e_products_invalid EXCEPTION; PRAGMA EXCEPTION_INIT ( e_products_invalid, -2292); v_message VARCHAR2(50); BEGIN . . . EXCEPTION WHEN e_products_invalid THEN :g_message := Product code specified is not valid.; . . . END; User-Defined Except

23、ion DECLARE e_amount_remaining EXCEPTION; . . . BEGIN . . . RAISE e_amount_remaining; . . . EXCEPTION WHEN e_amount_remaining THEN :g_message := There is still an amount in stock.; . . . END; RAISE_APPLICATION_ERROR DECLARE . Invalidpart EXCEPTION; BEGIN . IF SQL%NOTFOUND THEN RAISE invalidpart; END

24、 IF; EXCEPTION WHEN invalidpart THEN Raise_application_error(-20003,Invalid Part id #| partnum); WHEN OTHERS THEN Raise_application_error(-20000,errNum|errMsg); END ² 使用EXCEPTION关键字在一个PL/SQL块的声明部分声明用户自定义异常 ² 使用PL/SQL命令RAISE检测用户自定义异常 ² PL/SQL可使用Raise_application_error过程返回一个用户自定义错误数和消息给

25、调用环境。所有的用户自定义错误消息必须在-20000到-20999之间 ² PL/SQL程序可以使用WHEN OTHERS 异常处理来处理没有特定处理的所有异常 ² PL/SQL程序可以使用特殊的SQLCODE和SQLERRM函数返回最新的oracle错误号码和消息 DECLARE v_error_code NUMBER; v_error_message VARCHAR2(255); BEGIN . EXCEPTION . WHEN OTHERS THEN ROLLBACK; v_error_code := SQLCODE ; v_error_message := SQLE

26、RRM ; INSERT INTO errors VALUES(v_error_code, v_error_message); END; 数据库联接 定义：CREATE DATABASE LINK link_name CONNECT TO username IDENTIFIED BY password USING sqlnet_string; 使用：表名link_name 替代名 CREATE SYNONYM synonym_name FOR reference; Eg：CREATE SYNONYM backup FOR backupesal 一、关系数据库的一些概念 1、主键的值一般不可以改

27、变 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,rol

28、lback,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(

29、升序) 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 命令用以执行一个已储存的文件,等同于