Brief introduction to PL/SQL basic knowledge

1.PL/SQL code block The PL/SQL code block is a collection of instructions that supports all DML,NDS,DBMS_SQL,DDL.    : DML is a data manipulation language (manipulation Language) including, Insert, Update,delete    : DDL is the data definition Language, including, Alter,create,drop,truncate,grant,revoke    : NDS local Dynamics SQL (Native dynamic SQL)

2.pl/  SQL code block structure    : Declaration part:     Execution Part     : Exception Handling Department

--Minimum code blockbegin    NULL;End;/--Anonymous BlockDeclare   --Declarations SectionV_date_timetimestamp;begin     --Executive Section    SelectSystimestramp intoV_date_time fromdual; Dbms_output. Put_Line (v_date_time); exception--Exception Handling Section     whenothers ThenDbms_output. Put_Line (SQLERRM)End;//*anonymous block run 1. Type all code after the sql> command under SQL windows to run. 2. Use the CD to enter the Save directory for this script 3. Running in the PL/SQL user interface*/--named BlocksCreate or Replace procedureCompoileerror asV_timesramp Timestramp; begin    SelectSystimestamp intoV_timesramp fromDuall;--This is a non-existent tableDbms_output. Put_Line (V_timesramp); exception whenothers ThenDbms_output. Put_Line (SQLERRM);End;//*The name is quickly compiled, then stored in the database, and then executed---because calling a table that does not exist compiles the error warring:procedure created with Complilation errors view error message 1. You can use Show errors Statement view detailed error show errors2. Returns the code and line number of the stored procedure Select Line| | ' ' Text procedure from User_source where Name= ' Compoileerror ' 3. Execute view error exec compoileerror*/

4. Trigger triggers are a special implementation of PL/SQL, which are stored in a database but are not stored procedures or functions. Event-driven and associated with some operation performed in the database.

Create or Replace Triggerauthor_tring AfterUpdate  offirst_name onauthors forEach row when(Old.first_name!=new.first_name)beginDbms_output. Put_Line ('First Name'        ||: Old.first_name||' have change to'        ||: new.first_name);End;/trigger is triggered when update first_nameUpdateauthorsSetFirst_Name='Roald'whereFirst_Name='Ron'--the screen will show:First Name Ron has a change toRonald

------------------------------------procedure, the creation of a function------------------------------create a stored procedureCreate or ReplaceAddnewauthor (p_id authors.id%TYPE, P_firstname authors.first_name%TYPE, P_lasetname authors.last_name%TYPE) asbegin     Insert  intoauthors (Id,first_name,last_name)Values(p_id,p_firstname,p_lasetname);EndAddnewauthor;/--call a stored procedurebeginAddnewauthor ( -,'Zelda','Zudink');End;--Create a functionCreate or Replace functionThreeauthors (P_ISBNinchBooks.ISBN%TYPE)returnBoolean asV_author3 Books.authors3%TYPEbeginSelectAuthors3 intoV_author3 fromBookswhereIsbn=P_ISBN;ifV_author3 is NULL  Then    returnfalse;Else    returntrue;End if;Endthreeauthors;--calling Functionsbegin     forCur_recinch(SelectIsbn,title frombooks) LoopifThreeauthors (Cur_rec. ISBN) ThenDbms_output. Put_Line ('""'||Cur_rec||'"has 3 authors'); End if; EndLoop;End;/---removal of procedures and functions--drop procedure procedure_name;Drop procedureAddnewauthor;--drop function functionname;Drop functionThreeauthors;