PL/SQL功能特点

最新推荐文章于 2024-08-27 09:35:48 发布

原创最新推荐文章于 2024-08-27 09:35:48 发布 · 592 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

11 篇文章

订阅专栏

1.1结构化程序设计

1.顺序结构

2.分支结构

<pre name="code" class="html">IF...
THEN...;
ELSIF...
THEN...;
END IF;

3.循环结构

<div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"><span style="word-wrap: normal; word-break: normal; line-height: normal; widows: 2; border-collapse: separate; font-stretch: normal;font-size:14px; orphans: 2; border-spacing: 0px;"><span style="color:#FF0000;word-wrap: normal; word-break: normal; line-height: 21px;">1.ORACLE中的GOTO用法</span></span></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"> <wbr><wbr><span style="font-family:宋体;word-wrap: normal; word-break: normal;">DECLARE
x number;
BEGIN
x:=9;
<<repeat_loop>> <wbr><wbr><span style="word-wrap: normal; word-break: normal;"> <wbr></wbr></span>--循环点
x:=x-1;
DBMS_OUTPUT.PUT_LINE(X);
IF X>0
THEN
GOTO repeat_loop; <wbr><wbr><span style="word-wrap: normal; word-break: normal;"> <wbr></wbr></span>--当x的值小于9时,就goto到repeat_loop
END IF;
END;
/</wbr></wbr></wbr></wbr></span></wbr></wbr></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"> <wbr><wbr></wbr></wbr></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"><span style="color:#FF0000;word-wrap: normal; word-break: normal;">2。ORACLE中的FOR循环用法</span></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"><span style="font-family:宋体;word-wrap: normal; word-break: normal;">DECLARE
X number; <wbr><wbr><span style="word-wrap: normal; word-break: normal;"> <wbr></wbr></span>--声明变量
BEGIN
x:=1; <wbr><wbr> <wbr><wbr> <wbr><wbr><span style="word-wrap: normal; word-break: normal;"> <wbr></wbr></span>--给初值
FOR X IN REVERSE 1..10 LOOP <wbr><wbr> <wbr><wbr><span style="word-wrap: normal; word-break: normal;"> <wbr></wbr></span>--reverse由大到小
DBMS_OUTPUT.PUT_LINE('内:x='||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('end loop:x='||X); <wbr><wbr> <wbr><wbr><span style="word-wrap: normal; word-break: normal;"> <wbr></wbr></span>--x=1
END;
/</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></span></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"> <wbr><wbr></wbr></wbr></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"><span style="color:#FF0000;word-wrap: normal; word-break: normal;">3。ORACLE中的WHILE循环用法</span></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"><span style="font-family:宋体;word-wrap: normal; word-break: normal;">DECLARE
x number;
BEGIN
x:=0;
WHILE x<9 LOOP
x:=x+1;
DBMS_OUTPUT.PUT_LINE('内:x='||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外:x='||x);
END;
/</span></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"> <wbr><wbr></wbr></wbr></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"><span style="color:#FF0000;word-wrap: normal; word-break: normal;">4。ORACLE中的LOOP循环用法</span></div><div style="font-family: simsun; font-size: 14px; line-height: 21px; background-color: rgb(204, 232, 207);"><span style="font-family:宋体;word-wrap: normal; word-break: normal;">DECLARE
x number;
BEGIN
x:=0;
LOOP
x:=x+1;
EXIT WHEN x>9;
DBMS_OUTPUT.PUT_LINE('内:x='||x);
END LOOP;
DBMS_OUTPUT.PUT_LINE('外:x='||x);
END;</span></div>

1.2与SQL语言整合（参考DML/DDL/DCL及动态SQL）

只能直接使用DML语句，处理DDL语句的方式是使用动态SQL。

1.3面向对象开发

<pre name="code" class="html">CREATE OR REPLACE TYPE emp_obj AS OBJECT();

--定义对象类型体，实现对象方法

CREATE OR REPLACE TYPE BODY  emp_obj AS...
END;

1.4模块化应用程序开发

创建函数封装

CREATE OR REPLACE FUNCTION getaddsr(p_job VARCHAR2) 
RETURN NUMBER 
AS 
v_result NUMBER(7,2);
BEGIN
 IF...THEN...;
 ELSIF...THEN...;
END IF;
RETURN v_result;
END;

调用函数简化程序

v_ratio:=getaddsr(v_job);

1.5提高应用程序性能

通过在应用程序中嵌入PL/SQL块，在网络上只需要发送一次PL/SQL块，就可以同时执行多条SQL语句，大大提高了程序性能。