一、基本语法
使用set serveroutput on 命令设置环境变量serveroutput为打开状态,从而使得pl/sql程序能够在SQL*plus中输出结果
使用函数dbms_output.put_line()可以输出参数的值。
set serveroutput ondeclare--声明变量v_sal employees.salary%type;v_email employees.email%type;v_hire_date employees.hire_date%type;
begin--sql语句的操作:select...into...from...where...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);
end;
二、流程控制
--用while循环求2-100之间的质数(素数)
declarev_i number(3) := 2;v_j number(3) := 2;v_flag number(1) := 1;
beginwhile v_i <= 100 loop
--为什么是 j <= i/j 例如 i = 20,那么可能整除的组合就是 2 * 10、4 * 5、5 * 4、10 * 2。
--可以发现组合是重复的了一次的
--所以只要 j <= i/j 就能试完所有的情况,即j<=sqrt(i) while v_j <= sqrt(v_i) loopif mod(v_i,v_j) = 0 then v_flag := 0;end if;v_j := v_j + 1;end loop;if v_flag = 1 then dbms_output.put_line(v_i);end if;v_j := 2;v_i := v_i + 1;v_flag := 1;end loop;end;
--用for循环求2-100之间的质数(素数)
declarev_flag number(1) := 1;
beginfor v_i in 2..100 loopfor v_j in 2..sqrt(v_i) loopif mod(v_i,v_j) = 0 then v_flag := 0;goto label;end if;end loop;<<label>>if v_flag = 1 then dbms_output.put_line(v_i);end if;v_flag := 1;end loop;end;
--打印1-100的自然数,当打印到50时,跳出循环,输出“打印结束”
beginfor i in 1..100 loopif i = 50 then goto label;end if;dbms_output.put_line(i);end loop;<<label>>dbms_output.put_line('打印结束');
end;
三、游标
游标是一个指向上下文的句柄( handle)或指针。
--打印出80号部门所有员工的员工号和工资:empid:xxx salary:xxx
declare--声明一个记录类型type emp_record is record(v_sal employees.salary%type,v_empid employees.employee_id%type);--声明一个记录类型的变量v_emp_record emp_record;--定义游标cursor emp_sal_cursor is select salary,employee_id from employees where department_id = 80;
begin--打开游标open emp_sal_cursor;--提取游标fetch emp_sal_cursor into v_emp_record;while emp_sal_cursor%found loopdbms_output.put_line('empid:'||v_emp_record.v_empid||' salary:'||v_emp_record.v_sal);fetch emp_sal_cursor into v_emp_record;end loop;--关闭游标close emp_sal_cursor;
end;
/*利用游标,调整公司中员工的工资:工资范围 调整基数0-5000 5%5000-10000 3%10000-15000 2%15000- 1%
*/
declarecursor emp_sal_cursor is select employee_id,salary from employees;v_temp number(4,2);v_empid employees.employee_id%type;v_sal employees.salary%type;beginopen emp_sal_cursor;fetch emp_sal_cursor into v_empid,v_sal;while emp_sal_cursor%found loopif 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;dbms_output.put_line(v_empid||','||v_sal);update employeesset salary = salary * (1 + v_temp)where employee_id = v_empid;fetch emp_sal_cursor into v_empid,v_sal;end loop;close emp_sal_cursor;
end;
--使用sql中的decode函数实现
update employees
set salary = salary * (1 + (decode(trunc(salary/5000),0,0.05,1,0.03,2,0.02,0.01)))
--使用for循环实现
declarecursor emp_sal_cursor is select employee_id,salary from employees;v_temp number(4,2);beginfor c in emp_sal_cursor loopif 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;update employeesset salary = salary * (1 + v_temp)where employee_id = c.employee_id; end loop;end;
四、异常处理机制
1.预定义的异常处理
declarev_salary employees.salary%type;
beginselect salary into v_salaryfrom employeeswhere employee_id > 100;dbms_output.put_line(v_salary);
exceptionwhen too_many_rows then dbms_output.put_line('输出的行数太多了!!');when others then dbms_output.put_line('出现其他类型的异常');
end;
--通过select...into...查询某人的工资。若没有查询到,则输出“未找到数据”
declarev_sal employees.salary%type;
beginselect salary into v_sal from employees where employee_id = 1001;dbms_output.put_line(v_sal);
exceptionwhen no_data_found then dbms_output.put_line('未找到数据');
end;
2.非预定义的异常处理
declaree_deleteid_exception exception;pragma exception_init(e_deleteid_exception,-02292);
begindelete from employees where employee_id = 100;
exceptionwhen e_deleteid_exception then dbms_output.put_line('违反完整性约束条件,故不可删除此用户');
end;
3.用户自定义的异常处理
--查询员工号为100的员工工资,如工资大于10000,抛出异常并进行异常处理:"工资太高了!!"
declaree_too_high_sal exception;v_sal employees.salary%type;beginselect salary into v_sal from employees where employee_id = 100;if v_sal > 10000 thenraise e_too_high_sal;end if;exceptionwhen e_too_high_sal then dbms_output.put_line('工资太高了!!');end;
*五、存储函数和存储过程
过程和函数的唯一区别是函数总向调用者返回数据,而过程则不返回数据。
1.存储函数(有返回值)
--定义一个函数hello_world:返回一个"helloworld"的字符串
create or replace function hello_world
return varchar2
is
beginreturn 'helloworld';
end;
--定义一个函数:获取给定部门的工资总和。要求:部门号定义为参数,工资总额定义为返回值
create or replace function get_sal(dept_id number)
return number
isv_sumsal number(10) := 0;cursor salary_cursor is select salary from employees where department_id = dept_id;beginfor c in salary_cursor loopv_sumsal := v_sumsal + c.salary;end loop;return v_sumsal;
end;
函数名后面是一个可选的参数列表,其中包含 IN、OUT 或 IN OUT 标记。参数之间用逗号隔开。
IN 参数标记表示传递给函数的值在该函数执行中不改变;
OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句;
IN OUT 标记表示传递给函数的值可以变化并传递给调用语句。
若省略标记,则参数隐含为 IN。
--定义一个函数:获取给定部门的工资总和 和 该部门的员工总数(定义为OUT类型的参数)。
--要求:部门号定义为参数,工资总额定义为返回值
create or replace function get_sal1(dept_id number,total_count out number)
return number
isv_sumsal number(10) := 0;cursor salary_cursor is select salary from employees where department_id = dept_id;begintotal_count := 0;for c in salary_cursor loopv_sumsal := v_sumsal + c.salary;total_count := total_count + 1;end loop;return v_sumsal;
end;
----------------------------------------------declarev_num number(5) := 0;
begindbms_output.put_line(get_sal1(80,v_num));dbms_output.put_line(v_num);
end;
2.存储过程(没有返回值)
--定义一个存储过程:获取给定部门的工资总和(通过OUT函数)。要求:部门号和工资总额定义为参数
create or replace procedure get_sal2(dept_id number,sumsal out number)
iscursor salary_cursor is select salary from employees where department_id = dept_id;
beginsumsal := 0;for c in salary_cursor loopsumsal := sumsal + c.salary;end loop;dbms_output.put_line(sumsal);
end;
----------------------------------------------
declarev_sal number(10) := 0;
beginget_sal2(80,v_sal);
end;
/*
自定义一个存储过程完成以下操作:
对给定部门(作为输入参数)的员工进行加薪操作, 若其到公司的时间在 (? , 95) 期间,为其加薪 %5[95 , 98) %3 [98, ?) %1
得到以下返回结果: 为此次加薪公司每月需要额外付出多少成本(定义一个 OUT 型的输出参数).
*/
create or replace procedure add_sal(dept_id number,temp_sal out number)
iscursor sal_cursor is select employee_id,salary,hire_date from employees where department_id = dept_id;v_i number(4,2) := 0;
begintemp_sal := 0;for c in sal_cursor loopif to_char(c.hire_date,'yyyy') < '1995' then v_i := 0.05;elsif to_char(c.hire_date,'yyyy') < '1998' then v_i := 0.03;else v_i := 0.01;end if;--1.更新工资update employees set salary = salary * (1 + v_i) where employee_id = c.employee_id;--2.公司每月需额外付出成本temp_sal := temp_sal + c.salary * v_i;end loop;dbms_output.put_line(temp_sal);
end;
----------------------------------------------
declarev_temp number(10);
beginadd_sal(80,v_temp);
end;
*六、触发器
1.修饰符:NEW :OLD
:NEW 修饰符访问操作完成后列的值
:OLD 修饰符访问操作完成前列的值
--编写一个触发器,在对my_emp记录进行删除的时候,在my_emp_bak表中备份对应的记录
1)准备工作:
create table my_emp as select employee_id id, last_name name, salary sal from employeescreate table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2
2)编写触发器
create or replace trigger delete_emp_trigger
before delete on my_emp
for each row
begininsert into my_emp_bakvalues(:old.id,:old.name,:old.sal);
end;