oracle入门（6）——PL/SQL常用语法

浏览数：18 / 时间：2015年06月12日

【本文介绍】

本文不是”语法大全“，只是记录下作项目里自己常用的一些语法。方便查询。

【语法】

【输出】
（1）输出语法	DBMS_OUTPUT.PUT_LINE( )
【定义】
（1）定义变量：	...... as 变量名类型（长度） begin ...... 例如：
（2）定义变量，类型依赖其他变量的类型	例如：这样，改了name的类型，returnValue类型也跟着改变。
（3）自定义类型（类似C语言的结构体）每次只能拿一条数据，不然会的报错。	例如：
（4）以“表”做为类型。每次只能拿一条数据，不然会的报错。	例如：
（5）以“表”做为类型的数组，数组下标为int型。每次只能拿一条数据，不然会的报错。
（6）以“表”做为类型的数组，数组下表为字符型。每次只能拿一条数据，不然会的报错。	CREATE OR REPLACE PROCEDURE "userList" AS TYPE user_type_list is table OF "user"%rowtype --索引表类型 index by VARCHAR2(20); myUserList user_type_list; BEGIN SELECT * into myUserList(‘one‘) from "user" WHERE "user"."id" = ‘1‘; DBMS_OUTPUT.PUT_LINE(myUserList(‘one‘)."id"); DBMS_OUTPUT.PUT_LINE(myUserList(‘one‘)."name"); DBMS_OUTPUT.PUT_LINE(myUserList.first); END;
（7）VARCHAR变长数组	CREATE OR REPLACE PROCEDURE "userList" AS TYPE user_type_list --数组名 is varray(100) OF --长度 "user"%rowtype; --类型 myUserList user_type_list := user_type_list(); --定义 BEGIN myUserList.EXTEND; --分配存储空间，相当于C语言里面的malloc SELECT * into myUserList(1) from "user" WHERE "user"."id" = ‘1‘; DBMS_OUTPUT.PUT_LINE(myUserList(1)."id"); DBMS_OUTPUT.PUT_LINE(myUserList(1)."name"); END;
【赋值】
（1）赋值	变量名：= 值例如：
（2）查询并赋值
【控制语句】
（1）if语句	CREATE OR REPLACE PROCEDURE "userList" AS myResult NUMBER(10,4); --变量 BEGIN myResult := SQRT(58+253+(19-9)*2);--数值表达式 IF myResult > 10 THEN --if语句 DBMS_OUTPUT.PUT_LINE(‘结果大于10‘); ELSE IF myResult > 15 then -- else if语句 DBMS_OUTPUT.PUT_LINE(‘结果大于15‘); END IF; --结束else if END IF; --结束if END;
（2）case语句	CREATE OR REPLACE PROCEDURE "userList" AS myCase VARCHAR2(10) := ‘A‘; --变量 BEGIN CASE myCase WHEN ‘A‘ THEN DBMS_OUTPUT.PUT_LINE(‘A‘); WHEN ‘B‘ THEN DBMS_OUTPUT.PUT_LINE(‘B‘); ELSE DBMS_OUTPUT.PUT_LINE(‘什么也不匹配‘); END CASE; END;
（3）循环语句	CREATE OR REPLACE PROCEDURE "userList" AS myNumber number := 1; --变量 BEGIN LOOP --循环开始的标志 if myNumber < 5 THEN --逻辑部分 DBMS_OUTPUT.PUT_LINE(‘myNumber的值为：‘\|\|myNumber); myNumber := myNumber+1; ELSE EXIT ; --一定要有退出语句 END IF; END LOOP; --结束循环 END; 或 CREATE OR REPLACE PROCEDURE "userList" AS myNumber number := 1; --变量 BEGIN LOOP --循环开始的标志 if myNumber < 5 THEN --逻辑部分 DBMS_OUTPUT.PUT_LINE(‘myNumber的值为：‘\|\|myNumber); myNumber := myNumber+1; END IF; EXIT WHEN myNumber >= 5; --eixt when 语句 END LOOP; --结束循环 END; 或： CREATE OR REPLACE PROCEDURE "userList" AS myNumber number := 1; --变量 BEGIN FOR inx IN 1..4 --for loop语句 LOOP --循环开始的标志 DBMS_OUTPUT.PUT_LINE(‘myNumber的值为：‘\|\|myNumber); myNumber := myNumber+1; END LOOP; --结束循环 END;
【异常】
异常处理
注释	单行： -- 多行：/* */
转义字符
【常用函数】
数值表达式	CREATE OR REPLACE PROCEDURE "userList" AS myResult NUMBER(10,4); --变量 BEGIN myResult := SQRT(58+253+(19-9)*2);--数值表达式 DBMS_OUTPUT.PUT_LINE(myResult); END;
字符切割函数	/ 用pipe函数实现字符串分割 / CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000); / CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2) RETURN ty_str_split PIPELINED IS j INT := 0; i INT := 1; len INT := 0; len1 INT := 0; str VARCHAR2 (4000); BEGIN len := LENGTH (p_str); len1 := LENGTH (p_delimiter); WHILE j < len LOOP j := INSTR (p_str, p_delimiter, i); IF j = 0 THEN j := len; str := SUBSTR (p_str, i); PIPE ROW (str); IF i >= len THEN EXIT; END IF; ELSE str := SUBSTR (p_str, i, j - i); i := j + len1; PIPE ROW (str); END IF; END LOOP; RETURN; END fn_split;
转义字符	cgar(47) 即代表： /
数组长度函数	len := LENGTH (p_str);
产生随机数	substr(DBMS_RANDOM.VALUE(10,24),1,2)
生成日期	select to_char(sysdate,‘yyyy-mm-dd-hh24-mi-ss‘) into mydate from dual;
UUID	select sys_guid() into myUser."id" from dual;