oracle 数据库入门pl/sql 基本操作

sql 语句的基本操作

1.create

CREATE TABLE tablename(
    id number not null primary key,
    name varchar(20),
    create_time date,
    total_consumption number(4,2)
    );

2.insert

INSERT INTO tablename VALUES(222,'shuifenglin','01-DEC-95',4756.50);

3.update

UPDATE tablename
SET total_consumption=8888.56
WHERE id=222;

4.delete

DELETE FROM tablename
WHERE id=222;

5.search

SELECT DISTINCT name FROM tablename
WHERE  name LIKE '%lin%';  // '%lin'查找以lin结尾，'lin%'查找以lin开头

select查询语句以后单独写

---

pl/sql 基本操作：

1.定义函数

* 大写为语法部分，小写是自己定义的部分 *

CREATE OR REPLACE PROCEDURE factorial (num in number)
return number
AS
BEGIN
    IF num=0
    THEN return 1;
    ELSE return num*factorial(num-1);
    END IF;
END;

2.cursor的用法

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。我们常用到的SELECT…INTO…查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

---

隐式游标 :implicit cursor

显式游标主要是用于对查询语句的处理，尤其是在查询结果为多条记录的情况下；

而对于非查询语句，如修改、删除操作，则一般使用隐式游标。隐式游标由Oracle 系统自动地为这些操作设置游标并创建其工作区，隐式游标的名字为SQL，这是由ORACLE 系统定义的。

对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。

格式调用为： SQL%
DECLARE  
   v_rows NUMBER;  
BEGIN  
   --更新数据  
   UPDATE employees SET salary = 30000  
     WHERE department_id = 90 AND job_id = 'AD_VP';  
   --获取默认游标的属性值  
   v_rows := SQL%ROWCOUNT;  
   DBMS_OUTPUT.PUT_LINE('更新了'||v_rows||'个雇员的工资');  
  ```

  ```   
    --删除指定雇员；如果部门中没有雇员，则删除部门  
    DELETE FROM employees WHERE department_id=v_deptno;  
    IF SQL%NOTFOUND THEN  
        DELETE FROM departments WHERE department_id=v_deptno;  
    END IF;  
END;  

---

显式游标: explicit cursor

显式是相对与隐式cursor而言的，就是有一个明确的声明的cursor。显式游标的声明类似如下：

cursor cursor_name (parameter list) is select ...

游标从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，它的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用它。

1.只取一行：

DECLARE
    id EMPLOYEES.id%type;
    name EMPLOYEES.name%type;
    cursor cur is select *from EMPLOYEES;
BEGIN
    open cur;
    fetch cur  into id,name;
    close cur;
    dbms_output.put_line(id||name);
END;

2.手动LOOP循环取多行

DECLARE
    id number(4)
    name varchar(20)
    cursor cur is select *from EMPLOYEES;
BEGIN
    open cur;
    LOOP
        fetch cur  into id,name;
        EXIT WHEN cur%NOTFOUND;
        dbms_output.put_line(id||name);
    END LOOP;   
    close cur;
END;

3.游标for循环取多行

PL/SQL语言提供了游标FOR循环语句，自动执行游标的OPEN、FETCH、CLOSE语句和循环语句的功能；

当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据；
当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理；
当提取完结果集合中的所有数据行后结束循环，并自动关闭游标。
格式：

FOR index_variable IN cursor_name[(value[, value]…)] LOOP
– 游标数据处理代码
END LOOP;

其中：

index_variable为游标FOR 循环语句隐含声明的索引变量，该变量为记录变量，其结构与游标查询语句返回的结构集合的结构相同。在程序中可以通过引用该索引记录变量元素来读取所提取的游标数据，index_variable中各元素的名称与游标查询语句选择列表中所制定的列名相同。如果在游标查询语句的选择列表中存在计算列，则必须为这些计算列指定别名后才能通过游标FOR 循环语句中的索引变量来访问这些列数据。

DECLARE
            cursor cur is select *from EMPLOYEES;
        BEGIN
            FOR counter IN cur
            LOOP
                dbms_output.put_line(counter.id||counter.name);
            END LOOP;   
        END;

4.带参数的游标for循环：

例：

DECLARE  
  CURSOR c_cursor(dept_no NUMBER DEFAULT 10)   
  IS  
    SELECT department_name, location_id FROM departments WHERE department_id <= dept_no;  
BEGIN  
    --当dept_no参数值为30  
    FOR c1_rec IN c_cursor(30) LOOP          
         DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);  
    END LOOP;  

 --使用默认的dept_no参数值10  
FOR c1_rec IN c_cursor LOOP         
     DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);  
END LOOP;  
END;  

或者可以在游标FOR循环语句中使用子查询

BEGIN  
        FOR c1_rec IN(SELECT department_name, location_id FROM departments) LOOP   
           DBMS_OUTPUT.PUT_LINE(c1_rec.department_name||'---'||c1_rec.location_id);  
        END LOOP;  
    END;