oracle PL/SQL示例

如果我想使用PL/SQL得到下面sql查詢的結果

SQL> conn scott/tiger

Connected.

SQL> select ename from emp where empno=7839;

ENAME

----------

KING

SQL>

 PL/SQL

SQL> declare

  2  vename varchar2(15);

  3  begin

  4  select ename into vename from emp where empno=7839;

  5  dbms_output.put_line(vename);

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL>

需要開啟serveroutput

SQL>save /tmp/p1

Created file /tmp/p1.sql

SQL> set serveroutput on

SQL> start /tmp/p1.sql

KING

PL/SQL procedure successfully completed.

SQL>

嘗試使用指令碼引數

[oracle@yang ~]$ vi /tmp/p1.sql

set serveroutput on

declare

vename varchar2(15);

vempno number:=&1;

begin

select ename into vename from emp where empno=vempno;

dbms_output.put_line(vename);

end;

/

SQL> start /tmp/p1.sql

Enter value for 1: 7839  （這裡輸出想要查詢的empno的值）

old   3: vempno number:=&1;

new   3: vempno number:=7839;

KING

PL/SQL procedure successfully completed.

SQL>

可以隱藏顯示的新舊值

SQL> set verify off

SQL> start /tmp/p1.sql

Enter value for 1: 7839

KING

PL/SQL procedure successfully completed.

SQL>

還可以直接執行指令碼後加引數

SQL> start /tmp/p1.sql  7839

KING

PL/SQL procedure successfully completed.

SQL>

使用動態SQL實現

SQL> declare

  2  vename varchar2(15);

  3  begin

  4  execute immediate 'select ename from emp where empno=7839' into vename;

  5  dbms_output.put_line(vename);

  6  end;

  7  /

KING

PL/SQL procedure successfully completed.

SQL>

或者

[oracle@yang ~]$ vi /tmp/q2.sql

declare

vename varchar2(15);

begin

execute immediate 'select ename from emp where empno=:1' into vename using 7839;

dbms_output.put_line(vename);

end;

/

當然也可以使用指令碼引數

[oracle@yang ~]$ vi /tmp/q2.sql

declare

vename varchar2(15);

vempno number:=&1;

begin

execute immediate 'select ename from emp where empno=:1' into vename using vempno;

dbms_output.put_line(vename);

end;

/

SQL> select ename,sal from emp where empno=7839;

ENAME             SAL

---------- ----------

KING             5000

SQL>

修改指令碼

[oracle@yang ~]$ vi /tmp/p1.sql

set serveroutput on

declare

vename varchar2(15);

vsal   number;

vempno number:=&1;

begin

select ename,sal into vename,vsal from emp where empno=vempno;

dbms_output.put_line(vename||'  '||vsal);

end;

/

執行指令碼

SQL> start /tmp/p1.sql 7839

KING  5000

PL/SQL procedure successfully completed.

SQL>

修改動態sql指令碼

[oracle@yang ~]$ vi /tmp/q2.sql

declare

vename varchar2(15);

vempno number:=&1;

vsal number;

begin

execute immediate 'select ename,sal from emp where empno=:1' into vename,vsal using vempno;

dbms_output.put_line(vename||’  ‘||vsal);

end;

/

執行指令碼

SQL> start /tmp/q2.sql  7839

KING  5000

PL/SQL procedure successfully completed.

SQL>

SQL> declare

  2  type r1 is record(vename varchar2(20),vsal number);

  3  v_r r1;

  4  begin

  5  select ename,sal into v_r from emp where empno=&1;

  6  dbms_output.put_line(v_r.vename||'  '||v_r.vsal);

  7  end;

  8  /

KING  5000

PL/SQL procedure successfully completed.

SQL>

SQL> save /tmp/q3

Created file /tmp/q3.sql

SQL> start /tmp/q3.sql 7839

KING  5000

PL/SQL procedure successfully completed.

SQL>

[oracle@yang ~]$ vi /tmp/p1.sql

set serveroutput on

declare

vename emp.ename%type;

vsal   emp.sal%type;

vempno number:=&1;

begin

select ename,sal into vename,vsal from emp where empno=vempno;

dbms_output.put_line(vename||'  '||vsal);

end;

/

SQL> start /tmp/p1.sql 7839

KING  5000

PL/SQL procedure successfully completed.

SQL>

[oracle@yang ~]$ vi /tmp/p4.sql

set serveroutput on

declare

vemp emp%rowtype;

vempno number:=&1;

begin

select * into vemp from emp where empno=vempno;

dbms_output.put_line(vemp.ename||'  '||vemp.sal);

end;

/

SQL> start /tmp/p4.sql 7839

KING  5000

PL/SQL procedure successfully completed.

SQL>

SQL> select ename,sal from emp where deptno=10;

ENAME             SAL

---------- ----------

CLARK            2450

KING             5000

MILLER           1300

SQL>

使用for迴圈

[oracle@yang ~]$ vi /tmp/p5.sql

begin

for i in (select ename,sal from emp where deptno=10) loop

dbms_output.put_line(i.ename||'  '||i.sal);    --修改為(rpad(i.ename,15,’  ‘)||'  '||i.sal)可以使輸出的欄位對齊）

end loop;

end;

/

SQL> start /tmp/p5.sql

CLARK  2450

KING  5000

MILLER  1300

PL/SQL procedure successfully completed.

SQL>

SQL> select  ename,sal,decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*1.3,sal) from emp;

ENAME             SAL DECODE(DEPTNO,10,SAL*1.1,20,SAL*1.2,30,SAL*1.3,SAL)

---------- ---------- ---------------------------------------------------

SMITH             800                                                 960

ALLEN            1600                                                2080

WARD             1250                                                1625

JONES            2975                                                3570

MARTIN           1250                                                1625

BLAKE            2850                                                3705

CLARK            2450                                                2695

SCOTT            3000                                                3600

KING             5000                                                5500

TURNER           1500                                                1950

ADAMS            1100                                                1320

ENAME             SAL DECODE(DEPTNO,10,SAL*1.1,20,SAL*1.2,30,SAL*1.3,SAL)

---------- ---------- ---------------------------------------------------

JAMES             950                                                1235

FORD             3000                                                3600

MILLER           1300                                                1430

14 rows selected.

SQL>

[oracle@yang ~]$ vi /tmp/p6.sql

declare

vdeptno number;

begin

vdeptno:=&1;

if vdeptno=10 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal*1.1);

end loop;

elsif vdeptno=20 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal*1.2);

end loop;

elsif vdeptno=30 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal*1.3);

end loop;

else

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal);

end loop;

end if;

/

SQL> start /tmp/p6.sql 10

CLARK            2695

KING             5500

MILLER           1430

PL/SQL procedure successfully completed.

SQL>

可以將sal列原值一起輸出做比較

[oracle@yang ~]$ vi /tmp/p6.sql

declare

vdeptno number;

begin

vdeptno:=&1;

if vdeptno=10 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal||'  '||a.sal*1.1);

end loop;

elsif vdeptno=20 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal||'  '||a.sal*1.2);

end loop;

elsif vdeptno=30 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal||'  '||a.sal*1.3);

end loop;

else

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal||'  '||a.sal);

end loop;

end if;

/

SQL> start /tmp/p6.sql 10

CLARK            2450  2695

KING             5000  5500

MILLER           1300  1430

PL/SQL procedure successfully completed.

SQL>

SQL> start /tmp/p6.sql 20

SMITH            800  960

JONES            2975  3570

SCOTT            3000  3600

ADAMS            1100  1320

FORD             3000  3600

PL/SQL procedure successfully completed.

SQL> start /tmp/p6.sql 30

ALLEN            1600  2080

WARD             1250  1625

MARTIN           1250  1625

BLAKE            2850  3705

TURNER           1500  1950

JAMES            950  1235

PL/SQL procedure successfully completed.

SQL>

將if迴圈轉換為when迴圈

[oracle@yang ~]$ vi /tmp/p6.sql

declare

vdeptno number;

begin

vdeptno:=&1;

case vdeptno when 10 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal||'  '||a.sal*1.1);

end loop;

when 20 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal||'  '||a.sal*1.2);

end loop;

when 30 then

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal||'  '||a.sal*1.3);

end loop;

else

for a in (select ename,sal from emp where deptno=vdeptno) loop

dbms_output.put_line(rpad(a.ename,15,'  ')||'  '||a.sal||'  '||a.sal);

end loop;

end case;

end;

/

SQL> start /tmp/p6.sql 10

CLARK            2450  2695

KING             5000  5500

MILLER           1300  1430

PL/SQL procedure successfully completed.

SQL>

SQL> create or replace function getsal(vempno number)

  2  return number

  3  is

  4  vsal number;

  5  begin

  6  select sal into vsal from emp where empno=vempno;

  7  return vsal;

  8  end getsal;

  9  /

Function created.

SQL>

SQL> select getsal(7839) from dual;

GETSAL(7839)

------------

        5000

SQL>

SQL> select sal from emp where empno=7839;

       SAL

----------

      5000

SQL>

如果想得到的結果為兩列那麼

SQL> create or replace function getsal(vempno number)

  2  return varchar2

  3  is

  4  vname varchar2(20);

  5  vsal number;

  6  begin

  7  select  ename,sal into vename,vsal from emp where empno=vempno;

  8  return (rpad(vname,15,’  ‘)||’  ‘||to_char(vsal));

  9  end getsal;

 10  /

SQL>

[oracle@yang ~]$ vi /tmp/p9.sql

 create or replace function getsal(vempno number)

return varchar2

is

vename varchar2(20);

vsal number;

begin

select  ename,sal into vename,vsal from emp where empno=vempno;

return (rpad(vename,15,'  ')||'  '||to_char(vsal));

end getsal;

/

SQL> select getsal(7839) from dual;

GETSAL(7839)

--------------------------------------------------------------------------------

KING             5000

SQL>

SQL> create or replace procedure gsal(vempno in number)

  2  is

  3  vename varchar2(15);

  4  vsal number;

  5  begin

  6  select ename,sal into vename,vsal from emp where empno=vempno;

  7  dbms_output.put_line(vename||'  '||vsal);

  8  end gsal;

  9  /

Procedure created.

SQL>

SQL> set serveroutput on;

SQL> execute gsal(7839);

KING  5000

PL/SQL procedure successfully completed.

SQL>

[oracle@yang ~]$ vi /tmp/p10.sql

create or replace procedure gsal(vempno in number)

is

vename varchar2(15);

vsal number;

begin

update emp set sal=sal+500 where empno=vempno;

commit;

select ename,sal into vename,vsal from emp where empno=vempno;

dbms_output.put_line(vename||'    '||vsal);

end gsal;

/

SQL> execute gsal(7839);

KING    5500

PL/SQL procedure successfully completed.

SQL>

[oracle@yang ~]$ vi /tmp/p11.sql

create or replace procedure phone(vno in out varchar2)

is

begin

vno:=' ('||substr(vno,1,length(vno)-8)||')-'||substr(vno,-8);

end phone;

/

[oracle@yang ~]$ vi /tmp/p12.sql

declare

vno varchar2(20):='&1';

begin

phone(vno);

dbms_output.put_line(vno);

end;

/

~

SQL> start /tmp/p12.sql 02812345678

(028)-12345678

PL/SQL procedure successfully completed.

SQL>

SQL> start /tmp/p12.sql 028123456789

(0281)-23456789

PL/SQL procedure successfully completed.

SQL>

建立包頭

SQL> create or replace package p1 is

  2  function gsal(vempno number) return varchar2;

  3  procedure usal(vempno number);

  4  end p1;

  5  /

Package created.

SQL>

建立包體

SQL> create or replace package body p1

  2  is

  3  function gsal(vempno number)

  4  return varchar2 is

  5  vename varchar2(20);

  6  vsal number;

  7  begin

  8  select ename,sal into vename,vsal from emp where empno=vempno;

  9  return (rpad(vename,15,'  ')||'  '||to_char(vsal));

 10  end gsal;

 11  procedure usal(vempno in number)

 12  is

 13  begin

 14  update emp set sal=sal+500 where empno=vempno;

 15  commit;

 16  end usal;

 17  end p1;

 18  /

Package body created.

SQL>

驗證

SQL> select p1.gsal(7566) from dual;

P1.GSAL(7566)

--------------------------------------------------------------------------------

JONES            3475

SQL> exec p1.usal(7566);

PL/SQL procedure successfully completed.

SQL> select p1.gsal(7566) from dual;

P1.GSAL(7566)

--------------------------------------------------------------------------------

JONES            3975

SQL>

建立記錄表logt

SQL> create table logt(uname varchar2(15),utime date);

Table created.

SQL>

登陸scott使用者

SQL> conn scott/tiger

Connected.

SQL> conn scott/tiger

Connected.

查詢表

SQL> select uname,to_char(utime,'yyyy-mm-dd hh24:mi:ss') utime from logt;

UNAME           UTIME

--------------- -------------------

SCOTT           2014-05-04 08:19:11

SCOTT           2014-05-04 08:21:34

SQL>

建立記錄表

SQL> create table empsal  as  select sal osal,sal nsal from emp where 0=1;

Table created.

SQL>

建立觸發器

SQL> create or replace trigger emp_trig

  2  before insert or update or delete of sal on emp for each row

  3  begin

  4  insert into empsal values(:old.sal,:new.sal);

  5  end emp_trig;

  6  /

Trigger created.

SQL>

驗證

SQL> update emp set sal=sal+1 where empno=7566;

1 row updated.

SQL> select * from empsal;

      OSAL       NSAL

---------- ----------

      3975       3976

SQL> insert into emp(empno,ename,sal) values(3588,'A11',2000);

1 row created.

SQL> select * from empsal;

      OSAL       NSAL

---------- ----------

      3975       3976

                 2000

SQL> delete emp where empno=3588;

1 row deleted.

SQL> select * from empsal;

      OSAL       NSAL

---------- ----------

      3975       3976

                 2000

      2000

SQL>

更新檢視不成功

SQL> create view v1 as  select deptno,min(sal) msal from emp group by deptno;

View created.

SQL> select * from v1;

    DEPTNO       MSAL

---------- ----------

        30        950

        20        800

        10       1300

SQL> update v1 set msal=msal+1 where masl=950;

update v1 set msal=msal+1 where masl=950

                                *

ERROR at line 1:

ORA-00904: "MASL": invalid identifier

SQL>

建立觸發器

SQL> create or replace trigger v1_trig

  2  instead of insert or update or delete on v1

  3  begin

  4  update emp set sal=:old.msal+1 where sal=:old.msal;

  5  end;

  6  /

Trigger created.

SQL>

更新檢視v1

SQL> select * from v1;

    DEPTNO       MSAL

---------- ----------

        30        950

        20        800

        10       1300

SQL> update v1 set msal=msal+1 where msal=950;

1 row updated.

SQL> select * from v1;

    DEPTNO       MSAL

---------- ----------

        30        951

        20        800

        10       1300

SQL>

SQL> select object_name,object_type,status from user_objects where object_type in ('FUNCTION','PROCEDURE','TRIGGER','PACKAGE BODY') and status <>'VALID';

no rows selected

SQL>

模擬錯誤

[oracle@yang ~]$ vi /tmp/p9.sql

 create or replace function getsal(vempno number)

return varchar2

is

vename varchar2(20);

vsal number;

begin

select  ename,sal into vename,vsala from emp where empno=vempno;

return (rpad(vename,15,'  ')||'  '||to_char(vsal));

end getsal;

/

SQL> start /tmp/p9.sql

Warning: Function created with compilation errors.

SQL>

再次查詢

SQL> select object_name,object_type,status from user_objects where object_type in ('FUNCTION','PROCEDURE','TRIGGER','PACKAGE BODY') and status <>'VALID';

OBJECT_NAME

--------------------------------------------------------------------------------

OBJECT_TYPE         STATUS

------------------- -------

GETSAL

FUNCTION            INVALID

SQL>

對於有問題的函式編譯檢視錯誤

SQL> alter function getsal compile;

Warning: Function altered with compilation errors.

SQL> show error

Errors for FUNCTION GETSAL:

LINE/COL ERROR

-------- -----------------------------------------------------------------

7/1      PL/SQL: SQL Statement ignored

7/31     PLS-00201: identifier 'VSALA' must be declared

7/37     PL/SQL: ORA-00904: : invalid identifier

SQL>

SQL> select text from user_source where name='GETSAL'; （有時原始碼會加密，導致無法看懂，只能找開發人員解決）

TEXT

--------------------------------------------------------------------------------

function getsal(vempno number)

return varchar2

is

vename varchar2(20);

vsal number;

begin

select  ename,sal into vename,vsala from emp where empno=vempno;

return (rpad(vename,15,'  ')||'  '||to_char(vsal));

end getsal;

9 rows selected.

SQL>

找到問題修改在執行。