PL-SQL經典試題
- 準備工作:
set serveroutput on
hellowrold 程序
begin
dbms_output.put_line('hello world');
end;
/
[語法格式]
--declare
??--聲明的變量、類型、游標
begin
??--程序的執行部分（類似于java里的main()方法）
??dbms_output.put_line('helloworld');
--exception
??--針對begin塊中出現的異常，提供處理的機制
??--when .... then ...
??--when ?.... then ...
end;
*******************************************************************************
基本語法
*******************************************************************************
- 使用一個變量
declare
??--聲明一個變量
??v_name varchar2(25);
begin
??--通過 select ... into ... 語句為變量賦值
?select last_name into v_name
?from employees
?where employee_id = 186;
?-- 打印變量的值
?dbms_output.put_line(v_name);
end;
- 使用多個變量
declare
??--聲明變量
??v_name varchar2(25);
??v_email varchar2(25);
??v_salary number(8, 2);
??v_job_id varchar2(10);
begin
??--通過 select ... into ... 語句為變量賦值
??--被賦值的變量與SELECT中的列名要一一對應
?select last_name, email, salary, job_id into v_name, v_email, v_salary, v_job_id
?from employees
?where employee_id = 186;
?-- 打印變量的值
?dbms_output.put_line(v_name || ', ' || v_email || ', ' || ?v_salary || ', ' || ?v_job_id);
end;
----------------------------------------------------------------
記錄類型
----------------------------------------------------------------
3.1 自定義記錄類型
declare
??--定義一個記錄類型
??type customer_type is record(
????v_cust_name varchar2(20),
????v_cust_id number(10));
??--聲明自定義記錄類型的變量
??v_customer_type customer_type;
begin
??v_customer_type.v_cust_name := '劉德華';
??v_customer_type.v_cust_id := 1001;
??dbms_output.put_line(v_customer_type.v_cust_name||','||v_customer_type.v_cust_id);
end;
3.2 自定義記錄類型
declare
??--定義一個記錄類型
??type emp_record is record(
????v_name varchar2(25),
????v_email varchar2(25),
????v_salary number(8, 2),
????v_job_id varchar2(10));
??--聲明自定義記錄類型的變量
??v_emp_record emp_record;
begin
??--通過 select ... into ... 語句為變量賦值
?select last_name, email, salary, job_id into v_emp_record
?from employees
?where employee_id = 186;
?-- 打印變量的值
?dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' || ?v_emp_record.v_salary || ', ' || ?v_emp_record.v_job_id);
end;
- 使用 %type 定義變量，動態的獲取數據的聲明類型
declare
??--定義一個記錄類型
??type emp_record is record(
????v_name employees.last_name%type,
????v_email employees.email%type,
????v_salary employees.salary%type,
????v_job_id employees.job_id%type);
??--聲明自定義記錄類型的變量
??v_emp_record emp_record;
begin
??--通過 select ... into ... 語句為變量賦值
?select last_name, email, salary, job_id into v_emp_record
?from employees
?where employee_id = 186;
?-- 打印變量的值
?dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' || ?v_emp_record.v_salary || ', ' || ?v_emp_record.v_job_id);
end;
- 使用 %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;
6.1 賦值語句：通過變量實現查詢語句
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;
6.2 ?通過變量實現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;
*******************************************************************************
流程控制
*******************************************************************************
-----------------------------------------------------
條件判斷
-----------------------------------------------------
- 使用 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;
7+ 使用 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;
- 使用 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;
-----------------------------------------------------
循環結構
-----------------------------------------------------
- 使用循環語句打印 1 - 100.（三種方式）
1). ?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;
2). 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;
3).
begin
???????for i in 1 .. 100 loop
?????????????dbms_output.put_line(i);
???????end loop;
end;
- 綜合使用 if, while 語句, 打印 1 - 100 之間的所有素數
(素數: 有且僅用兩個正約數的整數, 2, 3, 5, 7, 11, 13, ...).
declare
??v_flag number(1):=1;
??v_i number(3):=2;
??v_j number(2):=2;
begin
??while (v_i<=100) loop
????????while v_j <= sqrt(v_i) loop
??????????????if (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_flag :=1;
????????v_j := 2;
????????v_i :=v_i +1;
???end loop;
end;
(法二)使用for循環實現1-100之間的素數的輸出
declare
??--標記值, 若為 1 則是素數, 否則不是
??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;
???????????end if; ???????
???????end loop;
???????if v_flag = 1 then
???????????dbms_output.put_line(i);
???????end if;
???end loop;
end;
- 使用 goto
declare
??--標記值, 若為 1 則是素數, 否則不是
??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;
11+.打印1——100的自然數，當打印到50時，跳出循環，輸出“打印結束”
(方法一)
begin
??for i in 1..100 loop
??????dbms_output.put_line(i);
??????if(i = 50) then
??????goto label;
??????end if;
??end loop;
??????<<label>>
??????dbms_output.put_line('打印結束');
end;
(方法二)
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;
******************************************************************************游標的使用
*******************************************************************************12.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;
12.2 使用游標
要求: 打印出 80 部門的所有的員工的工資: Xxx 's salary is: xxx
declare
??cursor sal_cursor is select salary ,last_name from employees where department_id = 80;
??v_sal number(10);
??v_name varchar2(20);
begin
??open sal_cursor;
??fetch sal_cursor into v_sal,v_name;
??while sal_cursor%found loop
????????dbms_output.put_line(v_name||'`s salary is '||v_sal);
????????fetch sal_cursor into v_sal,v_name;
??end loop;
??close sal_cursor;
end;
- 使用游標的練習:
打印出 manager_id 為 100 的員工的 last_name, email, salary 信息(使用游標, 記錄類型)
declare ?
???????????--聲明游標 ???
???????????cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;
???????????--聲明記錄類型
???????????type emp_record is record(
????????????????name employees.last_name%type,
????????????????email employees.email%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(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary ); ???????????????
??????????????????fetch emp_cursor into v_emp_record;
???????????end loop;
???????????--關閉游標
???????????close emp_cursor;
end;
(法二：使用for循環)
declare
??????cursor emp_cursor is
??????select last_name,email,salary
??????from employees
??????where manager_id = 100;
begin
??????for v_emp_record in emp_cursor loop
??????????dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);
??????end loop;
end;
- 利用游標, 調整公司中員工的工資:
????工資范圍 ??????調整基數
????0 - 5000 ??????5%
????5000 - 10000 ??3%
????10000 - 15000 ?2%
????15000 - ???????1%
declare
????--定義游標
????cursor emp_sal_cursor is select salary, employee_id from employees;
????--定義基數變量
????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
?????????????temp := 0.05;
??????????elsif v_sal<= 10000 then
?????????????temp := 0.03; ??
??????????elsif v_sal <= 15000 then
?????????????temp := 0.02;
??????????else
?????????????temp := 0.01;
??????????end if;
??????????--dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
??????????update employees set salary = salary * (1 + temp) where employee_id = v_id;
??????????fetch emp_sal_cursor into v_sal, v_id;
????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 循環完成 14.
declare
????--定義游標
????cursor emp_sal_cursor is select salary, employee_id id from employees;
????--定義基數變量
????temp number(4, 2);
begin
????--處理游標的循環操作
????for c in emp_sal_cursor loop
??????????--判斷員工的工資, 執行 update 操作
??????????--dbms_output.put_line(c.employee_id || ': ' || c.salary);
??????????if c.salary <= 5000 then
?????????????temp := 0.05;
??????????elsif c.salary <= 10000 then
?????????????temp := 0.03; ??
??????????elsif c.salary <= 15000 then
?????????????temp := 0.02;
??????????else
?????????????temp := 0.01;
??????????end if;
??????????--dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
??????????update employees set salary = salary * (1 + temp) where employee_id = c.id;
????end loop;
end;
16*. 帶參數的游標
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;
????--定義基數變量
????temp number(4, 2);
begin
????--處理游標的循環操作
????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
?????????????temp := 0.05;
??????????elsif c.sal <= 10000 then
?????????????temp := 0.03; ??
??????????elsif c.sal <= 15000 then
?????????????temp := 0.02;
??????????else
?????????????temp := 0.01;
??????????end if;
??????????dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
??????????--update employees set salary = salary * (1 + temp) where employee_id = c.id;
????end loop;
end;
- 隱式游標: 更新指定員工 salary(漲工資 10)，如果該員工沒有找到，則打印”查無此人” 信息
begin
?????????update employees set salary = salary + 10 where employee_id = 1005;
?????????if sql%notfound then
????????????dbms_output.put_line('查無此人!');
?????????end if;
end;
*******************************************************************************
異常處理
*******************************************************************************
[預定義異常]
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;
[非預定義異常]
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;
[用戶自定義異常]
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;
??if v_sal > 1000 then
?????raise too_high_sal;
??end if;
??delete 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不能直接被刪除');
??--處理異常
??when too_high_sal then dbms_output.put_line('工資過高了');
end;
- 異常的基本程序:
通過 select ... into ... 查詢某人的工資, 若沒有查詢到, 則輸出 "未找到數據"
declare
??--定義一個變量
??v_sal employees.salary%type;
begin
??--使用 select ... into ... 為 v_sal 賦值
??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;
或
declare
??--定義一個變量
??v_sal employees.salary%type;
begin
??--使用 select ... into ... 為 v_sal 賦值
??select salary into v_sal from employees;
??dbms_output.put_line('salary: ' || v_sal);
exception
??when No_data_found then
???????dbms_output.put_line('未找到數據!');
??when Too_many_rows then
???????dbms_output.put_line('數據過多!'); ????
end;
- 更新指定員工工資，如工資小于300，則加100；對 NO_DATA_FOUND 異常, TOO_MANY_ROWS 進行處理.
declare
???v_sal employees.salary%type;
begin
???select salary into v_sal from employees where employee_id = 100;
???if(v_sal < 300) then update employees set salary = salary + 100 where employee_id = 100;
???else dbms_output.put_line('工資大于300');
???end if;
exception
???when no_data_found then dbms_output.put_line('未找到數據');
????when too_many_rows then dbms_output.put_line('輸出的數據行太多');
end;
- 處理非預定義的異常處理: "違反完整約束條件"
declare
??--1. 定義異常
??temp_exception exception;
??--2. 將其定義好的異常情況，與標準的 ORACLE 錯誤聯系起來，使用 EXCEPTION_INIT 語句
??PRAGMA EXCEPTION_INIT(temp_exception, -2292);
begin
??delete from employees where employee_id = 100;
exception
??--3. 處理異常
??when temp_exception then
???????dbms_output.put_line('違反完整性約束!');
end;
- 自定義異常: 更新指定員工工資，增加100；若該員工不存在則拋出用戶自定義異常: no_result
declare
??--自定義異常 ??????????????????????????????????
??no_result exception; ??
begin
??update employees set salary = salary + 100 where employee_id = 1001;
??--使用隱式游標, 拋出自定義異常
??if sql%notfound then
?????raise no_result;
??end if; ?
exception
??--處理程序拋出的異常
??when no_result 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
--函數體(可以實現增刪改查等操作，返回值需要return)
???????return 'helloworld'|| v_param;
end;
22.1 函數的 helloworld: 返回一個 "helloworld" 的字符串
create or replace function hello_func
return varchar2
is
begin
???????return 'helloworld';
end;
執行函數
begin
????dbms_output.put_line(hello_func());
end;
或者： select hello_func() from dual;
22.2 返回一個"helloworld: atguigu"的字符串，其中atguigu 由執行函數時輸入。
--函數的聲明(有參數的寫在小括號里)
create or replace function hello_func(v_logo varchar2)
--返回值類型
return varchar2
is
--PL/SQL塊變量的聲明
begin
--函數體
???????return 'helloworld'|| v_logo;
end;
22.3 創建一個存儲函數，返回當前的系統時間
create or replace function func1
return date
is
--定義變量
v_date date;
begin
-函數體
--v_date := sysdate;
????select sysdate into v_date from dual;
???????dbms_output.put_line('我是函數哦');
???????return v_date;
end;
執行法1：
select func1 from dual;
執行法2：
declare
??v_date date;
begin
??v_date := func1;
??dbms_output.put_line(v_date);
end;
- 定義帶參數的函數: 兩個數相加
create or replace function add_func(a number, b number)
return number
is
begin
???????return (a + b);
end;
執行函數
begin
????dbms_output.put_line(add_func(12, 13));
end;
或者
????select add_func(12,13) from dual;
- 定義一個函數: 獲取給定部門的工資總和, 要求:部門號定義為參數, 工資總額定義為返回值.
create or replace function sum_sal(dept_id number)
???????return number
???????is
???????cursor sal_cursor is select salary from employees where department_id = dept_id;
???????v_sum_sal number(8) := 0; ??
begin
???????for c in sal_cursor loop
???????????v_sum_sal := v_sum_sal + c.salary;
???????end loop; ??????
???????--dbms_output.put_line('sum salary: ' || v_sum_sal);
???????return v_sum_sal;
end;
執行函數
begin
????dbms_output.put_line(sum_sal(80));
end;
- 關于 OUT 型的參數: 因為函數只能有一個返回值, PL/SQL 程序可以通過 OUT 型的參數實現有多個返回值
要求: 定義一個函數: 獲取給定部門的工資總和 和 該部門的員工總數(定義為 OUT 類型的參數).
要求: 部門號定義為參數, 工資總額定義為返回值.
create or replace function sum_sal(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; ??????
???????--dbms_output.put_line('sum salary: ' || v_sum_sal);
???????return v_sum_sal;
end; ??
執行函數:
delare
??v_total number(3) := 0;
begin
????dbms_output.put_line(sum_sal(80, v_total));
????dbms_output.put_line(v_total);
end;
26*. 定義一個存儲過程: 獲取給定部門的工資總和(通過 out 參數), 要求:部門號和工資總額定義為參數
create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)
???????is
???????cursor sal_cursor is select salary from employees where department_id = dept_id;
begin
???????v_sum_sal := 0;
???????for c in sal_cursor loop
???????????--dbms_output.put_line(c.salary);
???????????v_sum_sal := v_sum_sal + c.salary;
???????end loop; ??????
???????dbms_output.put_line('sum salary: ' || v_sum_sal);
end;
[執行]
declare
?????v_sum_sal number(10) := 0;
begin
?????sum_sal_procedure(80,v_sum_sal);
end;
27*. 自定義一個存儲過程完成以下操作:
對給定部門(作為輸入參數)的員工進行加薪操作, 若其到公司的時間在
(? , 95) 期間, ???為其加薪 %5
[95 , 98)?????????????????%3 ??????????????????????????????????????????????????????????????????[98, ?) ?????????????????%1
得到以下返回結果: 為此次加薪公司每月需要額外付出多少成本(定義一個 OUT 型的輸出參數).
create or replace procedure add_sal_procedure(dept_id number, temp out number)
is
???????cursor sal_cursor is select employee_id id, hire_date hd, salary sal from employees where department_id = dept_id;
???????a number(4, 2) := 0;
begin
???????temp := 0; ??????
???????for c in sal_cursor loop
???????????a := 0; ???
???????????if c.hd < to_date('1995-1-1', 'yyyy-mm-dd') then
??????????????a := 0.05;
???????????elsif c.hd < to_date('1998-1-1', 'yyyy-mm-dd') then
??????????????a := 0.03;
???????????else
??????????????a := 0.01;
???????????end if;
???????????temp := temp + c.sal * a;
???????????update employees set salary = salary * (1 + a) where employee_id = c.id;
???????end loop; ??????
end;
*******************************************************************************
觸發器
*******************************************************************************
一個helloworld級別的觸發器
create or replace trigger hello_trigger
after
update on employees
--for each row
begin
????dbms_output.put_line('hello...');
????--dbms_output.put_line('old.salary:'|| :OLD.salary||',new.salary'||:NEW.salary);
end;
然后執行：update employees set salary = salary + 1000;
- 觸發器的 helloworld: 編寫一個觸發器, 在向 emp 表中插入記錄時, 打印 'helloworld'
create or replace trigger emp_trigger
after
insert on emp
for each row
begin
???????dbms_output.put_line('helloworld');
end;
- 行級觸發器: 每更新 employees 表中的一條記錄, 都會導致觸發器執行
create or replace trigger employees_trigger
after
update on employees
for each row
begin
???????dbms_output.put_line('修改了一條記錄!');
end;
語句級觸發器: 一個 update/delete/insert 語句只使觸發器執行一次
create or replace trigger employees_trigger
after
update on employees
begin
???????dbms_output.put_line('修改了一條記錄!');
end;
- 使用 :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;
- 編寫一個觸發器, 在對 my_emp 記錄進行刪除的時候, 在 my_emp_bak 表中備份對應的記錄
1). 準備工作:
create table my_emp as select employee_id id, last_name name, salary sal from employees;
create table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2
2).
create or replace trigger bak_emp_trigger
???????before delete on my_emp
???????for each row
begin
???????insert into my_emp_bak values(:old.id, :old.name, :old.sal);
end;
