IT Notes‎ > ‎Database‎ > ‎Oracle‎ > ‎

Oracle PL/SQL Programming

@draft

Oracle PL/SQL 是 Oracle 查询中相对高阶的内容,对提供生产效率有很大意义。直观上解释,Oracle PL/SQL 即对普通的 SQL 加上能控制过程的一些功能,扩展了基本的 SQL.

存储过程举例

例一:插入一条数据

1. 创建一个简单的表
create table mytest(name varchar2(30), passwd varchar2(30));
2. 创建过程(插入一条数据)
create or replace procedure sp_pro1 is
begin
  insert into mytest values('罗永浩', 'dafadfaf');
end;
3. 调用过程
exec sp_pro1;
 

例二:删除数据

SQL» create or replace procedure deletelyh is
  2  begin
  3      delete from mytest where passwd='dafadfaf';
  4  end;
  5  /
 
Procedure created
 
SQL» exec deletelyh;
 
PL/SQL procedure successfully completed
 

最简单的块

SQL» set serveroutput on;
SQL» begin
  2  dbms_output.put_line('Hello, world!');
  3  end;
  4  /
 
Hello, world!
 
PL/SQL procedure successfully completed
 

带有声明的块

declare
  v_ename varchar2(20);
begin
  select ename into v_ename from emp where empno = &no;
  dbms_output.put_line('雇员名: ' || v_ename);
end;
 

稍微复杂一点块

declare
  v_ename varchar2(20);
  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);
end;
 

块中的标量

案例一:通过员工好查找姓名,工资,所得税(税率是0.03) 
declare 
  c_tax_rate number(3,2) := 0.03; -- 块中的标量
  v_ename    varchar2(10); 
  v_sal      number(7, 2); 
  v_tax_sal  number(7, 2); 
begin 
  select ename, sal, sal * c_tax_rate 
    into v_ename, v_sal, v_tax_sal 
    from emp4 
   where empno = &no; 
   -- 注意:要让下面的语句执行,记得之前设置参数:set serveroutput on 
  dbms_output.put_line('Name: ' || v_ename); 
  dbms_output.put_line('Salary: ' || v_sal); 
  dbms_output.put_line('Tax: ' || v_tax_sal); 
           end;   

复合变量

declare 
--定义一个记录 
  type emp_record_type is record( 
    ename  emp4.ename%type, -- 留意此处需用逗号 
    salary emp4.sal%type, 
    title  emp.job%type); 
  sp_record emp_record_type; 
begin 
  select ename, sal, job into sp_record from emp4 where empno = &no; 
  dbms_output.put_line('Name: ' || sp_record.ename); 
  dbms_output.put_line('Salary: ' || sp_record.salary); 
  dbms_output.put_line('Title: ' || sp_record.title); 
end; 

异常

抛出异常
declare
  v_ename varchar2(20);
  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;
 

调用存储过程举例

普通调用

举例:写一个过程,输入雇员名和工资,修改该雇员的工资。
create or replace procedure hello001(v_name varchar2, v_sal number) is
begin
  update emp4 set sal = v_sal where ename = v_name; 
  commit;
end;
-- 以上语句中的表 emp4 来源于:create table scott.emp4 as select * from scott.emp
调用:
exec hello001('SCOTT', 1234) 或者:call hello001('SCOTT', 2345.67); 
-- 这里需要注意,值是大小写敏感的,如果写成了 'Scott',虽然该存储过程也调用成功了,但不会修改表中的任何值。
 

Java 调用过程

Class.forName("oracle.jdbc.driver.OracleDriver"); 

// 2. 取得连接 
Connection ct = DriverManager .getConnection("jdbc:oracle:thin:@172.168.1.104:1521:iridium", "scott", "hello"); 
CallableStatement cs = ct.prepareCall("{call hello001(?, ?)}"); 
cs.setString(1, "SCOTT"); 
cs.setDouble(2, 4456.78); 
cs.execute(); 
       

函数

定义:

create or replace function annual_income(helloName varchar2) return number is
  annual_salary number(7, 2);
begin
  select sal * 12 + nvl(comm, 0)
    into annual_salary
    from emp4
   where ename = helloName;
  return annual_salary;
end;
 
在SQL中调用:
SQL» var income number
SQL» call annual_income('SCOTT') into :income;
SQL» print income;
-- 如果 sal 的值太大,比如 sal = 12345.67, 则 12*sal 的值将超出 annual_salary 可以表达的范围,调用该函数,将出现报错。
-- ORA-06502: PL/SQL: 数字或值错误 :  数值精度太高
-- ORA-06512: 在 "SCOTT.ANNUAL_INCOME", line 4
 
Java 中的调用:
select annual_income('SCOTT') from dual;
可以通过 rs.getInt(1) 得到返回结果。
 

create package ps_package is
  procedure hello001(v_name varchar2, v_sal number);
  function annual_income(helloName varchar2) return number;
end;
 
create or replace package body ps_package is
  --分别写上 procedure 和 function 的定义就可以了,但不要再带 create or replace 这句话了。
  procedure hello001(v_name varchar2, v_sal number) is
  begin
    update emp4 set sal = v_sal where ename = v_name;
  end; 

  function annual_income(helloName varchar2) return number is
    annual_salary number(7, 2);
  begin
    select sal * 12 + nvl(comm, 0)
      into annual_salary
      from emp4
     where ename = helloName;
    return annual_salary;
  end;
end;
 





declare
-- 可以理解为:sp_table_type 是一个 emp4.ename%type 类型元素组成的数组
-- index by binary_integer 表示下标是整数
  type sp_table_type is table of emp4.ename%type index by binary_integer;
  sp_table sp_table_type;
begin
  select ename into sp_table(0) from emp4 where empno = &no;
  dbms_output.put_line('Name: ' || sp_table(0));
end;
 
--------------改写,去掉 where 条件
declare
  type sp_table_type is table of emp4.ename%type index by binary_integer;
  sp_table sp_table_type;
begin
  select ename into sp_table from emp4;
  dbms_output.put_line('Name: ' || sp_table);
end;
 
--根据部门号,显示员工信息
declare
  --定义游标类型
  type sp_emp_cursor is ref cursor;
  --定义一个游标变量
  test_cursor sp_emp_cursor;
  --定义变量
  v_ename emp4.ename%type;
  v_sal   emp4.sal%type;
begin
  open test_cursor for
    select ename, sal from emp4 where deptno = &no;
  --循环取出
  loop
    fetch test_cursor
      into v_ename, v_sal;
    --判断cursor是否为空?
    exit when test_cursor%notfound;
    dbms_output.put_line('Name ' || v_ename);
    dbms_output.put_line('Sal ' || v_sal);
  end loop;
end;
 
-- 输入员工名,如果工资低于2000,就上调10%
create or replace procedure hello003(spName varchar2) is
v_sal emp4.sal%type;
begin
  select sal into v_sal from emp4 where ename = spName;
  if v_sal « 2000 then
    update emp4 set sal = sal * 1.1 where ename = spName;
  end if;
end;
 
-- 输入雇员名,如果补助为零就设置为200,如果不为零就增加100
create or replace procedure hello004(spName varchar2) is
  v_comm emp4.comm%type;
begin
  select nvl(comm, 0) into v_comm from emp4 where ename = spName;
  if v_comm = 0 then
    update emp4 set comm = 200 where ename = spName;
  else
    update emp4 set comm = comm + 100 where ename = spName;
  end if;
end;
 
-- 输入雇员编号,如果是president就加1000,是manager加500,其他加200
create or replace procedure hello005(spNum number) is
  v_job emp4.job%type;
begin
  select job into v_job from emp4 where empno = spNum;
  if v_job = 'PRESIDENT' then
    update emp4 set sal = sal + 1000 where empno = spNum;
    elsif v_job = 'MANAGER' then update emp4 set sal = sal + 500 where empno = spNum;
  else
    update emp4 set sal = sal + 200 where empno = spNum;
  end if;
end;
 
-- 输入用户名,并循环添加10个用户到 users 表中,用户编号从1开始增加
create or replace procedure hello006(spName varchar2) is
  v_num number := 1;
begin
  loop
    insert into users1 (userno, username) values (v_num, spName);
    exit when v_num = 10;
    v_num := v_num + 1;
  end loop;
end;
 
-- 输入用户名,并循环添加10个用户到 users 表中,用户编号从11开始增加
-- 用 while 循环
create or replace procedure hello007(spName varchar2) is
  v_num number := 11;
begin
  while v_num «= 20 loop
    insert into users1 (userno, username) values (v_num, spName);
    v_num := v_num + 1;
  end loop;
end;
 
--分页
-- table BOOK
create table book(bookId number(20), bookName varchar2(50), publishHouse varchar2(50))
 
-- 编写过程
create or replace procedure hello008(spBookId       in number,
                                     spBookName     in varchar2,
                                     spPublishHouse in varchar2) is
begin
  insert into book
    (bookId, bookName, publishHouse)
  values
    (spBookId, spBookName, spPublishHouse);
end;
 
-- 在 Java 中调用该过程
 
----------
有返回值的存储过程
--输入雇员编号,返回雇员信息
create or replace procedure hello009(spNum in number, spName out varchar2) is
begin
  select ename into spName from emp4 where empno = spNum;
end;
 
--输入雇员编号,返回雇员信息之二
create or replace procedure hello010(spNum  in number,
                                     spName out varchar2,
                                     spSal  out number,
                                     spJob  out varchar2) is
begin
  select ename, sal, job
    into spName, spSal, spJob
    from emp4
   where empno = spNum;
end;
 
--输入雇员号,返回结果集
-- 1. 创建包
create or replace package testpackage as
  type test_cursor is ref cursor;
end testpackage;
 
-- 2. 创建过程
create or replace procedure hello011(spNo     in number,
                                     p_cursor out testpackage.test_cursor) is
begin
  open p_cursor for
    select * from emp4 where deptno = spNo;
end;
 
-- 3. 在 Java 中调用
 
 ..............

例外处理
declare
  v_ename emp.ename%type;
begin
  select ename into v_ename from emp where empno = &no;
  dbms_output.put_line('Name: ' || v_ename);
exception
  when no_data_found then
    dbms_output.put_line('木有这个编号');
end;
 
  --自定义例外
create or replace procedure hello013(var_empno in number) is
  myex exception;
begin
  update emp4 set sal = sal + 1000 where empno = var_empno;
  if sql%notfound then
    raise myex;
  end if;
exception
  when myex then
    dbms_output.put_line('没有更新任何用户');
end;

定义记录类型,
type user_record is record (
name varchar2(20),
sex number(1)
);
 
对象,
create or replace type userObject as object (
name varchar2(21),
sex number(1)
);
 
常量
sex_male constant int:=1
sex_femal constant int:=0

==============以下好像有点问题:
-- Create table
create table USERS
(
  USERID   NUMBER primary key,
  USERNAME VARCHAR2(20),
  PASSWD   VARCHAR2(20),
  EMAIL    VARCHAR2(100),
  GRADE    NUMBER
)
 
-- Create/Recreate primary, unique and foreign key constraints  
alter table USERS
  add primary key (USERID)
  using index;
 
======================
Name     Type          Nullable Default Comments  
-------- ------------- -------- ------- --------  
USERID   NUMBER                                   
USERNAME VARCHAR2(20)  Y                          
PASSWD   VARCHAR2(20)  Y                          
EMAIL    VARCHAR2(100) Y                          
GRADE    NUMBER        Y                          
 
create or replace procedure additemstousers(username varchar2) is
  v_userid number := 20;
begin
  loop
    insert into users
      (userid, username, passwd, email, grade)
    values
      (v_user_id,
       username || to_char(v_user_id),
       username,
       username || to_char(v_user_id) || '@gmail.com',
       5);
    exit when v_userid = 200000;
    v_userid := v_userid + 1;
  end loop;
end;
 
Your Host: ACA80165.ipt.aol.com  
pw.println("«br/» Your Host: " + request.getLocalName());
Warning: Procedure created with compilation errors
 
Errors for PROCEDURE SCOTT.ADDITEMSTOUSERS:
 
LINE/COL ERROR
-------- ---------------------------------
11/28    PL/SQL: ORA-00984: 列在此处不允许
5/5      PL/SQL: SQL Statement ignored
 
===================
可以,比如表D中有三个字段:NUM,   NO,Q
update   d   set   no= '1111111 ',   q= '1111111 '   where   num=1;
commit;
 
还可以这样:
update   tablename   set   (col1,col2,...coln)   =   (select   ...   from   ...)   --只能是子查询语句
Comments