首页 > 数据库 > MsSql数据库 > PLSQL语法&&游标&&存储过程/存储函数&&异常&&触发器

PLSQL语法&&游标&&存储过程/存储函数&&异常&&触发器
类别：MsSql数据库作者：码皇来源：<a href="http://blog.csdn.net/rocky_03" target="_blank" rel="nofollow&qu 点击：

PLSQL语法&&游标&&存储过程存储函数&&异常&&触发器。结构化查询语言(Structured Query Language，简称SQL)是用来访问关系型数据库一种通用语言，属于第四代语言（4GL），其执行特点是非过程化，即不用指明执行的具体方法和途径，而是简单地调用相应语句来直接取得结果即可。

什么是PL/SQL？

PLSQL语法&&游标&&存储过程/存储函数&&异常&&触发器。结构化查询语言(Structured Query Language，简称SQL)是用来访问关系型数据库一种通用语言，属于第四代语言（4GL），其执行特点是非过程化，即不用指明执行的具体方法和途径，而是简单地调用相应语句来直接取得结果即可。显然，这种不关注任何实现细节的语言对于开发者来说有着极大的便利。然而，有些复杂的业务流程要求相应的程序来描述，这种情况下4GL就有些无能为力了。PL/SQL的出现正是为了解决这一问题，PL/SQL是一种过程化语言，属于第三代语言，它与C、 C++、Java等语言一样关注于处理细节，可以用来实现比较复杂的业务逻辑。

1. 变量命名规则

这里写图片描述

2. helloworld

declare --声明的变量，类型，游标begin --程序处理部分(类似于java的main()方法) dbms_output.put_line('
helloworld'
);
exception --针对于begin块中处理的异常，提供处理机制 -- when ... then ... -- when ... then ...end；

输出：

helloworld

3. 简单的查询操作

declare --声明变量v_sal number(10,2);
-- v_sal employees.salary%type;
v_email varcgar2(20);
-- v_email employees.email%type;
V_hire_date date;
-- v_hire_date employees.hire_date%type;
begin -- sql语句的操作 select salary,email,hire_date, into v_sal,v_email,v_hire_date from employees where employee_id=100;
dbms_output.put_line(v_sal||'
,'
||v_email||'
,'
||v_hire_date);

3.1 创建一个对象，存放需要查询的值作为成员变量

type [对象名] is record(
v_xxx [类型]
)

declare --声明变量 type emp_record is record( v_sal number(10,2), v_email varcgar2(20), v_hire_date date );
--定义一个记录类型的成员变量 v_emp_record emp_record;
begin -- sql语句的操作 select salary,email,hire_date, into v_emp_record from employees where employee_id=100;
dbms_output.put_line(v_emp_record.v_sal||'
,'
||v_emp_record.v_email||'
,'
||v_emp_record.v_hire_date);

3.2 使用 %rowtype

declare--声明一个记录类型的变量 v_emp_record employees%rowtype;
begin --通过 select ... into ... 语句为变量赋值 select * into v_emp_record from employees where employee_id = 186;
-- 打印变量的值 dbms_output.put_line(v_emp_record.last_name || '
, '
|| v_emp_record.email || '
, '
|| v_emp_record.salary || '
, '
|| v_emp_record.job_id || '
, '
|| v_emp_record.hire_date);
end;

3.3 赋值语句：通过变量实现查询语句

declare v_emp_record employees%rowtype;
v_employee_id employees.employee_id%type;
begin --使用赋值符号位变量进行赋值 v_employee_id := 186;
--通过 select ... into ... 语句为变量赋值 select * into v_emp_record from employees where employee_id = v_employee_id;
-- 打印变量的值 dbms_output.put_line(v_emp_record.last_name || '
, '
|| v_emp_record.email || '
, '
|| v_emp_record.salary || '
, '
|| v_emp_record.job_id || '
, '
|| v_emp_record.hire_date);
end;

3.4 通过变量实现DELETE、INSERT、UPDATE等操作

declare v_emp_id employees.employee_id%type;
begin v_emp_id := 109;
delete from employees where employee_id = v_emp_id;
--commit;
end;

4.流程控制

4.1 条件判断(两种)

方式一：if … then elsif then … else … end if;

要求: 查询出 150号员工的工资, 若其工资大于或等于 10000 则打印 ‘salary >= 10000’;
若在 5000 到 10000 之间, 则打印 ‘5000<= salary < 10000’; 否则打印 ‘salary < 5000’

declare v_salary employees.salary%type;
begin --通过 select ... into ... 语句为变量赋值 select salary into v_salary from employees where employee_id = 150;
dbms_output.put_line('
salary: '
|| v_salary);
-- 打印变量的值 if v_salary >= 10000 then dbms_output.put_line('
salary >= 10000'
);
elsif v_salary >= 5000 then dbms_output.put_line('
5000 <= salary < 10000'
);
else dbms_output.put_line('
salary < 5000'
);
end if;

方式二：case … when … then … end;

declare v_sal employees.salary%type;
v_msg varchar2(50);
begin select salary into v_sal from employees where employee_id = 150;
--case 不能向下面这样用 /* case v_sal when salary >= 10000 then v_msg := '
>=10000'
when salary >= 5000 then v_msg := '
5000<= salary < 10000'
else v_msg := '
salary < 5000'
end;
*/ v_msg := case trunc(v_sal / 5000) when 0 then '
salary < 5000'
when 1 then '
5000<= salary < 10000'
else '
salary >= 10000'
end;
dbms_output.put_line(v_sal ||'
,'
||v_msg);
end;

要求:

查询出 122 号员工的 JOB_ID, 若其值为 ‘IT_PROG’, 则打印 ‘GRADE: A’;
‘AC_MGT’, 打印 ‘GRADE B’,
‘AC_ACCOUNT’, 打印 ‘GRADE C’;
否则打印 ‘GRADE D’

declare --声明变量 v_grade char(1);
v_job_id employees.job_id%type;
begin select job_id into v_job_id from employees where employee_id = 122;
dbms_output.put_line('
job_id: '
|| v_job_id);
--根据 v_job_id 的取值, 利用 case 字句为 v_grade 赋值 v_grade := case v_job_id when '
IT_PROG'
then '
A'
when '
AC_MGT'
then '
B'
when '
AC_ACCOUNT'
then '
C'
else '
D'
end;
dbms_output.put_line('
GRADE: '
|| v_grade);
end;

4.2 循环结构(三种)

使用循环语句打印 1 - 100.（三种方式

方式一：loop … exit when … end loop;

declare --初始化条件 v_i number(3) := 1;
begin loop --循环体 dbms_output.put_line(v_i);
--循环条件 exit when v_i = 100;
--迭代条件 v_i := v_i + 1;
end loop;
end;

方式二：while … loop … end loop;

declare v_i number(3) :=1;
begin while v_i <=100 loop dbms_output.put_line(v_i);
v_i := v_i+1;
end loop;
end;

方式三for i in … loop … end loop;

begin for i in 1 .. 100 loop dbms_output.put_line(i);
end loop;
end;

练习

输出100以内的素数

declare v_i number(3) :=2;
v_j number(2) :=2;
--标记值, 若为 1 则是素数, 否则不是 v_flg number(1):=1;
begin while(v_i <= 100) loop while(v_j < sqrt(v_i)) loop if(mod(v_i,v_j)=0) then v_flg :=0;
end if;
v_j := v_j+1;
end loop;
if (v_flg=1) then dbms_output.put_line(v_i);
end if;
v_i :=v_i+1;
v_flg := 1;
v_j:=2;
end loop;
end;

输出：

SQL> /2...........97PL/SQL procedure successfully completed

4.3goto、exit

goto

同样拿素数来举列子

declarev_flg number(1):=0;
begin for i in 2 .. 100 loop v_flg := 1;
for j in 2 .. sqrt(i) loop if mod(i,j)=0 then v_flg :=0;
-- use lable goto label;
end if;
end loop;
<> if v_flg = 1 then dbms_output.put_line(i);
end if;
end loop;
end;

exit

相当于 Java中个break，跳出循环
举例说明：
打印1——100的自然数，当打印到50时，跳出循环，输出“打印结束”

begin for i in 1..100 loop dbms_output.put_line(i);
if(i mod 50 = 0) then dbms_output.put_line('
打印结束'
);
-- 跳出循环 exit;
end if;
end loop;
end;

5. 游标

类似于Java的Iterator

定义游标：cursor [游标名] is select XXX 打开游标：open [游标名]; 提取游标：fetch [游标名] into [变量名]; 获得游标下一个：[游标名]%found 关闭游标：close [游标名]

练习1：

打印出 80 部门的所有的员工的工资:salary: xxx

declare --1. 定义游标 cursor salary_cursor is select salary from employees where department_id = 80;
v_salary employees.salary%type;
begin --2. 打开游标 open salary_cursor;
--3. 提取游标 fetch salary_cursor into v_salary;
--4. 对游标进行循环操作: 判断游标中是否有下一条记录while salary_cursor%found loop dbms_output.put_line('
salary: '
|| v_salary);
fetch salary_cursor into v_salary;
end loop;
--5. 关闭游标 close salary_cursor;
end;

练习2：

打印出 manager_id 为 100 的员工的 employee_id,last_name, salary 信息(使用游标, 记录类型)

declarecursor emp_cursor is select employee_id,last_name,salary from employees where department_id = 80;
type emp_record is record( id employees.employee_id%type, name employees.last_name%type, salary employees.salary%type);
v_emp_record emp_record;
begin open emp_cursor;
fetch emp_cursor into v_emp_record;
while(emp_cursor%found) loop dbms_output.put_line('
id:'
||v_emp_record.id||'
name: '
|| v_emp_record.name||'
salary:'
||v_emp_record.salary);
fetch emp_cursor into v_emp_record;
end loop;
end;

练习3

利用游标, 调整公司中员工的工资:

工资范围调整基数0 - 5000 5%5000 - 10000 3%10000 - 15000 2%15000 - 1%

declare --定义游标 cursor emp_sal_cursor is select salary, employee_id from employees;
--定义基数变量 v_temp number(4, 2);
--定义存放游标值的变量 v_sal employees.salary%type;
v_id employees.employee_id%type;
begin --打开游标 open emp_sal_cursor;
--提取游标 fetch emp_sal_cursor into v_sal, v_id;
--处理游标的循环操作 while emp_sal_cursor%found loop --判断员工的工资, 执行 update 操作 --dbms_output.put_line(v_id || '
: '
|| v_sal);
if v_sal <= 5000 then v_temp := 0.05;
elsif v_sal<= 10000 then v_temp := 0.03;
elsif v_sal <= 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;
update employees set salary = salary * (1 + v_temp) where employee_id = v_id;
fetch emp_sal_cursor into v_sal, v_id;
end loop;
--关闭游标 close emp_sal_cursor;
end;

利用 for 循环遍历游标

使用 for 遍历游标的话就不用再打开游标或者关闭游标了，相关操作会自动进行

练习4

同样用上述练习3的例子

declare --定义游标 cursor emp_sal_cursor is select salary, employee_id id from employees;
--定义基数变量 v_temp number(4, 2);
begin --处理游标的循环操作 for c in emp_sal_cursor loop --判断员工的工资, 执行 update 操作 if c.salary <= 5000 then v_temp := 0.05;
elsif c.salary <= 10000 then v_temp := 0.03;
elsif c.salary <= 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;
--dbms_output.put_line(v_id || '
: '
|| v_sal || '
, '
|| temp);
update employees set salary = salary * (1 + v_temp) where employee_id = c.id;
end loop;
end;

带参数的游标

同样用上述例子说明：

declare --定义游标 cursor emp_sal_cursor(dept_id number, sal number) is select salary + 1000 sal, employee_id id from employees where department_id = dept_id and salary > sal;
--定义基数变量 v_temp number(4, 2);
begin --处理游标的循环操作sal => 4000表示4000赋值给sal，这个是形参变量的复制操作，不是比较运算 for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop --判断员工的工资, 执行 update 操作 --dbms_output.put_line(c.id || '
: '
|| c.sal);
if c.sal <= 5000 then v_temp := 0.05;
elsif c.sal <= 10000 then v_temp := 0.03;
elsif c.sal <= 15000 then v_temp := 0.02;
else v_temp := 0.01;
end if;
update employees set salary = salary * (1 + v_temp) where employee_id = c.id;
end loop;
end;

6.异常的处理(三种)

预定义异常

在预定义异常的表中能找到的异常

练习1

declare v_sal employees.salary%type;
begin select salary into v_sal from employees where employee_id >100;
dbms_output.put_line(v_sal);
exception when Too_many_rows then dbms_output.put_line('
输出的行数太多了'
);
end;

非预定义异常

在预定义异常表中没有的

练习2

declare  v_sal employees.salary%type;  --声明一个异常  delete_mgr_excep exception;  --把自定义的异常和oracle的错误代码号关联起来  PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);begin  delete from employees  where employee_id = 100;  select salary into v_sal  from employees  where employee_id >100;  dbms_output.put_line(v_sal);exception  when Too_many_rows then dbms_output.put_line('输出的行数太多了');  when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');end;

相关热词搜索：

上一篇：整理navicat遇到的问题（navicatdesignquery.sql.bak系统找不到指定路径）
下一篇：sql语句_自连接_外连接_左连接_子查询_排序_分组

PLSQL语法&amp;&amp;游标&amp;&amp;存储过程/存储函数&amp;&amp;异常&amp;&amp;触发器 类别：MsSql数据库 作者：码皇 来源：<a href="http://blog.csdn.net/rocky_03" target="_blank" rel="nofollow&qu 点击：