PL/SQL示例
11-26-1set 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;
ELSIFv_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;
页:
[1]