PL/SQL示例

huijial

　　11-26-1
　　set serveroutput on;
　　declare
　　v DATE;
　　begin
　　select sysdate into v from dual;
　　dbms_output.put_line('当前时间：' || v);
　　end;
　　/
　　11-26-2
　　set serveroutput on
　　declare
　　dbms_output.put_line('现在的日期时间：')；
　　dbms_output.put('今天是：')；
　　dbms_output.put_line(to_char(sysdate,'DAY'));
　　dbms_output.put('现在的时间是：')；
　　dbms_output.put_line(to_char(sysdate,'YYYY-MM-DD HH24:MM:SS'));
　　end;
　　/
　　dbms_output.put_line  会在最后有个回车
　　dbms_output.put  没有回车
　　11-26-3
　　declare
　　v_EmpName varchar2(50);
　　begin
　　select EName into v_empname from select scott.emp where empNo=&EmpNo;
　　dbms_output.put_line('当前查询的员工编号为：'|| &EmpNo||'员工名称：'||v_Emp
　　Name);
　　end;
　　/
　　11-26-4
　　create or replace procedure vttpc.vcinv_demo_01(pempno in number) is tmpvar varchar2(100);
　　begin
　　tmpvar := 0;
　　dbms_ouput.put_line('将开始查询数据库：');
　　select ename into tmpvar from scott.emp where empno=pempno;
　　dbms_output.put_line('员工名称为：' || tmpvar);
　　exception
　　when no_data_found then
　　dbms_output.put_line('没有找到该员工记录！');
　　when others then
　　raise;
　　end vcinv_demo_01;
　　/
　　11-26-5
　　declare
　　v_sal1 number;
　　v_sal2 number;
　　v_sumsal number;
　　begin
　　select sal into v_sal1 from emp where empno=&empno1;
　　select sal into v_sal2 from emp where empno=&empno2;
　　v_sumsal := v_sal1 + v_sal2;
　　dbms_output.put_line ( '员工编号为'
　　||&empno1
　　||'的薪资和员工编号为'
　　||&empno2
　　||'的薪资合计为'
　　||v_sumsal );
　　end;
　　/
　　11-26-6
　　declare
　　c_manager constant number := 0.15;
　　c_salesmanconstant number := 0.12;
　　c_clerkconstant number := 0.10;
　　v_jobvarchar(100);
　　begin
　　select job into v_job from scott.emp where empno=&empno1;
　　IF v_job = 'CLERK'
　　THEN
　　updata scott.emp
　　set sal = sal * (1 + c_clerk)
　　where empno = &empno1;
　　ELSIFv_job ='SALESMAN'
　　THEN
　　updata scott.emp
　　set sal = sal * (1 + c_salesman)
　　where empno = &empno1;
　　ELSIF  v_job='MANAGER'
　　THEN
　　updata scott.emp
　　set sal = sal * (1 + c_manager)
　　where empno = &empno1;
　　END IF;
　　dbms_output.put_line('已经为员工' || &empno1 || '成功加薪！');
　　EXCEPTION
　　when no_data_found
　　then
　　dbms_output.put_line('没有找到员工数据');
　　END;
　　11-27-1
　　declare
　　c_managerconstant number := 0.15;     定义变量
　　c_salesmanconstant number := 0.12;
　　c_clerkconstant number := 0.10;
　　v_jobvarchar(100);
　　v_empnovarchar(20);
　　v_enamevarchar(60);
　　cursor c_emp                                          声明游标及查询
　　is
　　select job,empno,ename from scott.emp
　　for update;
　　begin
　　open c_emp;                                           打开游标
　　loop                                                  循环
　　fetchc_emp                                 提取游标数据
　　into v_job,v_empno,v_ename;
　　exit when c_emp%notfound;
　　ifv_job='CLERK'                                  循环开始
　　then
　　update scott.emp
　　set sal = sal * (1 + c_clerk )
　　where current of c_emp;
　　elsifv_job = 'SALSMAN'
　　then
　　update scott.emp
　　set sal = sal * (1 + c_salesman)
　　where current of c_emp;
　　elsifv_job='MANAGER'
　　then
　　update scott.emp
　　set sal = sal * (1 + c_manager)
　　where current of c_emp;
　　end if;
　　dbms_output.put_line ( '已经为员工' || v_empno || ':' || v_ename || '成功加薪' );
　　end loop;                                              循环结束
　　close c_emp;                                           关闭游标
　　exception                                                      异常处理
　　when no_data_found
　　then
　　dbms_output.put_line ('没有找到员工的工资');
　　end;
　　11-27-2
　　执行DDL 语句  需要使用动态sql   否则 oracle 会报错
　　declare
　　v varchar(300) :='drop table t';
　　begin
　　execute immediate v;
　　end;
　　/
　　11-27-3
　　有问题
　　create or replace type emp_job as object
　　(
　　empno number(4),
　　ename varchar(10),
　　job varchar(9),
　　sal number(7,2),
　　deptno number(2),
　　member procedure addsalary(ratio number));
　　create or replace type body emp_obj
　　as
　　member procedure addsalary(ratio number)
　　is
　　begin
　　sal := sal * (1 + ratio);
　　end;
　　end;
　　11-27-4
　　create or replace function getaddsalaryratio(p_job varchar2) return number
　　as
　　v_result number(7,2);
　　begin
　　ifp_job = 'CLERK'
　　then
　　v_result :=  0.10;
　　elsif p_job = 'SALESMAN'
　　then
　　v_result := 0.12;
　　elsifp_job ='MANAGER'
　　then
　　v_result := 0.15;
　　end if;
　　return v_result;
　　end;
　　11-27-5
　　有问题
　　declare
　　v_jobvarchar(100);
　　v_empnovarchar(20);
　　v_enamevarchar(60);
　　v_ratio number(7,2);
　　cursor c_emp
　　is
　　select job,empno,ename from scott.emp for update;
　　begin
　　open c_emp;
　　loop
　　fetch c_emp
　　into v_job,v_empno,v_ename;
　　exit when c_emp%notfound;
　　v_ratio := getaddsalaryratio(v_job);
　　update scott.emp
　　set sal = sal * (1 + v_ratio);
　　where current of c_emp;
　　dbms_output.put_line ( '已经为员工' || v_empno || ':' || v_ename || '成功加薪');
　　end loop;
　　close c_emp;
　　exception
　　whenothers
　　then
　　dbms_output.put_line('没有找到员工数据');
　　end;
　　11-27-6
　　在scott用户下执行
　　declare
　　v_deptcountnumber(2);
　　v_deptno number(2) := 80;
　　begin
　　select count(1) into v_deptcount from dept where deptno = v_deptno;
　　if v_deptcount=0
　　then
　　insert into dept values(v_deptno,'财务部','深圳1');
　　dbms_output.put_line('成功插入部门资料');
　　end if;
　　exception
　　when others
　　then
　　dbms_output.put_line('部门资料插入失败');
　　end;
　　11-28-1
　　declare
　　v_deptcountnumber(2);
　　v_deptno number(2) := 60;
　　v_deptnamevarchar(12);
　　begin
　　begin
　　select dname into v_deptname from dept where deptno= v_deptno;
　　dbms_output.put_line('您查询的部门名称为' || v_deptname);
　　end;
　　declare
　　v_locvarchar(10) := '深圳罗湖';
　　begin
　　update dept
　　set loc=v_loc
　　where deptno=v_deptno;
　　dbms_output.put_line('在内部嵌套块中成功更新部门资料');
　　end;
　　exception
　　when no_data_found
　　then
　　begin
　　insert into dept values(v_deptno,'财务部','深圳');
　　dbms_output.put_line('在异常处理嵌套块成功插入部门资料');
　　exception
　　when others
　　then
　　dbms_output.put_line(SQLERRM);
　　end;
　　end;
　　11-28-2
　　declare
　　v_deptcountnumber(2);
　　v_deptnonumber(2) := 60;
　　v_deptnamevarchar2(12);
　　begin
　　begin
　　select dname into v_deptname from dept where deptno = v_deptno;
　　dbms_output.put_line('您查询的部门名称为' || v_deptname);
　　end;
　　declare
　　v_locvarchar(10) := '深圳罗湖';
　　begin
　　update dept set loc =v_loc where deptno = v_deptno;
　　dbms_output.put_line('在内部嵌套块中成功更新部门资料');
　　end;
　　exception
　　when no_data_found
　　then
　　begin
　　insert into dept values(v_deptno,'财务部','深圳');
　　dbms_output.put_line('在异常处理嵌套块成功插入部门资料');
　　exception
　　when others
　　then
　　dbms_output.put_line(SQLERRM);
　　end;
　　end;
　　11-28-3
　　declare
　　v_deptnamevarchar2(10);
　　v_loopcounterbinary_integer;
　　type t_employee is record(empname varchar2(20),empno number(7),job varchar2(20));
　　v_employeet_employee;
　　type csor is ref cursor;
　　v_datedate not null default sysdate;
　　begin
　　null;
　　end;
　　11-28-4
　　case-when  当发现一个条件出问题时   就会报错
　　create or replace function getaddsalaryratiocase(p_job varchar2)
　　return number
　　as
　　v_resultnumber(7,2);
　　begin
　　casep_job
　　when'CLERK'
　　then
　　v_result := 0.10;
　　when'SALESMAN'
　　then
　　v_result := 0.12;
　　when'MANAGER'
　　then
　　v_result := 0.15;
　　end case;
　　return v_result;
　　end;
　　11-28-5
　　99乘法表
　　set serveroutput on
　　declare
　　v_number1number(3);
　　v_number2number(3);
　　begin
　　forv_number1 in 1 .. 9
　　loop
　　for v_number2 in 1 .. v_number1
　　loop
　　dbms_output.put(v_number1 || '*' || v_number2 || '=' || v_number1 * v_number2 || '  ');
　　end loop;
　　dbms_output.put_line('');
　　end loop;
　　end;
　　11-28-6
　　create or replace procedure addempsalary(p_ratio number,p_empno number)
　　as
　　begin
　　if p_ratio > 0
　　then
　　update scott.emp set sal =  (1 + p_ratio) where empno = p_empno;
　　end if;
　　dbms_output.put_line('加薪成功');
　　end;
　　11-28-7
　　包
　　create or replace package empsalary
　　as
　　procedure addempsalary(p_ratio number,p_empno number);
　　function getaddsalaryratio(p_job varchar2) return number;
　　function getaddsalaryratiocase(p_job varchar2) return number;
　　end empsalary;
　　create or replace package body empsalary
　　as
　　procedure addempsalary(p_ratio number,p_empno number)
　　as
　　begin
　　if p_ratio > 0
　　then
　　update scott.emp set sal = sal * (1 + p_ratio) where empno = p_empno;
　　end if;
　　dbms_output.put_line('加薪成功');
　　end;
　　function getaddsalaryratio(p_job varchar2)
　　return number
　　as
　　v_result number(7,2);
　　begin
　　if p_job='CLERK'
　　then
　　v_result := 0.10;
　　elsif p_job='SALESMAN'
　　then
　　v_result := 0.12;
　　elsif p_job='MANAGER'
　　then
　　v_result := 0.15;
　　end if;
　　return v_result;
　　end;
　　function getaddsalaryratiocase(p_job varchar2)
　　return number
　　as
　　v_result number(7,2);
　　begin
　　case p_job
　　when 'CLERK'
　　then
　　v_result := 0.10;
　　when 'SALESMAN'
　　then
　　v_result := 0.12;
　　when 'MANAGER'
　　then
　　v_result := 0.15;
　　end case;
　　return v_result;
　　end;
　　end empsalary;
　　11-28-8
　　触发器(有错误)
　　create table scott.raisesalarylog2
　　(
　　empno number(10) not null primary key,
　　raiseddate DATE,
　　originalsal number(10,2),
　　raisesal number(10,2)
　　);
　　create or replace trigger scott.raisesalarychange2
　　after
　　update of sal on scott.emp
　　for each row
　　declare
　　v_reccount int;
　　begin
　　select count(*) into v_reccount from scott.raisesalarylog2
　　where empno = :OLD.empno;
　　if v_reccount = 0
　　then
　　insert into scott.raisesalarylog2 values(:OLD.empno,SYSDATE,:OLD.sal,:NEW.sal);
　　else
　　update scott.raisesalarylog2
　　set raiseddate = SYSDATE,
　　originalsal = :OLD.sal,
　　raisedsal = :NEW.sal
　　where empno = :OLD.empno;
　　end if;
　　exception
　　when others
　　then
　　dbms_output.put_line(SQLERRM);
　　end;
　　11-28-9
　　declare
　　v_ename varchar2(30);
　　begin
　　select ename into v_ename from emp where empno = & empno;
　　dbms_output.put_line('员工名称为：'|| v_ename);
　　exception
　　when no_data_found
　　then
　　dbms_output.put_line('没有找到记录');
　　when others
　　then
　　dbms_output.put_line('其他未处理异常');
　　end;
　　12-8-1
　　declare
　　type emp_info_type is record(
　　empnamevarchar2(10),
　　jobvarchar(9),
　　salnumber(7,2)
　　);
　　empinfo emp_info_type;
　　begin
　　select ename,job,sal into empinfo from emp where empno = &empno;
　　dbms_output.put_line('员工信息为：员工姓名：' || empinfo.empname || '职位：' || empinfo.job || '薪资' || empinfo.sal);
　　end;
　　12-8-2
　　declear
　　type emp_table is table of varchar2(10)
　　index by binary_integer;
　　emplistemp_table;
　　cursorempcursor
　　is
　　select ename from emp;
　　begin
　　if not empcursor%ISOPEN
　　then
　　open empcursor;
　　end if;
　　fetch empcursor
　　bulk collect into emplist;
　　for i in 1 .. emplist.count
　　loop
　　dbms_output.put_line('员工名称：' || emplist(i));
　　end loop;
　　close empcursor;
　　end;
　　12-8-3
　　declare
　　v_sqlstrvarchar2(200);
　　v_idint;
　　v_namevarchar(100);
　　begin
　　begin
　　v_sqlstr := 'drop table temptable';
　　execute immediate v_sqlstr;
　　exception
　　when others
　　then
　　null;
　　end;
　　v_sqlstr := 'create table temptable(id int not null primary key,tmpname varchar2(100))';
　　execute immediate v_sqlstr;
　　v_sqlstr := 'insert into temptable values(10,''临时名称1'')';
　　execute immediate v_sqlstr;

　　v_sqlstr := 'select * from temptable where>　　execute immediate v_sqlstr into v_id,v_name using &1;
　　dbms_output.put_line(v_id || '' || v_name);
　　end;
　　12-8-4 %ROWTYPE
　　declare
　　v_emp emp%ROWTYPE;
　　begin
　　select * into v_emp from emp where empno = &empno;
　　dbms_output.put_line(v_emp.empno  || '
　　'|| v_emp.ename);
　　end;
　　12-8-5
　　declare
　　cursor emp_cursor
　　is
　　select empno,ename,job,sal,hiredate from emp;
　　v_emp emp_cursor%ROWTYPE;
　　begin
　　open emp_cursor;
　　loop
　　fetch emp_cursor into v_emp;
　　exit when emp_cursor%NOTFOUND;
　　dbms_output.put_line(v_emp.empno || ' '|| v_emp.ename || ' '|| v_emp.job || v_emp.sal || ' '||TO_CHAR(v_emp.hiredate,'YYYY-MM-DD'));
　　end loop;
　　close emp_cursor;
　　end;