oracle-database-11g-plsql-编程实战笔记

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、始终在执行块中进行赋值或初始化。