PLSQL入门

一、条件控制语句
IF condition THEN 
   S;
END IF;
------------------------------------------------------------------------------------------------------------------------
IF condition THEN
   S1; 
ELSE 
   S2;
END IF;
------------------------------------------------------------------------------------------------------------------------
IF(boolean_expression 1)THEN 
   S1; -- Executes when the boolean expression 1 is true 
ELSIF( boolean_expression 2) THEN
   S2;  -- Executes when the boolean expression 2 is true 
ELSIF( boolean_expression 3) THEN
   S3; -- Executes when the boolean expression 3 is true 
ELSE 
   S4; -- executes when the none of the above condition is true 
END IF;
------------------------------------------------------------------------------------------------------------------------
CASE selector
    WHEN 'value1' THEN S1;
    WHEN 'value2' THEN S2;
    WHEN 'value3' THEN S3;
    ...
    ELSE Sn;  -- default case
END CASE;
------------------------------------------------------------------------------------------------------------------------
CASE
    WHEN selector = 'value1' THEN S1;
    WHEN selector = 'value2' THEN S2;
    WHEN selector = 'value3' THEN S3;
    ...
    ELSE Sn;  -- default case
END CASE;






二、循环控制
LOOP
  Sequence of statements;
If(s) then exit;end if;
If(s) then continue ;end if;
END LOOP;
------------------------------------------------------------------------------------------------------------------------
WHILE condition LOOP
   sequence_of_statements
END LOOP;
------------------------------------------------------------------------------------------------------------------------
FOR counter IN initial_value .. final_value LOOP
   sequence_of_statements;
END LOOP;




三、字符串函数


S.N. 函数及用途
1 ASCII(x);        返回字符 x 的 ASCII 值
2 CHR(x);        返回字符 x 的 ASCII 值
3 CONCAT(x, y);        连接字符串x和y，并返回附加的字符串
4 INITCAP(x);        每个单词的首字母x中转换为大写，并返回该字符串
5 INSTR(x, find_string [, start] [, occurrence]);        搜索find_string在x中并返回它出现的位置
6 INSTRB(x);         返回另一个字符串中字符串的位置，但返回以字节为单位的值
7 LENGTH(x);         返回x中的字符数
8 LENGTHB(x);         返回为单字节字符集的字节的字符串的长度
9 LOWER(x);         在x转换为小写字母，并返回该字符串
10 LPAD(x, width [, pad_string]) ;         X用空格向左填充，把字符串的总长度达宽字符
11 LTRIM(x [, trim_string]);         从x的左修剪字符
12 NANVL(x, value);         如果x匹配NaN的特殊值（非数字）则返回其值，否则返回x
13 NLS_INITCAP(x);         相同INITCAP函数，但它可以使用不同的排序方法所指定NLSSORT
14 NLS_LOWER(x) ;         同样的，不同的是它可以使用不同的排序方法所指定NLSSORT LOWER函数
15 NLS_UPPER(x);         相同，不同之处在于它可以使用不同的排序方法所指定NLSSORT UPPER函数
16 NLSSORT(x);         改变排序的字符的方法。任何NLS函数之前必须指定该参数;否则，默认的排序被使用
17 NVL(x, value);         返回如果x为null返回null; 否则返回x
18 NVL2(x, value1, value2);         如果x不为null返回value1; 如果x为null，则返回value2
19 REPLACE(x, search_string, replace_string);    搜索x对于SEARCH_STRING并替换使replace_string它
20 RPAD(x, width [, pad_string]);         填充x到右侧
21 RTRIM(x [, trim_string]);         从x右边修剪
22 SOUNDEX(x) ;         返回包含x的拼音表示形式的字符串
23 SUBSTR(x, start [, length]);         返回x的一个子开始于由start指定的位置。可选长度为子字符串
24 SUBSTRB(x);         相同SUBSTR除外的参数均以字节代替字符的单字节字符的系统
25 TRIM([trim_char FROM) x);         从左侧和右侧修剪x字符
26 UPPER(x);         x转换为大写字母，并返回该字符串





四、数组


定义数组类型
create or replace type arrayname as varray(3) of varchar(10);
array1=arrayname(‘ssss’,’sede’,’dedece’);


type vararray is table of varchar2(50) index by binary_integer; 
array2 vararray; ---------不定长数组


array1.extend(m) 扩大m个内存
array1.trim(m) 减小m个内存


type arrayname is varray(3) of varchar2(10);
array1 arrayname:=arrayname(‘ssss’,’sede’,’dedece’);


declare 
  type arrayname is varray(10) of number(10,5);
  array1 arrayname:=arrayname();
  i integer:=0;
begin
  loop
    i:=i+1;
  array1.extend;
  array1(i):=i*10;
  dbms_output.put_line(array1(i));
  if i>=10 then exit;end if;
 end  loop;
dbms_output.put_line(array1.count);


end;


DECLARE
   CURSOR c_customers is
   SELECT  name FROM customers;
   type c_list is varray (6) of customers.name%type;
   name_list c_list := c_list();
   counter integer :=0;
BEGIN
   FOR n IN c_customers LOOP
      counter := counter + 1;
      name_list.extend;
      name_list(counter)  := n.name;
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter));
   END LOOP;
END;






四、过程


CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
  < procedure_body >
END procedure_name;


DECLARE
   a number;
   b number;
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
X1 number:=1;-----参数
X2 number:=2;-----参数
BEGIN
   IF x < y THEN
      z:= x;
   ELSE
      z:= y;
   END IF;
END; 
BEGIN
   a:= 23;
   b:= 45;
   findMin(a, b, c);
   dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;





五、函数


Create or replace function findmax(x in number,y in number)
Return data_type
Is 
X1 number:=1;-----参数
X2 number:=2;-----参数
Begin
if x>y then return x;end if;
  if x<=y then return y;end if;
end;




create or replace function findmax(x in number,y in number)
return number
is
begin
  if x>y then return x;end if;
  if x<=y then return y;end if;
end;


declare
a number:=10;
b number:=20;
function findmax(x in number,y in number)
return number
as begin
if x>y then return x;end if;
  if x<=y then return y;end if;
end;
begin
dbms_output.put_line(findmax(a,b));
end;






六、游标


隐式游标


%FOUND 返回TRUE如果一个INSERT，UPDATE或DELETE语句影响了一行或多行或SELECT INTO语句返回一行或多行。否则，它将返回FALSE。
%NOTFOUND 逻辑相反%FOUND。返回TRUE如果一个INSERT，UPDATE或DELETE语句影响没有行或SELECT INTO语句返回任何行。否则，它将返回FALSE。
%ISOPEN 隐式游标总是返回FALSE，因为Oracle执行其相关的SQL语句之后自动关闭SQL游标。
%ROWCOUNT 返回受INSERT，UPDATE影响的行数，或DELETE语句，或者通过一个SELECT INTO语句返回。




Sql%found、Sql%notfound、Sql%isopen、Sql%rowcount


DECLARE 
   total_rows number(2);
BEGIN
   UPDATE customers
   SET salary = salary + 500;
   IF sql%notfound THEN
      dbms_output.put_line('no customers selected');
   ELSIF sql%found THEN
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected ');
   END IF; 
END;




显式游标


CURSOR c_customers is SELECT id, name, address FROM customers;


OPEN c_customers;
FETCH c_customers INTO c_id, c_name, c_addr;
CLOSE c_customers;


DECLARE
   c_id customers.id%type;
   c_name customers.name%type;
   c_addr customers.address%type;
   CURSOR c_customers is
      SELECT id, name, address FROM customers;
BEGIN
   OPEN c_customers;
   LOOP
      FETCH c_customers into c_id, c_name, c_addr;
      EXIT WHEN c_customers%notfound;
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
   END LOOP;
   CLOSE c_customers;
END;




七、记录


X table_name%rowtype
Y cursor_name%rowtype




TYPE
type_name IS RECORD
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION],
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION],
   ...
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION);
record_name  type_name;


DECLARE
TYPE books IS RECORD
(title  varchar(50),
    author  varchar(50),
    subject varchar(100),
    book_id   number);
book1 books;
book2 books;





八、异常
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;




自定义异常、引发异常
DECLARE
   exception_name EXCEPTION;
BEGIN
   IF condition THEN
      RAISE exception_name;
   END IF;
EXCEPTION
   WHEN exception_name THEN
   statement;
END;




预定义异常
Exception 描述
ACCESS_INTO_NULL 当一个空对象会自动分配一个值引发此异常
CASE_NOT_FOUND 当没有任何选择，在当选择一个CASE语句的子句，并且没有ELSE子句时被引发
COLLECTION_IS_NULL 当程序试图申请其他收集方法不是存在未初始化的嵌套表或VARRAY，或程序试图值分配给未初始化的嵌套表或变长数组的元素时被引发
DUP_VAL_ON_INDEX 当重复值试图被存储在具有唯一索引的列时被引发
INVALID_CURSOR 当尝试以使这是不允许的，如关闭一个未打开的游标的游标操作时被引发
INVALID_NUMBER 当一个字符的字符串转换成一个数失败，因为字符串不表示一个有效的数据被引发
LOGIN_DENIED 当程序试图登录到数据库使用无效的用户名或密码被引发
NO_DATA_FOUND 它被引发当一个SELECT INTO语句无任何行返回
NOT_LOGGED_ON 在不连接到数据库发出数据库调用它被引发
PROGRAM_ERROR 它被引发当PL/SQL有一个内部问题
ROWTYPE_MISMATCH 当游标取值有不兼容的数据类型的变量被引发
SELF_IS_NULL 它被引发，当对象的成员方法被调用，但对象类型的实例没有被初始化。
STORAGE_ERROR 它被引发当PL/SQL内存不足或内存已损坏
TOO_MANY_ROWS 它被引发当SELECT INTO语句返回多行
VALUE_ERROR 当算术，转换，截短，或大小约束错误时引发
ZERO_DIVIDE 它被引发当一个数试图除以零。










九、触发器




CREATE [OR REPLACE ] TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF } 
{INSERT [OR] | UPDATE [OR] | DELETE} 
[OF col_name] 
ON table_name 
[REFERENCING OLD AS o NEW AS n] 
[FOR EACH ROW] 
WHEN (condition)  
DECLARE
   Declaration-statements
BEGIN 
   Executable-statements
EXCEPTION
   Exception-handling-statements
END;