Pl/SQL 编制程序

1 1 create table tb1(
2 2 
3 3 id int ,
4 4 
5 5 name nvarchar(20)
6 6 
7 7 )

3.建立一个返回游标: PKG_PUB_UTILS

永利官网ylg客户端 1View
Code

--建立一个返回游标CREATEORREPLACE PACKAGE PKG_PUB_UTILS IS--动态游标    TYPE REFCURSOR IS REF CURSOR;END PKG_PUB_UTILS;

六   ——2: 自定义异常

永利官网ylg客户端 2


7.开发JAVA调用函数返回结果集

代码示例:JDBCoracle10G_INVOKEFUNCTION.java

永利官网ylg客户端 3View
Code

永利官网ylg客户端 4

import java.sql.*;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;/* /* 本例是通过调用oracle的函数来返回结果集: * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip   */publicclass JDBCoracle10G_INVOKEFUNCTION {    Connection conn = null;    Statement statement = null;    ResultSet rs = null;    CallableStatement stmt = null;    String driver;    String url;    String user;    String pwd;    String sql;    String in_price;    public JDBCoracle10G_INVOKEFUNCTION()    {        driver = "oracle.jdbc.driver.OracleDriver";        url = "jdbc:oracle:thin:@localhost:1521:ORCL";        // oracle 用户        user = "test";        // oracle 密码        pwd = "test";        init();        // mysid:必须为要连接机器的sid名称,否则会包以下错:        // java.sql.SQLException: Io 异常: Connection        // refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))        // 参考连接方式:        // Class.forName( "oracle.jdbc.driver.OracleDriver" );        // cn = DriverManager.getConnection(        // "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );    }    publicvoid init() {        System.out.println("oracle jdbc test");        try {            Class.forName(driver);            System.out.println("driver is ok");            conn = DriverManager.getConnection(url, user, pwd);            System.out.println("conection is ok");            statement = conn.createStatement();            // conn.setAutoCommit(false);            // 输入参数            in_price = "5.0";            // 调用函数            stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}");            // stmt.registerOutParameter(1, java.sql.Types.FLOAT);            // stmt.registerOutParameter(2, java.sql.Types.CHAR);            stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);            stmt.setString(2, in_price);            stmt.executeUpdate();            // 取的结果集的方式一:            rs = ((OracleCallableStatement) stmt).getCursor(1);            // 取的结果集的方式二:            // rs = (ResultSet) stmt.getObject(1);            String ric;            String price;            String updated;            while (rs.next()) {                ric = rs.getString(1);                price = rs.getString(2);                updated = rs.getString(3);                System.out.println("ric:" + ric + ";-- price:" + price + "; --"                        + updated + "; ");            }        } catch (Exception e) {            e.printStackTrace();        } finally {            System.out.println("close ");        }    }    publicstaticvoid main(String args[])// 自己替换[]    {        new JDBCoracle10G_INVOKEFUNCTION();    }}

永利官网ylg客户端 5

 

 

 

六   ——2_____1:  错误编号异常

永利官网ylg客户端 6

永利官网ylg客户端 7

永利官网ylg客户端 8

永利官网ylg客户端 9

永利官网ylg客户端 10永利官网ylg客户端 11

  1 
  2 set seroutput on
  3 declare
  4  primary_iterant exception;/*定义野怪异常变量*/
  5  pragma exception_init(primary_iterant,-00001);/*关联错误号 和 异常变量名*/
  6 begin
  7   /*向dept表中插入一条与已有主键值重复的记录,以便引发异常*/
  8   insert into dept values(10,'rrr','rrr');
  9 exception
 10    when primary_iterant then /*若Oracle系列捕获到的异常为-000001异常*/
 11    dbms_output.put_line('主键不允许重复!'); /*输出异常描述信息*/
 12 end;
 13 /

View Code

永利官网ylg客户端 12

永利官网ylg客户端 13

 1 declare @my_cur cursor
 2 
 3 declare @id int, @name nvarchar(20)
 4 
 5 exec tb1_proc @my_cur output
 6 
 7 --open @cursor    -- @cursor already opened
 8 
 9 fetch next from @my_cur into @id, @name
10 
11 while(@@fetch_status=0)
12 
13   begin
14 
15     print '编号:' + convert(nvarchar,@id)
16 
17     print '姓名:' + @name
18 
19     print '......................'
20 
21     fetch next from @my_cur into @id, @name
22 
23   end

6.JAVA调用存储过程返回结果集

代码示例:JDBCoracle10G_INVOKEPROCEDURE.java

永利官网ylg客户端 14View
Code

永利官网ylg客户端 15

import java.sql.*;import oracle.jdbc.OracleCallableStatement;import oracle.jdbc.OracleTypes;/* 本例是通过调用oracle的存储过程来返回结果集: * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip */publicclass JDBCoracle10G_INVOKEPROCEDURE {    Connection conn = null;    Statement statement = null;    ResultSet rs = null;    CallableStatement stmt = null;    String driver;    String url;    String user;    String pwd;    String sql;    String in_price;    public JDBCoracle10G_INVOKEPROCEDURE()     {        driver = "oracle.jdbc.driver.OracleDriver";        url = "jdbc:oracle:thin:@localhost:1521:ORCL";        // oracle 用户        user = "test";        // oracle 密码        pwd = "test";        init();        // mysid:必须为要连接机器的sid名称,否则会包以下错:        // java.sql.SQLException: Io 异常: Connection        // refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))        // 参考连接方式:        // Class.forName( "oracle.jdbc.driver.OracleDriver" );        // cn = DriverManager.getConnection(        // "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );    }    publicvoid init() {        System.out.println("oracle jdbc test");        try {            Class.forName(driver);            System.out.println("driver is ok");            conn = DriverManager.getConnection(url, user, pwd);            System.out.println("conection is ok");            statement = conn.createStatement();            // conn.setAutoCommit(false);            // 输入参数            in_price = "3.0";            // 调用函数            stmt = conn.prepareCall("call P_GET_PRICE(?,?,?,?)");            stmt.registerOutParameter(1, java.sql.Types.FLOAT);            stmt.registerOutParameter(2, java.sql.Types.CHAR);            stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);            stmt.setString(4, in_price);            stmt.executeUpdate();            int retCode = stmt.getInt(1);            String retMsg = stmt.getString(2);            if (retCode == -1) { // 如果出错时,返回错误信息                System.out.println("报错!");            } else {                // 取的结果集的方式一:                rs = ((OracleCallableStatement) stmt).getCursor(3);                // 取的结果集的方式二:                // rs = (ResultSet) stmt.getObject(3);                String ric;                String price;                String updated;                // 对结果进行输出while (rs.next()) {                    ric = rs.getString(1);                    price = rs.getString(2);                    updated = rs.getString(3);                    System.out.println("ric:" + ric + ";-- price:" + price                            + "; --" + updated + "; ");                }            }        } catch (Exception e) {            e.printStackTrace();        } finally {            System.out.println("close ");        }    }    publicstaticvoid main(String args[])// 自己替换[]    {        new JDBCoracle10G_INVOKEPROCEDURE();    }}

永利官网ylg客户端 16

三     —— 2_____3:   %rowtype 类型

永利官网ylg客户端 17

永利官网ylg客户端 18

永利官网ylg客户端 19永利官网ylg客户端 20

  1 SQL> set serveroutput  on
  2 SQL> declare
  3   2       rowVar_emp emp%rowtype;/*定义能够储存emp表中一行数据的变量 rowVar_emp*/
  4   3  begin
  5   4    select * into rowVar_emp from emp where empno=7839 ;/*检索数据*/
  6   5      dbms_output.put_line( '雇员'||rowVar_emp.ename||'的职位是'||rowVar_emp.job||'、工资是'||rowVar_emp.sal);
  7   6
  8   7  end;
  9   8  /
 10 
 11 雇员KING的职位是PRESIDENT、工资是5000
 12 
 13 PL/SQL procedure successfully completed
 14 
 15 SQL>

View Code

永利官网ylg客户端 21

永利官网ylg客户端 22

 1 create proc tb1_proc (
 2 
 3 @cur cursor varying output
 4 
 5 )
 6 
 7 as
 8 
 9 begin
10 
11   set @cur=cursor for
12 
13   select * from tb1
14 
15 end
16 
17 open @cur

1.创建表:STOCK_PRICES

永利官网ylg客户端 23View
Code

--创建表格CREATETABLE STOCK_PRICES(    RIC VARCHAR(6) PRIMARYKEY,    PRICE NUMBER(7,2),    UPDATED DATE );

四    ——2:循环语句

永利官网ylg客户端 24

 注意:存储过程中创建游标后要打开

5.创建函数:

永利官网ylg客户端 25View
Code

永利官网ylg客户端 26

--创建函数:F_GET_PRICECREATEORREPLACEFUNCTION F_GET_PRICE(v_price INNUMBER)    RETURN PKG_PUB_UTILS.REFCURSORAS    stock_cursor PKG_PUB_UTILS.REFCURSOR;BEGINOPEN stock_cursor FORSELECT*FROM stock_prices WHERE price < span> v_price;    RETURN stock_cursor;END;

永利官网ylg客户端 27

</span>

三     —— 3: 定义变量和常量

永利官网ylg客户端 28

View Code

4.创建和存储过程:P_GET_PRICE

永利官网ylg客户端 29View
Code

永利官网ylg客户端 30

--创建存储过程CREATEORREPLACEPROCEDURE P_GET_PRICE(  AN_O_RET_CODE OUT NUMBER,  AC_O_RET_MSG  OUT VARCHAR2,  CUR_RET OUT PKG_PUB_UTILS.REFCURSOR,  AN_I_PRICE INNUMBER) ISBEGIN    AN_O_RET_CODE :=0;    AC_O_RET_MSG  :='操作成功';        OPEN CUR_RET FORSELECT*FROM STOCK_PRICES WHERE PRICE< span>AN_I_PRICE;EXCEPTION    WHEN OTHERS THEN        AN_O_RET_CODE :=-1;        AC_O_RET_MSG  :='错误代码:'|| SQLCODE || CHR(13) ||'错误信息:'|| SQLERRM;END P_GET_PRICE;

永利官网ylg客户端 31

</span>

永利官网ylg客户端,三     —— 1_____4:布尔类型

永利官网ylg客户端 32

View Code

在oracle下创建一个test的账户,然后按一下步骤执行:

三     —— 2_____2:  record  类型

永利官网ylg客户端 33

永利官网ylg客户端 34

永利官网ylg客户端 35

永利官网ylg客户端 36永利官网ylg客户端 37

  1 set serveroutput on  /**/
  2 declare
  3   type emp_type is record
  4    (
  5     var_ename varchar2(20),  /*定义字段--成员变量  */
  6     var_job varchar2(20),
  7     var_sal number
  8    );
  9    empinfo emp_type;  /*定义变量*/
 10 begin
 11   select ename,job,sal into empinfo from emp where empno=7839 ;/*检索数据*/
 12     dbms_output.put_line( '雇员'||empinfo.var_ename||'的职位是'||empinfo.var_job||'、工资是'||empinfo.var_sal);
 13 
 14 end;
 15 /

View Code

永利官网ylg客户端 38

永利官网ylg客户端 39永利官网ylg客户端 40

2.插入测试数据:

永利官网ylg客户端 41View
Code

--插入数据INSERTINTO stock_prices values('1111',1.0,SYSDATE);INSERTINTO stock_prices values('1112',2.0,SYSDATE);INSERTINTO stock_prices values('1113',3.0,SYSDATE);INSERTINTO stock_prices values('1114',4.0,SYSDATE);

一:前言

永利官网ylg客户端 42

View Code

二     ——  1: Pl/Sql块结构

永利官网ylg客户端 43

  1 【declare】
  2 --声明部分,可选
  3 begin
  4 --执行部分,必须
  5 [exception]
  6 --异常处理部分,可选
  7 end

永利官网ylg客户端 44永利官网ylg客户端 45

永利官网ylg客户端 46永利官网ylg客户端 47

  1 SQL> set serveroutput on;
  2 SQL>
  3 SQL> declare
  4   2  a int:=10;
  5   3  b int:=200;
  6   4  c number;
  7   5  begin
  8   6    c:=(a+b)/(a-b);
  9   7    dbms_output.put_line(c);
 10   8  exception
 11   9     when zero_divide then
 12  10       dbms_output.put_line('除数不许为零');
 13  11   end;
 14  12  /
 15 
 16 -1.10526315789473684210526315789473684211
 17 
 18 PL/SQL procedure successfully completed
 19 
 20 SQL>

View Code

永利官网ylg客户端 48

永利官网ylg客户端 49

创建返回游标的存储过程:

五    ——1_____3: 读取游标 

永利官网ylg客户端 50

永利官网ylg客户端 51

永利官网ylg客户端 52

永利官网ylg客户端 53永利官网ylg客户端 54

  1 
  2 set serveroutput on
  3 declare
  4   /*声明游标,检索雇员信息*/
  5   cursor cur_emp(var_job varchar2 := 'SALESMAN') is
  6     select empno, ename, sal from emp where job = var_job;
  7   type record_emp is record /*声明一个记录类型 record 类型*/
  8   (
  9     /*定义当前记录的成员变量*/
 10     var_empno emp.empno%type,
 11     var_ename emp.ename%type,
 12     var_sal   emp.sal%type);
 13   emp_row record_emp; /*声明一个record_emp 类型变量*/
 14 begin
 15   open cur_emp('MANAGER'); /*打开游标*/
 16   fetch cur_emp
 17     into emp_row; /*先让指针指向结果集中的第一行,并将值保存到emp_row 中*/
 18   while cur_emp%found loop
 19     dbms_output.put_line(emp_row.var_ename || '的编号是' || emp_row.var_empno ||
 20                          ',工资是' || emp_row.var_sal);
 21 
 22     fetch cur_emp
 23       into emp_row; /*让指针指向结果集的下一行,并将值保存到emp_row中*/
 24   end loop;
 25   close cur_emp; /*关闭游标*/
 26 end;
 27 /

View Code

永利官网ylg客户端 55

永利官网ylg客户端 56

创建表:

三     —— 2 :特殊数据类型

永利官网ylg客户端 57


二     —— 2_____2:多行注释

永利官网ylg客户端 58

永利官网ylg客户端 59永利官网ylg客户端 60

  1 set serveroutput on;      /*在服务器端 输出结果*/
  2  declare
  3    Num_sal number;
  4    Var_ename varchar2(20);
  5  begin
  6    /*检索指定的值并储存到变量中*/
  7       select e.ename,e.sal into Var_ename,Num_sal  from emp e where empno=7839;  --检索指定的值并储存到变量中
  8     dbms_output.put_line(Var_ename||'工资是'||Num_sal);
  9 end;
 10 /

View Code

永利官网ylg客户端 61

永利官网ylg客户端 62

永利官网ylg客户端 63永利官网ylg客户端 64

五    ——2: 游标的属性

永利官网ylg客户端 65

永利官网ylg客户端 66

永利官网ylg客户端 67永利官网ylg客户端 68

  1 set serveroutput on
  2 declare
  3    var_ename varchar2(50);/*声明变量,用来储存雇员名称*/
  4    var_job   varchar2(50);/*声明变量,用来储存雇员的职务*/
  5    /*声明游标,检索指定员工编号的雇员信息*/
  6   cursor cur_emp /*定义游标,检索指定编号的记录信息*/
  7    is select ename ,job from emp where empno=7499;
  8 begin
  9    open cur_emp;/*打开游标*/
 10    fetch cur_emp into var_ename,var_job ;/*读取游标,并且储存雇员名称和职务*/
 11      if cur_emp%found then /*若检索到数据记录,则输出雇员信息*/
 12        dbms_output.put_line('编号是7499的雇员名称为'||var_ename||',职务是:'||var_job);
 13       else
 14         dbms_output.put_line('无数据记录');/*提示无记录信息*/
 15       end if;
 16 end;
 17 /

View Code

永利官网ylg客户端 69

永利官网ylg客户端 70

 使用存储过程返回的游标:

五    —1:显示游标 

永利官网ylg客户端 71

永利官网ylg客户端 72永利官网ylg客户端 73

六   ——2_____2:   业务逻辑异常

永利官网ylg客户端 74

永利官网ylg客户端 75

永利官网ylg客户端 76永利官网ylg客户端 77

  1 set serveroutput on
  2 declare
  3     null_exception exception ;/*声明一个exception 类型的异常变量*/
  4     dept_row dept%rowtype;   /*声明rowtype 类型的变量 dept_now*/
  5 begin
  6    dept_row.deptno :=66; /*给部门编号变量赋值*/
  7    dept_row.dname := '公关部';/*给部门名称变量赋值*/
  8    insert into  dept values(dept_row.deptno,dept_row.dname,dept_row.loc);/*向dept表插入一条记录*/
  9    if dept_row.loc is null then
 10       raise null_exception; /*引发 null 异常 程序进入exception部分*/
 11    end if;
 12 exception
 13     when null_exception then    /*当 raise 引发的异常是 null_exception 时*/
 14         dbms_output.put_line('loc 字段的值不允许为null');     /*则输出异常提示信息*/
 15         rollback; /*回滚插入的数据记录*/
 16 end ;
 17 /

View Code

永利官网ylg客户端 78

发表评论

电子邮件地址不会被公开。 必填项已用*标注