Oracle PL/SQL entry syntax points

Pl_sql: With branching and looping, process oriented
Anonymous BLOCK:
Declare (optional, where various variables and cursors are declared)
Begin (necessary, start from here)
Exception (executed after crawling to an exception)
End
[SQL] View plaincopy
Set serveroutput on; (default is OFF)
--The simplest PL/SQL statement block
Begin
Dbms_output.put_line (' helloworld! ');
End
--the simplest block of statements
Declare
V_name varchar2 (20);
Begin
V_name: = ' myname ';
Dbms_output.put_line (V_name);
End
--The composition of the statement block
Declare
V_name number: = 0;
Begin
V_name: = 2/v_num;
Dbms_output.put_line (V_name);
exception
When others then
Dbms_output.put_line (' error ');
End
--Variable declaration
1. Variable name can not use reserved words, such as from,select, etc.
2. The first character must be a letter
3. Variable name contains up to 30 characters
4. Do not have the same name as a database table or column
5. Only one variable can be declared per line
--Common variable types
1. Binary_integer: integer, used primarily to count rather than to denote field type, high efficiency
2. Number: Numeric type
3. Char: fixed-length string
4. VARCHAR2: Variable length string
5. Date: Dates
6. Long: length string, maximum 2GB
7. Boolean: Boolean type, can take value ture, false and null value, must give initial value, cannot print
--Variable declaration

[SQL] View plaincopy
Declare
V_temp number (1);
V_count Binary_integer: = 0;
V_sal number (7,2): = 4000.00;
V_date Date: = Sysdate;
V_PI constant Number (3,2): = 3.14;
V_valid Boolean: = false;
V_name varchar2 () NOT null: = ' MyName ';
Begin
Dbms_output.put_line (' v_temp value: ' | | v_count);
End
--variable declaration, using the%type property

[SQL] View plaincopy
/* Comment Multiple lines */--Comment one line
Declare
V_empno number (4);
V_empno2 emp.empno%type;--declaring a variable type with a field type in the table
V_EMPNO3 v_empno2%type;--declaring variable type with variable Type property
Begin
Dbms_output.put_line (' Test ');
End
--Simple variable assignment

[SQL] View plaincopy
Declare
V_name varchar2 (20);
V_sal number (7,2);
V_sal2 number (7,2);
V_valid Boolean: = false;
V_date date;
Begin
V_name: = ' MyName ';
V_sal: = 23.77;
V_sal2: = 23.77;
V_valid: = (v_sal = v_sal2);--Determine whether equal use =
V_date: = To_date (' 1999-08-12 12:23:30 ', ' yyyy-mm-dd HH24:MI:SS ');
End
--table variable type, which represents an array

[SQL] View plaincopy
Declare
Type Type_table_emp_empno is table of Emp.empno%type index by binary_integer;--first declares a type
V_empnos type_table_emp_empno;--then declare the variable with this type
Begin
V_empnos (0): = 7369;
V_empnos (2): = 7839;
V_empnos (-1): = 9999;
Dbms_output.put_line (V_empnos ( -1));---1 access is not available, 0,2 can access the
End
--record type, representing a class

[SQL] View plaincopy
Declare
type type_record_dept is record
(
Deptno dept.deptno%type,
Dname Dept. Dname%tpye,
Loc Dept.loc%type
);
V_temp type_record_dept;
Begin
V_temp.deptno: = 50;
V_temp.dname: = ' aaaa ';
V_temp.loc: = ' BJ ';
Dbms_output.put_line (V_temp.deptno | | "| | V_temp.dname);
End;
--Declare the record variable with%rowtype
[SQL] View plaincopy
declare
v_temp dept%rowtype;
Begin
V_temp.deptno: = 50;
V_temp.dname: = ' aaaa ';
V_temp.loc: = ' BJ ';
Dbms_output.put_line (V_temp.deptno | | "| | V_temp.dname);
End;
--pl/sql Executes a SELECT statement, with into, you must return and return only one record
--sql statement application
[SQL] View plaincopy
Declare
V_ename Emp.ename%type;
V_sal Emp.sal%type;
Begin
Select Ename,sal to V_ename,v_sal from emp where empno = 7369;
Dbms_output.put_line (ename | | "| | V_sal);
End;

Declare
V_emp Emp%rowtype;
Begin
SELECT * into V_emp from emp where empno = 7369;
Dbms_output.put_line (V_emp.ename);
End

Declare
V_deptno Dept.deptno%type: = 50;
V_dname dept.dname%type: = ' aaaa ';
V_loc dept.loc%type: = ' BJ ';
Begin
INSERT into DEPT2 values (V_DEPTNO,V_DNAME,V_LOC);
commit;--, don't forget!!!
End

SELECT * from Dept2;

Declare
V_deptno emp2.deptno%type: = 10;
V_count number;
Begin
--update emp2 Set sal = SAL/2 where deptno = V_deptno;
--select Deptno to V_deptno from emp2 where empno = 7369;
Select COUNT (*) into V_count from EMP2;
Dbms_output.put_line (Sql%rowcount | | ' records are affected ');//sql, the keyword that represents the SQL program that you just executed
end;
--pl/sql executes the DDL statement and requires execute immediate
[SQL] view plaincopy
Begin
Execute immediate ' CREATE TABLE T (nnn VARCHAR2 (() Default ' aaaa ') ';
End;
--if Statement
[SQL] View plaincopy
--remove 7369 of the salary, if <1200, output ' low ' if <2000 output ' middle ', otherwise output ' high '

Declare
V_sal Emp.sal%type;
Begin
Select Sal into V_sal from emp where empno = 7369;
if (v_sal<1200) then
Dbms_output.put_line (' low ');
elsif (v_sal<2000) then--elsif no E
Dbms_output.put_line (' Midddle ');
Else
Dbms_output.put_line (' High ');
End If;
End
--Cycle
[SQL] View plaincopy
Declare
I binary_integer: = 1;
Begin
Loop
Dbms_output.put_line (i);
I: = i + 1;
Exit when (I >= 11);
End Loop;
End

Declare
J Binary_integer: = 1;
Begin
While J < loop
Dbms_output.put_line (j);
J: = j + 1;
End Loop;
End

Begin
For k in 1..10 Loop
Dbms_output.put_line (k);
End Loop;

For k in reverse 1..10 loop
Dbms_output.put_line (k);
End Loop;
End
--Error handling
[SQL] View plaincopy
Declare
V_temp number (4);
Begin
Select Empno to V_temp from emp where deptno = 10;
exception
When Too_many_rows Then
Dbms_output.putline (' Too many Records ');
When others then
Dbms_output.put_line (' error ');
End

Declare
V_temp number (4);
Begin
Select Empno to V_temp from emp where empno = 2222;
exception
When No_data_found Then
Dbms_output.put_line (' no data ');
End

--sql Program cross-database platform is better, PL/SQL efficiency is high
--DBA, error log
[SQL] View plaincopy
CREATE TABLE Errorlog
(
ID number primary Key,
Errcode number,
ErrMsg VARCHAR2 (1024),
Errdate Date
);

Create sequence seq_errorlog_id start with 1 increment by 1;

Declare
V_deptno Dept.deptno%type: = 10;
V_errcode number;
V_errmsg VARCHAR2 (1024);
Begin
Delete from dept where deptno = V_deptno;
Commit
exception
When others then
Rollback
V_errcode: = SQLCODE;
V_errmsg: = SQLERRM;
INSERT into errorlog values (seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
Commit
End
cursors cursor, pointer on result set
[SQL] View plaincopy
Declare
Cursor C is
SELECT * from emp;--just declares the cursor, not yet fetching the data
V_emp C%rowtype
Begin
Open C;
Fetch C into v_emp;
Dbms_output.put_line (V_emp.ename);
Close C;
End

Declare
Cursor C is
SELECT * from EMP;
V_emp C%rowtype;
Beigin
Open C;
Loop
Fetch C into v_emp;
Exit when (C%notfound);
Dbms_output.put_line (V_emp.ename);
End Loop;
Close C;
End
--Cursors with parameters
[SQL] View plaincopy
Declare
Cursor C (v_deptno emp.deptno%type,v_job emp.job%type)
Is
Select Ename,sal from emp where deptno = v_deptno and job = V_job;
Begin
For v_temp in C (+, ' clerk ') loop
Dbms_output.put_line (V_temp.ename);
End Loop;
End
--Updatable Cursors
[SQL] View plaincopy
Declare
Cursor C
Is
SELECT * from EMP2 for update;
Begin
For v_temp in C loop
if (V_temp.sal <) Then
Update EMP2 Set sal = sal*2 where CURRENT of C;
else if (v_temp.sal =) Then
Delete from EMP2 where current of C;
End If;
End Loop;
Commit
End
--Create a stored procedure, a PL/SQL block with a name
[SQL] View plaincopy
Grant CREATE procedure to Shijin;
Create or Replace procedure P
Is
Cursor C is
SELECT * from EMP2 for update;
Begin
For v_emp in C loop
if (V_emp.deptno = ten) Then
Update EMP2 Set sal = Sal + ten where current of C;
else if (V_emp.deptno =) Then
Update EMP2 Set sal = sal + where current of C;
Else
Update EMP2 Set sal = sal + where current of C;
End If;
End Loop;
Commit
End Loop;

Begin
P
End
Or
exec p;
--Stored procedure with parameters, default is in
[SQL] View plaincopy
Create or Replace procedure P
(V_a in Number,v_b Number,v_ret out number,v_temp in out number)
Is

Begin
if (V_a > V_b) Then
V_ret: = v_a;
Else
V_ret: = V_b;
End If;
V_temp: = v_temp + 1;
End

Declare
V_a Number: = 3;
V_b Number: = 4;
V_ret number;
V_temp Number: = 5;
Begin
P (v_a,v_b,v_ret,v_temp);
Dbms_output.put_line (V_ret);
Dbms_output.put_line (v_temp);
End
--function
[SQL] View plaincopy
Create or Replace function Sal_tax
(v_sal number)
return number
Is
Begin
if (V_sal <) Then
return 0.10;
elsif (V_sal < 2750) Then
return 0.15;
Else
return 0.20;
End If;
End

Select Sal_tax (SAL) from EMP;
--Trigger!!! Grasp the concept firmly
[SQL] View plaincopy
CREATE TABLE Emp2_log
(
uname varchar2 (20),
Action VARCHAR2 (10),
Atime Date
);
Grant create Triger to Shijin;
Create or Replace trigger trig
After insert or delete or update on EMP2--Here you can add a for each row
Begin
If inserting then
INSERT into Emp2_log values (USER, ' Insert ', sysdate);
elsif updating Then
INSERT into Emp2_log values (USER, ' Update ', sysdate);
elsif deleting Then
INSERT into Emp2_log values (USER, ' delete ', sysdate);
End If;
End

Update EMP2 Set sal = sal*2 where deptno = 30;

Drop trigger trig;

Create or Replace trigger trig
After update on dept
For each row
Begin
Update emp Set deptno =: New.deptno where deptno =: old.deptno;
End
--Storage and display of tree-like structure data
[SQL] View plaincopy
drop table article;

CREATE TABLE Article
(
ID number primary KEY,
Cont varchar2 (4000),
PID number,
IsLeaf number (1),--0 Represents a non-leaf node, 1 represents a leaf node
Alevel number (2)
)

Create or Replace procedure P (v_pid article.pid%type,v_level binary_integer) is
cursor C was select * from article where pid = V_pid;
V_prestr VARCHAR2 (1024): = ';
Begin
For i in 1..v_level loop
V_prestr: = V_prestr | | ‘****‘;
End Loop;
for v_article in C loop
Dbms_output.put_line (v_prestr | | v_article.cont);
if (v_article.isleaf = 0) then
P (v_articel.id,v_level + 1);
End If;
End Loop;
End;
INSERT into article values (1, ' Ant vs Elephant ', 0,0,0);
INSERT into article values (2, ' elephants are beaten down ', 1,0,1);
INSERT into article values (3, ' ants are not good ', 2,1,2);
INSERT into article values (4, ' nonsense ', 2,0,2);
INSERT into article values (5, ' no nonsense ', 4,1,3);
INSERT into article values (6, ' How possible ', 1,0,1);
INSERT into article values (7, ' How not possible ', 6,1,2);
INSERT into article values (8, ' very likely ', 6,1,2);
INSERT into article values (9, ' Elephant in hospital ', 2,0,2);
INSERT into article values (10, ' Nurse is Ant ', 9,1,3);

Set serverout on;
Begin
P (0,0);
End
Show error;

Oracle PL/SQL entry syntax points