Oracle的存储过程

Oracle的PLSQL程序设计

1.PLSQL是什么?
PLSQL是专用于Oracle服务器的过程化SQL语言,它在SQL的基础之上,添加了一些过程化控制语句.过程化包括有:类型定义,判断,循环,游标,异常或例外处理…,强调的是过程.
PLSQL完整的组成结构如下:

[declare]  变量声明; -- 可以有多个  begin  DML/TCL操作;  [exception] 例外处理;  end;  /

注意:PLSQL中以;表示每条语句的结束,/表示整个PLSQL的结束.

2.PLSQL的Demo
第一个PLSQL程序

begin  --向SQLPLUS客户端工具输出字符串  dbms_output.put_line('Hello World');  end;  /--设置显示程序运行结果  set serveroutput on;

求10+100的和.

declare --声明变量mysum number(3) :=0;tip varchar2(10) :='结果是';begin --业务算法mysum:=10+100;--输出到控制器dbms_output.put_line(tip||mysum);end;/

输出7369号员工的姓名和工资,7369号员工的姓名是SMITH,薪水是800，语法：使用表名.字段%type

declare psname emp.ename%type;psal emp.sal%type;beginselect ename,sal into pename,psal from emp where empno=7369;    end;/

输出7788号员工姓名和工资，格式如下：7788号员工的姓名是SMITH,薪水是3000，语法：使用表名%rowtype

declare emp_record emp%rowtype;begin select * into emp_record from emp where empno=7369;end;/

%type&%rowtype的区别:
当定义的变量类型与表中的某字段类型相同时,用%type;
当定义的变量类型与整个表结构相同时,用%rowtype;
项目中,常用表.字段%rowtype

使用if-else-end if显示今天是星期几,是”工作日”还是”休息日”

declare pday varchar2(10);begin select to_char(sysdate,'day') into pday from dual;dbms_output.put_line('今天是'||pday);if pday in ('星期六','星期日') then dbms_output.put_line('休息日');elsedbms_output.put_line('工作日');end if;end;/ 

从键盘接收值，使用if-elsif-else-end if显示”age<16”，”age<30”，”age<60”，”age<80”

declare age number(3):=&age;beginif age < 16 then dbms_output.put_line('未成年');elseif age < 30dbms_output.put_line('青年人');elsedbms_output.put_line('奋斗人');end;/

使用loop循环显示1-10

declarei number(2):=1;beginloopexit when i > 10;dbms_output.put_line(i);i:=i+1;end loop;end;/

使用while循环显示1-10

declare i number(2):=1;begin while i < 11loopdbms_output.put_line(i);i:=i+1;end loop;iend;/

使用for循环显示1-10

declare i number(2) :=1;beginfor i in 1..10loopdbms_output.put_line(i);end loop;end;/

使用while循环，向emp表中插入999条记录

declarei number(4) :=1;beginwhile i<1000loopinsert into emp(empno,ename) values(i,'啊哈');i:=i+1;end loop;end;/

3.光标/游标/cursor
cursor类似于JDBC中的ResultSet对象的功能,从上向下依次获取每一记录的内容.
使用场景:当需要遍历多条记录时,使用cursor,无记录时使用cursor%notfound
示例一:使用无参光标查询所有员工的姓名和工资

declare--定义游标cursor cemp is select ename,sal from emp;--定义变量vename  emp.ename%type;vsal emp.sal%type;begin--开启游标,这时游标位于第一条记录之前open cemp;loop--向下移动游标一次fetch cemp into vename,vsal;--当游标下移一次后,找不到记录时,退出循环exit when cemp%notfound; dbms_output.put_line(vename||':'||vsal);end loop;--关闭游标close cemp;end;/

使用带参光标cursor,查询10号部门的员工姓名和工资

declarecursor cemp(pdeptno emp.deptno%type) is select ename,sal from emp where deptno=pdeptno;pename emp.ename%type;psal emp.sal%type;beginopen cemp(&deptno);loopfetch cemp into pename,psal;exit when cemp%notfound;dbms_output.put_line(pename||'d的薪水是'||psal);end loop;close cemp;end;/

使用无参光标cursor，真正给员工涨工资，ANALYST涨1000，MANAGER涨800，其它涨400，要求显示编号，姓名，职位，薪水

declare cursor cemp is select empno,ename,job,sal from emp;pempno emp.empno%type;pename emp.ename%type;pjob emp.job%type;psal emp.sal%type;beginopen cemp;loopfetch cemp into pempno,pename,pjob,psal;if pjob = 'ANALYST' then   update emp set sal=sal+1000 where empno = pempno;elseif pjob = 'MANAGER' then  update emp set sal=sal+800 where empno = pempno;else  update emp set sal=sal+400 where empno = pempno;end if;end loop;--*Oracle中,凡是update的操作都要进行commit*commit;close cemp;end;/

4.PLSQL例外
使用oracle系统内置例外，演示除0例外

declare   myresult number;beginmyresult :=1/0;dbms_output.put_line(myresult);exception  when zero_divide then    dbms_output.put_line('除数不能为0');   delete from emp; --这里不会在执行end;/

使用Oracle系统内置的例外,查询100号部门的员工姓名，演示没有找到数据(no_data_found)

declare   pename varchar2(20) ; begin  select ename into pename from emp where deptno = 100;  dbms_output.put_line(pename);exception  when no_data_found     dbms_output.put_line('查无该部门员工');     insert into emp(empno,ename) values(1111,"ERROR");end;/

使用用户自定义例外，使用光标cursor，查询100号部门的员工姓名，演示没有找到数据【no_data_found_emp】

declare   pename emp.ename%type;  cursor cemp is select ename into pename from emp where deptno = 100;  no_data_found_emp exception;begin  open cemp;    loop      fetch cemp into pename ;      if cemp%notfound then          raise no_data_found_emp;      else           dbms_output.put_line(pename);      end if;        end loop;  close cemp;exception  when no_data_found_emp then  dbms_output.put_line('查无该部门员工');end;/   

5.存储过程
为什么要用存储过程?
(1)PLSQL每次执行都要整体运行一遍,才有结果
(2)PLSQL不能将其封装起来,长期保存在Oracle服务器中
(3)PLSQL不能被其他应用程序调用,例如:Java
定义:存储过程是存储在数据库中供所有用户程序调用的子程序
创建无参存储过程hello,无返回值.

create or replace procedure p1asbegin   dbms_output.put_line('Hello World');end;/--执行存储过程方式一 PLSQL程序set serveroutput on;begin p1();end;/--执行存储过程方式二  execute 存储过程名(exec是SQLPLUS的命令,只能在SQLPLUS中用)set serveroutput on;execute p1();  //可以简写为 exec p1();--调用存储过程方式三 Java代码(JDBC)CallableStatement();--在sql的执行窗口中调用call p1(); --call是sql命令,任何工具都可以使用--删除存储过程drop procedure p1;

创建有参存储过程raiseSalary(编号)，为7369号员工涨10%的工资，演示in的用法，默认in，大小写不敏感

create or replace procedure raiseSalary(   pempno number  --不写时,默认为输入参数  )as  --as可看做declare,但不能出现declare,声明变量  psal emp.sal%type;begin  update emp set sal=sal*1.1 where empno =pempno;  select sal into psal from emp where empno=pempno;end;/--调用存储过程declare  pempno number;begin  raiseSalary(7369);  end;--exec raiseSalary(7369);

创建有参存储过程findEmpNameAndSalAndJob(编号)，查询7788号员工的的姓名，职位，月薪，返回多个值，演示out的用法

create or replace procedure findEmp(   pempno in number,   pename out varchar2,   pjob out varchar2,   psal out number )as  --或者用isbeginselect ename,job,sal into penaem,pjob,psal from emp where empno=pempno;--声明自定义异常exceptionwhen NO_DATA_FOUND then dbms_output.put_line('查无此工号的员工');end;/--调用declare  pempno number(4):=7788;  pename varchar2(20);  pjob varchar2(9);  psal number;begin  findEmp(pempno,pename,pjob,psal);  dbms_output.put_line(pename||'的职位是'||pjob||'薪水是'||psal);end;/

SQLPLUS中如何根据过程名查询已创建的过程?

select text from user_source where name='YOUR_PROC' --过程名要大写,因为数据字典中是按大写存储的order by line;

6.存储函数
语法:create or replace function 函数名(参数) return type as PLSQL程序段
创建有参存储函数findEmpIncome(编号)，查询7369号员工的年收入，演示in的用法，默认in

create or replace function findEmpIncome(  fempno number  )  return numberis  income number;begin  select round(sal*12+NVL(comm,0),0) into income from emp where empno=fempno; return income;exception when NO_DATA_FOUND then    dbms_output.put_line('查无此员工'); when OTHERS then    dbms_output.put_line(SQLCODE||'---'||SQLERRM);end findEmpIncome;--调用declare  income number;begin income:=findEmpIncome(7369); dbms_output.put_line(income);end;

获取某部门的工资总和

create or replace function get_salary(  Dept_no number,  Emp_count out number )return numberis V_sum number;begin  select SUM(sal),count(*) into V_sum,emp_count from emp where deptno=Dept_no;return V_sum;exception  when NO_DATA_FOUND then    dbms_output.put_line('查无数据');  when OTHERS then    dbms_output.put_line(SQLCODE||'--'||SQLERRM);end get_salary;--调用存储函数方式一,PLSQL程序--位置表示法declare v_num number;  v_sum number;begin v_sum:=get_salary(10,v_num);end;--名称表示法declare v_num number; v_sum number;begin  v_sum:=get_salary(emp_count => v_num,dept_no => 10);end;--SQLPLUS中调试错误  show errors;

过程和函数统称为PLSQL子程序,它们是被命名的PL/SQL块,均存储在数据库中,并通过输入,输出参数或输入/输出参数与其调用者交换信息.
过程VS函数
函数适合于只有一个返回值,过程适合于无返回值或多个返回值
过程与函数的使用场景
1>需要长期保存在数据库中
2>需要被多个用户重复使用
3>业务逻辑相同,只是参数不一样
4>批量操作大数据,如:批量插入很多数据
SQL的适合场景:
对表,视图,序列,索引等这些还是用sql

sql语句的优化,提高执行性能
1>选择最有效率的表名顺序
Oracle解析器按照从右到左的顺序处理from子句中的表名,from子句中写在最后的表将会被最先处理.因此,当from子句包含多个表的情况下,你必须选择记录条数最少的表放在最后;如果有3个以上的表做连接查询时,那就需要选择那个被其他表所引用的表放在最后.
例如:查询员工的编号,姓名,工资,工资等级,部门名

select e.empno,e.ename,e.sal,s.grade,d.dnamefrom salgrade s,dept d,emp e where (e.deptno=d.deptno) and (e.sal between s.losal and s.hisal)

1>>如果三个表完全无关系的话,将记录和列名最少的表,写在最后,然后依此类推
2>>如果三个表完全有关系的话,将引用最多的表,放在最后,然后依此类推

2>where子句中的连接顺序
Oracle采用自右向左的顺序解析where子句,根据这个原理,表之间的连接必须写在where条件之左,那些可以过滤掉最大数量记录的条件必须写在where子句之右.
例如:查询员工的编号,姓名,工资,部门名

select emp.empno,emp.ename,emp.sal,dept.dname from emp,deptwhere (emp.deptno = dept.deptno) and (emp.sal > 1500)

3>select语句中避免使用*号
Oracle在解析的过程中,会将*依次转换为所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间.
4>使用DECODE函数来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的
5>整合简单,无关联的数据库访问
6>用TRUNCATE替代DELETE
7>尽量多使用commit,因为commit会释放回滚点
8>用where子句替换having子句,where先执行,having后执行
9>多使用内部函数提高sql效率
10>使用表别名
11>使用列的别名
12>用索引提高查询效率
13>字符串型,能用=号,不用like
14>SQL语句用大写的
因为Oracle服务器总是先将小写字母转成大写的,才执行
15>避免在索引列上使用not
因为Oracle服务器在遇到not后,他就会停止目前的工作,转而执行全表扫描
16>避免在索引列上使用计算
where子句中,如果索引是函数的一部分,优化器将不使用索引而使用全表扫描,这样会是查询变慢
例如:SAL上有索引,

--低效SELECT EMPNO,ENAME FROM EMPWHERE SAL*12 > 24000;--高效SELECT  EMPNO,ENAMEFROM EMPWHERE SAL > 24000/12;

17>用>=替代>

--低效select ename,sal from emp where deptno>3;//首先会定位到deptno=3的记录并且扫描到第一个dept大于3的记录selece ename,sal from emp where deptnon >= 4;//直接跳到第一个deptno=4的记录

18>用in替代or

select * from emp where sal = 1500 or sal = 3000 or sal =800;select * from emp where sal in (1500,3000,800);

19>总是使用索引的第一个列
如果索引是建立在多个列上,只有在它的第一个列被where子句引用时,优化器才会选择使用该索引,当只引用索引的第二个列时,不引用索引的第一个列时,优化器使用了全表扫描而忽略了索引

create index emp_sal_job_index on emp(sal,job);select * from emp where job!='sales';

20>避免改变索引的类型,显示比隐式更安全
当字符和数值比较时,Oracle会优先转换数值到字符类型

select 123  || '123' from dual;  --结果:123123