Oracle practices-Basic PL/SQL code blocks

PL/SQL basic getting started code block

PL/SQL:A programming Language combined with a Structured Language (Structured Query Language) is an extension of SQL and supports multiple data types, such as large objects and Collection types, you can use conditions, loops, and other control statements to create stored procedures, packages, and triggers, and add program logic to SQL statement execution, which is closely integrated with Oracle servers and Oracle tools, it is portable, flexible, and secure.

Advantages:

1. Supports SQL. You can use DML, DCL, cursor control, and SQL operators.

2. Support for Object-Oriented (OOP ).

3. tightly integrated with SQL to simplify data processing, support for all data types, support for NULL, support for % type and % rowtype

4. Users can access data through stored procedures to improve security.

-- Variable Declaration: common: varchar2, number, % rowtype, % type

Select * from emp; declare v_name varchar2 (20): = '& name:'; -- varchar2 type declaration v_ename emp. ename % type; -- consistent with the type of the forehead ename field in the emp table v_emp_rec emp % rowtype; -- consistent with the data type of the row of emp v_age number; -- number type begin v_age: = '& age:'; select ename into v_ename from emp where empno = 7369; select * into v_emp_rec from emp where empno = 7369; dbms_output.put_line (v_ename); values (v_emp_rec.job ); end;

-- Condition Control (If) Keywords : If then elsif else end if;

Declare v_age number: = '& enter age:'; -- Note: The variable type must be specified when the variable is declared. '& enter the age' and cannot end with an English ': 'In in if v_age <18 then dbms_output.put_line (v_age | 'years old, teenagers! '); --' | 'Is the string connector, equivalent to' + 'elsif v_age <35 then in java -- note that it is elsif, not elseif dbms_output.put_line (v_age |' years old, nobody! '); Else dbms_output.put_line (v_age |' years old, expired! '); End if; -- note that the end if cannot be forgotten;

-- Condition Control (Case) , Keyword : Case When then else end case;

Declare v_grade varchar2 (2); begin v_grade: = '& enter the level:'; case v_grade when 'A' then dbms_output.put_line ('excellent '); -- Syntax: when... then ...; when 'B' then dbms_output.put_line ('good'); -- when... then ...; when 'C' then dbms_output.put_line ('General '); -- when... then ...; else dbms_output.put_line ('poorer '); -- else is equivalent to the default end case in java; -- pay attention to the end, end caseend;

-- Loop Control Loop , Keyword: Loop Exit when end loop

Declare v_times number; v_currentTime number: = 1; begin v_times: = '& enter the number of times helloworld is printed:'; loop dbms_output.put_line ('helloworld! '); V_currentTime: = v_currentTime + 1; -- in java, ++ = cannot use exit when v_currentTime> v_times; end loop; end;

-- Loop Control While , Keyword : While Loop end loop

declare v_count number :=1;begin while v_count <=7    loop      dbms_output.put_line(v_count);      v_count := v_count +1;    end loop; end;

--Loop ControlFor, Keyword:ForIn reverse loop end loop

Declarebegin for num in 1 .. 5 loop dbms_output.put (num); -- if you don't want to wrap the line, use put, but the console will not display the output result end loop; dbms_output.new_line (); -- display the output result for num in reverse 1 after wrapping .. 5 loop dbms_output.put (num); end loop; dbms_output.New_Line (); -- it is written in uppercase, and it seems that the end is case insensitive in Oracle;

-- Recurrence exercise multiplication table

Declarebegin for I in 1 .. 9 loop -- each for loop must have a loop end loop for j in 1 .. I loop dbms_output.put (I | '*' | j | '=' | I * j | '); end loop; dbms_output.new_line; -- Note, the brackets are optional, but the standard writing method is not like this: end loop; end;

--Sequence Control, Keyword:Goto null

Declare v_age number: = '& input age:'; begin if v_age <18 then goto young; -- Skip to <young>, run elsif v_age in sequence <30 then goto strong; -- Skip to <strong>, and then run else goto older in sequence; -- Skip to <older>, end if; <young> null; <strong> dbms_output.put_line ('strong '); <older> dbms_output.put_line ('older'); end;

-- Dynamic execution SQL Statement, Keyword: Execute immediate

Declare v_ SQL varchar2 (222); v_emp_rec emp % rowtype; begin v_ SQL: = 'select * from emp where empno =: num'; -- '=:' is a placeholder, the identifier cannot use keywords, such as number execute immediate v_ SQL into v_emp_rec using 7369; -- bind the placeholder value dbms_output.put_line (v_emp_rec.ename); end;

--Exception, pre-defined exception, keywordException when then

Declare v_emp emp % rowtype; v_ SQL varchar2 (200); v_ename emp. ename % type; begin v_ SQL: = 'select * from emp'; execute immediate v_ SQL into v_emp; -- v_ SQL: = 'select ename from emp where empno = 100 '; -- execute immediate v_ SQL into v_ename; exception when too_many_rows then -- too_many_rows is of the exception type, and there is a data dbms_output.put_line ('too many lines! '); -- When data_not_found then -- dbms_output.out_line (' not found! '); End;

--Exception, custom exception, Keyword:Exception raise

Declare v_ageException exception; -- exception type exception v_age number: = '& age:'; begin if v_age> 120 then raise v_ageException; -- when conditions are met, raise throws an exception else dbms_output.put_line (v_age); end if; exception when v_ageException then dbms_output.put_line ('Too old! '); End;