ITeye博客

PL/SQL编程基础 学习总结

quningstudy · 2010-05-27 18:45 · 数据库技术文档 · 数据库 · Markdown

PL/SQL编程基础 1.如何编写和编译PL/SQL 因为PL/SQL是嵌入在Oracle服务器和Oracle开发工具中的,所以在Oracle客户端的各种工具和服务器中 都可以编写PL/SQL程序 SQL>edit c:\plsqlblock1.sql; --建一个编写的文件 SQL>set serveroutput on; --激活dbms_output包,以便在PL/SQL块中可以引用dbms_output包将变量的值输出在屏幕上 SQL>start c:\plsqlblock1.sql; --编译和运行该PL/SQL块,查看该块是否有语法错误和逻辑错误

注意:在PL/SQL中使用SELECT 语句的语法与标准SQL中的SELECT语法有,其他PL/SQL所支持的SQL命令在标 准SQL和PL/SQL中则没有区别 标准SQL中的SELECT语法如下: select 列名,列名.... from 表 where 条件....; 在PL/SQL中的SELECT语法为: SELECT 列名,列名... into 变量1,变量2... from 表 where 条件....;

2.PL/SQL程序结构 --说明部分 要用到的所有变量,常量,数据类型,游标和用户定义的错误等对象的定义 ...... BEGIN ..... --主块的语句执行部分 --出错处理部分 END; / 3.变量定义 NUMBER(数字型),CHARACTER(字符类型),DATE/TIME(日期型)和BOOLEAN(布尔型) 变量数据类型; eg: V_CHAR VARCHAR2(20); %TYPE和%ROWTYPE类型 %TYPE即一个变量的类型用另一个已经定义的变量的类型定义,或用某一个表的某一列的类型定义 eg: BOOKPRINT NUMBER; V_BOOK BOOKPRINT%TYPE; 用已经定义的BOOKPRINT V_EMPNO EMP.EMPNO %TYPE; 用EMP表中的EMPNO定义

%ROWTYPE 即一个变量的类型参考基表,视图中记录的类型或游标的结构类型,%ROWTYPE前面是表名或游标名,此类型 的变量内的字段与表或视图,游标内的列的名字和类型完全一致 eg: V_REC EMP%ROWTYPE emp代表表名或前面定义的游标名 V_REC.EMPNO:=100;

用户自定义的类型 定义用户自定义的数据类型student_type SQL>create replace type student_type as object( id number(5), firstname varchar2(20), lastname varchar2(20), major varchar2(30), current_credits number(3)); )

引用用户自定义类型V_student student_type; 4.变量赋值 通过:=给变量赋值 变量名:=常量或PL/SQL表达式 或通过SELECT INTO 语句或FETCH INTO 语句给变量赋值 select sal into NUM_VAR from emp where empno = 7788; 5.PL/SQL运算符和函数 注意:PL/SQL中不支持聚组函数 PL/SQL中对空值进行的算术运算和比较运算得到的结果都是NULL,NULL能做字符串的联结,要判断 某一变量的值是否为空不能直接用 等号去判断必须要用IS NULL IS NOT NULL eg: NULL+(数字)=null null>(数字)=null null||'字符串'='字符串' 判断一个变量的值是否为NULL的正确写法如下; IF my_var is null then .... end if;

5.PL/SQL的控制结构 (1).条件语句 IF 条件1 THEN 语句系列1;

END IF; eg: DECLARE V_sal emp.sal%type; BEGIN select sal into v_sal from emp where empno=7788; if v_sal<500 then update emp set sal=sal1.5 where empno=7788; elsif v_sal<1500 then update emp set sal=sal1.3 where empno=7788; elsif v_sal<3000 then update emp set sal=sal1.1 where empno=7788; else update emp set sal=sal1.0 where empno=7788; end if; commit; end; /

(2).CASE语句 1.含selector的CASE语句

case selector when 表达式1 then 语句系列1; when 表达式2 then 语句系列2; ... when 表达式n then 语句系列n;

end case;

6.循环 简单循环 LOOP

END LOOP; while循环 while ...loop end loop; 数字式for循环 for ...loop end loop; 循环体内必须有退出循环的语句EXIT

loop end loop; DECLARE V_counter binary_integer:=1; BEGIN LOOP INSERT INTO TEMP_table values(v_counter,'loop index'); V_counter:=v_counter+1; IF v_counter>50 then exit; end if; end loop; end; /

for ...loop end loop; declare

V_counter NUMBER:=1; BEGIN for V_counter IN 1..50 LOOP IN索引的变量从小到大 INSERT INTO temp_table values(V_counter,'loop index'); end loop; end;

declare

V_counter NUMBER:=1; BEGIN for V_counter IN REVERSE 1..50 LOOP IN REVERSE索引的变量从大到小 INSERT INTO temp_table values(V_counter,'loop index'); end loop; end;

7.标签 为了提高程序的可读性,可以给语句,块,循环加标签(label),标签在一个块或一个循环前面,由双尖括号引起来 用:GOTO + LABEL将控制传递到程序的另一部分

8.出错处理部分 EXCEPTION WHEN 错误1 THEN 语句序列1; WHEN 错误3THEN 语句序列2; WHEN OTHERS THEN 语句序列3; END;

(1).系统预定义的错误 (2).用户自定义错误 DECLARE my_exception EXCEPTION; 用户定义的错误是在语句执行部分通过显式使用RAISE语句来触发的 RAISE 错误名

eg: 查找classes表中历史系101课程的当前学生数与最大学生数,比较这两个数,如果当前学生数超过最大学生 数是不允许的,就触发错误,当这种错误发生时向log_table表中插入一条记录

9.游标的使用 为了能够得到查询结果的多条记录必须使用游标:游标有显示游标和隐式游标 对单条记录的SQL语句的操作都使用隐式游标 (1).定义游标 cursor 游标名)] is select 语句; eg:cursor c1 is select ename,deptno from emp; (2).打开游标 open 游标名)]; (3).取值到变量 Fetch 游标名 into 变量1;

Declare cursor c_classes is select *from class; V_classes c_classes%rowtype; /c_classes是一个游标名,必须先定义该游标/ BEGIN ... FETCH c_classes into v_classes;

.. end;

/ (4).关闭游标 close c_classes;
游标的属性 %ISOPEN %NOTFOUND %FOUND %ROWCOUNT

eg: 通过游标用简单循环控制从emp表中取出某一部门的员工姓名和工资 Declare v_deptno emp.deptno%type:=&p_deptno;/&p_deptno表示可以在运行时接受输入的变量p_deptno/ v_ename emp.ename%type; v_sal emp.sal%type; cursor emp_cursor is select ename,sal from emp where dptno_v_deptno; BEGIN OPEN emp_cursor; loop FETCH emp_cursor into v_ename,v_sal; exit when emp_cursor%notfound; insert into temp(ename,sal) values(v_ename,v_sal); end loop; close emp_cursor; commit; end; /

使用游标的for循环(for循环自动完成的三个步骤:打开游标,取数据,关闭游标); 用游标的for循环实现上述功能如下: Declare v_deptno emp.deptno%type:=&p_deptno; cursor emp_cursor is select ename,sal from emp where deptno=v_deptno; BEGIN For emp_record IN emp_cursor loop insert into temp(ename,sal) values(emp_record.ename,emp_record.sal);/返回结果在记录名.列名中/ end loop; commit; end; /

10.通过游标操纵数据库

当通过游标更新,删除多条记录的时候,游标的查询语句中必须包含for update of 否则返回错误,必须先加 锁,然后才能进行更新删除操作 eg: 查询emp表某部门的雇员情况,如果某雇员的工资小于800,则将其工资改成800 Declare v_deptno emp.deptno%type:=&p_deptno; v_empno emp.empno%type; v_sal emp.sal%type; v_job emp.job%type; cursor emp_cursor is select empno,job,sal from emp where deptno=v_deptno for update of sal;--表示更新游标取出来的那条记录 BEGIN for emp_record in emp_cursor loop if emp_record.sal<800 then update emp set sal=800 where current of emp_cursor; end if ; dbms_output.put_line(emp_record.empno||emp_record.sal); end loop; commit; end; /

eg: 为职工增加10%的工资,从最低工资开始长,增加后工资总额限制在50万元以内

Declare cursor c1 is select empno,sal from emp order by sal for update of sal; emp_num number:=0; s_sal emp.sal%type; e_sal emp.sal%type; e_empno emp.empno%type; BEGIN OPEN c1; select sum(sal) into s_sal from emp; while s_sal<500000 loop FETCH c1 into e_empno,e_sal; exit when c1%notfound; update emp set sal=sal1.1 where current of c1; s_sal:=s_sal+e_sal0.1; emp_num:=emp_num+1; end loop; close c1; insert into msg values(emp_num,s_sal); commit; end ; / 11.带参数的游标 eg: 首先查询DEPT表取出所有的部门号,然后根据DEPT表中返回的每个部门号,从emp表中查询该部门的雇员信息 DECLARE cursor dept_cursor is select deptno from dept; v_ename emp.ename%type; v_sal emp.sal%type; cursor emp_cursor (v_deptno number) is select ename,sal /定义带参数的游标/ from emp where deptno=v_deptno; Begin for dept_record in dept_cursor loop exit when dept_cursor%notfound; open emp_sursor(dept_record.deptno);/打开带参数的游标,参数的实际值是外层游标取出的部门号/ loop fetch emp_cursor into v_ename,v_sal; exit when emp_sursor%notfound; dbms_output.put_line(dept_record.deptno||' '||v_ename||' '||v_sal); end loop close emp_cursor; end loop; commit; end;

评论(0)

暂无评论。

评论功能维护中,当前仅展示历史评论。