Oracle開發學習
1.rowtype的使用
create or replace procedure PD_ROWTYPE is
v_emp_rec emp%rowtype;
begin
select * into v_emp_rec from emp where empno=7839;
dbms_output.put_line(v_emp_rec.ename||'的薪水是'||v_emp_rec.sal);
end PD_ROWTYPE;
2.判斷使用者從鍵盤輸入的數字
accept num prompt'請輸入一個數字';
declare
pnum number :=#
begin
if pnum=0 then dbms_output.put_line('您輸入的數字是0');
elsif pnum=1 then dbms_output.put_line('您輸入的數字是1');
elsif pnum=1 then dbms_output.put_line('您輸入的數字是2');
else dbms_output.put_line('其他數字');
end if;
end;
3.使用while迴圈列印數字的1~10
declare
pnum number:=1;
begin
while pnum<=10 loop
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop;
end;
4.使用loop迴圈列印
declare
pnum number:=1;
begin
loop
exit when pnum>10;
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop;
end;
5. 使用for迴圈列印1~10
declare
pnum number:=1;
begin
for pnum in 1..10 loop
dbms_output.put_line(pnum);
end loop;
end;
6.-查詢並列印員工的姓名和薪水
--游標的屬性
--%found %notfound
declare
cursor cemp is select ename,sal from emp;
--為游標定義對應的變數
pename emp.ename%type;
psal emp.sal%type;
begin
-- Test statements here
open cemp;
loop
--取一條記錄
fetch cemp into pename,psal;
--思考：1.迴圈什麼時候退出？2.fetch不一定能取到記錄
exit when cemp%notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
--列印
end loop;
close cemp;
end;
7.給員工漲工資，總裁1000，經理800，其他400
declare
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
-- Test statements here
open cemp;
loop
--取出一個員工
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--判斷員工的職位
if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+500 where empno=pempno;
end if;
end loop;
close cemp;
--事物的提交
commit;
dbms_output.put_line('漲工資完成');
end;
8./*
1)游標的屬性
%found %notfound
%isopen 判斷游標是否開啟
%rowcount 影響的行數，不是總行數，到目前取走的行數
2)游標的限制:預設情況下，oracle資料庫只允許在同一個會話中，開啟300個游標
修改游標數的限制：
alter system set open_cursor=400 scope=both;
*/
9.統計每年的入職員工數
declare
cursor cemp is select to_char(hiredate,'yyyy') from emp;
phiredate varchar2(4);
--每年入職的員工人數
count80 number:=0;
count81 number:=0;
count82 number:=0;
count87 number:=0;
begin
open cemp;
loop
--取出一個員工的入職年份
fetch cemp into phiredate;
exit when cemp%notfound;
--判斷入職年份
if phiredate = '1980' then count80:=count80+1;
elsif phiredate = '1981' then count81:=count81+1;
elsif phiredate = '1982' then count82:=count82+1;
else count87:=count87+1;
end if;
end loop;
close cemp;
--輸出結構
dbms_output.put_line('Total:'||(count80+count81+count82+count87));
dbms_output.put_line('1980:'||count80);
dbms_output.put_line('1981:'||count81);
dbms_output.put_line('1982:'||count82);
dbms_output.put_line('1987:'||count87);
end;
10、員工漲工資問題（有bug待完善）
declare
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
--漲工資的人數
countEmp number:=0;
--漲後的工資總額
salTotal number;
begin
--得到工資總額的初始值
select sum(sal) into salTotal from emp;
--開啟游標
open cemp;
loop
--1工資總額>5w
exit when salTotal>50000;
--取一個員工漲工資
fetch cemp into pempno,psal;
--2%notfound
exit when cemp%notfound;
--漲工資
update emp set sal*1.1 empno=pempno;
countEmp:=countEmp+1;
--漲後的工資總額=漲錢的工資總額+sal*0.1
salTotal:=salTotal+psal*0.1;
end loop;
--關閉游標
close cemp;
commit;
dbms_output.put_line('人數：'||countEmp||'漲後的工資總額:'||salTotal);
end;
11、對比2種儲存過程的寫法，該儲存過程是用來為emp1表中對於不同層次工資進行相應的增長，2中寫法都能達到同樣的效果。
第一種寫法：
declare
cursor csr_update is select * from emp1;
empinfo csr_update%rowtype;
saleinfo emp1.sal%type;
empnoinfo emp1.empno%type;
begin
open csr_update;
loop
fetch csr_update into empinfo;
exit when csr_update%notfound;
IF empInfo.SAL<1500 THEN
saleInfo:=empInfo.SAL*1.2;
elsif empInfo.SAL<2000 THEN
saleInfo:=empInfo.SAL*1.5;
elsif empInfo.SAL<3000 THEN
saleInfo:=empInfo.SAL*2;
else
saleInfo:=empInfo.sal;
END IF;
empnoinfo:=empInfo.empno;
update emp1 set sal=saleinfo where empno=empnoinfo;
end loop;
close csr_update;
end;
第二種寫法：
declare
cursor
csr_Update is select * from emp1 for update OF SAL;
empInfo csr_Update%rowtype;
saleInfo emp1.SAL%TYPE;
begin
FOR empInfo IN csr_Update LOOP
IF empInfo.SAL<1500 THEN
saleInfo:=empInfo.SAL*1.2;
elsif empInfo.SAL<2000 THEN
saleInfo:=empInfo.SAL*1.5;
elsif empInfo.SAL<3000 THEN
saleInfo:=empInfo.SAL*2;
else
saleInfo:=empInfo.sal;
END IF;
UPDATE emp1 SET SAL=saleInfo WHERE CURRENT OF csr_Update;
END LOOP;
END;
12、對每位員工的薪水進行判斷，如果該員工薪水高於其所在部門的平均薪水，則將其薪水減50元，輸出更新前後的薪水，員工姓名，所在部門編號。
DECLARE
CURSOR
crs_testAvg
IS
select EMPNO,ENAME,JOB,SAL,DEPTNO,AVG(SAL) OVER (PARTITION BY DEPTNO ) AS DEP_AVG
FROM EMP1 for update of SAL;
r_testAvg crs_testAvg%rowtype;
salInfo emp1.sal%type;
begin
for r_testAvg in crs_testAvg loop
if r_testAvg.SAL>r_testAvg.DEP_AVG then
salInfo:=r_testAvg.SAL-50;
else salInfo:=r_testAvg.SAL;
end if;
update emp1 set SAL=salInfo where current of crs_testAvg;
end loop;
end;
13、放入儲存過程中巢狀
create or replace procedure proc_tmp_dept
(out_cnt out number)
as
begin
declare cursor cur_dept is select deptno from dept;
v_count number;
--此begin不能少，不寫此begin與對應的end（即for loop程式碼塊不用begin end括起來，會報編譯錯誤）
begin
for dept_rec in cur_dept
loop
insert into temp_dept(deptno) values(dept_rec.deptno);
v_count:=1000000;
out_cnt:=v_count;
end loop;
end;
end;
14、type字典放入儲存過程
create or replace procedure pd_test
(
in_req_id varchar2,
out_no out varchar2
)
is
test tb_test%rowtype;
type type_record is record
(test_code varchar2(50),
money varchar2(32));
type_record_user type_record;
type type_array is table of type_record_user%type index by binary_integer;
var_array type_array;
begin
select * into test from tb_fund_test where req_id = in_req_id;
if test.opr_type = 'A' then
pd_mid_test(test.test_id, test.test_type, test.user_id, var_array(1).money,
var_array(2).money, var_array(3).money, var_array(4).money,
var_array(5).money, var_array(6).money, var_array(7).money);
var_array(1).test_code:='10001';
var_array(2).test_code:='10002';
var_array(3).test_code:='10003';
var_array(4).test_code:='10004';
var_array(5).test_code:='10005';
var_array(6).test_code:='10006';
var_array(7).test_code:='10007';
for i in 1..var_array.count loop
dbms_output.put_line(var_array(i).money||','||var_array(i).test_code);
end loop;
elsif test.code_type = 'B' then
out_no := '1';
end if;
end pd_test;
15、分解字串函式
CREATE OR REPLACE FUNCTION fc_sys_para
(
f_srcstr VARCHAR2,
f_lkpstr VARCHAR2
)
RETURN VARCHAR2
IS
v_max_len NUMBER(18, 6);
v_cur_pos NUMBER(18, 6);
v_rtn_str VARCHAR(1024);
BEGIN
v_rtn_str := '';
v_cur_pos := instr(upper(f_srcstr), upper(f_lkpstr) || '=');
IF v_cur_pos > 0 THEN
v_cur_pos := v_cur_pos + length(f_lkpstr) + 1;
v_max_len := length(f_srcstr) - v_cur_pos + 1;
IF v_max_len > 0 THEN
v_rtn_str := substr(f_srcstr, v_cur_pos, v_max_len);
v_cur_pos := instr(v_rtn_str, ',');
IF v_cur_pos > 0 THEN
v_rtn_str := substr(v_rtn_str, 1, v_cur_pos - 1);
END IF;
END IF;
END IF;
RETURN rtrim(ltrim(v_rtn_str));
END fc_sys_para;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29812844/viewspace-1878490/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Web開發學習Web
- web開發學習之旅Web
- SVG開發學習【轉】SVG
- Kinect開發學習筆記之（二）Kinect開發學習資源整理筆記
- 明天開始系統學習oracleOracle
- android開發之網路學習-Socket學習Android
- iOS開發學習路線iOS
- 如何學習Java Web開發JavaWeb
- DSP開發入門學習
- Android開發學習之路Android
- Scrum敏捷開發學習心得Scrum敏捷
- ESP32開發學習
- Oracle學習Oracle
- 深度學習開發必備開源框架深度學習框架
- 前端開發學習Day8前端
- 學習web前端開發的原因Web前端
- 微信小程式學習：雲開發微信小程式
- 學習NEO開發如何進階
- JAVA後臺開發學習（5）Java
- 開發規範文字（自我學習）
- IDEA外掛開發學習Idea
- Flask Web開發學習之“HelloWorld”FlaskWeb
- Rest API 開發 學習筆記RESTAPI筆記
- iOS開發捷徑學習(三)iOS
- 如何系統學習 iOS 開發？iOS
- Android開發學習之路03Android
- Android 開發學習筆記Android筆記
- 學習Java開發難不難？好學嗎？Java
- NDK學習筆記-NDK開發流程筆記
- Web前端開發學習路線圖Web前端
- JS開發步驟學習筆記JS筆記
- 爬蟲實戰開發學習（一）爬蟲
- 【機器學習】深度學習開發環境搭建機器學習深度學習開發環境
- Flutter學習指南：開發環境搭建Flutter開發環境
- Java後臺開發學習（3）——MongoDBJavaMongoDB
- HarmonyOS應用開發學習路線
- 鴻蒙開發學習（一）之ArkTS鴻蒙
- 遊戲開發完整學習路線遊戲開發