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;