oracle--Stored Procedure--bai

--1 The simplest stored procedure create or replace procedure Hello_proc as V_name varchar2 (20); V_age number;   Begin v_name:= ' Bai ';   v_age:=18; Dbms_output.put_line (' Hello ' | | v_name| | ', you this year ' | | V_age); end;--call in Plsql beginhello_proc;end;/* run set serverout on;exec system.hello_proc;*/--exercise 1create or Replace in command window Procedure Show_sum_procas v_i number:= ' 1 ';    V_sum number:= ' 0 '; begin loop V_sum:=v_sum+v_i;      v_i:=v_i+1;    Exit when v_i=201;   End Loop; Dbms_output.put_line (v_sum); End;--2 stored procedure with entry to create or replace procedure Show_add_result (I number default 10,--Defaults J Number default) as V_result number;begin V_result: =i+j; Dbms_output.put_line (i| | ' + ' | | j| | ' = ' | | V_result); End;begin show_add_result (' 2 '); End;select * FROM scott.emp;--Classic example displays the employee name and salary for the specified work number, and if not, outputs that employee does not exist create or re  Place procedure Show_emp_by_eno_proc (V_eno scott.emp.empno%type:= ' 7369 ') asv_ename varchar; V_sal number;begin  Select Ename,sal into V_ename,v_sal from Scott.emp where Empno=v_eno; Dbms_output.put_line (V_eno| | ' Corresponding Employee Name ' | | v_ename| | ', Wages ' | | V_sal); Exception when No_data_found then Dbms_output.put_line (' Can't find ' | | v_eno| | ' End;create or Replace procedure Show_emp_by_eno_procasbegin dbms_output.put_line (' Test overloads '); End;begin Show_emp_  By_eno_proc (); end;--find employees by name Create or replace procedure Show_emp_by_ename_proc (v_ename varchar2) asv_sal Number;begin  Select Sal into V_sal from Scott.emp where ename=v_ename; Dbms_output.put_line (v_ename| | ' Corresponding employee Wages ' | | V_sal); Exception when No_data_found then Dbms_output.put_line (' Can't find ' | | v_ename| | '  The corresponding employees '); When Too_many_rows then Dbms_output.put_line (v_ename| | '  The corresponding staff of more than 1 '); When the others then Dbms_output.put_line (' The lookup process is abnormal! '); end;---Create or replace procedure Show_dname_by_eno_depno (v_depno scott.dept.deptno%type) asv_dname varchar (20);  Begin select Dname to V_dname from scott.dept where deptno = V_depno; Dbms_output.put_line (v_depno| | ' The corresponding department name ' | | V_dname); Exception when No_data_found then Dbms_output.put_line (' Can't find ' | | v_depno| | ' The corresponding department '); End;--3 have the entry, there is the argumentStored procedure input i,j, with the result parameter to get the added results of Create or replace procedure Get_add_result_proc (i number, j number, result out number--Specify      Result is a parameter) Asbegin result:=i+j;end;--uses Plsql to call the stored procedure declare result number;begin Get_add_result_proc (' 1 ', ' 2 ', result); Dbms_output.put_line (result); end;--create a stored procedure to get the current date and day of the week create or replace procedure Get_date_and_day (v_date out varchar 2, v_day out varchar2) asbegin Select To_char (sysdate, ' DD '), To_char (sysdate, ' Day ') to V_date,v_day from Dual;end;

　　

oracle--Stored Procedure--bai