如果我想使用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>

找到问题修改在执行。