oracle學習第五天

標籤：

視圖

什麼是視圖
視圖(VIEW)也被稱作虛表,也就是虛擬表,是一組資料的邏輯表示.
視圖對應一個select語句,結果集被賦予一個名字,就是視圖的名字
視圖本身不包含任何資料,它只是包含映射到基表的一個查詢語句,當基表資料發生變化,視圖資料也隨之變化

視圖建立後可以像動作表一樣操作視圖,主要是查詢.
根據視圖所對應的子查詢種類可以分為幾種類型
select語句是基於單表建立,並且不包含任何函數運算,運算式或者分組函數,叫做簡單視圖,此時視圖是基表的子集.
select語句是基於單表建立,但是包含了單行函數,運算式,分組函數或者group by子句,叫做複雜視圖
select語句是基於多個表的,叫做串連視圖

視圖的作用
如果需要經常執行某種複雜查詢,可以基於這個複雜查詢建立視圖,以後查詢此視圖即可,可以簡化複雜查詢
視圖本質上就是一條select語句,所以當訪問視圖時,只能訪問到對應的select語句中涉及到的列,對基表中的其他列起到安全和保密的作用,限制資料的訪問

建立視圖的使用者必須要獲得建立視圖的許可權
在sysdba角色下為指定使用者授權
conn sys/root as sysdba;
show user;
grant create view to scott;
conn scott/a;
show user;

建立視圖
create or replace view v_emp
as
select col1,col2 from emp;

grant create view to scott;

desc v_emp;

刪除視圖
drop view v_emp;

create or replace view v_emp_2
as
select empno no,ename name,job job,sal sal from emp;
---------------------------------------------------------------------------------------------

序列(sequence)
用在哪裡
在mysql資料庫中可以設定某個id欄位以自動成長的方式,實現資料的插入
create table mysql_tbl(
id int primary key auto_increment,
name varchar(100)
)

在oracle中如何?mysql表中的id欄位自動成長
使用序列 序列是oracle中的一種對象
create sequence seq_user --建立序列的關鍵字和序列名稱
increment by 1 --自動增加的步長 預設 1
start with 1 --開始的大小值 預設 1
maxvalue | minvalue num --最大值和最小值 預設nomaxvalue 預設最大值10的26次方
nomaxvalue//預設情況下就是他 --預設的 不限制
cycle | nocycle//輪迴,不輪迴,預設不輪迴 預設nocycle
cache num | nocache --緩衝區大小 預設20

建立序列
create sequence seq_user;
使用序列
查看下一個value
select seq_user.nextval from dual;
查看當前value
select seq_user.currval from dual;

建表
create table t_user(id number,username varchar2(100),password varchar2(48),regtime date,constraint pk_user primary key(id));

insert into t_user values(seq_user.nextval,‘jack‘,‘123123‘,sysdate);

刪除序列
drop sequence seq_user;

需要一張表t_product
編號 主鍵 自動成長
產品名稱 非空
產品銷量 非空 預設0
產品庫存 非空 預設0
產品初始化銷量

建立表
插入5條資料
建立視圖(視圖中不顯示 產品初始化銷量)
查詢檢視
從視圖插入一條資料

create table t_product(
id number,
p_name varchar2(100) not null,
p_salenum number default 0,
p_stock number default 0,
p_init_salenum number,
constraint pk_product primary key(id)
);

create sequence seq_product; --建立序列的關鍵字和序列名稱
插入5條資料
insert into t_product values(seq_product.nextval,‘zy‘,100,200,50);
insert into t_product values(seq_product.nextval,‘zhangsan‘,300,600,100);
insert into t_product values(seq_product.nextval,‘lisi‘,500,1000,300);
insert into t_product values(seq_product.nextval,‘wangwu‘,1000,2000,500);
insert into t_product values(seq_product.nextval,‘mm‘,200,400,30);

建立視圖
create or replace view v_product
as
select id no,p_name name,p_salenum salenum,p_stock stock from t_product;

查詢檢視
select * from v_product;

從視圖插入一條資料
insert into v_product values(seq_product.nextval,‘xiaoliu‘,100,200);
-------------------------------------------------------------------------------
預存程序
預存程序適合做更新操作,特別是大資料量的更新

建立預存程序
create or replace procedure proc1
as | is --相當於declare 聲明的意思
abc varchar2(100); --定義該預存程序的變數 範圍就是本預存程序中
begin
update t_user set username = ‘lucy‘ where id=3;
end;

例如
create or replace procedure proc1
as
begin
update t_user set username = ‘lucy‘ where id=3;
commit;
end;
/

create or replace procedure proc
as
begin
insert into t_user values(seq_user.nextval,‘zy‘,324234,sysdate);
commit;
end;

調用預存程序
exec proc1;
exec proc;
-------------------------------
帶輸入參數的預存程序
建立預存程序
create or replace procedure proc2(
param1 varchar2,param2 varchar2default ‘888888‘ --預存程序的參數 類型不需要指定寬度(範圍)
)
as
begin
insert into t_user values (seq_user.nextval,param1,param2,sysdate);
commit;
end;
執行
exec proc2(‘tom‘,‘121212‘);
call proc2(‘mary‘);

-------------------------------
帶輸出參數的預存程序
create or replace procedure proc3(
param1 in varchar2,param2 out varchar2--in代表輸入參數 out 代表輸出參數 param0 in out number輸入輸出參數
)
as
begin
select password into param2 from t_user where username =param1;
dbms_output.put_line(param2);
end;
set serverout on;
var pp varchar2(100);
exec proc3(‘tom‘,:pp);
-------------------------------
帶輸出多個參數的預存程序
create or replace procedure proc3(
param1 in varchar2,param2 out varchar2,param3 out varchar2--in代表輸入參數 out 代表輸出參數 param0 in out number
)
as
begin--注意:這裡只能處理返回的是一條記錄的結果集,如果結果集含有多條記錄,
--oracle沒有提供直接處理的方式,必須間接的藉助遊標,遊標效率很差,實際開發中基本不使用
select id,password into param2,param3 from t_user where username =param1;
dbms_output.put_line(‘查詢的結果資料是:id=‘ || param2 || ‘,password=‘ || param3);
end;
set serverout on;
var pp1 varchar2(100);
var pp2 varchar2(100);
exec proc4(‘tom‘,:pp1,:pp2);

--------------------------------
定義變數
param1 varchar2(100);--變數的類型,可以是oracle系統所有合法的資料類型
param2 number;

給變數賦值
param1 :=‘who am i!‘;
param2 :=‘123‘;

判斷
if t_value = 1 then
begin
do...
end;
end if;
-----------
create or replace procedure proc_if(pp in number)
as
total number;
begin
total :=pp;
if total<4 then
begin
insert into t_user values (seq_user.nextval,‘suqier‘,‘666666‘,sysdate);
commit;
end;
end if;
end;

call proc_if(3);

---------------------------------------
while迴圈
while t_value = 1 loop
begin
do...
end;
end loop;

create or replace procedure proc_while
as
i number;
begin
i:=1;
while i<100 loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

exec proc_while;
---------------------------------------
for y in 1..100 loop
i:=x*y;
exit when i=300;
end loop;

create or replace procedure proc_for
as
begin
for i in 1..100 loop
dbms_output.put_line(i);
end loop;
end;

exec proc_for;
---------------------------------------
create or replace procedure proc3(
param1 in varchar2,param2 out varchar2,param3 out varchar2--in代表輸入參數 out 代表輸出參數 param0 in out number
)
as
param varchar2(100);
begin
select id,password into param2,param3 from t_user where username = param1;
param :=‘who am i?‘;
dbms_output.put_line(‘查詢的結果資料是:id=‘ || param2 || ‘,password=‘ || param3);
dbms_output.put_line(param);
end;
set serverout on;
var pp1 varchar2(100);
var pp2 varchar2(100);
exec proc4(‘tom‘,:pp1,:pp2);

 

oracle學習第五天