oracle pl/sql之pl/sql语法

vivion34

　　一.pl/sql基础
　　pl/sql分匿名块和命名块.
　　命名块:存储过程，函数，触发器，包等.
　　pl/sql语句块分3部分：
　　(1)声明部分
　　(2)可执行部分
　　(3)异常处理部分
　　其中可执行部分是语句块中唯一要求必须存在的部分，声明部分和异常处理部分是可选的。
　　1.匿名块语法：
　　DECLARE
　　declaration statements
　　BEGIN
　　executable statements
　　EXCEPTION
　　exception-handing statements
　　END;
　　2.声明变量
　　语法：
　　identifier [CONSTANT] datatype [NOT NULL] [:= value | DEFAULT expr]
　　声明变量方法1：
　　declare
　　v_first_name varchar2(50);
　　c_count   constant number :=0;  ---常量
　　v_hiredate  date;
　　v_valid   BOOLEAN NOT NULL DEFAULT TRUE;
　　声明变量方法1：
　　declare
　　v_ename emp.ename%type;
　　v_sal emp.sal%type;
　　//显示雇员的姓名，工资,个人所得税
set serveroutput on;　　
declare
　　
v_ename varchar2(20);
　　
v_sal   number(6,2);
　　
c_tax_rate constant number(3,2) :=0.06;      ---税率
　　
v_tax_sal  number(6,2);
　　
begin
　　
select ename,sal into v_ename,v_sal from emp where empno = &no;
　　
v_tax_sal := v_sal * c_tax_rate;     ---赋值
　　
dbms_output.put_line('v_ename: ' || v_ename);
　　
dbms_output.put_line('v_sal: ' || v_sal);
　　
dbms_output.put_line('v_tax_sal: ' || v_tax_sal);
　　
end;
　　3.标识符的命名规则
　　(1)定义变量，建议用v_,如v_ename
　　(2)定义常量，建议用c_,如c_tax_rate
　　(3)定义游标，建议用_cursor作为后缀,如emp_cursor
　　(4)定义表类型，建议用_table_type作为后缀，如sal_table_type;
　　(5)定义表变量，建议用_table作为后缀，例如sal_table;
　　4.PL/SQL编译过程步骤
　　编译过程包括语法检查，绑定以及为代码生成。语法检查涉及检查PL/SQL代码中的编译错误。在纠正语法错误之后，会给每个变量分配内存地址，以保存ORACLE数据，这个过程成为绑定。接下来，会产生PL/SQL语句块的伪代码，伪代码是PL/SQL引擎的指令列表，对于命名语句块，伪代码会存储在数据库中，并在程序下一次执行时使用。
　　5.替代变量
　　在匿名PL/SQL块中接受输入参数使用&或者&&作为替代变量
　　6.初始化变量用select into语法
//求emp表中的平均工资　　
SQL> set serveroutput on;
　　
SQL>
　　
SQL> declare
　　2  v_avg_sal number(6,2);
　　3  begin
　　4  select avg(sal) into v_avg_sal from emp;
　　5  dbms_output.put_line('v_avg_sal: ' || v_avg_sal);
　　6  end;
　　7  /                  ---表示执行
　　
v_avg_sal: 2530.88
　　
PL/SQL procedure successfully completed.
　　二.控制语句
　　7.if语句
　　if语法：
　　IF condition THEN
　　statements;
　　[ELSIF condition THEN
　　statements;]
　　[ELSE
　　statements;
　　]
　　END IF;
　　//如果工资小于2000,把雇员的薪水加薪50
setserveroutput on;　　
declare
　　v_sal emp.sal%type;
　　
begin
　　select sal into v_sal from emp where lower(ename)=lower('&name');
　　
if v_sal 100;
　　
i := i+1;
　　
end loop;
　　
end;
　　//while循环
　　while condition loop
　　statement1;
　　statement2;
　　......
　　end loop;
　　//例子
declare i number :=1;　　
begin
　　
while i 　　
type name_table_type is table of varchar2(10) index by binary_integer;

　　
id_table>　　
name_table name_table_type ;
　　
start_time number;
　　
end_time number;
　　
begin
　　
for i in 1..5000 loop
　　id_table(i):=i;
　　
name_table(i):='name' || i;
　　
end loop;
　　
start_time := DBMS_UTILITY.GET_TIME;
　　
forall i in 1..5000
　　insert into tmp01 values(id_table(i),name_table(i));
　　
commit;
　　
end_time := DBMS_UTILITY.GET_TIME;
　　dbms_output.put_line('total time is: ' || to_char((end_time-start_time)/100));
　　
end;
　　5.索引表
　　---语法
　　TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY key_type;
　　indentifer type_name;
　　type_name指定用户自定义数据类型名称
　　element_type 指定索引表元素的数据类型
　　key_type指定索引表元素下标的数据类型(varchar2,binary_integer,pls_integer等)
　　indentifer指定索引表变量
　　索引表的下标可以为负值，而且元素个数没有限制。
　　---code
set serveroutput on;　　
declare
　　
type ename_table_type is table of emp.ename%type index by binary_integer;
　　
ename_table ename_table_type;
　　
begin
　　select ename into ename_table(1) from emp where empno=&no;
　　dbms_output.put_line('employee name : ' || ename_table(1));
　　
end;
　　---索引表中使用varchar2
set serveroutput on;　　
declare
　　
type city_table_type is table of number index by varchar2(10);
　　
city_table city_table_type;
　　
begin
　　
city_table('zhouzq') := 1;
　　
city_table('lidd') := 2;
　　
city_table('lijian') := 3;
　　
dbms_output.put_line('the first element: ' || city_table.first);
　　
dbms_output.put_line('the last element: ' || city_table.last);
　　
end;
　　6.游标
　　使用显示游标包括定义游标，打开游标，提取游标和关闭游标。
　　(1)定义游标
　　CURSOR cursor_name IS  select_statement;
　　(2)打开游标
　　OPEN cursor_name;
　　(3)提取游标
　　---提取一行
　　FETCH cursor_name into var1,var2...;
　　---提取多行
　　FETCH cursor_name BULK COLLECT INTO collect1,collect2...[LIMIT rows];
　　---collect1用于指定接受游标的集合变量
　　(4)关闭游标
　　CLOSE cursor_name;
　　显示游标属性：%isopen,%found,%notfound,%rowcount
　　---显示游标中使用fetch...into
　　//提取雇员名，薪水
set serveroutput on;　　
declare
　　
cursor emp_cursor is select ename,sal from emp where deptno=10;
　　
v_ename emp.ename%type;
　　
v_salary emp.sal%type;
　　
begin
　　
open emp_cursor;
　　
loop
　　
fetch emp_cursor into v_ename,v_salary;
　　
exit when emp_cursor%notfound;
　　
dbms_output.put_line(v_ename || ' : ' || v_salary);
　　
end loop;
　　
end;
　　---显示游标中使用fetch...bulk collect into
　　###显示部门10的所有雇员名
set serveroutput on;　　
declare
　　
cursor ename_cursor is select ename from emp where deptno=10;
　　
type ename_table_type is table of varchar2(10);
　　
ename_table ename_table_type;
　　
begin
　　
open ename_cursor;
　　
fetch ename_cursor bulk collect into ename_table;
　　
for i in 1..ename_table.count loop
　　dbms_output.put_line(ename_table(i));
　　
end loop;
　　
close ename_cursor;
　　
end;
　　---游标FOR循环
　　语法：
　　FOR record_name IN cursor_name loop
　　statement1;...
　　end loop;
　　cursor_name:已定义的游标名称
　　record_name:隐含定义的记录变量名
　　在执行循环体内容之前，oracle会隐含地打开游标，并且每循环一次提取一次数据，提取完所有数据后，会自动退出循环并隐含关闭游标。
　　---FOR循环游标
set serveroutput on;　　
declare
　　
cursor ename_cursor is select ename from emp;
　　
begin
　　
for cc_record in ename_cursor loop
　　
dbms_output.put_line(ename_cursor%rowcount ||'： ' || cc_record.ename);
　　
end loop;
　　
end;
　　---游标FOR循环中直接使用子查询
set serveroutput on;　　
begin
　　
for cc_record in (select ename from emp) loop
　　
dbms_output.put_line( cc_record.ename);
　　
end loop;
　　
end;