The sixth day of oracle learning: pl/sql01

The sixth day of oracle learning: pl/sql01

pl/sql

  • What is PL/SQL
    PL/SQL is Oracle's procedural extension to the sql language. It refers to the addition of procedural processing statements (such as branches, loops, etc.) to the sql command language to enable the SQL language to have procedural processing capabilities. Combining the data manipulation ability of SQL language with the data processing ability of procedural language makes PL/SQL process-oriented but simpler, more efficient, flexible and practical than procedural language.
Insert picture description here

Program grammar

declare
    说明部分 (变量说明,游标申明,例外说明)
begin
    语句序列  (DML语句)
exception
    例外处理语句
end;
  • For example, enter hello world
declare
    begin
      DBMS_OUTPUT.PUT_LINE('hello world');
end;

Oracle Advanced PLSQL Variables

Variables and constants can be defined in the declaration phase of the program

variable

The basic type of variables is the field variables such as char, varchar2, date, number, boolean, long when building a table in Oracle
  • Definition syntax:
    varl char(15); Psal number(9,2) After
    specifying the variable name, data type and length, end the statement with a semicolon.

Add a semicolon after each statement

declare 
 pname varchar2(10);
begin
 pname:='zhangsan';
 dbms_output.put_line(pname);
end;

constant

Constant definition: married boolean:=true;
declare 
 page number(3):=52;
begin
 dbms_output.put_line(page);
end;

Reference variable

Myname emp.ename%type;
reference variable, that is, the type of Myname is the same as the type of the ename column in the emp table. Use into to assign values ​​in sql
declare
    myname emp.ename%type;
begin
    select t.ENAME into myname from emp t where t.EMPNO=7369;
    DBMS_OUTPUT.PUT_LINE(myname);
end;

Record variable

Equivalent to an object in Java
declare
    p EMP%rowtype;
begin
    select * into p from EMP t where t.EMPNO=7369;
    DBMS_OUTPUT.PUT_LINE(p.ENAME||''||p.EMPNO);
end;