Oracle学习笔记

1–基本结构

 

存储过程（Procedure）的定义：
A named PL/SQL block that performs one or more actions and is called as an executable PL/SQL statement. You can pass information into and out of a procedure through its parameter list.

存储过程的结构如下：

PROCEDURE name [ ( parameter [, parameter ... ] ) ]

IS

[declaration statements]

BEGIN

executable-statements

[ EXCEPTION

exception handler statements]

END [ name ];

无参存储过程示例：

1. create or replace procedure helloWorld IS
2.  
3. BEGIN
4.  
5.   dbms_output.put_line('hell world!');
6.  
7. END helloWorld;

有参存储过程示例：

1. create or replace procedure test_param(test_id IN number) IS
2.  
3. BEGIN
4.  
5.   dbms_output.put_line(test_id);
6.  
7. END test_param;

无参存储过程的运行：
1、首先打开PL/SQL Developer，连接到一个测试数据库；
2、选择file –>new –> program window –> blank，新建一个编程窗口；
3、把代码黏贴进去或者敲进去；
4、点击工具栏上的齿轮按钮或者用快捷键F8，编译该存储过程；
5、在左侧的对象浏览器窗口中展开 Procudures，找到刚才编译的存储过程，没有的话刷新一下再找找看；
6、选中存储过程，点击右键，选择test；
7、点击工具栏上的齿轮按钮或者用快捷键F8，运行该存储过程；
8、在DBMS Outputs 标签页中可以看到输出：hell world!。

有参存储过程的运行：
在步骤7运行存储过程的时候，可以在Test script窗口下部的表格的第三列中输入测试参数，输入测试参数之后，再点击运行；
其他步骤相同。

 

2–变量的使用

 

变量的命名规则
1、Can be up to 30 characters in length
2、Must start with a letter
3、Can then be composed of any of the following: letters, numerals, $, #, and _

变量的数据类型
varchar2(n)
长度可变得字符数据，n表示这个列的最大长度。最大字符长度为4000个字符；

char(n)
长度固定的字符列；默认大小为1。最大大小是2000个字符，如果输入的字符小于n，则向右边添加空格；

number(p,s)
p表示精确度，即小数点左边和右边的总位数，最大值为38位，s为小数位数，表示小数点右边的位数，如果没有制定p或s那么精确度将默认38位；

date
存储公元前4721年1月1日与公元9999年12月31日间的日期和时间；
这个列默认用7个字节存储日期的世纪、年、月、日、小时、分、秒；
Oracle9i以DD-MON-YY格式显示日期；
可以用to_char格式化日期；

变量的声明
变量的声明一般都放在关键字AS和BEGIN之间，声明格式如下：
identifier_name identifier_type := default_value。

示例：

1. CREATE OR REPLACE PROCEDURE procedure_name
2. AS
3.   v_object_id        varchar2(10) := '01';
4.   v_object_name   dqcobject.object_name%type;
5.   v_object_type    varchar2(5);
6. BEGIN
7.  
8.     procudure body....
9.  
10. END;

其中，v_object_id已经赋值为’01′，v_object_name的类型与表dqcobject中object_name字段的类型一致。

变量的使用

示例：

1. create or replace procedure helloWorld IS
2.   v_object_id     varchar2(10) := '01';
3.   v_object_name   dqcobject.object_name%type;
4.   v_object_type   varchar2(5);
5. BEGIN
6.   SELECT a.object_name, a.object_type
7.   INTO v_object_name, v_object_type
8.   FROM dqcobject a
9.   WHERE a.object_id = v_object_id;
10.   dbms_output.put_line(v_object_name);
11.   dbms_output.put_line(v_object_type);
12.  
13. END helloWorld;

3–判断语句

 

IF语句的语法如下：
IF

THEN

… sequence of executable statements …

END IF;

示例：

1. create or replace procedure a_test(age IN INT) IS
2. BEGIN
3.   IF age > 18 THEN
4.     dbms_output.put_line('age great than 18! ');
5.   END IF;
6.  
7. END a_test;

IF-ELSE语句的语法如下：
IF

THEN

… TRUE sequence of executable statements …

ELSE

… FALSE/NULL sequence of executable statements …

END IF;

示例：

1. create or replace procedure a_test(age IN INT) IS
2. BEGIN
3.   IF age > 18 
4.   THEN
5.     dbms_output.put_line('age > 18! ');
6.   ELSE
7.     dbms_output.put_line('age <= 18! ');
8.   END IF;
9.  
10. END a_test;

IF-ELSIF的语法格式如下：
IF

THEN

 

…

ELSIF

THEN

 

示例：

1. create or replace procedure a_test(age IN INT) IS
2. BEGIN
3.   IF age > 18 
4.   THEN
5.     dbms_output.put_line('age > 18! ');
6.   ELSIF age = 18
7.   THEN
8.     dbms_output.put_line('age = 18! ');
9.   ELSIF age < 18
10.   THEN
11.     dbms_output.put_line('age < 18! ');
12.   END IF;
13.  
14. END a_test;

4–循环语句

 

循环控制的基本形式是LOOP语句，LOOP与END LOOP之间的语句将无限次执行。

LOOP语句的语法如下：
LOOP
statements;
END LOOP;

示例：

1. CREATE OR REPLACE PROCEDURE helloworld IS
2. v_counter int := 10;
3. BEGIN
4.   LOOP
5.     v_counter := v_counter - 1;
6.     dbms_output.put_line(v_counter);
7.     IF v_counter <= 0 THEN
8.       EXIT;
9.     END IF;
10.   END LOOP;
11. END;

退出循环的时候，也可以使用：EXIT WHEN语句，如果条件为TRUE，则结束循环，如下示例：

1. CREATE OR REPLACE PROCEDURE helloworld IS
2. v_counter int := 10;
3. BEGIN
4.   LOOP
5.     v_counter := v_counter - 1;
6.     dbms_output.put_line(v_counter);
7.     EXIT WHEN v_counter <= 0;
8.   END LOOP;
9. END;

WHILE…LOOP的语法如下：
WHILE condition LOOP
statement;
END LOOP;

示例：

1. CREATE OR REPLACE PROCEDURE helloworld IS
2. v_counter int := 10;
3. BEGIN
4.   WHILE v_counter > 0 LOOP
5.     v_counter := v_counter - 1;
6.     dbms_output.put_line(v_counter);
7.   END LOOP;
8. END;

FOR…LOOP的语法如下：
FOR counter IN [REVERSE] LOOP start_range .. end_range LOOP
statement;
END LOOP;

示例：

1. CREATE OR REPLACE PROCEDURE helloworld IS
2. BEGIN
3.   FOR i in 1..10 LOOP
4.     dbms_output.put_line(i);
5.   END LOOP;
6. END;

说明：
1、start_range 和end_range之间用两个’.'，用三个’.'的时候，上面的测试代码没有输出。
2、REVERSE表示递减