oracle数据库学习总结

oracle数据库学习 Oracle讲解 Oracle入门 1.1 oracle物理组件 数据文件 用于存储数据库数据的文件，如、索引数据等，一个数据库有一个或多个物理数据文件，但一个数据文件只能与一个数据库关联。 日志文件 日志文件用于记录对数据库进行的修改信息等 控制文件 控制文件是记录数据库物理结构的二进制文件 1.2 oracle逻辑组件 表空间 数据库可以划分为一个或多个逻辑单位，该逻辑单位成为表空间。Oracle使用表空间将相关的逻辑组件组合在一起，每个表空间是由一个或多个数据文件组成的，一个数据文件只能与一个表空间相联系。每一个数据库中都有一个名为SYSTEM的表空间。即系统表空间，可以用于存储数据字典表、系统程序单元、过程、函数、包和触发器等。也可以存储用户表、索引等。 创建表空间代码: CREATE TABLESPACE worktbs DATAFILE ‘D:\ORACLE\ORACLEATDA\APTECH\WORKTBS01.DBF’ SIZE 10M AUTOEXTEND ON; //示例:创建一个临时表空间，注意在创建表空间的时候需要使用SYSTEM用户登录(password:system) CREATE TEMPORARY TABLESPACE TEST1_temp TEMPFILE 'd:\aaa.dbf' SIZE 10M AUTOEXTEND ON NEXT 5M MAXSIZE 100M EXTENT MANAGEMENT LOCAL; / //示例:创建一个表空间 CREATE TABLESPACE TEST1_data LOGGING DATAFILE 'd:\aaa_data.dbf' SIZE 10M AUTOEXTEND ON 1 Oracle讲解 NEXT 5M MAXSIZE 100M EXTENT MANAGEMENT LOCAL; / 表空间的其他操作 删除表空间 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 修改表空间大小 alter database datafile '/path/NADDate05.dbf' resize 100M 移动表至另一表空间 alter table move tablespace room1; 二、建立UNDO表空间(回退表空间) CREATE UNDO TABLESPACE UNDOTBS02 DATAFILE '/oracle/oradata/db/UNDOTBS02.dbf' SIZE 50M #注意:在OPEN状态下某些时刻只能用一个UNDO表空间，如果要用新建的表空间，必须切换到该表空间: ALTER SYSTEM SET undo_tablespace=UNDOTBS02; 三、改变表空间状态 1.使表空间脱机 ALTER TABLESPACE game OFFLINE; 如果是意外删除了数据文件，则必须带有RECOVER选项 ALTER TABLESPACE game OFFLINE FOR RECOVER; 2.使表空间联机 ALTER TABLESPACE game ONLINE; 3.使数据文件脱机 ALTER DATABASE DATAFILE 3 OFFLINE; 4.使数据文件联机 ALTER DATABASE DATAFILE 3 ONLINE; 5.使表空间只读 ALTER TABLESPACE game READ ONLY; 2 Oracle讲解 6.使表空间可读写 ALTER TABLESPACE game READ WRITE; 四、删除表空间 DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES; 五、扩展表空间 首先查看表空间的名字和所属文件 select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; 1.增加数据文件 ALTER TABLESPACE game ADD DATAFILE '/oracle/oradata/db/GAME02.dbf' SIZE 100M; 2.手动增加数据文件尺寸 ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' RESIZE 4000M; 3.设定数据文件自动扩展 ALTER DATABASE DATAFILE '/oracle/oradata/db/GAME.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 10000M; 1.3连接到oracle服务器 监听器、地址及相关信息参数配置在listener.ora文件中，ORACLE_HOME\product\10.1.0\Db_1\NETWORK\ADMIN目录下，ORACLE_HOME是Oracle软件的主目录。(ADDRESS=(PROTOCOL=TCP)(HOST=host_name)(PORT=1521)) 另外还有一个文件tnsnames.ora文件中，保存了服务器主机名或IP地址、监听端口和数据库SID 1.4 oracle用户管理 Sys是Oracle的超级用户，它只能通过SYSOPER或SYSDBA角色登陆。 默认密码:change_on_install orcl SYSTEM是Oracle中默认的系统管理员，只能用默认方式登录 默认密码:manager orcl 以上两个用户操作的都是SYSTEM表空间 3 Oracle讲解 SCOTT是Oracle的一个示范用户。 默认密码:tiger 查看当前登录用户使用:show user 使用SYSTEM用户登录进去管理其他用户 创建新用户并授予权限 新用户都有默认的表空间和临时表空间 CREATE USER user IDENTIFIED BY password [DEFAULT TABLESPACE tablespace] [TEMPPRARY TABLESPACE tablespace] 使用上节创建的临时表空间以及默认表空间创建用户 CREATE USER USER_TEST1 IDENTIFIED BY USER_TEST1 DEFAULT TABLESPACE TEST1_data TEMPORARY TABLESPACE TEST1_temp; / 给刚创建的USER_TEST1用户分配权限 GRANT CREATE SESSION, CREATE ANY TABLE, CREATE ANY VIEW ,CREATE ANY INDEX, CREATE ANY PROCEDURE, ALTER ANY TABLE, ALTER ANY PROCEDURE, DROP ANY TABLE, DROP ANY VIEW, DROP ANY INDEX, DROP ANY PROCEDURE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE TO USER_TEST1; / 或者 GRANT role TO USER_TEST1; 解释:把用户role拥有的资源访问权限给USER_TEST1 //导入导出权限 grant exp_full_database,imp_full_database,dba to USER_TEST1 查看当前用户下的所有用户表: select table_name from user_tables sqlplusw清屏命令clea scre 4 Oracle讲解 -----------------------------------------------查看用户权限--------------------------------------------------------- 查看所有用户 SELECT * FROM DBA_USERS; SELECT * FROM ALL_USERS; SELECT * FROM USER_USERS; 查看用户系统权限 SELECT * FROM DBA_SYS_PRIVS; SELECT * FROM USER_SYS_PRIVS; 查看用户对象或角色权限 SELECT * FROM DBA_TAB_PRIVS; SELECT * FROM ALL_TAB_PRIVS; SELECT * FROM USER_TAB_PRIVS; 查看所有角色 SELECT * FROM DBA_ROLES; 查看用户或角色所拥有的角色 SELECT * FROM DBA_ROLE_PRIVS; SELECT * FROM USER_ROLE_PRIVS; 非PL/SQL的语句在输入”/”后会再次运行 分配权限 GRANT CONNECT TO user 允许用户连接数据库并在数据库中创建表或其他对象 GRANT RESOURCE TO user 允许用户使用数据库中的空间 GRANT CREATE SEQUENCE TO user 允许用户创建序列 GRANT SELECT ON EMP TO user 允许用户查看EMP表中的记录 修改用户口令 ALERT USER user IDENTIFIED BY password 删除用户 DROP USER user CASCADE 删除用户，CASCADE是删除其对应的模式对象 使用system登录 conn system/system@orcl as sysdba; conn system/system 查看所有用户: select * from all_users; 5 Oracle讲解 使用USER_TEST1登录数据库服务器 conn USER_TEST1/USER_TEST1 1.5 ORACLE的服务 OracleHOME_NAMEINSListener 是Oracle的监听程序。在连接Oracle的时候需要启动此服务。 OracleServiceSID 该服务是为名为SID的数据库实例创建的，在连接时需要启动 Sqlplus与sqlplusw是编辑SQL语句、执行sql语句的工具 如果在sqlplusw中不好编辑，可以使用ed 1打开记事本编辑 执行就是@1 可以通过 Set linesize 300 设置行的长度 Set pagesize 20 设置每页显示的记录数 SQL查询和SQL函数 数据定义语言(DDL) CREATE(创建)、ALTER(更改)、DROP(删除)命令 数据操纵语言(DML) INSERT(插入)、SELECT(查询)、DELETE(删除)、UPDATE(更新)命令 事务控制语言 COMMIT(提交)、SAVEPOINT(保存点)和ROLLBACK(回滚)命令 数据控制语言 GRANT(授予)和REVOKE(回收)命令 2.1 Oracle数据类型 CHAR数据类型，固定长度的字符串，可以是1-2000字节 VARCHAR2数据类型，支持可变长度的字符串，1-4000字节 LONG数据类型，此数据类型用来存储可变长度的字符数据，最多存储2GB , 一个表只有一列可以为LONG数据类型 , LONG列不能定义唯一约束或主键约束 , LONG列不能建立索引 , 过程或存储过程不能接受LONG数据类型的参数 NUMBER数据类型可以存储正数、负数、零、定点数和精度为38位的浮点数。该数据类型 的格式为NUMBER(p,s) p为精度，表示总位数，s为范围表示小数点右边的数字位数 NUMBER类型详细介绍，此内容只做了解，比较复杂 6 Oracle讲解 NUMBER[(p [, s])] s不写就为0 范围: 1 <= p <=38, -84 <= s <= 127 保存数据范围:-1.0e-130 <= number value < 1.0e+126 保存在机器内部的范围: 1 ~ 22 bytes 有效位:从左边第一个不为0的数算起的位数。 s要分几种情况来看，如果: s > 0 精确到小数点右边s位，并四舍五入。然后检验有效位是否 <= p。 s < 0 精确到小数点左边s位，并四舍五入。然后检验有效位是否 <= p + |s|。 s = 0 此时NUMBER表示整数。 部分示例: 123.89 NUMBER 123.89 123.89 NUMBER(3) 124 123.89 NUMBER(6,2) 123.89 123.89 NUMBER(4,2) Error (有效位为5, 5 > 4) 123.89 NUMBER(6,-2) 100 12345.12345 NUMBER(6,2) Error (有效位为5+2 > 6) 12345678 NUMBER(5,-2) Error (有效位为8 > 7) 123456789 NUMBER(5,-4) 123460000 0.1 NUMBER(4,5) Error (0.10000, 有效位为5 > 4) 0.01234567 NUMBER(4,5) 0.01235 0.09999 NUMBER(4,5) 0.09999 DATE数据类型(公元前4712年1月1日到公元9999年12月31日) SYSDATE获取当前日期 select sysdate from dual; TIMESTAMP数据类型,SYSTIMESTAMP返回当前日期、时间 RAW数据类型 LONG RAW数据类型 LOB数据类型 , CLOB , BLOB , BFILE 伪列 伪列就像Oracle中的一个表列，但实际上并未存储在表中，它可以从表中查询，但是不能 插入、更新或删除它们的值。 数据库中的每一行都有一个行地址，ROWID伪列返回该行地址，可以通过ROWID来定位 表中的一行。ROWID SELECT ROWID,ENAME from EMP WHERE EMPNO=’7900’; ROWNUM 7 Oracle讲解 对于一个查询返回的每一行，ROWNUM伪列返回一个数值表示行的次序，第1行是1，第2行是2等 SELECT * FROM EMP WHERE ROWNUM<11; 可以使用ROWNUM来进行分页查询 SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM emp) A WHERE ROWNUM <= 10) WHERE RN >= 5; 2.2 建表 CREATE TABLE student( Stucode varchar(5) , StuName varchar(20) ); 表名首字符应该为字母 不能使用Oracle的保留字为表命名 表名的最大长度为30个字符 同一用户模式下的不同表不能具有相同的名称 不能用空格和单引号 GRANT connect,resource to USER_TEST1; 这样才能操作表空间 //通过system用户给USER_TEST1用户授权，这样USER_TEST1用户才能创建约束 使用USER_TEST1用户建表 CREATE TABLE student ( stuId NUMBER(4) CONSTRAINT pk_stu_Id PRIMARY KEY NOT NULL, stuName VARCHAR2(50) NOT NULL, stuEmail VARCHAR2(50) CONSTRAINT ck_stu_Email CHECK(stuEmail LIKE '%@%'), stuAge NUMBER(2) CONSTRAINT ck_stu_Age CHECK (stuAge BETWEEN 16 AND 45), stuAddress VARCHAR2(100) ); / 创建表的同时加约束 单独加默认约束 ALTER TABLE student MODIFY(stuAddress DEFAULT '荆州市青少年宫'); //建立 CREATE TABLE course ( courseId NUMBER(2) CONSTRAINT pk_course_Id PRIMARY KEY NOT NULL, courseName VARCHAR(10) ); / 8 Oracle讲解 //建立成绩表,外键这样添加 CREATE TABLE score ( scoreId NUMBER(2) CONSTRAINT pk_score_Id PRIMARY KEY NOT NULL, stuId NUMBER(4) NOT NULL, courseId NUMBER(2) NOT NULL, score NUMBER(4,1) CONSTRAINT ck_score_score CHECK(score BETWEEN 0 AND 100) NOT NULL, CONSTRAINT fk_student_score FOREIGN KEY(stuId) REFERENCES student(stuId), CONSTRAINT fk_course_score FOREIGN KEY(courseId) REFERENCES course(courseId) ); / TRUNCATE TABLE student 删除student表里的所有数据，不删结构 DESC stduent 显示表结构 DROP TABLE stduent 删除表数据及其结构 其他CRUD操作基本与SQLSERVER一样 GRANT SELECT,UPDATE ON 表名 TO 用户名 授予权限 REVOKE SELECT,UPDATE ON 表名 TO 用户名 撤销权限 2.3运算符 + - * / = != < > <= >= BETWEEN..AND IN LIKE IS NULL NOT IS NOT NULL AND OR NOT UNION返回两个查询中所有不重复的行 UNION ALL返回两个查询中所有行,包含重复的 INTERSECT返回两个查询都有的行 MINUS返回第一个查询选定但是没有被第二个查询选定的行 || 连接字符串用的操作符 9 Oracle讲解 2.4 SQL函数 ADD_MONTHS(d, n)，d表示日期，那表示加多少月 MONTHS_BETWEEN(d1,d2)函数返回两个日期之间的月数,如果d1晚于d2结果为正 LAST_DAY(d)返回指定日期当月的最后一天的日期值 ROUND(d,[fmt])四舍五入到指定模型的日期 NEXT_DAY(SYSDATE,’星期四’)返回从当前日期到下一个星期几的日期 TRUC(SYSDAYE,’YEAR’)如果为2009-11-11则返回2009-1-1日 EXTRACT(YEAR FROM SYSDATE) 如果为2009-11-11则返回2009 字符函数与函数基本与其他语言一致 转换函数 TO_CHAR将日期转换成字符串 SELECT TO_CHAR(SYSDATE,'YYYY-MM-SS HH24:MI:SS') 当前时间 FROM dual; TO_CHAR(SYSDATE,’YYYY”年”fmMM”月”fmDD”日” HH24:MI:SS) 返回:2009年8月11日 15:02:50 fm是格式掩码用来避免空格填充和数字零填充 TO_DATE将字符串转换成日期 SELECT TO_DATE(‘2009-11-12’,’yyyy-mm-dd’) FROM dual; 结果是’12-11月-09’ Dual表是一个特殊的表，只有一行一列，所以在测试函数的时候可以使用它 TO_NUMBER将数字转换成NUMBER数据类型 SELECT SQRT(TO_NUMBER(‘100’)) FROM dual; 返回10 NVL函数将空值替换成指定的值 NVL(expression1,expression2) 如果expression1为NULL,则NVL返回expression2,否则返回expression1 NVL2(expression1, expression2, expression3) 如果expression1为NULL,则返回expression2,否则返回expression3 NULLIF(expr1,expr2) 如果他们相等就返回空值,否则返回expr1,等价于 CASE WHEN expr1=expr2 THEN NULL ELSE expr1 END 分组函数 AVG、MIN、MAX、SUM、COUNT GROUP BY子句 HAVING子句 使用方法与SQLSERVER一样 分析函数 分析函数是对分组或排序号的数据进行分析 ROW_NUMBER 返回一个唯一的排序值从1开始 10 Oracle讲解 RANK 在一组中排位，如一组中有两个数据相等，则会产生跳跃计数 DENSE_RANK 与RANK作用相同，只是不会跳跃计数，但也允许有相同的计数 锁、分区、视图、索引等内容在此省略 2.5 Oracle序列 序列类似于SQLSERVER里的标识列，它是用来生成唯一的、连续的整数的数据库对象，可按升序或降序排列 CREATE SEQUENCE student_seq START WITH 10 --从哪里开始 INCREMENT BY 2 --每次增加多少 MAXVALUE 2000 --最大值是多少 NOCYCLE --达到最大值后不能继续从头开始生成 CACHE 30; --使用CACHE预先生成一系列数据在缓存 访问序列 NEXTVAL访问序列的下一个值 CURRVAL返回序列的当前值 INSERT INTO Student(stuID,stuName) VALUES ('Y2'||student_seq.NEXTVAL,'张三'); SELECT stduent_seq.CURRVAL FROM dual; 更改序列 ALTER SEQUENCE student_seq MAXVALUE 5000 CYCLE; 删除序列 DROP SEQUENCE student_seq; 查看有所有序列 select sequence_name from ALL_SEQUENCES; //创建学生表、课程表，成绩表所使用的序列 CREATE SEQUENCE student_seq START WITH 1 INCREMENT BY 1; CREATE SEQUENCE course_seq START WITH 1 INCREMENT BY 1; CREATE SEQUENCE score_seq START WITH 1 INCREMENT BY 1; / //使用序列往三张表里放入测试数据 INSERT INTO student VALUES(student_seq.NEXTVAL,'杰克逊','JACKSON@163.com',23,'公安'); 11 Oracle讲解 INSERT INTO student VALUES(student_seq.NEXTVAL,'张学友','zhangxueyou@sina.com',25,'香港'); INSERT INTO student VALUES(student_seq.NEXTVAL,'张国荣','zhangguorong@126.com',30,'沙市'); INSERT INTO student VALUES(student_seq.NEXTVAL,'范冰冰 ','fanbingbing@jzUSER_TEST1.com',18,'荆州'); --往课程表里放数据 INSERT INTO course VALUES(course_seq.NEXTVAL,'JAVA'); INSERT INTO course VALUES(course_seq.NEXTVAL,'HTML'); INSERT INTO course VALUES(course_seq.NEXTVAL,'SQL'); INSERT INTO course VALUES(course_seq.NEXTVAL,'C#'); ALTER TABLE student MODIFY(stuName varchar(25)); ALTER TABLE student ADD(stuEmail varchar(20)); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1021,21,80); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1021,22,56); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1021,23,76); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1021,24,35); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1022,21,52); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1022,22,90); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1022,23,98); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1023,21,65); INSERT INTO SCORE VALUES(score_seq.NEXTVAL,1023,24,24); INSERT、UPDATE、DELETE后执行COMMIT;不然数据是临时的 //内连接查询(不是PL/SQL不要再后面加/因为加了会执行两次) SELECT stu.stuName 用户名,sco.score 成绩,sco.courseId 课程编号 from student stu INNER JOIN score sco ON(stu.stuId=sco.stuId); //外连接查询 SELECT stu.stuName 学员姓名,sco.score 成绩,sco.courseId 课程编号 FROM student stu,score sco WHERE stu.stuId=sco.stuId(+); 等价于: SELECT stu.stuName 学员姓名,sco.score 成绩,sco.courseId 课程编号 FROM student stu LEFT OUTER JOIN score sco ON(stu.stuId=sco.stuId); //+跟着哪边就以对方作为基准去找 PL/SQL简介 (Procedural Language/SQL)过程语言/SQL是结合了ORACLE的过程语言和结构化查询语言 12 Oracle讲解 SQL的一种扩展语言。 其一般分为三部分: [DECLARE declarations] --声明部分 BEGIN Executable statements --可执行部分 [Exception exception handlers] --异常处理部分 END; PL/SQL对大小写不敏感，可以包含下列复合符号: := 赋值 || 连接 -- 单行注释 /* */ 多行注释 << >> 标签分隔符 .. 范围 ** 求幂 声明变量必须指定变量的数据类型，可以在声明的时候进行初始化 声明常量时被赋予初始值 DECLARE vcode VARCHAR2(10); vname VARCHAR2(25):=’JACK’; total CONSTANT NUMBER:=100; BEGIN vcode:=’Y21001’; .. END; 其中:=可以使用DEFAULT代替 数据类型: 数据类型 描述 BINARY_INTEGER 带符号整数，有很多子类型 NUMBER 存储整数定点数浮点数，子类型有FLOAT、INTEGER等 PLS_INTEGER 带符号的整数，效率更高 CHAR 存储固定长度的字符串 RAW 存储二进制或字节串,最大2000字节 LONG或LONG RAW 最大2G VARCHAR2 可变长度的字符串，子类型STRING与VARCHAR DATE 日期类型 TIMESTAMP 日期类型是DATE的扩展 BOOLEAN 存储TRUE、FALSE、NULL BFILE 存储二进制对象，BFILE是文件定位器 BLOB 存储二进制对象 CLOB 存储大型字符数据 13 Oracle讲解 NCLOB 存储大型NCHAR数据 %TYPE 引用某个变量或某列的数据类型 %ROWTYPE 表中一行的记录类型，可以表示从游标提取出的整行数据 Oracle的控制结构: 3.1 条件结构: IF 条件 THEN 语句; END IF; IF 条件 THEN 语句1; ELSE 语句2; END IF; IF 条件1 THEN 语句1; ELSEIF 条件2 THEN 语句2; ELSE 语句3; END IF; CASE 表达式 WHEN 表达式1 THEN 语句1; WHEN 表达式2 THEN 语句2; WHEN 表达式3 THEN 语句3; ELSE 语句n; END CASE; SET SERVEROUTPUT ON; DECLARE scode1 NUMBER(2):=7; BEGIN scode1:=&请输入scode的值; CASE scode1 WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('1---添加用户'); WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('2---修改用户'); 14 Oracle讲解 WHEN 3 THEN DBMS_OUTPUT.PUT_LINE('3---删除用户'); ELSE DBMS_OUTPUT.PUT_LINE('请输入1-3之间的数'); END CASE; END; / &grade是要求用户输入grade的值 SET SERVEROUTPUT ON 打开输出 DBMS_OUTPUT.PUT_LINE(‘输出的内容’); 3.2 循环结构: LOOP 循环体语句; END LOOP; SET SERVEROUTPUT ON; DECLARE n NUMBER(2):=0; BEGIN LOOP n:=n+1; DBMS_OUTPUT.PUT_LINE('USER_TEST1'); EXIT WHEN n>10; END LOOP; END; / WHILE 条件 LOOP 语句;@ END LOOP; SET SERVEROUTPUT ON; DECLARE n NUMBER(2):=0; BEGIN WHILE n<10LOOP n:=n+1; DBMS_OUTPUT.PUT_LINE('USER_TEST1'); END LOOP; END; / 15 Oracle讲解 --循环加分，直到都及格，最高分不能超过100 --给JAVA(21)成绩循环加5分，直到都及格为止，分数不能超过100分 SET SERVEROUTPUT ON; DECLARE num NUMBER(5):=1; BEGIN WHILE num>0 LOOP SELECT COUNT(*) INTO num FROM score WHERE courseId=21 AND score<60; UPDATE score SET score=100 WHERE score>=95 AND courseId=21; UPDATE score SET score=score+5 WHERE score<95 AND courseId=21; END LOOP; COMMIT; END; PL/SQL块出 //如果以上现死循环，会导致score表被锁定，需要这样解锁 首先用conn system/system登录系统 使用: select p.spid,c.object_name,a.serial#,b.session_id,b.oracle_username,b.os_user_name from v$process p,v$session a, v$locked_object b,all_objects c where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id; 查看被锁定的对象。 然后通过 alter system kill session ‘session_id,serial#’; 其中session_id,serial#是取下面的那条记录。 解除锁定。 FOR 计数器 IN value1..value2 LOOP 语句; END LOOP; SET SERVEROUTPUT ON; DECLARE n NUMBER(2):=0; BEGIN FOR n IN 1..10 LOOP DBMS_OUTPUT.PUT_LINE('USER_TEST1'); END LOOP; END; / 顺序控制: GOTO语句无条件将控制权转到标签指定的语句，标签是用<< USER_TEST1 >> 表示的 NULL语句什么都不做将控制权转到下一个语句 16 Oracle讲解 3.3 预定义异常 BEGIN 语句; EXCEPTION WHEN 异常名称 THEN 语句; WHEN OTHERS THEN 语句; END; 系统自带的异常在此省略 用户自定义异常 SET SERVEROUTOUT ON; DECLARE myException EXCEPTION; BEGIN IF(条件) Oracle游标 4.1 隐式游标 %FOUND 只有在DML语句影响一行或多行时，%FOUND属性才返回TRUE SET SERVEROUTPUT ON BEGIN UPDATE student SET studentId=2 where studentId=1; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(‘表已更新’); ELSE DBMS_OUTPUT.PUT_LINE(‘编号未找到’); END IF; END; %NOTFOUND 与%FOUND属性的作用相反。 %ROWCOUNT 返回DML语句影响的函数。如果没有影响任何行，则返回0 %ISOPEN 17 Oracle讲解 返回游标是否已经打开，在执行SQL后Oracle自动关闭SQL游标，所以隐式游标的%ISOPEN 属性始终为FALSE。 4.2显示游标 是由用户显示声明的游标。4个步骤来使用显示游标 1:声明游标 2:打开游标 3:从游标中获取记录 4:关闭游标 CURSOR 游标名称 [输入参数] [定义游标提取的行的类型] IS 指定游标使用的查询语句 控制游标可以使用 OPEN 打开游标 OPEN 游标名称[参数] FETCH 从游标中获取行 FETCH 游标名称 INTO 变量 常写在循环里，每次提取记录后就会向下移动一行 CLOSE 关闭游标 在处理完游标的所有行之后，必须关闭 4.3 使用游标查询 18 Oracle讲解 SET SERVEROUTPUT ON DECLARE stuName student.stuName%TYPE; CURSOR stu_cus IS SELECT stuName FROM student WHERE stuId>6; BEGIN OPEN stu_cus; LOOP FETCH stu_cus INTO stuName; EXIT WHEN stu_cus%NOTFOUND; DBMS_OUTPUT.PUT_LINE(stu_cus%ROWCOUNT || ‘,学员姓名:’ || stuName); END LOOP; CLOSE stu_cus; END; / 使用显示游标删除或更新 将成绩小于60分的学员成绩修改成60分,在定义游标的时必须使用SELECT…FOR UPDATE语句，而在执行DELETE和UPDATE时使用WHERE CURRENT OF子句指定游标的当前行。 SET SERVEROUTPUT ON DELCARE new_Score NUMBER; CURSOR cur_stu IS SELECT score FROM student WHERE score<60 FOR UPDATE OF score; BEGIN OPEN cur_stu; LOOP FETCH score INTO new_Score; UPDATE student SET score=60 WHERE CURRENT OF cus_stu; END LOOP; CLOSE cus_stu; END; 带参数的显示游标 DECLARE stuId student.stu_Id%TYPE; stuName student.stu_stuName%TYPE; CURSOR cus_stu (stuAge NUMBER) IS SELECT stu_Id,stu_Name FROM student WHERE stu_Ageemp_No; BEGIN empnum:=&输入编号; OPEN my_emp(empnum); LOOP FETCH my_emp INTO cur_row; EXIT WHEN my_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE('编号'||cur_row.empNo); END LOOP; CLOSE my_emp; END; / 循环游标 从循环游标提取所以行之后自动终止 提取和处理游标中的每一条记录 如果%NOTFOUNT返回TRUE，则终止循环。 SET SERVEROUTPUT ON; DECLARE CURSOR my_emp IS SELECT * FROM EMP; BEGIN FOR emp_rec IN my_emp LOOP DBMS_OUTPUT.PUT_LINE('编号'||emp_rec.empNo); END LOOP; END; / 说明:其中emp_rec是%ROWTYPE类型的变量，自动定义的，不能再FOR外面访问。 20 Oracle讲解 4.4 REF游标 因为隐式游标和显示游标都是静态定义的，就是sql语句是固定的，如果需要动态确定执行查询，就可以使用REF游标和游标变量。 创建游标变量需要声明REF_CURSOR类型和声明REF_CURSOR类型的游标变量。 TYPE ref_cursor_name IS REF CURSOR [RETURN record_type]; 其中RETURN是可选子句，用于指定游标提取结果集的返回类型。.包含RETURN语句表示是强类型的游标，不包含RETURN是弱类型游标 在PL/SQL执行部分打开游标 OPEN cursor_name FOR 动态select语句 [USING bind_argument_list]; 绑定参数列表 动态SQL的用法: SET SERVEROUTPUT ON; DECLARE cur_row emp%ROWTYPE; TYPE c_type IS REF CURSOR; cur c_type; p_salary NUMBER; BEGIN p_salary :=2500; OPEN cur FOR 'SELECT * FROM emp WHERE sal>:1 ORDER BY sal DESC' USING p_salary; DBMS_OUTPUT.PUT_LINE('薪水大于'||p_salary||'的用户有:'); FETCH cur INTO cur_row; WHILE cur%FOUND LOOP FETCH cur INTO cur_row; DBMS_OUTPUT.PUT_LINE('编号:'||cur_row.empno|| ' 姓名'||cur_row.ename); END LOOP; CLOSE cur; END; / /* LOOP FETCH cur INTO cur_row; EXIT WHEN cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE('编号:'||cur_row.empno|| ' 姓名'||cur_row.ename); END LOOP; CLOSE cur; */ 游标变量(如上题的: c_type)的优点: 可用于从不同的结果集中提取记录 21 Oracle讲解 可以作为过程的参数进行传递 可以引用游标的所有属性 可以用于赋值运算 但是游标变量也有一定的限制 FOR UPDATE子句不能与游标变量一起使用。 不允许在程序包中声明游标变量 另外一台服务器的远程子过程不能接受游标变量参数的值 不能将NULL值赋给游标变量 游标变量不能使用比较运算符 数据库的列不能存储游标变量 子程序和程序包 5.1过程: 创建过程的语法: CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_list)] --参数列表 {IS|AS} --类似于DECLARE [local_declarations] --局部声明部分 BEGIN executable_statements --可执行语句 [EXCEPTION exception_handlers] --异常处理程序 END [procedure_name]; --可以用名称结束 例子: CREATE OR REPLACE PROCEDURE findemp ( emp_no NUMBER ) AS empname VARCHAR(20); BEGIN SELECT ename INTO empname FROM emp where empno=emp_no; DBMS_OUTPUT.PUT_LINE('雇员姓名是'||empname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('没有找到指定的雇员'); END; / 以上过程是根据雇员编号为参数查找相应的雇员。其中参数列表不能用;结尾 22 Oracle讲解 过程参数模式 调用程序是通过参数向被调用的过程传递值的，参数传递模式有3种:IN、OUT即输入、 输出、输入输出。 创建带IN模式参数的过程 CREATE OR REPLACE PROCEDURE Itemdesc(item_code IN VARCHAR2) IS v_itemdesc VARCHAR2(5); BEGIN SELECT itemdesc INTO v_itemdesc FROM itemfile WHERE itemcode=item_code; END; 执行带参数的过程: BEGIN EXECUTE itemdesc(‘i201’); END; 创建带OUT参数的过程 CREATE OR REPLACE PROCEDURE TEST1(value1 IN VARCHAR2,value2 OUT NUMBER) IS identity NUMBER; BEGIN SELECT itemRate INTO identity FROM itemFile where itemCode=value1; IF identity<200 THEN value2:=100; ELSE value2:=50; END IF; END; / 调用带OUT参数的过程 DECLARE value2 NUMBER; BEGIN TEST1(‘i201’,value2); DBMS_OUTPUT.PUT_LINE(‘value2的值是:’||TO_CHAR(value2)); END; 创建带IN OUT参数的过程 CREATE OR REPLACE PROCEDURE swap ( p1 IN OUT NUMBER, 23 Oracle讲解 p2 IN OUT NUMBER ) AS temp NUMBER; BEGIN temp:=p1; p1:=p2; p2:=temp; END; / 调用带IN OUT参数的过程 SET SERVEROUTPUT ON; DECLARE p1 NUMBER:=1; p2 NUMBER:=2; BEGIN swap(p1,p2); DBMS_OUTPUT.PUT_LINE(p1||' '||p2); END; / 将过程findemp的权限授予用户MARTIN: GRANT EXECUTE ON findemp TO MARTIN; 删除过程 DROP PROCEDURE findemp; 5.2 函数: CREATE [OR REPLACE] FUNCTION function_name [(parameter1,parameter2…)] RETURN datatype {IS|AS} [location_declarations] BEGIN executable_Statements; [EXCEPTION exception_Handlers;] END; / 函数只能带有IN参数，而不能带有IN OUT或OUT参数。 形式参数必须只能使用数据库类型，不得使用PL/SQL类型。 函数的返回类型也必须是数据库类型。 24 Oracle讲解 创建函数: CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2 IS BEGIN RETURN '朋友，您好'; END; / 与过程不同，函数不能单独执行，只能通过SQL语句或PL/SQL程序块来调用。 SELECT fun_hello FROM dual; 创建带有业务功能的函数 CREATE OR REPLACE FUNCTION my_fun(sal NUMBER) RETURN VARCHAR2 AS sal1 NUMBER; sal2 NUMBER; BEGIN SELECT MAX(sal),MIN(sal) INTO sal1,sal2 FROM emp; IF(sal BETWEEN sal2 AND sal1) THEN RETURN '价格介于最大价格与最小价格之间'; ELSE RETURN '超过范围'; END IF; END; / 调用此函数 SET SERVEROUTPUT ON; DECLARE sal NUMBER:=&输入价格; msg VARCHAR2(50); BEGIN msg:=my_fun(sal); DBMS_OUTPUT.PUT_LINE(msg); END; / 过程 函数 作为PL/SQL语句执行 作为表达式的一部分调用 在规范中不能包含RETURN子句 必须在规范中包含RETURN子句 不返回任何值 必须返回单个值 可包含RETURN但不返回值 必须包含至少一条RETURN 25 Oracle讲解 导出数据/导入数据 6.1 导出数据 导出整个数据库中的对象，需要特定的权限，比如管理员 DOS命令行完成导入导出 Exp system/manager@USER_TEST1 file=FULLDB full=y buffer=32768; 导出SCOTT模式下的所有对象 Exp scott/tiger@USER_TEST1 file=scott_back owner=scott; 以表的形式把数据导出成文件 EXP scott/tiger@USER_TEST1 tables=(emp,dept) file=文件路径 ;*.dmp 6.2 导入数据 IMP USER_TEST1/USER_TEST1@USER_TEST1 file=item_back.dmp ignore=y full=y; 导入文件: imp USER_TEST1/USER_TEST1@orcl tables=(t1,t2,...) file=文件路径 ignore=y ignore=y如果表存在就不用创建，而是直接导入数据到此表 // 将SCOTT用户的表导入MARTIN用户 Imp system/aptech@USER_TEST1 file=scott_back fromuser=scott touser=martin tables=(emp,dept) 在.net中使用ORACLE 首先给项目添加引用 System.Data与System.Data.OracleClient OracleConnection conn = new OracleConnection(); conn.ConnectionString = "user id=USER_TEST1;data source=ORCL;password=USER_TEST1"; conn.Open(); OracleCommand command = conn.CreateCommand(); command.CommandText = "SELECT * FROM student"; OracleDataReader reader = command.ExecuteReader(); while (reader.Read()) { Console.WriteLine("{0}\t{1}\t{2}\t{3}", reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3)); } reader.Close(); conn.Close(); 在JAVA中使用Oralce,需要添加oracle的jar包 Class.forName("oracle.jdbc.driver.OracleDriver"); 26 Oracle讲解 Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@192.168.10.77:1521: orcl","USER_TEST1","USER_TEST1"); System.out.println(conn); CallableStatement stm=conn.prepareCall("{call TEST12(?)}"); stm.registerOutParameter(1,oracle.jdbc.OracleTypes.); CURSORstm.execute(); ResultSet rs=(ResultSet)stm.getObject(1); while(rs.next()){ System.out.println(rs.getInt(1)+" "+rs.getString(2)); } conn.close(); Oracle9i的做法: 学校机房里的ORACLE不能启动监听器解决办法需要先通过 Oracle Net Configuration Assistant删除现有监听器，再添加一个新的监听器， 那么新的监听器服务会自动启动。 另外如果需要用程序连接则还要修改net Manager里的服务名称name下的地址为本机IP地址 Oracle10g的做法: 找到C:\oracle\product\10.1.0\DB_1\ NETWORK\ADMIN\目录下的listener.ora和tnsnames.ora文件里的(HOST=自己计算机的名称),然后重启ORACLE服务 创建学员信息表(student)表 列名 类型 描述 stuId NUMBER(4) 从1001开始，自动增长1，主键 stuName VARCHAR2(20) 非空 stuAge NUMBER(2) 16到45之间的数字 stuEmail VARCHAR2(20) Email地址必须包含@ stuAddress VARCHAR2(50) 默认值为’荆州’ 课程表(course) 列名 类型 描述 courseId NUMBER(2) 从1开始，自动增长1，主键 courseName VARCHAR2(20) 非空 成绩表(score) 列名 类型 描述 scoreId NUMBER(4) 从1开始，自动增长1，主键 stuId NUMBER(4) 外键，引用student表的主键 courseId NUMBER(2) 外键，引用course表的主键 score NUMBER(4,2) 1到100之间 27 Oracle讲解 --查询每个学员的总成绩 ,SUM(score) 总成绩 FROM score GROUP BY stuId ORDER BY 总成绩 SELECT stuId 学号 DESC; --查询JAVA成绩的前三名 SELECT score java成绩 FROM score WHERE courseId=1 AND rownum<=3; --查询地址为空的学员 SELECT * FROM stduent WHERE stuAddress IS NULL; --查询学员的SQL成绩并显示学号、SQL成绩、以及学员姓名 SELECT sco.score SQL成绩,sco.stuId 学号,stu.stuName 姓名 FROM student stu INNER JOIN score sco ON(stu.stuId=sco.stuId) WHERE sco.courseId=2; --查询哪些学生没有参加考试 SELECT sco.score HTML成绩,sco.stuId 学号,sco.courseId 课程编号,stu.stuName 姓名 FROM student stu LEFT JOIN score sco ON(stu.stuId=sco.stuId) WHERE sco.score IS NULL; --等价于 SELECT sco.score HTML成绩,sco.stuId 学号,sco.courseId 课程编号,stu.stuName 姓名 FROM student stu,score sco WHERE stu.stuId=sco.stuId(+) AND sco.score IS NULL; --查询显示学员的JAVA成绩，如果成绩<60分 显示’不及格’ 60<=成绩<80 显示’三等’ 80<=成绩<90 显示’二等’ 90<=成绩 显示’一等’ --编写程序，显示从2到100之间的素数 SET SERVEROUTPUT ON; DECLARE n NUMBER(4):=1; i NUMBER(4):=1; BEGIN LOOP n:=n+1; i:=1; LOOP i:=i+1; EXIT WHEN mod(n,i)=0; END LOOP; IF n=i THEN DBMS_OUTPUT.PUT_LINE(n); END IF; EXIT WHEN n=100; END LOOP; END; / --循环加分，JAVA成绩普遍很低，现都给与加分，但是总分不能超过100分，直到所有人 28 Oracle讲解 都及格，停止加分。 SET SERVEROUTPUT ON; DECLARE num NUMBER:=0; BEGIN SELECT count(*) INTO num FROM score WHERE courseId=1 AND score<60; WHILE num>0 LOOP UPDATE score SET score=100 WHERE courseId=1 AND score>=95; UPDATE score SET score=score+5 WHERE courseId=1 AND score<95; SELECT count(*) INTO num FROM score WHERE courseId=1 AND score<60; END LOOP; COMMIT; END; / --使用游标查询并显示学员信息 SET SERVEROUTPUT ON; DECLARE stuName student.stuName%TYPE; CURSOR stuName_cur IS SELECT stuName FROM student; BEGIN OPEN stuName_cur; LOOP FETCH stuName_cur INTO stuName; DBMS_OUTPUT.PUT_LINE('姓名: '||stuName); EXIT WHEN stuName_cur%NOTFOUND; END LOOP; CLOSE stuName_cur; END; / --使用参数游标传递学员学号，查询与之相关的信息 SET SERVEROUTPUT ON; DECLARE stuRow student%ROWTYPE; stuCode student.stuId%TYPE; CURSOR stuName_cur(stu_Id student.stuId%TYPE) IS SELECT * FROM student WHERE stuId=stu_Id; BEGIN stuCode:=&请输入学号; OPEN stuName_cur(stuCode); LOOP FETCH stuName_cur INTO stuRow; DBMS_OUTPUT.PUT_LINE('姓名: '||stuRow.stuName||' 年龄:'||stuRow.stuAge); EXIT WHEN stuName_cur%NOTFOUND; END LOOP; 29 Oracle讲解 CLOSE stuName_cur; END; / --编写过程，接收学员的学号，求出此学员的三门课的平均成绩打印输出。 SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE findStudent ( stu_Id NUMBER ) AS stu_Name VARCHAR2(20); BEGIN SELECT stuName INTO stu_Name FROM student WHERE stuId=stu_Id; DBMS_OUTPUT.PUT_LINE('学生姓名:'||stu_Name); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('学号不存在'); END; / SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE findStudent ( stu_Id NUMBER ) AS stu_Age NUMBER(2); stu_Name VARCHAR2(20); CURSOR my_CUR IS SELECT stuName,stuAge FROM student WHERE stuId>stu_Id; BEGIN OPEN my_CUR; FETCH my_CUR INTO stu_Name,stu_Age; WHILE my_CUR%FOUND LOOP FETCH my_CUR INTO stu_Name,stu_Age; DBMS_OUTPUT.PUT_LINE(stu_Name||' '||stu_Age); END LOOP; CLOSE my_CUR; END; / --编写过程，带输入参数学号和课程编号，输出参数为这个学生这门课的成绩，如果缺考就为0 SET SERVEROUTPUT ON; CREATE OR REPLACE PROCEDURE findStudent ( stu_Id IN NUMBER, 30 Oracle讲解 course_Id IN NUMBER, scoreout OUT NUMBER ) AS findscore NUMBER(3); --查询出来的成绩 BEGIN SELECT score INTO findscore FROM score WHERE stuId=stu_Id AND courseId=course_Id; IF SQL%NOTFOUND THEN scoreout:=0; ELSE scoreout:=findscore; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN scoreout:=0; WHEN TOO_MANY_ROWS THEN scoreout:=0; END; / 调用 SET SERVEROUTPUT ON; DECLARE stu_id NUMBER(4); course_id NUMBER(2); scoreout NUMBER(3); BEGIN stu_id:=&请输入学号; course_id:=&请输入课程编号; findStudent(stu_id,course_id,scoreout); DBMS_OUTPUT.PUT_LINE('学号为'||stu_id||'的学员成绩是'||scoreout); END; / --编写函数，接收课程的编号，求出这门课的总成绩并返回，调用此函数。 CREATE OR REPLACE FUNCTION findScore(course_id NUMBER) RETURN NUMBER AS totalScore NUMBER(4):=0; BEGIN SELECT sum(score) INTO totalScore FROM score WHERE courseId=course_id; return totalScore; END; / //调用 31 Oracle讲解 SELECT findscore(1) FROM dual; //调用 SET SERVEROUTPUT ON; DECLARE courseId NUMBER(2):=0; totalscore NUMBER(4):=0; BEGIN courseId:=&请输入课程编号; totalscore:=findScore(courseId); DBMS_OUTPUT.PUT_LINE('总成绩是:'||totalscore); END; / 32