-- 1. 使用一个变量
declare
-- Local variables here
v_name varchar2(50);
begin
-- Test statements here
select t.user_name into v_name
from pay_mer_order t
where t.id=3530816;
dbms_output.put_line(v_name);
end;
-- 2. 使用多个变量
declare
-- Local variables here
v_name varchar2(50);
v_trans_no varchar2(50);
v_app_code varchar2(50);
begin
-- Test statements here
select t.user_name, t.pay_brh_trans_no, t.app_code
into v_name, v_trans_no, v_app_code
from pay_mer_order t
where t.id=3530816;
dbms_output.put_line(v_name || ',' || v_trans_no || ',' || v_app_code);
end;
--3. 自定义记录类型
declare
-- 自定义一个记录类型
type order_info is record(
v_name varchar2(50),
v_trans_no varchar2(50),
v_app_code varchar2(50));
--声明自定义记录类型的变量
v_tmp_record order_info;
begin
select t.user_name, t.pay_brh_trans_no, t.app_code
into v_tmp_record
from pay_mer_order t
where t.id=3530816;
dbms_output.put_line(v_tmp_record.v_name || ',' || v_tmp_record.v_trans_no || ',' || v_tmp_record.v_app_code);
end;
-- 4. 使用%type定义变量，动态的获取数据的声明类型
declare
-- 定义一个记录类型
type order_info is record(
v_name pay_mer_order.user_name%type,
v_trans_no pay_mer_order.pay_brh_trans_no%type,
v_app_code pay_mer_order.app_code%type);
--声明自定义记录类型的变量
v_tmp_record order_info;
begin
select t.user_name, t.pay_brh_trans_no, t.app_code
into v_tmp_record
from pay_mer_order t
where t.id=3530816;
dbms_output.put_line(v_tmp_record.v_name || ',' || v_tmp_record.v_trans_no || ',' || v_tmp_record.v_app_code);
end;
-- 5. 使用%rowtype定义变量，动态的获取数据的声明类型
declare
-- 声明一个记录类型的变量
v_tmp_record pay_mer_order%rowtype;
begin
select t.*
into v_tmp_record
from pay_mer_order t
where t.id=3530816;
dbms_output.put_line(v_tmp_record.user_name || ',' || v_tmp_record.pay_brh_trans_no || ',' || v_tmp_record.app_code);
end;
-- 6. 赋值语句：通过变量实现查询语句
declare
-- 声明一个记录类型的变量
v_tmp_record pay_mer_order%rowtype;
v_order_info_id pay_mer_order.id%type;
begin
v_order_info_id := 3530816;
select t.*
into v_tmp_record
from pay_mer_order t
where t.id=v_order_info_id;
dbms_output.put_line(v_tmp_record.user_name || ',' || v_tmp_record.pay_brh_trans_no || ',' || v_tmp_record.app_code);
end;
--7. 通过变量实现DELET, INSERT, UPDATE等操作
declare
-- 声明一个记录类型的变量
v_order_info_id pay_mer_order.id%type;
begin
v_order_info_id := 3530816;
delete
from pay_mer_order t
where t.id=v_order_info_id;
commit;
end;
--8. 使用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;
--(方法二)
declare
v_emp_name employees.last_name%type;
v_emp_sal employees.salary%type;
v_emp_sal_level varchar2(20);
begin
select last_name,salary into v_emp_name,v_emp_sal
from employees
where employee_id = 150;
if(v_emp_sal >= 10000) then
v_emp_sal_level := 'salary >= 10000';
elsif(v_emp_sal >= 5000) then
v_emp_sal_level := '5000<= salary < 10000';
else v_emp_sal_level := 'salary < 5000';
end if;
dbms_output.put_line(v_emp_name||','||v_emp_sal||','||v_emp_sal);
end;
--9. 使用 CASE ... WHEN ... THEN ...ELSE ... 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;
--10. 使用 CASE ... WHEN ... THEN ... ELSE ... 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;
--11. 使用循环语句打印1-100.（三种方式）
--a. 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;
--b. WHILE...LOOP...END LOOP
declare
v_i number(3) := 1;
while v_i <= 100 loop
dbms_output.put_line(v_i);
v_i := v_i + i;
end loop;
end;
--c. FOR...IN...LOOP
begin
for i in 1..100 loop
dbms_oupput_put_line(i);
end loop;
end;
--12. 综合使用if, while语句，打印1-100之间的所有素数
--（素数：有且公有两个正约数的整数，2， 3， 5，7，11，13...）
declare
v_i number(3) := 2;
v_j number(3) := 2;
v_flag number(1) := 0;
begin
while v_i < 101 loop
v_j := 2;
while v_j < v_i loop
if(mod(v_i, v_j) = 0) then
v_flag := 1;
end if;
exit when v_flag = 1;
end loop;
if(v_flag = 0) then
dbms_output.put_line(v_i);
end if;
v_i := v_i + 1;
end loop;
end;
--13. 使用for in实现12
declare
v_flag number(1) := 0;
begin
for i in 2..100 loop
for j in 2...i loop
if(mod(i, j) = 0) then
v_flag := 1;
end if;
exit when v_flag = 1;
end loop;
if(v_flag = 0) then
dbms_output.put_line(i);
end if;
end loop;
end;
--14. goto
declare
v_flag number(1) := 0;
begin
for i in 2..100 loop
v_flag := 1;
for j in 2..sqrt(i) loop
if i mod j = 0 then
v_flag := 0;
goto label;
end if;
end loop;
<<label>>
if v_flag = 1 then
dbms_output.put_line(i);
end if;
end loop;
end;
----------------------------------------------------------------------------
--游标的使用
--游标1. 打印出80部门的所有的员工的工资
declare
--a. 定义游标
cursor salary_cursor is select salary from employees where department_i = 80;
v_salary employees.salary%type;
begin
--b. 打开游标
open salary_cursor;
--c. 提取游标
fetch salary_cursor into v_salary;
--d. 对游标进行循环操作：判断游标中是否有下一条记录
while salary_cursor%found loop
dbms_output.put_line('salary: ' || v_salary);
fetch salary_cursor into v_salary;
end loop;
--e. 关闭游标
close salary_cursor;
end;
--游标2. 打印出80部门的所有员工的工资：Xxx's salary is: xxx
declare
cursor salary_cursor is select e.salary, e.last_name from employees e where department_i = 80;
v_sal number(10);
v_name varchar2(20);
begin
open salary_cursor;
fetch salray_cursor into v_sal, v_name;
while salary_cursor%found loop
dbms_output.put_line(v_name || '、s salary is: ' || v_sal);
fetch salary_cursor into v_sal, v_name;
end loop;
close salary_cursor;
end;
--游标3：打印同manage_id为100的员工的last_name, email, salary信息（使用游标，记录类型）
declare
type v_emplyee is record(
name emplyees.last_name%type,
mail emplyees.email%type,
sal emplyees.salary%type);
v_employee_info v_employee;
cursor salary_cursor is select e.last_name, e.email, e.salary from employees e where e.manage_id = 100;
begin
open salary_cursor;
fetch salary_cursor into v_emplyeee_info;
while salary_cursor%found loop
dbms_output.put_line(v_employee_info.name || '、s salary is: ' || v_employee_info.mail ', email is: ' || v_employee_info.mail);
fetch salary_cursor into v_employee_info;
end loop;
close salary_cursor;
end;
--游标4：使用for .. in
declare
type v_emplyee is record(
name emplyees.last_name%type,
mail emplyees.email%type,
sal emplyees.salary%type);
v_employee_info v_employee;
cursor salary_cursor is select e.last_name, e.email, e.salary from employees e where e.manage_id = 100;
begin
for v_employee_info in salary_cursor loop
dbms_output.put_line(v_employee_info.name || '、s salary is: ' || v_employee_info.mail ', email is: ' || v_employee_info.mail);
end loop;
end;
--游标5：利用游标，调整公司中员工的工资
--0~5000 5%, 5000~10000 3%, 10000~15000 2%, 150~ 1%
declare
salary_info employees&rowtype
cursor salary_adjust_cursor is select e.* from employees
v_adjust number(4, 2);
begin
for salary_info in salary_adjust_cursor loop
if(salary_info.salary <= 5000) then
v_adjust := 0.05;
elsif(salary_info.salary <= 10000) then
v_adjust := 0.03;
elsif(salary_info.salary <= 15000) then
v_adjust := 0.02;
else
v_adjust := 0.01;
end if;
update employees e set e.salary=(s.salary + e.salary * v_adjust) where t.id = salary_info.id;
end loop;
end;
--游标6：带参数的游标
declare
salary_info employees&rowtype
cursor salary_adjust_cursor(dept_id number, sal number) is
select salary + 1000 sal, employee_id id from employees e
where t.department_id = dept_id and salary > sal;
v_adjust number(4, 2);
begin
for salary_info in salary_adjust_cursor(sal => 4000, dept_id => 80) loop
if(salary_info.salary <= 5000) then
v_adjust := 0.05;
elsif(salary_info.salary <= 10000) then
v_adjust := 0.03;
elsif(salary_info.salary <= 15000) then
v_adjust := 0.02;
else
v_adjust := 0.01;
end if;
update employees e set e.salary=(s.salary + e.salary * v_adjust) where t.id = salary_info.id;
end loop;
end;
--游标7：隐式游标，更新指定员工salary涨10%,如果该员工没找到，则打印“查无此人”
begin
update employees e set salary = salary + salary * 0.1
where e.employees_id = 1055;
if sql%notfound then
dbms_output.put_line('查无此人！');
end if;
end;
----------------------------------------------------------------------------
--异常处理1
declare
v_sal employees.salary%type;
begin
select salary into v_sal
from employees e where e.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 e where e.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;
--用户自定义异常3
declare
v_sal employees.salary%type;
--声明一个异常
delete_mgr_excep exception;
--把自定义的异常和oracle的错误关联起来
PRAGMA EXCEPTION INIT(delete_mgr_excep, -2292);
--声明一个异常
too_high_sal exception;
begin
select salary into v_sal
from employees where employee_id > 100;
dbms_output.put_line(v_sal);
if(v_sql > 1000) then
raise too_high_sal;
end if;
exception
when Too_many_rows then dbms_output.put_line('输出的行数太多了');
when delete_mgr_excep then dbms_output.put_line('Manager不能直接被删除');
--处理异常
when too_high_sal then dbms_output.put_line('工资过高了');
end;
--异常的基本程序4
declare
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id=1000;
dbms_output.put_line('salary: ' || v_sal);
exception when No_data_found then dbms_output.put_line('未找到数据');
end;
----------------------------------------------------------------------------
--存储过程和函数
--存储函数：有返回值，创建完成后，通过select function() from dual;执行
--存储过程：由于没有返回值，创建完成后，不能使用select语句，只能使用pl/sql块执行
--函数的声明（有参数的写在小括号里）
create or replace function func_name(v_param varchar2)
--返回值类型
return varchar2 is
--PL/SQL块变量、记录类型、游标的声明（类似于前面的declare的部分）
begin
--函数体（可以实现crud操作，返回值需要return)
return 'hello world' || v_param;
end;
--存储函数1：helloworld
create or replace function hello_func
return varchar2 is
begin
return 'hello world';
end;
--执行函数
begin
dbms_output.put_line(hello_func());
end;
--或者
select hello_func() from dual;
--存储函数2：参数输入
create or replace function hello_func(v_logo varchar2)
return varchar2
is
begin
return 'hello world ' || v_logo;
end;
--存储函数3：使用OUT型的参数，因为函数只能有一个返回值，PL/SQL程序可以通过OUT型的参数实现多个返回值
--要求: 定义一个函数: 获取给定部门的工资总和 和 该部门的员工总数(定义为 OUT 类型的参数).
--要求: 部门号定义为参数, 工资总额定义为返回值.
create or replace function sum_sql(dept_id number, total_count out number)
return number
is
cursor sal_cursor is select salary from employees
where department_id = dept_id;
v_sum_sal number(8) := 0;
begin
total_count := 0;
for c in sal_cursor loop
v_sum_sal := v_sum_sal + c.salary;
total_count := total_count + 1;
end loop;
return v_sum_sal;
end;
--执行函数
declare v_total number(3) := 0;
begin
dbms_output.put_line(sum_sal(80, v_total));
dbms_output.put_line(v_total);
end;
----------------------------------------------------------------------------
--触发器1：helloworld触发器
create or replace trigger hello_trigger
after update on employees
begin
dbms_output.put_line('hello world..');
end;
--在执行以下更新语句之后会打出hello world
update employees set salary = salary + 1000;
--触发嚣2：行触发器
create or replace trigger employees_trigger
after update on employees for each row
begin
dbms_output.put_line('修改了一条记录');
end;
--触发嚣2：语句级触发器：一个update/delete/insert语句只使触发器执行一次
create or replace trigger employees_trigger
after update on employees
begin
dbms_output.put_line('修改了一条记录');
end;
--触发嚣3：使用:new, :old修饰符
create or replace trigger employees_trigger
after update on employees for each row
begin
dbms_output.put_line('old salary: ' || :old.salary || ', new salary: ' || :new.salary);
end;