oracle plsql语句、游标

plsql语句

SELECT * FROM jobs
--求jobs表中总裁最低工资的编号，如果对应记录太多抛出相应异常，没有记录则抛出没有记录的异常
DECLARE
v_result VARCHAR(20);
BEGIN
   SELECT job_id INTO v_result FROM jobs WHERE min_salary>2000;
   dbms_output.put_line('总裁最低工资编号:'||v_result);
   EXCEPTION
     WHEN no_data_found THEN
       dbms_output.put_line('没有对应的记录');
       WHEN too_many_rows THEN
       dbms_output.put_line('对应记录太多!');        
END;

/

--定义记录类型的变量
DECLARE

`TYPE product_rec IS RECORD
(
       v_productid productinfo.productid%TYPE,--代表v_productid的类型和productinfo表中productid的类型一样
       v_product_name VARCHAR2(20),
       v_product_type VARCHAR(20)
)

v_product product_rec;

BEGIN
           SELECT productid,productname,producttype INTO v_product FROM productinfo;
           dbms_output.put_line(v_product.v_productid);   

END;
/
--%rowtype,将变量的类型设置为表类型
DECLARE

v_result productinfo%ROWTYPE;

BEGIN
SELECT * INTO v_result FROM productinfo;
END;
/


--if  elseif  end if结构,也可以嵌套使用,case语句



--loop结构
DECLARE

v_num NUMBER(8) :=1;

BEGIN
<<basic_LOOP>>
LOOP
         v_num := v_num+1;
         IF
             v_num>10 THEN
             dbms_output.put_line('当前v_num的值是:'||v_num);
             EXIT basic_LOOP;
           
         END IF; 
END LOOP;

END;
/

--while loop


DECLARE

v_num NUMBER(8) :=1;

BEGIN
<<basic_LOOP>>
WHILE v_num<20
LOOP
         v_num := v_num+1;
         IF
             MOD(v_num,4)=0 THEN
             dbms_output.put_line('当前v_num的值是:'||v_num);
             EXIT basic_LOOP;
           
         END IF; 
END LOOP;

END;
/
--使用for  loop计算0-20之间的整数和
DECLARE
         v_num NUMBER(8):=1;
BEGIN
  <<for_loop>>
  FOR inx IN 1..20 LOOP
      v_num := v_num+inx;  
  END LOOP;
dbms_output.put_line('当前v_num的值是:'||v_num);
END;
/
--自定义异常
DECLARE
salary jobs.min_salary%TYPE;
msg_exc EXCEPTION;
PRAGMA Exception_Init(msg_exc,-20001);
BEGIN
SELECT  min_salary INTO salary FROM jobs WHERE job_id='AD_PRES';
IF salary>10000 THEN
   RAISE msg_exc;
END IF;
dbms_output.put_line('工资是:'||salary);
EXCEPTION
   WHEN msg_exc THEN
dbms_output.put_line('工资不符合标准'||'error code:'||SQLCODE);
WHEN no_data_found THEN
    dbms_output.put_line('没有对应的数据');
END;
/

游标

--loop
DECLARE
  CURSOR JOB_LOOP IS
    SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY FROM JOBS;

  CUR_JOBID     JOBS.JOB_ID%TYPE;
  CUR_JOBTITLE  JOBS.JOB_TITLE%TYPE;
  CUR_MINSALARY JOBS.MIN_SALARY%TYPE;
  CUR_MAXSALARY JOBS.MAX_SALARY%TYPE;

BEGIN

  OPEN JOB_LOOP;
  LOOP
    FETCH JOB_LOOP
      INTO CUR_JOBID, CUR_JOBTITLE, CUR_MINSALARY, CUR_MAXSALARY;
    EXIT WHEN JOB_LOOP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('工作id:' || CUR_JOBID || ',    职称:' ||
                         CUR_JOBTITLE || ',   最低工资:' || CUR_MINSALARY ||
                         ',   最高工资:' || CUR_MAXSALARY);
  END LOOP;
  CLOSE JOB_LOOP;

END;
/
--bult collect  for实现同样的效果，rowtype对应一行记录所有的列，会跟所有的列类型一样

DECLARE CURSOR JOB_LOOP IS
  SELECT JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY FROM JOBS;

TYPE JOB_TAB IS TABLE OF JOBS%ROWTYPE;
JOB JOB_TAB;

BEGIN

  OPEN JOB_LOOP; --也可以做增删改查操作
  LOOP
    FETCH JOB_LOOP BULK COLLECT
      INTO JOB;
    FOR I IN 1 .. JOB.COUNT LOOP
   
      DBMS_OUTPUT.PUT_LINE('工作id:' || JOB(I).JOB_ID || ',    职称:' || JOB(I)
                           .JOB_TITLE || ',   最低工资:' || JOB(I).MIN_SALARY ||
                           ',   最高工资:' || JOB(I).MAX_SALARY);
    END LOOP;
    EXIT WHEN JOB_LOOP%NOTFOUND;
  END LOOP;
  CLOSE JOB_LOOP;

END;
/
--还有带参数的游标