PL/SQL示例

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 

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

dbms_output.put_line  會在最後有個回車

dbms_output.put  沒有回車

11-26-3

v_EmpName varchar2(50);

select EName into v_empname from select scott.emp where empNo=&EmpNo;

dbms_output.put_line('目前查詢的員工編号為：'|| &EmpNo||'員工名稱：'||v_Emp

Name);

11-26-4

create or replace procedure vttpc.vcinv_demo_01(pempno in number) is tmpvar varchar2(100);

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

v_sal1 number;

v_sal2 number;

v_sumsal number;

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

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

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)

ELSIF   v_job='MANAGER'

set sal = sal * (1 + c_manager)

END IF;

dbms_output.put_line('已經為員工' || &empno1 || '成功加薪！');

EXCEPTION

when no_data_found

then

dbms_output.put_line('沒有找到員工資料');

END;

11-27-1

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;

open c_emp;                                           打開遊标

loop                                                  循環

fetch c_emp                                 提取遊标資料

into v_job,v_empno,v_ename;

exit when c_emp%notfound;

if v_job='CLERK'                                  循環開始

update scott.emp

set sal = sal * (1 + c_clerk )

where current of c_emp;

elsif v_job = 'SALSMAN'

elsif v_job='MANAGER'

end if;

dbms_output.put_line ( '已經為員工' || v_empno || ':' || v_ename || '成功加薪' );

end loop;                                              循環結束

close c_emp;                                           關閉遊标

exception                                                      異常處理

dbms_output.put_line ('沒有找到員工的工資');

11-27-2

執行DDL 語句  需要使用動态sql   否則 oracle 會報錯

v varchar(300) :='drop table t';

execute immediate v;

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)

sal := sal * (1 + ratio);

11-27-4

create or replace function getaddsalaryratio(p_job varchar2) return number

v_result number(7,2);

if p_job = 'CLERK'

v_result :=  0.10;

elsif  p_job = 'SALESMAN'

v_result := 0.12;

elsif p_job ='MANAGER'

v_result := 0.15;

return v_result;

11-27-5

v_job varchar(100);

v_empno varchar(20);

v_ename varchar(60);

v_ratio number(7,2);

cursor  c_emp

select job,empno,ename from scott.emp for update;

open c_emp;

loop

fetch c_emp

v_ratio := getaddsalaryratio(v_job);

set sal = sal * (1 + v_ratio);

dbms_output.put_line ( '已經為員工' || v_empno || ':' || v_ename || '成功加薪');

end loop;

close c_emp;

when others

11-27-6

在scott使用者下執行

v_deptcount number(2);

v_deptno  number(2) := 80;

select count(1) into v_deptcount from dept where deptno = v_deptno;

if v_deptcount=0

insert into dept values(v_deptno,'财務部','深圳1');

dbms_output.put_line('成功插入部門資料');

when others

dbms_output.put_line('部門資料插入失敗');

11-28-1

v_deptno  number(2) := 60;

v_deptname varchar(12);

select dname into v_deptname from dept where deptno= v_deptno;

dbms_output.put_line('您查詢的部門名稱為' || v_deptname);

v_loc varchar(10) := '深圳羅湖';

update dept

set loc=v_loc

where deptno=v_deptno;

dbms_output.put_line('在内部嵌套塊中成功更新部門資料');

insert into dept values(v_deptno,'财務部','深圳');

dbms_output.put_line('在異常處理嵌套塊成功插入部門資料');

dbms_output.put_line(SQLERRM);

11-28-2

v_deptno number(2) := 60;

v_deptname varchar2(12);

select dname into v_deptname from dept where deptno = v_deptno;

update dept set loc =v_loc where deptno = v_deptno;

11-28-3

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;

null;

11-28-4

case-when  當發現一個條件出問題時   就會報錯

create or replace function getaddsalaryratiocase(p_job varchar2)

return number

v_result number(7,2);

case p_job

when 'CLERK'

v_result := 0.10;

when 'SALESMAN'

when 'MANAGER'

end case;

11-28-5

99乘法表

set serveroutput on

v_number1 number(3);

v_number2 number(3);

for v_number1 in 1 .. 9

for v_number2 in 1 .. v_number1

dbms_output.put(v_number1 || '*' || v_number2 || '=' || v_number1 * v_number2 || '  ');

dbms_output.put_line('');

11-28-6

create or replace procedure addempsalary(p_ratio number,p_empno number)

if p_ratio > 0

update scott.emp set sal =  (1 + p_ratio) where empno = p_empno;

dbms_output.put_line('加薪成功');

11-28-7

包

create or replace package empsalary

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

procedure addempsalary(p_ratio number,p_empno number)

update scott.emp set sal = sal * (1 + p_ratio) where empno = p_empno;

function getaddsalaryratio(p_job varchar2)

if p_job='CLERK'

elsif p_job='SALESMAN'

elsif p_job='MANAGER'

function getaddsalaryratiocase(p_job varchar2)

case p_job

when 'CLERK'

when 'SALESMAN'

when 'MANAGER'

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

v_reccount int;

select count(*) into v_reccount from scott.raisesalarylog2

where empno = :OLD.empno;

if v_reccount = 0

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

11-28-9

v_ename varchar2(30);

select ename into v_ename from emp where empno = & empno;

dbms_output.put_line('員工名稱為：'|| v_ename);

dbms_output.put_line('沒有找到記錄');

dbms_output.put_line('其他未處理異常');

12-8-1

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

12-8-2

declear

type emp_table is table of varchar2(10)

index by binary_integer;

emplist emp_table;

cursor empcursor

select ename from emp;

if not empcursor%ISOPEN

open empcursor;

fetch empcursor

bulk collect into emplist;

for i in 1 .. emplist.count

dbms_output.put_line('員工名稱：' || emplist(i));

close empcursor;

12-8-3

v_sqlstr varchar2(200);

v_id int;

v_name varchar(100);

v_sqlstr := 'drop table temptable';

execute immediate v_sqlstr;

v_sqlstr := 'create table temptable(id int not null primary key,tmpname varchar2(100))';

v_sqlstr := 'insert into temptable values(10,''臨時名稱1'')';

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

12-8-4 %ROWTYPE

v_emp emp%ROWTYPE;

select * into v_emp from emp where empno = &empno;

dbms_output.put_line(v_emp.empno  || '

'|| v_emp.ename);

12-8-5

cursor emp_cursor

select empno,ename,job,sal,hiredate from emp;

v_emp emp_cursor%ROWTYPE;

open emp_cursor;

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

close emp_cursor;

      本文轉自潘闊 51CTO部落格，原文連結：http://blog.51cto.com/pankuo/1630244，如需轉載請自行聯系原作者