pl/sql 工具或者用toad for oracle 工具下载地址:http://www.skycn.com/soft/11662.html
你们说啥呢,我就是在plsql里需要写一句话啊
-- 再说:你这样的逻辑可以这样去理解: -- 用select查找一条记录,如果没找到就执行一个语句-- "如果没找到就执行一个语句"放在前面 where (select查找一条记录)=1;
-- 下面的示例:如果dept表中不存在deptno=50的数据行,就返回emp表的所有数据!scott@SZTYORA> select * from emp 2 where (select count(*) from dept where deptno=50)=0;未选定行-- 按照你的思想:用select查找一条记录(select count(*) from dept where deptno=50), -- 如果没找到就执行一个语句: select * from emp
scott@SZTYORA> select * from emp 2 where (select count(*) from dept where deptno=50)=0; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ---------- 111 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 8663 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 8663 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 0087-07-13 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 0087-07-13 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 5201 10已选择14行。scott@SZTYORA> select * from dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
--逻辑判断: declare v_ename varchar2(10):='CSDNER'; v_flag number(2):=0; begin select 1 into v_flag from dual where exists( select * from emp where ename=v_ename); if v_flag=1 then dbms_output.put_line('CSDNER存在于emp表中'); else for v_emp in (select ename,job,sal from emp) loop dbms_output.put_line(v_emp.ename||' '||v_emp.job||' '||v_emp.sal); end loop; end if; end;
-- 用select查找一条记录,如果没找到就执行一个语句-- "如果没找到就执行一个语句"放在前面 where (select查找一条记录)=1;
2 where (select count(*) from dept where deptno=50)=0;未选定行-- 按照你的思想:用select查找一条记录(select count(*) from dept where deptno=50),
-- 如果没找到就执行一个语句: select * from emp
2 where (select count(*) from dept where deptno=50)=0; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
111 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 8663 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 8663 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 0087-07-13 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 0087-07-13 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 5201 10已选择14行。scott@SZTYORA> select * from dept; DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--逻辑判断:
declare
v_ename varchar2(10):='CSDNER';
v_flag number(2):=0;
begin
select 1 into v_flag
from dual
where exists(
select * from emp
where ename=v_ename);
if v_flag=1 then
dbms_output.put_line('CSDNER存在于emp表中');
else
for v_emp in (select ename,job,sal from emp) loop
dbms_output.put_line(v_emp.ename||' '||v_emp.job||' '||v_emp.sal);
end loop;
end if;
end;