PL/SQL详细整理

最新推荐文章于 2021-04-09 20:54:39 发布

我叫阿狸猫

最新推荐文章于 2021-04-09 20:54:39 发布

阅读量1.2k

点赞数

本文链接：https://blog.csdn.net/world_java/article/details/8445230

版权

公司一专栏收录该内容

25 篇文章 0 订阅

订阅专栏

PL/SQL的优点:

1.提高应用程序的运行性能

2.模块化的设计思想(分页或一些共性的业务流程操作)

3.减少网络传输量

4.提高安全性

PL/SQL块由三个部分构成:定义部分,执行部分,异常处理部分

declare

/*定义部分------>定义常量,变量,游标,异常,复杂数据类型 (这部分是可选的)*/

begin

/*执行部分------->要执行的pl/sql语句和sql语句* (这部分是必须的)/

exception

/*异常处理部分-------->处理运行的各种错误 (该部分是可选的)*/

end;

PL/SQL编写规范:

1.注释

*单行注释 --

select * from emp where empno=7788; --获取员工信息

*多行注释 /* */ (和java类似)

2.标示符号的命名规范

*当定义变量时,建议用v_作为前缀 v_sal

*当定义常量时,建议用c_作为前缀 c_rete

*当定义游标时,建议用_cursor作为后缀 emp_cursor

*当定义异常时,建议用e_作为前缀 e_error

例1:

1.创建一个简单的表

create table mytest(

name varchar2(10),
password varchar2(10)

);

2.创建过程

创建添加的过程

create or replace procedure xxc_pro1
is
begin
insert into mytest values('xxc','123');
end;

创建删除的过程
create or replace procedure xxc_pro2
is
begin
delete from mytest;
end;

执行删除的过程
若创建存储过程当中出现了编译错误在SQL Plus中可以调用show error;命令来查看错误信息

3.调用过程（在命令行中）
(1).exec或execute 过程名(参数1，参数2...); 在命令行窗口中执行execute xxc_pro1;
(2).call 过程名(参数1，参数2...); 在命令行窗口中执行call xxc_pro1();

注意:存储过程若是声明变量直接跟在is后即可,不需要declare

例2:(只包括执行部分的PL/SQL块)

set serveroutput on--打开输出选项

begin

dbms_out.put_line('hello');

end;

相关说明:

dbms.output是oracle所提供的包(类似java的开发包),该包包含一些过程,put.line就是dbms.output包的一个过程

例3:(只有定义部分和执行部分的PL/SQL块)

declare

--定义变量

v_ename varchar2(5);

v_sal number(7,2);

begin

--执行部分

select ename,sal into v_ename,v_sal from emp where empno=&no; --&后面的随便定义 &表示会弹出一个输入框让用户输入前面变量的对应顺序不能颠倒

--在控制台显示用户名

dbms_output.put_line('用户名---->'||v_ename||'工资--------->'||v_sal);

--异常处理

exception

when no_data_found then

dbms_output.put_line('输入的编号不存在,请重新输入');

end;

例4：(带有参数的存储过程)

create or replace procedure xxc_pro3(myName varchar2,mySal number)

--declare 如果要定义变量,可在此处定义

begin

--执行部分

update emp set sal=mySal where ename=myName;

end;

/ --加这个表示在命令行可以执行,因为在命令行按回车后不像在sql窗口是立即执行的

在命令行中调用execute xxc_pro3('xxc',500); 或call xxc_pro3('xxc',900);

在java程序中调用存储过程

package com.xxc.plsqltest;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *测试java程序调用oracle的存储过程
 */
public class TestOracleProcedure {
	private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:ORCL";
	private static String username = "scott"; 
	private static String password = "xxc";
	public static void main(String[] args) {
		try {
			//1.加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");
			//2.得到连接
			Connection conn = DriverManager.getConnection(url,username,password);
			//3.创建CallableStatement
			CallableStatement cs = conn.prepareCall("{call xxc_pro3(?,?)}");
			//给？赋值
			cs.setString(1, "xxc");
			cs.setInt(2, 1000);
			//4.执行
			cs.execute();
			//5.关闭资源
			cs.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

函数:

函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而在函数体内必须包含return语句的数据(函数只能返回一个值)。我们可以用create function来建立函数

例1：(输入雇员的姓名,返回该雇员的年薪)

create or replace function xxc_fun1(myName varchar2) return
number is yearSal number(7,2); -- 表示返回一个number类型的变量名为yearSal(共7位,并包含两位小数) is别忘记加
begin
--执行部分
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=myName; --nvl函数表示如果comm为null的话,就用0代替
return yearSal;
end;

调用函数:

var abc number;
call xxc_fun1('xxc') into:abc;

包：

包用于在逻辑上组合过程和函数的,它由包规范(包头)和包体两部分组成。

例1：(创建一个包xxc_package 声明该包中有一个过程xxc_pro3和一个函数xxc_fun1)

包头:

create or replace package xxc_package

is

procedure xxc_pro3(myName varchar2,mySal number);

function xxc_fun1(name varchar2) return number;

end;

包体:

create or replace package body xxc_package is
procedure xxc_pro3(myName varchar2,mySal number)      --具体过程的实现
is
begin
    update emp set sal = mySal where ename=myName;
end;

   function xxc_fun1(name varchar2)     --具体函数的实现
    return number is
    year_sal number(7,2);
    begin
      select sal*12+nvl(comm,0) into year_sal from emp where ename=name;
      return year_sal;
    end;
end;

需要注意的是在包头中定义的过程或者函数的参数名必须和包体中的参数名一致,否则编译出错

调用包的过程或者函数:(需要加包名点)

exec xxc_package.xxc_pro3('xxc',5000);

标量(scalar)-使用标量

在定义好变量后,就可以使用这些变量。这里需要说明的是PL/SQL块为变量赋值不同于其他的编程语言,需要在等号前加冒号(:=)

declare
v_tax_rate number(3,2):=0.03;   --税率
v_ename emp.ename%type;    --用户名   varchar(5)这样定义不好,万一超过5个就出异常了 %type表示参照表中此字段的类型
v_sal emp.sal%type;     --工资 number(7,2)
v_tax_sal number(7,2);    --税后工资
begin
select ename,sal into v_ename,v_sal from emp where empno=&nh;
v_tax_sal:=v_sal*v_tax_rate; --冒号别忘记
dbms_output.put_line('姓名----->'||v_ename);
dbms_output.put_line('工资----->'||v_sal);
dbms_output.put_line('交税----->'||v_tax_sal);
--执行
end;

%type属性定义变量,这样它就会按照数据库列来确定你定义的变量的类型和长度

用法-----> 标识符名表名.列明%type

复合变量(composite):

用于存放多个值的变量。主要包括这几种:

(1)pl/sql记录

(2)pl/sql表

(3)嵌套表(自己看书用的较少)

(4)varray(自己看书用的较少)

例1(PL/SQL记录实例)

declare

--自定义一个类型自定义这个类型里存储了哪些类型的数据

type emp_record_typeis record(name emp.ename%type,salary emp.sal%type,job emp.job%type);

xxc_record emp_record_type;--声明一个名为xxc_record的,类型为emp_record_type的变量

begin

select ename,sal,job into xxc_record from emp where empno=&no;

dbms_output.put_line('姓名---->'||xxc_record.name);

dbms_output.put_line('工资---->'||xxc_record.salary);

end;

例2：(PL/SQL表实例)

declare --定义了一个PL/SQL表表类型 xxc_table_type,该类型用于存放emp.ename%type

type xxc_table_typeis table of emp.ename%type index by binary_integer; --index by binary_integer表示下标是整数

xxc_table xxc_table_type; --定义了一个名为xxc_table的变量,其类型为上面定义的type xxc_table_type类型

begin

select ename into xxc_table(-1) from emp where empno=&no; --下标可为负数

dbms_output.put_line('员工名------------>'||xxc_table(-1));

end;

参照变量：

参照变量是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。在编写PL/SQL程序时,可以使用游标变量(ref cursor)和对象类型变量(ref obj type)两种参照变量类型(用的较少)

例1:(参照变量 -ref cursor 游标变量)

使用游标时,当定义游标时不需要指定相应的select语句,但是当使用游标时(即open时)需要指定select语句,这样一个游标就与一个select语句结合了

需求:使用PL/SQL编写一个块,可以输入部门号,并显示该部门所有员工姓名和他的工资。

declare
--定义游标   (定义一个游标类型)
type xxc_emp_cursor is ref cursor;
--定义一个游标变量
xxc_cursor xxc_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把xxc_cusor和一个select语句结合 (即打开游标)
open xxc_cursor for select ename,sal from emp ;   --经测试这边有几个字段,下面就要取几个字段,也就是说上面就要定义几个变量。否则会出现<结果集变量或查询的返回类型不匹配>
--循环取值
loop
    fetch xxc_cursor into v_ename,v_sal;
    if(v_sal<1000)      --若工资小于1000则加1000
      then
          v_sal:=v_sal+1000;
    end if;
   --判断xxc_cursor是否为为null(即跳出循环的条件)
    exit when xxc_cursor%notfound;
    dbms_output.put_line('姓名----->'||v_ename);
    dbms_output.put_line('工资----->'||v_sal);
end loop;
close xxc_cursor;     --关闭游标

end;

PL/SQL控制结构:

一.条件分支语句

PL/SQL中提供了三种条件分支语句

(1)if then

(2)if then else

(3)if then elsif else (注意是elsif而不是elseif)

二.循环语句

（1）loop

是PL/SQL中最简单的循环语句,这种循环语句以loop开头,以end loop结尾,这种循环至少会被执行一次。

例1:(创建一个存储过程循环添加10次传进来的参数)

create or replace procedure xxc_pro1(myName varchar,myAge number) is
countt number(2):=0;
begin
loop
    exit when countt>10;
    insert into test (name,age) values(myName,myAge);
    countt := countt+1;
end loop;
end;

执行存储过程

exec xxc_pro1('xxc',10);

（2）while循环

基本循环至少要执行循环体一次,而对于while循环来说,只有条件为true时候,才会执行循环体语句,while循环以while..loop开始,以end loop结束.

需求:编写一个过程,可输入姓名,年龄,并循环添加10个用户到test表中。

create or replace procedure xxc_pro2(myName varchar,myAge number) is
v_count number(2):=11;
begin
while (v_count<=20)
   loop
     insert into test (name,age) values(myName,myAge);
     v_count:=v_count+1;
   end loop;
end;

（3）for循环

基本for循环的基本结构如下

begin
for i in reverse 1..10 loop
insert into test values('pkx',16);
end loop;
commit;
end;

我们可以看到,控制变量i,在隐含中就在不停的增加,并且步长不能改变。

三.顺序控制语句

（1）goto

goto语句用于跳转到特定标号去执行语句,注意由于使用goto语句会增加程序的复杂性,并使得应用程序可读性变差,所以在做一般应用程序开发时,建议大家不要使用goto语句。

基本语法如下:

declare
   i int:=1;
begin
   loop
      dbms_output.put_line('输出i='||i);
   if i=10 then
      goto end_xxc;
   end if;
      i:=i+1;
   end loop;
      dbms_output.put_line('循环结束1');
   <<end_xxc>>
      dbms_output.put_line('循环结束2');   --这句话如果没有会出错
end;

执行结果是输出1-10,打印循环结束2

（2）null

null语句不会执行任何操作,并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高PL/SQL的可读性。

declare

v_sal emp.sal%type;

v_ename emp.ename%type;

begin

select ename,sal into v_ename,v_sal from emp where empno=&no;

if v_sal<3000 then

update emp set comm=sal*0.1 where ename=v_ename;

else

null;

end if;

end;

用java程序调用带OUT参数的存储过程

例1:

create or replace procedure page_numf(xxc_bookId in number,xxc_bookName out varchar2,xxc_bookChuBanShe out varchar2)
is
begin
select b.bookname,b.bookchubanshe into xxc_bookName,xxc_bookChuBanShe from book b where bookid = xxc_bookId;
end;

package com.xxc.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 *调用一个无返回值的过程 
 */
public class CopyOfprocedureFTest {
	private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
	private static String username = "scott";
	private static String password = "xxc";
	private static Connection conn ;
	private static CallableStatement cs ;
	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, username,password);
			cs = conn.prepareCall("{call page_numf(?,?,?)}");//注意,这里调用的存储过程有几个参数下面就要设置几个。否则出'并非所有变量都已关联'异常
			cs.setInt(1, 1);
			//给第二个输出参数赋值
			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
			//给第三个输出参数赋值
			cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR);
			cs.execute();
			//取出返回值
			String bookName = cs.getString(2);
			String bookChuBanShe = cs.getString(3);
			System.out.println(bookName);
			System.out.println(bookChuBanShe);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs != null){
				try {
					cs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					if(conn != null){
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
	}
}

例2：

创建一个包,包中定义一个游标类型的变量方便调用

create or replace package testpackage
is
type test_cursor is ref cursor;
end testpackage;

定义存储过程,有两个参数,第一个参数是输入参数类型为number,第二个参数是输出参数,类型为游标类型

create or replace procedure xxc_test1(xxcNo in number,xxc_cursor out testpackage.test_cursor)
is--第二个参数的意思表示定义一个输出参数
begin
open xxc_cursor for select * from emp where deptno = xxcNo;
end;

package com.xxc.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *调用一个无返回值的过程 
 */
public class procedureCursorTest {
	private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
	private static String username = "scott";
	private static String password = "xxc";
	private static Connection conn ;
	private static CallableStatement cs ;
	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, username,password);
			cs = conn.prepareCall("{call xxc_test1(?,?)}");
			cs.setInt(1, 10);
			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);//调用存储过程输出参数为cursor类型时候需要这样设定
			cs.execute();
			//得到结果集,因为被调用的存储过程的第二个参数是游标类型
			ResultSet rs = (ResultSet) cs.getObject(2);//在java中用ResultSet类型接收
			while(rs.next()){
				System.out.println(rs.getInt(1));//进行结果集的遍历取值
				System.out.println(rs.getString(2));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs != null){
				try {
					cs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					if(conn != null){
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
	}
}

例3：

调用分页功能的存储过程

create or replace procedure fenye
(tableName in varchar2,
pageSize in number,     --一页显示多少条
pageNum in number,    --当前第几页
myrows out number,    --数据库总条数
myPageCount out number,   --总页数
my_cursor out testpackage.test_cursor   --返回结果集(游标类型)
)
is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number := (pageNum-1)*pageSize;
v_end number := pageNum*pageSize;
begin
v_sql := 'select * from (select t1.*,rownum rn from(select * from '||tableName|| ' order by sal) t1
            where rownum<'|| v_end ||')
            where rn>='||v_begin;
--打开游标,将sql语句和游标关联
open my_cursor for v_sql;
--计算数据库总条数,总页数
v_sql := 'select count(*) from '||tableName;
--执行sql,将返回值赋值给myrows;
execute immediate v_sql into myrows;
--计算总共多少页即是myPageCount
if mod(myrows,pageSize)=0 then    --这是在pl/sql里的取模,并且判断是否等于0不是用==而是=
     myPageCount := myrows/pageSize;
else
     myPageCount := myrows/pageSize+1;
end if;
--关闭游标   在java程序中调用会出java.sql.SQLException: Ref 游标无效异常
--close my_cursor;
end;

在java程序中调用

package com.xxc.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *调用一个无返回值的过程   分页的存储过程
 */
public class procedureFenYeTest {
	private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
	private static String username = "scott";
	private static String password = "xxc";
	private static Connection conn ;
	private static CallableStatement cs ;
	public static void main(String[] args) {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(url, username,password);
			cs = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");
			/**设置输入参数 */
			cs.setString(1, "emp");//设置查询哪张表
			cs.setInt(2, 3);//设置每页显示多少条
			cs.setInt(3, 2);//当前第几页
			
			/**设置输出参数 */
			cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);//注册总记录条数
			cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);//注册总页数
			cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);//注册返回的结果集
			cs.execute();
			
			int rowNum = cs.getInt(4);//获取总记录条数
			int pageCount = cs.getInt(5);//获取总页数
			ResultSet rs = (ResultSet) cs.getObject(6);//在java中获取游标类型的结果集
			System.out.println("rowNum="+rowNum);
			System.out.println("总页数="+pageCount);
			while(rs.next()){
				System.out.println("编号"+rs.getInt(1));//这里的索引表示在表中字段的索引
				System.out.println("名字"+rs.getString(2));
				System.out.println("工资"+rs.getFloat(6));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(cs != null){
				try {
					cs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}finally{
					if(conn != null){
						try {
							conn.close();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
				}
			}
		}
	}
}

自定义PL/SQL异常：

create or replace procedure ex_test(xxcNo number)
is
--定义一个异常
myException exception;
begin
--更新用户sal
update emp set sal=sal+1000 where empno=xxcNo;
-- sql%notfound 表示没有执行成功
if sql%notfound then
    raise myException; -- raise myException; 表示触发myException异常
end if;
exception
    when myException then
      dbms_output.put_line('没有更新任何用户');
end;

视图：

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行的数据。但是，视图并不在数据库

中以存储的数据值集形式存在。行和列数据来自于定义视图的查询所引用的表，并且在引用视图时动态生成。

视图与表的区别

1.表需要占用磁盘空间,视图不需要

2.视图不能添加索引

3.使用视图可以简化复杂查询

如果创建视图没有权限的话，可以先用system账户登陆进去,然后 grant create all view to 用户名；

创建视图或修改视图

create or replace view 视图名 as select 语句 [with read only]      中括号表示这个视图不能改

删除视图

drop view

例1：

create or replace view xxc_view as 
select emp.empno,emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno with read only;

调用视图

select * from xxc_view

我叫阿狸猫

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录