下载文档原格式
『ORACLE』PLSQL动态游标的使⽤（11g）#静态游标指的是程序执⾏的时候不需要再去解析sql语⾔，对于sql语句的解析在编译的时候就可以完成的。
动态游标由于含有参数，对于sql语句的解析必须要等到参数确定的时候才能完成。
从这个⾓度来说，静态游标的效率也⽐动态游标更⾼⼀些。
#游标的相关概念： 定义： 游标它是⼀个服务器端的存储区，这个区域提供给⽤户使⽤，在这个区域⾥ 存储的是⽤户通过⼀个查询语句得到的结果集，⽤户通过控制这个游标区域当中 的指针来提取游标中的数据，然后来进⾏操作。
实质： 是⽤户在远程客户端上对服务器内存区域的操作，由数据库为⽤户提供这样的 ⼀个指针，使得⽤户能够去检索服务器内存区的数据。
#游标具有的属性：1、%ISOPEN（确定游标是否已经打开 true or false）2、%FOUND（返回是否从结果集提取到了数据 true or false）3、%NOTFOUND（返回是否从结果集没有提取到数据 true or false）4、%ROWCOUNT（返回到当前为⽌已经提取到的实际⾏数）#游标分类⼀、静态游标1、隐式游标：对于select..into...语句，⼀次只能从数据库中获取到⼀条数据，对于这种类型的DML SQL语句，就是隐式cursorselect update/insert/delete操作2、显⽰游标：由程序员定义和管理，对于从数据库中提取多⾏数据，就需要使⽤显式cursor1）定义游标---cursor [cursor name] is2）打开游标---open [cursor name]3）操作数据---fetch [cursor name]4）关闭游标---close [cursor name]⼆、REF游标1、REF 游标：动态关联结果集的临时对象。
即在运⾏的时候动态决定执⾏查询。
2、REF 游标作⽤：实现在程序间传递结果集的功能，利⽤REF CURSOR也可以实现BULK SQL，从⽽提⾼SQL性能。
Oracle11g--使⽤PLSQL进⾏excel、csv、txt⽂件的数据导⼊Oracle 11g -- 使⽤PLSQL进⾏excel、csv、txt⽂件的数据导⼊1、导⼊ txt/csv ⽂件 (使⽤ Text Importer)创建表结构：create table student(id int,name varchar(255),birthday varchar(255),sex varchar(255))创建表数据-student.txt（分隔符为’\t’）：01 赵雷 1990-01-01 男02 钱电 1990-12-21 男03 孙风 1990-05-20 男04 李云 1990-08-06 男05 周梅 1991-12-01 ⼥06 吴兰 1992-03-01 ⼥07 郑⽵ 1989-07-01 ⼥08 王菊 1990-01-20 ⼥使⽤Text Importer导⼊⼯具：然后点击下⽅的import进⾏导⼊。
遇到问题：解决⽅法：原始数据的⽇期是⽤”-”分隔开的，并不是默认的⽇期数字的形式，这⾥可以不使⽤to_date()进⾏转换，保留原来的格式即可。
再次import即可导⼊成功，查询数据如下：注：如果有主键约束会⾃动覆盖掉相同键的记录2、导⼊excel⽂件（excel转csv）准备的excel⽂件如下：清空Oracle中表student的数据：truncate table student;1）尝试导⼊⼀：使⽤ODBC Importer导⼊⼯具User/System DSN中没有选项，并且出现问题：解决过程（fail了）：⾃⼰电脑是64位系统，⽽ODBC数据源管理程序是32位的，需要⼿动添加新excel数据源DSN驱动：运⾏C:\Windows\SysWOW64\odbcad32.exe点击添加，选择如下图所⽰Microsoft Excel Driver（*.xls）但是重启PLSQL或是服务之后还是识别不到新增的驱动。
建议在PLSql Develeper 工具中查看，阅读/*Oracle学习笔记*/SQLPlus：所谓的SQLPlus，就是oracle数据库的一个客户端。
登录sqlplus的几种方式：1 命令行版的sqlplus：在dos窗口中直接输入sqlplus，则需要你输入用户名和密码，进入oracle的命令行了，即进入了oracle的客户端。
2 图形版的sqlplus：开始程序处执行。
3 浏览器模式isqlplus：http://127.0.0.1:5560/isqlplus/ --5560是oracle的端口号，通过这个方式可以访问其他ip主机的oracle4 oracle常用的客户端软件：Toad(英文意思蛤蟆)/*数据库管理的时候常用*/，PL/SQL Developer(常用)/*需要在本地的oracle客户端也装上oracle*/，相比之下，isqlplus较为好用，在客户端不需要装任何东西，只需要一个浏览器就可访问----------------------------------------dos命令：1 sqlplus sys/bjsxt AS SYSDBA; --as sysdba代表以数据库管理员的身份登录2 ALTER USER scott ACCOUNT UNLOCK; --将用户scott解锁------------------------------------------------------------------------------------------------------------------------------------------SQL语言SQL(Structured Query Language)SQL是关系数据库的标准语言，到现在为止，有两个标准：1992年定义的SQL/92和1999年定义的SQL99，SQL在大多数的数据库上通用，只是不同的数据库对于SQL语言都有那么一点小的改变。
PL/SQL学习笔记PLSQL特有的%TYPE属性来声明与XX类型一致的变量类型：举例：...v_st_name%TYPE;v_min_balancev_balance%TYPE:= 10;...可绑定变量（Bind Variable 也称为Host Variable , 非PLSQL 变量）：可绑定变量是一种在缩主环境中定义的变量，所谓缩主环境一般指示SQLPLUS执行环境或者是PLSQL Developer 的Command Window执行环境；可绑定变量可用于在运行时把值传递给PLSQL, 创建语法:V ARIABLE return_codeNUMBERV ARIABLE return_msgV ARCHAR2(30)大家注意，在标准的PLSQL中定义变量是不能用V ARIABLE关键字的，此关键字只在SQLPLUS执行环境中有效，可使用PRINT语句输出变量内容。
在PLSQL中使用这种变量时，前面加”:”, 以示区分。
SELECT INTO 语句：用于把从数据库查询出内容存入变量BEGININSERT INTO employees(employee_id, first_name, last_name, email,hire_date, job_id, salary)V ALUES(employees_seq.NEXTV AL, 'Ruth', 'Cores', 'RCORES',sysdate, 'AD_ASST', 4000);END;循环语句的语法与其他语言类似：有基本循环、For循环、Wihle循环三种LOOPstatement1;. . .EXIT [WHEN condition];END LOOP;WHILE condition LOOPstatement1;statement2;. . .END LOOP;FOR counter IN [REVERSE]lower_bound..upper_bound LOOPstatement1;statement2;. . .END LOOP;概述：PLSQL中常用的自定义类型就两种：记录类型、PLSQL内存表类型（根据表中的数据字段的简单和复杂程度又可分别实现类似于简单数组和记录数组的功能）记录类型的定义语法：TYPE type_name IS RECORD(field_declaration[, field_declaration]…);identifiertype_name;这里的field_declaration 的具体格式可以是：field_name {field_type | variable%TYPE| table.column%TYPE | table%ROWTYPE}[[NOT NULL] {:= | DEFAULT} expr]%ROWTYPE属性：在PLSQL中%ROWTYPE 表示某张表的记录类型或者是用户指定以的记录类型，使用此属性可以很方便的定义一个变量，其类型与某张表的记录或者自定义的记录类型保持一致。
Oracle学习笔记1第一章PL/SQL简介1.1块结构（Block）DECLARE--declarative section/*声明部分，声明变量（variables）、类型（types）、游标（cursors）和局部子程序*/BEGIN--executable section/*执行部分，放置过程性语句（procedural statement）*或SQL语句（SQL statemnet）。
*这是主体，是必须的。
*/EXCTPTION--exception section/*错误处理部分*/END;//“；”很关键，不要忘记1.2变量和类型（variables and types）支持各种常用的数据库类型，还支持用户自定义类型，如表（table）、记录（record）。
还支持对象类型。
1.3注释(comment)Oracle的PL/SQL支持"--"和“/**/”的注释风格。
2第二章PL/SQL基础2.1PL/SQL块匿名块（anonymous）动态生成，只执行一次。
带名块（named）是带有标签的匿名块，动态生成，只执行一次。
子程序（subprogram）是存储在数据库内部的过程、函数和包。
可多次执行。
触发器（trigger）是存储在数据库内部的带名块，可多次执行。
由触发事件（triggering event）来触发。
2.2词法单位（lexical unit）包括标识符（identifier）、分界符（delimiter）、文字（literal）和注释（comment）2.2.1标识符（identifier）PL/SQL对大小写不敏感。
最长30位。
a)保留字（reserved word）或关键字（keyword），保留字单独使用时是保留的，但可以出现在其他标识符的内部。
如：下面的用法是合法的，尽管“Date”是保留字。
DECLAREv_BeginDate Date;b)引号标识符（quoted identifier）如果想要标识符大小写敏感，如在标识符中出现空格这样的字符或使用一个保留字，那么必须使用引号标识符。
PLSQL笔记PLSQL开发笔记和小结*****************************************PLSQL基本结构*****************************************基本数据类型变量1. 基本数据类型Number 数字型Int 整数型Pls_integer 整数型，产生溢出时出现错误Binary_integer 整数型，表示带符号的整数Char 定长字符型，最大255个字符Varchar2 变长字符型，最大2000个字符Long 变长字符型，最长2GBDate 日期型Boolean 布尔型（TRUE、FALSE、NULL三者取一）在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型，有的含义是完全一致的，有的是有不同的含义的。
2. 基本数据类型变量的定义方法变量名类型标识符 [not null]:=值;declareage number(3):=26; --长度为3，初始值为26begincommit;end;其中，定义常量的语法格式：常量名 constant 类型标识符 [not null]:=值;declarepi constant number(9):=3.1415926;--为pi的数字型常量，长度为9，初始值为3.1415926 begincommit;end;表达式变量、常量经常需要组成各种表达式来进行运算，下面介绍在PL/SQL中常见表达式的运算规则。
1. 数值表达式PL/SQL程序中的数值表达式是由数值型常数、变量、函数和算术运算符组成的，可以使用的算术运算符包括+（加法）、-（减法）、*（乘法）、/（除法）和**（乘方）等。
命令窗口中执行下列PL/SQL程序，该程序定义了名为result的整数型变量，计算的是10+3*4-20+5**2的值，理论结果应该是27。
―――――――――――――――――――――――――――――――――――――set serveroutput onDeclareresult integer;beginresult:=10+3*4-20+5**2;dbms_output.put_line('运算结果是：'||to_char(result));end;―――――――――――――――――――――――――――――――――――――dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。
备注：以下是个人学习笔记总结，其实是本人容易忘记，工作中碰到的一些知识点，记录下来整理成笔记了，序号之间并不是按照某种类型划分的，都是随意的标记一下，希望对大家有所帮助。
昵称：阿杜笑傲江湖（其实就是个名字而已，并不江湖…）name：杜立鸿（不要冒充，万一哪天中奖了呢？）sex:爷们---------------------------废话不多说，GO，GO，Go……1.允许修改分区建（有时候分区键更新不了，需要以下这样处理，当然了分区键本来是不允许更新的，都得根据实际情况）alter table t1 enable row movement；2. 获取某一时间最近的时间sqlselect *from t_datetime twhere t.f_time - to_date('2018-09-06 10:10:00','yyyy-mm-ddhh24:mi:ss') =(select min(t.f_time - to_date('2018-09-06 10:10:00','yyyy-mm-ddhh24:mi:ss'))from t_datetime t);3. 1.创建一个object类型的数据库类型对象。
表示学生实体类型。
（注意：此类型必须定义为数据库对象级别的类型，而不能定义成包、函数级别的类型。
否则，函数外部代码是无法识别该类型的）。
CREATE OR REPLACE TYPE student_obj_type AS OBJECT(stu_no NUMBER, --学号stu_name VARCHAR2(255), --姓名stu_sex VARCHAR2(2),--性别score NUMBER--成绩);4.创建一个嵌套表类型的数据库类型对象。
表示学生实体集合类型。
该类型也将用作函数中定义的返回类型。
（注意：此类型必须定义为数据库对象级别的类型，而不能定义成包、函数级别的类型。
ORACLE11gR2学习笔记<一>PL/SQL Developer(64位)远程连接64位oracle11gR2数据库(数据库在vmware)前言：自PL/SQL Developer出了64位版本以后，本人亲测试验安装，现分享给大家。
有的人可能已经在32位的PL/SQL Developer 上安装起来了，可以略过。
一：首先配置监听器（Listener）监听器是Oracle基于服务器端的一种网络服务，主要用于监听客户端向数据库服务器端提出的连接请求。
既然是基于服务器端的服务，那么它也只存在于数据库服务器端，进行监听器的设置也是在数据库服务器端完成的。
配置如下图：1：首先打开”运行”窗口输入cmd命令，然后输入netca打开监听配置页面;2:打开后弹出如下窗口3:先配件监听程序，因为我本机已经配置好，所以我选重新配置，第一次配置的朋友选择添加，如下图：二：然后配件本地网络服务名本地服务名是基于Oracle客户端的网络配置，所以，如果客户端需要连接数据库服务器进行操作，则需要配置该客户端，其依附对象可以是任意一台欲连接数据库服务器进行操作的PC机，也可以是数据库服务器自身。
1：配置如下图注意：因为我创建的数据库是ORCL，所以选择ORCL;注意：这里的主机名与端口号必须与数据库服务器端监听器配置的主机名和端口号相同。
3：最后打开Net Manage检查下前面所做的配置如下图：注意：如果有修改，最后别忘记点下保存网络配置；二：安装pl/sql developer(x64)和instantclient-basic-windows.x64-11.2.0.4.0.zip 1：首先去网上下这个两个软件，然后先解压instantclient-basic-windows.x64-11.2.0.4.0.zip(下载64位版本)到客户端任意磁盘的根目录，如图,然后在oracle服务端拷贝一份tnsnames.ora(C:\app\product\11.2.0\dbhome_1\NETWORK\ADMIN)到C:\instantclient_11_2目录下，打开编辑如下：2:安装64位版本PL/SQL Developer,安装过程这里省略；3：配置PL/SQL Developer，打开工具-->首选项-->连接，然后配置如图：(第一次打开软件先点取消登陆)注意：Oracle主目录名也可以为：OraDb11g_home1三：配置操作系统(本人是windows7x64)环境变量1：添加环境变量NLS_LANG=AMERICAN_AMERICA.ZHS16GBKTNS_ADMIN=C:\instantclient_11_22：在系统环境变量中配置Path，添加instantclient的路径，即C:\instantclient_11_2如图;到此PL/SQL Developer就全部配置完成了，我截图给大家看下：<二>干净地卸载oracle数据库1：步骤如下图：<三>oracle监听器1：启动监听器-lsnrctl start LISTENER;//listener是监听器的名字，如果没有指定监听器的名字，则该命令将启动默认的监听器。
Chap1DML语句是select 、insert、update、delete和mergeDDL语句是create、alter、drop、rename、truncate、commentDCL语句是grant、revokeTCL语句是commit、rollback和savepointsql16个基本命令——参考书《OCA认证考试指南（IZ0-051）》清华大学出版社《oracle database sql language reference 11g》有非遵循格式字符串依赖于格式掩码chap22.1.3 关于语句中有多个单引号时处理：1、select'It''s a bird,no plan can''t be 'as pharsefrom dual; 此处两个单引号即为一个单引号2、只能用q 再加’(语句)’select q'(It's a bird,no plan can't be)'as pharsefrom dual;均输出PHARSE----------------------------It's a bird,no plan can't be2.1.4定义变量与申明变量的区别：定义变量即为变量分配名称并指定数据类型；申明变量首先需要定义变量，然后为其赋值。
(赋值也称为初始化)替代变量前面要加&前缀且若替代变量为字符型时要加两个单引号如’&a’declarelv_whom varchar2(20);/*lv-whom为申明变量，a为替代变量，a没有变量类型*/beginlv_whom := '&a';end;或者declarelv_whom varchar2(20);beginlv_whom := &a;end;但是要在输入框中字符加两个单引号替代变量用define申明，且定义时不可以指定类型，默认为char型①Define x=emp;Select * from &x; /*调用要用&，此处不加单引号，解析后即为emp表*/② define x=adasd;select'&x' z from dual;/*此处解析后变为select' adasd ' z from dual ，此处必须要有单引号，使adasd作为直接变量，否则出错*/所以替代变量是否加单引号要根据解析的值来确定注意：避免在声明块中将任何实参赋给局部变量（constant变量除外），且替代变量与绑定变量都不应该在声明块中赋值定义为constant的变量，必须在申明块中申明，这意味着常量必须先定义，再给他赋一个不变的值绑定变量（bind）用var或variable申明使用冒号（:）作为前缀var a numberbegin:a := 22;end;上面也可以输出结果，下面也可以输出结果，但二者不能同时放一块print a; /*此处a前面不能加冒号，print可用来输出所有类型的变量*/另外也可通过exec直接给绑定变量赋值：SQL> var x number /*必须要先定义再赋值，而替代变量定义时不需指定类型，直接赋值即可*/SQL> var y numberSQL> exec :x :=1;:y :=2;/*exec 等价于begin … end*/PL/SQL procedure successfully completedx---------1y---------2也可以先定义好绑定变量，再将其赋给其他变量：var a numberbegin:a := 22;end;现将其运行再declarelv_a number;beginlv_a:= :a;dbms_output.put_line('hello，'||lv_a||'.');end;结果：hello,22.将上面的代码放入到d:\q.sql中用@d:\q.sql;调用即可，但是里面的代码要正确2.1.5命名块1、过程块create or replace procedure abc (aa varchar2) isbegindbms_output.put_line('sdhl'||aa);end;exec abc('dkjdhhdj');可能出现错误一：【ORA-00955: 名称已由现有对象使用】这个错误说的不仅仅是有可能你有存储过程使用了【abc】，还有可能是你有表名叫【abc】或者其他oracle对象叫【abc】。
说明：其实由于你已经使用了【create or repalace procedure】即使有存储过程叫【abc】也会被覆盖掉，你用下面的命令查询一下，有什么对象叫【abc】，删掉它或者换一个存储过程名字。
【SELECT OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_NAME='STUDENT2';】错误二：过程中的参数不能指定具体长度2、函数块错误一:函数里面要有return，不是dbms_output.put_line（但实验之后二者均可以成功创建函数，但是调用时只有return的能没有错误的执行）create or replace function abc (aa varchar2)return varchar2isbegindbms_output.put_line('sdhl'||aa);end;改为create or replace function abc(aa varchar2) return varchar2isbeginreturn'sdhl' || aa;end;但函数不能像过程一样来执行，因为execute命令不允许管理函数返回值。
必须要用call或begin dbms... end命令将返回值放入一个绑定变量（要先定义）中，在输出。
（必须要先执行函数，在执行下面语句，不能一起运行）variable result varchar2(20);call abc('akdhak') into :result; --call后面只能用绑定变量？或者：SQL> begin2 dbms_output.put_line(abc('du'));3 end;4 /sdhldu此处若用print则绑定变量不能加冒号只能为print result;但可以查询select :result from dual;（exec是sqlplus的命令，只能在sqlplus中使用。
call是sql命令，任何工具都可以使用）2.1.6嵌套块命名块还可以嵌套在其他命名块或匿名块中，但嵌套命名块并不是已经发布的，这意味着在调用一个命名块时，被调用的命名块可能还没有定义declareprocedure a isbegindbms_output.put_line(b||'hello!'); /*此处b为被调用函数，但之前没有被解析，所以运行会出错*/end a;function b return varchar2isbeginreturn'hello!';end b;begina;end;注：所有匿名块都会在程序都会在实际执行前进行分析，分析是一个编译过程。
分析过程将会识别标识符（保留字）、预定义标识符、引用标识符、用户定义变量、子例程或UDT。
命名块也是标识符，PL/SQL按照自顶向下的次序，将标识符读取到内存中。
上例中函数b因为在过程a 的下面还没被解析，所以会出错。
使用“前向引用”可以修正这个问题，函数或过程的前向引用只需要函数或过程的署名，不需要同时包括署名和实现，这些署名在PL/SQL中叫做“占位程序（stub）”。
占位程序允许编译过程在实现命名块之前接受其标识符名称。
更正：declareprocedure a;function b return varchar2;procedure a isbegindbms_output.put_line(b||'hello!');end a;function b return varchar2isbeginreturn'hello!';end b;begina;end;2.2 变量：类型、赋值和运算符2.2.1文本数据类型用伪列来隐式地定义数据类型。
伪列如%typeChar(20)为一个定长的类型，不管其中变量的长度有没有达到20，最后显示长度均为20字符Varchar2（20）则要根据实际情况来确定其长度Clob（character large object 字符大对象）同varchar22.2.2日期和时间戳类型1、日期有两种字符串字面值赋值支持到date类型的隐式转换Lv_date date := ’22-mon-75’；或者Lv_date date := ’22-mon-1975’；除了上面两种，任何其他字符串字面值都要求使用to_date内置SQL函数来覆盖格式掩码如lv_date_1 date := to_date(‘19750430’,’YYYYMMDD’);2、间隔间隔子类型允许将天的间隔表示秒，将年的间隔表示月份数Interval day to second 数据类型的默认值在两个date相减时能起作用，只要在执行减法之前将其转化为timestamp，因为to_timestamp函数保留了date的精度，而该精度低于timestamp将天的间隔转换为秒的数据类型为interval day/day(4或其他数) to seconddeclarelv_interval interval day to second;lv_end_day date :=sysdate; ---sysdate与date对应lv_start_day date := '18-4月-2012';beginlv_interval := to_timestamp(lv_end_day)-to_timestamp(lv_start_day); dbms_output.put_line(lv_interval);end;结果-20 14:56:54.000000PL/SQL procedure successfully completedDate数据类型默认支持2位数字表示的天，timestamp的精度要求使用9位数字表示的天或者declarelv_interval interval day(9) to second;lv_end_day timestamp := systimestamp; ---systimestamp与timestamp对应lv_start_day timestamp := '18-4月-2012';beginlv_interval := lv_end_day-lv_start_day;dbms_output.put_line(lv_interval);end;结果-000002943 02:52:41.860000PL/SQL procedure successfully completed将年的间隔转化为月份数的数据类型为interval year to month从一个日期中提取年用 to_char(extract(year from lv_end_day))完整代码：declarelv_a date := '20-4月-2009';a varchar2(12);begina := to_char(extract(year from lv_a));dbms_output.put_line(a);end;结果为：20093、时间戳（timestamp）Timestamp数据类型精度要比date数据类型精度高2．2.3 数值类型可以将number数据类型隐性转化为intger类型，可能会丢失一些小数点Binary_float 32位浮点数Binary_double 是一个64位浮点数2.2.4复合变量类型SQL UDT用于保存一个数据结构2.3控制结构1、If语句，if elsif else2、case语句分为简单的case语句case … when … then …else … end case (break 隐式存在)与搜索型的case语句case when … then … else … end case简单case语句可使用char、nchar、varchar2数据类型，而搜索case语句可以使用任意布尔表达式，搜索case语句不局限于等值匹配2.3.3、循环结构Loop循环退出要借助exit或exit when语句For loop循环：分为范围循环和游标循环插入：关于游标（见E:\sas\sql\各类知识要点\游标）A、范围循环即for i in 1..4 loopB、游标循环 1、隐式 for i in (select 语句)loop2、显式 for i in cur_v(游标)loop其中第2种要比第一种要有更好的可读性（以后用这种）C、where current of 字句while循环while (...)loop内可含continue/goto语句Simple 循环语句即利用隐式游标属性来进行循环判断如if SQL%FOUND then ...else ...end if ;2．4批量操作（见E:\sas\sql\各类知识要点\游标）批量处理是进行成批处理和大批量处理的默认选择本章小结：1、始终在执行块中进行赋值或初始化。
