PLSQL学习笔记.docx

一、 PL/SQL基本语法要素1. 相对其他语言特殊运算符=号是是否相等的比较运算符:=才是赋值运算符 两个单引号之间表示字符类型的变量 双引号之间标识引用，如果字段名，数据库名等| 表示字符之间的连接符-表示单行注释%属性指示器2. PL/SQL变量及数据类型1. 变量类型标量类型：数值，字符，日期，布尔复合类型：索引表，嵌套表，数组引用类型：游标类型（CURSOR）,对象类型（REF）LOB类型：大文本等2. 使用%定义变量的类型跟某个表的列属性一致的变量的声明 v_name %type 表示v_name 这个变量与表student中的列name 属性一样v_student student%rowtype 表示v_student这个变量客户存student中的一行数据3. 用select into个变量赋值的时候select结果必须只有一行数据，如果多行会包too many row错误，空的话则会报空错误。3. PL/SQL中最基本的语句块结构DECLARE 声明变量，要使用的变量必须在这里声明BEGIN 需要执行的程序语句EXCEPTION异常处理语句END二、 PL/SQL 程序顺序结构（分支和循环）1. 正常都是从上到下的顺序结构2. 使用if进行分支处理IF 语句的格式：if condition_1 then statement_1; elsif condition_n then statement_n;else else_statements; end if;3. 使用case进行分支处理CASE语句格式：1. 单值比较: case expression when result_1 then statements_1;when result_2 then statements_2;else else_statement;end case;2. 多值比较： case when expression_1 then statement_1;when expression_2 then statement_2;else else_statements;end case;4. 使用循环结构1. 使用loop循环结构：loop statements；exit when expression；end loop；满足expression条件的时候跳出循环；2. 使用while循环结构：while expression loop statements；end loop；expression 的格式：vi=10 不加分号3. 使用for循环结构：for expression loop statements；end loop;expression 的格式：vi in 1.10 不叫分号三、 游标基础及使用1. 游标的定义：游标用于表示一查询结果的结果集2. 游标使用四个步骤：-1.声明declare - 2.打开 open -3.读取fetch -4.关闭close1. 声明游标：CURSOR cursor_name IS select_statements;例如：CURSOR cursor_student IS SELECT id,name,age,sex FROM students WHERE name LIKE 林%；2. 使用游标是打开：Open cursor_name;例如：OPEN cursor_student;打开游标的时候系统才会去查询游标声明的数据集；3. 读取数据：FETCH cursor_name INTO variable_name1,variable_name2;例如：FETCH cursor_studnet INTO v_id,v_sname,v_age,v_sex;第一次读取的是第一行数据，再次使用FETCH读取数据的时候是下一行所以处理游标时一般都是用循环；4. 关闭游标：CLOSE cursor_name;例如:CLOSE cursor_student;3. 游标中的属性：四个常用属性：%ISOPEN 、 %FOUND 、 %NOTFOUND 、 %ROWCOUNT1. %ISOPEN 返回游标是否打开 使用方法 cursor_name%ISOPEN 例如：cursor_student%ISOPEN;2. %NOTFUND 返回最近一次FETCH取数是否有取到数，没取到返回true，有取到返回false；一般用于判断循环终止使用方法：cursor_name%NOTFUND 例如：cursor_student%NOTFOUND3. %FOUND 与%NOTFOUND相反4. %ROWCOUNT 返回现在已经读取的行数使用方法：cursor_name%ROWCOUNT例如：cursor_student%ROWCOUNT4. 简单使用游标循环读取数据的例子读取并打印学生表中“计算机系学生的信息”DECLAREv_specialty students.specialty%type; -声明变量v_name %type;v_dob students.dob%type;CURSOR student_cur -声明游标IS SELECT name,dob FROM students WHERE specialty=v_specialty;BEGIN v_specialty:=计算机; -给变量赋值OPEN student_cur; -打开游标DBMES_OUTPUT.PUT_LINE(学生姓名 出生日期); -输出LOOP FETCH student_cur INTO v_name,v_dob; -读取游标中的数据EXIT WHEN student_cur%NOTFUND; -设定退出条件DBMS_OUTPUT.PUT_LINE(v_name| |v_dob); -输出结果END LOOP; -循环体结束标志CLOSE sutent_cur; -关闭游标END; -块结构结束标志5. 游标的应用：1. 对游标指定的数据进行修改：需要对游标指定的数据修改必须在声明游标的时候带有FOR UPDATE 关键字语法：CURSOR cursor_name IS select_statement FOR UPDATE OF table_name NOTWAIT;OF table_name 可以不写，用于自动选择多表的时候确定那些表需要锁定；NOTWAIT可以不写，用于确认是否等待锁。对数据操作的语句使用WHERE CURRENT OF 关键字表示修改游标指向的当前行。例如根据职称调整教师工资：DECLAREv_title teachers.title%type;CURSOR teachers_cur -声明游标并指定为可修改ISSELECT title FROM teachers FOR UPDATE;BEGIN OPEN teachers_cur; -打开游标 LOOP FETCH teachers_cur INTO v_title; -读取游标数据 EXIT WHEN teacher_cur%NOTFOUND; -退出循环条件 CASE -使用case分支判断 WHEN v_title=教授 THEN UPDATE teachers SET wage=1.1*wage WHERE CURRENT OF teachers_cur; -使用WHERE CURRENT OF 指定修改的当前行 WHEN v_title=高工 THEN UPDATE teachers SET wage=1.05*wage WHERE CURRENT OF teachers_cur; END CASE; -结束case分支END LOOP;CLOSE teachers_cur;END;2. 使用游标对数据进行删除跟修改类似，参考修改的例子，同样需要使用 WHERE CURRENT OF 关键字。3. 游标WHILE循环OPEN student_curFETCH student_cur INTO v_name,v_specialty;WHILE student_cur%FOUND LOOP IF v_specialty=计算机 THEN DELETE FROM students WHERE CURRENT OF students_cur; END IF; FETCH student_cur INTO v_name,v_specialty;END LOOP;CLOSE student_cur;4. 游标FOR循环使用FOR循环不用使用FETCH 和CLOSE 游标格式一：(使用声明的游标)FOR record_name IN cursor_name LOOP Statement1; Statement2;END LOOP;格式二：(使用子查询(匿名游标)FOR record_name IN selectstatement LOOP Statement1; Statement2;END LOOP;例如显示计算机系所有学生信息：格式一：DECLARE v_specialty students.specialty%TYPE; CURSOR students_cur IS SELECT name,dob FROM students WHERE specialty=v_specialty; BEGIN v_specialty:=计算机; DBMS_OUTPUT.put_line(序号 学生姓名 出生日期); FOR student_record IN students_cur LOOP dbms_output.put_line(students_cur%ROWCOUNT| |student_| |student_record.dob); END LOOP; END;格式二：DECLARE v_specialty students.specialty%TYPE; BEGIN v_specialty:=计算机; DBMS_OUTPUT.put_line(学生姓名 出生日期); FOR student_record IN (SELECT name,dob FROM students WHERE specialty=v_specialty) LOOP -直接使用只查询 dbms_output.put_line( |student_| |student_record.dob); END LOOP; END;5. 使用游标参数：(给游标传递参数)语法格式：CURSOR cursor_name(para_name1 datatype,para_name2 datatype,) IS select_statement;例如用计算机系作为参数显示计算机系的学生信息：DECLARE V_NAME STUDENTS.name%TYPE; V_DOB STUDENTS.DOB%TYPE; CURSOR STUDENTS_CUR(V_SPECIALTY STUDENTS.SPECIALTY%TYPE) IS SELECT name, DOB FROM STUDENTS WHERE SPECIALTY = V_SPECIALTY;BEGIN OPEN STUDENTS_CUR(计算机系);-打开游标的时候使用计算机系作为参数 FETCH STUDENTS_CUR INTO V_NAME, V_DOB; WHILE STUDENTS_CUR%FOUND LOOP DBMS_OUTPUT.PUT_LINE(V_NAME | | V_DOB); FETCH STUDENTS_CUR INTO V_NAME, V_DOB; END LOOP; CLOSE STUDENTS_CUR;END;6. 游标变量游标变量可以指定动态的变更指定不同的游标。游标变量的使用也要经过四个步骤：1.定义 2.打开 3.读取数据 4.关闭数据1. 定义游标变量：(1).定义游标类型TYPE ref_type_name IS REF CURSOR RETURN return_type;ref_type_name 指定游标变量使用的数据类型；RETURN return_type 为ref_type_name指定返回的类型，可以省略，而且类型必须是record类型的数据；没有指定return的时候游标变量能跟任何select语句关联；(2).定义游标类型变量：cursor_variable ref_type_name;cursor_variable 游标变量名；2. 打开游标变量：OPEN cursor_variable FOR select_statement;select_statement 查询子句3. 读取游标变量的数据：FETCH cursor_variable INTO variable1,variable2,.;4. 关闭游标变量：CLOSE cursor_variable;7. 游标变量使用实例1. 显示学生姓名和出生日期，不实用returnDECLARE TYPE student_cur IS REF CURSOR; -定义游标类型StuCursor student_cur; -声明游标变量students_record students%ROWTYPE;BEGIN IF NOT StuCursor%ISOPEN THEN OPEN StuCursor FOR SELECT * FROM students;-打开游标变量并附select子句 END IF; dbms_output.put_line(学生姓名 出生日期); LOOP FETCH StuCursor INTO students_record; -读取游标变量数据 EXIT WHEN StuCursor%NOTFOUND; dbms_output.put_line(students_| |students_record.dob); END LOOP; CLOSE StuCursor; -关闭游标END;2. 显示学生姓名和出生日期，使用returnDECLARE TYPE students_record IS RECORD( -定义一个记录类型的数据 StuName VARCHAR2(10), StuDob DATE ); StuRecord students_record; -声明一个记录TYPE students_cur IS REF CURSOR RETURN students_record; -定义一个游标类型并且游标类型的数据是上面的记录类型 StuCursor students_cur; -声明一个游标变量BEGIN IF NOT StuCursor%ISOPEN THEN OPEN StuCursor FOR SELECT name,dob FROM students; -打开游标select子句要和record一致 END IF; dbms_output.put_line(学生姓名 出生日期); LOOP FETCH StuCursor INTO StuRecord; EXIT WHEN StuCursor%NOTFOUND; dbms_output.put_line(StuRecord.StuName| |StuRecord.StuDob ); END LOOP; CLOSE StuCursor; END;8. 游标表达式游标表达式在select语句中使用，构成嵌套游标。(在声明游标时候的select语句中使用)，返回是是游标变量（REF CURSOR）。游标表达式的格式:CURSOR(subquery)例子：循环显示个系部的所有老师姓名和职称DECLARE v_tname %TYPE; v_title teachers.title%TYPE; v_dname departments.department_name%TYPE; TYPE cursor_type IS REF CURSOR; -定义一个游标类型 -声明一个游标，并且一个游标表达式嵌套 CURSOR departments_cur(dept_id NUMBER) IS SELECT d.department_name,CURSOR(SELECT name,title FROM teachers WHERE department_id=d.department_id) FROM departments d WHERE d.department_id=dept_id; teachers_cur cursor_type; -声明一个游标变量BEGIN OPEN departments_cur(101); -打开外层游标 LOOP FETCH departments_cur INTO v_dname,teachers_cur; -取外层游标数据同时把内层游标打开 EXIT WHEN departments_cur%NOTFOUND; dbms_output.put_line(系部名称：|v_dname); dbms_output.put_line(教师名 职称); LOOP FETCH teachers_cur INTO v_tname,v_title; -取内层游标数据 EXIT WHEN teachers_cur%NOTFOUND; dbms_output.put_line(v_tname| |v_title);END LOOP;END LOOP;四、 复合数据类型1. 记录类型显示的定义记录类型格式：TYPE record_type_name IS RECORD( -定义一个记录类型filed1_name datatype1, -记录类型的成员 名称和数据类型filed2_name datatype2,filed3_name datatype3);variable_name record_type_name; -声明一个记录变量2. 记录表类型数据记录类型的数据类似二维数组定义记录表类型的格式：TYPE recordtable_type_name IS TABLE OF table_name%ROWTYPE| column_name%TYPE INDEX BY binay_integer; -定义记录表variable_name recordtable_type_name; -声明记录表table_name%ROWTYPE跟column_name%TYPE 为二选一指定一行数据中包含的变量binay_integer 指定表元素下标使用的数据类型使用记录表是要指定列variable_name(index_number)如：variable_name(2) 表示下表为2的记录type student_recordtable is table of students%rowtype index by binay_integer;select * into student_recordtable(1) from students where id=101;五、 子程序1. 存储过程定义存储过程的语句：CREATE OR REPLACE PROCEDURE procedure_name (argument_list IN |OUT | IN OUT argument_type,)IS | ASvariable_name1 datatype1; -声明变量variable_name2 datatype2;BEGINprocedure_body;END procedure_name;OR REPLACE 可加可不加，加的话会覆盖相同名称的存储过程argument_list 表示参数列表argument_type 表示参数类型IN |OUT | IN OUT 三选一 IN 表示外部输入给过程，OUT表示参数在过程中可以被赋值IS | AS 二选一variable_name1 datatype1 声明变量和变量类型调用或执行存储过程：CALL| EXECUTE procedure_name(argument_list);例如：显示某系的老师的平均工资，最大工资，最小工资CREATE OR REPLACE PROCEDURE display_teacher(v_no teachers.department_id%TYPE)AS v_wage teachers.wage%TYPE; v_maxwage teachers.wage%TYPE; v_minwage teachers.wage%TYPE;BEGIN SELECT AVG(wage) INTO v_wage FROM teachers WHERE department_id=v_no; SELECT MAX(wage) INTO v_maxwage FROM teachers WHERE department_id=v_no; SELECT MIN(wage) INTO v_minwage FROM teachers WHERE department_id=v_no; dbms_output.put_line(该系平均工资：|v_wage); dbms_output.put_line(该系最大工资：|v_maxwage); dbms_output.put_line(该系最低工资：|v_minwage);EXCEPTION WHEN no_data_found THEN dbms_out.put_line(该系不存在。);END display_teacher;存储过程管理：在系统里面查找存储过程 SELECT object_name,created,status FROM user_objects WHERE object_name=procedure_name查看存储过程代码：SELECT text FROM user_source WHERE name=procedure_name2. 函数函数与过程的不同在于函数可以换回值，过程不能有返回值函数定义格式：CREATE OR REPLACE FUNCTION function_name (argument_list IN |OUT | IN OUT argument_type,)RETURN datatype -返回数据类型IS | ASvariable_name1 datatype1; -声明变量variable_name2 datatype2;BEGIN procedure_body; RETURN expression; -换回语句END procedure_name;调用函数方法：直接用表达式：function_name(argument_list)函数管理:查找函数：SELECT object_name,created,status FROM user_objects WHERE object_name=function_name;查看函数代码:SELECT text FROM user_source WHERE name=function_name;3. 触发器触发器是特殊的子程序，不能被调用。只能被触发。由SELECT INSERT UPDATE DELETE 等DML操作触发触发关键字AFTER 和 BEFORE 之后和之前触发触发器格式：CREATE OR REPLACE TRIGGER trigger_name BEFORE | AFTER INSERT OR UPDATE OR DELETE OF column_name ON table_nameBEGIN trigger_statement1; RAISE_APPLICATION_ERROR(-20000, error message); -定义触发器执行时检测条件不符合时的错误 END trigger_name;BEFORE | AFTER 二选一 before就是在触发事件前触发INSERT OR UPDATE OR DELETE 可以一个或者多个column_name 表示修改对应的列时触发 可不写表示对整张表简单的触发器实例：非工作时间无法修改教师信息：CREATE OR REPLACE TRIGGER CHANGE_TEACHER BEFORE INSERT OR UPDATE OR DELETE ON TEACHERSBEGIN IF TO_CHAR(SYSDATE, hh24) NOT BETWEEN 8 AND 17 OR TO_CHAR(SYSDATE, DY, nls date_langudage=american) IN (SAT, SUN) THEN RAISE_APPLICATION_ERROR(-20000, 非工作时间不能改变教师信息！); END IF END CHANGE_TEACHER;触发器管理：查询系统中的触发器：SELECT * FROM user_triggers WHERE trigger_name=trigger_name;启用，禁用，删除触发器ALTER TRIGGER trigger_name ENABLE;ALTER TRIGGER trigger_name DISABLE;DROP TRIGGER trigger_name;4. 创建临时表：会话级的临时表：Create Global Temporary Table Table_Name (Col1 Type1,Col2 Type2.) On Commit Preserve Rows;例：create global temporary table