oracle SQL 控制,Oracle 顺序控制,错误处理,动态sql

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

-----------------顺序控制--------------------

--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;