2022年oracle数据库 .pdf

oracle 数据库的安装首先安装的时候需要设置口令：这里的口令只是针对system、sys等用户登陆时候的口令；口令管理：此时可以针对scott 用户进行设置口令、如果此时不修改scott 的口令 scott的默认口令为 tiger oracle 数据库的安装与卸载（删除之前需要将服务停止）1. 开始菜单中程序oracle-oradb10g_home1下面有个卸载2. 清理注册表：a.删除 HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE目录b.删除 HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services中所有以oracle或 OraWeb 为开头的键。c.HKEY_LOCAL_MACHINE/SYSETM/CurrentControlSet/Services/Eventlog/application 中所有以 oracle开头的键。d.删除 HKEY_CLASSES_ROOT 目录下所有以 Ora、Oracle、Orcl 或 EnumOra为前缀的键e.HKEY_CURRENT_USER/SOFTWARE/Microsoft/windows/CurrentVersion/Explorer/MenuOrder/Start Menu/Programs中所有以 oracle 开头的键。f.删 除HKDY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI中 除Microsoft ODBC for Oracle 注册表键以外的所有含有Oracle 的键到这一步，检查一下环境变量，看oracle配置的环境变量是否存在(path) 完了重启电脑Oracle 数据库开发工具(PL/SQL developer 和微软的 sqldeveloper) Oracle 服务器由 Oracle 数据库和Oracle 实例组成Oracle 实例由系统全局区内存结构和用于管理数据库的后台进程组成Oracle 中用于访问数据库的主要查询工具有SQL*Plus、iSQL*Plus 和PL/SQL 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 49 页 - - - - - - - - - Oracle 企业管理器是用于管理、诊断和调整多个数据库的工具Oracle 中的 SYSTEM 用户和 SYS 用户具有管理权限，而SCOTT 用户只有基本的权限Oracle 服务在Windows 注册表中注册，并由Windows 操作系统管理Oracle 的数据类型：数值类型Number(p,s):数字型 p 为整数位s为小数位Decimal(p,s)：数字型p 为整数位s 为小数位Integer:整数类型Float：浮点型 双精度Real：实数类型精度更高字符数据类型Char：固定长度字符，最大长度为2000bytes varchar2：可变长度的字符串， 最大长度为 4000bytes 可做索引的最大长度为749 nchar：根据字符集而定的固定长度字符串最大长度为 2000bytes nvarchar2：根据字符集而定的可变长度字符串最大长度为 4000bytes Long:超长字符串最大长度 2G(231-1) 日期时间Date 日期(日-月-年)：格式DD_MM_YY(HH_MI_SS) LOB(数据) RAW:固定长度的二进制数据最大长度 2000bytes 可存放多媒体图像声音Long RAW：可变长度的二进制数据最大长度 2G Blob：二进制数据，最大长度4G Clob：字符数据最大长度 4G 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 49 页 - - - - - - - - - Nclob:根据字符集而定的字符数据最大长度 4G Bfile：存放在数据库外的二进制数据，最大长度4G 伪列Rowid:表示行的存储地址，该地址可以唯一标识数据库中的一行，可以使用ROWID 伪列快速的定位表中的一行Rownum:是查询返回的结果集中行的序号，可以使用它来限制查询返回的行数Oracle 的语句-创建同义词create public synonym tb_student for scott.tb_student -创建表空间sys账户可以默认有创建表空间的权限-其余的账户创建表空间需要呗赋予权限create tablespace linge datafile e:/linge_data.dbf size 100M autoextend on -删除表空间drop tablespace linge -创建新用户分配默认空间create user linge identified by linge default tablespace linge create user zhangsan identified by zhangsan - 删除用户drop user zhangsan alter user zhangsan default tablespace linge -权限分配grant connect to linge grant connect to zhangsan -授予 resource角色grant resource to linge grant create session,create table,create view ,unlimited tablespace to linge 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 49 页 - - - - - - - - - 数据库查询Sql 语言按功能划分： DDL、DML 、DCL 和数据库事务处理语言查询关键字：ALTER 、DROP、REVOKE 、AUDIT 、GRANT 、ROLLBACK 、COMMIT 、INSERT、SELECT、COMMENT 、LOCK 、UPDATE 、CREATE、NOAUDIT 、VALIDATE 、DELETE、RENAME -查询 emp表中部门编号为 10 的所有人员信息select * from emp where deptno=10 select * from SCOTT.emp; -查询部门中部门编号和部门名称select deptno,dname from dept; select rownum,ename from emp; -计算列不是表中的列计算出来的select ename,sal,sal*(1+20/100) from emp select ename as a 姓名 ,sal as 薪水 from emp; select ename|job|deptno as 雇员和职务表 from emp select ename | is |job as 雇员和职务表 from emp select 你好 | ename|, 您的薪水是 |sal from emp -order by desc(降序) asc(升序 默认) select ename ,sal from emp order by sal desc select ename,hiredate from emp order by hiredate select ename,hiredate,deptno from emp order by deptno,hiredate -对计算列排序select empno,ename,sal*months_between(sysdate,hiredate) as total from emp order by total -Months_between sys_date -条件查询-注意大小写名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 49 页 - - - - - - - - - -字符串的值对大小写敏感select ename,job,sal from emp where job=SALESMAN select ename,job,sal from emp where job=salesman select * from emp where ename =SMITH select ename,job,sal from emp where sal=3000; select ename ,hiredate from emp where hiredate=1-1 月-82 -数值是整数在查询的条件的时候可以不要单引号select ename,hiredate,deptno from emp where deptno=10 -多条件查询-逻辑与select * from emp where sal=1000 and sal1500 and deptno=10 -逻辑或select * from emp where job=CLERK or job=MANAGER -不等于的三种表达方式select * from emp where not deptno=10 select * from emp where deptno!=10 select * from emp where deptno10 -逻辑与和逻辑或一起使用select * from emp where (deptno=10 or deptno=20) and sal1500; -逻辑与和逻辑或一起使用的时候不要括号的时候select * from emp where deptno=10 or deptno=20 and sal 1200 group by deptno having sum(sal)8500 order by deptno -group by 子句中使用 having 自居时，查询结果中只返回满足having -条件的组在一个where 子句的作用是在对查询结果进行分组前，将不符合where条件-的行去掉-即在分组前过滤数据，条件中不能包含聚合函数，使用where 显示特定的行名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 49 页 - - - - - - - - - -having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚合函数-使用 having条件显示特定的组，也可以使用多个分组标准分组select deptno,job,count(*) from emp group by deptno,job -分析函数-row_number 返回连续的排位，不论值是否相等-rank 具有相等值的行排位相同，序数随后跳跃-dense_rank 具有相等值的行排位相同，序号是连续的-以上三种分析函数用于计算一个行在一组有序行中的排位序号从 1 开始select ename,job,deptno,sal,row_number() over (order by sal desc) as sal_rank from emp; select ename,job,deptno,sal,rank() over(order by sal desc) as sal_rank from emp; select ename,job,deptno,sal,dense_rank()over(order by sal desc) as sal_rank from emp; PL/SQL 是过程语言 (Procedural Language) 与结构化查询语言 (SQL)结合而成的编程语言，是对 SQL 的扩展循环语句dbms_output.put_line( - 从1打印到 10-); DECLAREv_count number(5); BEGINv_count:= 1; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 49 页 - - - - - - - - - LOOPdbms_output.put_line(v_count); v_count:=v_count+ 1; EXITWHEN v_count 10; END LOOP ; dbms_output.put_line( -); dbms_output.put_line( count的值为： |v_count); v_count:= 1; while v_count 100; end loop; dbms_output.put_line(v_sum); end; declarev_count number(5); v_sum number(5):=0; beginv_count:= 1; while v_count= 100 loopv_sum:=v_sum+v_count; v_count:=v_count+ 1; end loop; dbms_output.put_line(v_sum); end; declarev_sum number(5):=0; beginfor i in 1.100 loopv_sum:=v_sum+i; end loop; dbms_output.put_line(v_sum); end; 动态 SQL 静态 SQLSQL 与动态 SQL：Oracle 编译 PL/SQL 程序块分为两个种：其一为前期联编（early binding） ，即 SQL 语句在程序编译期间就已经确定，大多数的编译名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 49 页 - - - - - - - - - 情况属于这种类型；另外一种是后期联编（late binding） ，即 SQL 语句只有在运行阶段才能建立，例如当查询条件为用户输入时，那么Oracle 的 SQL 引擎就无法在编译期对该程序语句进行确定，只能在用户输入一定的查询条件后才能提交给 SQL 引擎进行处理。通常， 静态 SQL 采用前一种编译方式，而动态SQL 采用后一种编译方式。动态 SQL 程序开发理解了动态 SQL 编译的原理，也就掌握了其基本的开发思想。动态SQL 既然是一种” 不确定 ” 的 SQL，那其执行就有其相应的特点。Oracle中提供了 Execute immediate语句来执行动态SQL，语法如下：Excute immediate 动态 SQL 语句 using 绑定参数列表returning into 输出参数列表; 对这一语句作如下说明：1)动态 SQL 是指 DDL 和不确定的 DML （即带参数的 DML ）2)绑定参数列表为输入参数列表，即其类型为in 类型，在运行时刻与动态SQL 语句中的参数（实际上占位符， 可以理解为函数里面的形式参数）进行绑定。3)输出参数列表为动态SQL 语句执行后返回的参数列表。4)由于动态 SQL 是在运行时刻进行确定的， 所以相对于静态而言， 其更多的会损失一些系统性能来换取其灵活性。游标-隐式游标-特点：自动声明、打开、关闭名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 49 页 - - - - - - - - - -属性：%found 整型sql语句没有影响任何行时为true- %rowcount 布尔型sql语句影响的行数- isopen 布尔型游标是否打开，始终为false- %notfound 布尔型declarev_emp emp%rowtype; beginselect * into v_emp from emp where empno=7369; if sql%found thendbms_output.put_line(found); endif; dbms_output.put_line(sql%rowcount); end; -显式游标-显式游标的使用分为4个步骤-1.声明游标-格式：cursor 游标名(参数1 数据类型),(参数2 数据类型) is select 语句-select语句不能使用into子句-2.打开游标open 游标名-3.提取数据-第一种方式：fetch 游标名into 变量名1，变量名.-第二种方式：者fetch 游标名into 记录变量名-游标打开后，有一个指针指向数据区fetch语句返回指针所指的一行数据-要反悔多行需要重复执行，可以使用循环语句来实现。控制循环可以通过游标的属性来判断-两种方式说明：-第一种格式中的变量名是用来从游标接收数据的变量，需要实现定义。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 49 页 - - - - - - - - - -变量的个数和类型应与select语句中的字段变量的个数和类型一致-第二种格式一次将一行数据取到记录变量中，需要使用%rowtype事先定义记录变量-这种形式使用起定义记录变量的方法如下：-变量名：表名|游标名%rowtype;其中的表必须存在游标名也必须定义-4.关闭游标-第一种方式：declarecursor v_cursor is select * from dept; v_deptno number(2); v_dname varchar2(14); v_loc dept.loc%type; beginif v_cursor%isopenthendbms_output.put_line(cursor is open!); elseopen v_cursor; endif; loopfetch v_cursor into v_deptno,v_dname,v_loc; exit when v_cursor%notfound; dbms_output.put_line(v_cursor%rowcount); dbms_output.put_line(v_deptno| -|v_dname| -|v_loc); endloop; close v_cursor; end; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 49 页 - - - - - - - - - declarev_ename varchar2(10); v_job varchar2(10); cursor emp_cursor isselect ename,job from emp where empno=7788; beginopen emp_cursor; fetch emp_cursor into v_ename,v_job; dbms_output.put_line(v_ename| ,|v_job); close emp_cursor; end; declarev_ename varchar2(10); v_job varchar2(10); v_sal number(5); cursor test_cursor is select ename,job,sal from emp ; beginopen test_cursor; loopfetch test_cursor into v_ename,v_job,v_sal; exit when test_cursor% notfound; dbms_output.put_line(v_ename| -|v_job|-|v_sal); endloop; close test_cursor; end; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 49 页 - - - - - - - - - -第二种方式：declarecursor v_cursor is select * from dept; v_class v_cursor% rowtype; beginopen v_cursor; loopfetch v_cursor into v_class; exit when v_cursor%notfound; dbms_output.put_line(v_class.deptno| -|v_class.dname| |v_class.loc); endloop; close v_cursor; end; declarecursor emp_cursor is select ename,job,sal from emp where empno=7788; emp_record emp_cursor% rowtype; beginopen emp_cursor; fetch emp_cursor into emp_record; dbms_output.put_line(emp_record.ename| -|emp_record.job| -|emp_record.sal); close emp_cursor; end; -显示工资最高的前3名雇员的名称和工资declarev_ename varchar2(10); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 49 页 - - - - - - - - - v_sal number(10); cursor emp_cursor is select ename,sal from emp order by sal desc ; beginopen emp_cursor; for i in 1.3 loopfetch emp_cursor into v_ename,v_sal ; dbms_output.put_line(v_ename| -|v_sal); endloop; close emp_cursor; end; -循环游标-循环游标自动打开关闭 不必手动关闭declarecursor v_cursor is select * from dept; v_dept v_cursor%rowtype; beginfor v_dept in v_cursor loopdbms_output.put_line(v_dept.deptno| -|v_dept.dname| -|v_dept.loc); endloop; end; -存储过程-存储过程1 带输入参数-根据用户编号查找用户姓名createor replaceprocedure名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 49 页 - - - - - - - - - find_emp(emp_no number) asv_empname varchar2(20); beginselect ename into v_empname fromemp where empno=emp_no; dbms_output.put_line(雇员的姓名是 |v_empname); exceptionwhen no_data_found thendbms_output.put_line(雇员编号未找到 ); end ; -存储过程的删除drop procedure find_emp -存储过程的调用declarebeginfind_emp(7369); end; declarebeginfind_emp(&deptno); end; -存储过程2 不带输入参数-查询系统时间createor replaceprocedure usp_outtime isbegin名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 49 页 - - - - - - - - - dbms_output.put_line(sysdate ); end; -调用存储过程declarebeginusp_outtime; end; -过程参数的三种模式：- in 用于接受调用程序的值-默认的参数模式-out 用于向调用程序返回值- in out 用于接受调用程序的值，并向调用程序返回更新的值-存储过程3 带输入输出参数createor replaceprocedurefind_emp2(emp_no in number,emp_name out varchar2) asv_empname varchar2(50); beginselect ename into v_empname from emp where empno=emp_no; - dbms_output.put_line(雇员姓名是|v_empname);emp_name:=v_empname; exceptionwhen no_data_found thendbms_output.put_line(雇员编号未找到 ); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 19 页，共 49 页 - - - - - - - - - emp_name:= no; end; drop procedure find_emp2 declarev_empname varchar2(20); beginfind_emp2(&no,v_empname); dbms_output.put_line(查询出来的结果是 :|v_empname); end; -in out 输入输出参数-将两个值进行交换createor replaceprocedureswap(p1 in out number,p2 in out number) isv_temp number; beginv_temp:=p1; p1:=p2; p2:=v_temp; end; drop procedure swap 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 20 页，共 49 页 - - - - - - - - - declarenum1 number :=100; num2 number:=200; beginswap(num1,num2); dbms_output.put_line(num1=|num1); dbms_output.put_line(num2=|num2); end; -将过程的执行权限授予其他用户-格式：grant execute on 存储过程名to 用户名grantexecuteon find_emp to 用户名-函数 特点：可以有返回值-函数只能接收in参数,而不能接受in out 或者out参数-形参不能是PL/SQL类型-函数的返回类型也必须是数据库类型-访问函数的两种方式-使用PL/SQL块-使用SQL语句-createor replacefunction fun_hello returnvarchar2-申明函数返回类型isbeginreturn朋友,您好; end; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 21 页，共 49 页 - - - - - - - - - -调用函数-第一种 ：使用SQL语句select fun_hello from dual -第二种：使用PL/SQL块declareval varchar2(20); beginval:=fun_hello; dbms_output.put_line(val); end; createor replacefunctionf_find_emp(emp_no number) returnvarchar2asv_empname varchar2(50); beginselect ename into v_empname from emp where empno=emp_no; return v_empname; exceptiion when no_data_found thenreturnno; end; drop procedure f_find_emp; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 22 页，共 49 页 - - - - - - - - - declarev_empname varchar2(50); beginv_empname:=f_find_emp(&no); dbms_output.put_line(v_empname); end; select f_find_emp(7369) from dual; 过程和函数的比较过程函数在规格说明中不包含 return子句必须在规格说明中包含 return子句没有任何返回值必须返回单个值可以包含 return语句，但是与函数不同，它不能用于返回值必须包含至少一条 return语句-程序包是对相关过程、函数、变量、游标和异常等对象的封装-组成部分：规范和主体-规范部分：声明程序包中公共对象包括类型、变量、常量、异常、游标规范和子程序规范等-主体部分：声明程序包私有对象和实现在包规范中声明的子程序和游标名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 23 页，共 49 页 - - - - - - - - - createor replacepackage my_pack isv_hello varchar2(20):=hello; procedure find_emp3(emp_no in number,emp_name out varchar2); end my_pack; / createor replacepackagebody my_pack asprocedure find_emp3(emp_no in number,emp_name out varchar2) isv_empname varchar2(50); beginselect ename into v_empname from emp where empno=emp_no; emp_name:=emp_no; exceptionwhen no_data_found thenemp_name:= no; endfind_emp3; end my_pack; -调用包declarev_empname varchar2(50); begin名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 24 页，共 49 页 - - - - - - - - - dbms_output.put_line(my_pack.v_hello); my_pack.find_emp3(7369,v_empname); dbms_output.put_line(v_empname); end; -删除包drop package my_pack -内packages 置程序包-打印三角形declarebegindbms_output.put_line(打印三角形 ); for i in 1.9 loopfor j in 1.i loopdbms_output.put(* ); endloop for_j; dbms_output.put_line( ); endloop for_i; end; -生成随机数declarei_num number; counter number; begincounter:=1; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 25 页，共 49 页 - - - - - - - - - while counterold.sal) declarev_saldiff number; beginv_saldiff:=: new.sal-:old.sal; dbms_output.put_line(diff:|v_saldiff); end; select * from emp where empno=7369update emp set sal=1000 where empno=7369-语句级触发器createor replacetrigger t_dept after insertor updateor deleteon dept 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 27 页，共 49 页 - - - - - - - - - beginif updating thendbms_output.put_line(已更新dept中的数据 ); elsif deleting thendbms_output.put_line(已删除dept中的数据 ); elsif inserting thendbms_output.put_line(已在dept中插入数据 ); endif; end; update dept set dname= linge where deptno=10select * from dept -禁用触发器altertrigger t_dept disable; -启用触发器altertrigger t_dept enable; -删除触发器drop trigger t_dept; -查看触发器的信息select trigger_name from user_triggers where table_name= emp-?select trigger_type,triggering_event,when_clause from user_triggers where trigger_name=t_dept名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 28 页，共 49 页 - - - - - - - - - -自增