Oracle 顺序控制,异常处理,动态sql

emltycpqzd

-----------------顺序控制--------------------
--1.  GOTO语句　　：用于跳转到特定标记号去执行语句  (使可读性变差)
--2. null 语句 ：不会执行任何操作，并直接将控制传递下一条语句   （使可读性变强）
--示例1
Declare
v_sal emp.sal%type;
v_name emp.ename%type；
Begin
select sal,ename into v_sal,v_ename from emp where empno=&empno;
if v_sal<3000 then
update emp set comm=sal*0.1 where ename=v_ename;
else
null;
end if;
END;



　　
---------------------异常处理--------------------
-- 语法
Exception
when  异常错误的名称1 [or 异常错误名称2...] then
执行语句1；
when  异常错误的名称3 [or 异常错误名称4...] then
执行语句2；
when others then
执行语句3；
--示例1
declare
v_dept_row dept%rowtype;  --定义变量，用于保存dept表的相关信息
begin
select * into v_dept_row form dept;  --查询dept中的所有记录 将触发too_mang_rows 异常;
--向dept表总插入数据， 会违反主键唯一约束， 会触发 DUP_val_on_index 异常
insert into dept values(10,'product','china');  --
Exception
when too_many_rows then
Dbms_Output.put_line('返回了多行，请使用游标来处理多行记录的集合！');
when DUP_val_on_index then
Dbms_Output.put.line('主键不能重复！');
END;
-----------------自定义异常--------------------------
--示例1  
declare
ept_no_emp Exception;
Begin
update emp set comm=case when comm is null then 50 else comm*1.1 end where empno=&empno;
If sql%notfound then
raise ept_no_emp ;   --如果没有数据更新 就抛出异常 使用raise;
else
DBMS_output.put_line('奖金已经更新了！');  
Exception
when ept_no_emp then
Dbms_Output.put_line('该员工编号不存在！');      
END;




--开发动态SQL
declare
type Create_table_record is record
(
filed_name varchar2(15),
filed_type varchar2(15),
filed_explain varchar2(15)
);
type Dynamic_sql_table is table of Create_table_record index by binary_integer;
v_dynamic_sql_table_name Dynamic_sql_table;
v_create_table_name varchar2(20);
v_dynamic_ddl_sal varchar2(500);
v_dynamic_dcl_sal varchar2(500);
v_grant_user varchar2(10);
v_grant_authority varchar2(10);
Begin
v_create_table_name:='STUD';
v_grant_user:='scott';
v_grant_authority:='select';
v_dynamic_sql_table(0).field_name:='sid';
v_dynamic_sql_table(0).field_type:='varcar2(10)';
v_dynamic_sql_table(0).field_explain:='primary key';
v_dynamic_sql_table(1).field_name:='sname';
v_dynamic_sql_table(1).field_type:='varcar2(10)';
v_dynamic_sql_table(1).field_explain:='not null';
v_dynamic_sql_table(2).field_name:='sclass';
v_dynamic_sql_table(2).field_type:='varcar2(10)';
v_dynamic_sql_table(2).field_explain:='not null';
v_dynamic_ddl_sql:='create table'||v_create_table_name||chr(13)||'('||chr(13);
for i in 1..v_dynamic_sql_table.count-1
Loop
v_dynamic_ddl_sql：=v_dynamic_ddl_sql||v_dynamic_sql_table(i).field_name ||' '||
v_dynamic_sql_table(i).field_type || ' '||v_dynamic_sql_table(i).field_explain ||','||chr(13);
End lOOP;
v_dynamic_ddl_sql:=substr(v_dynamic_ddl_sql,0,lenth(v_dyname_ddl_sql)-2);
v_dynamic_ddl_sql:=v_dynamic_ddl_sql||chr(13)||')';
v_dynamic_dcl_sql:='grant'||v_grant_authority||'on'||c_create_table_name||'to'||v_grant_user;
execute immediate v_dynamic_ddl_sql;
execute immediate v_dynamic_dcl_sql;
End;
--处理无占位符和returning子句的DML语句
--示例1
declare
v_dynamic_sql varchar2(100);
begin
v_dynamic_sql:='Update emp set sal=sal*1.1 where deptno=30';
execute immediate v_dynamic_sql;
END;

--示例2
declare
v_dynamic_sql varchar2(100);
begin
v_dynamic_sql:='update emp set sal=sal*(1+:percent/100.0) where deptno=:deptno';
execute immediate v_dynamic_sql Using &percent,&deptno;
END;
--示例3
declare
v_dynamic_sql varchar2(100);  --存储SQL语句的变量
v_sal number(7,2);   --存储工资的变量
v_empno number(4):=&deptno;  --接受用户输入的编号变量
v_percent number(2):=&percent;   --定义增加员工工资的百分比变量
Begin
select sal into v_sal from emp where empno=v_empno; --查询更新前的个工资
DBMS_OUTPUT.put_line('更新前的工资'||v_sal);  --输出
--要执行的sql 语句 并返回 跟新后的 sal  
v_dynamic_sql:='update emp set sal=sal*(1+:percent/100.0)' where empno=:empno returning sal into :sal;

--执行定义的sql语句  并使用returning into 接受放回的值；
execute immediate v_dyname_sql using v_percent,v_empno returning into v_sal;
Dbms_Output.put_line('增长率：'||v_percent||'%');
Dbms_Output.put_line('新工资：'||v_sal);
END;
--示例4   --处理单行查询语句
declare
v_dynamic_sql varchar2(100);   
emp_record emp%rowtype;
begin
v_dynamic_sql:='select * from emp where empno=:empno';
execute immediate v_dynamic_sql into emp_record using &empno;
DBMS_output.put_line('雇员：'||emp_record.ename||'的工资是:'||emp_record_sal);
--示例5  是用Bulk collect
declare
type emp_ename_table Is table of emp.ename%type index by binary_integer;  --一个列类型
v_emp_ename_table emp_ename_table;  --一个列类型的变量
v_dynamic_sql varchar2(100);  --保存要执行的sql语句
begin
v_dynamic_sql:='select ename from emp where deptno=:deptno';   --给变量赋值
execute immediate v_dynamic_sql bulk collect into v_emp_enam_table using &deptno;
for i in 1..v_emp_ename_table.cont
LOOP
dbms_output.put_line(v_emp_ename_table(i));
END　loop;
END;