📄 createstoreprocedure_sqlserver.jsp

📁 此资源是jsp应用开发邓子云等编写的一书里的源代码
💻 JSP
字号:
<%@ page language="java" contentType="text/html; charset=GBK"%>
<%@ page import="java.sql.*,javax.naming.*,javax.sql.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>    
    <title>创建SQLServer存储过程</title>
  </head>  
  <body>
  <%
   Connection con = null;
	try {
			// 通过连接池来获得一个连接
			Context initContext = new InitialContext();
			Context envContext  = (Context)initContext.lookup("java:/comp/env");
			DataSource ds = (DataSource)envContext.lookup("jdbc/sqlserver");
			con = ds.getConnection();
			Statement stmt = con.createStatement();			
			// 1.创建存储过程show_students
			String createProcedure1 = "create procedure show_students " + "as "	+ "select id, name,age " + "from student " + "order by id";
			// 删除数据库中存在的同名过程
			stmt.executeUpdate("if exists(select name from sysobjects "
					+ "where name='show_students'and type='p') "
					+ "drop procedure show_students");
			stmt.executeUpdate(createProcedure1);
			out.println("<h1>第一个存储过程show_students创建成功</h1><br>");			

			// 2.创建储存过程onestudent
			String createProcedure2 = "create procedure onestudent "
					+ "@stu_id int = null, " + "@name varchar(20) output, "
					+ "@age  int  output " + "as " + "if @stu_id = null "
					+ "BEGIN "
					+ "  PRINT 'ERROR: You must specify a stu_id value.' "
					+ "  RETURN "
					+ "END "
					+
					// Get the sales for the specified cof_name and " +
					// assign it to the output parameter. " +
					"SELECT @name = name, @age = age " + "FROM student "
					+ "WHERE id = @stu_id " + "RETURN ";
			stmt.executeUpdate("if exists(select name from sysobjects "
					+ "where name='onestudent'and type='p') "
					+ "drop procedure onestudent");
			stmt.executeUpdate(createProcedure2);			
			out.println("<h1>第二个存储过程onestudent创建成功</h1><br>");

			// 3.创建函数
			String createProcedure3 = "CREATE FUNCTION ageofstu "
					+
					// Input cof_name
					"(@stu_name varchar(20)) "
					+ "RETURNS int "
					+ // return sales
					"AS " + "BEGIN " + "  DECLARE @age int "
					+ "  SELECT @age = age " + "  FROM student "
					+ "  WHERE name like @stu_name " + "  RETURN @age "
					+ "END ";
			stmt.executeUpdate("if exists(select name from sysobjects "
					+ "where name='ageofstu') "
					+ "drop function ageofstu");
			stmt.executeUpdate(createProcedure3);
			out.println("<h1>函数ageofstu创建成功</h1><br>");
			stmt.close();
			//con.close();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// 用完后要关闭连接，释放资源
				if (con != null)
					con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
   %>    
  </body>
</html>
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -