oracle pl/sq技朮升級

最新推荐文章于 2023-03-29 10:08:15 发布

貓哥是個傳說

最新推荐文章于 2023-03-29 10:08:15 发布

阅读量1k

点赞数

分类专栏： Oracle知識點文章标签： oracle table exception performance null insert

Oracle知識點专栏收录该内容

37 篇文章 0 订阅

订阅专栏

《oracle pl/sql技术升级》就是其中的一本全书9章看完这本书收获相当大，对繁体字和名称也熟悉了很多。

其实到现在这本书还有地方我看的一头雾水，估计有是实践的机会慢慢我更多的了解的~ 把笔记整理出来以后哪忘记了也有个地方查阅

第一章对资料库（数据库）资料表栏位等做了些介绍~ 都是理论

二：资料库的介绍

建表CERATE TABLE plsql101_test_1 (

first)name CHAR(15),

last_namr CHAR(20)

);

新增記錄

Insert INTO plsql101_rest_1 VALUES ('Jane', 'Smith');

選擇記錄

Select * FROM plsql101_test_1;

刪除刪除

Drop TABLE plsql101_rest_1;

資料表和欄位的命名原則：名稱最好不超過30個字命名可以用應為字幕數字和底線，用英文字母開頭，不區分大小?。名稱里面不能放空格，不能重名

oracle儲存文字資料的方式

建立一個具有CHAR(CHARACHTER文字資料類型) 欄位的資料表 n是欄位的長度Create TABLE table_name (column_name CHAR(n));

CHAR這種欄位一般會用在長度固定的資料上

VARCHAR2用在長度不固定的資料上

orACLE儲存數值資料的方式

定義一個儲存數值資料的欄位可以用NUMBER

product_price NUMBER(3,3)

orACLE儲存日期資料的方式茱莉亞日期

訂定資料表的結構

DESC table_name DESC 指令的?出結果共包含有3?欄位 Name(顯示名稱) Null(空) Type(顯示?欄位的資料型和資料長度)

NULL欄位和NOT NULL欄位

NULL（表示空欄位）NOT NULL（不能為空）

新增有空值的記錄

Insert INTO plsql101_purchase VALUEA('Product Name 1',1,NULL);

Select * FROM plsql101_purchase;

如何新增含有頓號的資料

在Insert指令之前執行SET SCAN OFF的指令將頓號用兩個單連續頓號表示

SET SCAN OFF

Insert INTO plsql101_purchase VALUES

('Fifth Product''s Name',25,'05-MAY-03');

SET SCAN ON

選擇特定欄位Select product_name FROM plsql101_purchase;

資料表做數據運算Select product_name,product_price*1.15 FROM plsql101_purchase;

表示式Select product_name, product_price *2 +10 FORM

plsql101_purchase;

將兩個或多個文字連在一起Select product_name || salesperson FROM plsql101_purchase；

設置欄位別名Select product_name || 'was sold by' || salesperson "Sold By" FROM plsql101_purchase；

=================================

3 进阶资料的处理

限制所选的资料录

Select columns FROM table_name Where condition(s);

Condition条件式

过滤数值资料

依单一值来选择资料

Select * FORM plsql101_product

Where quantity_on_hand = 1;

以上指令可以找出某一个值为1的所有资料

依某个区间值来选择资料

select * from plsql101_product

where product_prict >= 50

and

product_prict >= 100

;

select * from plsql101_product

where product_prict between 50 and 100;

显示不包函的纪录

select * from plsql101_product

where product_prict < 50

product_prict > 100

;

select * from plsql101_product

where product_prict not between 50 and 100;

select * from plsql101_product

where product_prict <> 99;

select * from plsql101_product

where product_prict != 99;

就是用ＯＲ来取代ＡＮＤ　　用<>或者!=?取代=

选取一组可接受的值

select * form product

where color = 'red'

color = 'green'

color = 'white'

;

如果数量多的话　可以用in

select * form plsql101_product

where product_price in (50, 99);

过滤文字资料

select * form plsql101_product

where product_name = 'sankk widget';

找出某一组字串的资料一样可以用in来做　

select * form plsql101_product

where product_name in ('samll widget', 'round chrome snaohoo');

使用万用字元

select * form plsql101_product

where product_name like 'Chrome%';

%就是万用字元　放在后面的时候表示（只要开头为这个文字的字符串的资料就符合搜索条件）chrome前面还有其他字

select * form plsql101_product

where product_name like '%Chrome%';

被搜索的内容区分大小?

where product_name like '%w_d%';

搜索含有Ｄ的所有资料

过滤日期资料

select * from plsql101_product

where last_stock_date = '15-JAN-03';

同样也可以用 between not between

NULL值的处理

select * form plsql101_product

where last_stork_date is null;

检查日期栏位为空的资料

select * form plsql101_product

where last_stork_date is not null;

检查日期栏位不为空的?料　

更改纪录的顺序

依据某一栏位做排序

select * form table_name order by column_to_sort_by;

table_na表名　column_to_sort_by栏位名

依据多个栏位做排序

select last_stock_date,

product_name,

product_price,

quantitu_on_hand

from plsql101_product

order by last_stock_date,

product_name

;

显示唯一值

Select distinct product_name

From plsql101_purchase

order by product_name;

加入distinct来过滤唯一值也可以用unique

依据DUAL来显示资料

desc dual;

select * from dual;

select 18*1.05 from dual;

变更资料表里面的资料

Update table_name set column_name = new_value where condititon;

Select * from plsql_purchase;

Update plsql101_purchase

Set product_name = ‘large widget’

Where product_name = ‘samll widget’;

把plsql101_purchase 里面所有名为”small widget”的产品改成”large widget”

删除资料表里面的纪录

Delete from table_name where condition;

指定条件来做删除

Select * from plsql101_purchase;

Delete from plsql101_purchase

Where purchase_date >’15-jul-03’ ;

删除july 15,2003之后的资料

Delete from plsql101_purchase

Where purchase_name = ‘large widget’;

产品名称为large widget 的资料全部删除

删除某个表里面的所有资料

Delete from table_name;

资料表的解除

Truncate table tble_name;

Truncate table plsql101_purchase;

Select * from plsql101_purchase;

针对plsql101_purchase来做删除动作

交易控制

复原DML交易

Insert ioto plsql101_purchse values

(‘small widget’, 1, ’14-jul-03’, ‘ca’);

Savepoint a;

Rollback to a;

复原使用rollback savepoint用来做保存节点 commit会直接将异动资料进资料库

=====================

Sql *plus的控制

使用文字编辑器

Edit 可以简写成ED 用来开启文本编辑器输入代码结束用 / 在按回车就可以了

行编辑

使用change指令

Select product_nmae from plsql101_product;

这里的拦位名nmae写错

Change/nmge/name

然后按/再执行命令

Change可以用c来代替

在行编辑模式里面选择所要编辑的行

Select product_nmae

From plsql101_produtc

Where quantity_on_hand >= 100

And

Last_stocl_date is not null

order by product_name;

Sql> 1

1* select product_nmae

Sql> c/ma/am

1* select product_name

Sql> 2

2* from plsql101_produtc

Sql > c/tc/ct

2* from plsq;101_pruduct

Sql> /

清楚sql *plus的画面

在SQL*PLUS低下按住SHIFT然后再按住Delete 即可清空

产生比较可观性的输出画面

在sql *plus 里做数值资料的格式化

小数点对齐

Column column_name format format_code

column_name参数是小数点对齐的栏位名称

format_code是要做栏位格式化的参数

select * from plsql101_product;

column product_price format 9999.99

select * from plsql101_product;

product_price 栏位里面的数字资料末尾都多了。00

加入钱号

Column product_price format $99.99

Select * from plsql101_product;

在SQL里面做文字资料的格式化

Column column_name format ann word_wrap

里面的nn是便是超过了多少字元之后要做换行处理（A代表字母或者数字的意思）

Select * from plsql101_product;

Column product_name format a10 word_wrap

Select * from plsql101_product;

Sql * plus每遇10个字元就会自动换行

在SQL*PLUS 里做栏位抬头的格式化

Column column_name heading ‘heading_test’ justify left

Column column_name heading ‘heading_test’ justify center

Column column_name heading ‘heading_test’ justify righr

将输出结果存入在磁盘

Spool spool_file_name

===================================

在资料表之间传送资料

使用insert传送资料

Insert into table_name (

Select statement

)

;

从现在的表中建立一个新表

Create table new_table_name AS select statement;

变更资料表名称

Rename old_table_name to new_table_name;

新增栏位

Alter table table_name

Add new_column_name datatype [not null]

;

例子

Desc plsql101_log

Alter table plsql101_log

Add data_load_date varchar2(8);

Desc plsql101_log - -上下对比

更改栏位的类别

Alter table table_name

Modify column_name new_datatype

;

例子

Alter table plsql101_log

Modify data_load_date date;

改变NULL的选项

Alter table table_name

Modify column_name not null；

例子

Alter table plsql101_log

Modify data_load_data not null;

资料集

建立资料集

Create or replace view view_name as

Select statement

;

or replace 透过这种方式我们就可以透过指令来建立一个与现有名称相同的资料集

删除资料集

Drop view view_name;

例子

Drop view plsql101_sales_per_person_v;

前Ｎ笔记录得分析

Select column_name_1 [1,column_name_2….]

From table_name

Where rownum <=number_of_record_you_want

order by column_with_value_you_care_about

;

例子

Create or replace view plsql101_overstocked_items as

Select product_name, quantity_on_hand

From plsql101_product

Where rowum <= 3

order by quantity_on_hand

;

Select * from plsql101_overstocked_items;

序数　

建立序数　　

Create sequence sequence_name;

[increment by increment_quantity]

[start with starting_value]

[maxvalue highest_value]

[minvalue lowest_value]

[cycle]

;

Increment by 参数允许你建一个序数且每次的增加可以不时１

Start with 建立让你建立一个初始值不为１的序数

maxvalue minvalue参数允许你定义序数产生的最大和最小值

同意字

Select * from your_user_name.your_table_name;

建立同意字

Create [public] synonym synonym_name

For object_name

;

修改既有的同意字

Srop [public] synonym synonym_name;

PL/SQL介绍

内建程序

所谓内建程序（store procedure）是一组由pl/sql所选择的指令集合。

Increase_price (percent_increase number)

呼叫以上这支程序时需要将某个值传送给它，从传入给这个程序的值回在程序里面以percent_increase 的变数来使用

Increase_salary_find_tax (increase_percent in number := 7

Sal)

以上这支程序具有三个形式参数，而接在参数名称之后的IN这个字则是表示这是一个输入给该程序的值；而OUT这个字则表示此程序处理完后会利用这个参数将值传回给呼叫他的元件；而参数名称后接IN OUT的话，则表示这个可以透过参数某写值送入这个程序

Increase_percent后面的7 会以 7%来计算。

内建函式

calc_percent(value_1 number, value_2 number)

Return number

可以接受两个参数，在函式里可以透过以value_1和 value_2的变数来参考折两格值。

例子

Insert into employ values(3000, calc_percent(300, 3000));

驱动程序

它会在某些由驱动程序所制定的事件发生时自动执行。

驱动程序根内建程序的差别

无法在程式里面呼叫驱动程序，因为驱动是由OEACLE资料库根据某些预设事件来自动执行、驱动无法由参数列表的，驱动程序规格里面具有某些内建程序规格所没有的资讯

PL/SQL程式区块的结构

PL/SQL包含4个区块：镖头区块，选择性宣告区块，执行区段以及选择性例外区段。

匿名区块：由于它不具有标头或名称区块，所以叫匿名区块

Set serveroutput on

declare

num_a number := 6;

num_b number;

begin

num_b := 0;

num_a := num_a / num_b;

num_b :=7;

dbms_output.put_line('value of num_b' || num_b);

exception

when zero_divide

then

dbms_output.put_line('trying to divide by zero');

dbms_output.put_line('value of num_b' || num_a);

dbms_output.put_line('value of num_b' || num_b);

end;

标头区块

<<just_a_label>>

Declare

执行区段

从关键字begin开始但是结束的职位则由两种要是区块里具有例外区段的话，则执行区段的结束位置会在关键字exception ;

如果没有例外区段的话用 end

Begin

一个或多个pl/sql叙述

[例外区段]

End[函式程序的名称]；

例外区段

Exception 列外名称

Then

某个例外发生时需要采取的动作

When 例外名称

Then

某个例外发生时所要采取的动作

撰写一支简单的pl/sql程序

create procedure my_first_proc is

greetings varchar2(20);

begin

greetings := 'hello world';

dbms_output.put_line(greetings);

end my_first_proc;

程序和函数的呼叫

@body text indent 2 = procedure_name();

例子

Procedure_name(formal_param1 => acual_param1,

Formal_param1 => Actual_param2

….

);

PL/SQL的变数和常数

Number 资料型类别的变数就存放数值资料用的

而char或varchar2 类别是用来存文字资料的

PL/SQL的变数宣告

Variable_name data_type [[not null] := default_value_expression];

Variable_name data_type[[not null] default default_value_expression];

Variable_name 事 PLSQL里面任何一个合法识别字

PL/SQL的常数宣告

Variable_name data_type constant := constant_value_expression;

指定变数的值

Variable_name := expression;

Pl/sql的控制结构

IF叙述

If condition_1 then

Action_1;

[elsif condition_2 then

Action_2;]

…..

[slse

Actions_last;]

End if;

Actions_1到actions_last均为一个或多个PL/SQL的叙述

例子

create function compute_discountesi (order_amt number)

return number

small_order_amt number := 400;

small_order_amt number := 1000;

small_disct number := 1;

small_disct number := 5;

begin

if (order_amt < large_order_amt

and

order_amt >= small_order_amt)

then

return (order_amt * large_disct / 100);

elsif (order_amt >= large_order_amt)

then

return (order_amt * large_disct / 100);

else

return(0);

end if;

end compute_discountesi;

LOOP

所有叙述均会不断重复执行，如果循环里面利用WHEN证明离开条件表示式的条件时，每当所有叙述执行完一遍之后程式回自动检测条件是否为真。如果事真的话，则所有在EXIT之后的叙述均会跳过不执行，而直接跳到END LOOP后面来执行其他的叙述。

<<loop_name>>

Loop

Statement;

Exit loop_name [when exit_condition_expression];

Statements;

End loop;

例子

Declare

Just_a_num number := 1;

Begin

<<just_a_loop>>

Loop

Dbms_output.put_line(just_a_num);

exit just_a_loop

when (just_a_num >= 10);

just_a_num := just_a_num + 1;

end loop;

end;

每执行一次就会将变数具尸体just_a_num的值增加1 加到10 离开开条件

While 循环

当叙述需要要反复的执行的次数不确定时，就可以用 while 来处理

While while_condition_expression

Loop

Statements;

End loop;

例子：

declare

just_a_num number := 1;

begin

while (just_a_num <= 10) loop

dbms_output.put_line(just_a_num);

just_a_num := just_a_num + 1;

end loop;

end;

for循环

FIR循环会用到一个计数器变数，这种变数也称为循环引索[loop index],透过此一变数就可以计算循环的次数了

for counter in [reverse] lower) bound…upper_bound

loop

statements;

end loop;

例子

begin

for just_a_num in 1..10

loop

dbms_output.put_line(just_a_num);

end loop;

end;

指标

跟sql的 select叙述结合在一起使用。透过指标的话，可以将SQL叙述的一笔一笔资料拿出来做处理

指标的宣告与指标的属性

cursor cursor_name [(parameter1 [, parameter2 ...])]

[return return_specification]

select_statement

[for update

[of table_or_coll

[,table_or_col2 ...]

]

;

指标里面所有参数都为IN的参数指标传回的规格return_specification 则是注明指标里面的Select叙述所截取的类别资料 table_or_col可以指要变更的栏位名称 for update的指令可以在指标开启时将Select 叙述所选的资料锁住。

Cursor_name%isopen 检查指标是否开启传回TURE 表明指标cursor_name已经开启

Cursor_name%powcount 由指标的select叙述所传回的资料笔数

Cursor_name%found 检查指标里面是否由下一笔资料 TURE 表示还有

Cursor_name%notfound 与found属性刚好相反 TURE 表示没资料了

PL/SQL的记录

以资料表为主的记录型别

指标为主的记录型别

使用者自定的记录型别

在指标里使用open, fetch 和close

Open cursor_name;

Fetch cursor_name into record_var_or_list_of_var;

Close cursor_name;

在指标内使用FOR循环

For cursor_record in cursor_name loop

Statements;

End loop;

透过指标专用的for循环可以反复将指标里面的资料取出来塞到cursor_record

Where current of

当指标开启准备提供资料更新或删除时使用

Where current of cursor_name

例子

create table plsql101_product(product_name varchar2(15), product_price varchar2(20));

select product_name, product_price

form plsql101_product;

declare

cursor product_cur is

select * form plsql101_product;

for update of product_price;

begin

for product_rec in product_cur

loop

update plsql101_product

set product_price = (product_rec.product_price * 0.97)

where current of product_cur;

end loop;

end;

巢状循环和指标的使用范例

create or replace procedure do_commissins is

commission_rate number := 2 ;

total_sale number := 0 ;

current_person char(3) := '';

next_person char(3) ;

quantity_sold number := 0 ;

item_price number := 0 ;

cursor sales_cur is

select tab1.salesperson,

tab1.quantity,

tab2.product_price

from pllsql101_purchase tab1,

pllsql101_product tab2

where tab1.product_name = tab2.product_name

order by salesperson;

begin

open sales_cur;

loop

fetch sales_cur into

next_person, quantity_sold, item_price;

while (next_person = current_person

and

sales_cur%found)

loop

total_sale :=

total_sale + (quantity_sold * item_price);

fetch sale_cur into

next_person, quantity_sold, tiem_price;

end loop;

if (sales_cur%found)

then

if (current_person != next_person)

then

if (current_person != '')

then

dbms_output.put_line

(current_person ||

' ' ||

total_sale ||

' ' ||

total_sale * commission_rate / 100);

end if;

total_sale := quantity_sold * item_price;

current_person := next_person;

end if;

else if (current_person != ' ')

then

dbms_output.put_line(current_person ||

' ' ||

total_sale ||

' ' ||

total_sale * commisson_rate / 100);

end if;

exit when sales_cur%notfound;

end loop;

close sales_cur;

end do_commossions;

其他重要的 pl/sql概念

程式的撰写惯例

所有PL/SQL命令都以大写来表示

名称均为小写来表示

所有的逻辑片段均以独立的一行来选择。

函式和程序的规格一定要写清除。

在逗号之后一定会接上格空格

变数型别的动态宣告 PL/SQL的记录

根据栏位型别来宣告PL/SQL的变数语法：

Variable_name table_name.column_name%TYPE;

根据资料库的栏位来宣告记录语法：

Record_name table_name%ROWTYPE;

利用指标根据资料库栏位来宣告记录语法：

Record_name cursor_name%ROWTYPE;

建立一个属于自己的记录

TYPE record_type_name IS

(field_1_name field_1_type,

Field_2_name field_2_type,

…

);

结构写好之后，记录的实际宣告语法是

Record_variable_type_name;

- - performatnce事指每一位业务员所接到的平均订单金额 status事指错误状态

set serveroutput on

declare

cype performance_type is record - - 宣告一个名为performance_type的记录类别

(person_code plsql101_person.person_code%TYPE,

person_name plsql101_person.list_name%TYPE, - -用到TYPE所有栏位就可以对照plsql101_person

current_sales number(8,2),

perform_percent number(8,1),

status varchar2(30)

);

one_perform performance_type; - - 又宣告了one_perform的记录变数

CURSOR person_cur IS - -然后接了一个指标可以通过指标将plsql101_person;所有资料截取出来

Select *

FORM plsql101_person;

- - 可以算出每一位业务员的平均订单金额及总业绩

procedure current_performance - -包含了两格参数 1一笔人事资料记录和plsql101_person;的栏位一样，由person_cur将资料截取出来

(a_person plsql101_person%ROWTYPE,

a_perform OUT performance_type) - -输出的结果均会由程序写入到这个函数里面自动将程序的执行结果填入a_perform

cursor history_cur (person varchar2) is - -一次只做一个截取动作用了一个栏位 avg_order

select avg(tab2.product_price * tab1.quantity)

avg_order

from plsql101_purchase_archive tab1,

plsql101_product tab2

where tab1.product_name = tab2.product_name

group by tab1.salasperson

having tab1.salesperson = person;

hist_rec history_cur%rowtype;

current_avg_sale number(8,2) := 0;

begin

a_perform.person_code := a_person.person_code;

a_perform.person_name := a_person.last_name;

a_perform.status := null;

begin

select sum(tbl2.product_price * tbll.quantity),

avg(tbl2.product_price * tbll.quantity)

into a_perform.current_sales,

current_avg_sales

from plsql101_purchase tbl1,

plsql101_product tb12

where tbl1.product_name = tbl2.product_name

group by tab1.salesperson

having tab1.salesperson = a_person.person_code;

exception

when no_data_found

then

a_perform.status := 'current purchases exception';

a_perform.current_sales := 0;

end;

open history_cur (a_person.person_code);

fetch history_cur into hist_rec;

if (history_cur%notfound)

then

a_perform.perform_percent := 0;

if (a_perform.status is null)

then

a_performl.status := 'erroneous or no history';

end if;

else

a_perform.perform_percent :=

100 * (current_avg_sales-hist_rec.avg_order) / hist_rec.avg_order;

a_perform.status := 'all fine';

end if;

close history_cur;

exception

when no_data_found

then

a_perform.status := 'exception found';

end current_performance;

begin

for person_rec in person_cur

loop

current_performance(person_rec, one_perform);

dbms_output.put_line(one_perform.person_code ||

' ' ||

one_perform.person_name ||

' ' ||

one_perform.current_sales ||

' ' ||

one_perform.perform_percent ||

' ' ||

one_perform.status);

end loop

end;

select * from plsql101_person;

select * from plsql101_perchase;

select * from plsql101_purchase_archive;

select * from plsql101_product;

貓哥是個傳說

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录