PL-SQL經(jīng)典試題
- 準(zhǔn)備工作:
set serveroutput on
hellowrold 程序
begin
dbms_output.put_line('hello world');
end;
/
[語(yǔ)法格式]
--declare
??--聲明的變量、類(lèi)型、游標(biāo)
begin
??--程序的執(zhí)行部分（類(lèi)似于java里的main()方法）
??dbms_output.put_line('helloworld');
--exception
??--針對(duì)begin塊中出現(xiàn)的異常，提供處理的機(jī)制
??--when .... then ...
??--when ?.... then ...
end;
*******************************************************************************
基本語(yǔ)法
*******************************************************************************
- 使用一個(gè)變量
declare
??--聲明一個(gè)變量
??v_name varchar2(25);
begin
??--通過(guò) select ... into ... 語(yǔ)句為變量賦值
?select last_name into v_name
?from employees
?where employee_id = 186;
?-- 打印變量的值
?dbms_output.put_line(v_name);
end;
- 使用多個(gè)變量
declare
??--聲明變量
??v_name varchar2(25);
??v_email varchar2(25);
??v_salary number(8, 2);
??v_job_id varchar2(10);
begin
??--通過(guò) select ... into ... 語(yǔ)句為變量賦值
??--被賦值的變量與SELECT中的列名要一一對(duì)應(yīng)
?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;
----------------------------------------------------------------
記錄類(lèi)型
----------------------------------------------------------------
3.1 自定義記錄類(lèi)型
declare
??--定義一個(gè)記錄類(lèi)型
??type customer_type is record(
????v_cust_name varchar2(20),
????v_cust_id number(10));
??--聲明自定義記錄類(lèi)型的變量
??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 自定義記錄類(lèi)型
declare
??--定義一個(gè)記錄類(lèi)型
??type emp_record is record(
????v_name varchar2(25),
????v_email varchar2(25),
????v_salary number(8, 2),
????v_job_id varchar2(10));
??--聲明自定義記錄類(lèi)型的變量
??v_emp_record emp_record;
begin
??--通過(guò) select ... into ... 語(yǔ)句為變量賦值
?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 定義變量，動(dòng)態(tài)的獲取數(shù)據(jù)的聲明類(lèi)型
declare
??--定義一個(gè)記錄類(lèi)型
??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);
??--聲明自定義記錄類(lèi)型的變量
??v_emp_record emp_record;
begin
??--通過(guò) select ... into ... 語(yǔ)句為變量賦值
?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
--聲明一個(gè)記錄類(lèi)型的變量
??v_emp_record employees%rowtype;
begin
??--通過(guò) select ... into ... 語(yǔ)句為變量賦值
?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 賦值語(yǔ)句：通過(guò)變量實(shí)現(xiàn)查詢(xún)語(yǔ)句
declare
??v_emp_record employees%rowtype;
??v_employee_id employees.employee_id%type;
begin
??--使用賦值符號(hào)位變量進(jìn)行賦值
??v_employee_id := 186;
??--通過(guò) select ... into ... 語(yǔ)句為變量賦值
?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 ?通過(guò)變量實(shí)現(xiàn)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;
要求: 查詢(xún)出 150號(hào) 員工的工資, 若其工資大于或等于 10000 則打印 'salary >= 10000';
若在 5000 到 10000 之間, 則打印 '5000<= salary < 10000'; 否則打印 'salary < 5000'
(方法一)
declare
??v_salary employees.salary%type;
begin
??--通過(guò) select ... into ... 語(yǔ)句為變量賦值
?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 完成上面的任務(wù)
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;
要求: 查詢(xún)出 122 號(hào)員工的 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);
???????--根據(jù) 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;
-----------------------------------------------------
循環(huán)結(jié)構(gòu)
-----------------------------------------------------
- 使用循環(huán)語(yǔ)句打印 1 - 100.（三種方式）
1). ?LOOP ... EXIT WHEN ... END LOOP
declare
???????--初始化條件
???????v_i number(3) := 1;
begin
???????loop
???????--循環(huán)體
????????dbms_output.put_line(v_i);
--循環(huán)條件
????????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
???????--循環(huán)條件
???????while v_i <= 100 loop
?????--循環(huán)體
?????????????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 語(yǔ)句, 打印 1 - 100 之間的所有素?cái)?shù)
(素?cái)?shù): 有且僅用兩個(gè)正約數(shù)的整數(shù), 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循環(huán)實(shí)現(xiàn)1-100之間的素?cái)?shù)的輸出
declare
??--標(biāo)記值, 若為 1 則是素?cái)?shù), 否則不是
??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
??--標(biāo)記值, 若為 1 則是素?cái)?shù), 否則不是
??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的自然數(shù)，當(dāng)打印到50時(shí)，跳出循環(huán)，輸出“打印結(jié)束”
(方法一)
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('打印結(jié)束');
end;
(方法二)
begin
??for i in 1..100 loop
??????dbms_output.put_line(i);
??????if(i mod 50 = 0) then dbms_output.put_line('打印結(jié)束');
??????exit;
??????end if;
??end loop;
end;
******************************************************************************游標(biāo)的使用
*******************************************************************************12.1 使用游標(biāo)
要求: 打印出 80 部門(mén)的所有的員工的工資:salary: xxx
declare
??--1. 定義游標(biāo)
??cursor salary_cursor is select salary from employees where department_id = 80;
??v_salary employees.salary%type;
begin
?--2. 打開(kāi)游標(biāo)
?open salary_cursor;
?--3. 提取游標(biāo)
?fetch salary_cursor into v_salary;
?--4. 對(duì)游標(biāo)進(jìn)行循環(huán)操作: 判斷游標(biāo)中是否有下一條記錄
while salary_cursor%found loop
??????dbms_output.put_line('salary: ' || v_salary);
??????fetch salary_cursor into v_salary;
end loop; ?
?--5. 關(guān)閉游標(biāo)
?close ?salary_cursor;
end;
12.2 使用游標(biāo)
要求: 打印出 80 部門(mén)的所有的員工的工資: 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;
- 使用游標(biāo)的練習(xí):
打印出 manager_id 為 100 的員工的 last_name, email, salary 信息(使用游標(biāo), 記錄類(lèi)型)
declare ?
???????????--聲明游標(biāo) ???
???????????cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;
???????????--聲明記錄類(lèi)型
???????????type emp_record is record(
????????????????name employees.last_name%type,
????????????????email employees.email%type,
????????????????salary employees.salary%type
???????????);
???????????-- 聲明記錄類(lèi)型的變量
???????????v_emp_record emp_record;
begin
???????????--打開(kāi)游標(biāo)
???????????open emp_cursor;
???????????--提取游標(biāo)
???????????fetch emp_cursor into v_emp_record;
???????????--對(duì)游標(biāo)進(jìn)行循環(huán)操作
???????????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;
???????????--關(guān)閉游標(biāo)
???????????close emp_cursor;
end;
(法二：使用for循環(huán))
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;
- 利用游標(biāo), 調(diào)整公司中員工的工資:
????工資范圍 ??????調(diào)整基數(shù)
????0 - 5000 ??????5%
????5000 - 10000 ??3%
????10000 - 15000 ?2%
????15000 - ???????1%
declare
????--定義游標(biāo)
????cursor emp_sal_cursor is select salary, employee_id from employees;
????--定義基數(shù)變量
????temp number(4, 2);
????--定義存放游標(biāo)值的變量
????v_sal employees.salary%type;
????v_id employees.employee_id%type;
begin
????--打開(kāi)游標(biāo)
????open emp_sal_cursor;
????--提取游標(biāo)
????fetch emp_sal_cursor into v_sal, v_id;
????--處理游標(biāo)的循環(huán)操作
????while emp_sal_cursor%found loop
??????????--判斷員工的工資, 執(zhí)行 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;
????--關(guān)閉游標(biāo)
????close emp_sal_cursor;
end;
使用SQL中的 decode 函數(shù)
update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,
??????????????????????????????????????????????????????????1, 0.03,
??????????????????????????????????????????????????????????2, 0.02,
??????????????????????????????????????????????????????????0.01)))
- 利用游標(biāo) for 循環(huán)完成 14.
declare
????--定義游標(biāo)
????cursor emp_sal_cursor is select salary, employee_id id from employees;
????--定義基數(shù)變量
????temp number(4, 2);
begin
????--處理游標(biāo)的循環(huán)操作
????for c in emp_sal_cursor loop
??????????--判斷員工的工資, 執(zhí)行 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*. 帶參數(shù)的游標(biāo)
declare
????--定義游標(biāo)
????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;
????--定義基數(shù)變量
????temp number(4, 2);
begin
????--處理游標(biāo)的循環(huán)操作
????for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
??????????--判斷員工的工資, 執(zhí)行 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;
- 隱式游標(biāo): 更新指定員工 salary(漲工資 10)，如果該員工沒(méi)有找到，則打印”查無(wú)此人” 信息
begin
?????????update employees set salary = salary + 10 where employee_id = 1005;
?????????if sql%notfound then
????????????dbms_output.put_line('查無(wú)此人!');
?????????end if;
end;
*******************************************************************************
異常處理
*******************************************************************************
[預(yù)定義異常]
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('輸出的行數(shù)太多了');
end;
[非預(yù)定義異常]
declare
??v_sal employees.salary%type;
??--聲明一個(gè)異常
??delete_mgr_excep exception;
??--把自定義的異常和oracle的錯(cuò)誤關(guān)聯(lián)起來(lái)
??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('輸出的行數(shù)太多了');
??when delete_mgr_excep then dbms_output.put_line('Manager不能直接被刪除');
end;
[用戶(hù)自定義異常]
declare
??v_sal employees.salary%type;
??--聲明一個(gè)異常
??delete_mgr_excep exception;
??--把自定義的異常和oracle的錯(cuò)誤關(guān)聯(lián)起來(lái)
??PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);
??--聲明一個(gè)異常
??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('輸出的行數(shù)太多了');
??when delete_mgr_excep then dbms_output.put_line('Manager不能直接被刪除');
??--處理異常
??when too_high_sal then dbms_output.put_line('工資過(guò)高了');
end;
- 異常的基本程序:
通過(guò) select ... into ... 查詢(xún)某人的工資, 若沒(méi)有查詢(xún)到, 則輸出 "未找到數(shù)據(jù)"
declare
??--定義一個(gè)變量
??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('未找到數(shù)據(jù)');
end;
或
declare
??--定義一個(gè)變量
??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('未找到數(shù)據(jù)!');
??when Too_many_rows then
???????dbms_output.put_line('數(shù)據(jù)過(guò)多!'); ????
end;
- 更新指定員工工資，如工資小于300，則加100；對(duì) NO_DATA_FOUND 異常, TOO_MANY_ROWS 進(jìn)行處理.
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('未找到數(shù)據(jù)');
????when too_many_rows then dbms_output.put_line('輸出的數(shù)據(jù)行太多');
end;
- 處理非預(yù)定義的異常處理: "違反完整約束條件"
declare
??--1. 定義異常
??temp_exception exception;
??--2. 將其定義好的異常情況，與標(biāo)準(zhǔn)的 ORACLE 錯(cuò)誤聯(lián)系起來(lái)，使用 EXCEPTION_INIT 語(yǔ)句
??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；若該員工不存在則拋出用戶(hù)自定義異常: no_result
declare
??--自定義異常 ??????????????????????????????????
??no_result exception; ??
begin
??update employees set salary = salary + 100 where employee_id = 1001;
??--使用隱式游標(biāo), 拋出自定義異常
??if sql%notfound then
?????raise no_result;
??end if; ?
exception
??--處理程序拋出的異常
??when no_result then
?????dbms_output.put_line('更新失敗');
end;
*******************************************************************************
存儲(chǔ)函數(shù)和過(guò)程
*******************************************************************************
[存儲(chǔ)函數(shù)：有返回值，創(chuàng)建完成后，通過(guò)select function() from dual;執(zhí)行]
[存儲(chǔ)過(guò)程：由于沒(méi)有返回值，創(chuàng)建完成后，不能使用select語(yǔ)句，只能使用pl/sql塊執(zhí)行]
[格式]
--函數(shù)的聲明(有參數(shù)的寫(xiě)在小括號(hào)里)
create or replace function func_name(v_param varchar2)
--返回值類(lèi)型
return varchar2
is
--PL/SQL塊變量、記錄類(lèi)型、游標(biāo)的聲明(類(lèi)似于前面的declare的部分)
begin
--函數(shù)體(可以實(shí)現(xiàn)增刪改查等操作，返回值需要return)
???????return 'helloworld'|| v_param;
end;
22.1 函數(shù)的 helloworld: 返回一個(gè) "helloworld" 的字符串
create or replace function hello_func
return varchar2
is
begin
???????return 'helloworld';
end;
執(zhí)行函數(shù)
begin
????dbms_output.put_line(hello_func());
end;
或者： select hello_func() from dual;
22.2 返回一個(gè)"helloworld: atguigu"的字符串，其中atguigu 由執(zhí)行函數(shù)時(shí)輸入。
--函數(shù)的聲明(有參數(shù)的寫(xiě)在小括號(hào)里)
create or replace function hello_func(v_logo varchar2)
--返回值類(lèi)型
return varchar2
is
--PL/SQL塊變量的聲明
begin
--函數(shù)體
???????return 'helloworld'|| v_logo;
end;
22.3 創(chuàng)建一個(gè)存儲(chǔ)函數(shù)，返回當(dāng)前的系統(tǒng)時(shí)間
create or replace function func1
return date
is
--定義變量
v_date date;
begin
-函數(shù)體
--v_date := sysdate;
????select sysdate into v_date from dual;
???????dbms_output.put_line('我是函數(shù)哦');
???????return v_date;
end;
執(zhí)行法1：
select func1 from dual;
執(zhí)行法2：
declare
??v_date date;
begin
??v_date := func1;
??dbms_output.put_line(v_date);
end;
- 定義帶參數(shù)的函數(shù): 兩個(gè)數(shù)相加
create or replace function add_func(a number, b number)
return number
is
begin
???????return (a + b);
end;
執(zhí)行函數(shù)
begin
????dbms_output.put_line(add_func(12, 13));
end;
或者
????select add_func(12,13) from dual;
- 定義一個(gè)函數(shù): 獲取給定部門(mén)的工資總和, 要求:部門(mén)號(hào)定義為參數(shù), 工資總額定義為返回值.
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;
執(zhí)行函數(shù)
begin
????dbms_output.put_line(sum_sal(80));
end;
- 關(guān)于 OUT 型的參數(shù): 因?yàn)楹瘮?shù)只能有一個(gè)返回值, PL/SQL 程序可以通過(guò) OUT 型的參數(shù)實(shí)現(xiàn)有多個(gè)返回值
要求: 定義一個(gè)函數(shù): 獲取給定部門(mén)的工資總和 和 該部門(mén)的員工總數(shù)(定義為 OUT 類(lèi)型的參數(shù)).
要求: 部門(mén)號(hào)定義為參數(shù), 工資總額定義為返回值.
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; ??
執(zhí)行函數(shù):
delare
??v_total number(3) := 0;
begin
????dbms_output.put_line(sum_sal(80, v_total));
????dbms_output.put_line(v_total);
end;
26*. 定義一個(gè)存儲(chǔ)過(guò)程: 獲取給定部門(mén)的工資總和(通過(guò) out 參數(shù)), 要求:部門(mén)號(hào)和工資總額定義為參數(shù)
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;
[執(zhí)行]
declare
?????v_sum_sal number(10) := 0;
begin
?????sum_sal_procedure(80,v_sum_sal);
end;
27*. 自定義一個(gè)存儲(chǔ)過(guò)程完成以下操作:
對(duì)給定部門(mén)(作為輸入?yún)?shù))的員工進(jìn)行加薪操作, 若其到公司的時(shí)間在
(? , 95) 期間, ???為其加薪 %5
[95 , 98)?????????????????%3 ??????????????????????????????????????????????????????????????????[98, ?) ?????????????????%1
得到以下返回結(jié)果: 為此次加薪公司每月需要額外付出多少成本(定義一個(gè) OUT 型的輸出參數(shù)).
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;
*******************************************************************************
觸發(fā)器
*******************************************************************************
一個(gè)helloworld級(jí)別的觸發(fā)器
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;
然后執(zhí)行：update employees set salary = salary + 1000;
- 觸發(fā)器的 helloworld: 編寫(xiě)一個(gè)觸發(fā)器, 在向 emp 表中插入記錄時(shí), 打印 'helloworld'
create or replace trigger emp_trigger
after
insert on emp
for each row
begin
???????dbms_output.put_line('helloworld');
end;
- 行級(jí)觸發(fā)器: 每更新 employees 表中的一條記錄, 都會(huì)導(dǎo)致觸發(fā)器執(zhí)行
create or replace trigger employees_trigger
after
update on employees
for each row
begin
???????dbms_output.put_line('修改了一條記錄!');
end;
語(yǔ)句級(jí)觸發(fā)器: 一個(gè) update/delete/insert 語(yǔ)句只使觸發(fā)器執(zhí)行一次
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;
- 編寫(xiě)一個(gè)觸發(fā)器, 在對(duì) my_emp 記錄進(jìn)行刪除的時(shí)候, 在 my_emp_bak 表中備份對(duì)應(yīng)的記錄
1). 準(zhǔn)備工作:
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;
