oracle的plsql笔记

一 代码块
' set serveroutput on size 1000000
可以在控制台看到输出
要这样写
DBMS_OUTPUT.PUT_LINE('Division by zero');

'

' 捕获异常
###################################################
EXCEPTION
WHEN ZREO_DIVIDE THEN
###################################################
这里是被除者为0的异常

' 代码块
有以下部分
###################################################
DECLARE

BEGIN

END;
/
###################################################
后面的斜线/ 标识运行PLSQL
END和其他statement一样需要分号

' 一个简单的例子
不用输入任何变量 从给的的高求矩形的高
###################################################
DECLARE
width INTEGER;
height INTEGER := 2;
area INTEGER;
BEGIN
area := 6;
width := area / height;
DBMS_OUTPUT.PUT_LINE('width = ' || width);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Division by zero');
END;
/
###################################################
将输出
oralen@ORCL> @001
width = 3

PL/SQL 过程已成功完成。

oracle plsql的包和类型 以及使用查看PL/SQL Packages and Types Reference章节
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/toc.htm

二 变量和类型
' 在declare段声明的变量只能在块内部使用

' 声明类型 第二个是直接赋值
DECLARE
width INTEGER;
height INTEGER := 2;
area INTEGER;

' %TYPE关键字
这个关键字告诉PLSQL使用与表中指定列相同的类型
例如
product_price products.price.%TYPE;

三 条件逻辑

' 关键字
IF condition1 THEN
statements1
ELSEIF contition2 THEN
statements2
ELSE
statements3
END IF;
里面可以嵌套

四 循环

' 简单循环
LOOP
statements
END LOOP;

要结束循环用
EXIT立即结束 或者EXIT WHEN 在指定条件下结束
一个例子
testloop.sql
###################################################
DECLARE
counter INTEGER := 0;
BEGIN
LOOP
counter := counter + 1;
DBMS_OUTPUT.PUT_LINE('counter = ' || counter );
EXIT WHEN counter = 5;
END LOOP;
END;
/
###################################################

' WHILE循环
WHILE condition LOOP
statements
END LOOP;

testwhile.sql
###################################################
DECLARE
counter INTEGER := 0;
BEGIN
WHILE counter < 5 LOOP counter := counter + 1; DBMS_OUTPUT.PUT_LINE('counter = ' || counter ); END LOOP; END; / ################################################### ' FOR 循环 for循环会运行预先设定的次数 FOR loop_variable IN [REVERSE] lower_bound.. upper-bound LOOP statements END LOOP; loop_variable循环所使用的变量 REVERSE 关键字是减少 不使用就是默认增加 lower_bound 指定循环的下限 upper-bound 指定循环的上限 例子 testfor.sql ################################################### BEGIN FOR counter IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE('counter = ' || counter ); END LOOP; END; / ################################################### 上面的例子如果使用REVERSE 关键字 则变成递减 五 游标 1 声明用于保存列值的变量 可以用%TYPE来声明和表里的列一样的类型 DECLARE v_product_id products.product_id%TYPE; v_name products.name%TYPE; v_price products.price%TYPE; 2 声明游标 放在declare里 后面加上要查询的statements CURSOR cursor_name IS SELECT_statements; 3 打开游标 OPEN cursor_name; 4 从游标中取记录 FETCH cursor_name INTO variable [, variable ...]; 游标可能包含多条记录,所以要通过循环体来读取每一条记录 确定循环的结束可以用 EXIT WHEN cursor_name%NOTFOUND 来结束循环体 5 关闭游标 CLOSE cursor_name 一个完整的示例 testcursor.sql ################################################### DECLARE v_sys_id T_UNIVERSITY.SYS_ID%TYPE; v_cname T_UNIVERSITY.CNAME%TYPE; v_ename T_UNIVERSITY.ENAME%TYPE; v_state T_UNIVERSITY.STATE%TYPE; v_nation T_UNIVERSITY.NATION%TYPE; CURSOR c_university_cursor IS SELECT SYS_ID,CNAME,ENAME,STATE,NATION FROM T_UNIVERSITY; BEGIN OPEN c_university_cursor; LOOP FETCH c_university_cursor INTO v_sys_id, v_cname, v_ename, v_state, v_nation; EXIT WHEN c_university_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE( 'v_sys_id=' || v_sys_id || ', v_cname=' || v_cname || ', v_ename=' || v_ename || ', v_state=' || v_state || ', v_nation=' || v_nation ); END LOOP; CLOSE c_university_cursor; END; / ################################################### 用for循环写不用声明变量 不用open和close游标 下面是for循环版的 test4cursor.sql ################################################### DECLARE CURSOR c_university_cursor IS SELECT SYS_ID,CNAME,ENAME,STATE,NATION FROM T_UNIVERSITY; BEGIN FOR v_university IN c_university_cursor LOOP DBMS_OUTPUT.PUT_LINE( 'v_sys_id=' || v_university.SYS_ID || ', v_cname=' || v_university.CNAME || ', v_ename=' || v_university.ENAME || ', v_state =' || v_university.STATE || ', v_nation=' || v_university.NATION ); END LOOP; END; / ################################################### 要简洁许多 六 包 创建包 含有过程的包 示例 ################################################### --用户基本信息工具包 声明 CREATE OR REPLACE PACKAGE USERBASE_PACKAGE AS PROCEDURE init_user_baseinfo ( -- 用户的id 和头像的系统id 和 时间 好友分组 user_sys_id IN VARCHAR2, user_img_sys_id IN VARCHAR2, create_date IN VARCHAR2, friend_group IN VARCHAR2, max_head_url IN VARCHAR2, min_head_url IN VARCHAR2 ); END USERBASE_PACKAGE; / --用户基本信息工具包 包体 CREATE OR REPLACE PACKAGE BODY USERBASE_PACKAGE AS -- 定义过程 创建用户的时候初始化所有的表 PROCEDURE init_user_baseinfo ( -- 用户的id 和头像的系统id 和 时间 好友分组 user_sys_id IN VARCHAR2, user_img_sys_id IN VARCHAR2, create_date IN VARCHAR2, friend_group IN VARCHAR2, max_head_url IN VARCHAR2, min_head_url IN VARCHAR2 ) AS -- 这里可以声明参数 BEGIN -- 插入一个空的用户头像数据 INSERT INTO T_USER_PROFILE(SYS_ID, USER_ID, HEADIMGNOR_URL, HEADIMGMIN_URL, STATE) VALUES (user_img_sys_id, user_sys_id, max_head_url, min_head_url, '1'); -- 插入一个用户公开设置的数据 INSERT INTO T_USER_SETTING(SYS_ID, USER_ID, UPDATE_DATE) VALUES (SYS_GUID(), user_sys_id, to_date(create_date, 'yyyy-MM-dd HH24:mi:ss')); -- 插入用户的好友数据 INSERT INTO T_FRIEND(SYS_ID, USER_ID, FRIENDS, UPDATE_DATE) VALUES (SYS_GUID(), user_sys_id, empty_clob(), to_date(create_date, 'yyyy-MM-dd HH24:mi:ss')); -- 插入用户好友分组 INSERT INTO T_FRIEND_GROUP(SYS_ID, USER_ID, FRIENDS_GROUP, UPDATE_DATE) VALUES (SYS_GUID(), user_sys_id, friend_group, to_date(create_date, 'yyyy-MM-dd HH24:mi:ss')); -- 插入blog分类 INSERT INTO T_BLOG_GROUP (SYS_ID, USER_ID) VALUES (SYS_GUID(), user_sys_id); EXCEPTION WHEN OTHERS THEN ROLLBACK; END init_user_baseinfo; END USERBASE_PACKAGE; / ################################################### java调用 Connection connection = null; CallableStatement cstmt = null; String sql= "{call USERBASE_PACKAGE.init_user_baseinfo(?,?,?,?,?,?)}"; String sysid = getNewUUID(); try { connection = getConnection(2); connection.setAutoCommit(false); cstmt = connection.prepareCall(sql); cstmt.setString(1, userid); cstmt.setString(2, sysid); cstmt.setString(3, DateUtil.getOracleDateString()); cstmt.setString(4, friendGroupTemplate.newUserGroup()); cstmt.setString(5, maxHead); cstmt.setString(6, minHead); cstmt.execute(); connection.commit(); connection.setAutoCommit(true); } catch (SQLException e) { log.error("初始化用户头像 用户公开设置初始化 用户好友初始化 好友分组初始化 , 调用存储过程数据库异常", e); } finally { this.release(2, null, cstmt, connection); }