1.if循环做判断
SET SERVEROUTPUT ON
accept num prompt 'qinshuu';
DECLARE
pnum NUMBER :=& num ;
BEGIN
IF pnum = 0 THEN
SYS.DBMS_OUTPUT.PUT_LINE ('') ;
ELSIF pnum = 1 THEN
dbms_output.put_line ('') ;
ELSE
DBMS_OUTPUT.PUT_LINE ('') ;
END
IF ;
END ;
/
2.引用变量
SET SERVEROUTPUT ON
DECLARE
pename emp.ename%type;
psal emp.sal%type;
BEGIN
SELECT ename,sal into pename,psal FROM emp where empno= 7902;
dbms_output.put_line(pename ||'hahah'||psal);
end;
/
3.基本变量 char类型会补全
set SERVEROUTPUT ON
DECLARE
var_char1 char;
var_char2 CHAR(20);
BEGIN
var_char1 :='a';
var_char2 :='abc';
DBMS_OUTPUT.PUT_LINE('var_char1 is' ||var_char1||'*');
DBMS_OUTPUT.PUT_LINE('var_char2 is' ||var_char2||'*');
end;
/
/*
PL/SQL 过程已成功完成。
var_char1 isa*
var_char2 isabc *
*/
4.while循环10次
SET SERVEROUTPUT ON
DECLARE pnum NUMBER := 1 ;
BEGIN
while pnum <= 10
loop dbms_output.put_line (pnum) ;
pnum := pnum + 1 ;
END loop ;
END ;
/
5.loop循环
SET SERVEROUTPUT ON
DECLARE pnum NUMBER := 1 ;
BEGIN
loop exit
WHEN pnum > 10 ;
dbms_output.put_line (pnum) ;
pnum := pnum + 1 ;
END loop ;
END ;
/
6.for循环
SET SERVEROUTPUT ON
DECLARE pnum NUMBER := 1 ;
BEGIN
FOR pnum IN 1..10
loop DBMS_OUTPUT.PUT_LINE (pnum) ;
END loop ;
END ;
/
7.游标打印所有人薪水
SET SERVEROUTPUT ON
DECLARE CURSOR cemp IS SELECT
ENAME,
sal
FROM
EMP ;
pename emp.ename % TYPE ;
psal emp.sal % TYPE ;
BEGIN
OPEN cemp ;
loop FETCH cemp INTO pename,
psal ; exit
WHEN cemp % notfound ;
dbms_output.put_line (pename || 'xin shui shi' || psal) ;
END loop ; CLOSE cemp ;
END ;
/
8.游标所有人涨薪水
set SERVEROUTPUT ON
DECLARE
CURSOR cemp is select empno,job FROM emp;
pempno EMP.EMPNO%type;
pjob emp.job%type;
begin
open cemp;
loop
fetch cemp into pempno,pjob;
exit when cemp%notfound;
if pjob = 'PRESIDENT' then update emp set sal = sal+1000 where empno = pempno;
elsif pjob='MANAGER' then update emp set sal = sal+800 where empno = pempno;
else update emp set sal = sal+400 where empno = pempno;
end if;
end loop;
close cemp;
commit;
dbms_output.put_line('wanc');
end;
/
9.游标自定义选项
SET SERVEROUTPUT ON
DECLARE CURSOR cemp (dno NUMBER) IS SELECT
ename
FROM
emp
WHERE
deptno = dno ; pename emp.ename % TYPE ;
BEGIN
OPEN cemp (20) ; loop FETCH cemp INTO pename ; exit
WHEN cemp % notfound ; dbms_output.put_line (pename) ;
END loop ; CLOSE cemp ;
END ;
/
备注：实验环境为orcle SQL Developer;
sys登录为系统自带scott解锁并且授予SCOTT用户开发权限
ALTER USER scott IDENTIFIED BY xxxxxx ACCOUNT UNLOCK;
GRANT RESOURCE TO scott;
