版權聲明：本文為部落客原創文章，遵循 CC 4.0 BY-SA 版權協定，轉載請附上原文出處連結和本聲明。
本文連結：https://blog.csdn.net/zhao1299002788/article/details/101758110
/*
授權指令 grant 權限 to 使用者
授權可以授予多個權限
grant connect,resource to baidu
收回權限 revoke 權限 from 使用者
revoke dba from baidu
建立使用者 配置設定表空間--指定使用者的預設表空間
create table p(..) 建表存在預設表空間
--建表時候指定表空間
create table p(...) talebspace 表空間名
*/
/*
PlSql程式設計語言 procedure language 過程語言
是在sql語句中加入一些處理過程的語句
常見的條件表達式 if else 還有循環結構
基本結構
declare
--聲明部分 了解為定義
--聲明使用的變量
begin
--處理邏輯的代碼塊
end;
*/
--psSql簡單示例
declare
v_n number := 1; --聲明數值變量 指派使用符号:=
v_s varchar2(4) :='s'; --聲明字元類型變量
emp_ename emp.ename%type ;-- 引用類型變量
emp_row emp%rowtype ;-- 記錄類型變量
begin
dbms_output.put_line('v_n====='||v_n); --輸出語句相當于sys out
dbms_output.put_line('v_s====='||v_s);
select ename into emp_ename from emp where empno=7499; --使用into關鍵字指派
dbms_output.put_line('emp_ename====='||emp_ename);
select * into emp_row from emp where empno = 7499; --指派記錄類型變量
dbms_output.put_line('員工編号=='||emp_row.empno||'員工姓名'||emp_row.ename);
end;
/*
plsql 的條件表達式判斷
if .. 處理語句 else if ..處理語句
-------------------------
if .. then
elsif .. then
else
end if;
*/
---使用條件表達式判斷員工的工資 使用漢字輸出
declare
emp_row emp%rowtype ;-- 記錄類型變量
begin
select * into emp_row from emp where empno = 7499; --指派記錄類型變量
--使用表達式判斷工資
if emp_row.sal > 3000 then
dbms_output.put_line('員工工資大于3000=='||emp_row.sal);
elsif emp_row.sal < 1000 then
dbms_output.put_line('員工工資小于1000=='||emp_row.sal);
else
dbms_output.put_line('員工工資位于1000到3000之間=='||emp_row.sal);
end if;
end;
/*
循環結構
第一種-----
loop
exit when 條件
end loop;
第二種 ---
while 條件 loop
end loop;
第三種 ---
for 變量 in 範圍 loop
end loop;
*/
-------使用循環輸出數字 1-----10
/*
第一種
loop
exit when 條件
end loop;
*/
declare
v_n number :=1;
begin
loop
--隻是用來判斷退出使用的,并不是相當于if()else{}
exit when v_n>10 ; --退出條件
dbms_output.put_line(v_n);
v_n:=v_n+1; --自增
end loop;
end;
/*
第二種
while 條件 loop
end loop;
*/
declare
v_n number :=1;
begin
while v_n<11 loop
dbms_output.put_line(v_n);
v_n:=v_n+1; --自增
end loop;
end;
/*
第三種
for 變量 in 範圍 loop 變量的聲明和範圍的控制是由for循環自動執行
end loop;
*/
declare
begin
for i in 1..10 loop
dbms_output.put_line(i);
end loop;
end;
/*
遊标 光标 是用于接收查詢的記錄結果集 ResultSet 提示記錄使用.next()
遊标的使用步驟
聲明遊标 cursor 遊标名 is select 語句 指定遊标的記錄結果集
打開遊标 open 遊标名
提取遊标 fetch 遊标名 into 記錄類型變量
關閉遊标 close cursor
遊标的兩個屬性 遊标名%found : 判斷它有找到
遊标名%notfound : 判斷它沒有找到
if emp_cursor%found then
dbms_output.put_line('found');
elsif emp_cursor%notfound then
dbms_output.put_line('notfound');
elsif emp_cursor%notfound is null then
dbms_output.put_line('null');
end if;
*/
--使用while循環結構示範遊标
declare
--聲明遊标
cursor emp_cursor is select * from emp;
--聲明記錄類型變量 用于接收遊标提取的記錄
emp_row emp%rowtype;
begin
--打開遊标
open emp_cursor;
--提取遊标(判斷下一個是否有值)
fetch emp_cursor into emp_row ;
--有值就執行while循環
while emp_cursor%found loop
dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename);
--繼續提取遊标(并判斷下一個是否有值)
fetch emp_cursor into emp_row ;
end loop;
close emp_cursor;
end;
/*
loop
exit when 遊标提取不到
end loop
*/
declare
--聲明遊标
cursor emp_cursor is select * from emp;
--聲明記錄類型變量 用于接收遊标提取的記錄
emp_row emp%rowtype;
begin
--打開遊标
open emp_cursor;
loop
fetch emp_cursor into emp_row;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename);
end loop;
close emp_cursor;
end;
--使用遊标提取某個部門的員工資訊
--聲明帶參數的遊标資訊
declare
--聲明遊标
cursor emp_cursor(dno number) is select * from emp where deptno = dno ;
--聲明記錄類型變量 用于接收遊标提取的記錄
emp_row emp%rowtype;
begin
--打開遊标 時候傳入參數
open emp_cursor(10);
loop
fetch emp_cursor into emp_row;
exit when emp_cursor%notfound;
dbms_output.put_line(emp_row.empno||'員工姓名'||emp_row.ename);
end loop;
close emp_cursor;
end;
/*
錯誤資訊開發中的異常
資料庫中叫做 例外
異常的分類 1.系統異常 系統定義好的異常
2.自定義的異常
new 自定義類繼承Exception 自定義傳值（錯誤代碼，提示資訊）
使用場景
不滿足某些特定業務場景，抛出自定義異常
異常的處理
java try{}catche(IndexOutOfBoundException e){}catche(Exception e){}
java try{}catche(Exception e){} catche(IndexOutOfBoundException e){}--報錯
資料庫可以捕捉處理異常
exception 關鍵字捕捉異常
when 異常類型 then 處理語句 判斷異常類型 處理異常
*/
--異常的簡單示例
/*
--除0的異常 除數為0
--指派錯誤
*/
declare
v_n number :=0;
v_m number :=1;
begin
v_m:='s'; --将字元串指派給數值變量
v_m:= v_m/v_n;
exception
when zero_divide then
dbms_output.put_line('除數不能為0');
when value_error then
dbms_output.put_line('指派有誤');
end;
---處理太多記錄數異常
declare
emp_row emp%rowtype ;-- 記錄類型變量
begin
select * into emp_row from emp ; --指派記錄類型
exception
when too_many_rows then
dbms_output.put_line('太多記錄數');
when others then --others是最大範圍的異常 相當于java 的 Exception
dbms_output.put_line('其他異常');
end;
/*
需求 ：使用遊标查詢部門下的員工資訊
如果部門下沒有員工 報錯提示
需要自定義異常
變量名 exception --聲明自定義異常
*/
declare
cursor emp_cursor is select * from emp where deptno= 40; --遊标結果集不存在
emp_row emp%rowtype ;-- 記錄類型變量
no_dept_emp exception ; --聲明自定義異常
begin
open emp_cursor; --打開遊标
fetch emp_cursor into emp_row;
if emp_cursor%notfound then
--沒有員工 抛出錯誤異常
raise no_dept_emp;
end if;
close emp_cursor;
exception
when no_dept_emp then
dbms_output.put_line('部門下面沒人，快招人吧');
end;
/*
存儲過程 是一段封裝好的代碼塊，過程是編譯好放在伺服器提供開發人員調用
封裝的代碼塊意義： 提升開發效率 可以複用 誰用直接調用
提升運作效率 一調用直接運作
文法：create [or repalce] procedure 過程名稱(參數名 out|in 參數類型)
as|is
--聲明變量的部分
begin
--處理過程語句代碼塊
end;
調用存儲過程
在begin和end之間使用 過程名傳參調用
*/
--存儲過程的簡單示例 使用存儲過程給某個員工增加工資100
create or replace procedure add_sal(eno in number )
as
emp_sal number :=0;
begin
select sal into emp_sal from emp where empno = eno ;
dbms_output.put_line('漲工資之前是===='||emp_sal);
update emp set sal=sal+100 where empno = eno;
select sal into emp_sal from emp where empno = eno ;
dbms_output.put_line('漲工資之後是===='||emp_sal);
commit;
end;
--------調用存儲過程
declare
begin
add_sal(7499);
end;
/*
使用存儲過程統計某個員工的年薪，年薪需要傳回輸出列印
in 類型輸入參數可以 省略 預設就是輸入參數
*/
create or replace procedure count_sal(eno number,year_sal out number)
as
begin
select sal*12+nvl(comm,0) into year_sal from emp where empno=eno; --使用into指派給輸出參數
end;
----調用存儲過程計算年薪
declare
v_emp_sal number :=0;
begin
count_sal(7499,v_emp_sal);
dbms_output.put_line('年薪為=='||v_emp_sal);
end;
/*
使用存儲過程 查詢出某個部門的員工資訊
某個部門應該接受一個in類型的輸入參數
查詢到的部門員工多條記錄傳回應該使用結果集
聲明遊标 cursor 遊标名 is select 語句指定結果集
系統引用遊标
sys_refcursor
聲明系統引用遊标 變量名 sys_refcursor; --不需要指定結果集
打開遊标 open 系統引用遊标 for select 語句 --使用for關鍵字裝入資料
*/
create or replace procedure dept_emp(dno number,cusor_emp out sys_refcursor)
as
begin
--根據傳進來的部門編号給遊标裝入結果集資料
open cusor_emp for select * from emp where deptno = dno;
end;
----調用存儲過程查詢部門下的員工
declare
cursor_emp sys_refcursor; --聲明系統引用遊标傳參使用
emp_row emp%rowtype ;--記錄類型變量
begin
dept_emp(10,cursor_emp);
--提取遊标中的資料
loop
fetch cursor_emp into emp_row;
exit when cursor_emp%notfound;
dbms_output.put_line('編号'||emp_row.empno||'姓名'||emp_row.ename);
end loop;
close cursor_emp;
end;
/*
存儲函數 是一段封裝好的代碼塊，是編譯好放在伺服器提供開發人員調用
封裝的代碼塊意義： 提升開發效率 可以複用 誰用直接調用
提升運作效率 一調用直接運作
文法：create [or repalce] function 函數名稱(參數名 out|in 參數類型) return 資料類型
in 代表傳入參數,out 代表傳出參數
as|is
--聲明變量的部分
begin
--處理過程語句代碼塊
--return 變量
end;
調用存儲函數
在begin和end之間使用 函數名傳參調用 函數必須使用變量接收 傳回值
*/
--使用存儲函數統計某個員工的年薪
create or replace function count_emp_sal(eno number,year_sal out number) return number
as
v_sal number :=0;
begin
select sal*12+nvl(comm,0) into year_sal from emp where empno=eno; --使用into指派給輸出參數
return v_sal;
end;
--不帶out類型輸出參數統計年薪
create or replace function count_sal_noout(eno number) return number
as
v_sal number :=0;
begin
select sal*12+nvl(comm,0) into v_sal from emp where empno=eno; --使用into指派給輸出參數
return v_sal;
end;
--調用函數統計年薪
declare
emp_sal number:=0;
total_sal number :=0;
begin
--total_sal := count_emp_sal(7499,emp_sal);
total_sal := count_sal_noout(7499);
dbms_output.put_line(emp_sal);--0
dbms_output.put_line(total_sal); --統計後年薪
end;
/*
存儲函數和過程的差別
1.建立的關鍵字 procedure funciton
2.建立函數 必須使用return 聲明函數的傳回變量資料類型
3.在函數的方法體内 必須使用return 傳回一個變量
4.函數的調用 必須有變量接收傳回值
5.函數可以用在select 查詢語句中 select emp.*,count_sal_noout(empno) from emp;
存儲函數和過程使用場景
開發規範 java代碼待用過程 過程是用來處理業務邏輯代碼
如果邏輯中需要用到一些功能性的封裝，可以調用函數
90%情況下 函數和過程通用 過程可以調用函數，函數同樣可以調用過程
*/
/*
觸發器 是一個螢幕，監視對表中資料的操作
如果對資料的操作滿足觸發器的執行條件，
觸發器會自動運作
觸發器文法：
create or repalce trigger 觸發器名稱
after|before --觸發器執行時機
insert|update|delete --監視的動作
on 表名 --表級觸發器
declare
begin
end;
行級觸發器 insert update delete
:new 動作之後的記錄 要插入的記錄 修改後的記錄 空
:old 動作之前的記錄 空 原始的記錄 原始的記錄
*/
--建立觸發器監視表，如果表中有資料插入，輸出一個歡迎語句
create or replace trigger insert_trigger
after
insert
on p
declare
begin
dbms_output.put_line('歡迎加入！');
end;
----插入資料測試效果
insert into p values(1,'zs');
commit;
--插入資料不能在休息日插入資料
--休息日 周六和周日
/*
raise_application_error(v1,v2) v1錯誤代碼 v2是提示語句
-20000 -20999
*/
create or replace trigger insert_no_work
before
insert
on p
declare
v_day varchar2(10) ;
begin
--擷取到目前星期
select to_char(sysdate,'day') into v_day from dual;
--判斷星期是否在休息日
if trim(v_day) in ('saturday','sunday') then
--如果休息 錯誤提示
raise_application_error(-20001,'不能休息日插入資料');
end if;
end;
----插入資料測試效果
insert into p values(1,'zs');
commit;
--使用觸發器監視表中資料修改，不能做降低工資的操作
create or replace trigger can_not_low
before
update
on emp
for each row --行級觸發器
declare
begin
--擷取到原始記錄的工資 --擷取修改後的工資
if :old.sal > :new.sal then
--談錯誤框提示
raise_application_error(-20002,'不能降低工資');
end if;
end;
--修改員工的工資測試觸發器
update emp set sal=sal-1 where empno=7499;
/*
觸發器實際應用
使用觸發器實作 插入資料的id 自增長 面試題
**/
create or replace trigger auto_increment_id
before
insert
on test_trigger
for each row
declare
begin
--補全将要插入記錄的id
--補全的id 是自增長的數值 如果沒有提前建立序列,需要提前建立序列 --建立序列 create sequence order_sequence
select order_sequence.nextval into :new.pid from dual;
end;
insert into test_trigger(pname,phone) values('zs','1234566');
commit;
package baidu;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class TestJdbc {
String driverClass = "oracle.jdbc.driver.OracleDriver";
String url ="jdbc:oracle:thin:@192.168.17.128:1521:orcl";
String user= "baidu_03";
String password = "baidu_03";
/*
*測試jdbc連接配接資料庫
*
* */
@Test
public void querEmp(){
try{
//加載驅動
Class.forName(driverClass);
//擷取連結
Connection con = DriverManager.getConnection(url, user,password);
//擷取預編譯的statement
PreparedStatement pst= con.prepareStatement("select * from emp");
//執行查詢
ResultSet rs = pst.executeQuery();
//處理結果
while(rs.next()){
System.out.println(rs.getInt(1)+"員工姓名"+rs.getString("ename"));
}
rs.close();
con.close();
//關閉連接配接
}catch(Exception e){
e.printStackTrace();
}
}
/*存儲過程的調用
* {call <procedure-name>[(<arg1>,<arg2>, ...)]}
add_sal(eno number,addsal number)
* */
@Test
public void callAddSal(){
try{
//加載驅動
Class.forName(driverClass);
//擷取連結
Connection con = DriverManager.getConnection(url, user,password);
//擷取預編譯的statement
CallableStatement pst= con.prepareCall("{call add_sal(?,?)}");
pst.setInt(1, 7499);
pst.setInt(2, 1000);
//執行查詢
pst.execute();
con.close();
//關閉連接配接
}catch(Exception e){
e.printStackTrace();
}
}
/*存儲過程的調用
* {call <procedure-name>[(<arg1>,<arg2>, ...)]}
count_yearsal(eno number,total_year_sal out number)
* */
@Test
public void callCountSal(){
try{
//加載驅動
Class.forName(driverClass);
//擷取連結
Connection con = DriverManager.getConnection(url, user,password);
//擷取預編譯的statement
CallableStatement pst= con.prepareCall("{call count_yearsal(?,?)}");
pst.setInt(1, 7499);
//注冊輸出參數
pst.registerOutParameter(2, OracleTypes.NUMBER);
//執行查詢
pst.execute();
int total = pst.getInt(2);
System.out.println(total);
con.close();
//關閉連接配接
}catch(Exception e){
e.printStackTrace();
}
}
/*
* pro_dept_emp(dno number,dept_emp out sys_refcursor)
* */
@Test
public void callProEmp(){
try{
//加載驅動
Class.forName(driverClass);
//擷取連結
Connection con = DriverManager.getConnection(url, user,password);
//擷取預編譯的statement
CallableStatement pst= con.prepareCall("{call pro_dept_emp(?,?)}");
pst.setInt(1, 10);
//注冊輸出參數
pst.registerOutParameter(2, OracleTypes.CURSOR);
//執行查詢
pst.execute();
OracleCallableStatement ocs = (OracleCallableStatement)pst;
ResultSet rs = ocs.getCursor(2);
while(rs.next()){
System.out.println(rs.getInt(1)+"員工姓名"+rs.getString("ename"));
}
rs.close();
ocs.close();
pst.close();
con.close();
//關閉連接配接
}catch(Exception e){
e.printStackTrace();
}
}
}
public void show4(){
try {
Class.forName(driverClass);
Connection con = DriverManager.getConnection(url, user,password);
CallableStatement pst= con.prepareCall("{?= call count_sal_noout(?)}");
//給第二個參數指派
pst.setLong(2, 7499);
// stat2.setLong(2, empno);
//聲明第一個參數的類型
pst.registerOutParameter(1, OracleTypes.NUMBER);
pst.execute();
OracleCallableStatement ocs = (OracleCallableStatement)pst;
NUMBER num = ocs.getNUMBER(1);
System.out.println(num);
// long i = pst.getLong(1);
// System.out.println(i);
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/* 複制