Oracle知识点简介 (1)

SQL*PLUS常用命令：　　conn system/rr:登陆数据库　　disc:断开连接
　　SQL*PLUS常用命令：
　　conn system/rr:登陆数据库
　　disc:断开连接
　　show user：显示当前用户
　　exit：退出系统
　　clear：清屏
　　start d:\aa.sql [arg1 arg2 arg3]：运行一个脚本，指定参数，脚本中的参数用&1 &2 $3来代替，参数依次代替。
　　select &a from scott.emp where deptno=20 and job='&b';$a个&b为交互式参数，接下来会输入a和b的值
　　SQL> define b=clerk;/*事先定义好交互变量的值*/
　　SQL> define a=ename;
　　SQL> select &a from scott.emp where deptno=20 and job='&b';
　　SQL> UNDEFINE a;;/* 清除交互变量的值*/
　　绑定变量的使用：
　　SQL> variable num number;/*定义一个绑定变量*/
　　SQL> execute :num=7788;/*设定绑定变量的值*/
　　SQL> select * from scott.emp where empno=:num;/*使用绑定变量*/
　　SQL> print num;/*输出绑定变量的值*/
　　SQL> alter user hr identified by hrpassword account unlock;;/*解锁hr用户*/
　　SQL>SAVE E:\orasql\kc.sql注意：如果文件已存在，原有文件将被替换。
　　SQL>GET e:\orasql\kc.sql将保存在磁盘上的文件kc.sql调入缓冲区。
　　SQL>START e:\ orasql\kc.sql     运行磁盘上的命令文件。
　　Oracle中的数据类型
　　1.Nvarchar2（n）:n最大为4000,n代表能存储的字符串数，如nvarchar2（50）类型的字段能存放50个字符，不足50个的后边没有空格补齐。
　　2.Char（n）:n最大为2000,n代表能存储的字符串数，如char（50）类型的字段能存放50个字符，不足50个的后边有空格补齐。
　　3.Number（p,s）数字类型，p为数字的总位数，s为小数点后的位数。
　　4.BOOLEAN：布尔类型，取值为TRUE,FALSE,NULL，该类型只能应用到PL/SQL中，不能应用到列类型中。
　　5.DATE：日期类型，ORACLE中默认的日期格式是'07-10月-82'，我们要想修改默认的日期格式000（基于本次回话）为’YYYY-MM-DD’，则可以用命令：alter session set nls_date_format='yyyy-mm-dd';
　　6.LOB数据类型（存储最大为4G的无结构大文件）：
　　1）BLOB：一般为图片，图像
　　2）CLOB：只有文本的大文件
　　3）BFILE:存储声音视频等文件
　　7.%Type:使一个变量的类型参照其他类型，一般为列的类型。
　　如：
　　DECLARE
　　V_ename emp.ename%type;--参照emp表的ename类型。
　　如：
　　declare
　　v_ename emp.ename%type;--参照emp表中的ename该列的类型。
　　v_sal emp.sal%type;
　　v_tax_rate constant number（3,2） :=0.03;
　　v_sal_tax v_sal%type;--参照v_sal的类型。
　　begin
　　select ename,sal into v_ename,v_sal from emp where empno=&eno;--交互式变量eno
　　v_sal_tax:=v_sal*v_tax_rate;
　　DBMS_OUTPUT.PUT_LINE（'雇员名'||v_ename）;
　　DBMS_OUTPUT.PUT_LINE（'工资'||v_sal）;
　　DBMS_OUTPUT.PUT_LINE（'所得税'||v_sal_tax）;
　　end;
　　8.rowtype类型，用于参照一个表的记录类型，该类型的变量能存储一条记录。
　　如：
　　declare
　　v_emp emp%ROWTYPE;
　　begin
　　select * into v_emp from emp where empno=&eno;
　　DBMS_OUTPUT.PUT_LINE（'雇员名'||v_emp.ename）;
　　DBMS_OUTPUT.PUT_LINE（'工资'||v_emp.sal）;
　　DBMS_OUTPUT.PUT_LINE（'所得税'||v_emp.job）;
　　end;
　　9.表类型（即相当于数组）：
　　DECLARE
　　type emp_table_type is table of emp.ename%type index by BINARY_INTEGER;--声明一个数组类型，参照emp.ename
　　emptable emp_table_type;--声明一个数组
　　BEGIN
　　select ename into emptable（-1） from emp where empno=&eno;
　　emptable（0）:='jack';
　　dbms_output.put_line（'雇员名1'||emptable（-1））;
　　dbms_output.put_line（'雇员名1'||emptable（0））;
　　end;
　　ORACLE单行函数：
　　字符函数，接受字符参数，输出字符或者数字
　　Lower（）：转换小写
　　Upper（）:转换大写
　　InitCap（）:首字母变大写函数
　　Contac（‘a’，’b’）:连接字符串，ab
　　Length（‘abc’）:获取字符串的长度
　　instr（‘ename’，'a'）获取ename中a的位置，返回3
　　substr（‘job’，1,2）:截取字符串：jo
　　TRIM（'S' FROM 'SSMITH'）:MITH（从SSMITH中除去S）
　　数字函数
　　ROUND（45.926, 2）        45.93 截取四舍五入
　　TRUNC（45.926, 2）        45.92截取不四舍五入
　　MOD（1600, 300）          100：取余
　　日期函数
　　select ename,（ MONTHS_BETWEEN （'01-9月-95','01-2月-95'）） m from emp;m为7
　　ADD_MONTHS （'11-1月-94',6）：1994/07/11
　　LAST_DAY（'01-9月-95'）：1995/09/30
　　日期转换：
　　select to_char（hiredate,'YYYY-MM-DD'） a from emp;将日期转换为指定的格式（2011-07-03）
　　PL/SQL基础
　　PL/SQL是ORACLE对标准数据库语言的扩展，ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了，近几年中更多的开发人员和DBA开始使用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服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤。
　　PL/SQL块结构
　　PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL 程序包含了一个或多个逻辑块，每个块都可以划分为三个部分（生命部分，执行部分，异常处理部分）。与其他语言相同，变量在使用之前必须声明，PL/SQL提供了独立的专门用于处理异常的部分，下面描述了PL/SQL块的不同部分：
　　声明部分（Declaration section） 声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分；需要说明的是游标的声明也在这一部分。
　　执行部分（Executable section）
　　执行部分是PL/SQL块中的指令部分，由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。
　　异常处理部分（Exception section） 这一部分是可选的，在这一部分中处理异常或错误，对异常处理的详细讨论我们在后面进行。
　　PL/SQL块语法
　　[DECLARE]
　　---declaration statements
　　BEGIN
　　---executable statements
　　[EXCEPTION]
　　---exception statements
　　END
　　PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以使多行的，但分号表示该语句的结束。一行中可以有多条SQL语句，他们之间以分号分隔。每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。注释由--标示。
　　Oracle中过程介绍
　　oracle中的过程是一种命名的快，存储在oracle数据库中，相当于sql server中的存储过程。
　　下面是一个过程的定义：
　　create or replace procedure query_emp--先删除重名的，后新建
　　（
　　v_no in emp.empno%type,--该参数是接受外来实参赋值的（in）
　　v_name out emp.ename%type,--该参数是在过程内部赋值的（out）
　　v_sal out emp.sal%type--该参数是在过程内部赋值的（out）
　　）
　　is
　　e_sal_error exception;--声明了一个异常
　　begin
　　select ename,sal into v_name,v_sal from emp where empno=v_no;
　　if v_sal >2500 then
　　dbms_output.put_line（'雇员工资'||v_sal）;
　　raise e_sal_error;
　　else
　　null;
　　end if;
　　exception
　　when no_data_found then
　　dbms_output.put_line（'该雇员不存在'）;
　　when e_sal_error then
　　dbms_output.put_line（'该雇员工资高于2500了'）;
　　end query_emp;
　　过程的调用：
　　a.在sql*plus中：
　　SQL> variable a1 nvarchar2（16）;
　　SQL> variable a2 number;
　　SQL> execute query_emp（7788,:a1,:a2）;
　　b.在其他pl/sql块中
　　declare
　　v_al emp.ename%type;
　　v_a1 emp.sal%type;
　　begin
　　query_emp（v_ename=>v_al,v_sal=>v_a1,v_no=>5678）;
　　end;
　　我们创建的过程都是存放在数据字典中的，可以用以下语句来查询我们创建好的块：
　　select * from user_source where name='QUERY_EMP';
　　将过程的执行权限授予其他用户：
　　Grant execute procname to username;
　　删除过程：
　　Drop procedure procname;
　　Oracle中函数的介绍
　　函数和过程一样也是一种命名的块，其主要作用是返回值
　　如：
　　create or replace function get_salary_by_deptno
　　（
　　v_dept_no in emp.deptno%type,--输入部门号
　　v_emp_cnt out number--输出部门人数
　　）
　　return number
　　is
　　Result number;--要返回的工资总数
　　begin
　　select sum（sal）,count（*） into Result,v_emp_cnt from emp where deptno =v_dept_no;
　　return（Result）;
　　end get_salary_by_deptno;
　　在sql中调用函数以及在PL/SQL中调用函数和调用过程是一样的，只不过是调用函数必须放在表达式中：
　　删除函数
　　drop function get_salary_by_deptno
　　Oracle游标介绍
　　简单说，Oracle游标提供了以一种对多行数据的查询结果集中的每行数据单独处理的方式，是设计交互式应用程序常用的编程接口。
　　游标分为显示游标和隐式游标，隐式游标是oracle提供的游标，显示游标是用户自定义的游标。
　　游标的使用步骤：
　　1）声名游标
　　2）打开游标
　　3）提取游标
　　4）关闭游标
　　使用游标的几种方式
　　1.一般调用方式
　　declare
　　cursor c1（v_ss nvarchar2）--声名了一个个带参数的游标
　　is
　　select * from stu where ss=v_ss;--游标的select语句
　　v_stu c1%rowtype;--基于游标定义的变量，可以存储游标取出的每一行数据
　　begin
　　open c1（224）;
　　loop
　　fetch c1 into v_stu;
　　exit when c1%notfound;
　　dbms_output.put_l