PL/SQL
--第一個PL/SQL程序
declarevar_empname??varchar2(20)?;
begin
select?empname??into?var_empname?from??employeewhere?empid=¶m_empid;dbms_output.put_line(var_empname);
end;
--rowtype
declare
row_result?employee%rowtype;
begin
select?*??into?row_result?from?employee?where?empid=¶m_empid;
dbms_output.put_line(row_result.empid||'-'||row_result.empname||'--'||row_result.Salary);
end;---------------------------Day1203練習-------------------------------------
---1.?圓周率PI?假定取常量3.14?，讓用戶輸入圓的半徑，用PL/SQL?程序計算出圓的面積，
--并輸出到控制臺。
declare
constant_PI??number(3,2):=3.14;
var_r?number:=¶m_r;
begin
dbms_output.put_line('圓的面積是'||round(constant_PI*var_r*var_r,3));
end;--2.用戶輸入的某員工號查詢出該員工所在的部門，
--根據其所在部門的不同，修改工資。比如：如果部門編號（自己定）是10，
--該部門全體員工的工資工資改為1000；如果部門編號是20，工資改為1200；
--如果部門編號是30，工資定為1500；以此類推...如果都不滿足，工資定為1800。
--分別用if和case語句實現--case
declare
var_deptid?employee.deptid%type;
row_result?employee%rowtype;
begin
select?*?into?row_result?from?employee?where?empid=¶m_empid;
var_deptid:=row_result.deptid;
case?var_deptidwhen?110?then?update?employee?set?Salary=1000?where?deptid=110;when?120?then?update?employee?set?Salary=1200?where?deptid=120;when?130?then?update?employee?set?Salary=1500?where?deptid=130;elseupdate?employee?set?Salary=1800?;end?case;
end;
select?*?from?employee--if
declare
var_deptid?employee.deptid%type;
row_result?employee%rowtype;
begin
select?*?into?row_result?from?employee?where?empid=¶m_empid;
var_deptid:=row_result.deptid;
if?var_deptid=110?thenupdate?employee?set?Salary=180?where?deptid=110;elsif?var_deptid=120?then?update?employee?set?Salary=1200?where?deptid=120;elsif?var_deptid=130?then?update?employee?set?Salary=1500?where?deptid=130;elseupdate?employee?set?Salary=1800?;end?if;
end;
-------------------
select?*?from?employee---4:屬性數據類型type,if..then..else....end?if
--根據用戶用戶輸入的員工號，查詢某個員工的工資，
--如果工資大于某個數（比如1000）,將該員工工資減100，否則加100。
select?*?from?employeedeclarevar_sal?employee.salary?%type;
begin
select?Salary?into?var_sal?from?employee?where?empname='武器大師';
if?var_sal?<600?thenupdate?employee?set?Salary?=Salary+100?where?empname='武器大師';else?update?employee?set?Salary?=Salary-100?where?empname='武器大師';end?if;?
end;--5:?case..when..else
--輸入一個字母A、B、C分別輸出對應的級別信息。具體為：
--A對應“Excellent”，B對應“Very?Good”,?C對應"Not?Bad",其他則對應“No?Such?Data”
declare
var_zimu?varchar2(20):='&ss';
var_result?varchar2(20);
begin
var_result:=case?var_zimu?
when?'A'?then?'Excellent'
when?'B'?then?'Very?Good'
when?'C'?then?'Not?Bad'
else?'No?Such?Data'
end;
dbms_output.put_line(var_result);
end;--6:從1到100的累加?（循環結構）while+loop
declare
v_num?integer?:=1;
v_sum?integer?:=0;
begin
while?v_num<=100loopv_sum?:=?v_sum+v_num;dbms_output.put(v_num);if?v_num<10??thendbms_output.put('+');end?if;v_num:=v_num+1;end?loop;dbms_output.put('=');dbms_output.put_line(v_sum);
end;--6:從1到100的累加?for...in.+loop
declare?var_num?integer;var_sum?integer:=0;
begin?
for?var_num?in?1..100
loop?var_sum?:=var_sum+var_num;dbms_output.put(var_num);if?var_num<100?then?dbms_output.put('+');end?if;
end?loop;
dbms_output.put('=');
dbms_output.put_line(var_sum);
end;----無參存儲過程
create?or?replace?procedure?getempCount
asempcount??number;beginselect?count(*)?into?empcount?from??employee;dbms_output.put_line('總共有'||empcount||'個員工');end;
---
call?getempcount();-----只帶輸入參數的存儲過程
create?or?replace?procedure?showsalarybyempid(param_empid?number)
as
var_salary?number;
beginselect?salary??into?var_salary?from?employee?where?empid=param_empid;dbms_output.put_line('該員工的工資為：'||var_salary);
end;?
--
call?showsalarybyempid(1);
--帶輸入和輸出參數的存儲過程
create?or?replace?procedure?showempbyempid(param_empid?number,oneemp?out?employee%rowtype)
as?
begin
select?*?into?oneemp?from?employee?where?empid=param_empid;
end;
---
declare
row_oneemp?employee%rowtype;
beginshowempbyempid(&empid,row_oneemp);--實際參數dbms_output.put_line(row_oneemp.empname||'--'||row_oneemp.hiredate);end;--cursor(游標)對結果集的操作
declare?cursor??cur_emp(minSalary?in?number,maxSalary?in?number)
is?
select?*?from?employee?where?Salary>=minSalary?and?Salary<=maxSalary;oneemp?employee%rowtype;
beginopen?cur_emp(500,800);fetch?cur_emp?into?oneemp;while?cur_emp%found?loopdbms_output.put_line(oneemp.empname||'--'||oneemp.Salary);fetch?cur_emp?into?oneemp;?end?loop;close?cur_emp;
end;
--trigger(觸發器)
create?or?replace?trigger?tri_department_id?beforeinsert?on?department?for?each?rowbeginselect?seq_department.nextval??into?:new.departid??from?dual;end;------------
insert?into?department(departname,remark)?values('打野部','先buff')select?*?from?department------------------------select?*?from?employee----分頁?先有結果集再有rownum
--考慮排序必須得有三層查詢
select?*?from?(
select?rownum?num,r.*?from(select?*?from?employee?order?by?empid)?r?where?rownum?<=7)
where?num>=3;
------
select?*?from?(select?rownum?num,e.*?from?employee?e?where?rownum?<=7)
where?num>=3
轉載于:https://blog.51cto.com/xiangzhengxian/1593041
總結
- 上一篇： Nginx 模块开发（1）—— 一个稍稍
- 下一篇： phpDocumentor