PLSQL语言的运用.docx

1、PLSQL语言的运用 PL/SQL是 Procedure Language & Structured Query Language 的缩写 PL/SQL是对SQL语言存储过程语言的扩展PL/SQL的优点 有利于客户/服务器环境应用的运行 PL/SQL是对SQL语言存储过程语言的扩展 适合于客户环境 PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成 PL/SQL块结构和组成元素 PL/SQL块PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。 PL/SQL块结构和组成元素 PL/SQL块的结构如下：DECLARE /* 声明部分: 在此声

2、明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 */BEGIN /* 执行部分: 过程及SQL 语句 , 即程序的主要部分 */EXCEPTION /* 执行异常部分: 错误处理 */END;其中 执行部分是必须的。 PL/SQL块可以分成三类： 匿名块：动态构造，只能执行一次 子程序：存储在数据库中的存储过程、函数及包等。当在数据库上建立好后可以在其它程序中调用它们 触发器：当数据库发生操作时，会触发一些事件，从而自动执行相应的程序 PL/SQL中的标识符PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同 标识符名不能超过30字符 第一个字符必须为字母 不分大小

3、写 不能是SQL保留字 PL/SQL中的变量类型类型 说明CHAR定长字符串VARCHAR2 可变字符串BINARY_INTEGER 带符号整数,为整数计算优化性能 NUMBER(p,s) 数值LONG 变长字符串 DATE 日期BOOLEAN 布尔ROWID 存放数据库行号 PL/SQL中的标识符一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果 例：DECLARE Ename varchar2(20) :=KING;BEGIN DELETE FROM emp WHERE ename=ename;END;PL/SQL中声明变量在语句块的声明部分对变量声明，声明一个变量的语

4、法是： variable_name constant type not null :=value其中： variable_name 为变量名 type 为类型 value 为变量的初值PL/SQL中的标识符下面是建议使用的变量命名方法 标识符命名原则例程序变量 V_nameV_id程序常量 C_constantC_student_name游标变量 Name_cursorEmp_cursor异常标识 E_nameE_too_many_rows表类型 Name_table_typeEmp_record_type表 Name_tableEmp记录类型 Name_typeEmp_record参数P_n

5、ameP_idPL/SQL中声明变量例： DECLARE V_Description VARCHAR2(50); V_Number NUMBER := 45; V_Counter BINARY_INTEGER := 0;注意：如果变量在声明时使用了NOT NULL选项则必须为变量指定初值。 如果变量在声明时使用了CONSTANT选项则必须为变量指定初值， 并且该初值不能被改变。PL/SQL中的复合类型 记录类型 ：记录类型是把逻辑相关的数据作为一个单元存储起来 定义记录类型语法如下：TYPE record_type IS RECORD( Field1 type1 NOT NULL := exp

6、1 , Field2 type2 NOT NULL := exp2 , . . . . . . Fieldn typen NOT NULL := expn ) ; PL/SQL中的复合类型 %TYPE:定义一个变量，其数据类型与已经定义的某个数据变量的类型相同，或者与数据库表的某个列的数据类型相同 ，这时可以使用%TYPE 例：DECLARE - 用 %TYPE 类型定义与表相配的字段 TYPE t_Record IS RECORD( T_no emp.empno%TYPE, T_name emp.ename%TYPE, T_sal emp.sal%TYPE ); - 声明接收数据的变量 v_

7、emp t_Record; PL/SQL中的复合类型 %ROWTYPE:返回一个记录类型, 其数据类型和数据库表的数据结构相 一致。 例：DECLARE v_empno emp.empno%TYPE :=&no; rec emp%ROWTYPE;BEGIN SELECT * INTO rec FROM emp WHERE empno=v_empno; DBMS_OUTPUT.PUT_LINE(姓名:|rec.ename|工资:|rec.sal|工作时间:|rec.hiredate); END; PL/SQL表 PL/SQL 表, 或者称为索引表(index-table)，是可以在PL/SQL

8、程序中引用、能够模仿数组的非永久表。用户可以定义一个表类型，然后声明这种类型的变量。接下来，用户就可以将记录添加到用户的PL/SQL 表中，并且采用与引用数组元素大体相同的方法引用他们 表包括两个基本成分： 数据处理类型为BINARY_INTEGER主键 标量或记录数据类型的列 PL/SQL表 定义一个表：TYPE type_name IS TABLE OF column_type | variable%TYPE | table.column%TYPE NOT NULL | table%ROWTYPE INDEX BY BINARY_INTEGER; 例： DECLARE TYPE dept_

9、table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER; my_dname_table dept_table_type;PL/SQL表 注意： Index-by表中的元素不一定要按任何特定的顺序排序 用于Index-by表的关键字没有必要是顺序的 关键字唯一允许的类型是BINARY_INTERGER引用表元素 例： DECLARE type dept_table_type is table of dept%ROWTYPE index by binary_integer; my_dname_table dept_table_type

10、; int number(3);BEGIN int := -10; my_dname_table(int).dname := searching; dbms_output.put_line(my_dname_table(int).dname); int := 0; my_dname_table(int).dname := market; dbms_output.put_line(my_dname_table(int).dname); int := 10; my_dname_table(int).dname := finance; dbms_output.put_line(my_dname_ta

11、ble(int).dname);END;PL/SQL中的可变数组 一般格式为：TYPE type_name IS VARRAY (maximum_size) OF element_type其中：type_name是新可变长数组类型的类型名。 maximum_size是一个指定可变数组中元素最大数目的整数。 element_type是一个PL/SQL标量、记录或对象类型。一般格式为：DECLARE type numberlist is varray(10) of number(5); type recordlist is varray(5) of dept%rowtype;引用数组元素 例： D

12、ECLARE type strings is varray(5) of varchar2(10); - Declare a varray with four element v_list strings := strings(scott,peter,smith,tom); int number;BEGIN int := 1; dbms_output.put_line(v_list(int); v_list(int) := urman; dbms_output.put_line(v_list(int); int := 3; dbms_output.put_line(v_list(int); v_

13、list(int) := jackson; dbms_output.put_line(v_list(int); v_list.extend; v_list(4):=oracle; dbms_output.put_line(v_list(4);END;表和数组属性 属性返回类型描述有效范围existsboolean指定元素在集合中是否存在表、可变数组countnumber返回集合中元素的数目表、可变数组limitnumber返回集合中最大元素数可变数组First&lastBinary_integer返回第一个（最后一个）元素的索引表、可变数组Next&priorBinary_integer返回当

14、前元素的下一个（前一个）元素的索引表、可变数组extendN/A向集合中添加元素可变数组trimN/A从集合的最后删除元素可变数组deleteN/A从集合中删除指定元素表DECLARE type strings_table is table of varchar2(10) index by binary_integer; strings strings_table; int number;BEGIN int := 1; strings(int) := element1; if strings.exists(int) then dbms_output.put_line(strings(int);

15、 else dbms_output.put_line(no data!); return; end if;strings(2) := element2; strings(3) := element3; strings(4) := element4; strings(5) := element5; strings(6) := element6; dbms_output.put_line(strings.count); dbms_output.put_line(strings.first); dbms_output.put_line(strings.last); dbms_output.put_l

16、ine(strings.next(2); dbms_output.put_line(strings.prior(4); strings.delete(1,3); -dbms_output.put_line(strings(2);END;PL/SQL运算符和表达式 运算符 意义 = 等于 , != , = , = 不等于 大于=大于等于运算符 意义 +加号-减号*乘号/除号:=赋值号| 连接符运算符 意义 IS NULL 空值BETWEEN AND介于两者之间IN 在一个值列表之中AND并OR或NOT否PL/SQL中的变量赋值在PL/SQL编程中，变量赋值是一个值得注意的地方，它的语法如下： v

17、ariable := expression ; variable 是一个PL/SQL变量, expression 是一个PL/SQL 表达式. BOOLEAN 型变量赋值： 布尔值只有TRUE, FALSE及 NULL 三个值，其中空值在参加算数运算时，结果仍为空值。PL/SQL中的变量赋值DECLARE done BOOLEAN; /* the following statements are legal: */BEGINdone := FALSE;WHILE NOT done LOOPNull;END LOOP;END;PL/SQL中的变量作用范围及可见性 PL/SQL的变量作用范围特点是

18、： 变量的作用范围是在所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束 一个变量（标识）只能在所引用的块内是可见的 当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了） 在子块中重新定义该变量后，它的作用仅在该块内 PL/SQL中的变量作用范围及可见性 例： DECLARE v_Number NUMBER(3 , 2); BEGIN DECLARE v_Character VARCHAR2(10); BEGIN END; END;PL/SQL中的注释 在PL/SQL里，可以使用两种符号来写注释： PL/SQL允许用 来写注释，它的作用范围是

19、只能在一行有效。 例： V_Sal NUMBER(12,2); - 工资变量 使用 /* */ 来加一行或多行注释。 例：/*/* 文件名： department_salary.sql */*/DML语句的返回值RETURNING 列值1，列值2， INTO 变量1，变量2，插入语句返回值DECLARE v_deptno NUMBER; v_dname VARCHAR2(100);BEGIN INSERT INTO departments VALUES (122, HSW, 206, 1500) RETURNING department_id, department_name INTO v_d

20、eptno, v_dname; dbms_output.put_line(v_deptno); dbms_output.put_line(v_dname);END;更新语句返回值DECLARE v_deptno NUMBER; v_dname VARCHAR2(100);BEGIN UPDATE departments SET department_name = GE WHERE department_id = 20 RETURNING department_id, department_name INTO v_deptno, v_dname; dbms_output.put_line(v_d

21、eptno); dbms_output.put_line(v_dname);END;删除语句返回值 DECLARE v_deptno NUMBER; v_dname VARCHAR2(100);BEGIN DELETE FROM departments WHERE department_id = 190 RETURNING department_id, department_name INTO v_deptno, v_dname; dbms_output.put_line(v_deptno); dbms_output.put_line(v_dname);END;DML返回多行值 DECLARE

22、 TYPE emp_record_type IS RECORD( v_last_name employees.last_name%TYPE, v_salary employees.salary%TYPE); TYPE table_type IS TABLE OF emp_record_type; emp_table table_type;BEGIN UPDATE employees SET salary = salary + 1 WHERE salary = 5000 RETURNING last_name, salary BULK COLLECT INTO emp_table; FOR i

23、IN 1 . emp_table.count LOOP dbms_output.put_line(emp_table(i).v_last_name); dbms_output.put_line(emp_table(i).v_salary); dbms_output.put_line(-); END LOOP;END;1 基本输出语句BEGIN dbms_output.put_line(hello,world); dbms_output.put_line(127); dbms_output.put_line(sysdate); 系统当前时间 -dbms_output.put_line(ture)

24、; 不能直接输出true/falseEND;2 变量的使用SELECT 列1，列2.INTO 变量1 、变量2DECLARE i number(4):=250; j number(6); c varchar(100):=hello,world; d DATE := SYSDATE; -e boolean := true;BEGIN j:=1000; dbms_output.put_line(i= | i); dbms_output.put_line(j= | j); dbms_output.put_line(c= | c); dbms_output.put_line(d= | to_char(

25、d,YYYY-MM-DD); -dbms_output.put_line(e= | e);END;CONSTANT 常量的使用declare /只做声明 c_a CONSTANT NUMBER(4):=100; v_b number(4) not null:=101; 使用not null修饰变量时，变量必须初始化；begin -c_a:=101; 常量不能边 dbms_output.put_line(c_a); dbms_output.put_line(v_b); null； 不能为空end;更新100员工，工资增加1并在控制台打印输出declare v_empid binary_integ

26、er:=100; v_money number(10):=1;begin update employees set salary = salary +v_money where employee_id = v_empid; commit; dbms_output.put_line(v_empid | ,| v_money);end;查询某个员工的编号的姓名，工资，入职日期，部门编号declare v_empid binary_integer :=101; v_name varchar(50); v_salary number(8,2); v_hiredate DATE; v_deptid bi

27、nary_integer; begin select employee_id,last_name,salary,hire_date,department_id into v_empid,v_name,v_salary,v_hiredate,v_deptid from employees where employee_id= v_empid; dbms_output.put_line(v_empid | , | v_name | , | v_salary | , | to_char(v_hiredate,yyyY/mm/DD)|,|v_deptid );end;记录类型declare type

28、emp_record_type is record( v_empid binary_integer :=101, v_name varchar(50), v_salary number(8,2), v_hiredate DATE, v_deptid binary_integer ); e emp_record_type;begin select employee_id,last_name,salary,hire_date,department_id into e from employees where employee_id= e.v_empid; dbms_output.put_line(e.v_empid | , | e.v_name | , | e.v_salary | , | to_char(e.v_hiredate,yyyY/mm/DD)|,|e.v_deptid );end;参照引用类型未知的变量类型可以参照已知的变量类型，或者参照数据库表中的列的类型 语法%typedeclare i number(4); j i%type:=100; k e