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 isbegin 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) isbegin 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 numberSQL» 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 ...) --只能是子查询语句