【轉】oracle PLSQL基礎學習

標籤：

--oracle 練習；
/**************************************************PL/SQL編程基礎***************************************************************/
--firstday
--》》》資料類型
-- Create table
create table T_CSCUSTOMER
(
CUST_NO VARCHAR2(12) primary key not null,
PERSON_ID VARCHAR2(12),
GROUP_NO VARCHAR2(12),
CUST_ADDRESS_ID VARCHAR2(20),
ORGANISEID VARCHAR2(10),
CUST_NAME VARCHAR2(128),
CUST_TYPE VARCHAR2(2),
SERVE_PASSWORD VARCHAR2(128),
REGIONCODE VARCHAR2(4),
OPERATOR VARCHAR2(16),
OPENDATE DATE,
REMARK VARCHAR2(500),
ADDRESS VARCHAR2(256),
CUST_KIND VARCHAR2(4),
LINKMAN VARCHAR2(64),
LINKPHONE VARCHAR2(64),
LINKMOBILE VARCHAR2(64),
COMPANY_ID VARCHAR2(10),
INSTALL_ADDRESS VARCHAR2(256)
)

set serveroutput on

--%TYPE類型
--SQL>
declare
var_ename t_cscustomer.cust_name%type;
var_phone t_cscustomer.linkphone%type;

begin
SELECT cust_name,linkphone
into var_ename,var_phone
from t_cscustomer
where cust_no=‘0061121890‘;
dbms_output.put_line(var_ename||‘的電話是：‘||var_phone);
end;
/

--SQL>
declare
var_ename varchar2(200);
var_phone varchar2(200);

begin
SELECT cust_name,linkphone
into var_ename,var_phone
from t_cscustomer
where cust_no=‘0061121890‘;
dbms_output.put_line(var_ename||‘的電話是：‘||var_phone);
end;
/

--record類型
--SQL>
declare
type emp_type is record
(
var_ename varchar2(20),
var_phone varchar2(20),
var_sal varchar2(200)
);
empinfo emp_type;
begin
select cust_name,linkphone,address into empinfo from t_cscustomer where cust_no=‘0110542709‘;
dbms_output.put_line(‘僱員‘||empinfo.var_ename||‘的電話是‘||empinfo.var_phone||‘、地址是‘||empinfo.var_sal);
end;
/

--%rowtype類型
--SQL>
declare
rowVar_emp t_cscustomer%rowtype;
begin
SELECT * into rowVar_emp FROM t_cscustomer where cust_no=‘0110542709‘;
/*輸出資訊*/
dbms_output.put_line(‘僱員‘||rowVar_emp.cust_name||‘的電話是‘||rowVar_emp.linkphone||‘、地址是‘||rowVar_emp.address);
end;
/

--變數 、常量
var_countryname varchar2(50):=‘中國‘;
con_day constant integer:=365;

--secondday
--》》》流程式控制制
--if
--SQL>
declare
var_name1 varchar2(20);
var_name2 varchar2(20);
begin
var_name1:=‘East‘;
var_name2:=‘xiaoke‘;
if length(var_name1) < length(var_name2) then
dbms_output.put_line(‘字串“‘||var_name1||‘”的長度比字串“‘||var_name2||‘”的長度小‘);
end if;
end;
/
--if elseif
--SQL>
declare
num_age int :=55;
begin
if num_age>=56 then
dbms_output.put_line(‘您可以申請退休了‘);
elsif num_age<56 then
dbms_output.put_line(‘您小於56歲，不可以申請退休！‘);
else
dbms_output.put_line(‘對不起，年齡不合法！‘);
end if;
end;
/

--SQL>
declare
num_age int :=55;
aboutinfo varchar2(50);
begin
if num_age>=56 then
aboutinfo:=‘您可以申請退休了‘;
elsif num_age<56 then
aboutinfo:=‘您小於56歲，不可以申請退休！‘;
else
aboutinfo:=‘對不起，年齡不合法！‘;
end if;
dbms_output.put_line(aboutinfo);
end;
/

--case when
--SQL>
declare
season int:=3;
aboutinfo varchar2(50);
begin
case season
when 1 then
aboutinfo := season||‘季度包括1、2、3 月份‘;
when 2 then
aboutinfo := season||‘季度包括4、5、6 月份‘;
when 3 then
aboutinfo := season||‘季度包括7、8、9 月份‘;
when 4 then
aboutinfo := season||‘季度包括10、11、12 月份‘;
else
aboutinfo := season||‘季度不合法‘;
end case;
dbms_output.put_line(aboutinfo);
end;
/

--》》》迴圈語句
--loop語句 一直運行到exit when end_condition_exp 為true時退出
declare
sum_i int:=0;
i int:=0;
begin
loop
i:=i+1;
sum_i :=sum_i + i;
exit when i = 100;
end loop;
dbms_output.put_line(‘前100個自然數的和是：‘||sum_i);
end;
/
--while 語句
declare
sum_i int := 0;
i int :=0;
begin
while i<100 loop
i:=i+1;
sum_i:=sum_i+i;
end loop;
dbms_output.put_line(‘前100個自然數的和是：‘||sum_i);
end;
/
--for 語句
declare
sum_i int:=0;
begin
for i in reverse 1..100 loop -- reverse 表示i從100遞減
sum_i:= sum_i+i;
end loop;
dbms_output.put_line(‘前100個自然數的和是：‘||sum_i);
end;
/

--》》》遊標
/*
遊標屬性：
cur_tmp%found 至少影響到一行資料為true;
cur_tmp%notfound 與%found相反
cur_tmp%rowcount 返回受SQL語句影響的行數
cur_tmp%isopen 遊標開啟時為true
*/
--顯示cursor
set serveroutput on
declare
cursor cur_emp(var_name in varchar2:=‘lili‘)
is select cust_no,cust_name,address
from t_cscustomer
where cust_name like var_name||‘%‘;
type record_emp is record
(
var_empno t_cscustomer.cust_no%type,
var_empname t_cscustomer.cust_name%type,
var_empaddress t_cscustomer.address%type
);
emp_row record_emp;
begin
DBMS_OUTPUT.ENABLE(buffer_size => null); --表示輸出buffer不受限制
open cur_emp(‘劉‘);
fetch cur_emp into emp_row;
while cur_emp%found loop
dbms_output.put_line(emp_row.var_empname||‘的編號是‘||emp_row.var_empno||‘,地址是‘||emp_row.var_empaddress);
fetch cur_emp into emp_row;
end loop;
close cur_emp;
end;
/

--for 中使用cursor 不用進行開啟遊標、讀取遊標、關閉遊標 oracle內部自動完成
declare
type emp_type is record
(
var_ename t_cscustomer.cust_name%type,
var_phone t_cscustomer.linkphone%type,
var_sal t_cscustomer.address%type
);
empinfo emp_type;
cursor cur_emp
is
select cust_name var_ename,linkphone var_phone,address var_sal from t_cscustomer where address like ‘%招南%0402室%‘;
begin
DBMS_OUTPUT.ENABLE(buffer_size => null); --表示輸出buffer不受限制
--open cur_emp;
--fetch cur_emp into empinfo;
--dbms_output.put_line(‘共有資料‘||cur_emp%rowcount||‘條‘);
for empinfo in cur_emp loop
dbms_output.put_line(‘僱員‘||empinfo.var_ename||‘的電話是‘||empinfo.var_phone||‘、地址是‘||empinfo.var_sal);
end loop;
end;
/

declare
cursor cur_emp
is
select cust_name var_ename,linkphone var_phone,address var_sal from t_cscustomer where address like ‘%招南%0402室%‘;
begin
DBMS_OUTPUT.ENABLE(buffer_size => null); --表示輸出buffer不受限制
for empinfo in cur_emp loop
dbms_output.put_line(‘僱員‘||empinfo.var_ename||‘的電話是‘||empinfo.var_phone||‘、地址是‘||empinfo.var_sal);
end loop;
end;
/

begin
DBMS_OUTPUT.ENABLE(buffer_size => null); --表示輸出buffer不受限制
for empinfo in (select cust_name var_ename,linkphone var_phone,address var_sal from t_cscustomer where address like ‘%招南%0402室%‘) loop
dbms_output.put_line(‘僱員‘||empinfo.var_ename||‘的電話是‘||empinfo.var_phone||‘、地址是‘||empinfo.var_sal);
end loop;
end;
/

--》》》異常處理
/*
預定義異常
自訂異常
*/
--預定義異常
declare
var_empno t_cscustomer.cust_no%type;
var_empname t_cscustomer.cust_name%type;
begin
select cust_no,cust_name into var_empno,var_empname from t_cscustomer where cust_no like ‘00%‘;
if sql%found then
dbms_output.put_line(‘僱員編號：‘||var_empno||‘、名稱：‘||var_empname);
end if;
exception
when too_many_rows then
dbms_output.put_line(‘返回記錄超過一行‘);
when no_data_found then
dbms_output.put_line(‘無資料記錄‘);
end;
/
--自訂異常
declare
primary_iterant exception;--定義一個異常變數
pragma exception_init(primary_iterant,-00001);--關聯錯誤號碼和異常變數名
begin
insert into dept_tmp values(‘0‘,‘綜合部‘,‘北京‘);
dbms_output.put_line(‘採用預設值插入dept_tmp成功！‘);
exception
when primary_iterant then
dbms_output.put_line(‘主鍵不允許重複！‘);
end;
/

/**************************************************預存程序、函數、觸發器、包***************************************************************/
--》》》預存程序
--查看錯誤
show errors;

--建立或替換pro_insertTmp
drop table dept_tmp;
create table dept_tmp(
DEPT_NO VARCHAR2(12) primary key not null,
DEPT_NAME VARCHAR2(50),
LOCATION VARCHAR2(200)
);
create or replace procedure pro_insertTmp is
begin
insert into dept_tmp values(1,‘市場拓展部‘,‘join‘);
commit;
dbms_output.put_line(‘插入dept_tmp新記錄成功‘);
end pro_insertTmp;
/
--執行pro_insertTmp
--execurte pro_insertTmp;
exec pro_insertTmp;

--程式塊中調用pro_insertTmp
set serverout on
begin
pro_insertTmp
end;
/

--third day2015-02-03
/**預存程序參數過程包括：in 輸入參數、out 輸出參數、in out可被修改的輸入參數，並作為輸出參數**/
-->>in
create or replace procedure pro_insertDept(v_deptno in varchar2,v_deptname in varchar2,v_loc in varchar2) is
begin
insert into dept_tmp values(v_deptno,v_deptname,v_loc);
commit;
dbms_output.put_line(‘通過in參數插入dept成功！‘);
end pro_insertDept;
/

--不按順序傳入參數，指定參數值
begin
pro_insertDept(v_deptname=>‘採購部‘,v_loc=>‘成都‘,v_deptno=>‘2‘);
end;
/
--按順序傳入參數
begin
pro_insertDept(‘3‘,‘市場部‘,‘深圳‘);
end;
/
--混合傳入參數
begin
pro_insertDept(‘4‘,v_loc=>‘成都‘,v_deptname=>‘工程部‘);
end;
/

-->>out
create or replace procedure pro_selectDept(v_deptno in varchar2,v_deptname out dept_tmp.dept_name%type,v_loc out dept_tmp.location%type) is
begin
select dept_name,location into v_deptname,v_loc from dept_tmp where dept_no=v_deptno;
exception
when no_data_found then
dbms_output.put_line(‘該編號的部門不存在！‘);
end pro_selectDept;
/

set serveroutput on
declare
v_deptname dept_tmp.dept_name%type;
v_loc dept_tmp.location%type;
begin
pro_selectDept(‘1‘,v_deptname,v_loc);
--if v_deptname = ‘‘ then
dbms_output.put_line(v_deptname||‘位於：‘||v_loc);
--end if;
end;
/

--執行
variable v_deptname varchar2(50);
variable v_loc varchar2(50);
exec pro_selectDept(‘2‘,:v_deptname,:v_loc);
print v_deptname v_loc;
select :v_deptname,:v_loc from dual;

-->> in out
create or replace procedure pro_square(num in out number,flag in boolean) is
i int:=2;
begin
if flag then
num := power(num,i); --計算平方
else
num := sqrt(num); --計算平方根
end if;
end pro_square;
/

declare
n_number number;
n_tmp number;
b_flag boolean;
begin
b_flag:=false;
n_tmp:=3;
n_number:=n_tmp;
pro_square(n_number,b_flag);
if b_flag then
dbms_output.put_line(n_tmp||‘的平方是：‘||n_number);
else
dbms_output.put_line(n_tmp||‘的平方根是：‘||n_number);
end if;
end;
/

--in 參數預設值
create or replace procedure pro_insertDeptDefault(v_deptno in varchar2,v_deptname in varchar2 default ‘綜合部‘,v_loc in varchar2 default ‘北京‘) is
primary_iterant exception;--定義一個異常變數
pragma exception_init(primary_iterant,-00001);--關聯錯誤號碼和異常變數名
begin
insert into dept_tmp values(v_deptno,v_deptname,v_loc);
commit;
dbms_output.put_line(‘採用預設值插入dept_tmp成功！‘);
exception
when primary_iterant then
dbms_output.put_line(‘主鍵不允許重複！‘);
end pro_insertDeptDefault;
/
--指定名稱傳值
declare
row_dept dept_tmp%rowtype;
begin
pro_insertDeptDefault(‘5‘,v_loc => ‘太原‘);
select * into row_dept from dept_tmp where dept_no=‘5‘;
dbms_output.put_line(‘部門名稱：‘||row_dept.dept_name||‘,位於：‘||row_dept.location);
exception
when no_data_found then
dbms_output.put_line(‘未找到相關的資料！‘);
end;
/

drop table t_emp;
create table t_emp(
emp_no number primary key not null,
emp_name varchar2(20),
age number,
sal number,
job varchar2(20),
dept_no number,
address varchar2(200),
hiredate date
);
insert into t_emp values(1,‘王力‘,22,9000,‘會計‘,3,‘深圳市北京路奧巴馬號‘,sysdate);
--》》》函數
create or replace function get_avg_pay(num_deptNo number) return number is
num_avg_pay number;
begin
select avg(sal) into num_avg_pay from t_emp where dept_no=num_deptNo;
return(round(num_avg_pay,2));
exception
when no_data_found then
dbms_output.put_line(‘該部門編號的員工不存在‘);
return(0);
end get_avg_pay;
/

--程式塊中調用函數
declare
avg_pay number;
begin
avg_pay:=get_avg_pay(3);
dbms_output.put_line(‘編號為3的部門，平均工資是：‘||avg_pay);
end;
/

--刪除函數
drop function get_avg_pay;

--》》》觸發器
--文法格式
create or replace trigger tri_name
[before|after|instead of] tri_event
on table_name|view_name|user_name|db_name
[for each row][when tri_condition]
begin
plsql_sentences;
end tri_name;
/

create table dept_log(
operate_tag varchar2(10),
operate_time date
);
--語句級觸發器
create or replace trigger tri_dept
before insert or update or delete
on dept_tmp
declare
v_tag varchar2(20);
begin
if inserting then
v_tag:=‘插入‘;
elsif updating then
v_tag:=‘修改‘;
elsif deleting then
v_tag:=‘刪除‘;
end if;
insert into dept_log values(v_tag,sysdate);
end tri_dept;
/
insert into dept_tmp values(6,‘業務諮詢部‘,‘長春‘);
update dept_tmp set location=‘瀋陽‘ where dept_no=‘6‘;
delete from dept_tmp where dept_no=‘6‘;

--行級觸發器
create table t_goods(
id int primary key not null,
good_name varchar2(50)
);

create sequence seq_goods_id;

--:new.id--列標識符，新值標識符用於標識當前行某個列的新值“:new.column_name”,通常在insert和update語句中使用
--:old.id--列標識符，原值標識符用於標識當前行某個列的原始值“:new.column_name”,通常在delete和update語句中使用
create or replace trigger tri_insert_goods
before insert
on t_goods
for each row
begin
select seq_goods_id.nextval into :new.id from dual;
end;
/

insert into t_goods(good_name) values(‘蘋果‘);
insert into t_goods(id,good_name) values(9,‘桃子‘);

--替換觸發器
--替換觸發器定義在視圖（一種資料庫物件）上，而不是定義在表上。
create view view_emp_dept
as select emp_no,emp_name,dept_tmp.dept_no,dept_name,job,hiredate from t_emp,dept_tmp where t_emp.dept_no=dept_tmp.dept_no;

create or replace trigger tri_insert_view
instead of insert
on view_emp_dept
for each row
declare
row_dept dept_tmp%rowtype;
begin
select * into row_dept from dept_tmp where dept_no=:new.dept_no;
if sql%notfound then
insert into dept_tmp(dept_no,dept_name) values(:new.dept_no,:new.dept_name);
end if;
insert into t_emp(emp_no,emp_name,dept_no,job,hiredate) values (:new.emp_no,:new.emp_name,:new.dept_no,:new.job,:new.hiredate);
end tri_insert_view;
/

--rollback不能再觸發器中使用
create or replace trigger tri_insert_view2
instead of insert
on view_emp_dept
for each row
declare
row_dept dept_tmp%rowtype;
begin
select * into row_dept from dept_tmp where dept_no=:new.dept_no;
if sql%notfound then
insert into dept_tmp(dept_no,dept_name) values(:new.dept_no,:new.dept_name);
end if;
insert into t_emp(emp_no,emp_name,dept_no,job,hiredate) values (:new.emp_no,:new.emp_name,:new.dept_no,:new.job,:new.hiredate);
exception
--when no_data_found then
--dbms_output.put_line(‘部門表中未找到相對應的部門編號‘);
--rollback;
end tri_insert_view;
/

--若部門中沒有編號10，會報錯
insert into view_emp_dept(emp_no,emp_name,dept_no,dept_name,job,hiredate) values (8888,‘東方‘,10,‘ACCOUNTING‘,‘CASHIER‘,sysdate);
commit;

select * from view_emp_dept where emp_no=8888;

--使用者事件觸發程序
create table t_ddl_oper_log(
db_obj_name varchar2(20),
db_obj_type varchar2(20),
oper_action varchar2(20),
oper_user varchar2(20),
oper_date date
);

create or replace trigger tri_ddl_oper
before create or drop or alter
on liulei.schema
begin
insert into t_ddl_oper_log values(ora_dict_obj_name,ora_dict_obj_type,ora_sysevent,ora_login_user,sysdate);
end;
/
create table t_test(id number);
create view view_test as select emp_no,emp_name from t_emp;
drop view view_test;
select * from t_ddl_oper_log;

--》》》程式包
--文法 規範
create or replace package pack_name is
[declare_variable];
[declare_type];
[declare_cursor];
[declare_function];
[declare_procedure];
end [pack_name];

--建立一個程式包的“規範”,不包聲明體的主體部分
create or replace package pack_emp is
function fun_avg_sal(num_deptno number) return number;--擷取指定部門的平均工資
procedure pro_regulate_sal(var_job varchar2,num_proportion number);--按照指定比例上調指定職務的工資
end pack_emp;
/

--文法 程式包主體
create or replace package body pack_name is
[inner_variable]
[cursor_body]
[function_title]
{
begin
fun_plsql;
[exception]
[dowith_sentences;]
end [fun_name]
}
[procedure_title]
{
begin
pro_plsql;
[exception]
[dowith_sentences;]
end [pro_name]
}
...
end [pack_name]

create or replace package body pack_emp is
function fun_avg_sal(num_deptno number) return number is
num_avg_sal number;
begin
select avg(sal) into num_avg_sal from t_emp where dept_no=num_deptno;
return(num_avg_sal);
exception
when no_data_found then
dbms_output.put_line(‘該部門編號不存在僱員記錄！‘);
return 0;
end fun_avg_sal;

procedure pro_regulate_sal(var_job varchar2,num_proportion number) is
begin
update t_emp set sal=sal*(1+num_proportion) where job=var_job;
end pro_regulate_sal;
end pack_emp;
/

--調用包
set serveroutput on
declare
num_deptno t_emp.dept_no%type;
var_job t_emp.job%type;
num_avg_sal t_emp.sal%type;
num_proportion number;
begin
num_deptno:=5;
num_avg_sal:=pack_emp.fun_avg_sal(num_deptno);
dbms_output.put_line(num_deptno||‘號部門的平均工資是：‘||num_avg_sal);

var_job := ‘SALEMAN‘;
num_proportion:=0.1;
pack_emp.pro_regulate_sal(var_job,num_proportion);
end;
/

【轉】oracle PLSQL基礎學習