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_salesman constant number := 0.12;

c_clerk constant number := 0.10;

v_job varchar(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;

ELSIF v_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_manager constant number := 0.15;     定义变量

c_salesman constant number := 0.12;

c_clerk constant number := 0.10;

v_job varchar(100);

v_empno varchar(20);

v_ename varchar(60);

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;

if v_job='CLERK'                                  循环开始

then

update scott.emp

set sal = sal * (1 + c_clerk )

where current of c_emp;

elsif v_job = 'SALSMAN'

then

update scott.emp

set sal = sal * (1 + c_salesman)

where current of c_emp;

elsif v_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

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;



11-27-5

有问题

declare

v_job varchar(100);

v_empno varchar(20);

v_ename varchar(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

when others

then

dbms_output.put_line('没有找到员工数据');

end;


11-27-6

在scott用户下执行

declare

v_deptcount number(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_deptcount number(2);

v_deptno number(2) := 60;

v_deptname varchar(12);

begin

begin

select dname into v_deptname from dept where deptno= v_deptno;

dbms_output.put_line('您查询的部门名称为' || v_deptname);

end;

declare

v_loc varchar(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_deptcount number(2);

v_deptno number(2) := 60;

v_deptname varchar2(12);

begin

begin

select dname into v_deptname from dept where deptno = v_deptno;

dbms_output.put_line('您查询的部门名称为' || v_deptname);

end;

declare

v_loc varchar(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_deptname varchar2(10);

v_loopcounter binary_integer;

type t_employee is record(empname varchar2(20),empno number(7),job varchar2(20));

v_employee t_employee;

type csor is ref cursor;

v_date date not null default sysdate;

begin

null;

end;


11-28-4

case-when  当发现一个条件出问题时   就会报错

create or replace 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;


11-28-5

99乘法表

set serveroutput on

declare

v_number1 number(3);

v_number2 number(3);

begin

for v_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(

empname varchar2(10),

job varchar(9),

sal number(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;

emplist emp_table;

cursor empcursor

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_sqlstr varchar2(200);

v_id int;

v_name varchar(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 id=:tempid';

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;