学习oracle笔记--PLSQL

学习oracle笔记一、PLSQL概念 (2)1.过程化语言：在oracle中编写存储过程、函数、触发器等。

(2)2.特点 (2)3.学习的必要性： (2)二、开发工具介绍 (2)1.Sqlplus (2)2.PL/SQL developer (2)三、PL/SQL基础 (3)1.介绍： (3)2.PL/SQL可以做什么 (3)3.编写规范 (3)4.块（block） (3)5.过程 (5)6.函数 (5)7.包 (6)8.变量类型 (6)四、Pl/sql进阶（控制语句、存储过程、例外） (10)1.控制结构（条件语句、循环语句、顺序控制结构...）.. (10)2.存储过程（编写分页过程） (13)3.例外处理 (16)五、Oracle视图 (20)1.概念： (20)2.视图的必要性 (20)3.视图和表的区别 (21)4.创建视图 (21)5.视图可以当作表一样进行join on联合查询。

(21)一、PLSQL概念1.过程化语言：在oracle中编写存储过程、函数、触发器等。

扩展oracle数据库的编程性能；减轻前端程序的复杂性；增强数据传输的安全性等等2.特点1)过程、函数、触发器是由PLSQL编写的2)过程、函数、触发器是写在oracle中3)PLSQL是非常强大的数据库过程语言4)过程、函数可以在java程序中调用3.学习的必要性：1)提高应用程序的运行性能（减少翻译语句步骤）2)模块化的设计思想（分页存储过程、复杂查询过程）3)减少网络传输量（传输语句减少，直接调用存储过程语句即可）4)提高安全性（传输语句直接调用存储过程名，看不到表名等，具体语句封装在oracle里，多了一层安全性）5)移植性不好（语句写在oracle里，换数据库则得重写）二、开发工具介绍1.Sqlplus举例：编写一个存储过程，该过程可以向某表中添加记录1)创建一张表Create table mytable(name varchar2(20),passwd varchar2(6));2)创建存储过程：往mytable表里添加数据Create (or replace) procedure mypro1 isBegin--执行部分Insert into mytable values(‘韩顺平’,’pw1234’);End;回车，输入/，再回车，如果有错误，可以SQL>show error查看错误3)如何调用该过程●Exec 过程名 (参数值1，参数值2)●Call过程名 (参数值1，参数值2)2.PL/SQL developer举例：编写一个存储过程，该过程可以删除某张表的记录1)Create (or replace) procedure mypro2 isBeginDelete from mytable;End;2)Exec mypro2;三、PL/SQL基础1.介绍：需掌握SQL语句、PL/SQL语句语法规则可以使用变量、逻辑控制语句可以轻松完成复杂的查询可以编写分页存储过程模块、订单处理存储过程模块等2.PL/SQL可以做什么1)简单分类：块（最小单元，衍生出）：●存储过程●函数●包●触发器3.编写规范1)注释：a)单行注释：--b)多行注释：/*.....*/2)标识符号的命名规范a)当定义变量时，建议用v_作为前缀，v_salb)当定义常量时，建议用c_作为前缀，c_ratec)当定义游标时，建议用_cursor作为后缀，emp_cursord)当定义例外时，建议用e_作为前缀，e_error4.块（block）：PLSQL执行的最小单元，嵌在存储过程、函数等等之中。

1)块是PL/SQL的基本程序单元，编写PL/SQL程序就是编写块，PL/SQL块中可以嵌套其他的PL/SQL块，可以实现复杂的功能2)块的结构：a)由三个部分构成：定义部分、执行部分、例外处理部分b)Declear--定义部分（该部分可选）/*定义常量、变量、游标、例外、复杂数据类型*/c)Begin--执行部分（该部分必须）/*执行PL/SQL语句和SQL语句*/d)Exception--例外处理部分（该部分可选）/*处理各种运行的错误*/3)实例1（最简单的块：只有执行部分）（要先执行Set serveroutput on/off –打开/关闭输出选项，才能输出）BeginDbms_output.put_line(‘hello’);--输出helloEnd;/，再回车，即可输出hello--dbms_output是oracle提供的包，put_line是该包中的过程4)实例2（有定义个执行部分的块）DeclareV_ename varchar2(5);--定义字符串变量BeginSelect ename into V_ename from emp where empno=&no;--执行部分Dbms_output.put_line(‘雇员名：’||v_ename);--输出部分End;/ 回车--&表示要接收从控制台输入的变量实例：declarev_ename varchar2(5);v_sal number(7,2);beginselect ename,sal into v_ename,v_sal from emp where empno=&no;dbms_output.put_line(’雇员名：’||v_ename||’,’||'工资：'||v_sal);end;/ 回车5)实例3（有定义、执行和例外部分的块）declarev_ename varchar2(5);v_sal number(7,2);beginselect ename,sal into v_ename,v_sal from emp where empno=&no;dbms_output.put_line(’雇员名：’||v_ename||’,’||'工资：'||v_sal);exceptionwhen no_data_found thendbms_output.put_line(’编号有误，请重新输入’);end;/ 回车5.存储过程实例4（要求输入用户名和新的工资，即可修改该用户的工资）Create procedure sp_pro4(spname varchar2,newsal number) isBeginUpdate emp set sal=newsal whwere ename=spname;End;/ 回车然后调用该过程即可实现修改工资的功能。

Java程序调用过程的方法6.函数实例5：输入雇员的姓名，返回该雇员的年薪create function sp_fun1(spname varchar2) return number is yearsal number(7,2);beginselect sal*12+nvl(comm,0)*12into yearsal from emp where ename=spname;return yearsal;end;/ 回车调用函数：var 年薪 number;call sp_fun1(’SCOTT’) into:年薪;7.包--创建一个包（包名sp_pack1）--声明该包有一个过程update_sal--声明该包有一个函数annual_incomecreate package sp_pack1 isprocedure update_sal(name varchar2,newsal number);function annual_income(name varchar2) return number;end;--给包（sp_pack1）实现包体create package body sp_pack1 isprocedure update_sal(name varchar2,newsal number)isbeginupdate emp set sal=newsal where ename=name;end;function annual_income(name varchar2)return number isannual_salary number;beginselect sal*12+nvl(comm,0) into annual_salary from emp where ename=name;return annual_salary;end;end;--调用包中的过程或函数：exec sp_pack1.update_sal('SCOTT',4000);8.变量类型●标量类型（scalar）●复合类型（composite）●参照类型（reference）●Lob（large object）1)标量，用于存放单个数据的变量，是最简单的变量定义标量类型案例a)定义一个可变长字符串V_name varchar2(10);b)定义一个小数范围-9999.99~9999.99V_sal number(6,2);c)定义一个小数并给一个初始值为5.4 :=是pl/sql的赋值号V_sal2 number(6,2):=5.4;d)定义一个日期类型的数据V_hiredate date;e)定义一个布尔变量，不能为空，初始值为falseV_valid Boolean not null default false;使用标量案例输入员工号，显示雇员姓名、工资、个人所得税（税率为0.03）declarec_tax_rate number(3,2):=0.03;v_ename varchar2(5);v_sal number(7,2);v_tax_sal number(7,2);beginselect ename,sal into v_ename,v_sal from emp where empno=&no;v_tax_sal:=v_sal*c_tax_rate;dbms_output.put_line('姓名：'||v_ename||'工资：'||v_sal||'所得税：'||v_tax_sal);end;/ 回车为防止定义字符串的长度不够用，可以用%type来定义：declarec_tax_rate number(3,2):=0.03;v_ename emp.ename%type;v_sal number(7,2);v_tax_sal number(7,2);beginselect ename,sal into v_ename,v_sal from emp where empno=&no;v_tax_sal:=v_sal*c_tax_rate;dbms_output.put_line('姓名：'||v_ename||'工资：'||v_sal||'所得税：'||v_tax_sal);end;/ 回车2)复合变量，用于存放多个值的变量，主要包括：●Pl/sql记录●Pl/sql表●嵌套表●varray案例：a)Pl/sql记录，类似高级语言中的结构体（类），引用记录成员时必须有前缀：（记录变量.记录成员）--pl/sql记录实例declare--定义一个pl/sql记录类型emp_record_type，类型包含三个数据name,salary,titletype emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);sp_record emp_record_type;beginselect ename,sal,job into sp_record from emp where empno=&no;dbms_output.put_line('员工名：'||sp_||'工资：'||sp_record.salary);end;/ 回车b)Pl/sql表，相当于高级语言中的数组，pl/sql表的下标可以为负数，并且没有限制。