Oracle PL/SQL开发学习笔记
基本知识
- 脚本中的斜线“/”的作用是什么：用于隔断DDL语句
- 长度为0 的字符串作为null处理：'str' || null、'str' || '' 这两个表达式的值均为'str'
- 布尔类型 TURE FALSE NULL 可以有三个值，布尔型只能用于计算，不能插入表
- GOTO语句不能跳到IF、LOOP语句或者子块，不能跳出子程序，不能从异常处理跳到当前块
- 存储过程调用命令：exec。代码里面不需要写exec。在java代码里面使用这种方式：{call procedure_name ...}
- 异常传播机制类似JAVA，向上级块传播
PL/SQL语法
以下代码示意PL/SQL的语法|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 |
/* 多行注释 */ DECLARE -- 声明变量：变量名由 0-9A-Za-z # $ _ 组成，最大30个字符。可以带双引号，以包含其他字符 V_NAME VARCHAR2(32); -- 声明常量，赋初始值 V_ORG_CODE CONSTANT NUMBER(8) := 876; -- 定义子类型（自定义类型），不能指定长度和精度 SUBTYPE MY_TYPE IS NUMBER; -- 定义记录类型 TYPE USER_RECORD ( NAME VARCHAR2(32) := '''' || '"', --字符串连接 ID NUMBER(21) NOT NULL := 6.02E23, GENDER NUMBER(1) NOT NULL DEFAULT 1, STAFF_CODE R$USER.STAFF_CODE%TYPE ) -- 声明记录类型变量 V_USERS USER_RECORD; --定义表类型，表是类似于数组的（indexed）结构。index by 说明其索引（下标）的类型 TYPE NAME_TABLE IS TABLE OF R$USER%ROWTYPE INDEX BY BINARY_INTEGER; -- 声明表类型变量 V_USER_TAB NAME_TABLE; -- 声明显式游标，可以绑定参数 CURSOR C_USER(P_ID R$USER.USER_ID%TYPE) IS SELECT ID,STAFF_CODE,USER_NAME FROM R$USER WHERE ORG = 876 AND P_ID != P_ID; --定义异常类型 E_AUTH_ERR EXCEPTION; --异常初始化的编译指令（对应oracle错误号 ora-01401） PRAGMA EXCEPTION_INIT(E_AUTH_ERR ,-1401) V_LOOP_COUNT BINARY_INTEGER := 1; BEGIN -- 命名的子块 <<SUB_BLOCK>> DECLARE V_VAR NUMBER DEFAULT 12; BEGIN SUB_BLOCK.V_VAR := 0; -- 可以用这种方式引用父块中的变量 END SUB_BLOCK; -- 表类型元素赋值 V_USER_TAB(-3).USER_NAME := 'WangZhen'; -- 表属性 V_USER_TAB.COUNT;-- 返回总数 V_USER_TAB.DELETE; -- 删除全部 V_USER_TAB.DELETE(i); -- 删除指定 V_USER_TAB.DELETE(i,j); -- 删除范围，包含i j V_USER_TAB.EXIST(i); -- 是否存在 V_USER_TAB.FIRST; V_USER_TAB.LAST; V_USER_TAB.NEXT(i); V_USER_TAB.PRIOR(i); -- select into 赋值 SELECT USER_NAME INTO V_NAME FROM R$USER WHERE ID = 10000; -- IF-ELSE IF -ELSE结构 IF V_NAME = 'ADMIN' THEN NULL; ELSIF V_NAME IS NULL THEN NULL; ELSE NULL; END IF; -- LOOP -EXIT WHEN循环 LOOP INSERT INTO TAB0 VALUES( V_LOOP_COUNT ); V_LOOP_COUNT := V_LOOP_COUNT + 1; EXIT WHEN V_LOOP_COUNT > 100; IF V_LOOP_COUNT > 100 THEN EXIT; END IF; END LOOP; -- WHILE - LOOP循环 WHILE V_LOOP_COUNT <= 100 LOOP END LOOP; -- FOR循环 FOR V_COUNT IN 1..100 LOOP INSERT INTO TAB0 VALUES( V_LOOP_COUNT ); END LOOP; -- 带标号的循环 <<OUTER>> LOOP LOOP OUTER.variable := 1; IF OUTER.variable = 1 THEN GOTO PRINT; END IF; END LOOP; <<PRINT>> END LOOP OUTER; -- 操作显式游标，FETCH用于推进游标。%FOUND %NOTFOUND %ISOPEN %ROWCOUNT表示已经读取的行数 OPEN C_USERS(10000); LOOP FETCH C_USERS INTO V_ID,V_STAFF_CODE,V_USER_NAME; -- 注意，fetch后面只能跟游标的名称 FETCH C_USERS INTO V_RECORD; EXIT WHEN C_USERS%NOTFOUND; END LOOP; CLOSE C_USERS; -- 显式游标FOR循环，不用打开、关闭。V_USER不需要显式声明 FOR V_USER IN C_USERS LOOP DELETE FROM R$USER WHERE CURRENT OF C_USERS ; -- 更新当前游标（需要for update） END LOOP; -- 隐式声明显式游标 FOR V_USER IN (SELECT * FROM R$USER) LOOP NULL; END LOOP; -- 隐式游标（SQL游标） 可以访问属性 %FOUND %NOTFOUND %ROWCOUNT UPDATE R$USER SET USER_NAME = 'admin' WHERE ID = 10000; V_EXIST := SQL%FOUND; -- 抛出异常(内置或者已经声明) RAISE E_AUTH_ERR; EXCEPTION WHEN NO_DATA_FOUND THEN -- 抛出异常(未声明的) 错误号在-20000 到 -20999，错误文本最大512字符 RAISE_APPLICATION_ERROR(-20001,'ERROR MESSAGE!'); DBMS_OUTPUT.PUT_LINE('ERROR!!'); -- select into 返回0行时抛出 WHEN TOO_MANY_ROWS THEN v_errcode := SQLCODE; v_errmsg := SQLERRM; -- select into 返回多行时抛出 WHEN OTHERS THEN NULL; END; -- 存储过程声明语法 CREATE OR REPLACE PROCEDURE my_procedure ( -- 参数不能指定精度或者长度 p_name in varchar2, p_staff_code in out varchar2 ) IS v_var number; BEGIN NULL; END my_procedure; -- 存储过程调用语法 my_procedure( v_name,-- 位置传参方式 p_staff_code => v_staff_code -- 名称传参方式 ); -- 存储函数：可以作为一个表达式调用，没有参数，则声明和定义都不需要()括号 CREATE OR REPLACE FUNCTION my_func ( -- 参数不能指定精度或者长度 p_name in varchar2, p_staff_code in out varchar2 ) RETURNS number IS v_var number; BEGIN NULL; END my_func; |
常用代码样板
伪随机函数
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- DBMS_RANDOM包含若干与随机数相关的函数 -- 生成一个随机数 SELECT ABS(MOD(DBMS_RANDOM.RANDOM, 3600)) FROM DUAL -- 随机字符串和随机数字 BEGIN FOR V_COUNT IN 1 .. 1000 LOOP INSERT INTO PFMS_TEST ( ID, NAME , AGE) VALUES (WSO2_SEQ.NEXTVAL, DBMS_RANDOM.STRING( 'A', 100 ), TRUNC(DBMS_RANDOM.VALUE(0 , 100))); END LOOP; COMMIT; END; |
Leave a Reply